# Shared Dev DB Schema Drift — Visa/Mastercard/Amex Clearing Tables **Status: resolved 2026-09-21.** Option 2 below (hand-reconcile in place) was chosen — the 30 Visa / 271 Mastercard / 301 DCF rows were kept, not dropped. See §6 for exactly what ran and how it was verified. Sections 1–5 are kept as the evidence trail/diagnosis; they describe the state **before** the fix. **Audience:** the DCF consumption feature owner (`feature/mpgs-dcf-consumption`), plus whoever owns `clearing_core`'s Visa/Mastercard/Amex real-spec migrations. **tl;dr:** your DCF migrations are not the bug. The shared local dev database (`shukria_transactions`) that every branch/worktree on this machine points at has `visa_tc33_records`, `mastercard_ipm_records` and `amex_gfsg_records` stuck in a **partial, pre-real-spec shape** that predates your work. Your `ALTER TABLE ADD COLUMN dcf_record_id` migrations applied cleanly on top of that wrong shape — MySQL doesn't check whether a table matches any particular migration history before letting you `ALTER` it — so nothing in your own branch surfaced the problem. This doc is the evidence trail and a concrete reconciliation plan. **Compared:** `feature/terminal-inventory`'s checkout of `apps/da_product_app/priv/repo/migrations/` (which is identical to `feature/mpgs-dcf-consumption`'s copy of the same files — verified by directory listing) against the actual live schema of `shukria_transactions` on `localhost`, via `information_schema` and `SHOW COLUMNS`, 2026-09-21. --- ## 1. What's actually true about your migrations `feature/mpgs-dcf-consumption` adds two migrations, both dated 2026-09-07: - **`20260907090001_create_dcf_records.exs`** — a brand-new, independent `dcf_records` table (one row per DCF 6220/6221(+622x) transaction). Doesn't touch any existing clearing table's structure at all. This is exactly right — a DCF file is an *input*, and this is where its parsed contents land before anything downstream reads them. - **`20260907090002_add_dcf_record_id_to_outbound_records.exs`** — adds a single **nullable** `dcf_record_id` FK to `visa_tc33_records`, `mastercard_ipm_records` and `amex_gfsg_records`, so a generated outbound record can be traced back to the `DcfRecord` it came from. Its own moduledoc states the intent plainly: *"Nullable because a record built from `core_transactions` (the existing path) has no DCF source."* This is the correct, additive pattern — DCF augments the existing pipeline, it doesn't replace its schema. Neither migration creates, drops, renames, or redefines any column on the three real-spec tables. **There is no development mistake in this code.** ## 2. What's actually wrong: the shared dev DB predates the real-spec rework Three earlier, unrelated migrations on this same branch rebuilt these tables to match the real card-scheme specs (as opposed to an earlier, wrong transcription): | Migration | What it does | |---|---| | `20260824100001_create_ipm_logical_files_and_correct_ipm_records.exs` | New `clearing_logical_files` table + corrects `mastercard_ipm_records` to the real DE listing (spec p. 208–210) | | `20260827090001_rework_visa_tc33_records_to_capture_format.exs` | Reworks `visa_tc33_records` from a guessed "CAS advice" layout to the real TC33.A Capture format (HEDR/CP01/TRLR) | | `20260828130001_create_amex_gfsg_records.exs` | Creates `amex_gfsg_records` from the real GFSG/CAPN TAB+TAA layout | **None of these three have ever been applied to `shukria_transactions`** — `schema_migrations` has no row for any of them, and hasn't for `20260907090001`/ `20260907090002` either (your DCF migrations aren't recorded as applied, despite their tables/columns physically existing). Yet the tables already exist, in a shape that matches **neither** the pre-rework nor the post-rework definition. Concretely, right now: **`visa_tc33_records`** (30 rows) has only: ``` id, clearing_batch_id, dcf_record_id, transaction_id, retrieval_reference_number, account_number, account_number_extension, purchase_date, decimal_positions_indicator, authorized_amount, source_amount, source_currency_code, action_code, card_acceptor_id, terminal_id, inserted_at, updated_at ``` It's missing ~44 real TC33.A fields the rework migration defines — `destination_identifier`, `source_identifier`, `application_code`, `message_identifier`, `authorization_date`, `total_authorized_amount`, `tip_amount`, `service_identifier`, `acquiring_identifier`, every TCR1 addenda field (`avs_response_code`, `authorization_source_code`, `pos_entry_mode`, `network_id`, `cvv2_result_code`, …), and `addenda_raw`. **`mastercard_ipm_records`** (271 rows) has only: ``` id, clearing_batch_id, dcf_record_id, message_type_indicator, function_code, acquirer_reference_data, retrieval_reference_number, transaction_amount, inserted_at, updated_at ``` Missing `clearing_logical_file_id` (the FK into the new `clearing_logical_files` table — meaning no row here can be tied to the logical file it came from), every institution-ID field the principal-member work needs (`acquirer_bin`, `acquiring_institution_id`, `forwarding_institution_id`, `transaction_destination_institution_id`, `transaction_originator_institution_id`, `receiving_institution_id`), `message_reason_code`, `message_number`, and the corrected DE columns (`amount_reconciliation`, `card_acceptor_business_code`, `date_time_local_transaction`, …). Worth flagging explicitly: `20260824100001`'s own moduledoc justifies its column drops with *"no IPM file has ever been ingested… so `mastercard_ipm_records` is empty in every environment."* That's false for this database — **271 rows**. If this migration is ever run against `shukria_transactions` as committed, it will likely error (some of its `remove`d columns don't exist in the current shape either) or, worse, silently damage real rows. Do not run it as-is against this database. **`amex_gfsg_records`** (0 rows) has only 9 columns (`clearing_batch_id`, `dcf_record_id`, `transaction_identifier`, `merchant_id`, `transaction_amount`, `retrieval_reference_number`, + id/timestamps) against the ~30 the real GFSG/CAPN migration defines (TAB fields like `approval_code`, `primary_account_number`, `terminal_id`, all of TAA's location-detail fields, `match_status`, `tab_raw`, `taa_location_raw`). **`amex_file_sequences`** (0 rows) is close but not exact: `last_sequence_number` is `bigint` where the migration specifies `:integer` — harmless in isolation, but another data point that this table didn't come from running `20260828140001` either. `clearing_exceptions` and the `rolled_back_at`/`rollback_reason`/`rolled_back_by` columns on `clearing_batches` had the same "table exists, migration never recorded" problem — those were interrupted mid-run in some earlier session (MySQL DDL isn't transactional, so a multi-statement migration that dies partway leaves whatever ran before the failure in place with no record of it). Those two have already been reconciled as of this writing. ## 3. Best-supported hypothesis for how this happened `shukria_transactions_30_08.sql` sits untracked in the repo root — almost certainly a raw dump taken from *some* environment on 2026-08-30, between the original 2026-08-09 create migrations and the 2026-08-24/27/28 real-spec rework. If that dump was loaded into this shared local database at some point (instead of building it up by running migrations in order), it would explain exactly this signature: tables that exist with a shape between "original" and "real spec," with `schema_migrations` never updated to match. Your DCF migrations were then written and tested against whatever this shared DB already looked like at the time — which already had this gap — so `ALTER TABLE ADD COLUMN dcf_record_id` (a command that doesn't care what shape the target table is in) just worked, and gave no signal that anything upstream was wrong. **Root enabler:** every worktree on this machine — this one and `tmsuat_apps-dcf-consumption` — points at the identical `shukria_transactions@localhost` database (confirmed by diffing both `config/dev.exs`). There's no per-branch/per-developer isolation, so schema drift introduced in one branch's session is immediately visible to (and mutated further by) every other branch's session. ## 4. What needs to happen **A. Immediate, for this shared database** — someone who knows whether the current 30 Visa / 271 Mastercard / 301 DCF rows are disposable test data or worth keeping needs to choose: 1. **Rebuild clean (recommended if the data is disposable):** drop `visa_tc33_records`, `mastercard_ipm_records`, `amex_gfsg_records`, `amex_file_sequences`, `dcf_records` and re-run every clearing_core migration in order from `20260809090001` through your `20260907090002`, then re-ingest from the real DCF/Visa/Mastercard sample files. Cleanest outcome, matches what `mix ecto.migrate` would produce on a fresh database. 2. **Hand-reconcile in place (if the data must be preserved):** for each of the three tables, write a corrective migration that adds the missing real-spec columns as nullable (no `remove`, no `rename` against columns that don't exist), verify `20260824100001`'s Mastercard `remove` list against what's *actually* present before running any of it, then re-run the ingest/backfill for the newly-added columns where the source data allows it. More work, keeps history. Either way, `20260824100001` should be split first — it bundles the new `clearing_logical_files` table (which `jcb_interchange_records` also needs, via FK) together with the risky Mastercard column rework in one migration file. That coupling means JCB Interchange currently can't be unblocked without also resolving Mastercard. Splitting it into two migrations removes that unnecessary dependency for future runs, independent of which option above is chosen. **B. Going forward** — each branch/developer should have its own isolated dev database (or at minimum, a documented, enforced process for how/when the shared one gets refreshed from a known-good migration state) so a partially-migrated table in one person's session can't silently become the baseline everyone else builds on. Whatever produced `shukria_transactions_30_08.sql` should not be re-run against a shared database until schema_migrations and the real schema are back in sync — it's the best-supported source of the current drift. ## 5. Still pending, unrelated to the above For completeness, these 8 migrations remain unapplied to `shukria_transactions` as of this writing — all part of the cluster above: ``` 20260809090001 create_visa_tc33_records 20260809100001 create_mastercard_ipm_records 20260809120001 fix_clearing_core_decimal_precision 20260824100001 create_ipm_logical_files_and_correct_ipm_records 20260826090001 create_jcb_interchange_records 20260827090001 rework_visa_tc33_records_to_capture_format 20260828130001 create_amex_gfsg_records 20260828140001 create_amex_file_sequences ``` Every other previously-pending migration in `apps/da_product_app/priv/repo/migrations/` (scheme connectivity/network/3DS/config-push-logs, Moneysend reference tables, JCB sequence tables, `scheme_member_institutions`, `scheme_routing_configs` presence/profile, `ipm_ard_sequences`, `visa_file_sequences`) has already been reconciled — those either had zero conflicts or their existing tables matched their migration's definition exactly. ## 6. What actually ran (option 2 — hand-reconcile in place) Four new migrations, additive only, none touching an existing row: | Migration | What it adds | |---|---| | `20260921120001_create_clearing_logical_files.exs` | The new table, split out of `20260824100001` so JCB isn't held hostage to the Mastercard reconciliation | | `20260921120002_reconcile_mastercard_ipm_records_to_real_spec.exs` | 44 missing nullable columns (`clearing_logical_file_id`, every institution-ID field, DE corrections, …) | | `20260921120003_reconcile_visa_tc33_records_to_real_spec.exs` | 51 missing nullable columns — sourced from `ClearingCore.Visa.Tc33Record`'s **schema module**, not `20260827090001` alone: the schema is itself ahead of that migration by 4 fields (`product_id`, `fee_program_indicator`, `tcr0_raw`, `tcr1_raw`) that no migration anywhere defines | | `20260921120004_reconcile_amex_gfsg_records_to_real_spec.exs` | 26 missing nullable columns | Field lists for all three were generated by diffing each table's live `information_schema` columns against its real Ecto schema module (not just the migration file — the Visa schema had drifted ahead of its own migration, see above), to avoid hand-transcription errors across ~120 columns. After these ran, `20260826090001_create_jcb_interchange_records.exs` (unmodified, its real committed version) applied cleanly — its `clearing_logical_files` FK target now exists. The 7 remaining historical versions (`20260809090001`, `20260809100001`, `20260809120001`, `20260824100001`, `20260827090001`, `20260828130001`, `20260828140001`) were then stamped as applied — their intent is now fully satisfied by the combination of pre-existing state + the four migrations above, so no further DDL was needed for them. **Verified:** - Row counts unchanged: 271 Mastercard, 30 Visa, 0 Amex, 301 DCF. - Column counts now match each schema module exactly (54/68/35 respectively, including `id`, timestamps, and `dcf_record_id`). - `mix ecto.migrations` shows **zero pending** migrations. - Queried all four tables through their real application schema modules (`ClearingCore.Mastercard.IpmRecord`, `ClearingCore.Visa.Tc33Record`, `ClearingCore.Amex.AmexRecord`, `ClearingCore.ClearingLogicalFile`) — every query succeeded with no missing-column errors, the same path `/admin/clearing`'s LiveViews use. **Not done, and worth doing separately:** no backfill was attempted for the newly-added columns on the 271 existing Mastercard / 30 existing Visa rows — they're genuinely `NULL` for fields the original (pre-real-spec) ingest never captured. If those specific rows need to be fully real-spec-complete rather than just schema-complete, they'd need to be re-ingested from source, which is a DCF/clearing_core-owner decision, not something this reconciliation assumed.