# Banco de Dados

Configuracao e administracao do PostgreSQL 16 para o Monetarie PIX, incluindo schemas, migracoes, seeds, particionamento e tuning de performance.

## Pre-requisitos

- PostgreSQL 16 instalado e acessivel
- Extensoes habilitadas: `pgcrypto`, `uuid-ossp`, `pg_trgm`
- Usuario com permissao `CREATEDB` (para `mix ecto.setup`) ou acesso ao banco `monetarie`
- Elixir 1.17 e Mix instalados (para migracoes)

## Arquitetura de Schemas

O Monetarie PIX organiza suas tabelas em 8 schemas PostgreSQL, cada um com um dominio funcional especifico:

```mermaid
erDiagram
    monetarie_auth ||--o{ monetarie_spi : "usuarios operam"
    monetarie_auth ||--o{ monetarie_dict : "usuarios gerenciam"
    monetarie_auth ||--o{ monetarie_audit : "acoes registradas"
    monetarie_spi ||--o{ monetarie_spi_ref : "referencia"
    monetarie_spi ||--o{ monetarie_spi_msg : "mensagens XML"
    monetarie_spi ||--o{ monetarie_settlement : "liquidacao"
    monetarie_dict ||--o{ monetarie_spi : "chaves vinculadas"
    bacen_simulator ||..o{ monetarie_spi : "simula"

    monetarie_auth {
        uuid users PK
        uuid institutions PK
        uuid groups PK
        uuid user_groups PK
        uuid audit_logs PK
        uuid sessions PK
        uuid mfa_configurations PK
        uuid mfa_events PK
        uuid activity_log PK
        uuid login_history PK
    }

    monetarie_dict {
        uuid pix_keys PK
        uuid claims PK
        uuid infraction_reports PK
        uuid cid_sync PK
        uuid dict_operations PK
        uuid med_notifications PK
    }

    monetarie_spi {
        uuid messages PK
        uuid payments PK
        uuid accounts PK
        uuid balances PK
        uuid message_history PK
        uuid balance_history PK
        enum debit_credit
        enum message_direction
    }

    monetarie_spi_ref {
        integer banks PK
        integer status_codes PK
        integer reason_codes PK
        integer message_types PK
        integer xsd_schemas PK
        integer error_codes PK
    }

    monetarie_spi_msg {
        uuid crypto_keys PK
        uuid private_keys PK
        uuid xml_messages PK
    }

    monetarie_audit {
        uuid xml_archives PK
        uuid bacen_api_validations PK
    }

    monetarie_settlement {
        uuid netting PK
        uuid reconciliation PK
        uuid fees PK
        uuid qr_codes PK
        uuid chart_of_accounts PK
        uuid journal_entries PK
        uuid accounting_events PK
        uuid cost_centers PK
    }

    bacen_simulator {
        uuid config PK
        uuid scenarios PK
        uuid test_runs PK
        uuid exchanges PK
    }
```

### Descricao dos Schemas

| Schema | Proposito | Tabelas Principais |
|--------|-----------|-------------------|
| `monetarie_auth` | Autenticacao, usuarios, audit logs, sessoes, MFA, instituicoes, grupos | ~10 tabelas |
| `monetarie_dict` | Chaves PIX, claims, MED 2.0, CID sync, operacoes DICT | ~6 tabelas |
| `monetarie_spi` | Transacoes, pagamentos, saldos, contas, historico, alcada | ~8 tabelas |
| `monetarie_spi_ref` | Dados de referencia: bancos, codigos de status, tipos de mensagem, XSD | ~6 tabelas |
| `monetarie_spi_msg` | Chaves criptograficas, mensagens XML | ~3 tabelas |
| `monetarie_audit` | Arquivo XML, validacoes BACEN API | ~2 tabelas |
| `monetarie_settlement` | Netting, reconciliacao, taxas, QR codes, contabilidade COSIF | ~8 tabelas |
| `bacen_simulator` | Configuracao e cenarios do simulador BACEN | ~4 tabelas |

### ENUMs do Banco

```sql
-- Tipo de chave PIX
CREATE TYPE monetarie_dict.key_type AS ENUM ('CPF', 'CNPJ', 'PHONE', 'EMAIL', 'EVP');

-- Debito ou Credito
CREATE TYPE monetarie_spi.debit_credit AS ENUM ('DEBIT', 'CREDIT');

-- Direcao da mensagem
CREATE TYPE monetarie_spi.message_direction AS ENUM ('OUTBOUND', 'INBOUND');
```

## Configuracao Inicial

### Criacao do Banco (Desenvolvimento)

```bash
cd /caminho/para/pix/backend

# Criar banco, executar migracoes e popular dados
mix ecto.setup

# Ou passo a passo:
mix ecto.create          # Cria o banco 'monetarie'
mix ecto.migrate         # Executa as 25 migracoes
mix run apps/shared/priv/repo/seeds.exs  # Popula dados iniciais
```

### Criacao do Banco (Producao/Kubernetes)

```bash
# Executar migracoes via release
kubectl exec -n pix deployment/pix-backend -- \
  bin/monetarie_pix eval "Shared.Release.migrate()"

# Executar seeds (somente primeira instalacao)
kubectl exec -n pix deployment/pix-backend -- \
  bin/monetarie_pix eval "Shared.Release.seed()"
```

::: warning EVAL vs RPC
Use `eval` para migracoes em releases. O comando `rpc` conecta a uma instancia ja em execucao. Se o endpoint ja esta rodando, use `rpc` para seeds: `bin/monetarie_pix rpc "Shared.Release.seed()"`.
:::

## Pool de Conexoes

O Monetarie PIX utiliza 4 repositorios Ecto (um por app umbrella), cada um com seu proprio pool de conexoes:

```mermaid
flowchart TB
    subgraph Pod1["Pod 1"]
        SR1["Shared.Repo<br/>POOL_SIZE conexoes"]
        DR1["DictService.Repo<br/>POOL_SIZE conexoes"]
        SP1["SpiService.Repo<br/>POOL_SIZE conexoes"]
        SE1["SettlementService.Repo<br/>POOL_SIZE conexoes"]
    end
    subgraph Pod2["Pod 2"]
        SR2["Shared.Repo<br/>POOL_SIZE conexoes"]
        DR2["DictService.Repo<br/>POOL_SIZE conexoes"]
        SP2["SpiService.Repo<br/>POOL_SIZE conexoes"]
        SE2["SettlementService.Repo<br/>POOL_SIZE conexoes"]
    end
    PG["PostgreSQL 16<br/>max_connections >= N"]
    SR1 & DR1 & SP1 & SE1 --> PG
    SR2 & DR2 & SP2 & SE2 --> PG
```

### Calculo de Conexoes

```
Total = POOL_SIZE x 4 repos x N pods
```

| POOL_SIZE | Pods | Total Conexoes | max_connections Recomendado |
|-----------|------|---------------|---------------------------|
| 50 | 2 | 400 | 500 |
| 100 | 2 | 800 | 1.000 |
| 200 | 2 | 1.600 | 2.000 |
| 250 | 2 | 2.000 | 2.500 |
| 250 | 4 | 4.000 | 5.000 |

::: tip CONFIGURACAO DO POOL
O `POOL_SIZE` padrao e 250. Para ambientes de desenvolvimento, 10-20 e suficiente. Para producao com 5.000 TPS, 200-250 por repositorio e recomendado.
:::

### Parametros de Pool Avancados

Configurados em `runtime.exs`:

| Parametro | Valor | Descricao |
|-----------|-------|-----------|
| `queue_target` | 50ms | Tempo alvo de espera na fila (agressivo para alto TPS) |
| `queue_interval` | 100ms | Intervalo para calculo do target |
| `timeout` | 15.000ms | Timeout geral de transacao |

## Migracoes

O Monetarie PIX possui 25 migracoes executadas sequencialmente:

| # | Timestamp | Descricao |
|---|-----------|-----------|
| 1 | `20260201000001` | Schemas iniciais e todas as tabelas base |
| 2 | `20260203000001` | 13 tabelas de resolucao de gaps |
| 3 | `20260203000002` | Seed de dados de referencia |
| 4 | `20260203100001` | Extensoes PostgreSQL e ENUMs |
| 5 | `20260203100002` | Schemas faltantes + tabelas auth |
| 6 | `20260203100003` | Tabelas DICT faltantes |
| 7 | `20260203100004` | Tabelas SPI faltantes |
| 8 | `20260203100005` | Tabelas settlement faltantes |
| 9 | `20260203100006` | Tabelas de novos schemas |
| 10 | `20260203100007` | Funcoes DB, triggers, views |
| 11 | `20260203100008` | Tabelas do simulador BACEN |
| 12 | `20260203100009` | Carregar SQL de seed restante |
| 13 | `20260206000001` | Instituicoes e grupos auth |
| 14 | `20260206200001` | Tabelas de conformidade BACEN |
| 15 | `20260206200002` | ALTER domain_values |
| 16 | `20260206200003` | Alcada (3 tabelas de autorizacao) |
| 17 | `20260207300001` | Campos MFA (users + mfa_configurations + mfa_events) |
| 18 | `20260207400001` | Contabilidade GL (4 tabelas + 44 COSIF + 6 centros de custo) |
| 19 | `20260207500001` | Tabela system_config + 13 parametros seed |
| 20 | `20260208000001` | Indices de performance (E2E ID, status, datas) |
| 21 | `20260208100001` | Conta ISPB + holder_name, user_groups junction |
| 22 | `20260209000001` | Recriar infraction_reports (UUID PK, campos MED 2.0, 22 reports seed) |
| 23 | `20260209100001` | Indices TPS (branch_id, CPF/CNPJ, GIN trigram, compostos) |
| 24 | `20260209200001` | Fix audit: reported_cpf_cnpj + campos user_groups |
| 25 | `20260210000001` | Fix tabelas particionadas (activity_log + login_history: particoes mensais) |

### Verificar Status das Migracoes

```bash
# Desenvolvimento
cd backend
mix ecto.migrations

# Producao (Kubernetes)
kubectl exec -n pix deployment/pix-backend -- \
  bin/monetarie_pix eval "Shared.Release.check_migrations()"
```

## Dados de Seed

O Monetarie PIX possui 11 arquivos de seed que devem ser executados na ordem correta:

| Arquivo | Registros | Descricao |
|---------|-----------|-----------|
| `seeds.exs` | ~17 | 10 participantes + 7 usuarios |
| `gap_resolution_seed.sql` | ~600 | 369 bancos + 15 codigos de status + 190 codigos de razao + 26 tipos de mensagem |
| `pix_keys_seed.sql` | 277 | Chaves PIX de teste |
| `dict_operations_seed.sql` | 15.442 | Operacoes DICT historicas |
| `xsd_schemas_seed.exs` | 27 | Schemas XSD para validacao |
| `bacen_error_codes_seed.exs` | 161 | Codigos de erro BACEN |
| `bacen_domain_values_seed.exs` | 175+ | Valores de dominio BACEN |
| `full_bank_directory_seed.exs` | 98 | Diretorio bancario complementar |
| `realistic_transactions_seed.exs` | 1.000+ | Transacoes realistas (30 dias) |
| `simulator_seed.exs` | ~52 | Config + cenarios + entries + saldos do simulador |
| Migration #18 (inline) | 50 | 44 contas COSIF + 6 centros de custo |

### Executar Seeds

```bash
# Desenvolvimento (todos os seeds)
cd backend
mix run apps/shared/priv/repo/seeds.exs

# Seed especifico
mix run apps/shared/priv/repo/xsd_schemas_seed.exs
```

## Particionamento de Tabelas

O Monetarie PIX utiliza particionamento por intervalo mensal para tabelas de alto volume:

### Tabelas Particionadas

| Tabela | Tipo | Coluna de Particao | Retencao |
|--------|------|--------------------|---------|
| `monetarie_auth.activity_log` | Range (mensal) | `created_at` | 12 meses |
| `monetarie_auth.login_history` | Range (mensal) | `login_at` | 12 meses |

### Particoes Criadas pela Migracao #25

```sql
-- Particoes mensais para activity_log
CREATE TABLE monetarie_auth.activity_log_2026_01
  PARTITION OF monetarie_auth.activity_log
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE monetarie_auth.activity_log_2026_02
  PARTITION OF monetarie_auth.activity_log
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- ... ate 2026-04 + particao DEFAULT
CREATE TABLE monetarie_auth.activity_log_default
  PARTITION OF monetarie_auth.activity_log DEFAULT;
```

### PartitionManager (GenServer)

O `Shared.PartitionManager` roda diariamente e:

1. **Cria particoes** para os proximos 60 dias (se ainda nao existem)
2. **Purga registros expirados** de auditoria via `XmlArchiver.purge_expired/0`
3. Opera com `try/rescue` para resiliencia (falha nao derruba o supervisor)

```mermaid
flowchart LR
    PM["PartitionManager<br/>(daily)"] --> CP["Criar Particoes<br/>+60 dias"]
    PM --> PA["Purgar Auditoria<br/>registros expirados"]
    CP --> AL["activity_log"]
    CP --> LH["login_history"]
```

## Indices de Performance

### Indices TPS (Migracao #23)

8 indices otimizados para throughput de 5.000 TPS:

| Indice | Tabela | Colunas | Tipo |
|--------|--------|---------|------|
| `idx_messages_branch_id` | `monetarie_spi.messages` | `branch_id` | B-tree |
| `idx_messages_branch_status` | `monetarie_spi.messages` | `branch_id, status_id` | Composto |
| `idx_messages_cpf` | `monetarie_spi.messages` | `cpf_cnpj` | B-tree |
| `idx_messages_cnpj` | `monetarie_spi.messages` | `cnpj` | B-tree |
| `idx_messages_e2e_trgm` | `monetarie_spi.messages` | `(end_to_end_id::text)` | GIN trigram |
| `idx_message_history_msg` | `monetarie_spi.message_history` | `message_id, created_at` | Composto |
| `idx_balance_history_acct` | `monetarie_spi.balance_history` | `account_id, created_at` | Composto |
| `idx_dict_keys_key_value` | `monetarie_dict.pix_keys` | `key_value` | B-tree |

::: warning GIN TRIGRAM EM COLUNAS CHAR
O operador `gin_trgm_ops` nao aceita colunas do tipo `CHAR`. E necessario fazer cast para `text`:
```sql
CREATE INDEX idx_messages_e2e_trgm
  ON monetarie_spi.messages
  USING gin ((end_to_end_id::text) gin_trgm_ops);
```
:::

### Indices de Performance (Migracao #20)

Indices adicionais para consultas frequentes:

| Indice | Tabela | Colunas |
|--------|--------|---------|
| `idx_messages_e2e_id` | `monetarie_spi.messages` | `end_to_end_id` |
| `idx_messages_status` | `monetarie_spi.messages` | `status_id` |
| `idx_messages_created_at` | `monetarie_spi.messages` | `created_at` |
| `idx_messages_operation_time` | `monetarie_spi.messages` | `operation_time` |

## Tuning PostgreSQL para PIX

### Parametros Recomendados

```ini
# postgresql.conf para carga PIX (5.000 TPS)
max_connections = 6000
shared_buffers = 8GB            # 25% da RAM
effective_cache_size = 24GB     # 75% da RAM
work_mem = 64MB
maintenance_work_mem = 2GB
wal_buffers = 64MB

# Write-Ahead Log
wal_level = replica
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9

# Planner
random_page_cost = 1.1          # Para SSD
effective_io_concurrency = 200  # Para SSD

# Autovacuum (agressivo para tabelas de alto volume)
autovacuum_max_workers = 6
autovacuum_naptime = 10s
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
```

### Cloud SQL (GCP)

Para o ambiente GCP com Cloud SQL:

```bash
# Flags de configuracao via gcloud
gcloud sql instances patch monetarie-db \
  --database-flags=max_connections=6000,shared_buffers=8GB,work_mem=64MB
```

::: tip CONEXAO DIRETA
O Monetarie PIX usa conexao direta via IP privado (`10.140.241.2`), sem Cloud SQL Proxy. O proxy causa rate limiting (429) com pools de conexao grandes.
:::

## Backup e Recuperacao

### pg_dump (Backup Logico)

```bash
# Backup completo
pg_dump -h $DB_HOST -U $DB_USER -d monetarie \
  --format=custom --compress=9 \
  --file=monetarie_$(date +%Y%m%d_%H%M%S).dump

# Backup de schema especifico
pg_dump -h $DB_HOST -U $DB_USER -d monetarie \
  --schema=monetarie_spi --format=custom \
  --file=monetarie_spi_$(date +%Y%m%d_%H%M%S).dump

# Restaurar
pg_restore -h $DB_HOST -U $DB_USER -d monetarie \
  --clean --if-exists monetarie_20260213.dump
```

### WAL Archiving (PITR)

Para point-in-time recovery, configure WAL archiving:

```ini
# postgresql.conf
archive_mode = on
archive_command = 'gcloud storage cp %p gs://fluxiq-wal-archive/%f'
```

```bash
# Restaurar para um ponto no tempo
pg_basebackup -h $DB_HOST -U replication -D /var/lib/postgresql/recovery

# recovery.conf
restore_command = 'gcloud storage cp gs://fluxiq-wal-archive/%f %p'
recovery_target_time = '2026-02-13 14:30:00 America/Sao_Paulo'
```

### Recomendacoes de Backup

| Tipo | Frequencia | Retencao | Metodo |
|------|-----------|----------|--------|
| Backup completo | Diario (03:00 BRT) | 30 dias | pg_dump + GCS |
| WAL archiving | Continuo | 7 dias | WAL + GCS |
| Snapshot de disco | Diario | 14 dias | GCP Disk Snapshot |
| Backup de teste | Semanal | 1 backup | Restaurar em ambiente de teste |

## Resultado Esperado

Apos a configuracao completa do banco de dados:

- O banco `monetarie` existe com 8 schemas criados
- Todas as 25 migracoes estao aplicadas (`mix ecto.migrations` mostra todas como `up`)
- Os seeds popularam aproximadamente 17.000 registros de referencia
- Os 8 indices TPS estao criados e ativos
- As particoes mensais para `activity_log` e `login_history` existem
- O `PartitionManager` esta em execucao no supervisor da aplicacao
- O pool de conexoes esta dimensionado para a carga esperada
- `SELECT count(*) FROM monetarie_spi.messages` retorna dados de transacoes seed