# Database Schema

Complete reference of all PostgreSQL schemas, tables, and key relationships in FluxiQ PIX.

## Prerequisites

- PostgreSQL 16 administration knowledge
- Understanding of UUID primary keys
- Familiarity with [Database configuration](../configuration/database.md)

## Schema Overview

| Schema | Purpose | Key Tables |
|--------|---------|------------|
| `monetarie_auth` | Authentication and authorization | users, sessions, institutions, groups, group_features, user_groups, mfa_configurations |
| `monetarie_dict` | DICT directory | dict_keys, claims, infraction_reports, cid_sync_events |
| `monetarie_spi` | SPI transactions | messages, payments, accounts, balances, message_history, balance_history |
| `monetarie_spi_ref` | Reference data | banks, status_codes, reason_codes, message_types, error_codes |
| `monetarie_spi_msg` | Crypto/XML | crypto_keys, private_keys, xml_messages |
| `monetarie_audit` | Audit trail | activity_log (partitioned), bacen_api_validations, xml_archive |
| `monetarie_settlement` | Settlement/GL | netting, reconciliation, qr_codes, chart_of_accounts, journal_entries, accounting_events, cost_centers |
| `bacen_simulator` | Simulator | simulator_config, scenarios, test_runs, exchanges |

## Key Tables

### monetarie_auth.users

| Column | Type | Description |
|--------|------|-------------|
| `id` | UUID | Primary key |
| `username` | VARCHAR | Login username |
| `password_hash` | VARCHAR | Bcrypt hash |
| `email` | VARCHAR | Email address |
| `role` | VARCHAR | User role |
| `is_active` | BOOLEAN | Account active |
| `is_blocked` | BOOLEAN | Account locked (10 failed logins) |
| `mfa_enabled` | BOOLEAN | MFA active |
| `mfa_secret` | VARCHAR | TOTP secret (encrypted) |
| `password_expires` | TIMESTAMP | Password expiration date |
| `inserted_at` | TIMESTAMP | Created at |
| `updated_at` | TIMESTAMP | Updated at |

### monetarie_spi.messages (Primary Transaction Table)

| Column | Type | Description |
|--------|------|-------------|
| `id` | UUID | Primary key |
| `end_to_end_id` | CHAR(32) | BCB End-to-End identifier |
| `message_type` | VARCHAR | ISO 20022 type (pacs.008, etc.) |
| `message_direction` | ENUM | OUTBOUND or INBOUND |
| `status_id` | INTEGER | FK to status_codes (1=PDNG...10=RTRN) |
| `amount` | BIGINT | Amount in centavos |
| `debtor_ispb` | CHAR(8) | Debtor institution ISPB |
| `creditor_ispb` | CHAR(8) | Creditor institution ISPB |
| `operation_time` | TIMESTAMP | Operation start time |
| `acceptance_time` | TIMESTAMP | ACCC timestamp |
| `settlement_time` | TIMESTAMP | STLD timestamp |
| `json_input` | JSONB | Original request payload |
| `inserted_at` | TIMESTAMP | Created at |

::: warning TWO TRANSACTION SCHEMAS
`Shared.Schemas.Spi.Transaction` maps to `monetarie_spi.messages` (production data). `SpiService.Transactions.Transaction` maps to `monetarie_spi.transactions` (different table, mostly empty). Always use the Shared schema for production queries.
:::

### monetarie_dict.dict_keys

| Column | Type | Description |
|--------|------|-------------|
| `id` | UUID | Primary key |
| `key_type` | ENUM | CPF, CNPJ, PHONE, EMAIL, EVP |
| `key_value` | VARCHAR | The PIX key value |
| `participant_ispb` | CHAR(8) | Owning institution ISPB |
| `account_number` | VARCHAR | Bank account number |
| `branch_number` | VARCHAR | Branch code |
| `holder_name` | VARCHAR | Account holder name |
| `holder_cpf_cnpj` | VARCHAR | Holder CPF or CNPJ |
| `created_at` | TIMESTAMP | Registration date |

### monetarie_settlement.chart_of_accounts

| Column | Type | Description |
|--------|------|-------------|
| `id` | UUID | Primary key |
| `code` | VARCHAR | COSIF account code |
| `name` | VARCHAR | Account name |
| `type` | VARCHAR | ASSET, LIABILITY, EQUITY, REVENUE, EXPENSE |
| `parent_id` | UUID | Parent account FK |
| `is_active` | BOOLEAN | Active status |

## ENUMs

```sql
CREATE TYPE monetarie_dict.key_type AS ENUM ('CPF', 'CNPJ', 'PHONE', 'EMAIL', 'EVP');
CREATE TYPE monetarie_spi.debit_credit AS ENUM ('DEBIT', 'CREDIT');
CREATE TYPE monetarie_spi.message_direction AS ENUM ('OUTBOUND', 'INBOUND');
```

## Key Indexes

| Table | Columns | Type | Purpose |
|-------|---------|------|---------|
| messages | `(end_to_end_id::text)` | GIN trigram | Fuzzy E2E search |
| messages | `status_id` | B-tree | Status filtering |
| messages | `branch_id, status_id` | Composite | Branch dashboard |
| messages | `inserted_at` | B-tree | Date range queries |
| payments | `debtor_cpf_cnpj` | B-tree | CPF/CNPJ lookup |
| dict_keys | `key_type, key_value` | Composite | Key lookup |
| message_history | `message_id, inserted_at` | Composite | History queries |
| balance_history | `account_id, recorded_at` | Composite | Balance history |

## Partitioned Tables

| Table | Partition Key | Strategy | Retention |
|-------|--------------|----------|-----------|
| `monetarie_audit.activity_log` | `created_at` | Monthly RANGE | PartitionManager |
| `monetarie_auth.login_history` | `login_at` | Monthly RANGE | PartitionManager |

Partitions: 2026-01 through 2026-04 + DEFAULT partition.

## Status ID Mapping

| ID | Code | Description |
|----|------|-------------|
| 1 | PDNG | Pending |
| 2 | ACSP | Accepted for Processing |
| 3 | ACCC | Accepted |
| 4 | ACSC | Accepted Settlement Completed |
| 5 | ACTC | Accepted Technical |
| 6 | ACWC | Accepted with Change |
| 7 | STLD | Settled |
| 8 | RJCT | Rejected |
| 9 | CANC | Cancelled |
| 10 | RTRN | Returned |

## Expected Outcome

After reviewing this schema reference:

- Complete understanding of the 8 database schemas
- Knowledge of key tables, columns, and relationships
- Understanding of indexes for query optimization
- Awareness of the two Transaction schema mapping