Global Schema Overhaul — Target State (pre-launch clean break)

Status: Active Last reviewed: 2026-08-10

Owner-ratified 2026-08-10: the app is NOT live, so instead of user-safe expand-and-contract we overhaul models/schemas directly to global-deployment best practice. This doc is the target state, standards-grounded; region-cutover-plan.md phases execute against it in clean-break mode (no dual-write ceremony, no legacy read paths kept). Wizard iteration continues in parallel — this arc must not block it.

Standards stack (the research, distilled)

Concern	Standard	Adoption
Countries/subdivisions	ISO-3166-1/2	✅ administrative_divisions already models it — becomes the ONLY geography
Localized names	CLDR-style locale maps	names JSONB {locale: text} (spine already does this) — extend to all data-driven labels
Currency	ISO-4217 + minor units	amount_minor BIGINT + currency CHAR(3); formatting via Intl.NumberFormat(locale, {currency}) — retire formatKRW
Time	UTC timestamptz + IANA tz per event/venue	Already largely done (00281/00287); recurrence moves to RRULE(-style) + tz-aware expansion (kills the DST landmine); crons compute per-row local windows, fire hourly
Locale	BCP-47 on profiles	profiles.locale distinct from country (residence) and jurisdiction (legal, derives per consent matrix)
Phone	E.164 storage	libphonenumber already in stack — enforce at write
External providers	Neutral ref table	external_refs(entity_type, entity_id, provider, external_id) — venues first (naver/kakao/google rows), retires naver_place_id as the anchor
Place/venue semantics	Schema.org	✅ venues spine already modeled on it

Target-state deltas by area

1. Geography (clean break of the TEXT vocabulary). adm FKs become THE region columns; the 17-key TEXT columns + CHECKs are DROPPED (display resolves through spine names). SIGUNGU_BY_SIDO client table → spine adm2 queries. coords-to-region → nearest-adm lookup. Region string enum retires; entities carry adm1Id/adm2Id (+ slug for URLs). Wizard/search/discovery read the spine only.
2. Money. All amount columns become minor-unit integers with an explicit currency column (KRW rows unchanged numerically — zero-decimal); one formatCurrency(amountMinor, currency, locale) replaces formatKRW; subscription pricing moves to pricing-region.config.
3. Ratings (folds in the three-track ruling). Replace profiles.elo_rating with a normalized player_ratings(profile_id, discipline, rating, matches_played, updated_at) table — 단식/복식/혼복 rows, never consolidated; extensible to future sports without another migration. ELO history gains discipline. RPC 00068 branches per discipline. Tier/leaderboard/badge surfaces read per-track (복식 primary in KR config).
4. Elite/anchor data. profiles.elite_background (EliteBackground enum) + verified external ratings (player_external_ratings(profile_id, scale, value, verified_at, evidence)) — the anchor-member calibration substrate.
5. Provider ports. Weather port redesigned lat/lng-native; venue search port drops naverXxx fields for externalRef; holidays port takes countryCode, entity renamed. Registry wires per-market like maps.
6. Content extraction. Achievement catalog / trust & manner labels / role titles / season names / zod messages / SQL-embedded copy → i18n; seasons become (year, quarter) numeric with display-time labels (hemisphere-neutral); LocalizedText {ko,en} → locale-keyed records.

Execution series (clean break; each ≈ one focused session, agent-fleet friendly)

• S1 Geography contract — the Phase-2/3/4 collapse: read paths + entities to adm FKs, drop TEXT columns/CHECKs, seed-verify with scenarios. (Highest leverage; unblocks nothing else — do first.)
• S2 Ratings table — three-track split via player_ratings (+ history discipline, RPC branch, surfaces).
• S3 Money — minor units + currency + formatCurrency sweep.
• S4 External refs — table + venue provider port neutralization.
• S5 Recurrence/tz — RRULE-style storage + DST-safe expansion + cron re-timing.
• S6 Content extraction — the mechanical i18n sweep (largest volume, lowest risk, parallelizable).

Wizard-improvement work continues between series; S-series items never block it.

S5 progress (2026-08-10)

Shipped: shiftRecurrenceDeadline (packages/app/src/domain/entities/recurrence.entity.ts) no longer shifts the deadline's absolute instant by N×24h ms — it decomposes the deadline into wall-clock fields IN a given IANA timezone (Intl.DateTimeFormat().formatToParts), advances only the calendar-date field by the whole-day offset, and recomposes the instant via the standard two-pass offset-correction technique. New signature: shiftRecurrenceDeadline(deadline, firstDate, childDate, ianaTimezone = 'Asia/Seoul'). Both callers (use-create-session.ts, host-pickup-game.usecase.ts) still call it without the 4th arg — neither CreateSessionInput nor Session exposes ianaTimezone client-side yet (DB-defaulted via COALESCE(iana_timezone, 'Asia/Seoul'), 00281/00287) — so behavior is byte-identical to pre-S5 for every existing (KR) session; threading a real per-session tz through these call sites is future work once a non-KR session can actually exist. Pinned in globalization-sample.test.ts: KR byte-identical regression, US spring-forward wall-clock preservation, EU (Europe/Berlin) late-March transition wall-clock preservation.

SQL side — no migration needed. Recurring child sessions are created entirely client-side (the two call sites above); no SQL function does whole-day date-shift arithmetic on a stored deadline. The iana_timezone-aware functions 00287 already fixed (classify_rsvp_cancel, hold-expiry, reminder windows, rsvp_deadline_soon, etc.) all compose a wall-clock instant from (date + time) AT TIME ZONE COALESCE(iana_timezone, 'Asia/Seoul') fresh per row at query time — never shift a previously-stored deadline forward by an interval — so they were already DST-safe and untouched by S5.

Remaining — cron fire-time (analysis only, no changes made): several pg_cron schedules are pinned to a UTC offset chosen to land on a nice KST wall-clock time, e.g. signals-rsvp-deadline-soon at '30 20 * * *' (20:30 UTC = 05:30 KST, commented "ahead of generate-reminders-daily (06:00 KST) and signalstick_dues (09:00 KST)"), and the weather medium-forecast fetches at '0 22 * * *' / '0 10 * * *' (07:00/19:00 KST). For a non-KR market these crons fire at an arbitrary, non-local hour — a US user's "05:30 KST" RSVP-deadline nudge lands mid-afternoon or worse depending on the season. This is a _fire-time problem, not a math problem — the per-row date/window comparisons inside each function are already tz-aware (00287 pattern: AT TIME ZONE COALESCE(row.iana_timezone, 'Asia/Seoul')) and correct for any row regardless of when the cron fires. The fix, when a non-KR market ships, is the same pattern already used for date windows: switch the daily-at-a-fixed-KST-time schedules to fire hourly, and have each function filter to rows whose own local wall-clock currently falls in the intended window (e.g. WHERE EXTRACT(HOUR FROM (now() AT TIME ZONE COALESCE(s.iana_timezone,'Asia/Seoul'))) = 6 for a "06:00 local" reminder) instead of relying on the cron's single global fire time. No cron schedules were changed in S5 — this is deferred until a session/profile with a non-Asia/Seoul iana_timezone actually exists in production.