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

Portfolio domain

Transactions domain

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

ColumnTypeExampleAllowed valuesNote

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

ColumnTypeExampleAllowed valuesNote

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.

Instruments table

ColumnTypeExampleAllowed valuesNote

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

ColumnTypeExampleAllowed valuesNote

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

ColumnTypeExampleAllowed valuesNote

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

instrument_code

TEXT(10)

BRL

N/A

Instrument'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

ColumnTypeExampleAllowed valuesNote

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

ColumnTypeExampleAllowed valuesNote

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.

instrument_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

ColumnTypeExampleAllowed valuesNote

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

instrument_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

Instruments rates table

ColumnTypeExampleAllowed valuesNotes

id

VARCHAR (UUID)

e8a623c3-a297-4245-8d82-303b0639212f

N/A

N/A

base_instrument_id

VARCHAR (UUID)

4431ef46-6ac7-45e9-9a03-8f35d41e9136

N/A

Instrument's ID in the instruments table.

counter_instrument_id

VARCHAR (UUID)

f3b158a7-0695-4727-a84a-dc665076e1b2

N/A

Instrument's ID in the instruments 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

PreviousRequests and ResponsesNextErrors list

Last updated