# Legacy Alignment — Full Architecture & Data Plan

> **For Claude:** REQUIRED SUB-SKILL: Use superpowers:executing-plans to implement this plan task-by-task.

**Goal:** Align our database, backend, and frontend with the legacy EvolutionPro architecture — two-table operations model, correct field naming, complete reference data, and full status mapping — to build a reliable, trustworthy SPB system.

**Architecture:** Introduce `spb_operations` as the parent table (one row per financial operation), keep `bacen_messages` as the child table (N messages per operation: outbound, COA, COD, R1, R2, R3). Standardize ISPB naming to `sender_ispb`/`receiver_ispb`. Import all legacy reference data.

**Tech Stack:** Elixir/Phoenix, PostgreSQL, Vue 3/TypeScript, NATS JetStream

---

## Impact Summary

| Area | Files Affected | Risk |
|------|---------------|------|
| New migration (spb_operations) | 1 new migration | LOW — additive |
| ISPB rename (institution_ispb → sender_ispb) | ~50 backend + ~28 frontend | HIGH — must be atomic |
| Reference data import | 4 new seed scripts | LOW — additive |
| LifecycleEngine refactor | 1 file, ~300 lines | MEDIUM — core logic |
| Controller updates | ~5 controllers | MEDIUM — SQL queries |
| Frontend field updates | ~20 Vue files | MEDIUM — field mapping |
| Ecto schema update | 1 file | HIGH — breaks changeset |

---

## SPRINT 1: Schema Foundation — `spb_operations` Table + ISPB Fix

### Task 1.1: Create `spb_operations` Table Migration

**Files:**
- Create: `services/bacen_gateway/priv/repo/migrations/20260210100000_create_spb_operations.exs`

**Step 1: Write migration**

```elixir
defmodule BacenGateway.Repo.Migrations.CreateSpbOperations do
  use Ecto.Migration

  def change do
    # Parent table: one row per financial operation (mirrors legacy spb_tb_pil_operacoes)
    create table(:spb_operations, primary_key: false) do
      add :id, :uuid, primary_key: true, default: fragment("gen_random_uuid()")
      add :message_type, :string, size: 10, null: false        # cd_msg (STR0008, GEN0001)
      add :message_category, :string, size: 5, null: false      # grp_mensagem (STR, GEN, LPI)
      add :status_code, :integer, null: false, default: 0       # st_operacao (legacy int codes)
      add :state, :string, size: 30, null: false, default: "created"  # our string state
      add :direction, :string, size: 10, null: false            # E→outbound, R→inbound
      add :sender_ispb, :string, size: 8, null: false           # ispb_if (our ISPB)
      add :receiver_ispb, :string, size: 8, null: false         # ispb_clearing (destination)
      add :domain, :string, size: 5                              # dom_spb (SPB01, MES01)
      add :control_number, :string, size: 23                     # nr_ctrl_if
      add :control_number_clearing, :string, size: 23            # nr_ctrl_clearing (BACEN's ctrl#)
      add :nuop, :string, size: 23                               # NUOp (BACEN unique)
      add :correlation_id, :string, size: 50                     # correl_id
      add :amount, :decimal, precision: 18, scale: 2, default: 0  # vl_operacao (CRITICAL — was missing)
      add :currency, :string, size: 3, default: "BRL"
      add :xml_content, :text                                    # str_xml (original outbound XML)
      add :settlement_date, :date                                # dt_movto
      add :priority, :string, size: 1                            # nivelpref (char)
      add :integration_level, :string, size: 2                   # nivel_int (B0, etc.)
      add :grid_type, :string, size: 1                           # tp_oper_grid (C=credit, N=neutral)
      add :send_type, :string, size: 1                           # tp_envio
      add :xml_hash, :string, size: 64                           # vl_hash (for dedup)
      add :observation, :string, size: 100                       # ds_observacao
      add :origin_system, :string, size: 100                     # ds_origem
      add :scheduled_at, :naive_datetime                         # dt_agendt
      add :sent_at, :naive_datetime                              # dt_hr_envio (first MQ send)
      add :confirmed_at, :naive_datetime                         # dt_hr_confirmacao
      add :r0_received_at, :naive_datetime
      add :coa_received_at, :naive_datetime
      add :cod_received_at, :naive_datetime
      add :r1_received_at, :naive_datetime
      add :r2_confirmed_at, :naive_datetime
      add :r3_settled_at, :naive_datetime
      add :response_code, :string, size: 10                      # error/response code from R1
      add :error_description, :string, size: 500
      add :is_corrected, :boolean, default: false                # flg_corrigida
      add :is_split, :boolean, default: false                    # flg_desdobrada
      add :is_reprocessing, :boolean, default: false             # flg_reproc
      add :retry_count, :integer, default: 0
      add :created_by_user_id, :integer                          # id_usr_req_gen FK→users
      add :institution_id, :integer                              # id_inst_financ FK→institutions

      timestamps(type: :naive_datetime, inserted_at: :created_at)
    end

    # Indexes matching legacy query patterns
    create index(:spb_operations, [:message_type])
    create index(:spb_operations, [:message_category])
    create index(:spb_operations, [:state])
    create index(:spb_operations, [:status_code])
    create index(:spb_operations, [:sender_ispb])
    create index(:spb_operations, [:receiver_ispb])
    create index(:spb_operations, [:domain])
    create index(:spb_operations, [:nuop], unique: true, where: "nuop IS NOT NULL")
    create index(:spb_operations, [:control_number])
    create index(:spb_operations, [:correlation_id], where: "correlation_id IS NOT NULL")
    create index(:spb_operations, [:settlement_date, :state])
    create index(:spb_operations, [:message_category, :state, :created_at])
    create index(:spb_operations, [:scheduled_at], where: "scheduled_at IS NOT NULL")
    create index(:spb_operations, [:xml_hash], where: "xml_hash IS NOT NULL")
  end
end
```

**Step 2: Run migration locally**

Run: `cd services/bacen_gateway && mix ecto.migrate`
Expected: Table created with 0 rows

**Step 3: Commit**

```bash
git add services/bacen_gateway/priv/repo/migrations/20260210100000_create_spb_operations.exs
git commit -m "feat: create spb_operations table (parent operations — legacy pil_operacoes)"
```

---

### Task 1.2: Add `operation_id` FK on `bacen_messages` + Fix ISPB Naming

**Files:**
- Create: `services/bacen_gateway/priv/repo/migrations/20260210100001_align_bacen_messages_schema.exs`

**Step 1: Write migration**

```elixir
defmodule BacenGateway.Repo.Migrations.AlignBacenMessagesSchema do
  use Ecto.Migration

  def up do
    # 1. Rename institution_ispb → sender_ispb (if institution_ispb exists)
    #    The migration created sender_ispb, but application.ex may have created institution_ispb too
    execute """
    DO $$
    BEGIN
      IF EXISTS (SELECT 1 FROM information_schema.columns
                 WHERE table_name = 'bacen_messages' AND column_name = 'institution_ispb') THEN
        -- Copy data from institution_ispb to sender_ispb where sender_ispb is null
        UPDATE bacen_messages SET sender_ispb = institution_ispb
        WHERE sender_ispb IS NULL AND institution_ispb IS NOT NULL;
      END IF;
    END $$;
    """

    # 2. Copy destination_ispb → receiver_ispb
    execute """
    DO $$
    BEGIN
      IF EXISTS (SELECT 1 FROM information_schema.columns
                 WHERE table_name = 'bacen_messages' AND column_name = 'destination_ispb') THEN
        UPDATE bacen_messages SET receiver_ispb = destination_ispb
        WHERE receiver_ispb IS NULL AND destination_ispb IS NOT NULL;
      END IF;
    END $$;
    """

    # 3. Add missing columns from legacy (msg_ctrl_operacoes)
    execute "ALTER TABLE bacen_messages ADD COLUMN IF NOT EXISTS message_sequence INTEGER DEFAULT 1"
    execute "ALTER TABLE bacen_messages ADD COLUMN IF NOT EXISTS control_number_clearing VARCHAR(23)"
    execute "ALTER TABLE bacen_messages ADD COLUMN IF NOT EXISTS integration_level VARCHAR(2)"
    execute "ALTER TABLE bacen_messages ADD COLUMN IF NOT EXISTS mq_timestamp TIMESTAMP"
    execute "ALTER TABLE bacen_messages ADD COLUMN IF NOT EXISTS origin_system VARCHAR(100)"
    execute "ALTER TABLE bacen_messages ADD COLUMN IF NOT EXISTS amount DECIMAL(18,2) DEFAULT 0"

    # 4. Add FK index for operation linkage
    create_if_not_exists index(:bacen_messages, [:operation_id, :message_sequence],
      name: :idx_bacen_messages_operation_seq)

    # 5. Add FK to spb_operations (soft — no constraint since legacy data exists)
    create_if_not_exists index(:bacen_messages, [:sender_ispb])
    create_if_not_exists index(:bacen_messages, [:receiver_ispb])
  end

  def down do
    # Reverse is not needed — columns are additive
    :ok
  end
end
```

**Step 2: Run migration**

Run: `cd services/bacen_gateway && mix ecto.migrate`
Expected: Columns added, data copied

**Step 3: Commit**

```bash
git add services/bacen_gateway/priv/repo/migrations/20260210100001_align_bacen_messages_schema.exs
git commit -m "feat: align bacen_messages — ISPB data migration + missing columns from legacy"
```

---

### Task 1.3: Create `SpbOperation` Ecto Schema

**Files:**
- Create: `services/bacen_gateway/lib/bacen_gateway/operations/spb_operation.ex`

**Step 1: Write schema**

```elixir
defmodule BacenGateway.Operations.SpbOperation do
  @moduledoc """
  Ecto schema for spb_operations — the parent table for financial operations.

  One SpbOperation has N BacenMessages (outbound, COA, COD, R1, R2, R3).
  Maps to legacy spb_tb_pil_operacoes.
  """
  use Ecto.Schema
  import Ecto.Changeset

  @valid_states ~w(
    created awaiting_approval sent sent_to_mq processing
    coa_received cod_received
    r1_confirmed r1_error r1_rejected
    r2_confirmed r2_rejected
    r3_settled
    cancelled expired failed dead_letter
    confirmed
  )

  @primary_key {:id, Ecto.UUID, autogenerate: true}
  schema "spb_operations" do
    field :message_type, :string
    field :message_category, :string
    field :status_code, :integer, default: 0
    field :state, :string, default: "created"
    field :direction, :string
    field :sender_ispb, :string
    field :receiver_ispb, :string
    field :domain, :string
    field :control_number, :string
    field :control_number_clearing, :string
    field :nuop, :string
    field :correlation_id, :string
    field :amount, :decimal, default: Decimal.new("0")
    field :currency, :string, default: "BRL"
    field :xml_content, :string
    field :settlement_date, :date
    field :priority, :string
    field :integration_level, :string
    field :grid_type, :string
    field :send_type, :string
    field :xml_hash, :string
    field :observation, :string
    field :origin_system, :string
    field :scheduled_at, :naive_datetime
    field :sent_at, :naive_datetime
    field :confirmed_at, :naive_datetime
    field :r0_received_at, :naive_datetime
    field :coa_received_at, :naive_datetime
    field :cod_received_at, :naive_datetime
    field :r1_received_at, :naive_datetime
    field :r2_confirmed_at, :naive_datetime
    field :r3_settled_at, :naive_datetime
    field :response_code, :string
    field :error_description, :string
    field :is_corrected, :boolean, default: false
    field :is_split, :boolean, default: false
    field :is_reprocessing, :boolean, default: false
    field :retry_count, :integer, default: 0
    field :created_by_user_id, :integer
    field :institution_id, :integer

    timestamps(type: :naive_datetime, inserted_at: :created_at)
  end

  def changeset(operation, attrs) do
    operation
    |> cast(attrs, [
      :message_type, :message_category, :status_code, :state, :direction,
      :sender_ispb, :receiver_ispb, :domain, :control_number, :control_number_clearing,
      :nuop, :correlation_id, :amount, :currency, :xml_content, :settlement_date,
      :priority, :integration_level, :grid_type, :send_type, :xml_hash,
      :observation, :origin_system, :scheduled_at, :sent_at, :confirmed_at,
      :r0_received_at, :coa_received_at, :cod_received_at, :r1_received_at,
      :r2_confirmed_at, :r3_settled_at, :response_code, :error_description,
      :is_corrected, :is_split, :is_reprocessing, :retry_count,
      :created_by_user_id, :institution_id
    ])
    |> validate_required([:message_type, :message_category, :state, :direction, :sender_ispb, :receiver_ispb])
    |> validate_inclusion(:state, @valid_states)
    |> validate_format(:sender_ispb, ~r/^\d{8}$/, message: "must be 8-digit ISPB")
    |> validate_format(:receiver_ispb, ~r/^\d{8}$/, message: "must be 8-digit ISPB")
    |> validate_format(:message_type, ~r/^[A-Z]{3}\d{4}[A-Z]?\d?$/i)
    |> validate_inclusion(:direction, ~w(outbound inbound))
  end

  def valid_states, do: @valid_states
end
```

**Step 2: Commit**

```bash
git add services/bacen_gateway/lib/bacen_gateway/operations/spb_operation.ex
git commit -m "feat: add SpbOperation Ecto schema (parent operations table)"
```

---

### Task 1.4: Update `BacenMessage` Schema — Use `sender_ispb`/`receiver_ispb`

**Files:**
- Modify: `services/bacen_gateway/lib/bacen_gateway/messages/bacen_message.ex`

**Step 1: Update schema fields**

Replace `institution_ispb` → `sender_ispb` and `destination_ispb` → `receiver_ispb`.
Add missing fields: `message_sequence`, `control_number_clearing`, `integration_level`, `mq_timestamp`, `origin_system`, `amount`.

Keep both old and new column names temporarily via `source:` option for backward compatibility during transition:
```elixir
field :sender_ispb, :string          # was institution_ispb
field :receiver_ispb, :string        # was destination_ispb
field :message_sequence, :integer, default: 1
field :control_number_clearing, :string
field :integration_level, :string
field :mq_timestamp, :naive_datetime
field :origin_system, :string
field :amount, :decimal, default: Decimal.new("0")
```

Update changeset to use new field names. Update validations.

**Step 2: Commit**

```bash
git commit -m "feat: BacenMessage schema — sender_ispb/receiver_ispb + missing legacy fields"
```

---

### Task 1.5: Update `application.ex` — Remove Old ISPB Columns, Add New Ones

**Files:**
- Modify: `services/bacen_gateway/lib/bacen_gateway/application.ex`

**Step 1: Update `ensure_lifecycle_columns/0`**

Remove `destination_ispb` from the ALTER TABLE list (now in migration).
Add new columns: `message_sequence`, `control_number_clearing`, `integration_level`, `mq_timestamp`, `origin_system`, `amount`.
Update the backfill query to populate `receiver_ispb` instead of `destination_ispb`.

**Step 2: Commit**

```bash
git commit -m "fix: application.ex — align lifecycle columns with new ISPB naming"
```

---

### Task 1.6: Rename `institution_ispb` → `sender_ispb` Across Backend (50 files)

**Files:**
- All `.ex` files containing `institution_ispb` or `destination_ispb`

**Step 1: Search and replace in all backend files**

Use grep to find all occurrences. Replace:
- `institution_ispb` → `sender_ispb` (in Elixir code, SQL queries)
- `destination_ispb` → `receiver_ispb` (in Elixir code, SQL queries)

Key files:
- `admin_controller.ex` (~15 occurrences)
- `messages_controller.ex` (~5 occurrences)
- `operations_controller.ex` (~8 occurrences)
- `lifecycle_engine.ex` (~6 occurrences)
- `bcmsg_envelope.ex` (~4 occurrences)
- All seed files (~10 occurrences)

**IMPORTANT**: SQL queries in controllers that reference DB columns must keep `sender_ispb`/`receiver_ispb` (matching the migration columns, NOT `institution_ispb`).

**Step 2: Compile and verify**

Run: `cd services/bacen_gateway && mix compile --warnings-as-errors`
Expected: 0 errors

**Step 3: Commit**

```bash
git commit -m "refactor: rename institution_ispb→sender_ispb, destination_ispb→receiver_ispb across backend"
```

---

### Task 1.7: Rename ISPB Fields Across Frontend (28 files)

**Files:**
- All `.vue` and `.ts` files containing `institution_ispb` or `destination_ispb`

**Step 1: Search and replace**

In `api.ts` types, views, components:
- `institution_ispb` → `sender_ispb`
- `destination_ispb` → `receiver_ispb`

**Step 2: TypeScript check**

Run: `cd frontend-vue && npx vue-tsc --build --force`
Expected: 0 errors

**Step 3: Commit**

```bash
git commit -m "refactor: rename ISPB fields in frontend to match backend schema"
```

---

## SPRINT 2: Reference Data Import from Legacy SQL Server

### Task 2.1: Import 5,314 Error Codes from Legacy

**Files:**
- Create: `services/bacen_gateway/priv/repo/seeds/030_legacy_error_codes.exs`

**Step 1: Extract from SQL Server**

```bash
docker exec spbcabin-mssql /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa \
  -P 'StrongP@ssw0rd123' -C -d EvolutionPro \
  -Q "SELECT cd_erro, ds_erro, dt_ativacao, dt_desativacao FROM spb_tb_gen_erro_bc ORDER BY cd_erro" \
  -W -s"|" -o /tmp/legacy_errors.csv
```

**Step 2: Write seed script**

Script reads the CSV, upserts into `bacen_error_codes` table using `ON CONFLICT (code) DO UPDATE`.

**Step 3: Run seed and commit**

```bash
git commit -m "data: import 5,314 BACEN error codes from legacy EvolutionPro"
```

---

### Task 2.2: Import 9,473 Tariff Records from Legacy

**Files:**
- Create: `services/bacen_gateway/priv/repo/seeds/031_legacy_tariffs.exs`

**Step 1: Extract tariffs**

Extract all columns from `spb_tb_gen_tarifas_bc` (11 cols: cd_msg, time ranges, amounts, flags).

**Step 2: Extend `bacen_tariffs` table if needed**

Current table has very few columns. May need migration to add: `hr_de`, `hr_ate`, `qtd_de`, `qtd_ate`, `dt_inicio`, `fl_d0`, `vl_emissor`, `vl_destinatario`, `flg_sitraf`.

**Step 3: Write seed and run**

```bash
git commit -m "data: import 9,473 tariff records from legacy"
```

---

### Task 2.3: Import 829 Holidays from Legacy

**Files:**
- Create: `services/bacen_gateway/priv/repo/seeds/032_legacy_holidays.exs`

**Step 1: Extract**

```sql
SELECT cd_grade, hr_abertura, hr_fechamento, id_usr_ult_alt FROM spb_tb_gen_feriados
```

**Step 2: Upsert into `bacen_holidays`**

**Step 3: Commit**

```bash
git commit -m "data: import 829 holidays from legacy"
```

---

### Task 2.4: Import 12 Clearings + 44 Message Groups

**Files:**
- Create: `services/bacen_gateway/priv/repo/seeds/033_legacy_clearings_groups.exs`

**Step 1: Extract clearings**

All 12 clearings with: id, ispb, name, abbreviation, status, MQ sequence, broker count.

**Step 2: Extract message groups**

All 44 groups with: code, description, domain, 24x7 flag, internal flag.

**Step 3: Upsert and commit**

```bash
git commit -m "data: import 12 clearings + 44 message groups from legacy"
```

---

### Task 2.5: Import 1,448 Message Type Configurations

**Files:**
- Create: `services/bacen_gateway/priv/repo/migrations/20260210100002_create_message_type_config.exs`
- Create: `services/bacen_gateway/priv/repo/seeds/034_legacy_message_config.exs`

**Step 1: Create `message_type_config` table**

This is the most important missing table — it maps to `spb_tb_gen_msg_detalhe` (49 cols) and drives ALL message processing behavior in legacy.

```elixir
create table(:message_type_config, primary_key: false) do
  add :message_type, :string, size: 10, primary_key: true  # cd_msg
  add :clearing_id, :integer                                 # id_clearing
  add :message_group, :string, size: 3                       # grp_mensagem
  add :direction_flag, :string, size: 1                      # flag_sentido (E/R)
  add :domain, :string, size: 5                              # dom_spb
  add :grid_type, :string, size: 1                           # tp_oper_grid
  add :debit_credit_flag, :string, size: 1                   # flag_db_cr
  add :financial_flag, :string, size: 1                      # flag_financ
  add :default_status, :integer                               # st_operacao_padrao
  add :finalize_on_cod, :boolean, default: false             # finaliza_cod
  add :allows_split, :boolean, default: false                # flg_desdobro_ativo
  add :allows_retroactive, :boolean, default: false          # fl_permite_data_retroativa
  add :allows_duplicate_ctrl, :boolean, default: false       # flag_permite_nrctrlif_duplicado
  add :is_large_xml, :boolean, default: false                # flg_xml_grande
  add :handles_accounting, :string, size: 10                 # flg_trata_contab
  add :mq_sequence, :string, size: 2                         # seq_obj_mqs
  add :is_return_alert, :boolean, default: false             # flg_trata_alerta_dev
  add :is_refund, :boolean, default: false                   # flg_devolucao
  # Tag references for value extraction from XML
  add :value_tag, :string, size: 500                         # id_tag_vlr
  add :confirmed_value_tag, :string, size: 500               # id_tag_vlr_confirmado
  add :rejected_value_tag, :string, size: 500                # id_tag_vlr_rejeitado
  add :ctrl_number_tag, :string, size: 100                   # id_tag_numctrl
  add :settlement_date_tag, :string, size: 100               # id_tag_dtmovto

  timestamps(type: :naive_datetime)
end
```

**Step 2: Extract from legacy and seed**

**Step 3: Commit**

```bash
git commit -m "feat: message_type_config table + import 1,448 configs from legacy"
```

---

### Task 2.6: Import 21,417 XML Tag Definitions

**Files:**
- Create: `services/bacen_gateway/priv/repo/migrations/20260210100003_create_message_tag_definitions.exs`
- Create: `services/bacen_gateway/priv/repo/seeds/035_legacy_tag_definitions.exs`

**Step 1: Create table**

```elixir
create table(:message_tag_definitions) do
  add :message_type, :string, size: 10, null: false  # cd_msg
  add :tag_sequence, :integer, null: false            # seq_tag
  add :tag_name, :string, size: 100, null: false      # id_tag
  add :min_occurs, :string, size: 1                   # min_occurs
  add :max_occurs, :string, size: 3                   # max_occurs
  add :is_enabled, :boolean, default: true            # habilitada
  add :is_exclusive, :boolean, default: false         # ou_exclusivo
end
```

**Step 2: Extract and seed**

**Step 3: Commit**

```bash
git commit -m "feat: message_tag_definitions table + import 21,417 tags from legacy"
```

---

## SPRINT 3: Status Mapping + Missing Legacy Statuses

### Task 3.1: Add Missing 28 Status Codes to `operation_status_catalog`

**Files:**
- Create: `services/bacen_gateway/priv/repo/seeds/036_legacy_status_codes.exs`

**Step 1: Write seed with all 58 legacy statuses**

Map legacy integer codes to our catalog, adding the ~28 missing ones:
- 11: Retirada pela E.E.
- 14: IF Inoperante
- 17: Estouro da Reserva
- 19: Cancelando E.E.
- 20: Pendente Operacional
- 22: Em Alteracao
- 23: Apartado
- 31: MSG Desdobrada
- 34: Pre Registro
- 35: Remessa Atrasada
- 38: Em Transito
- 44: Envio Confirmado
- 994-2002: All legacy cancellation/block codes

Add `legacy_code` and `hex_color` columns to `operation_status_catalog` to store legacy mapping.

**Step 2: Commit**

```bash
git commit -m "data: add 28 missing legacy status codes to operation_status_catalog"
```

---

### Task 3.2: Create Legacy ↔ Current Status Mapping Table

**Files:**
- Create: `services/bacen_gateway/priv/repo/migrations/20260210100004_create_status_mapping.exs`

**Step 1: Create table**

```elixir
create table(:legacy_status_mapping, primary_key: false) do
  add :legacy_code, :integer, primary_key: true     # st_operacao from legacy
  add :legacy_description, :string, size: 100
  add :current_state, :string, size: 30             # our string state
  add :current_status_code, :integer                 # our catalog code
  add :is_final, :boolean, default: false
  add :representation, :string, size: 1             # I=intermediate, C=confirmed, R=rejected
  add :hex_color, :string, size: 7                  # UI color from legacy
end
```

Seed with all 58 mappings from the comparison document (Section 5).

**Step 2: Commit**

```bash
git commit -m "feat: legacy_status_mapping table — maps 58 legacy codes to current states"
```

---

## SPRINT 4: LifecycleEngine + Backend Refactoring

### Task 4.1: Refactor LifecycleEngine for Two-Table Model

**Files:**
- Modify: `services/bacen_gateway/lib/bacen_gateway/lifecycle_engine.ex`

**Step 1: Update `process_outgoing/3`**

Instead of only updating `bacen_messages`, now:
1. INSERT into `spb_operations` (parent)
2. UPDATE `bacen_messages` with `operation_id` pointing to parent
3. Keep all existing event/tracking logic

```elixir
def process_outgoing_impl(message_id, message_type, domain, opts) do
  operation_id = opts[:operation_id] || Ecto.UUID.generate()
  now = NaiveDateTime.utc_now() |> NaiveDateTime.truncate(:second)

  # 1. Create parent operation
  Repo.query("""
    INSERT INTO spb_operations (id, message_type, message_category, state, direction,
      sender_ispb, receiver_ispb, domain, settlement_date, created_at, updated_at)
    SELECT $1, message_type, COALESCE(message_category, substring(message_type, 1, 3)),
      'created', COALESCE(direction, 'outbound'),
      COALESCE(sender_ispb, '18189547'), COALESCE(receiver_ispb, '00038166'),
      $2, CURRENT_DATE, $3, $3
    FROM bacen_messages WHERE id = $4
    ON CONFLICT (id) DO NOTHING
  """, [uuid_bin(operation_id), domain, now, message_id])

  # 2. Link message to operation
  Repo.query("""
    UPDATE bacen_messages
    SET state = 'created', operation_id = $1, domain = $2, message_sequence = 1, updated_at = $3
    WHERE id = $4
  """, [uuid_bin(operation_id), domain, now, message_id])

  # ... rest unchanged
end
```

**Step 2: Update `process_coa/2`, `process_cod/2`, `process_r1/3`**

Each now also updates `spb_operations` timestamps + state.

**Step 3: Compile and test**

Run: `cd services/bacen_gateway && mix compile`
Run lifecycle test: `curl -X POST https://spbapi-dev.fluxiq.com.br/api/admin/test/lifecycle`

**Step 4: Commit**

```bash
git commit -m "refactor: LifecycleEngine uses two-table model (spb_operations + bacen_messages)"
```

---

### Task 4.2: Update AdminController — Transactions + Operations Queries

**Files:**
- Modify: `services/bacen_gateway/lib/bacen_gateway_web/controllers/admin_controller.ex`

**Step 1: Update `list_transactions`**

Query `spb_operations` instead of `bacen_messages` for the transactions view:

```sql
SELECT id, message_type, message_category, state, status_code,
       sender_ispb, receiver_ispb, domain, amount, nuop, control_number,
       settlement_date, created_at, updated_at
FROM spb_operations
WHERE message_category = ANY($1)
ORDER BY created_at DESC
LIMIT $2
```

**Step 2: Update `test_lifecycle`**

Create operation in `spb_operations` FIRST, then create child message.

**Step 3: Update all references to `institution_ispb`/`destination_ispb` in SQL queries**

**Step 4: Commit**

```bash
git commit -m "refactor: AdminController queries spb_operations for transactions"
```

---

### Task 4.3: Update MessagesController, OperationsController, DashboardController

**Files:**
- Modify: `services/bacen_gateway/lib/bacen_gateway_web/controllers/messages_controller.ex`
- Modify: `services/bacen_gateway/lib/bacen_gateway_web/controllers/operations_controller.ex`
- Modify: `services/bacen_gateway/lib/bacen_gateway_web/controllers/dashboard_controller.ex`

**Step 1: Update all SQL queries**

Replace `institution_ispb` → `sender_ispb`, `destination_ispb` → `receiver_ispb`.
For operations endpoints, query `spb_operations` and join with `bacen_messages` when message-level detail is needed.

**Step 2: Update dashboard stats**

Stats queries should aggregate from `spb_operations` for operation-level counts.

**Step 3: Compile and test**

**Step 4: Commit**

```bash
git commit -m "refactor: controllers use sender_ispb/receiver_ispb + spb_operations"
```

---

### Task 4.4: Backfill `spb_operations` from Existing `bacen_messages`

**Files:**
- Create: `services/bacen_gateway/priv/repo/seeds/037_backfill_operations.exs`

**Step 1: Write backfill script**

For each unique `operation_id` in `bacen_messages`, insert a row into `spb_operations` by selecting the FIRST message (lowest `message_sequence` or `created_at`).

```sql
INSERT INTO spb_operations (id, message_type, message_category, state, direction,
  sender_ispb, receiver_ispb, domain, xml_content, settlement_date, created_at, updated_at)
SELECT DISTINCT ON (operation_id)
  operation_id, message_type,
  COALESCE(message_category, substring(message_type, 1, 3)),
  state, COALESCE(direction, 'outbound'),
  COALESCE(sender_ispb, '18189547'),
  COALESCE(receiver_ispb, '00038166'),
  domain, xml_content, CURRENT_DATE, created_at, updated_at
FROM bacen_messages
WHERE operation_id IS NOT NULL
ORDER BY operation_id, created_at ASC
ON CONFLICT (id) DO NOTHING
```

For messages WITHOUT operation_id, generate one and create the parent:

```sql
UPDATE bacen_messages SET operation_id = gen_random_uuid()
WHERE operation_id IS NULL
```

Then insert parents for those too.

**Step 2: Commit**

```bash
git commit -m "data: backfill spb_operations from existing bacen_messages"
```

---

## SPRINT 5: Frontend Alignment

### Task 5.1: Update TypeScript Types

**Files:**
- Modify: `frontend-vue/src/services/api.ts`

**Step 1: Add `SpbOperation` type**

```typescript
export interface SpbOperation {
  id: string
  message_type: string
  message_category: string
  status_code: number
  state: string
  direction: string
  sender_ispb: string
  receiver_ispb: string
  domain: string
  control_number: string
  nuop: string
  amount: number
  currency: string
  settlement_date: string
  priority: string
  observation: string
  created_at: string
  updated_at: string
  // Lifecycle timestamps
  sent_at: string | null
  confirmed_at: string | null
  coa_received_at: string | null
  cod_received_at: string | null
  r1_received_at: string | null
  r2_confirmed_at: string | null
  r3_settled_at: string | null
  response_code: string | null
  error_description: string | null
}
```

**Step 2: Update `Message` and `Transaction` types**

Replace `institution_ispb` → `sender_ispb`, `destination_ispb` → `receiver_ispb`.

**Step 3: TS check**

Run: `cd frontend-vue && npx vue-tsc --build --force`

**Step 4: Commit**

```bash
git commit -m "feat: frontend types — SpbOperation + ISPB field rename"
```

---

### Task 5.2: Update TransactionsView to Use `spb_operations`

**Files:**
- Modify: `frontend-vue/src/apps/admin/views/TransactionsView.vue`
- Modify: `frontend-vue/src/apps/operator/views/TransactionsView.vue`

**Step 1: Update field references**

- `from_account` → `sender_ispb`
- `to_account` → `receiver_ispb`
- Add `amount` column to table
- Add `status_code` for legacy status display
- Use `hex_color` from status mapping for row coloring

**Step 2: Commit**

```bash
git commit -m "feat: TransactionsView uses spb_operations with amount + status colors"
```

---

### Task 5.3: Update MessagesView + All Remaining Views

**Files:**
- Modify: all Vue files containing `institution_ispb` or `destination_ispb`

**Step 1: Global find/replace across 28 files**

**Step 2: TS check**

**Step 3: Commit**

```bash
git commit -m "refactor: frontend ISPB field rename across all views"
```

---

## SPRINT 6: Drop Empty Operation Tables + Cleanup

### Task 6.1: Drop 12 Empty Operation-Specific Tables

**Files:**
- Create: `services/bacen_gateway/priv/repo/migrations/20260210100005_drop_empty_operation_tables.exs`

**Step 1: Write migration**

Drop these EMPTY tables that legacy doesn't have (everything goes through `spb_operations`):
- `str_transfers`
- `lpi_operations`
- `settlements`
- `forex_operations`
- `securities_operations`
- `cash_operations`
- `cir_operations`
- `slb_operations`
- `sme_operations`
- `custody_operations`
- `fixed_income_operations`
- `other_operations`

**Step 2: Remove any remaining references in controllers/seeds**

**Step 3: Commit**

```bash
git commit -m "cleanup: drop 12 empty operation-specific tables (replaced by spb_operations)"
```

---

### Task 6.2: Clean Up `application.ex` — Remove ALTER TABLE Hacks

**Files:**
- Modify: `services/bacen_gateway/lib/bacen_gateway/application.ex`

**Step 1: Remove `ensure_lifecycle_columns/0`**

All columns should now come from proper migrations. Remove the ALTER TABLE IF NOT EXISTS hacks and the backfill query.

**Step 2: Commit**

```bash
git commit -m "cleanup: remove ALTER TABLE hacks from application.ex (proper migrations now)"
```

---

## SPRINT 7: Build, Deploy, Verify

### Task 7.1: Run Full Backend Compilation

Run: `cd services/bacen_gateway && mix compile --warnings-as-errors`
Expected: 0 errors, 0 warnings

### Task 7.2: Run Full Frontend TypeScript Check

Run: `cd frontend-vue && npx vue-tsc --build --force`
Expected: 0 errors

### Task 7.3: Deploy Backend

```bash
cd services/bacen_gateway && gcloud builds submit --config=cloudbuild.yaml \
  --substitutions=SHORT_SHA=$(git rev-parse --short HEAD) --project=fluxiqbr
```

### Task 7.4: Deploy Frontend

```bash
cd frontend-vue && gcloud builds submit --config=cloudbuild.yaml \
  --substitutions=SHORT_SHA=$(git rev-parse --short HEAD) --project=fluxiqbr
```

### Task 7.5: Verify E2E

1. Run lifecycle test: `POST /api/admin/test/lifecycle`
2. Check `spb_operations` has parent row
3. Check `bacen_messages` has child messages linked by `operation_id`
4. Check transactions view shows data with amounts and ISPB fields
5. Check messages view shows correct sender/receiver ISPB

---

## Dependency Graph

```
Sprint 1 (Schema Foundation)
  ├── Task 1.1: Create spb_operations table
  ├── Task 1.2: Align bacen_messages (ISPB data migration)
  ├── Task 1.3: SpbOperation Ecto schema
  ├── Task 1.4: Update BacenMessage schema
  ├── Task 1.5: Update application.ex
  ├── Task 1.6: Rename ISPB across backend (50 files)
  └── Task 1.7: Rename ISPB across frontend (28 files)

Sprint 2 (Reference Data) — can run IN PARALLEL with Sprint 3
  ├── Task 2.1: Import 5,314 error codes
  ├── Task 2.2: Import 9,473 tariffs
  ├── Task 2.3: Import 829 holidays
  ├── Task 2.4: Import 12 clearings + 44 groups
  ├── Task 2.5: Import 1,448 message type configs (NEW TABLE)
  └── Task 2.6: Import 21,417 XML tag definitions (NEW TABLE)

Sprint 3 (Status Mapping)
  ├── Task 3.1: Add 28 missing status codes
  └── Task 3.2: Legacy ↔ Current status mapping table

Sprint 4 (Backend Refactoring) — DEPENDS on Sprint 1
  ├── Task 4.1: LifecycleEngine two-table model
  ├── Task 4.2: AdminController updates
  ├── Task 4.3: Other controller updates
  └── Task 4.4: Backfill spb_operations from existing data

Sprint 5 (Frontend) — DEPENDS on Sprint 4
  ├── Task 5.1: TypeScript types
  ├── Task 5.2: TransactionsView
  └── Task 5.3: All remaining views

Sprint 6 (Cleanup) — DEPENDS on Sprint 5
  ├── Task 6.1: Drop 12 empty tables
  └── Task 6.2: Remove application.ex hacks

Sprint 7 (Deploy + Verify) — DEPENDS on ALL
  ├── Task 7.1-7.2: Compile checks
  ├── Task 7.3-7.4: Deploy
  └── Task 7.5: E2E verification
```

---

## Risk Mitigation

| Risk | Mitigation |
|------|-----------|
| ISPB rename breaks 50+ backend files | Atomic commit — compile check before commit |
| Backfill creates duplicate operations | `ON CONFLICT (id) DO NOTHING` on all inserts |
| Legacy data extraction fails | SQL Server container stable, tested extraction commands |
| Frontend breaks on field rename | Global find/replace + `vue-tsc --build` verification |
| Migration fails on Cloud SQL | All migrations use `IF NOT EXISTS` / `ADD COLUMN IF NOT EXISTS` |
| `application.ex` ALTER TABLE conflicts | Remove ALTER TABLE hacks AFTER migrations are deployed |

---

## Estimated Effort

| Sprint | Tasks | Effort |
|--------|-------|--------|
| Sprint 1: Schema Foundation | 7 tasks | ~2 hours |
| Sprint 2: Reference Data | 6 tasks | ~1.5 hours |
| Sprint 3: Status Mapping | 2 tasks | ~30 min |
| Sprint 4: Backend Refactoring | 4 tasks | ~2 hours |
| Sprint 5: Frontend | 3 tasks | ~1 hour |
| Sprint 6: Cleanup | 2 tasks | ~30 min |
| Sprint 7: Deploy + Verify | 5 tasks | ~30 min |
| **TOTAL** | **29 tasks** | **~8 hours** |

---

*This plan addresses ALL findings from the legacy database comparison (2026-02-09): architecture (two-table model), ISPB naming crisis (4 columns → 2), 25 missing columns from pil_operacoes, 13 missing columns from msg_ctrl_operacoes, 5,229 missing error codes, 9,466 missing tariffs, 779 missing holidays, 14 missing message groups, 4 missing clearings, 1,448 missing message type configs, 21,417 missing XML tag definitions, 28 missing status codes, and 12 empty tables to drop.*