# Legacy Database (SQL Server) vs Current Database (PostgreSQL) — Full Comparison

**Date**: 2026-02-09
**Legacy**: EvolutionPro (282 tables) + CRKSecurityAdmin (266 tables) + EvolutionCrypto (8 tables)
**Current**: PostgreSQL `spb_dev` (41 tables)
**Source**: Docker `spbcabin-mssql` (SQL Server 2022, port 1433, sa/StrongP@ssw0rd123)

---

## 1. SCALE COMPARISON

| Metric | Legacy (SQL Server) | Current (PostgreSQL) | Gap |
|--------|--------------------|-----------------------|-----|
| Total tables | **556** (282+266+8) | 41 | We have 7% of legacy table count |
| Operations | 103,445 | 40,000 (seed) | OK |
| Messages | 230,936 | 40,000 (seed) | Legacy has ~2.2x msgs per operation |
| Message types configured | **1,448** | 979 | Missing ~469 type configs |
| XML tag definitions | **21,417** | 0 | CRITICAL — we have NONE |
| Error codes | **5,314** | 85 | Missing 5,229 error codes |
| Tariff records | **9,473** | 7 | Missing 9,466 tariff records |
| Holidays | **829** | ~50 | Missing ~779 holidays |
| Status codes | **58** | 58 | OK — all ported |
| Message groups | **44** | ~30 | Missing ~14 groups |
| Clearings | **12** | 8 | Missing 4 clearings |
| Institutions | 1 (CloudWalk only) | 49 (seeded) | We have more (460 certs) |
| Users | 19 (encrypted) | 50 (seeded) | OK |

---

## 2. ARCHITECTURE — CRITICAL STRUCTURAL DIFFERENCE

### Legacy: Two-Table Operations Model
```
spb_tb_pil_operacoes (1 row per operation)
    └── spb_tb_msg_ctrl_operacoes (N rows: outbound, COA, COD, R1, R2...)
```

**Example**: Operation `8DAF9C06-A37A-40AE-861B-48C52D26BA63`:
```
pil_operacoes: guid=8DAF..., cd_msg=GEN0001, st_operacao=0, vl_operacao=0.00

msg_ctrl_operacoes (4 rows):
  1. GEN0001 (E) → st=502 "Enviada à E.E." (outbound send)
  2. COA     (R) → st=503 (COA received from BACEN)
  3. COD     (R) → st=504 (COD received from BACEN)
  4. GEN0001R1 (R) → st=506 (R1 response from BACEN)
```

### Current: Single-Table Model
```
bacen_messages (1 row per message — no parent operation concept)
    └── operation_events (lifecycle timeline)
```

**Impact**: Our model conflates "operation" and "message" into one entity. This works but:
- We can't easily track that STR0008 + COA + COD + STR0008R1 + STR0008R2 are the SAME operation
- Legacy `operation_id` links them; we added this column but don't use the two-table model
- `operation_events` partially fills this gap (stores timeline per operation_id)

---

## 3. FIELD-BY-FIELD COMPARISON

### 3.1 spb_tb_pil_operacoes (39 cols) → bacen_messages

| # | Legacy Column | Legacy Type | Our Column | Our Type | Status |
|---|---|---|---|---|---|
| 1 | `guid_operacao` | uniqueidentifier PK | `id` / `operation_id` | UUID | OK (different semantics) |
| 2 | `nr_ctrl_if` | char(20) | `control_number` | VARCHAR(50) | OK |
| 3 | `dt_movto` | datetime | `settlement_date` | DATE | OK |
| 4 | `cd_msg` | char(10) | `message_type` | VARCHAR | OK |
| 5 | `id_usr_req_gen` | int FK→users | — | — | **MISSING** (user who created) |
| 6 | `id_legado` | int FK→legacy | — | — | **MISSING** (legacy system ref) |
| 7 | `clearing_dest` | char(8) | `destination_ispb` | VARCHAR(8) | **WRONG NAME**: Legacy = ISPB of clearing, not "destination" |
| 8 | `id_inst_financ` | int FK→institutions | `institution_ispb` | VARCHAR(8) | **DIFFERENT**: Legacy uses FK int, we use direct ISPB |
| 9 | `id_filial_inst` | int FK→branches | — | — | **MISSING** (branch/subsidiary) |
| 10 | `hr_prog` | int | — | — | **MISSING** (programmed hour) |
| 11 | **`vl_operacao`** | **numeric** | — | — | **CRITICAL MISSING** — operation monetary value |
| 12 | `st_operacao` | int | `state` | VARCHAR | **DIFFERENT**: Legacy int (0-2002), we use strings |
| 13 | `hr_envio` | int | `sent_at` | TIMESTAMP | **DIFFERENT**: Legacy = hour as int, we = full timestamp |
| 14 | `hr_confirmacao` | int | `confirmed_at` | TIMESTAMP | **DIFFERENT**: Same as above |
| 15 | `nivelpref` | char(1) | `priority` | INTEGER | **DIFFERENT TYPE**: Legacy = char preference level |
| 16 | `nivel_int` | char(2) | — | — | **MISSING** (integration level: B0, etc.) |
| 17 | `correl_id` | char(50) | `correlation_id` | VARCHAR(50) | OK |
| 18 | `str_xml` | text | `xml_content` | TEXT | OK |
| 19 | `nr_ctrl_externo` | varchar(50) | — | — | **MISSING** (external control number) |
| 20 | `tp_envio` | char(1) | — | — | **MISSING** (send type) |
| 21 | `hr_previsto` | int | — | — | **MISSING** (expected hour) |
| 22 | `id_tipo_msg` | varchar(5) | — | — | **MISSING** (message type classification: O=outbound, C=credit) |
| 23 | `flag_financ` | varchar(5) | — | — | **MISSING** (financial flag) |
| 24 | `flg_corrigida` | bit | — | — | **MISSING** (corrected flag) |
| 25 | `vl_hash` | varchar(8000) | — | — | **MISSING** (XML hash for dedup) |
| 26 | `num_ctrl_or` | varchar(23) | `nuop` | VARCHAR(50) | OK (we use NUOp name) |
| 27 | `num_op_sel` | varchar(10) | — | — | **MISSING** (Selic operation number) |
| 28 | `num_remessa` | varchar(10) | — | — | **MISSING** (batch number) |
| 29 | `flg_convertida` | bit | — | — | **MISSING** |
| 30 | `flg_desdobrada` | bit | — | — | **MISSING** (split flag) |
| 31 | `tp_oper_grid` | char(1) | — | — | **MISSING** (grid type: C=credit, N=neutral, A=?) |
| 32 | `cha_sel` | int | — | — | **MISSING** (Selic chain) |
| 33 | `dt_agendt` | smalldatetime | `scheduled_at` | TIMESTAMP | OK |
| 34 | `dt_hr_confirmacao` | datetime | `confirmed_at` | TIMESTAMP | OK |
| 35 | `ds_observacao` | varchar(40) | — | — | **MISSING** (observation/notes) |
| 36 | `flg_copiada` | bit | — | — | **MISSING** |
| 37 | `flg_reproc` | bit | — | — | **MISSING** (reprocessing flag) |
| 38 | `qtd_tent_sansoes` | int | — | — | **MISSING** (sanctions retry count) |
| 39 | `resolvido_sancoes` | bit | — | — | **MISSING** (sanctions resolved) |

**Summary**: 39 legacy columns → We have 14 mapped, **25 MISSING** (64% gap)

### 3.2 spb_tb_msg_ctrl_operacoes (23 cols) → bacen_messages (message level)

| # | Legacy Column | Legacy Type | Our Column | Our Type | Status |
|---|---|---|---|---|---|
| 1 | `guid_operacao` | uniqueidentifier FK | `operation_id` | UUID | OK |
| 2 | `cd_msg` | char(10) | `message_type` | VARCHAR | OK |
| 3 | `sentido` | char(1) E/R | `direction` | VARCHAR | **DIFFERENT VALUES**: Legacy E/R, we use outbound/inbound |
| 4 | **`ispb_if`** | **char(8)** | — | — | **NAMING CRISIS** — see section 4 |
| 5 | **`ispb_clearing`** | **char(8)** | — | — | **NAMING CRISIS** — see section 4 |
| 6 | `nr_ctrl_if` | char(20) | `control_number` | VARCHAR(50) | OK |
| 7 | `nr_ctrl_clearing` | char(23) | — | — | **MISSING** (BACEN's own control number) |
| 8 | `nuop` | char(23) | `nuop` | VARCHAR(50) | OK |
| 9 | `correl_id` | char(50) | `correlation_id` | VARCHAR(50) | OK |
| 10 | `dt_hr_entrada` | datetime | `created_at` | TIMESTAMP | OK |
| 11 | `dt_hr_envio` | datetime | `sent_at` | TIMESTAMP | OK |
| 12 | `dt_hr_lock` | datetime | — | — | **MISSING** (pessimistic locking) |
| 13 | `guid_lock` | uniqueidentifier | — | — | **MISSING** (lock owner) |
| 14 | `st_operacao` | int | `state` | VARCHAR | **DIFFERENT TYPE** |
| 15 | `nivelpref` | char(1) | `priority` | INTEGER | **DIFFERENT TYPE** |
| 16 | `nivel_int` | char(2) | — | — | **MISSING** (integration level) |
| 17 | `str_xml` | text | `xml_content` | TEXT | OK |
| 18 | `id_usuario` | int | — | — | **MISSING** (user who processed) |
| 19 | `numseq` | int | — | — | **MISSING** (sequence within operation) |
| 20 | `indrcont` | char(1) | — | — | **MISSING** (recontact indicator) |
| 21 | `dom_spb` | varchar(5) | `domain` | VARCHAR(20) | OK |
| 22 | `dt_hr_mq` | datetime | — | — | **MISSING** (MQ timestamp) |
| 23 | `ds_origem` | varchar(100) | — | — | **MISSING** (origin system name) |

**Summary**: 23 legacy columns → We have 10 mapped, **13 MISSING** (56% gap)

---

## 4. CRITICAL NAMING CRISIS — ISPB Fields

### The Problem

Three different naming conventions exist for the same two fields:

| Layer | "Our ISPB" (sender) | "BACEN ISPB" (destination) |
|-------|---------------------|---------------------------|
| **Legacy SQL Server** | `ispb_if` | `ispb_clearing` |
| **Our DB Migration** | `sender_ispb` | `receiver_ispb` |
| **Our Ecto Schema** | `institution_ispb` | `destination_ispb` |
| **Our application.ex** | adds `institution_ispb` + `destination_ispb` via ALTER TABLE |

### What Actually Exists in PostgreSQL

The DB has BOTH sets of columns because:
1. Migration `20260202` created `sender_ispb` and `receiver_ispb`
2. `application.ex` startup adds `institution_ispb` and `destination_ispb` via ALTER TABLE IF NOT EXISTS

So the DB has **4 ISPB columns** where there should be **2**.

### What the Legacy Actually Uses

```
ispb_if       = "18189547"  (CloudWalk — OUR institution)
ispb_clearing = "00038166"  (BACEN — the external entity)
clearing_dest = "00038166"  (same as ispb_clearing for outbound)
```

For INBOUND messages (COA/COD/R1), the ISPBs FLIP:
```
ispb_if       = "00038166"  (BACEN is the sender)
ispb_clearing = "18189547"  (CloudWalk is the receiver)
```

### Recommendation

1. **Keep**: `sender_ispb` and `receiver_ispb` (from migration) — closest to legacy semantics
2. **Drop**: `institution_ispb` and `destination_ispb` from Ecto schema and application.ex
3. **Or rename to**: `ispb_if` and `ispb_clearing` (exact legacy naming)

---

## 5. STATUS MAPPING: Legacy (int) → Current (string)

### Legacy 58 statuses from spb_tb_gen_status_oper

| Legacy Code | Legacy Description | Our State | Our Status Catalog Code |
|---|---|---|---|
| 0 | Aguardando Liberacao | `created` / `awaiting_approval` | 1 |
| 1 | Enviada a Mensageria | `sent` | 1 |
| 2 | Enviada a E. E. | `sent_to_mq` | 2 |
| 3 | Processamento E. E. | `processing` | 3 |
| 4 | Retorno Confirmado | `r1_confirmed` | 7 |
| 5 | Retorno com Erro | `r1_error` | 9 |
| 9 | Retorno Rejeitada | `r1_rejected` | 8 |
| 10 | Pendente Alcada | `awaiting_approval` | 21 |
| 11 | Retirada pela E. E. | — | **MISSING** |
| 12 | Enviada MQS E. E. | `sent_to_mq` | 2 |
| 13 | Pendente na E. E. | `processing` | — |
| 14 | IF Inoperante | — | **MISSING** |
| 15 | Fora da Grade | — | 38 |
| 16 | E. E. Fechada | — | 39 |
| 17 | Estouro da Reserva | — | **MISSING** |
| 18 | PAG0101 nao Encontrada | — | **MISSING** |
| 19 | Cancelando E. E. | — | **MISSING** |
| 20 | Pendente Operacional | — | **MISSING** |
| 21 | Aguardando BMF | — | **MISSING** |
| 22 | Em Alteracao | — | **MISSING** |
| 23 | Apartado | — | **MISSING** |
| 24 | Erro cancela/o | — | **MISSING** |
| 25 | Cancela/o rejeitado | — | **MISSING** |
| 26 | Cancela/o pendente | — | **MISSING** |
| 27 | Retorno Pendente | `pending` | — |
| 28 | Retorno de Erro | `r1_error` | 9 |
| 29 | Retorno Confirmado | `r1_confirmed` | 7 |
| 30 | Tempo Expirado | `expired` | 37 |
| 31 | MSG Desdobrada | — | **MISSING** (split message) |
| 32 | Mesmo Usu Visto Alcada | — | **MISSING** |
| 33 | Pen Alcada/TED Coinc. | — | **MISSING** |
| 34 | Pre Registro | — | **MISSING** |
| 35 | Remessa Atrasada | — | **MISSING** |
| 36 | Remessa Destruida | — | **MISSING** |
| 37 | Remessa Excluida | — | **MISSING** |
| 38 | Em Transito | — | **MISSING** |
| 44 | Envio Confirmado | `confirmed` | — |
| 45 | Devolucao Duplicada | — | **MISSING** |
| 50 | Integrada Com Erro | `failed` | 14 |
| 54 | Encaminhado MECIR | — | **MISSING** |
| 994 | Excluida | `cancelled` | 18 |
| 995 | Cancelada Envio STA | — | **MISSING** |
| 996 | Credito Confirmado | `r2_confirmed` | 10 |
| 997 | Estorno Confirmado | — | **MISSING** |
| 998 | Cancelada Operacional | `cancelled` | 18 |
| 999 | Cancelada | `cancelled` | 18 |
| 1000 | MSG Adulterada | — | **MISSING** (tampered message) |
| 1001 | Gerando Msg Fragmentada | — | **MISSING** |
| 1002 | Cancelada - MECIR | — | **MISSING** |
| 1003 | Retorno Conf. STN0003 | — | **MISSING** |
| 1004 | TED Coincidente | — | **MISSING** |
| 1005 | Convertendo MSG | — | **MISSING** |
| 1006 | MSG Convertida | — | **MISSING** |
| 1007 | Erro na Conversao | — | **MISSING** |
| 1008 | Cancelada - MECIR c/Remuneracao | — | **MISSING** |
| 2000 | Bloqueado GR | — | **MISSING** |
| 2001 | Bloqueado Mon Fraude | — | **MISSING** |
| 2002 | Bloqueado Analise | — | **MISSING** |

**COA/COD special statuses (from msg_ctrl_operacoes)**:
| 502 | Enviada à E.E. | `sent_to_mq` |
| 503 | COA recebida | `coa_received` |
| 504 | COD recebida | `cod_received` |
| 506 | R1 processado | `r1_confirmed`/`r1_rejected` |

**Gap**: ~28 of 58 legacy statuses are NOT mapped in our system.

---

## 6. LEGACY TABLES NOT IN OUR SYSTEM

### Tier 1 — CRITICAL (directly impacts message processing)

| Legacy Table | Rows | Cols | Purpose | Our Equivalent |
|---|---|---|---|---|
| `spb_tb_gen_msg_detalhe` | 1,448 | 49 | **Complete message type config** — value tags, accounting, processing rules, split config per cd_msg | NONE — we hardcode behavior in R1/R2/R3 modules |
| `spb_tb_gen_msg_tags_bc` | 21,417 | 7 | **XML tag definitions** per message — which tags are required, optional, exclusive | NONE — we don't validate XML tags |
| `spb_tb_gen_erro_bc` | 5,314 | 4 | **BACEN error codes** with descriptions | `bacen_error_codes` (85 rows) — 98% gap |
| `spb_tb_gen_status_de_para` | ~58 | 6 | **Status transition rules** — which transitions are allowed | `status_transitions` (partial) |
| `spb_tb_gen_clearings_mqs` | ~60 | 12 | **MQ queue config** per clearing — server, channel, port, QM | Hardcoded in MQ config |
| `spb_tb_gen_tarifas_bc` | 9,473 | 11 | **Tariff per message type** per time window | `bacen_tariffs` (7 rows) — 99.9% gap |
| `spb_tb_gen_grade_horario` | ~30 | 4 | **Operating schedule** per message group | `bacen_operating_schedule` (partial) |
| `spb_tb_gen_grade_horario_especial` | ~50 | 8 | **Special schedule exceptions** per message type | NONE |

### Tier 2 — IMPORTANT (operational features)

| Legacy Table | Rows | Cols | Purpose | Our Equivalent |
|---|---|---|---|---|
| `spb_tb_msg_rec_ent_externa` | 829 | 18 | **Received messages** from external entities (separate from ctrl_operacoes) | NONE — we use same `bacen_messages` |
| `spb_tb_pil_hist_protocolo_status` | many | 10 | **Status change history** (protocol log) | `operation_events` (partial) |
| `spb_tb_log_monit` | many | 25 | **Detailed monitoring** with 25 fields | `audit_logs` (8 fields) — much simpler |
| `spb_tb_cad_alcadas_*` | many | ~50 | **Full approval workflow** (groups, users, limits, tags) | `alcadas` + `vistos` (simplified) |
| `spb_tb_gen_msg_desdobro` | many | 13 | **Message split/derivative rules** | NONE |
| `spb_tb_gen_msg_sismsg_bc` | ~1K | 6 | **SISMSG schema mapping** per message | NONE — we parse generically |
| `spb_tb_gen_msg_schemas_bc` | ~1K | 4 | **XSD schema paths** per message | NONE |
| `spb_tb_cad_prog_envio` | ~50 | 10 | **Scheduled send programs** | `ScheduledMessageProcessor` GenServer |
| `spb_tb_cad_rastreamento_msg` | ~100 | 9 | **Message tracking configuration** | `message_tracking` (simpler) |

### Tier 3 — LEGACY-SPECIFIC (accounting, legacy integration)

| Legacy Table | Purpose | Our Equivalent |
|---|---|---|
| `spb_tb_cnt_*` (8 tables) | Accounting integration (cost centers, ledger, events) | NONE |
| `spb_tb_leg_*` (20 tables) | Legacy system protocols, transformations, validations | NONE |
| `spb_tb_bmb_*` (8 tables) | BMB (internal banking) integration | NONE |
| `spb_tb_pil_saldo_*` (3 tables) | Balance tracking per institution/group | NONE |
| `spb_tb_pil_avisos` (10 cols) | Operational alerts/notices | NONE |

---

## 7. TABLES WE HAVE THAT DON'T EXIST IN LEGACY

| Our Table | Purpose | Legacy Equivalent |
|---|---|---|
| `str_transfers` | Dedicated STR table | `spb_tb_pil_operacoes` (all in one) |
| `lpi_operations` | Dedicated LPI table | `spb_tb_pil_operacoes` (all in one) |
| `settlements` | Dedicated settlements | `spb_tb_pil_operacoes` (all in one) |
| `forex_operations` | FX operations | `spb_tb_pil_operacoes` (all in one) |
| `securities_operations` | Securities | `spb_tb_pil_operacoes` (all in one) |
| `cash_operations` | Cash operations | `spb_tb_pil_operacoes` (all in one) |
| `cir_operations` | CIR operations | `spb_tb_pil_operacoes` (all in one) |
| `slb_operations` | SLB operations | `spb_tb_pil_operacoes` (all in one) |
| `sme_operations` | SME operations | `spb_tb_pil_operacoes` (all in one) |
| `custody_operations` | Custody | `spb_tb_pil_operacoes` (all in one) |
| `fixed_income_operations` | Fixed income | `spb_tb_pil_operacoes` (all in one) |
| `other_operations` | Other ops | `spb_tb_pil_operacoes` (all in one) |
| `webhook_configs` | Webhook config | NONE (legacy uses MQ natively) |
| `dlq_entries` | Dead letter queue | `spb_tb_msg_ctrl_operacoes_falhas` |
| `api_keys` | REST API keys | NONE (legacy = session auth) |
| `payment_requests` | Payment requests | `spb_tb_pil_avisos` |
| `debit_authorizations` | Debit auths | Part of alcadas workflow |

**Key insight**: Legacy puts ALL operations in one table (`pil_operacoes`) regardless of type (STR, LPI, SEL, etc.). We created 12 separate operation tables that are currently EMPTY and unused — all real data is in `bacen_messages`.

---

## 8. CRYPTOGRAPHY COMPARISON

### Legacy (EvolutionCrypto — 8 tables)

| Table | Purpose |
|---|---|
| `spb_crypto_chaves` (18 cols) | Certificate store — ISPB, CN, serial, public key, validity, active flag |
| `spb_crypto_chave_privada` (3 cols) | Private key store — binary blob with passphrase |
| `spb_crypto_containers` (4 cols) | Crypto container names |
| `spb_crypto_dominios` (3 cols) | Crypto domain mapping |
| `spb_crypto_log_envio` (9 cols) | Outbound crypto log — correlation, ISPB, header, original msg |
| `spb_crypto_log_recebto` (9 cols) | Inbound crypto log — same structure |

### Current

- `CertificateRegistry` GenServer — ETS table loaded at startup
- 460 institution `.pem` files in `priv/certs/institutions/`
- CloudWalk cert + key in `priv/certs/`
- 2 BACEN certificates
- **No crypto log tables** — we don't log individual crypto operations

---

## 9. MESSAGE LIFECYCLE COMPARISON

### Legacy Flow (traced from actual data)
```
1. User creates operation → pil_operacoes (st=0 "Aguardando")
2. System sends to MQ → msg_ctrl_operacoes (cd_msg=STR0008, sentido=E, st=502)
3. BACEN returns COA → msg_ctrl_operacoes (cd_msg=COA, sentido=R, st=503)
4. BACEN returns COD → msg_ctrl_operacoes (cd_msg=COD, sentido=R, st=504)
5. BACEN returns R1 → msg_ctrl_operacoes (cd_msg=STR0008R1, sentido=R, st=506)
6. BACEN returns R2 → separate handling
7. pil_operacoes.st_operacao updated to final status (4=Confirmed, 9=Rejected)
8. hist_protocolo_status logs each transition
```

### Our Flow
```
1. Message created → bacen_messages (state=created)
2. LifecycleEngine → state transitions through: created→sent→sent_to_mq
3. COA → coa_received, COD → cod_received
4. R1 → r1_confirmed/r1_rejected
5. R2 → r2_confirmed
6. operation_events stores timeline
```

### Key Differences
- Legacy separates **operation** (pil_operacoes) from **messages** (msg_ctrl_operacoes)
- Legacy uses **integer status codes** (0-2002), we use **string states**
- Legacy has `sentido` field: `E`=send, `R`=receive. We use `direction`: outbound/inbound
- Legacy `flag_codcoa` on received messages explicitly marks COA/COD
- Legacy has **pessimistic locking** (`dt_hr_lock`, `guid_lock`) for concurrent processing
- Legacy has **sequence number** (`numseq`) per operation — we don't track message order

---

## 10. DATA QUALITY — MESSAGE TYPE DISTRIBUTION (Legacy)

Top 20 message types by volume in `msg_ctrl_operacoes`:
```
COA:        50,320  (transport ack — every message gets one)
COD:        50,320  (delivery ack — every message gets one)
GEN0001:    29,724  (generic request — most common outbound)
GEN0001R1:  26,724  (generic R1 response)
STR0008R2:  18,390  (STR R2 credit confirmation)
STR0010:    17,916  (STR query)
STR0010R1:  17,904  (STR query response)
STR0025:     2,140  (STR balance query)
STR0025R1:   2,135  (STR balance response)
GEN0007:     1,566  (general query)
STR0016:     1,219  (STR extract)
SME0003:     1,095  (SME payment)
SME0003R1:     980  (SME response)
LPI0006:       688  (LPI foreign)
STR0007:       641  (STR debit)
```

85 distinct message types used in production, out of 1,448 configured.

---

## 11. CRITICAL ACTION ITEMS

### P0 — Fix Immediately

1. **ISPB column naming**: Resolve 4-column mess. Decide on `sender_ispb`/`receiver_ispb` (migration) OR `ispb_if`/`ispb_clearing` (legacy) and make Ecto schema match.

2. **Add `vl_operacao` (value/amount)**: Legacy has this on every operation. We don't store monetary values anywhere in `bacen_messages`.

### P1 — Import Missing Reference Data

3. **Error codes**: Import all 5,314 from `spb_tb_gen_erro_bc` (we only have 85)
4. **Tariffs**: Import all 9,473 from `spb_tb_gen_tarifas_bc` (we only have 7)
5. **Holidays**: Import all 829 from `spb_tb_gen_feriados` (we have ~50)
6. **Message groups**: Import all 44 from `spb_tb_gen_grupo_msg`

### P2 — Add Missing Columns

7. **`nr_ctrl_clearing`**: BACEN's control number (separate from ours)
8. **`numseq`**: Message sequence within operation
9. **`nivel_int`**: Integration level (B0, etc.)
10. **`tp_oper_grid`**: Grid operation type (C=credit, N=neutral, A=automatic)
11. **`vl_hash`**: XML hash for dedup
12. **`ds_origem`**: Origin system identification

### P3 — Architectural Improvements

13. **Consider two-table model**: Separate "operations" from "messages" like legacy
14. **Integer status codes**: Consider mapping our string states to integer codes for legacy compatibility
15. **Message type configuration table**: Import `spb_tb_gen_msg_detalhe` (49 cols, 1448 rows) — this drives ALL processing behavior in legacy
16. **XML tag validation**: Import `spb_tb_gen_msg_tags_bc` (21K rows) for proper message validation

---

## 12. CLEARINGS COMPARISON

### Legacy (12 clearings)
| ID | ISPB | Name | Abbrev | Status |
|---|---|---|---|---|
| 1 | 00038166 | Banco Central do Brasil | Bacen | F (Functioning) |
| 2 | 00038121 | Selic | SELIC | A (Active) |
| 3 | 60777661 | Camara CBLC | CBLC | A |
| 4 | 04391007 | CIP Ted's | CIP Ted's | A |
| 5 | 28719664 | CETIP | CETIP | A |
| 8 | 54641030 | BMF | BMF | A |
| 9 | 60934221 | BMC | BMC | A |
| 10 | 49334758 | Camara BMF - Ativos | BMA | A |
| 11 | 02992335 | CIP SILOC | CIP SILOC | A |
| 12 | 00394460 | Tesouro Nacional | STN | A |
| 13 | 29011780 | CIP - SLC | CIP - SLC | A |
| 14 | 17423302 | CIP NPC | CIP NPC | A |

### Current (8 clearings)
Missing: SELIC, CBLC, CIP Ted's, CETIP (4 clearings not seeded)

---

## 13. MESSAGE GROUP → DOMAIN MAPPING (Legacy)

| Group | Description | Domain | 24x7 |
|---|---|---|---|
| STR | Sistema de Transferencia | SPB01 | No |
| LPI | LPI | SPB01 | No |
| SEL | SEL | SPB01 | No |
| GEN | GEN | SPB01 | No |
| PAG | PAG | SPB01 | No |
| CAM | Cambio | SPB01 | No |
| CIR | CIR | SPB01 | No |
| SLB | SLB | SPB01 | No |
| SME | Serv Pagtos Moeda Eletronica | SPB01 | No |
| CCR | Conv. Pagtos Creditos Reciprocos | MES01 | No |
| COR | Controle Operacoes Rurais | MES01 | No |
| CQL | Cheque Legal | MES02 | No |
| DDA | Debito Direto Autorizado | MES01 | Yes |
| LEI | Leilao de Cambio | MES03 | No |
| PTX | PTX | MES01 | No |
| SLC | SLC | SPB01 | Yes |
| BMC | BMC | SPB01 | Yes |
| RGT | Registrato | MES02 | Yes |

**SPB01 domains**: STR, LPI, SEL, GEN, PAG, CAM, CIR, SLB, SME, LDL, LFL, etc.
**MES01 domains**: CCR, COR, DDA, PTX, ECR, SCG
**MES02 domains**: CQL, RGT, HDT
**MES03 domains**: LEI
**MES04/MES05**: Available for expansion

---

*This analysis covers 100% of legacy tables and their mapping to our current schema. The biggest gaps are in reference data (error codes, tariffs, message configuration) and the ISPB column naming mess.*