Data model

This page is for developers and database administrators. It contains detailed database schemas and table structures. For a business-level overview of Midaz entities and their relationships, see About Midaz.

Explore the core tables that power Midaz. As Midaz evolves, it may add new fields and tables. Midaz keeps the tables on this page in two PostgreSQL databases:

Onboarding database: organization, ledger, asset, account_type, account, portfolio, and segment.
Transaction database: balance, operation_route, operation, transaction_route, and transaction.

Midaz stores the metadata of each entity in MongoDB, not in these tables. Column names are database names and can differ from the API field names.

Tables

Table: `organization`

Column	Type	Description
id	`uuid`	The unique identifier of the Organization.
parent_organization_id	`uuid`	The unique identifier of the parent Organization.
legal_name	`text`	The legal name of the Organization.
doing_business_as	`text`	The trade name of the Organization.
legal_document	`text`	The document of the Organization.
address	`jsonb`	The address of the Organization.
status	`text`	The status code.
status_description	`text`	The description 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: `ledger`

Column	Type	Description
id	`uuid`	The unique identifier of the Ledger.
organization_id	`uuid`	The unique identifier of the Organization.
name	`text`	The name of the Ledger.
status	`text`	The status code.
status_description	`text`	The description of the status.
settings	`jsonb`	The Ledger settings.
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`	The unique identifier of the Asset.
organization_id	`uuid`	The unique identifier of the Organization.
ledger_id	`uuid`	The unique identifier of the Ledger.
name	`text`	The name of the Asset.
type	`text`	The type of Asset (`currency`, `fiat`, `crypto`, `commodity`, `others`).
code	`text`	The code used to refer to the Asset.
status	`text`	The status code.
status_description	`text`	The description 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_type`

Column	Type	Description
id	`uuid`	The unique identifier of the Account Type.
organization_id	`uuid`	The unique identifier of the Organization.
ledger_id	`uuid`	The unique identifier of the Ledger.
name	`varchar(100)`	Name of the Account Type.
description	`text`	Description of the Account Type.
key_value	`varchar(50)`	The key that identifies the Account Type in the Ledger.
default_direction	`varchar(16)`	The default balance direction for Accounts of this type: `credit` or `debit`.
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`	The unique identifier of the Account.
organization_id	`uuid`	The unique identifier of the Organization.
ledger_id	`uuid`	The unique identifier of the Ledger.
asset_code	`text`	The code used to refer to the Asset.
name	`text`	The name of the Account.
alias	`text`	The alias that transactions use to address the Account.
type	`text`	The type of account.
parent_account_id	`uuid`	The unique identifier of the Parent Account.
entity_id	`text`	A reference to a record in an external system.
holder_id	`uuid`	The unique identifier of the Holder that owns the Account.
holder_check_skipped	`boolean`	`true` when a `skip` flag bypassed the Holder check at creation.
portfolio_id	`uuid`	The unique identifier of the Portfolio.
segment_id	`uuid`	The unique identifier of the Segment.
status	`text`	The status code.
status_description	`text`	The description of the status.
blocked	`boolean`	`true` when the Account is blocked.
closed_at	`timestamptz`	Timestamp of closing, if applicable (UTC).
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`	The unique identifier of the Portfolio.
organization_id	`uuid`	The unique identifier of the Organization.
ledger_id	`uuid`	The unique identifier of the Ledger.
entity_id	`text`	The unique identifier of the user responsible for the Portfolio.
name	`text`	The name of the Portfolio.
status	`text`	The status code.
status_description	`text`	The description 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: `segment`

Column	Type	Description
id	`uuid`	The unique identifier of the Segment.
organization_id	`uuid`	The unique identifier of the Organization.
ledger_id	`uuid`	The unique identifier of the Ledger.
name	`text`	The name of the Segment.
status	`text`	The status code.
status_description	`text`	The description 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: `balance`

Column	Type	Description
id	`uuid`	The unique identifier of the Balance.
organization_id	`uuid`	The unique identifier of the Organization.
ledger_id	`uuid`	The unique identifier of the Ledger.
account_id	`uuid`	The unique identifier of the Account.
alias	`text`	The alias of the Account.
key	`text`	The balance key. The default balance uses `default`.
asset_code	`text`	The code used to refer to the Asset.
available	`decimal`	The available amount.
on_hold	`decimal`	Amount on hold/reserved.
version	`bigint`	Balance version, which is updated with each transaction.
account_type	`text`	The type of account.
allow_sending	`boolean`	If true, indicates that sending transactions is permitted.
allow_receiving	`boolean`	If true, indicates that receiving transactions is permitted.
direction	`varchar(16)`	The balance direction: `credit` or `debit`.
overdraft_used	`decimal`	The overdraft amount in use.
settings	`jsonb`	The balance settings, such as the overdraft limit.
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: `operation_route`

Column	Type	Description
id	`uuid`	The unique identifier of the Operation Route.
organization_id	`uuid`	The unique identifier of the Organization.
ledger_id	`uuid`	The unique identifier of the Ledger. Empty for a route that belongs to the Organization.
title	`varchar(255)`	Short text summarizing the purpose of the operation. Used as an entry note for identification.
description	`varchar(250)`	Detailed description of the Operation Route purpose and usage.
code	`text`	The code of the Operation Route.
operation_type	`varchar(20)`	The entry’s applicability: `source`, `destination`, or `bidirectional`.
account_rule_type	`varchar(20)`	The rule that selects the account: `alias` or `account_type`.
account_rule_valid_if	`text`	The values that the account rule accepts.
accounting_entries	`jsonb`	The accounting entries of the route.
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: `operation`

Column	Type	Description
id	`uuid`	The unique identifier of the Operation.
transaction_id	`uuid`	The unique identifier of the Transaction.
organization_id	`uuid`	The unique identifier of the Organization.
ledger_id	`uuid`	The unique identifier of the Ledger.
account_id	`uuid`	The unique identifier of the Account.
account_alias	`text`	The alias for the account used in the operation.
balance_id	`uuid`	The unique identifier of the Balance.
balance_key	`text`	The key of the Balance.
description	`text`	Description of the operation.
type	`text`	The type of the operation, such as `DEBIT`, `CREDIT`, `ON_HOLD`, or `RELEASE`.
direction	`varchar(10)`	The direction of the operation: `debit` or `credit`.
asset_code	`text`	The code used to refer to the Asset.
amount	`decimal`	The amount of the operation.
available_balance	`decimal`	The available amount before the operation.
on_hold_balance	`decimal`	The amount on hold before the operation.
available_balance_after	`decimal`	The available amount after the operation.
on_hold_balance_after	`decimal`	The amount on hold after the operation.
balance_version_before	`bigint`	The balance version before the operation.
balance_version_after	`bigint`	The balance version after the operation.
balance_affected	`boolean`	`false` when the operation does not change the balance, as in an annotation transaction.
chart_of_accounts	`text`	The name of the chart of accounts that the operation belongs to.
route	`text`	[Deprecated] Legacy route identifier, kept for backward compatibility. Use `route_id` instead.
route_id	`uuid`	Identifier of the route used by the operation (the current route field).
route_code	`text`	The code of the accounting rubric that Midaz resolved for the operation.
route_description	`text`	The description of the accounting rubric that Midaz resolved for the operation.
snapshot	`jsonb`	System context of the operation, such as the overdraft use before and after it.
status	`text`	The status code.
status_description	`text`	The description of the status.
recorded_at	`timestamptz`	Timestamp of when the ledger recorded the operation (UTC).
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_route`

Column	Type	Description
id	`uuid`	The unique identifier of the Transaction Route.
organization_id	`uuid`	The unique identifier of the Organization.
ledger_id	`uuid`	The unique identifier of the Ledger. Empty for a route that belongs to the Organization.
title	`varchar(255)`	Short text summarizing the purpose of the transaction. Used as an entry note for identification.
description	`varchar(250)`	A description for the Transaction Route.
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).

The operation_transaction_route table links each Transaction Route to its Operation Routes.

Table: `transaction`

Column	Type	Description
id	`uuid`	The unique identifier of the transaction.
parent_transaction_id	`uuid`	The unique identifier of the parent transaction, such as the transaction that a revert reverses.
group_id	`uuid`	Links the transactions that one cross-ledger request creates.
organization_id	`uuid`	The unique identifier of the Organization.
ledger_id	`uuid`	The unique identifier of the Ledger.
description	`text`	Description of the transaction.
route	`text`	[Deprecated] Legacy route identifier, kept for backward compatibility. Use `route_id` instead.
route_id	`uuid`	Identifier of the route used by the transaction (the current route field).
status	`text`	The status code.
status_description	`text`	The description of the status.
amount	`decimal`	The sent amount.
asset_code	`text`	The code used to refer to the Asset.
chart_of_accounts_group_name	`text`	The name of the chart of accounts group used to categorize the operations of a transaction.
body	`jsonb`	Internal copy of the transaction request.
fees_skipped	`boolean`	`true` when a `skip` flag bypassed the fee calculation.
tracer_skipped	`boolean`	`true` when a `skip` flag bypassed Tracer.
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).

Figure 1 shows the core Midaz tables and their relationships. Use it to guide data handling and integration.

Core Midaz database tables and the relationships between them, as a reference for data handling and integration — Figure 1. Midaz tables and their relations.

Midaz Core

CRM

Fees

Data model

Tables

Table: `organization`

Table: `ledger`

Table: `asset`

Table: `account_type`

Table: `account`

Table: `portfolio`

Table: `segment`

Table: `balance`

Table: `operation_route`

Table: `operation`

Table: `transaction_route`

Table: `transaction`

Data model

​Tables

​Table: organization

​Table: ledger

​Table: asset

​Table: account_type

​Table: account

​Table: portfolio

​Table: segment

​Table: balance

​Table: operation_route

​Table: operation

​Table: transaction_route

​Table: transaction

​Data model

Tables

Table: `organization`

Table: `ledger`

Table: `asset`

Table: `account_type`

Table: `account`

Table: `portfolio`

Table: `segment`

Table: `balance`

Table: `operation_route`

Table: `operation`

Table: `transaction_route`

Table: `transaction`

Data model