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 and schemas that power Midaz. As Midaz evolves, it may add new fields and tables.

Tables

Table: `organization`

Column	Type	Description
id	`uuid`	The unique identifier of the Organization.
legalName	`text`	The legal name of the Organization.
parentOrganizationId	`uuid`	The unique identifier of the parent Organization.
doingBusinessAs	`text`	The trade name of the Organization.
legalDocument	`text`	The document of the Organization.
status	`jsonb`	Information about the status.
address	`jsonb`	Information about the address of the Organization.
metadata	`jsonb`	Key-value pairs to add as metadata.
createdAt	`timestamptz`	Timestamp of creation (UTC).
updatedAt	`timestamptz`	Timestamp of last update (UTC).
deletedAt	`timestamptz`	The timestamp of soft deletion, if applicable (UTC).

Table: `ledger`

Column	Type	Description
id	`uuid`	The unique identifier of the Ledger.
organizationId	`uuid`	The unique identifier of the Organization.
name	`text`	The name of the Ledger.
status	`jsonb`	Information about the status.
metadata	`jsonb`	Key-value pairs to add as metadata.
createdAt	`timestamptz`	Timestamp of creation (UTC).
updatedAt	`timestamptz`	Timestamp of last update (UTC).
deletedAt	`timestamptz`	Timestamp of soft deletion, if applicable (UTC).

Table: `asset`

Column	Type	Description
id	`uuid`	The unique identifier of the Asset.
organizationId	`uuid`	The unique identifier of the Organization.
ledgerId	`uuid`	The unique identifier of the Ledger.
name	`text`	The name of the Asset.
type	`enum`	The type of Asset (`currency`, `fiat`, `crypto`, `commodity`, `others`).
code	`text`	The code used to refer to the Asset.
status	`text`	Information about the status.
metadata	`jsonb`	Key-value pairs to add as metadata.
createdAt	`timestamptz`	Timestamp of creation (UTC).
updatedAt	`timestamptz`	Timestamp of last update (UTC).
deletedAt	`timestamptz`	Timestamp of soft deletion, if applicable (UTC).

Table: `account-type`

Column	Type	Description
id	`uuid`	The unique identifier of the Account Type.
ledgerId	`uuid`	The unique identifier of the Ledger.
name	`text`	Name of the Account Type.
description	`text`	Description of the Account Type.
keyValue	`text`	Custom value defined by the user to identify the Account Type.
createdAt	`timestamptz`	Timestamp of creation (UTC).
updatedAt	`timestamptz`	Timestamp of last update (UTC).
deletedAt	`timestamptz`	Timestamp of soft deletion, if applicable (UTC).

Table: `account`

Column	Type	Description
id	`uuid`	The unique identifier of the Account.
organizationId	`uuid`	The unique identifier of the Organization.
ledgerId	`uuid`	The unique identifier of the Ledger.
assetCode	`text`	The code used to refer to the Asset.
name	`text`	The name of the Account.
alias	`text`	A unique, user-friendly identifier for the account.
type	`text`	The type of account.
parentAccountId	`uuid`	The unique identifier of the Parent Account.
entityId	`text`	The unique identifier of the Entity responsible for the Account.
portfolioId	`uuid`	The unique identifier of the Portfolio.
segmentId	`uuid`	The unique identifier of the Segment.
status	`jsonb`	Information about the status.
metadata	`jsonb`	Key-value pairs to add as metadata.
createdAt	`timestamptz`	Timestamp of creation (UTC).
updatedAt	`timestamptz`	Timestamp of last update (UTC).
deletedAt	`timestamptz`	Timestamp of soft deletion, if applicable (UTC).

Table: `portfolio`

Column	Type	Description
id	`uuid`	The unique identifier of the Portfolio.
organizationId	`uuid`	The unique identifier of the Organization.
ledgerId	`uuid`	The unique identifier of the Ledger.
entityId	`text`	The unique identifier of the user responsible for the Portfolio.
name	`text`	The name of the Portfolio.
status	`jsonb`	Information about the status.
metadata	`jsonb`	Key-value pairs to add as metadata.
createdAt	`timestamptz`	Timestamp of creation (UTC).
updatedAt	`timestamptz`	Timestamp of last update (UTC).
deletedAt	`timestamptz`	Timestamp of soft deletion, if applicable (UTC).

Table: `segment`

Column	Type	Description
id	`uuid`	The unique identifier of the Segment.
organizationId	`uuid`	The unique identifier of the Organization.
ledgerId	`uuid`	The unique identifier of the Ledger.
name	`text`	The name of the Segment.
status	`jsonb`	Information about the status.
metadata	`jsonb`	Key-value pairs to add as metadata.
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	`text`	The unique identifier of the Balance.
organizationId	`uuid`	The unique identifier of the Organization.
ledgerId	`uuid`	The unique identifier of the Ledger.
accountId	`uuid`	The unique identifier of the Account.
alias	`text`	The alias for the account used in the operation.
assetCode	`text`	The code used to refer to the Asset.
available	`text`	Previous available balance.
onHold	`text`	Amount on hold/reserved.
version	`integer`	Balance version, which is updated with each transaction.
accountType	`text`	The type of account.
allowSending	`boolean`	If true, indicates that sending transactions is permitted.
allowReceiving	`boolean`	If true, indicates that receiving transactions is permitted.
createdAt	`timestamptz`	Timestamp of creation (UTC).
updatedAt	`timestamptz`	Timestamp of last update (UTC).
deletedAt	`timestamptz`	Timestamp of soft deletion, if applicable (UTC).

Table: `operation-route`

Column	Type	Description
id	`uuid`	The unique identifier of the Operation Route.
organizationId	`uuid`	The unique identifier of the Organization.
ledgerId	`uuid`	The unique identifier of the Ledger.
title	`text`	Short text summarizing the purpose of the operation. Used as an entry note for identification.
description	`text`	Detailed description of the Operation Route purpose and usage.
operationType	`text`	`operationType` — the entry’s applicability: `source`, `destination`, or `bidirectional`.
account	`jsonb`	Defines the rule for selecting the account that will participate in the operation (debit or credit).
createdAt	`timestamptz`	Timestamp of creation (UTC).
updatedAt	`timestamptz`	Timestamp of last update (UTC).
deletedAt	`timestamptz`	Timestamp of soft deletion, if applicable (UTC).

Table: `operation`

Column	Type	Description
id	`uuid`	The unique identifier of the Operation.
transactionId	`uuid`	The unique identifier of the Transaction.
organizationId	`uuid`	The unique identifier of the Organization.
ledgerId	`uuid`	The unique identifier of the Ledger.
accountId	`uuid`	The unique identifier of the account of which you want to retrieve the balances.
balanceId	`uuid`	The unique identifier of the Balance.
accountAlias	`text`	The alias for the account used in the operation.
description	`text`	Description of the transaction.
type	`text`	The type of the operation (debit or credit).
assetCode	`text`	The name of the asset used in the operation.
chartOfAccounts	`text`	The name of the chart of accounts that the operation belongs to.
route	`text`	[Deprecated] Legacy route identifier, kept for backward compatibility. Use `routeId` instead.
routeId	`uuid`	Identifier of the route used by the operation (the current route field).
amount	`jsonb`	An object containing information about the amount used in the operation.
balance	`jsonb`	An object containing information about the balance before the operation.
balanceAfter	`jsonb`	An object containing information about the balance after the operation.
status	`jsonb`	The transaction status (pending, completed, reversed).
metadata	`jsonb`	Key-value pairs to add as metadata.
createdAt	`timestamptz`	Timestamp of creation (UTC).
updatedAt	`timestamptz`	Timestamp of last update (UTC).
deletedAt	`timestamptz`	Timestamp of soft deletion, if applicable (UTC).

Deprecated field: This field remains for backward compatibility. Do not use it.

Table: `transaction-route`

Column	Type	Description
id	`uuid`	The unique identifier of the Transaction Route.
organizationId	`uuid`	The unique identifier of the Organization.
ledgerId	`uuid`	The unique identifier of the Ledger.
title	`text`	Short text summarizing the purpose of the transaction. Used as an entry note for identification.
description	`text`	A description for the Transaction Route.
operationRoutes	`array`	A list of Operation Route IDs that define the debit and credit logic for the transaction.
metadata	`object`	Key-value pairs to add as metadata.
createdAt	`timestamptz`	Timestamp of creation (UTC).
updatedAt	`timestamptz`	Timestamp of last update (UTC).
deletedAt	`timestamptz`	Timestamp of soft deletion, if applicable (UTC).

Table: `transaction`

Column	Type	Description
id	`uuid`	The unique identifier of the transaction.
organizationId	`uuid`	The unique identifier of the Organization.
ledgerId	`uuid`	The unique identifier of the Ledger.
description	`text`	Description of the transaction.
route	`text`	[Deprecated] Legacy route identifier, kept for backward compatibility. Use `routeId` instead.
routeId	`uuid`	Identifier of the route used by the transaction (the current route field).
status	`jsonb`	Information about the status.
amount	`text`	The sent amount.
assetCode	`text`	The code used to refer to the Asset.
chartOfAccountsGroupName	`text`	The name of the chart of accounts group used to categorize the operations of a transaction.
source	`array`	The list of accounts used as source.
destination	`array`	The list of accounts used as destination.
operations	`array`	The list of operations in the transaction.
metadata	`jsonb`	Key-value pairs to add as metadata.
createdAt	`timestamptz`	Timestamp of creation (UTC).
updatedAt	`timestamptz`	Timestamp of last update (UTC).
deletedAt	`timestamptz`	Timestamp of soft deletion, if applicable (UTC).

Deprecated field: This field remains for backward compatibility. Do not use it.

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