Region Cutover Plan — TEXT vocabulary → administrative_divisions FKs

Status: Active Last reviewed: 2026-08-10

MODE CHANGE 2026-08-10 (same day): the app is NOT live — cutover proceeds as a CLEAN BREAK per global-schema-overhaul.md S1: phases 2–4 collapse into one series that moves reads AND drops the TEXT columns/CHECKs outright; no dual-read ceremony, no revertibility windows. Phases 0–1 below remain accurate as shipped groundwork.

Owner-ratified 2026-08-10: "the cost of migration is high once we have Korean users — make the cutovers to the global models." Expand-and-contract cutover of every region read/write from the 17-시도 TEXT vocabulary to the administrative_divisions spine (ISO-3166-2; KR adm1 slugs === the TEXT keys by design, 00279). Grounding: globalization-model-review.md — this was the #1 structural debt.

Done (2026-08-10)

• Phase 0 — de-triplication (commit with this doc): the vocabulary now has ONE source — REGION_KEYS in club.entity.ts; the Region type and regionSchema derive from it, and config/regions.ts SIDO_OPTIONS/SIDO_KEYS are compiler-checked against it. Three-way silent drift is impossible now.
• Phase 1 — write-path parity (migration 00455, live): club_play_regions (the one table that regressed in 00449) gains adm1_id/adm2_id, the same sync_region_adm BEFORE-trigger as clubs/court_venues/public_courts, and a resolver backfill. Every region-bearing table now dual-writes adm FKs on every write.

S1 COMPLETE (2026-08-10)

Chunk B shipped with chunk A: the Region literal union is retired (plain slug strings, DB composite FKs are the validity gate — the JP/US pins now assert 'tokyo'/'california' flow through), the hand-maintained SIDO/SIGUNGU tables are replaced by the generated divisions catalog (config/divisions.kr.ts, 17 adm1 + 228 adm2, byte-verified against the 00279 seeds) behind a thin regions.ts compat layer, and migration 00457 added country_defaults so auto-created spine rows derive tz/currency from their country instead of leaking Asia/Seoul+KRW. Still open from the original S1 list: profiles play_regions JSONB→junction (rides with a later pass); entity FIELD renames (cosmetic, deferred).

Superseded phase notes (historical)

(each one agent-sized; sequence matters)

• Phase 2 — read-path cutovers, one surface at a time. Order by risk: (a) club discovery filters (get_club_discovery_summaries + client filter params: region key → adm1_id/slug); (b) search-venues fn matchesBases/district scoping (composite busan-중구 slugs → adm2_id pairs); (c) wizard base derivation (deriveBasesFromPicks emits adm ids alongside keys; picks carry adm1_id/adm2_id from search results); (d) profile play-region readers move to profile_play_regions junction. Each cutover keeps writing TEXT (triggers derive FKs), only READS move — instantly revertible.
• Phase 3 — client vocabulary swap. Region fields in entities become regionCode: string (adm slug) + optional display resolution through the spine's names JSONB; coords-to-region replaced by nearest-adm lookup (or PostGIS later). SIDO_TO_ISO_3166_2 crosswalk retires (slugs are already the join key).
• Phase 4 — contract. Drop the 17-value CHECK constraints (keep TEXT columns as denormalized display cache or drop entirely), delete SIGUNGU_BY_SIDO client table in favor of spine adm2 queries, remove the legacy play_regions JSONB after junction cutover.

Invariants during the cutover

• TEXT and FKs never disagree: triggers own the FKs; clients never write adm ids directly.
• A read-path cutover ships only with a probe comparing old-vs-new result sets on live data (seeded scenarios + yarn debug:logs).
• New features MUST NOT add TEXT-vocabulary reads (standing rule, globalization review) — they read FKs from day one even before old surfaces cut over.

S1 EXECUTION CONTRACT (locked 2026-08-10 — clean break, app not live)

Key recon fact: adm1 slugs === today's 17 region TEXT keys, and adm2 slugs are {adm1slug}-{한글구명} composites (00279 seeds) — today's district 한글 maps to adm2_slug via region || '-' || district. So the cutover is a RENAME + FK, not a data rewrite. Environment-specific UUIDs make id-embedding client-side impossible → the stored reference becomes the SLUG with a composite FK.

Target shape (tables: clubs, court_venues, public_courts, club_play_regions, profiles home-region; spine venues stays as-is — already global):

• country_code CHAR(2) NOT NULL DEFAULT 'KR'
• region TEXT → RENAME to adm1_slug (values unchanged); CHECK(17) dropped; composite FK (country_code, adm1_slug) REFERENCES administrative_divisions(country_code, slug)
• district TEXT (한글) → adm2_slug = adm1_slug || '-' || district, same composite FK
• UUID adm1_id/adm2_id scaffolding columns + sync_region_adm triggers DROPPED (expand-phase artifacts)
• profiles.play_regions JSONB {sido,sigungu} → slug array or junction-by-slug (decide in-flight)

Client shape: Region enum type retires → adm1Slug: string (same values) + adm2Slug: string; display names resolve via a GENERATED divisions catalog (config/divisions.kr.ts, generated from the 00279 seed VALUES — slugs + ko/en names, no UUIDs); SIDO_OPTIONS/SIGUNGU_BY_SIDO/regionLabel retire into catalog lookups; coords-to-region returns adm1 slug (logic unchanged, output renamed).

Work chunks (agent-sized, in order):

1. Migration 00456: renames + country_code + composite FKs + adm2_slug backfill + drop UUID cols/triggers/CHECKs, for the 4 club/court tables (+ profiles home region). Regenerate supabase types (prettier!).
2. Divisions catalog: generate config/divisions.kr.ts from 00279 seed data; new lookups (divisionLabel, adm2ByAdm1); retire SIDO/SIGUNGU tables + Region enum (REGION_KEYS stays as KR adm1 slug list, re-typed).
3. SQL readers: sed-level rename fallout in every fn referencing region/district (get_club_discovery_summaries, search-venues fn matchesBases/districtsByReg, RLS, triggers, views) + adjust adm2 matching to slugs.
4. Client sweep: entities (club/venue/profile/session/preferences/directory-venue) field renames; wizard step2 (deriveBasesFromPicks, search params), step3 fallback, discovery filters, session surfaces; store persistence keys.
5. Seed-verify: scenario chains + search fn probes; gates; OTA.