The following page provides an overview of the core tables and their schemas in Midaz, offering developers and database administrators a reference for understanding data structure and relationships. Note that future versions of Midaz may introduce additional fields or tables.

Tables

Each table is structured to support efficient querying and data integrity, aligning with Midaz's commitment to scalability and flexibility.

Table: `organization`

Column	Type	Description
id	`uuid`	The unique ID for the Organization.
parent_organization_id	`uuid`	(Optional) If the Organization is a child, this is the unique ID of its Parent Organization.
legal_name	`text`	The official legal name of the Organization.
doing_business_as	`text`	The trade or DBA name, if different from the legal name.
legal_document	`text`	The registration or tax ID.
address	`jsonb`	A structured object containing address details.
status	`text`	The Organization’s status (e.g., active, inactive).
status_description	`text`	A description providing context for the status.
created_at	`timestamptz`	The timestamp when the Organization was created (UTC).
updated_at	`timestamptz`	The timestamp of the last update (UTC).
deleted_at	`timestamptz`	The timestamp of soft deletion, if applicable (UTC).

Table: `ledger`

Column	Type	Description
id	`uuid`	Unique ID for the Ledger.
name	`text`	Ledger name.
organization_id	`uuid`	ID of the Organization that owns the Ledger.
status	`text`	Ledger status (e.g., active, closed).
status_description	`text`	Explanation of the status.
created_at	`timestamptz`	Timestamp of creation (UTC).
updated_at	`timestamptz`	Timestamp of last update (UTC).
deleted_at	`timestamptz`	Timestamp of soft deletion, if applicable (UTC).

Table: `asset`

Column	Type	Description
id	`uuid`	Unique ID for the Asset.
name	`text`	Descriptive name (e.g., "US Dollar").
type	`text`	Asset category (currency, crypto, commodity, etc.).
code	`text`	Asset code (e.g., USD, BRL).
status	`text`	Asset status (active, inactive).
status_description	`text`	Explanation of the status.
ledger_id	`uuid`	Ledger where the asset is registered.
organization_id	`uuid`	Organization owning the asset.
created_at	`timestamptz`	Timestamp of creation (UTC).
updated_at	`timestamptz`	Timestamp of last update (UTC).
deleted_at	`timestamptz`	Timestamp of soft deletion, if applicable (UTC).

Table: `segment`

Column	Type	Description
id	`uuid`	Unique ID for the Segment.
name	`text`	Segment name (e.g., "Premium Customers").
ledger_id	`uuid`	Ledger the segment belongs to.
organization_id	`uuid`	Organization for this segment.
status	`text`	Segment status (active, inactive).
status_description	`text`	Explanation of the status.
created_at	`timestamptz`	Timestamp of creation (UTC).
updated_at	`timestamptz`	Timestamp of last update (UTC).
deleted_at	`timestamptz`	Timestamp of soft deletion, if applicable (UTC).

Table: `portfolio`

Column	Type	Description
id	`uuid`	Unique ID for the Portfolio.
name	`text`	Portfolio name (e.g., customer label).
entity_id	`text`	External reference for the owning entity/customer.
ledger_id	`uuid`	Ledger containing this portfolio.
organization_id	`uuid`	Organization that owns this portfolio.
status	`text`	Portfolio status (active, closed, etc.).
status_description	`text`	Explanation of the status.
created_at	`timestamptz`	Timestamp of creation (UTC).
updated_at	`timestamptz`	Timestamp of last update (UTC).
deleted_at	`timestamptz`	Timestamp of soft deletion, if applicable (UTC).

Table: `account`

Column	Type	Description
id	`uuid`	Unique ID for the Account.
name	`text`	Account name or nickname.
parent_account_id	`uuid`	(Optional) Parent account ID if this is a sub-account.
entity_id	`text`	External reference for the account owner.
asset_code	`text`	Code of the asset (e.g., "USD").
organization_id	`uuid`	Organization that owns this account.
ledger_id	`uuid`	Ledger containing this account.
portfolio_id	`uuid`	Portfolio grouping this account.
segment_id	`uuid`	Segment classification, if applicable.
available_balance	`numeric`	Current available balance.
on_hold_balance	`numeric`	Amount on hold/reserved.
balance_scale	`numeric`	Decimal precision factor.
status	`text`	Account status (active, frozen, etc.).
status_description	`text`	Explanation of the status.
allow_sending	`boolean`	Whether debits from this account are allowed.
allow_receiving	`boolean`	Whether credits to this account are allowed.
alias	`text`	An alternative identifier for easier reference.
type	`text`	Account type (e.g., "checking", "savings").
version	`numeric`	Version number for concurrency control.
created_at	`timestamptz`	Timestamp of creation (UTC).
updated_at	`timestamptz`	Timestamp of last update (UTC).
deleted_at	`timestamptz`	Timestamp of soft deletion, if applicable (UTC).

Table: `transaction`

Column	Type	Description
id	`uuid`	Unique ID for the Transaction.
parent_transaction_id	`uuid`	(Optional) Parent transaction ID, if linked.
description	`text`	Brief description or memo.
template	`uuid`	(Optional) Template ID if the transaction is templated.
status	`text`	Transaction status (pending, completed, reversed).
status_description	`text`	Explanation of the status.
amount	`numeric`	Total transaction amount.
amount_scale	`numeric`	Decimal precision factor for amount.
asset_code	`text`	Asset code (if single asset).
ledger_id	`uuid`	Ledger for this transaction.
organization_id	`uuid`	Organization that owns the transaction.
body	`jsonb`	JSON object storing details of the transaction.
created_at	`timestamptz`	Timestamp of creation (UTC).
updated_at	`timestamptz`	Timestamp of last update (UTC).
deleted_at	`timestamptz`	Timestamp of soft deletion, if applicable (UTC).

Data Model

In Figure 1, you can find a visual representation of the core tables in Midaz and their relationships. The diagram helps developers and DB administrators understand how the different entities are interconnected within the system, serving as a guide for efficient data handling and integration.

Figure 1. Midaz tables and their relations.

Tables

Table: organization

Table: ledger

Table: asset

Table: segment

Table: portfolio

Table: account

Table: transaction