Data model

Reference the core tables and schemas that power Midaz, designed for developers and database administrators working close to the data.

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, 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.
type	text	The type of operation (debit/credit).
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.

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.