Sendoka
Dashboard

Runbook — enforce unique (org_id, external_ref) on tenants

Status: prepared, not yet applied to production. Migration 0024_tired_stick is committed but must be applied manually, after the dedup below.

Why

resolveOrCreateTenantByRef (src/lib/api/tenant-resolve.ts) documents and relies on "a unique index on (org_id, external_ref)" as its race-safety source of truth, and its insert-conflict branch matches tenants_org_external_ref. That constraint never existed — the schema only declared a non-unique index("tenants_external_ref_idx"). Consequences:

  • The race-recovery branch is dead code.
  • Concurrent first-touch requests for the same external_ref (e.g. a platform like Ezzeefy onboarding a shop via X-Sendoka-Tenant-Ref) can create duplicate tenants, and the failing insert path threw uncaught → raw 500 (the symptom fixed separately in the withApiAuth try/catch hotfix).

This change adds the missing unique("tenants_org_external_ref_unique") so the data model and the code agree.

Apply order (must be in this order)

  1. Audit. Run Step 1 of scripts/dedup-tenants-by-external-ref.sql against production (read-only).
    • Zero rows → skip to step 3. The bug may never have fired; nothing to clean.
  2. Dedup (only if step 1 found duplicates). Follow steps 2–5 of the script. Per duplicate group: pick a survivor (oldest active), repoint the 14 tenant-scoped tables, resolve the per-tenant unique collisions by hand (tenant_usage, suppressions, templates, audiences, messaging_pools), delete the loser, all in one transaction. Snapshot the DB first.
  3. Apply the migration. Once the audit returns zero rows:
    DATABASE_URL=<prod> npm run db:migrate     # applies 0024 (prod is at 0023)
    or apply the single statement directly:
    ALTER TABLE tenants ADD CONSTRAINT tenants_org_external_ref_unique UNIQUE (org_id, external_ref);
-- (and DROP INDEX tenants_external_ref_idx; — folded into 0024)
    The ADD CONSTRAINT fails if any duplicate non-null external_ref remains — that failure is the safety net telling you dedup isn't complete.

Notes

  • external_ref is nullable; Postgres treats NULLs as distinct, so tenants created without a ref (e.g. via POST /tenants with no external_ref) are unaffected — many NULLs per org remain legal.
  • After this, resolveOrCreateTenantByRef's conflict-recovery becomes real: a lost race surfaces the unique violation and re-selects the winner instead of creating a duplicate.
  • Confirm the original production stack trace (incident ~`2026-06-25T11:15:10Z) names this insert — the withApiAuthhotfix now routes it tocaptureException`.

Follow-up — case folding (CLOSED, migration 0050)

external_ref is now canonicalized to lower case on every write and matched case-insensitively on every read (normalizeRef in src/lib/api/tenant-resolve.ts, the auto-provision path, POST /v1/tenants, POST /api/internal/tenants, and the GET /v1/tenants?external_ref= filter). Before that, the resolver's lookup was a byte-exact match, so a platform sending Shop.myshopify.com on one call and shop.myshopify.com on the next created two tenants for one merchant and split its suppressions, domains and usage across the pair.

Closed by migration 0050. tenants_org_external_ref_unique (byte-exact) is replaced by tenants_org_lower_external_ref_unique, a UNIQUE INDEX on (org_id, lower(external_ref)). The database is the source of truth for the fold again, application code is no longer the only thing preventing a twin, and the three lower(external_ref) read paths have an index to use instead of a scan.

The migration is ordered create-then-drop, against drizzle-kit's generated order: dropping first leaves a window with no uniqueness rule at all on a table the platform-mode auto-provision path writes to, which is exactly where a concurrent first-touch lands the duplicate. Both rules hold during the overlap, and the byte-exact one is strictly weaker, so nothing that passes the new index can fail the old one.

The deterministic ordering on the folded lookups (status = 'active', then exact casing, then created_at, id) stays. It has nothing to disambiguate now, and that is the point — it is what kept a pre-existing twin from flapping between requests in the window before this landed.

The audit that unblocked it (2026-08-25) returned zero twin groups against production: 6 tenants, all with a ref, all already lower-case, none with stray whitespace — so the fold changed no existing row's resolution and the index built on the first attempt. Re-run this before any future tightening:

  1. Audit production for existing twins:
    SELECT org_id, lower(external_ref) AS ref, count(*), array_agg(id)
  FROM tenants
 WHERE external_ref IS NOT NULL
 GROUP BY 1, 2
HAVING count(*) > 1;
  2. If it ever returns rows, merge each group by hand — pick the surviving tenant, repoint its workload rows, archive the loser. There is no automated merge, and there should not be one: which tenant is the real one is a business decision. CREATE UNIQUE INDEX failing on a twin is the safety net saying the merge is not finished.