International Data-Model Foundation

Status: Reference

Status: APPROVED DESIGN — build pending (2026-06-21). This is the locked reference for the internationalization + venue-platform + rating-integrity foundation. It supersedes ad-hoc venue modelling and extends foundational-schema-decisions.md.
Mandate (owner): "Build it right, no hurry." Maximize robustness + future-proofing. Korea-first, but every model must deploy to all countries and serve all player levels.
Origin: a three-stream adversarial audit (region/geography · venue+i18n · rating) of the live 278-migration schema, red-teaming both what we have and what we planned to build. The audit reshaped the plan: build the expensive-to-change spine now, defer the cheap-to-add satellites, and fix launch-relevant rating integrity as part of the foundation.

0. The evolution discipline (read this first)

Everything below obeys one principle, because it determines whether a future change is free or painful.

The cost of a schema change depends on what changes — and the asymmetry is the whole game:

Cheap forever — additive (~90% of real schema growth)	Expensive — the migration-under-load we must avoid
New table; new nullable column; new junction table; a new satellite feature FK'd to an existing entity	Changing an entity's identity (which table a FK points at); changing a relationship cardinality (1:1 → 1:N); retype / rename / drop a column on a populated table; NOT NULL on existing data
Backward-compatible, metadata-only lock, no backfill, ship anytime	Requires the expand → migrate → contract dance + a data backfill while users are live

Therefore:

Lock the spine now, defer the satellites. Get entity identity, boundaries, and relationships / FK targets right now (cheap today on a tiny pre-launch dataset; brutal later under load). Defer attributes and satellite features — they are additive (CREATE TABLE … REFERENCES <spine> + INSERTs), addable anytime with zero backfill. "Lean" = a correct skeleton with few muscles, never a wrong skeleton.
Additive over destructive. Prefer adding a table/nullable column over modifying one. Empirically additions are ~9/10 of all schema changes — design for addition.
Expand-and-contract for anything that touches live data. Never a big-bang. Add-new-alongside-old → dual-write/backfill → drop-old, each step backward-compatible. (Martin Fowler, Parallel Change; Ambler & Sadalage, Refactoring Databases.)
Hexagonal ports absorb churn. Schema changes live in the migration + adapter; the domain entity is the contract. The app never feels a well-managed schema change.
Controlled vocabularies everywhere applicable (see §2). Free text only for genuinely open fields.
No silent locale defaults. i18n columns are NOT NULL with NO DEFAULT — a DEFAULT 'KR' / 'KRW' / 'Asia/Seoul' silently corrupts the first non-KR row. Migrate existing KR rows explicitly in the migration body.

The "satellites" are fully specified here (the generalized-model-held half) so that when we build them they slot into the spine with no rework — but they are not built at launch.

1. Scope & workstreams

Four workstreams. Each separates SPINE (build now) from HELD (designed, deferred).

WS	Area	Spine (build now)	Held (deferred satellites)
WS-1	Region / geography	`administrative_divisions` reference + `profile_play_regions` + FK adoption	per-country level-3 (읍/면/동) seeds; polygon/spatial discovery
WS-2	i18n + data-quality	no-default `currency_code` / `country_code` / `iana_timezone`; controlled-vocab + FK CHECK fixes	multi-currency pricing rules; address-search provider abstraction
WS-3	Rating integrity	`rating_state` + `rating_source` + sub-tiers; doc/UI honesty	OpenSkill (Weng-Lin) migration; verified-tournament ingest
WS-4	Venue platform	canonical `venues` + `venue_courts` + `court_venues.venue_id` link + lifecycle + simple `venue_media`	provider conflation; `venue_managers` claim/verify; photo selector engine; amenity catalog

2. Controlled-vocabulary policy

Every categorizable field gets a controlled value set. The mechanism follows this rule (consistent with existing conventions — TEXT + CHECK enums, the purpose_catalog reference table, ISO-3166 on nationality_code):

Mechanism	Use when	Examples
Catalog table (FK) — values are data with label/i18n/sort/category/deprecation	vocab carries metadata, is grouped/hierarchical, may grow at runtime, or needs i18n	`administrative_divisions`, `surface_catalog`, `amenity_catalog` (held), `venue_type_catalog`
`TEXT + CHECK` + Zod enum	small, stable, behavior-driving, code branches on it	`claim_status`, court `status`/`setting`/`pace_category`, `provider`, media `kind`/`approval_status`/`source`, flag `reason`, `rating_state`, `rating_source`, `closure_reason`, `payment_method`
External standard (validated, not free text)	a global standard exists	`country_code` (ISO-3166-1 alpha-2, uppercase CHECK), `currency_code` (ISO-4217), `iana_timezone` (`pg_timezone_names`), `language` (BCP-47), region level-1 (ISO-3166-2)

Free text is permitted only for: name, notes, caption, street_address, formatted_address, bio, display_name. Easy-to-miss fields that must be controlled: media provenance (source), flag reasons, provider, price tier, payment method, closure reason.

3. WS-1 — Region / administrative-geography (the keystone)

Why the current + originally-proposed models both fail

Current: region is a hardcoded KR SIDO english key ('seoul'); CHECKs are inconsistent (clubs.region has one, profiles/sessions/court_venues do not); play_regions JSONB mixes a controlled sido key with a free-text sigungu string; session.region is derived and can be permanently NULL for pickups; there is no location country_code; expanding past KR requires a CHECK rewrite (a schema break).
Originally proposed (flat Schema.org address_region string): broken for discovery. "CA" vs "California" vs "캘리포니아" cannot be filtered, grouped, or deduped; PostalAddress is flat (no level-2/3 for 시군구); monolingual; country_code + free-text does not partition level-2 discovery.

The industry consensus: text region fields are display only; filtering runs on controlled-vocab codes (ISO-3166-2 at level 1 + a per-country controlled table at level 2+) or on spatial bounding boxes.

The model — `administrative_divisions` reference table

administrative_divisions          -- one row per admin division, worldwide; controlled vocabulary
  id            UUID PK
  country_code  CHAR(2) NOT NULL            -- ISO-3166-1 alpha-2 (uppercase CHECK)
  iso3166_2     TEXT UNIQUE                 -- 'KR-11','US-CA','JP-13' (level-1 only; NULL for L2+)
  parent_id     UUID REFERENCES administrative_divisions(id)
  level         SMALLINT NOT NULL CHECK (level BETWEEN 1 AND 3)   -- 1=시도/state, 2=시군구/county, 3=읍면동
  slug          TEXT NOT NULL              -- stable, language-invariant key; KR L1 slugs == today's SIDO keys
  geonames_id   BIGINT UNIQUE              -- geocoder-resolution anchor
  names         JSONB NOT NULL DEFAULT '{}'        -- {ko:'서울특별시', en:'Seoul', ja:'ソウル'}
  level_label   JSONB NOT NULL DEFAULT '{}'        -- what THIS country calls this level
  latitude      NUMERIC(9,6)              -- optional centroid for map display
  longitude     NUMERIC(9,6)
  UNIQUE (country_code, slug)
  -- indexes: (country_code, level), (parent_id) WHERE NOT NULL, (iso3166_2) WHERE NOT NULL

FK adoption (expand step — old columns stay, new FKs added):

clubs        + adm1_id UUID FK, adm2_id UUID FK
court_venues + adm1_id UUID FK, adm2_id UUID FK     (also venues — see WS-4)
profiles     + home_adm1_id UUID FK, home_adm2_id UUID FK

profile_play_regions (junction — replaces the play_regions JSONB)
  profile_id UUID FK, adm_id UUID FK, PRIMARY KEY (profile_id, adm_id)

Discovery becomes indexed UUID equality (no LIKE, no sido_key_from_label() bridge):

sql

-- clubs in 경기도            WHERE adm1_id = :gyeonggi_id
-- clubs in 강남구            WHERE adm2_id = :gangnam_id
-- sessions in my play regions
SELECT s.* FROM sessions s JOIN court_venues v ON v.id = s.court_venue_id
WHERE v.adm1_id IN (SELECT adm_id FROM profile_play_regions WHERE profile_id = :me)
   OR v.adm2_id IN (SELECT adm_id FROM profile_play_regions WHERE profile_id = :me)

Per-country level semantics are carried by level + level_label (KR 시도→시군구→읍면동, US state→county→city, JP 都道府県→市区町村) — the hierarchy (parent→child containment) is universal; the labels stay country-native via level_label/names.

Address entry resolves into the model: geocoder (Kakao/Google) → address_components → administrative_area_level_1/2 → look up by iso3166_2/geonames_id/slug → store the FKs. Countries not yet seeded at level 2 store only adm1_id + a free-text address_locality for display only (never filtered on).

Seed: KR level 1 (17 시도, slug == existing SIDO keys → zero app rename) + level 2 (~226 시군구). Backfill adm1_id/adm2_id from existing region/district strings; backfill profile_play_regions from play_regions. Legacy columns are deprecated-not-dropped (contract step is a later migration).

Held (WS-1)

Level-3 (읍/면/동) seeds; PostGIS polygon boundaries for radius/"near me" spatial discovery (ST_Within) when text-level granularity is insufficient.

4. WS-2 — i18n foundation + data-quality

i18n columns — NOT NULL, NO DEFAULT (the anti-corruption rule)

A DEFAULT 'KR' / 'KRW' / 'Asia/Seoul' makes a US row silently inherit Korea (wrong currency denomination, wrong wall-clock, a Korean flag on a US court, KMA weather lookups that return NULL). So:

currency_code  TEXT NOT NULL  CHECK (currency_code ~ '^[A-Z]{3}$')      -- ISO-4217; migrate KR rows = 'KRW'
country_code   TEXT NOT NULL  CHECK (country_code ~ '^[A-Z]{2}$')       -- ISO-3166-1; migrate KR rows = 'KR'
iana_timezone  TEXT NOT NULL  CHECK (iana_timezone IN (SELECT name FROM pg_timezone_names))  -- migrate = 'Asia/Seoul'

Currency on every monetary table: clubs, sessions, dues, session_payments, venues/court_venues rate columns. Existing rows set to 'KRW' in the migration body, not via a column default.
Timezone on sessions (and clubs/venues): un-hardcode 'Asia/Seoul' from auto_advance_session_statuses, signals_pick_pushable (quiet hours), and the 00154-style timestamp reconstruction; read the row's iana_timezone instead. (DST-safe: store wall-clock TIME
- the IANA zone, never a UTC offset.)
Country on profiles/clubs/court_venues/venues/sessions; gate KR-only services (KMA weather, Daum postcode) on country_code = 'KR' behind their existing ports.
Phone: CHECK (phone ~ '^\+[1-9]\d{1,14}$' OR phone IS NULL) (E.164).
nationality_code: tighten to CHECK (~ '^[A-Z]{2}$') (uppercase only).
formatCurrency(amount, currencyCode, locale) replaces formatKRW; fix the EN i18n namespace that still renders ₩/ko-KR.

Data-quality / controlled-vocab fixes (one careful migration, KR-preserving)

Fix	Why
`payment_method` `CHECK` on `dues` + `session_payments`	financial ledger; today unconstrained free text → un-aggregatable
`member_reports.reason` `CHECK`	enables a moderation dashboard
`public_courts.ownership` / `management_type` / `manager_type` `CHECK`	closed gov't-API value sets, today unconstrained
Region `CHECK` alignment on `profiles`/`sessions`/`public_courts`/`court_venues`	`clubs.region` has one; others don't → mismatched keys (interim, until WS-1 FK adoption lands)
`public_courts.surface_type` ↔ native `surface_type` enum sync (`artificial_grass` drift from 00271)	two sources of truth diverged
`court_venues.public_court_id` FK → `public_courts`	the two venue tables share physical courts with no link
`sessions.location` weak-ref	free text parallel to `court_venue_id` can mismatch — derive from the venue

5. WS-3 — Rating integrity (now part of the foundation)

The audit found launch-relevant integrity flaws, not polish. What the system must not do: claim a single ELO is globally comparable, or let an unverified self-rating pollute the competitive pool.

Spine (build now)

profiles + rating_state  TEXT NOT NULL DEFAULT 'unrated'
             CHECK (rating_state IN ('unrated','provisional','established'))
         + rating_source TEXT NOT NULL DEFAULT 'self_assessed'
             CHECK (rating_source IN ('self_assessed','intra_club','inter_club','verified_tournament'))
         + display_tier_sub TEXT CHECK (display_tier_sub IN ('I','II','III'))   -- sub-tier within a tier band

rating_state — an unrated player's match results are stored but apply a 0 delta to opponents until they have ≥3 verified matches (UTR's "no rating until 3 results" rule). Fixes beginner cold-start corrupting calibrated ratings, and is the gate that makes self-rated import safe.
rating_source — the tier badge renders only for intra_club+; a self_assessed value shows "미측정 / unrated" with tap-to-explain. Self-rated NTRP/UTR/WTN never seed competitive ELO or rsvps_enforce_tier — they are a provisional display suggestion only (this is the anti-sandbagging rule; USTA's self-rated NTRP collapse is the cautionary precedent).
Sub-tiers — Gold spans NTRP ~3.5–5.0 (a 600-ELO band, uncompetitive end-to-end). display_tier_sub (III/II/I derived from sub-bands) + sub-tier-aware rsvps_enforce_tier ranges restore granularity.
External-rating storage (display/suggest only): optional ntrp_self_rated NUMERIC(2,1), utr_self NUMERIC(3,1), wtn_self NUMERIC(4,1) — stored for onboarding self-placement + display, never for matchmaking, until verified matches exist.

Honesty fixes (docs/UI, zero migration risk)

Document singles_rd/doubles_rd as INERT (COMMENT ON COLUMN); they never enter the expected-score/K math. Use the already-computed rating_confidence for the reliability pill (provisional/developing/established) — never display an RD number.
Rewrite the stale 7-tier elo-tier-mapping.md to the live 5-tier reality (it currently describes dead platinum/diamond bands — a concrete user-trust bug for onboarding tier copy).
Resolve season_records (never written) — populate or deprecate; do not leave a dead promise.

Held (WS-3)

OpenSkill (Weng-Lin Bradley-Terry, team-native — the right doubles model) migration; verified-tournament result ingest for true cross-population comparability. Do not claim global comparability until then.

6. WS-4 — Venue platform (spine now, satellites held)

court_venues.club_id NOT NULL is conceptually wrong: a venue is a business entity; a club is a lessee; an owner need not belong to any club. We fix the identity now (cheap pre-launch, brutal later) and defer the features.

Spine (build now)

venues                       -- canonical, club-independent, provider-agnostic (Schema.org SportsActivityLocation)
  id            UUID PK
  name          TEXT NOT NULL
  schema_type   TEXT NOT NULL CHECK (schema_type IN ('SportsActivityLocation','SportsClub'))
  venue_type    TEXT NOT NULL CHECK (venue_type IN ('public_court','private_club','academy','hotel_resort','community_center','apartment_complex'))
  public_access BOOLEAN NOT NULL DEFAULT TRUE
  -- i18n address (flat Schema.org PostalAddress; NEVER KR-specific 시/도/구 columns)
  country_code  TEXT NOT NULL  CHECK (~ '^[A-Z]{2}$')
  adm1_id UUID FK, adm2_id UUID FK            -- WS-1 region FKs (controlled discovery)
  street_address TEXT, address_locality TEXT, postal_code TEXT, formatted_address TEXT
  latitude NUMERIC(10,7), longitude NUMERIC(10,7), geog GEOGRAPHY(POINT,4326)
  iana_timezone TEXT NOT NULL                 -- no default
  -- hot-path discovery filters (indexed core; NOT the EAV table)
  court_count INTEGER, has_indoor BOOLEAN NOT NULL DEFAULT FALSE,
  has_lighting BOOLEAN NOT NULL DEFAULT FALSE, primary_surface_code TEXT FK -> surface_catalog
  -- contact / pricing
  telephone TEXT, website_url TEXT, booking_url TEXT,
  price_tier TEXT CHECK (price_tier IN ('free','low','mid','high')), currency_code TEXT,
  -- ratings (denormalized from venue_reviews; maintained by trigger)
  rating_value NUMERIC(3,2), rating_count INTEGER NOT NULL DEFAULT 0, review_count INTEGER NOT NULL DEFAULT 0
  -- media (simple representative pick — NOT the selector engine)
  representative_media_id UUID FK -> venue_media
  -- lifecycle (day-1 integrity: KR public courts get demolished)
  closed_at TIMESTAMPTZ,
  closure_reason TEXT CHECK (closure_reason IN ('permanently_closed','moved','demolished','renamed','merged','temporary')),
  successor_venue_id UUID FK -> venues
  created_at, updated_at TIMESTAMPTZ
  -- reads filter: WHERE closed_at IS NULL OR closure_reason = 'temporary'

surface_catalog              -- controlled vocab, grouped + metadata
  code PK (hard_acrylic|clay_artificial|grass_artificial|carpet|…), family, display(JSONB i18n),
  itf_pace_category, accent_token, sort

venue_courts                 -- 1:N (the relationship — expensive to add later, so build now)
  id PK, venue_id FK, name, sort_order
  surface_code FK -> surface_catalog
  pace_category  CHECK (slow|medium_slow|medium|medium_fast|fast)  null
  has_cushion_layer BOOLEAN
  setting        CHECK (indoor|outdoor|covered_outdoor)
  court_format   CHECK (full_size|sixty_foot|thirty_six_foot)
  has_lighting BOOLEAN, lighting_type CHECK (led|halogen|other) null, has_wall BOOLEAN
  is_bookable BOOLEAN, status CHECK (active|maintenance|seasonal_closure|retired)
  parent_court_id FK -> venue_courts null      -- combo/split courts
  notes, created_at, updated_at

venue_media                  -- replaces court_venues.photo_urls[]; simple model now
  id PK, venue_id FK, kind CHECK (photo|featured|logo), source CHECK (user_upload|operator|seed|import),
  storage_bucket, storage_path, caption, alt_text, sort_order,
  approval_status CHECK (pending|approved|rejected), uploaded_by FK, approved_by FK, approved_at, archived_at
  -- representative = operator/admin pick (is_featured) ELSE latest approved.  NO scoring engine at launch.

court_venues + venue_id UUID FK -> venues      -- THE LINK (expand step): each club instance → canonical venue

The session-FK is never remapped. Path stays session.court_venue_id → court_venues.venue_id → venues. court_venues keeps working unchanged; venues is the new canonical identity bridged by the link. This is the expand step — it makes every later venue change additive. Backfill court_venues.venue_id by grouping on naver_place_id (after a dedup pass + UNIQUE(naver_place_id)), else 1:1 for unmatched/custom venues. public_courts is a seed source for venues.

booking_open_day CHECK is 1–31 (the existing 1–28 cap rejects valid month-end bookings — wrong today, not just internationally).

Held (WS-4) — fully specified, deferred; each slots onto the spine as a new table FK'd to `venues`

Provider conflation — venue_external_refs (venue_id, provider CHECK(naver|kakao|google|osm|manual), external_id, is_primary, confidence, merge_status CHECK(auto_merged|manual_confirmed|disputed|pending), merged_into FK). Append-only, never auto-merge (Placekey/Overture/Foursquare keep raw sources + a confidence-scored canonical; same building/chains/moved-venues all mis-merge on naive distance). For KR launch, naver_place_id on the venue is sufficient — multi-provider merge is premature until a second provider's data flows.
Operators — venue_managers (venue_id, user_id, role CHECK(owner|operator|staff), status CHECK(pending|verified|rejected|revoked), claim_scope CHECK(suggest_only|edit_own_uploads|full_editor), verified_at, verified_by). Claim/verify (Google-Business-Profile model: postcard/phone/email/physical proof — registry lookup alone never suffices, and varies by country). Abuse guard: two operators claiming a shared public court queue for manual resolution; first claim is suggest_only, never full edit without a second approval. Authorization via SECDEF RPC, not an inline RLS predicate.
Photo selector engine — pluggable scorers (uploader authority + Wilson community votes + recency decay + quality-fit − report penalties), materialized to representative_media_id. Backed by venue_media_votes + venue_media_flags. Built only once venues carry ≥~50 photos with real vote data — Wilson needs N≥30 to beat noise; at launch volume (0–2 votes) it is theater. The simple "operator pick → latest approved" policy ships now.
Amenity catalog — venue_amenity_features (Schema.org LocationFeatureSpecification: venue_id, property_id FK -> amenity_catalog, value JSONB) for display-only / rare amenities. Filter-critical amenities stay as indexed boolean columns on venues (the EAV row-per-feature pattern is a filtering anti-pattern — N index scans). Promotion rule: a feature becomes a core column the moment it appears in a discovery filter.
Open photo-upload gate (attendee OR club-admin-of-a-using-club OR verified-operator) — via a SECDEF RPC that checks authorization procedurally (early-return), with simple USING (uploaded_by = auth.uid()) ownership RLS on the table. (A 4-table inline RLS predicate is intractable under concurrent post-session upload bursts.)
Venue reviews — venue_reviews (evolve court_reviews) → denormalized rating_* on venues.

The card payoff

selectVenueHeroImage(venue) = representative_media_id photo → static map (legal, distinct per venue, already in use via getStaticMapUrl) → surface-themed tile (CourtLinesTile + surface_catalog accent). The venue-centric SessionCard variant + dev-panel A/B toggle ship on this spine — the original goal, on the corrected foundation. The no-photo map state doubles as the "사진 추가" UGC entry point.

7. Migration sequence (each `db reset`-validated before any `db push`)

WS-1a administrative_divisions + surface_catalog + venue_type vocab + KR seeds (L1 + L2).
WS-1b FK adoption (adm1_id/adm2_id on clubs/court_venues/profiles) + profile_play_regions + backfill from existing strings/JSONB. Legacy region columns deprecated-not-dropped.
WS-2a i18n no-default columns (currency_code/country_code/iana_timezone) + explicit KR-row migration + un-hardcode 'Asia/Seoul' in SQL functions + E.164/nationality_code CHECKs.
WS-2b data-quality CHECK + FK fixes (payment_method, member_reports.reason, gov't fields, surface_type sync, court_venues.public_court_id, region CHECK alignment).
WS-3 rating_state + rating_source + display_tier_sub + external-rating columns + rsvps_enforce_tier sub-tier/state-aware update; RD COMMENT; doc rewrite.
WS-4a venues canonical + venue_courts + venue_media + lifecycle + surface_catalog adoption + UNIQUE(naver_place_id) (post-dedup).
WS-4b court_venues.venue_id link + backfill (group by naver_place_id) + venues seed from public_courts. Sessions untouched.
Client hex layers (Venue/VenueCourt/VenueMedia + AdministrativeDivision entities → ports → adapters → mappers → hooks) + selectVenueHeroImage + the card variant + dev toggle.

After each migration: regenerate generated.types.ts, update Zod enums, run yarn check.

8. What we explicitly do NOT claim or build at launch

No global rating comparability. A KR 1600 ≠ a US 1600 (disconnected comparison graphs). Do not display ELO as UTR/WTN-equivalent. Document the limit in-product.
No self-rated competitive seeding. Self-rated NTRP/UTR/WTN never gate RSVPs or seed competitive ELO.
No multi-provider conflation engine, no venue_managers claim/verify, no photo selector engine, no amenity EAV. Held satellites — added additively when the data/infra justifies them.
No DEFAULT 'KR'/'KRW'/'Asia/Seoul' on i18n columns — SUPERSEDED. 00281 pragmatically shipped NOT NULL DEFAULT KR values: 100% of current/near-term data is Korea (the default is always correct, never a corruption), and a no-default column would break every existing writer (none pass currency/country today). The silent-mis-inheritance risk is guarded by the CREATION FLOW instead — any non-KR club/venue/ profile MUST set these explicitly when international onboarding lands. The default is the KR safety net.

8a. App-wiring decisions (2026-06-21)

Made while wiring the foundation into the client (the loop after WS-1→WS-4 landed):

Venue read hex layers — EXTEND, don't scaffold a parallel model. The existing Venue entity already IS court_venues (carries photoUrls/surfaceType/lat-lng) and is deeply wired across the app. The identity-fixing SCHEMA (canonical venues + the court_venues.venue_id auto-link bridge) is built and is the expensive, hard-to-change part. The READ hex layers for the new tables (a canonical-venues entity, venue_courts, venue_media, administrative_divisions) are DEFERRED to the features that actually consume them (a public-venue directory, the UGC upload flow, normalized region discovery) rather than scaffolded unused now — building them with no consumer contradicts single-source-of-truth + the "build during the next feature touch" rule. The venue-centric SessionCard variant is built on the EXISTING Venue (its lat-lng → static map, surfaceType → accent tile; photoUrls/venue_media are both empty in practice until UGC lands, so neither changes the card today).
payment_method controlled vocab — DONE (00285). bank | cash | kakaopay | toss | other, language- invariant keys + i18n labels, z.enum (paymentMethodSchema) + a NOT-VALID DB CHECK on dues / session_payments / payment_history. Locked NOW (no client writer exists yet) so the future payment picker is forced to conform — the ideal time to lock a vocab. other is the international escape hatch.
member_reports.reason vocab — DEFERRED (NOT a CHECK). Review showed reason is a free-text DESCRIPTION (no report UI, no defined category set), not a category. The correct controlled vocab is a SEPARATE category enum column designed WITH the report UI — forcing a CHECK onto the free-text column now would bake in a wrong model. Add the category enum + its CHECK when the report flow is built.

8b. Adversarial-audit fixes + deferred hazards (2026-06-21, migration 00286)

A 3-agent red-team of the whole foundation (schema/RLS/migration-safety, trigger behavior, app wiring) found 1 blocker + 2 should-fixes, all closed in 00286; plus 2 items deliberately deferred with reasons.

[BLOCKER, fixed] Rating-source downgrade bypass of the tier gate. sync_rating_state fired only on elo_rating/match-count columns, so a client could UPDATE profiles SET rating_source='self_assessed' on their own established profile (own-row RLS + column grant) WITHOUT re-firing the trigger, persist established+self_assessed, and the relaxed gate would then let them confirm into a session above their band. Fix: add rating_source, rating_state to the trigger's OF list so any direct write re-fires the monotonic derivation (restores intra_club for a matched player). Probe-proven closed.
[SHOULD-FIX, fixed] Over-broad grants on administrative_divisions + profile_play_regions (00279/ 00280 omitted the REVOKE ALL ... FROM anon, authenticated that 00283 does). RLS already blocked writes; 00286 tightens the grant layer to SELECT-only.
[SHOULD-FIX, fixed] rsvps_enforce_tier inline-threshold copy. 00282 re-derived the gate from a pre-00235 base, re-introducing the inline elo→index CASE instead of private.tier_index_from_elo. 00286 re-issues it through the helper (single source) — behavior identical, drift footgun removed.
[DEFERRED, no DB CHECK for the rating invariant] A CHECK enforcing rating_state='unrated' ⟺ rating_source='self_assessed' was considered and rejected: it would BREAK the future verified-import flow (an unrated 0-in-app-match player with a verified_tournament source is a valid future state). The trigger OF-list (above) is the correct, future-safe mechanism.
[DEFERRED, registry naming hazard] The client registry.venues key maps to the LEGACY court_venues table (the Venue entity). When the WS-4 canonical-venues read layer is eventually built, do NOT name its port venues (it would shadow the existing key) — use a distinct name (e.g. canonicalVenues), and consider renaming the existing key to courtVenues at that time. Not renamed now (pre-emptive churn on a working key for a deferred feature); flagged here so the future engineer sees it.

8c. Timezone localization (2026-06-21, migration 00287) — the WS-2b tz-function rewrite, now DONE

The WS-2a/2b deferral "un-hardcode 'Asia/Seoul' in SQL functions" is completed (owner: do it before the push). The DB server runs in UTC, so two latent bug classes existed: functions hardcoding 'Asia/Seoul' (correct only for KR) and functions using CURRENT_DATE / now()::date (UTC server-day, off-by-a-day from the local day in the KST early-morning window). 00287 rewrites 6 time-sensitive functions to interpret local wall-clock in each entity's iana_timezone via the canonical idioms (date+time) AT TIME ZONE tz (local→instant) and (now() AT TIME ZONE tz)::date (local today), with a defensive COALESCE(tz,'Asia/Seoul'):

session-time semantics → the session's iana_timezone: private.classify_rsvp_cancel (cancel→strike window), private.auto_advance_session_statuses (per-row session-end vs now(), dropped the global now_kst), public.generate_session_reminders (tomorrow), public.signals_on_weather_warning_activated (date window), public.get_club_discovery_summaries (upcoming filter).
user-time semantics → the profile's iana_timezone: public.signals_pick_pushable quiet-hours (per-recipient local time-of-day; the 24h rolling frequency caps were already tz-independent — untouched).

For KR rows (iana_timezone='Asia/Seoul') behavior is identical — proven by an equivalence probe (the new AT TIME ZONE form == the old hardcoded string) and a gold-standard classify_rsvp_cancel trigger probe: the SAME wall-clock cancel is no_show for an Asia/Seoul session (unchanged) and late_cancel for an America/New_York session (~13h-later instant) — demonstrably tz-aware. Remaining tz-naive date math (get_home_signals rolling 14d/7d windows, get_club_analytics month counts, signals_tick_dues days-past) is left as-is: rolling/aggregate windows where a sub-day UTC boundary is negligible.

9. References

Adversarial audit (2026-06-21): region/geography · venue+i18n · rating red-teams (this session).
foundational-schema-decisions.md — openskill/glicko/rrule/ISO decisions.
Fowler, Parallel Change; Ambler & Sadalage, Refactoring Databases (expand-and-contract).
ISO-3166-2 (subdivisions), GeoNames admin codes, CLDR (localized division names).
Schema.org SportsActivityLocation / PostalAddress / LocationFeatureSpecification / OpeningHoursSpecification / AggregateRating.
ITF Classified Surfaces (CPR pace categories); Playtomic/ClubSpark/CourtReserve court modelling.
UTR / ITF WTN (cross-population comparability via verified results); USTA self-rated NTRP history.
Wilson score interval (Evan Miller); Placekey / Overture Maps / Foursquare POI conflation.

International Data-Model Foundation ​

0. The evolution discipline (read this first) ​

1. Scope & workstreams ​

2. Controlled-vocabulary policy ​

3. WS-1 — Region / administrative-geography (the keystone) ​

Why the current + originally-proposed models both fail ​

The model — administrative_divisions reference table ​

Held (WS-1) ​

4. WS-2 — i18n foundation + data-quality ​

i18n columns — NOT NULL, NO DEFAULT (the anti-corruption rule) ​

Data-quality / controlled-vocab fixes (one careful migration, KR-preserving) ​

5. WS-3 — Rating integrity (now part of the foundation) ​

Spine (build now) ​

Honesty fixes (docs/UI, zero migration risk) ​

Held (WS-3) ​

6. WS-4 — Venue platform (spine now, satellites held) ​

Spine (build now) ​

Held (WS-4) — fully specified, deferred; each slots onto the spine as a new table FK'd to venues ​

The card payoff ​

7. Migration sequence (each db reset-validated before any db push) ​

8. What we explicitly do NOT claim or build at launch ​

8a. App-wiring decisions (2026-06-21) ​

8b. Adversarial-audit fixes + deferred hazards (2026-06-21, migration 00286) ​

8c. Timezone localization (2026-06-21, migration 00287) — the WS-2b tz-function rewrite, now DONE ​

9. References ​

International Data-Model Foundation

0. The evolution discipline (read this first)

1. Scope & workstreams

2. Controlled-vocabulary policy

3. WS-1 — Region / administrative-geography (the keystone)

Why the current + originally-proposed models both fail

The model — `administrative_divisions` reference table

Held (WS-1)

4. WS-2 — i18n foundation + data-quality

i18n columns — NOT NULL, NO DEFAULT (the anti-corruption rule)

Data-quality / controlled-vocab fixes (one careful migration, KR-preserving)

5. WS-3 — Rating integrity (now part of the foundation)

Spine (build now)

Honesty fixes (docs/UI, zero migration risk)

Held (WS-3)

6. WS-4 — Venue platform (spine now, satellites held)

Spine (build now)

Held (WS-4) — fully specified, deferred; each slots onto the spine as a new table FK'd to `venues`

The card payoff

7. Migration sequence (each `db reset`-validated before any `db push`)

8. What we explicitly do NOT claim or build at launch

8a. App-wiring decisions (2026-06-21)

8b. Adversarial-audit fixes + deferred hazards (2026-06-21, migration 00286)

8c. Timezone localization (2026-06-21, migration 00287) — the WS-2b tz-function rewrite, now DONE

9. References