Migration module

The Migration tab is the one-time cutover surface — how a shop moves from its prior POS (AIM Bike, the vertical's dominant incumbent) onto the Hub without hand-copying twenty thousand transactions. It's sys-admin-only, hidden from every other role, and it does the destructive work: wipe the target tables, load the source data, validate the load matched what came out of the .bak.

Tab label	Migration
Tab accent	#dc2626 (red — the destructive-work tab)
Strip position	last (position set by setTabOrder() in src/modules/index.ts, v0.7.409+)
Folder	src/modules/migration/
Migration file	049_migration_tool.sql
Process	BIKE.L1-0016
Access	screen.migration permission — granted to no role; only sys_admin holds it (via catalog sweep)

What it owns

src/modules/migration/
  index.ts          — registers the module with the Kernel
  view.ts           — markup + styles for the migration console
  client.ts         — browser JS (job control, phase indicator, parity report)
  api.ts            — server-side routes (/api/m/migration/*)
  runner.ts         — the ETL step pump (one step per POST, cursor in migration_jobs)
  wipe.ts           — the wipe engine (manifest-driven, order-independent, KEEP-asserted)
  gate.ts           — the fidelity gate (runs before wipe, on staged source data)

migrations/etl/     — the ETL runner + convert scripts (rescued out of scratchpad, ADR-0016)
  extract.py        — SQL-Server → portable SQLite snapshot (local, all 141k rows / 23 tables)
  stage.py          — SQLite snapshot → D1 aim_raw_* staging (tables split into _x2 at D1's 100-col cap)
  convert-*.ts      — INSERT..SELECT converters that run inside D1 (catalog, suppliers, customers, sales, service, purchasing)
  refresh.sh        — supersedes the older run_load.sh runner

What it does

Two halves — extract locally, run inside D1 (the "Shape 2" design). The .bak lives on the shop's Windows box; the shop's Cloudflare Worker can't reach it. So extract.py runs locally against the SQL Server, producing a portable SQLite .db snapshot that captures every AIM row without touching the network. stage.py then reads the snapshot and streams the rows into D1 as aim_raw_* staging tables — tables wider than D1's 100-column cap get split into an _x2 overflow twin. From there the converter runs entirely inside D1 as SQL — INSERT..SELECT from the staging tables into the target customers / product_variants / transactions / etc., with the shop offline for the cutover window.

Backup picker (v0.7.370). Instead of typing a .bak path, the extractor now shows a list of every .bak on the local box with drive / size / date columns. Reduces one class of "typed the wrong path" support call.

Manifest-driven wipe. The wipe.ts engine takes a KEEP-asserted manifest — a list of tables to preserve — and wipes everything else in a topological order that avoids FK cascades. Manifest is per-preset; migration 049 seeds one preset ("Fresh: Customers + Inventory + Sales History") that wipes customers + product_variants + transactions + purchasing + service but keeps users, roles, permission grants, shop_config, and feature_registry.

Convert phase — one step per POST. No Queues binding, no long-running Worker call. The runner in runner.ts pumps one step per POST and stores the cursor + phase in migration_jobs. The client polls; the operator watches a phase indicator progress catalog → suppliers → customers → sales → bikes → service → purchasing → finalize (tax_reapply + bucket_seed) without a timeout ever biting.

Fidelity gate — runs BEFORE the wipe. gate.ts compares the counts + spot-checks on the staged aim_raw_* rows against expected shape. Fails? Job stops. Wipe hasn't run. The operator inspects the gate result on the same tab, tells the ETL what changed, retries the extract. Only a green gate proceeds to wipe → convert → parity report.

Parity report — after convert, before commit. Per-entity count + sanity SUMs (customer count, variant count, sales-history-transaction count + gross), stamped against the AIM-source snapshot the extract produced. The operator sees "customers 3,099 → 3,099 · variants 7,246 → 7,246 · transactions 20,360 → 20,360 · gross $2.11M → $2.11M" and either commits the job or rolls back.

Undo — D1 Time Travel. The Worker doesn't manage a "restore point" bookmark. D1's built-in Time Travel gives a 30-day rewind window; the started_at timestamp on the migration_jobs row IS the restore point. If the parity report shows drift the operator didn't like — or a shop-owner-audit ticket lands the next morning — one CLI call rewinds the whole DB to before the run.

Local agent — .bak picker in the tab (v0.6.978)

The Migration tab runs inside a Cloudflare Worker; it cannot see E:\ or run SQL Server. A local agent (migrations/etl/shape2/migrate.py --serve) bridges that gap:

• Bound to 127.0.0.1 only — nothing off the machine reaches it.
• Endpoints: GET /baks (drive scan, newest first), POST /refresh {bak} (extract → drop staging → stage → verify, background thread), GET /status (live phase + log).
• CORS echoes the caller origin so the Migration tab can read it; POST /refresh requires an Origin header so a bare script can't trigger a staging rebuild silently.
• Refreshes STAGING only — never wipes live data — so a stray call's blast radius is a re-stage, not data loss.

The Migration tab's card 1 becomes a live picker: detects the agent, lists backups with dates / sizes as radios, "Restore & stage" streams progress, and on success the existing gated money-check → wipe → migrate flow runs unchanged.

Post-load compliance suite (v0.6.970, v0.6.991)

Two tax invariants now run after every load and fail loudly rather than passing silently:

• v0.6.970 — post-load compliance suite adds two tax invariants (verify against migrations/etl/shape2/migrate.py's post-hook — enforces the tax classification the shop can actually be audited on).
• v0.6.991 — source category + subcategory tax from AIM, deterministically — the tax classification for every imported product is derived from AIM's category + subcategory taxonomy through a deterministic rule set, not from a hand-tagged column. Reproduces on every re-load.
• v0.6.970 also fixes migration 036_layaway_backbone.sql so it applies cleanly from an empty database (a real defect for a fresh shop cutover).

What the operator sees

Ordered as the operator walks it:

1. Local extractor — the extractor lists every .bak file on every drive on the box (size + modified date). Operator picks one; no path typing.
2. Stage — snapshot streams into D1 aim_raw_* staging tables.
3. Money check — the fidelity gate runs on the staged source and shows a pass / fail with the delta if it fails.
4. Scope preset picker — one preset shipped ("Fresh: Customers + Inventory + Sales History"); more can be seeded per shop.
5. Type MIGRATE → Start — the destructive step is behind a literal typed confirmation; casual clicks can't fire it.
6. Phase indicator — catalog → suppliers → customers → sales → bikes → service → purchasing → finalize; each phase turns green as it completes with a row-count.
7. Parity report — target counts + sums after convert, next to the AIM-source counts.
8. Commit / Roll back — the decision surface; if the parity looks off, the screen prints the exact D1 Time Travel restore command the operator can paste to rewind the whole DB to before the run.

Settings are never touched. The wipe manifest KEEPs users, roles, permission grants, shop_config, feature_registry, tax rates, and PINs — and the parity report asserts each of them unchanged after the wipe so the shop signs into the same operators + roles they had before, with the same tax rules applied.

Fixes discovered during the first real load

The first live Swicked Cycles run surfaced twelve substrate bugs the converter's INSERT paths never validated against live schema:

• Three column names were wrong at the INSERT (v0.7.368 fix, db5ca67): code → is_sales_floor, phone → phone_number, email → email_address. Now every INSERT is statically validated against the live schema before deploy.
• CPU-limit deaths on the customer load — added target-side indexes idx_customers_external_aim, idx_variants_sku_norm; retargeted the refunded_quantity UPDATE; fillModelYears scans model text only, not notes (was scanning gigabytes of notes to find "2024" strings).
• screen.migration was never registered — the sys_admin's client-side allow-list didn't include it, so the tab never rendered (60be0f3).
• /api/m/migration/* dispatcher leading-slash normalisation — routes didn't match (5854d6c).

Tier-1 audit money bugs fixed at the source (not just in the till):

• T1 #1 / #4 — deposits now positive on load, deposit_kind set correctly
• T1 #2 — total_tax_cents now in the INSERT (was silently null)
• T1 #3 — return signs
• T1 #5 — payments.staff_id linked

Newly discovered during the ETL work: the same discount double-count bug the Python ETL had (sal_soldprice is already NET, not GROSS — the converter was subtracting the discount a second time), which mirrored the Sales client's alpha-v2 #5/#6 fix at v0.7.359. And a taxonomy bug: sad_type='S' in AIM was mapped to Service by the converter but actually means SERIALIZED — 717 bikes were being loaded as service tickets. Migration 073_sad_type_S.sql reclassifies.

Converter rules that emerged from real loads

Three later converter-only bumps landed deterministic rules that turned out to be needed across every AIM backup, not just Swicked's.

v0.7.379 — Accessory tax durable at the subcategory level (convert-catalog.ts). Tax resolution reads inventory → product → category and skips subcategory, so the coarse "Accessories" category made every subcategory under it (LIGHTS, FENDERS, LOCKS, etc.) ring GST+PST. Now a deterministic rule sets each product's tax by subcategory name: LIGHTS → safety-exempt, FENDERS → part-exempt, COMPUTERS + CAR RACKS → fully taxable, the seven real attachments (locks / bells / bags / pannier racks / water bottles / bottle cages / pumps) → context-dependent-accessory, GENERAL / NA / Misc inherit their category default. 976 products re-tagged on the Swicked load. The rule is embedded in the converter rather than a per-shop config so the same source-shape produces the same tax on every future AIM cutover. Pairs with the Sales-Till BC PST accessory rule (BIKE.L2-0248, v0.7.378) — the ETL sets the durable default; the till enforces the counter behaviour.

v0.7.386 — AIM on-account balances carried (convert-customers.ts). If a customer's AIM record shows money on account, the ETL now writes an opening-balance ledger entry on load so the shop's Hub-side balance matches what AIM showed the day before cutover. No-op on Swicked's data — every AIM customer imported with a $0 balance — but the capability exists for any AIM backup that carries balances (Tom's rule: money always comes over).

v0.7.387 — AIM user-switch ghost transactions omitted (convert-sales.ts). AIM's user-switching leaves behind cancelled, zero-dollar "Cash Sale" Point-of-Sale shells with no lines and no money — reported by James on the shop floor. A signature verified airtight (facts-before-changes) before trusting it as a delete rule: cus_pk=1 (Cash Sale Swicked Cycles) + transtype='Point of Sale' + status not posted ('X' 3,148, blank 30) + no scsasld lines + no scinvoic money = 3,178 rows. Every one has zero payments (nothing orphaned); zero POSTED Point-of-Sale entries exist for the cash-sale account, so the filter can't catch a real sale. Applied as NOT_GHOST in the transactions step WHERE clause + its parity expectation. Matched by customer name ('Cash Sale%'), not a hardcoded id, so it survives a different client's cash-sale account. Import drops from 22,111 rows to 18,933 — the drop is intended, not data loss.

First validated end-to-end run (job #6)

Migration job #6 ran a full wipe-and-reload through the Migration tab's own UI — 35 steps, ~5 minutes — and reproduced every accumulated fix. The final loaded state:

Table	Rows
customers	3,252
products	9,670
transactions	18,933
transaction_lines	22,677
service_tickets	3,201
purchase_orders	2,269
customer_bikes	2,863

The complete AIM dataset is now migrated: catalog, customers, contacts, bikes, sales history, service, purchasing. From v0.7.387 forward the tool is considered validated end-to-end; further ETL bumps are refinements against edge cases, not tool-shape changes.

AMEND — add a new backup on top, don't wipe (migs 130–135, 88f6b87 series)

"Add the data in the new backup, do not wipe and restart." (Tom.) A shop trading for another week after a cutover doesn't want the whole load re-run — the second load is "put the new receipts in", not "wipe and start over". This is a different job, not a smaller one — it skips the capture, the cross-ref nulling, and the wipe entirely, and goes straight to the load.

• Migration 131 — job mode. A migration job now knows whether it is a rebuild or an amend — one row's mode column, read by the runner.
• Migration 132 — amend is a PRESET, not a second switch. Migration 049 already decided that scope was a choice, versioned, visible at run time, variable per run; migration_presets is the store, step 3 of the console is its picker. What a run destroys is a property of the scope you pick — so amend went in there instead of growing a fifth flag.
• Migrations 130 + 135 — unique keys on the entity + child tables. Every converter INSERT is already INSERT OR IGNORE, so re-running against a newer backup ought to add what's new and skip what's already there. That only works where a UNIQUE constraint tells SQLite what "already there" means. Mig 130 covered the five primary-entity tables; mig 135 covered the child tables mig 130 should have included from the start.
• Migration 134 — undo the child-table duplication the 3 Sep amend caused. "My miss, recorded plainly. I gave unique keys to the five entity tables and declared the amend safe on that audit. I never checked the CHILD tables. Four of them had no unique key at all, and transaction_taxes had no aim_id column either — so its INSERT OR IGNORE had nothing to collide on and re-inserted every existing row." Mig 134 removes the duplicates; mig 135 stops it recurring.
• Migration 138 — dedupe customer_phones + customer_bikes on legacy duplicates. These two could not be protected by mig 134 because both already held duplicates that predate the amend — a unique index will not build over them. Mig 138 decides which duplicate wins per row, then the unique index can build.
• Amend refuses to run on a database a half-finished wipe has damaged (442fe10). A guard checks that the target is in a coherent state; a partial wipe (a crashed rebuild left mid-flight) is refused rather than amended-on-top.

Brand groups — one brand can wear several labels (mig 133)

"49N is Norco; Ltp is Norco." (James.) Swicked's ledger files the Norco group's product under three different manufacturer names — NORCO, Ltp, 49N. Without a mapping table the Channel tab believed they were three companies. Mig 133 makes the alias mapping data.

ZZ test catalog retired (mig 107)

The ZZ- SKUs + ZZSER- units — 12 products, 12 variants, 18 units — were the fixture bikes built to walk the Rentals scenarios. They were taken out of the rental fleet already (is_rental=0), but still sat in the catalog, findable in Inventory + Sales searches, indistinguishable from real stock to anyone opening those tabs. Mig 107 retires them from every operator surface.

What the module does NOT do

• Not a live sync. The tool is a one-time cutover, not a two-way sync with AIM. After a shop's live on the Hub, AIM is done. Amend (above) is not a sync — it's a subsequent cutover from a fresher backup, still initiated by an operator.
• Not a general import tool. The staging tables + converters are AIM-shaped; a shop coming off a different POS gets a different converter, not a config knob on this one.
• Rebuild is not idempotent. Running a rebuild job twice against the same target isn't safe — the wipe is destructive. Amend is designed to be safely repeatable, backed by the unique keys of migs 130 + 135.

See also

• Three strata — screen.migration is a permission the Kernel gates on
• Sales module — the till the loaded transactions ring against
• Settings module — where the sys_admin's role gates the tab