Runbook: Retire the CK "Demo" WhereFour connection (migrate to Sandbox)

Status: EXECUTED on staging 2026-06-05 (during the ENG investigation of “missing shipping addresses for CK on staging”). Not yet run on production. The scripts in ck-demo-retirement-scripts/ are the exact steps that were applied.

Goal

Consolidate Cleveland Kitchen onto a single WhereFour connection — the Sandbox (4f234677-7bc5-43c1-9059-bd616b33ef75, org org_3DisstYyUfNutmFA68xw5hxXfq1, sandbox.wherefour.com) — and retire the Demo connection (093e4161-3310-4559-ab5e-24723d93a6aa, org org_3AMxiy2y7ePCWJO3KY0eRYbCst4, my.wherefour.com) plus the inactive duplicate (cc3f86f7-22f7-4972-8798-2ecae28723d8).

Why this is NOT a raw DELETE

Deleting 093e4161 fails or destroys real POC data. FK inventory on staging (2026-06-05):

Table	onDelete	demo (093e4161)	inactive (cc3f86f7)	Notes
orders.erp_connection_id	NO ACTION	2	1	blocks delete (RESTRICT)
pdf_documents.erp_connection_id	NO ACTION	35	128	blocks delete
email_aliases.erp_connection_id	NO ACTION	1	3	blocks delete
manual_cross_references	CASCADE	2,546	0	hand-curated mappings — high value
erp_effective_prices	CASCADE	4,721	0	re-derivable from sandbox
erp_pricing_rules_raw	CASCADE	2,668	0	re-derivable
customer_aliases	CASCADE	32	15
product_enrichments	CASCADE	92	0

A raw delete RESTRICT-fails on orders/pdf_documents/email_aliases; forcing it cascade-wipes 2,546 curated cross-references + pricing/enrichment.

The hard part: customer-ID scheme differs

The Demo connection sourced from my.wherefour.com (prod tenant) → customer IDs like 415254, 21xxx. The Sandbox tenant uses a different ID scheme (206xxx, e.g. Harris Teeter Indian Trail = 206234 in sandbox vs 415254 in demo). So manual_cross_references / orders keyed to demo customer IDs cannot be blindly re-pointed to 4f234677 — they need a customer-ID remap (415254 → 206234, etc.), built by name-matching demo↔sandbox customers.

Migration steps

1. Decide canonical org. Sandbox is org_3Disst…; Demo is org_3AMxiy…. Confirm which org is the go-forward CK org (operators currently use the Sandbox).
2. Build the customer-ID remap demo→sandbox (415xxx → 206xxx) via name match (both tenants have the same customer names; see the Typesense customers collection per connection, or the WhereFour /customers lists).
3. Migrate high-value data (only what’s worth keeping):
   • manual_cross_references (2,546): re-point connection_id → 4f234677 AND remap customer_id via step 2. Items keyed by their_item_id are reusable; drop rows whose customer can’t be mapped.
   • orders (2) / pdf_documents (35+128): historical POC records — prefer archive (export) over migrate; or re-point erp_connection_id if they must stay live (after customer-id remap).
   • erp_effective_prices, erp_pricing_rules_raw: drop — re-derive by scraping pricing on the sandbox (NOTE: sandbox pricing scrape is currently broken — see “Open blockers”).
4. Clear the RESTRICT references (reassign or archive the 2 orders, 35+128 PDFs, 1+3 email_aliases) so the connection row can be deleted.
5. Delete the connection rows 093e4161 + cc3f86f7 (CASCADE cleans the rest).
6. Clean orphan Typesense docs for 093e4161 (it has ~1,163 shipping + products + customers docs in the shared cluster, not FK-linked, so they survive the DB delete). Delete by filter_by=connection_id:=093e4161 per collection (admin key).

Open blockers to resolve first (discovered 2026-06-05)

These broke the staging Dagster pipeline and must be fixed before a clean sandbox-only rebuild (products/pricing) is possible:

1. R2_ENDPOINT consolidation gap. infrastructure/.../dagster.yaml (compute_logs.endpoint_url), dbt profiles.yml, and apps/dagster/erp_pipeline/definitions.py read {env: R2_ENDPOINT} with no fallback, but the R2-endpoint consolidation kept only R2_ENDPOINT_URL in Infisical staging:/runtime/dagster-gcp. Fix: re-add R2_ENDPOINT as the alias (per secrets-map.json, which already documents it as the alias) OR update those 3 configs to R2_ENDPOINT_URL.
2. Products/pricing dbt models broken for sandbox. raw_products → Binder Error: ... no column named "list_price" (sandbox inventory schema differs); raw_product_pricing → Could not guess Iceberg table version (pricing never scraped for the sandbox tenant). Products won’t sync until fixed.

Alternative (lower effort)

If the POC order/PDF/cross-reference history doesn’t need to live under the sandbox connection, just deactivate 093e4161 + cc3f86f7 (active=false) to stop them resolving, and keep the data as archived POC history. The CK shipping fix does not depend on retiring the Demo connection.