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). |
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). |
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.
Figure 1. Midaz tables and their relations.