# Schema do Banco de Dados

Referencia completa do schema PostgreSQL da plataforma FluxiQ PIX, incluindo diagramas de entidade-relacionamento, catalogo de tabelas, particoes e indexes.

## Pre-requisitos

- Acesso psql ao banco de dados PostgreSQL
- Conhecimento basico de schemas PostgreSQL e particionamento
- Entendimento dos conceitos PIX (DICT, SPI, MED 2.0)

## Visao Geral dos Schemas

O banco `monetarie` possui 8 schemas com funcoes distintas:

```mermaid
erDiagram
    monetarie_auth ||--|{ monetarie_spi : "usuarios operam"
    monetarie_auth ||--|{ monetarie_dict : "usuarios gerenciam"
    monetarie_dict ||--|{ monetarie_spi : "chaves referenciam"
    monetarie_spi ||--|{ monetarie_settlement : "transacoes liquidam"
    monetarie_spi ||--|{ monetarie_spi_msg : "mensagens XML"
    monetarie_spi ||--|{ monetarie_audit : "auditoria"
    monetarie_spi_ref ||--|{ monetarie_spi : "dados de referencia"
    bacen_simulator ||--|{ monetarie_spi : "simulacao"
```

| Schema | Tabelas | Proposito |
|--------|---------|-----------|
| `monetarie_auth` | 9 | Usuarios, grupos, MFA, sessoes, institucoes |
| `monetarie_dict` | 5 | Chaves PIX, claims, infracoes, CID sync |
| `monetarie_spi` | 7 | Transacoes, pagamentos, historico, saldos, contas |
| `monetarie_spi_ref` | 6 | Dados de referencia (bancos, status, mensagens) |
| `monetarie_spi_msg` | 3 | Chaves criptograficas, mensagens XML |
| `monetarie_audit` | 2 | Auditoria XML, validacoes BACEN |
| `monetarie_settlement` | 8 | Liquidacao, contabilidade, QR codes |
| `bacen_simulator` | 24 | Simulador BACEN (testes) |

## monetarie_auth

Autenticacao, autorizacao e gestao de usuarios.

```mermaid
erDiagram
    users {
        uuid id PK
        varchar username
        varchar email
        varchar password_hash
        varchar role
        boolean is_active
        boolean is_blocked
        integer failed_login_attempts
        timestamp password_expires
        timestamp last_login_at
        timestamp created_at
    }

    groups {
        uuid id PK
        varchar name
        varchar description
        boolean is_active
        timestamp created_at
    }

    user_groups {
        uuid id PK
        uuid user_id FK
        uuid group_id FK
        boolean is_primary
        jsonb metadata
    }

    group_features {
        uuid id PK
        uuid group_id FK
        varchar feature_name
        boolean can_read
        boolean can_write
    }

    institutions {
        uuid id PK
        varchar name
        char8 ispb
        varchar cnpj
        boolean is_active
    }

    mfa_configurations {
        uuid id PK
        uuid user_id FK
        varchar secret
        varchar backup_codes
        boolean is_enabled
    }

    mfa_events {
        uuid id PK
        uuid user_id FK
        varchar event_type
        varchar ip_address
        timestamp created_at
    }

    activity_log {
        uuid id PK
        uuid user_id FK
        varchar action
        varchar entity_type
        uuid entity_id
        jsonb details
        timestamp created_at
    }

    login_history {
        uuid id PK
        uuid user_id FK
        varchar ip_address
        varchar user_agent
        boolean success
        timestamp login_at
    }

    users ||--|{ user_groups : "pertence a"
    groups ||--|{ user_groups : "contem"
    groups ||--|{ group_features : "tem permissoes"
    users ||--|{ mfa_configurations : "configura MFA"
    users ||--|{ mfa_events : "gera eventos MFA"
    users ||--|{ activity_log : "registra atividade"
    users ||--|{ login_history : "registra logins"
```

::: info Tabelas particionadas
`activity_log` e `login_history` sao tabelas particionadas por range mensal na coluna de data. O `PartitionManager` cria particoes automaticamente para os proximos 60 dias.
:::

## monetarie_dict

Diretorio de chaves PIX, reivindicacoes e infracoes MED 2.0.

```mermaid
erDiagram
    keys {
        uuid id PK
        enum key_type "CPF/CNPJ/PHONE/EMAIL/EVP"
        varchar key_value
        varchar owner_name
        char8 owner_ispb
        varchar owner_cpf_cnpj
        varchar account_branch
        varchar account_number
        varchar account_type
        timestamp created_at
        timestamp updated_at
    }

    claims {
        uuid id PK
        uuid key_id FK
        varchar claim_type
        varchar status
        char8 claimer_ispb
        char8 donor_ispb
        timestamp created_at
        timestamp completed_at
    }

    infraction_reports {
        uuid id PK
        varchar status
        varchar infraction_type
        char8 reported_by_ispb
        char8 reported_ispb
        varchar end_to_end_id
        varchar reported_cpf_cnpj
        varchar analysis_result
        varchar analysis_details
        varchar close_reason
        timestamp created_at
        timestamp closed_at
    }

    cid_sync {
        uuid id PK
        varchar sync_type
        varchar status
        integer records_processed
        timestamp started_at
        timestamp completed_at
    }

    operations {
        uuid id PK
        varchar operation_type
        varchar key_type
        varchar key_value
        varchar status
        char8 ispb
        timestamp created_at
    }

    keys ||--|{ claims : "reivindicada"
    keys ||--|{ operations : "operacao sobre"
```

ENUMs:
- `key_type`: CPF, CNPJ, PHONE, EMAIL, EVP

## monetarie_spi

Transacoes de pagamento instantaneo, pagamentos e historico.

```mermaid
erDiagram
    messages {
        uuid id PK
        varchar end_to_end_id
        varchar message_id
        varchar message_type "pacs.008/002/004/028"
        integer status_id FK
        enum direction "OUTBOUND/INBOUND"
        varchar debtor_cpf_cnpj
        varchar creditor_cpf_cnpj
        char8 debtor_ispb
        char8 creditor_ispb
        varchar branch_id
        varchar instrument_type
        jsonb json_input
        varchar return_id
        varchar original_end_to_end_id
        timestamp operation_time
        timestamp acceptance_time
        timestamp completion_time
        timestamp settlement_time
        timestamp created_at
    }

    payments {
        uuid id PK
        uuid message_id FK
        bigint amount "centavos"
        varchar currency "BRL"
        varchar debtor_name
        varchar creditor_name
        varchar creditor_proxy
        varchar ustrd "remittance info"
        timestamp created_at
    }

    message_history {
        uuid id PK
        uuid message_id FK
        integer old_status_id
        integer new_status_id
        varchar reason
        timestamp created_at
    }

    balance_history {
        uuid id PK
        uuid account_id FK
        bigint balance "centavos"
        enum debit_credit "DEBIT/CREDIT"
        bigint amount "centavos"
        timestamp created_at
    }

    accounts {
        uuid id PK
        char8 ispb
        varchar branch
        varchar number
        varchar account_type
        varchar holder_name
        bigint balance "centavos"
        boolean is_active
    }

    alcada_rules {
        uuid id PK
        varchar rule_name
        bigint min_amount
        bigint max_amount
        integer required_approvals
    }

    alcada_approvals {
        uuid id PK
        uuid rule_id FK
        uuid transaction_id FK
        uuid approver_id FK
        varchar status
        timestamp created_at
    }

    messages ||--|{ payments : "detalhe"
    messages ||--|{ message_history : "historico"
    accounts ||--|{ balance_history : "movimentacao"
    alcada_rules ||--|{ alcada_approvals : "aprovacoes"
```

ENUMs:
- `debit_credit`: DEBIT, CREDIT
- `message_direction`: OUTBOUND, INBOUND

Status IDs: 1=PDNG, 2=ACSP, 3=ACCC, 4=ACSC, 5=ACTC, 6=ACWC, 7=STLD, 8=RJCT, 9=CANC, 10=RTRN

::: warning Dois schemas de Transaction
`Shared.Schemas.Spi.Transaction` mapeia para `monetarie_spi.messages` (dados reais).
`SpiService.Transactions.Transaction` mapeia para `monetarie_spi.transactions` (tabela diferente, geralmente vazia).
Sempre use o schema Shared para queries de producao.
:::

## monetarie_spi_ref

Dados de referencia imutaveis (bancos, codigos de status, tipos de mensagem).

| Tabela | Registros | Descricao |
|--------|-----------|-----------|
| `banks` | 369 + 98 | Diretorio de bancos (ISPB, nome, codigo) |
| `status_codes` | 15 | Codigos de status de transacao |
| `reason_codes` | 190 | Codigos de motivo (rejeicao, devolucao) |
| `message_types` | 26 | Tipos de mensagem ISO 20022 |
| `xsd_schemas` | 27 | Schemas XSD para validacao |
| `error_codes` | 161 | Codigos de erro BACEN |

## monetarie_spi_msg

Chaves criptograficas e mensagens XML.

| Tabela | Descricao |
|--------|-----------|
| `crypto_keys` | Chaves publicas para verificacao de assinatura |
| `private_keys` | Chaves privadas para assinatura XMLDSig |
| `xml_messages` | Mensagens XML ISO 20022 arquivadas |

## monetarie_audit

Auditoria de conformidade BACEN.

| Tabela | Retencao | Descricao |
|--------|----------|-----------|
| `xml_audit_logs` | 10 anos (ICOM), 2 anos (DICT reads) | XML arquivado com hash SHA-256 |
| `bacen_api_validations` | 2 anos | Resultados de validacao XSD |

## monetarie_settlement

Liquidacao, reconciliacao e contabilidade.

```mermaid
erDiagram
    chart_of_accounts {
        uuid id PK
        varchar code "COSIF"
        varchar name
        varchar account_type "ATIVO/PASSIVO/RECEITA/DESPESA"
        boolean is_active
    }

    journal_entries {
        uuid id PK
        uuid account_id FK
        uuid event_id FK
        bigint debit_amount "centavos"
        bigint credit_amount "centavos"
        bigint running_balance "centavos"
        varchar description
        timestamp created_at
    }

    accounting_events {
        uuid id PK
        varchar event_type
        varchar description
        bigint amount "centavos"
        varchar reference_id
        timestamp created_at
    }

    cost_centers {
        uuid id PK
        varchar code
        varchar name
        varchar description
        boolean is_active
    }

    netting {
        uuid id PK
        varchar cycle_id
        char8 participant_ispb
        bigint debit_total
        bigint credit_total
        bigint net_amount
        varchar status
        timestamp settled_at
    }

    reconciliation {
        uuid id PK
        varchar run_id
        varchar status
        integer total_records
        integer matched
        integer discrepancies
        timestamp started_at
        timestamp completed_at
    }

    fees {
        uuid id PK
        char8 participant_ispb
        varchar fee_type
        bigint amount
        varchar period
        timestamp created_at
    }

    qr_codes {
        uuid id PK
        varchar payload
        varchar key_value
        bigint amount
        varchar merchant_name
        timestamp created_at
        timestamp expires_at
    }

    chart_of_accounts ||--|{ journal_entries : "lancamentos"
    accounting_events ||--|{ journal_entries : "gera"
```

Dados COSIF seedados: 44 contas (BCB Circular 4010) + 6 centros de custo.

## bacen_simulator

24 tabelas para o simulador BACEN integrado. Ativo apenas quando `SIMULATOR_ENABLED=true`.

## Estrategia de Particionamento

### Tabelas Particionadas

| Tabela | Tipo | Coluna | Particoes |
|--------|------|--------|-----------|
| `monetarie_auth.activity_log` | Range mensal | `created_at` | 2026-01 ate 2026-04 + DEFAULT |
| `monetarie_auth.login_history` | Range mensal | `login_at` | 2026-01 ate 2026-04 + DEFAULT |

O `PartitionManager` GenServer executa diariamente para criar particoes para os proximos 60 dias e purgar registros expirados.

### Verificar Particoes

```bash
psql -h 10.140.241.2 -U postgres -d monetarie -c "
  SELECT parent.relname, child.relname, pg_get_expr(child.relpartbound, child.oid)
  FROM pg_inherits
  JOIN pg_class parent ON parent.oid = inhparent
  JOIN pg_class child ON child.oid = inhrelid
  WHERE parent.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'monetarie_auth')
  ORDER BY parent.relname, child.relname;
"
```

## Catalogo de Indexes

### Indexes TPS (Migration 23)

| Index | Tabela | Colunas | Tipo | Proposito |
|-------|--------|---------|------|-----------|
| idx_messages_branch_id | messages | branch_id | B-tree | Filtro por agencia |
| idx_messages_branch_status | messages | branch_id, status_id | B-tree composto | Filtro agencia + status |
| idx_messages_cpf_cnpj | messages | debtor_cpf_cnpj, creditor_cpf_cnpj | B-tree | Busca por CPF/CNPJ |
| idx_messages_e2e_id_trgm | messages | (end_to_end_id::text) | GIN trigram | Busca parcial E2E ID |
| idx_message_history_msg_id | message_history | message_id | B-tree | Join com messages |
| idx_balance_history_account | balance_history | account_id, created_at | B-tree composto | Historico de saldo |
| idx_dict_keys_value | keys | key_value | B-tree | Busca por chave PIX |
| idx_dict_keys_owner | keys | owner_ispb, owner_cpf_cnpj | B-tree composto | Busca por titular |

::: warning GIN trigram em CHAR
O operador `gin_trgm_ops` nao aceita colunas do tipo CHAR diretamente. O index usa cast explito: `(end_to_end_id::text) gin_trgm_ops`. Isso requer a extensao `pg_trgm`.
:::

### Indexes Padrao

Todas as tabelas possuem:
- Primary Key (UUID) com index B-tree automatico
- Foreign Key indexes criados implicitamente pelo Ecto
- `created_at` indexes em tabelas com consultas temporais frequentes

### Verificar Uso de Indexes

```bash
psql -h 10.140.241.2 -U postgres -d monetarie -c "
  SELECT
    schemaname || '.' || indexrelname AS index_name,
    idx_scan AS scans,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
  FROM pg_stat_user_indexes
  WHERE schemaname LIKE 'monetarie_%'
  ORDER BY idx_scan DESC
  LIMIT 20;
"
```

## Extensoes PostgreSQL

```bash
psql -h 10.140.241.2 -U postgres -d monetarie -c "SELECT extname, extversion FROM pg_extension;"

# Extensoes necessarias:
# uuid-ossp     — Geracao de UUIDs
# pg_trgm       — GIN trigram indexes (busca parcial)
# pgcrypto      — Funcoes criptograficas
```

## Tamanho do Banco

```bash
# Tamanho total
psql -h 10.140.241.2 -U postgres -d monetarie -c "
  SELECT pg_size_pretty(pg_database_size('monetarie'));
"

# Tamanho por schema
psql -h 10.140.241.2 -U postgres -d monetarie -c "
  SELECT
    schemaname,
    pg_size_pretty(SUM(pg_total_relation_size(schemaname || '.' || tablename))) AS total_size,
    COUNT(*) AS tables
  FROM pg_tables
  WHERE schemaname LIKE 'monetarie_%' OR schemaname = 'bacen_simulator'
  GROUP BY schemaname
  ORDER BY SUM(pg_total_relation_size(schemaname || '.' || tablename)) DESC;
"
```

## Resultado Esperado

Ao utilizar esta referencia de schema, voce tera:

- Visao completa dos 8 schemas e suas ~64 tabelas
- Diagramas ER para os schemas principais (auth, dict, spi, settlement)
- Entendimento da estrategia de particionamento (mensal por data)
- Catalogo dos 8 indexes TPS criticos e seus propositos
- Comandos para verificar tamanho, uso de indexes e particoes
- Conhecimento das extensoes PostgreSQL necessarias