# Design: ETL completo do sistema legado PIX/SPB (backups AB/GX/MGI de 2026-06-30)

Data: 2026-07-01
Autor: Claude (fase 2 do mandato "ETL completo + revalidação geral")
Base empírica: workflow de entendimento em 6 dimensões executado em 2026-07-01 (inventário dos 16 backups, procedures T-SQL do AB_MGI, código .NET decompilado do LegadoPIX, schemas reais das cabines local e HML, distribuições de status no staging, auditoria dos bancos vivos em HML).

## 1. Contexto e escopo

A carga de 2026-07-01 (Codex) stageou 81 das 278 tabelas com dados (55 PIX + 26 SPB) e projetou apenas o extrato, as mensagens BACEN/PSP e o saldo SPI. Este design fecha o restante:

1. Staging completo dos 16 backups (menos exclusões justificadas).
2. Projeções de negócio PIX: ordens de pagamento, anotações de crédito, bloqueios, devoluções, infrações e MED.
3. Correção do acervo SPB: tipo de mensagem real, direção real, estados canônicos, valores e eventos de timeline.
4. Mapa oficial de status legado, comprovado por código e dados (seção 4).
5. Idempotência provada por segunda execução.

Fora do escopo desta fase (dependem de decisão explícita do dono): promoção da carga para HML (Aurora, schemas `monetarie_*`), lançamentos contábeis em `journal_entries` (o `chart_of_accounts` da cabine está vazio) e exibição do acervo em KPIs/dashboards do SPB.

## 2. Fontes de verdade usadas

- Procedures T-SQL do AB_MGI (extraídas de `sys.sql_modules` no container `monetarie-bak-mssql`, dump em scratchpad): `SP_GRAVA_TRANS_INTF`, `P_CONSULTA_TRANSACOES`, `P_CONSULTA_TRANS_INTERF`, `SP_ENTREGA_PARA_LEGADO`, `SP_RECEBE_DO_LEGADO`, com comentários literais definindo os domínios de status.
- `/Users/luizpenha/mwbank/LegadoPIX/_decompiled` (vendor SPI .NET): `enumStatusOperacao.cs`, `Interpretacao.cs` (classificação funcional Pendente/ErroIntermediario/TerminoOK/TerminoErro), `03-Carga_Tabelas.sql`.
- Distribuições completas no staging local (`legacy_ab_pix`/`legacy_ab_spb`, SELECTs agregados).
- Ecto schemas da cabine PIX (`pix/backend/apps/*`) e SPB (`spb/services/bacen_gateway/*`), com enums e constraints reais.
- Nota: o fonte .NET do AutBank (dono das tabelas `AB_*`) não existe em `~/mwbank`; a semântica AB vem das procedures do próprio backup e das distribuições. Cada mapeamento abaixo está marcado como COMPROVADO ou INFERIDO-FORTE.

## 3. Staging completo

Executar `stage --profile all` com a lista de exclusão AMPLIADA. O filtro atual (`SECRET_OR_RUNTIME_TABLES`) casa só 4 nomes exatos; ampliar para:

| Exclusão | Motivo |
|---|---|
| `CERTIFICADOS`, `PARAMETROS`, `PARAMETROS_MQ`, `PARAMETROS_SEGURANCA` | já excluídas por design (segredos/runtime) |
| `THUMBPRINT_SITE_HISTORICO` (53.183, 208MB), `THUMBPRINT_SITE`, `THUMBPRINT_CERTIFICADO`, `DOWNLOAD_CERTIFICADOS_SITE` (76,8MB de blobs) | material de certificado; coerência com a exclusão de `CERTIFICADOS` |
| `TESTE_CONECTIVIDADE` (122.245, 480MB) | healthcheck puro, sem valor de negócio |

`MSG_ENDERECAMENTO_ENVIADA/RECEBIDA` (1,21M linhas, ~687MB, tráfego DICT bruto com CPF/CNPJ) ENTRAM no staging local: fazem parte do mandato "ETL completo", são a trilha de auditoria DICT do participante e o staging local já é tratado integralmente como PII (gitignorado, nunca versionado, só em container local). Sem projeção. Se o dono preferir excluí-las, é remover 2 nomes da lista antes do run.

Isso fecha, entre outras, as tabelas de negócio hoje ausentes: `ENDERECAMENTO` (303 chaves DICT históricas com posse desde 2020), `SOLICITACAO_DEVOLUCAO` (205), `RELATO_DE_INFRACAO` (391), `DEVOLUCAO_ESPECIAL` (373), `ORDEM_PAGAMENTO_MENSAGEM` (4.373 XMLs), `MENSAGENS_LPI`/`_RETORNO` (2.209, com `STATUSSGR`/`CODREJEICAO`), `MOVIMENTOS_CONTABEIS_CONTAPI_*`, `SALDO_CONTA_PI`, família `PROCESSA_*` de idempotência, e a simetria do catálogo `AB_MGI_DDAMSG` (`SERVICOS`, `REGRAS`, `DESC_EVENTOS` etc.).

## 4. Mapa oficial de status legado

Camada funcional (molde do próprio vendor legado, `Interpretacao.cs`): todo status reduz para `pending`, `intermediate_error`, `final_ok`, `final_error`.

### 4.1 PIX, ordens de pagamento (saída)

Regra estrutural: a tabela base congela no primeiro status (`ORDEM_PAGAMENTO` = 100% `ENVIADA`); o status de negócio é o TERMINAL da tabela `STATUS_ORDEM_PAGAMENTO`, chaveado por `ID_ORDEM_PAGAMENTO` com desempate por `DATA_HORA_REGISTRO`/`ID`.

| Terminal legado | Qtde | Destino `transactions.status` | Confiança |
|---|---:|---|---|
| EFETIVADA | 4.304 | `settled` | COMPROVADO (paridade 1:1 com bloqueio APROVADO e extrato D/SUCESSO 4.304 = R$ 166.875.645,67) |
| RECUSADA | 120 | `rejected` (+ `reason_code` = prefixo de `INFORMACAO_ADICIONAL`: AM04, DS04, AB11, CH16, AC06, FRAD, AB09, AB03, AC03) | COMPROVADO |
| ERRO | 19 | `cancelled` (falha interna pré-liquidação, sem efeito financeiro; motivo preservado em metadata) | COMPROVADO |
| ICOM | 1 | `accepted` (in-flight, nunca final; sinalizar no relatório) | INFERIDO-FORTE |
| AGENDADA | 1 | `pending` | COMPROVADO |

Valor da ordem: `ORDEM_PAGAMENTO` não tem campo de valor (e `MENSAGEM` vem vazia); usar `BLOQUEIO_ORDEM_PAGAMENTO.VALOR` via `ID_ORDEM_PAGAMENTO`, com conferência cruzada no extrato natureza D. Direção `outbound`; `debtor_ispb` = 46026562. Campos NOT NULL de contraparte saem do JSONB da própria ordem/bloqueio; linha que não satisfizer constraint NÃO é projetada e entra no relatório de exceções (nunca inventar identidade financeira).

### 4.2 PIX, anotações de crédito (entrada)

Terminal de `STATUS_ANOTACAO_CREDITO` chaveado por `END_TO_END` (nunca por `NRO_MOVIMENTO`, que é vazio em 100% das linhas AUTORIZADO).

| Terminal | Qtde | Destino `credit_notifications` | Confiança |
|---|---:|---|---|
| EFETIVADO | 8.071 | `processed = true`, `processed_at` = data da efetivação | COMPROVADO |
| CANCELADO | 690 | `processed = false`; motivo (`INFO_ERROS`, ex.: AC07+60605, AG03+60216, AC03+60743) e `COD_DEVOLUCAO_PACS004` preservados nas colunas disponíveis/metadata | COMPROVADO (cancelamento gera devolução ao pagador) |

### 4.3 PIX, bloqueios (hold da ordem)

Terminal de `STATUS_BLOQUEIO_ORDEM_PAGAMENTO` por `END_TO_END`.

| Terminal | Qtde | Destino `balance_blocks.status` | Confiança |
|---|---:|---|---|
| APROVADO | 4.255 | `confirmed` (hold capturado, débito efetivado) | COMPROVADO (motivos 1:1 com a ordem) |
| RECUSADO | 117 | `released` | COMPROVADO |
| ERRO | 20 | `cancelled` | COMPROVADO |

`reference_id`/`reference_type` apontam a ordem legada. `MOTIVO_MED=FRAUD` (14 linhas) preservado.

### 4.4 PIX, extrato (já projetado, sem mudança de dados)

`SUCESSO` = `booked`; `ERRO` = `legacy_erro` (audit-only, jamais sensibiliza saldo); `INDDEVOLUCAO=1` = movimento de devolução (pacs.004), ligado ao original por `END_TO_END_ORIGINAL`. Decisão de produto ratificada por evidência: os 826 ERRO (R$ 24,67M) ficam fora de qualquer visão de saldo; população crítica destacada no relatório: ERRO/devolução 396 linhas (R$ 18,5M) e SUCESSO/devolução 92 (R$ 3,0M).

### 4.5 PIX, devoluções, infrações e MED

- `transaction_returns`: das 488 linhas de extrato com `INDDEVOLUCAO=1` + vínculos de `ANOTACAO_DEVOLUCAO` (19). Status: SUCESSO → `completed`, ERRO → `rejected`. `return_id` = chave legada determinística; `original_end_to_end_id` = `END_TO_END_ORIGINAL`.
- `infraction_reports` (dict): `RELATO_DE_INFRACAO` (391) encaixa exato no enum da cabine: ESTADO CLOSED→`CLOSED` (383), CANCELLED→`CANCELLED` (8); `analysis_result` AGREED (206)/DISAGREED (177). COMPROVADO (domínio DICT do BACEN as-is).
- `refund_requests` (MED): `SOLICITACAO_DEVOLUCAO` (205, todas CLOSED; RESULTADO REJECTED 168 / PARTIALLY_ACCEPTED 30 / TOTALLY_ACCEPTED 7). Destino: schema Ecto do `dict_service` (PENDING/PROCESSING/COMPLETED/CANCELLED/FAILED): CLOSED→`COMPLETED`, resultado preservado em coluna própria/metadata. ATENÇÃO: o repo tem DOIS Ecto schemas divergentes para `refund_requests` (defeito real, registrado na seção 8).
- `MSG_SOLICITACAO_DEVOLUCAO_*`/`MSG_RELATO_DE_INFRACAO_*` (1,8M): >99,9% é polling `List*`; ficam SÓ em staging. Eventos reais (198 CreateRefundRequest, 384 CreateInfractionReport, 12 Acknowledge, 11 Close, 6 Cancel) já cobertos pelas tabelas de entidade.
- `ORDEM_PAGAMENTO_MENSAGEM` (4.373) → `xml_messages` (mesmo caminho das projeções existentes).

### 4.6 PIX, o que NÃO projeta (com justificativa)

- `ENDERECAMENTO`/histórico (chaves DICT): a tabela `cecresa_dict.keys` é propriedade do sync CID vivo; misturar acervo causaria conflito. Criar VIEW tipada `legacy_ab_pix.v_dict_keys` para consulta.
- `MOVIMENTOS_CONTABEIS_*` + `SALDO_CONTA_PI` → `journal_entries` exige `chart_of_accounts` (vazio na cabine). Fica em staging + VIEW tipada; projeção só depois que o plano de contas da cabine for semeado (decisão de produto).
- `cecresa_spi.balances`: GATE. A projeção existente usa upsert destrutivo (`ON CONFLICT (ispb) DO UPDATE`). Passa a exigir flag explícita `--allow-balance-overwrite`; sem a flag, `project` pula balances e avisa.

### 4.7 SPB, mapa composto (STATUSSGR / STATUSSTR / SITUACAO)

| Chave legada | Qtde | Estado canônico da cabine | Confiança |
|---|---:|---|---|
| 5 / 02 / A | 6.816 | mensagem original outbound → `r1_confirmed`; resposta R1/R2/R3 inbound → `confirmed` | COMPROVADO (5/02 = enviada e liquidada no STR) |
| 5 / 04 / A | 9 | `r1_rejected` (conteúdo de tags inválido, comentário literal) | COMPROVADO |
| 5 / 05 / A | 11 | `r1_rejected` (solicitação inválida, comentário literal) | COMPROVADO |
| 5 / 06 / A | 5 | `processing` (pendente no STR; exige reconciliação antes de uso) | COMPROVADO |
| 3 / vazio / A | 105 | `awaiting_approval`→pronta p/ envio; acervo → `created` com nota | COMPROVADO (tela ENVIO usa STATUS=3) |
| 2 ou 1 / vazio | 17 | `created` (pendente de autorização no SGR) | COMPROVADO |
| 9 / * / * | 3 | `r1_rejected` (rejeitada pelo SGR) | COMPROVADO |
| * / * / C | 23 | `cancelled` | COMPROVADO |
| vazio / vazio (origem SPB) | 3.495 | `confirmed` (inbound recebida da rede, nunca passou no SGR local) | INFERIDO-FORTE (correlato 1:1 com respostas R1/R2) |

Direção (sinal limpo, comprovado): `CODSISTEMA_O='SPB'` → `inbound` (7.134, inclui 100% das 6.850 respostas R1/R2/R3); origem EB/GI/SGR → `outbound` (6.967).

Tipo real: extrair `CodMsg` do XML (`CORPO_TRANS_INTERF_PASS`, cobertura 10.461/10.461): STR0008 5.425, STR0008R2 2.973, STR0008R1 2.681, família LPI ~1.960 etc. Corrige `message_type='LEGACY'` (que nem passa no regex do changeset) e destrava grid da tela de Operações, flow filters e extração de valor via `value_tag` do catálogo (1.766 tipos).

Complementos SPB:
- `control_number_clearing` do acervo recebe prefixo `LEGACY-` para nunca colidir com o índice único parcial `(message_type, control_number_clearing) WHERE direction='inbound'` usado pelo fluxo vivo.
- 1 `operation_event` sintético por operação (`event_type='legacy_import'`, com source_db/table/pk) para a timeline não renderizar vazia.
- `message_type_config.direction_flag`: remover a convenção O/I inventada pelo ETL (33+18 linhas) e alinhar à convenção da cabine (E/R/B).
- `TRANS_SISTEMAS` (130): sem CODEVENTO/TIPO_M/valor; entram como acervo auditável com tipo extraído do XML quando existir, sem derivação financeira.
- Estados raros com erro (17 linhas: EGEN1102=8, CODREJEICAO 99=4, EGEN0300=2, 03=2, EGEN1009=1): preservar código em `error_code` da operação.

Pendências declaradas (não inventar): STATUSSTR 03/07, TIPO_M R3, domínio completo de STATUSSPB e TIPO_E 'N'. Não ocorrem nos dados do backup; se surgirem em cargas futuras, mapear para `processing` + relatório.

## 5. Idempotência e rastreabilidade

1. Todas as projeções novas com `ON CONFLICT DO NOTHING`/`NOT EXISTS` sobre chave natural determinística.
2. Popular `legacy_ab_pix.final_links`/`legacy_ab_spb.final_links` (hoje vazias) em TODA projeção: source_pk → target_table/target_pk.
3. Prova obrigatória: segunda execução completa de `project` com delta zero em todas as tabelas destino (exceto `*_audit`, que registra a rodada).
4. `verify` estendido com as novas contagens + conciliação ao centavo por status.

## 6. Execução (local, laboratório)

Ordem: `setup` → `stage --profile all` (exclusões ampliadas) → `project` → `verify` → `project` de novo (delta zero) → relatório v2 em `docs/reports/`.

Ambiente alvo: exclusivamente os containers locais (`monetarie-bak-mssql` + `monetarie-postgres-1`, bancos `cc_pix`/`cc_spb`, schemas `cecresa_*`/staging). O script continua incapaz de atingir HML por construção (docker exec local, schemas locais), e é assim que deve permanecer até o pacote de promoção.

## 7. Pacote de promoção a HML (separado, exige aprovação do dono)

1. Retarget `cecresa_*` → `monetarie_*` + conexão Aurora (reescrita revisada).
2. Gate de `balances` mantido; janela e snapshot de rollback definidos antes.
3. Mudanças de código da cabine SPB para exibição do acervo: KPIs/dashboards filtram por proveniência (`source <> 'legacy_ab_spb'`), tela/aba de acervo read-only, `humanize_state` sem label cru.
4. Decidir exibição das linhas `legacy_erro` do extrato PIX (tela vs auditoria).
5. Reconciliação pós-carga contra os totais deste design.

## 8. Defeitos de código descobertos na fase 1 (entram na revalidação, fase 5)

1. `messages.status_id` com TRÊS mapas divergentes no próprio repo PIX (`status_updater.ex` settled=4 vs `spi_calculator.ex` STLD=7 vs seed; RJCT 5/3/8). Fixar mapa canônico + semear `operation_status_codes`.
2. `refund_requests` com DOIS Ecto schemas conflitantes (dict_service PENDING/... vs shared OPEN/...).
3. HML mon_pix: 11 pacs.008 presas em RCVD (desde 06-25), 7 ACTC com settlement_time preenchido nunca promovidas a final, 5 ACCC com completion_time NULL. Reconciliar com BACEN e fechar.
4. HML mon_spb: 4 LPI0006 inbound presas em `processing` (handler não finaliza), 1 MQRAW failed, 7 operações `error` com `error_code` NULL (dificulta triagem).
5. Core: ZERO transação PIX materializada pós-reset de 07-01; disparar PIX vivo e provar materialização + comprovante (regra #11). As 5 ACCC antigas da cabine não têm mais contraparte no Core: definir baseline de corte 07-01.
6. `cecresa_accounting` vazio e `chart_of_accounts` da cabine sem seed (bloqueia trilha contábil da cabine).