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 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.
