Data model

This page is intended for developers and database administrators. It contains detailed database schemas and table structures. If you’re looking for a business-level overview of Midaz’s entities and how they relate to each other, see About Midaz.

Explore the core tables and schemas that power Midaz. This reference helps developers and database administrators understand how data is structured and connected across the system. As Midaz evolves, new fields and tables may be added.

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 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 Transaction Route.
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`	[Deprecated] The name of the Chart-of-Accounts that the operation belongs to.
route	`text`	The chart of accounts group name that categorizes the operation under a specific group.
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 is still present for backward compatibility but should no longer be used.

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`	The chart of accounts group name that categorizes the operation under a specific group.
status	`jsonb`	Information about the status.
amount	`text`	The sent amount.
assetCode	`text`	The code used to refer to the Asset.
chartOfAccountsGroupName	`text`	[Deprecated] The name of the group used to categorize the operations of a transaction under the same group.
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 is still present for backward compatibility but should no longer be used.

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.

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