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)
Display order	99 (last)
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.

What the operator sees

Backup picker — list of .bak files on the box, size + modified date. Pick one.
Phase indicator — catalog → suppliers → customers → sales → bikes → service → purchasing → finalize; each phase turns green as it completes, with a row-count.
Gate result — pass / fail with the delta if fail (staged vs expected).
Parity report — target counts + sums after convert, next to the AIM-source counts.
Preset picker — one preset shipped ("Fresh: Customers + Inventory + Sales History"); more can be seeded per shop.
Commit / Roll back — the parity report's decision surface.

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.

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.
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.
Not idempotent. Running the same job twice against the same target isn't safe. The wipe is destructive; the operator uses Time Travel to undo, not re-run.

What it owns​

What it does​

What the operator sees​

Fixes discovered during the first real load​

What the module does NOT do​

See also​

What it owns

What it does

What the operator sees

Fixes discovered during the first real load

What the module does NOT do

See also