Database Modeling & Schema

In this section, you will find important information about the data saved by Midaz.

---

Context

Midaz uses three main database models to save information for each of its domains, which will be better described below.

It's worth noting that, as it's an open-source project, the data topology tends to change over time, and when this occurs, users will be informed so they can decide how to carry out the update in their instances.

For Midaz users in the SaaS model, such updates are transparent, and it's sufficient for the user to use the endpoints (or DSL) of the new version and, instead of using POST /v1/organizations, use POST /v2/organizations.

Relational databases

The Onboarding and Portfolio domains use a relational database to store their data, given that those entities might have their information updated or deleted.

ImmuDB

For the Transactions domain, we rely on ImmuDB to provide us the immutability and resilience needed for the double-entry ledger.

Non-relational databases

To store the content of metadata, we use non-relational databases to provide us with better performance when searching for JSON objects sent by the users. The default NoSQL database used by Midaz is MongoDB, but users can select another one that better fits their needs.

Database modeling

The diagrams below show the relationship between the entities when it comes to their tables inside the databases.

Onboarding domain

Figure 1: Onboarding domain database modeling.

Portfolio domain

Figure 2: Portfolio domain database modeling.

Transactions domain

Figure 3: Transactions domain database modeling.

Data profile

Down below you can find the data profile for each table of Midaz.

Onboarding domain

The tables for the Onboarding domain are shown below.

Organizations table

Column	Type	Example	Allowed values	Note
id	VARCHAR (UUID)	cc15194a-6bc9-4ebb-b15d-43411a54ba4b	N/A	N/A
parent_organization_id	VARCHAR (UUID)	776f2d3d-1383-4bd1-8788-a82a53276736	N/A	N/A
legal_name	TEXT(100)	Sanchez Tech LTDA	N/A	N/A
doing_business_as	TEXT(100)	The ledger.io	N/A	Optional.
legal_document	TEXT(100)	48784548000104		N/A
address	JSON	{ "line1": "Avenida Paulista, 1234" "line2": "CJ 203", "neighborhood": "Jardim Paulista", "zipCode": "04696040" "city": "São Paulo", "state": "SP", "country": "BR" //de acordo com a ISO 3166-1 alpha2 }	N/A	N/A
status	TEXT(100)	BLOCKED	N/A	N/A
status_description	TEXT(100)	Blocked by Gabriel on 2024-04-14	N/A	N/A
created_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A
updated_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A
deleted_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A

Ledgers table

Column	Type	Example	Allowed values	Note
id	VARCHAR (UUID)	77b0fb8b-1bd9-4810-9c6d-7e80064fab0c	N/A	N/A
name	TEXT(100)	Blue Rock Brazil	N/A	N/A
organization_id	VARCHAR(UUID)	cc15194a-6bc9-4ebb-b15d-43411a54ba4b	N/A	N/A
status	TEXT(100)	BLOCKED	N/A	N/A
status_description	TEXT(100)	Blocked by Gabriel on 2024-04-14	N/A	N/A
created_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A
updated_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A
deleted_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A

Portfolio domain

The tables for the Portfolio domain are shown below.

Assets table

Column	Type	Example	Allowed values	Note
id	VARCHAR (UUID)	bb907427-5a8c-4622-b1ce-801b70e40d0c	N/A	N/A
name	TEXT(100)	Bitcoin	N/A	N/A
ledger_id	VARCHAR (UUID)	77b0fb8b-1bd9-4810-9c6d-7e80064fab0c	N/A	N/A
organization_id	VARCHAR (UUID)	cc15194a-6bc9-4ebb-b15d-43411a54ba4b	N/A	N/A
type	ENUM(VARCHAR)	crypto	currency crypto commodity others	N/A
code	TEXT(100)	BTC	If type == currency, you MUST follow ISO 4217.	N/A
status	TEXT(100)	INACTIVE	N/A	N/A
status_description	TEXT(100)	Blocked by Jhon Doe	N/A	N/A
created_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A
updated_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A
deleted_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A

Portfolios table

Column	Type	Example	Allowed values	Note
id	VARCHAR (UUID)	1b8037e8-459d-4bf4-a1f9-0d075b500910	N/A	N/A
name	TEXT(100)	New portfolio	N/A	N/A
entity_id	VARCHAR (UUID)	a8079c89-ce45-4a39-9b8b-6bc95439be1f	N/A	Account holder ID, regardless if this is a company or a person. This will be the ID of the Entities satellite.
ledger_id	VARCHAR (UUID)	77b0fb8b-1bd9-4810-9c6d-7e80064fab0c	N/A	N/A
organization_id	VARCHAR (UUID)	cc15194a-6bc9-4ebb-b15d-43411a54ba4b	N/A	N/A
status	TEXT(100)	INACTIVE	N/A	N/A
status_description	TEXT(100)	Account blocked by BACENJUD, #21213123 on 2024-04-03	N/A	N/A
created_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A
updated_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A
deleted_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A

Accounts table

Column	Type	Example	Allowed values	Note
id	VARCHAR (UUID)	776c4fcd-62dc-4aaa-842c-6bbcbe35ed76	N/A	N/A
name	TEXT(100)	My BRL account	N/A	N/A
type	TEXT(100)	deposit	deposit savings loans marketplace creditCard external	N/A
portfolio_id	VARCHAR (UUID)	298c1e79-00b8-4f4b-a045-dd7300213b7a	N/A	N/A
asset_code	TEXT(10)	BRL	N/A	Asset's code.
available_balance	INTEGER	87	N/A	N/A
on_hold_balance	INTEGER	13	N/A	N/A
balance_scale	INTEGER	2	N/A	N/A
status	TEXT(100)	INACTIVE	N/A	N/A
status_description	TEXT(100)	Account blocked by BACENJUD, #21213123 on 2024-04-03	N/A	N/A
allow_sending	BOOLEAN	true	true false	Allows sending transactions.
allow_receiving	BOOLEAN	true	true false	Allows receiving transactions.
ledger_id	VARCHAR (UUID)	77b0fb8b-1bd9-4810-9c6d-7e80064fab0c	N/A	N/A
organization_id	VARCHAR (UUID)	cc15194a-6bc9-4ebb-b15d-43411a54ba4b	N/A	N/A
alias	TEXT(30)	gabriel	N/A	N/A
entity_id	VARCHAR (UUID)	a8079c89-ce45-4a39-9b8b-6bc95439be1f	N/A	N/A
parent_account_id	VARCHAR (UUID)	11bd8018-e2a6-4e4b-85cd-08b15f4b1501	N/A	If it is a parent account, it is null. If it is a child account, here we have the id of the parent account.
product_id	VARCHAR (UUID)	aca9abcc-4dcd-454c-8ec1-b22df0e88795	N/A	Product ID, used to cluster customer accounts.
created_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A
updated_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A
deleted_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A

Products table

Column	Type	Example	Allowed values	Note
id	VARCHAR (UUID)	9099ce3f-32bd-482f-9617-16c7809681aa	N/A	N/A
name	TEXT(100)	Vip	N/A	N/A
status	TEXT(100)	ACTIVE	ACTIVE BLOCKED CLOSED	N/A
status_description	TEXT(100)	Activated during the creation	N/A	N/A
ledger_id	VARCHAR (UUID)	77b0fb8b-1bd9-4810-9c6d-7e80064fab0c	N/A	N/A
organization_id	VARCHAR (UUID)	cc15194a-6bc9-4ebb-b15d-43411a54ba4b	N/A	N/A
created_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A
updated_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A
deleted_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A

Transactions domain

The tables for the Transactions domain are shown below.

Transactions table

Column	Type	Example	Allowed values	Note
id	VARCHAR (UUID)	bb907427-5a8c-4622-b1ce-801b70e40d0c	N/A	N/A
parent_transaction_id	VARCHAR (UUID)	4431ef46-6ac7-45e9-9a03-8f35d41e9136	N/A	N/A
description	TEXT(100)	4431ef46-6ac7-45e9-9a03-8f35d41e9136	N/A	N/A
ledger_id	VARCHAR (UUID)	77b0fb8b-1bd9-4810-9c6d-7e80064fab0c	N/A	N/A
organization_id	VARCHAR (UUID)	cc15194a-6bc9-4ebb-b15d-43411a54ba4b	N/A	N/A
template	TEXT(100)	PIX_OUT	N/A	Shown as null if this was a manual transaction.
asset_code	TEXT(10)	BTC	N/A	N/A
status	TEXT(100)	ACTIVE		N/A
amount	INTEGER	300	N/A	N/A
amount_scale	INTEGER	1	N/A	N/A
chart_of_accounts_group_name	TEXT(100)	PIX_OUT_USING_NEOBANK_92839	N/A	N/A
created_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A
updated_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A

Operations table

Column	Type	Example	Allowed values	Note
id	VARCHAR (UUID)	bb907427-5a8c-4622-b1ce-801b70e40d0c	N/A	N/A
transaction_id	VARCHAR (UUID)	4431ef46-6ac7-45e9-9a03-8f35d41e9136	N/A	N/A
description	TEXT(100)	Testing the transaction	N/A	N/A
ledger_id	VARCHAR (UUID)	77b0fb8b-1bd9-4810-9c6d-7e80064fab0c	N/A	N/A
type	VARCHAR	DEBIT	DEBIT CREDIT	N/A
asset_code	VARCHAR	BTC	N/A	N/A
amount	INTEGER	300	N/A	N/A
amount_scale	INTEGER	1	N/A	N/A
available_balance	INTEGER	300	N/A	N/A
on_hold_balance	INTEGER	1	N/A	N/A
balance_scale	INTEGER	1	N/A	N/A
available_balance_after	INTEGER	300	N/A	N/A
on_hold_balance_after	INTEGER	1	N/A	N/A
balance_scale_after	INTEGER	1	N/A	N/A
account_id	VARCHAR (UUID)	4431ef46-6ac7-45e9-9a03-8f35d41e9136	N/A	N/A
account_alias	TEXT(30)	@gabriel	N/A	N/A
portfolio_id	VARCHAR (UUID)	98d7164f-4f6a-4811-a1cd-273750a9edde	N/A	N/A
chart_of_accounts	TEXT(100)	PIX_DEBIT_FEE_SOURCE	N/A	N/A
organization_id	VARCHAR(UUID)	cc15194a-6bc9-4ebb-b15d-43411a54ba4b	N/A	N/A
created_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A
updated_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A

Assets rates table

Column	Type	Example	Allowed values	Notes
id	VARCHAR (UUID)	e8a623c3-a297-4245-8d82-303b0639212f	N/A	N/A
base_asset_id	VARCHAR (UUID)	4431ef46-6ac7-45e9-9a03-8f35d41e9136	N/A	Asset's ID in the assets table.
counter_asset_id	VARCHAR (UUID)	f3b158a7-0695-4727-a84a-dc665076e1b2	N/A	Asset's ID in the assets table.
amount	INTEGER	6900000	N/A	N/A
scale	INTEGER	2	N/A	N/A
source	TEXT(100)	Chainlink database	N/A	N/A
status	TEXT(100)	ACTIVE	ACTIVE INACTIVE	N/A
status_description	TEXT(100)	Updated using the fallback	N/A	N/A
organization_id	VARCHAR(UUID)	cc15194a-6bc9-4ebb-b15d-43411a54ba4b	N/A	N/A
ledger_id	VARCHAR(UUID)	77b0fb8b-1bd9-4810-9c6d-7e80064fab0c	N/A	N/A
created_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A
updated_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A
deleted_at	DATETIME	2024-02-08T16:59:31-0300	N/A	N/A

Data migration

• data migration and backward compatibility