Update the Interchange

Path ① — Manual run

Server: the EC2 box (/var/www/interchange_update, runtime user interchange, SQL Server Express on localhost:1433). On the server, prefix commands with sudo -u interchange php /var/www/interchange_update/artisan; shown below as php artisan for brevity. The build only ever writes to hollander_ic_urgpro — production (hollander_ic) is untouched until you promote.

1. Get the Hollander update

   Hollander delivers a full database backup, e.g. 9301_ENTERPRISE.bak. If a refreshed Interchange database (the Int_Pline OEM source) ships with it, grab that .bak too. Copy them to the server.

2. Restore the backup to SQL Server

   Put the file where the mssql service can read it, then restore (replacing the current database):

   sudo cp 9301_ENTERPRISE.bak /var/opt/mssql/data/
   sudo chown mssql:mssql /var/opt/mssql/data/9301_ENTERPRISE.bak
   
   # the bak's logical names are interchange_Data / interchange_Log, so MOVE them into ENTERPRISE
   sqlcmd -S localhost -U sa -C -Q "
   RESTORE DATABASE [ENTERPRISE] FROM DISK = N'/var/opt/mssql/data/9301_ENTERPRISE.bak'
   WITH MOVE 'interchange_Data' TO '/var/opt/mssql/data/ENTERPRISE.mdf',
        MOVE 'interchange_Log'  TO '/var/opt/mssql/data/ENTERPRISE_log.ldf',
        REPLACE, RECOVERY, STATS = 5"

   Unsure of the logical names? List them first: RESTORE FILELISTONLY FROM DISK = N'…'. Confirm the version landed:

   sqlcmd -S localhost -U sa -P '***' -C -Q \
     "SELECT Version FROM ENTERPRISE.dbo.Hollander_Interchange_Version"

   Note — the Interchange DB is a one-time frozen base. Only ENTERPRISE is refreshed each update. The separate Interchange database holds Int_Pline — a hand-built OEM cross-reference that can't be refreshed — so it is restored once during setup and left in place. Every ids2hol build reads it. Don't overwrite or drop it when loading a new ENTERPRISE.bak.

3. Run the build (one command)

   This runs the whole deterministic pipeline in order and stops before promotion:

   php artisan interchange:run-update

   It performs, in sequence (≈40–60 min on the 4-vCPU box):

   • Sync — pull the 10 Hollander base tables from MSSQL ENTERPRISE.
   • Carline + make — build carline_w_make and assign brands (435 rules).
   • Interchange — rebuild urg_interchangelist (parse the application text, synthetic one-to-one rows, VIN explosion).
   • OEM cross-ref — rebuild ids2hol from Interchange.Int_Pline.
   • Lookups — rebuild interchangelist_dist.
   • Registry — stage the hollander_numbers (urg_stock) reconcile: register genuinely-new interchange numbers and list retirement candidates. Additive — nothing in the live registry is deleted here.

   Prefer to run a single stage? Each is its own command: hollander:sync-from-mssql, hollander:build --phase=2, interchange:update, interchange:build-ids2hol. Add --skip-sync to run-update if the base tables are already current.

4. Validate against production

   Read-only comparison of the freshly-built schema against the live hollander_ic:

   php artisan interchange:validate

   Expect the new build to land close to production (typically ~96% of row counts, slightly higher on the newer version), with VIN columns and parsed fields matching. Large unexplained drops — e.g. ids2hol losing a third of its rows — mean do not promote; investigate first.

   The bottom of the report also shows the registry plan: how many new urg_stock numbers will be registered and how many retirement candidates exist (with a threshold check). A retirement count above the threshold means the build isn't yet at full coverage — expected while parity is still being reached, and harmless because retirement is opt-in.

5. Promote to production (gated)

   First a dry run — it prints the exact rename plan and the rollback, and changes nothing:

   php artisan interchange:publish

   When the validation looks right, perform the reversible cutover. It renames the current production schema to a timestamped backup and promotes the new one in its place:

   php artisan interchange:publish --confirm=PROMOTE

   This also promotes the registry: it backs up aces_data.hollander_numbers and inserts the new numbers (assigning a fresh urg_stock each). By default it is additive only — no numbers are deleted. To also retire numbers gone from this build, run it deliberately with --allow-retire (refused if retirements exceed the safety threshold):

   php artisan interchange:publish --allow-retire --confirm=PROMOTE

   Rollback (if needed) is a pair of schema renames, printed by the command; the registry is backed up to a timestamped table first. After a promotion, recreate hollander_ic_urgpro (via php artisan migrate) before the next build.

Path ② — AI automated Available

Same deterministic build as Path ① — just driven from the browser, with an analyst doing the judgment a developer does by hand today. The guiding rule: the AI supervises and reports; it never transforms data and never promotes. Execution stays the proven command pipeline; promotion stays a human click.

Open the AI Update Console →

1. Upload

   Drop the new .bak into an upload form here. It is saved straight to a local staging folder on the box (no S3 — the file never leaves the server) and a build run is queued.

2. Restore & build

   The job restores the backup to SQL Server and runs interchange:run-update — the exact same pipeline as the manual path, including the additive hollander_numbers registry stage — streaming live progress to the page.

3. Analyze (Bedrock / Claude)

   At each checkpoint the job sends the structured results to Bedrock. The model summarizes what this Hollander version changed (numbers added / changed / retired, which part types), judges the validation deltas against expected bounds, flags anomalies, and on any failure explains the error and halts.

4. Report & recommend

   You get a plain-English build report with a clear GO / REVIEW / NO-GO verdict — e.g. “Build #12 (v93.01): 9.1M rows, +24k numbers, −2.9k retired, all deltas within version-drift bounds → GO.”

5. Promote (still your click)

   If you agree, you click Promote, which runs the same gated interchange:publish cutover — additive only (it registers new urg_stock numbers but never deletes). Retiring numbers is intentionally kept off the web button; it is a deliberate CLI action (--allow-retire). The AI only ever recommends.

Build status: the console — upload, local restore, queued build, validation, report, and the gated Promote — is built and working. The Bedrock (Claude) analyst is wired (Bedrock Converse REST API, no AWS SDK); to turn it on, set BEDROCK_ENABLED=true, BEDROCK_REGION, BEDROCK_MODEL, and a Bedrock API key in AWS_BEARER_TOKEN_BEDROCK. Until then a deterministic report is used. A queue worker must be running (php artisan queue:work); SQL Server is local on the EC2 box, so the restore is a local sqlcmd step.

Reference

Server build steps (OS, SQL Server, PHP, Nginx/TLS, systemd, queue worker) live in docs/UBUNTU22_DEPLOYMENT.md in the repo (Ubuntu 22.04, native SQL Server 2022, app at /var/www/interchange_update). The stage-by-stage data flow is on the About page; live table counts are on the Hollander index.