WhirLocal Database Restructure Plan

Context

WhirLocal is a local community/business platform. The minigolf app (this repo) was built first as a standalone micro app, but the vision is a broader platform with business listings, neighborhoods, reviews, a "WhirLocal Pass" coupon system, and more micro apps (bowling, etc.).

Problem: All data currently lives in the public PostgreSQL schema, with business/venue data baked into minigolf-specific tables (especially courses). This prevents data reuse across apps and blocks the WhirLocal Proper build-out.

Goal: Restructure the database into clean PostgreSQL schemas that separate shared platform data from minigolf-specific data, design the full WhirLocal platform schema, and do it all incrementally without breaking the live app.

Strategy: Additive-only migration — create new schemas/tables alongside existing ones, populate them, update app code, then deprecate old tables.

Phase 0: Create Monorepo + Import Minigolf

This happens BEFORE any database work. Create a new repo that will house all WhirLocal apps.

0a. Create the monorepo

whirlocal/                    ← NEW repo
├── apps/
│   ├── minigolf/             ← this repo's src/, public/, index.html, vite config, etc.
│   └── whirlocal/            ← placeholder for Astro app (created later)
├── packages/
│   └── shared/               ← shared Supabase types, design tokens (created later)
├── supabase/                 ← moved from minigolf, lives at root
│   ├── config.toml
│   ├── migrations/
│   └── seed.sql
├── package.json              ← pnpm workspace root
├── pnpm-workspace.yaml
├── turbo.json                ← optional (Turborepo for build orchestration)
└── .env.local                ← Supabase keys (shared across apps)

0b. Steps

Create new repo whirlocal on GitHub
Initialize workspace root:
- pnpm init at root
- Create pnpm-workspace.yaml with packages: ["apps/*", "packages/*"]
Import minigolf:
- Copy this repo's contents into apps/minigolf/
- Preserve git history via git subtree add or accept a clean import
- Update apps/minigolf/package.json name to @whirlocal/minigolf
Move supabase/ to root:
- Move apps/minigolf/supabase/ → supabase/ (root level)
- Update apps/minigolf/ Supabase client import path if needed
- The Supabase CLI reads from repo root by default — this just works
Move .env.local to root — Supabase keys shared by all apps
Update minigolf paths:
- Vite config: ensure root and publicDir are correct
- Import aliases (@/ etc.) still resolve within apps/minigolf/
- Verify pnpm dev --filter @whirlocal/minigolf starts the dev server
Verify minigolf still works: full dev server startup, can play a round

0c. Files created/modified

package.json (root workspace)
pnpm-workspace.yaml
apps/minigolf/package.json (renamed)
apps/minigolf/vite.config.ts (path updates if needed)
apps/minigolf/src/integrations/supabase/client.ts (verify env vars resolve)

0d. Acceptance criteria

pnpm install at root succeeds
pnpm dev --filter @whirlocal/minigolf starts dev server
Minigolf app functions identically (start round, play, finish, admin)
supabase/ at root, npx supabase gen types works from root
No broken imports

Target Architecture: Three Schemas

whirlocal schema (shared platform data)
├── venues          ← venue/location data extracted from public.courses
├── neighborhoods   ← geographic areas (NEW)
├── categories      ← business type tags (NEW)
├── venue_categories ← many-to-many (NEW)
├── reviews         ← community ratings/feedback (NEW)
├── passes          ← WhirLocal Pass coupons/discounts (NEW)
├── contacts        ← platform-level marketing contacts (migrated from course_contacts)
└── pass_claims     ← redemption tracking (NEW)

minigolf schema (minigolf-specific)
├── courses         ← minigolf config only, FK → whirlocal.venues
├── holes           ← migrated from public.holes
├── rounds          ← migrated from public.rounds
├── round_players   ← migrated from public.round_players_public
├── scores          ← migrated from public.scores
├── leaderboard_entries ← migrated from public.course_leaderboard_entries
├── round_feedback  ← migrated from public.round_feedback
└── round_contacts  ← migrated from public.round_contacts_private

public schema (stays — auth, orgs, platform infra)
├── organizations, organization_users, profiles, platform_admins
├── sponsors, sponsor_slots, sponsorships, sponsor_requests
├── sponsor_pricing_models, sponsor_analytics_events
├── email_send_log, email_send_state, suppressed_emails, email_unsubscribe_tokens
└── admin_audit_logs

Phase 1: Create `whirlocal` Schema + Venues Table

Migration file: supabase/migrations/YYYYMMDD_phase1_whirlocal_schema.sql

1a. Create schema

CREATE SCHEMA whirlocal;
GRANT USAGE ON SCHEMA whirlocal TO authenticated, anon, service_role;

1b. `whirlocal.venues` — extracted from `public.courses`

Columns extracted from courses (venue/location data):

id, organization_id (FK → public.organizations)
name, slug (UNIQUE)
phone, website, email
address_line_1, address_line_2, city, state, postal_code, country
latitude, longitude (DECIMAL — for future geo search)
logo_url, header_bg_color, body_bg_image_url, body_bg_overlay_style
timezone, operating_hours (JSONB)
header_scripts, body_scripts, footer_scripts
is_active, is_verified (NEW — for directory trust)
seo_title, seo_description (NEW — for WhirLocal Proper SEO pages)
created_at, updated_at

Columns that stay minigolf-specific (NOT in venues):

number_of_holes, daily_code, daily_code_enabled, daily_code_last_generated
course_rules, require_rules_acceptance, handicap_accessible
public_leaderboard_enabled, shareable_rounds_enabled
enable_social_sharing, social_share_bg_image_url, share_headline_text, share_cta_text
giveaway_enabled, giveaway_title, giveaway_description, giveaway_terms_url
prize_enabled, prize_details
theme, whirlocal_profile_url
sponsor_pricing_model_id, sponsor_pricing_helper_*

1c. `whirlocal.neighborhoods`

id, name, slug (UNIQUE)
description, city, state
latitude, longitude (center point)
bounds (JSONB — optional polygon for map display)
image_url
is_active, created_at, updated_at

1d. `whirlocal.categories`

id, name, slug (UNIQUE)
parent_category_id (self-FK, for hierarchy: "Entertainment" > "Minigolf")
icon, sort_order
created_at

1e. `whirlocal.venue_categories` (many-to-many)

venue_id (FK → venues), category_id (FK → categories)
is_primary (BOOLEAN — one primary per venue)
UNIQUE(venue_id, category_id)

1f. `whirlocal.venue_neighborhoods` (many-to-many)

venue_id (FK → venues), neighborhood_id (FK → neighborhoods)
UNIQUE(venue_id, neighborhood_id)

1g. Indexes

venues(slug) WHERE is_active
venues(organization_id)
venues(city, state) WHERE is_active
neighborhoods(slug)
venue_categories(venue_id), venue_categories(category_id)

1h. RLS Policies for venues

anon SELECT: is_active = true
authenticated SELECT: org member OR is_active = true
INSERT/UPDATE/DELETE: org manager+ (via organization_users check)

1i. Populate venues from courses

INSERT INTO whirlocal.venues (id, organization_id, name, slug, phone, website, ...)
SELECT gen_random_uuid(), organization_id, name, slug, phone, website, ...
FROM public.courses;

Validation: Row count matches. All slugs unique. All org FKs resolve.

App impact: NONE. Minigolf app still reads public.courses. New tables sit alongside.

Phase 2: New WhirLocal Platform Tables

Migration file: supabase/migrations/YYYYMMDD_phase2_whirlocal_platform.sql

2a. `whirlocal.reviews`

id, venue_id (FK → venues), user_id (FK → auth.users)
rating (INT 1-5), title, body
source (TEXT — 'direct', 'round_feedback', 'import')
status (TEXT — 'published', 'pending', 'flagged', 'removed')
created_at, updated_at
UNIQUE(venue_id, user_id) — one review per user per venue

2b. `whirlocal.passes` (WhirLocal Pass offers)

id, venue_id (FK → venues), organization_id (FK → organizations)
title, description
offer_type (TEXT — 'percentage_discount', 'fixed_discount', 'bogo', 'free_item')
offer_value (NUMERIC — e.g., 10 for 10%)
terms, redemption_code
start_date, end_date
max_claims (INT, nullable for unlimited)
claim_count (INT DEFAULT 0)
is_active, created_at, updated_at

2c. `whirlocal.pass_claims` (redemption tracking)

id, pass_id (FK → passes), user_id (FK → auth.users)
claimed_at, redeemed_at (nullable — claimed vs actually used)
UNIQUE(pass_id, user_id) — one claim per user per pass

2d. `whirlocal.contacts` (platform-level, migrated from course_contacts)

id, organization_id (FK → organizations)
venue_id (FK → venues, nullable — NULL = org-wide)
name, email
source (TEXT — 'leaderboard', 'scorecard', 'pass_claim', 'direct', 'round_start')
source_id (UUID, nullable — round_id, pass_id, etc.)
marketing_opt_in (BOOLEAN)
created_at
UNIQUE(organization_id, email)

2e. Populate contacts from course_contacts

INSERT INTO whirlocal.contacts (...)
SELECT cc.id, c.organization_id, v.id, cc.name, cc.email, cc.source, cc.round_id, cc.marketing_opt_in, cc.created_at
FROM public.course_contacts cc
JOIN public.courses c ON c.id = cc.course_id
JOIN whirlocal.venues v ON v.slug = c.slug;

App impact: NONE. Still additive.

Phase 3: Create `minigolf` Schema + Move Game Tables

Migration file: supabase/migrations/YYYYMMDD_phase3_minigolf_schema.sql

3a. Create schema

CREATE SCHEMA minigolf;
GRANT USAGE ON SCHEMA minigolf TO authenticated, anon, service_role;

3b. `minigolf.courses` — minigolf-specific config only

id, venue_id (FK → whirlocal.venues)
All minigolf-specific columns listed in Phase 1b "stays minigolf-specific"
is_active, archived_at, created_at, updated_at

3c. Populate from public.courses

INSERT INTO minigolf.courses (id, venue_id, number_of_holes, daily_code, ...)
SELECT c.id, v.id, c.number_of_holes, c.daily_code, ...
FROM public.courses c
JOIN whirlocal.venues v ON v.slug = c.slug AND v.organization_id = c.organization_id;

Important: minigolf.courses.id = public.courses.id — preserves all existing FK references from rounds, holes, etc. during transition.

3d. Copy game tables to minigolf schema

For each: holes, rounds, round_players_public, scores, course_leaderboard_entries, round_feedback, round_contacts_private:

CREATE TABLE in minigolf schema with same structure but updated FKs
INSERT data from public table
Verify row counts match

3e. Create backwards-compatibility views

-- So existing app code keeps working during transition
CREATE OR REPLACE VIEW public.courses_compat AS
SELECT mc.id, v.organization_id, v.name, v.slug,
       v.address_line_1, v.city, v.state, v.postal_code, v.country,
       v.phone, v.website, v.logo_url, v.timezone, v.operating_hours,
       mc.number_of_holes, mc.daily_code, mc.theme, ...
FROM minigolf.courses mc
JOIN whirlocal.venues v ON v.id = mc.venue_id;

App impact: NONE yet. Views available for gradual switchover.

Phase 4: Update RPC Functions

Migration file: supabase/migrations/YYYYMMDD_phase4_update_rpcs.sql

All existing RPCs need SET search_path = public, minigolf, whirlocal and updated JOINs.

RPCs to update (13 total):

get_round_data — JOIN minigolf.rounds → minigolf.courses → whirlocal.venues
save_hole_scores — reference minigolf.scores, minigolf.holes
finish_round — reference minigolf tables
start_round — reference minigolf.courses + whirlocal.venues
get_course_sponsors — JOIN through minigolf.courses → whirlocal.venues
get_public_leaderboard — reference minigolf.leaderboard_entries
get_admin_course_leaderboard — same
get_giveaway_entries — same
get_effective_plan — no change needed (org-level)
course_has_daily_code — reference minigolf.courses
is_course_open — JOIN minigolf.courses → whirlocal.venues (operating_hours)
verify_daily_code — reference minigolf.courses
delete_round — reference minigolf tables
get_shared_round — similar to get_round_data
save_share_image — reference minigolf.rounds
onboard_new_user — no change needed
get_course_leaderboard — reference minigolf tables

Key: RPCs should return the SAME shape as today so frontend code doesn't break. The JOIN path changes, but the JSON output stays identical.

App impact: NONE if RPC return shapes are preserved. This is the critical invariant.

Phase 5: Update Frontend Code

This is where the minigolf app switches to the new schema.

5a. Direct table queries to update (~40 references across ~20 files)

Venue data queries (address, logo, hours, etc.) → whirlocal.venues:

src/pages/AdminCourseEdit.tsx (~25 references — biggest file to change)
src/pages/AdminCourses.tsx
src/pages/AdminCourseNew.tsx
src/pages/AdminCourseScripts.tsx
src/pages/AdminContacts.tsx
src/pages/AdminDashboard.tsx
src/pages/AdminSponsorRequests.tsx
src/pages/AdminLeaderboard.tsx
src/pages/PlatformArchivedCourses.tsx
src/pages/LeaderboardDisplay.tsx
src/components/SponsorExpirationAlerts.tsx
src/components/SponsorPricingHelper.tsx
src/components/CourseScriptsEditor.tsx
src/hooks/useCourseScripts.ts

Public course views (currently courses_public) → whirlocal.venues + join:

src/pages/CourseStart.tsx
src/pages/CourseLeaderboard.tsx
src/pages/EmbedLeaderboard.tsx
src/pages/CourseSponsorOpportunities.tsx
src/pages/RoundResults.tsx
src/pages/SharedRound.tsx

RPC calls (~40 references) → No changes needed if Phase 4 preserves return shapes.

5b. Regenerate Supabase types

npx supabase gen types typescript --project-id <id> > src/integrations/supabase/types.ts

5c. Update admin pages to split venue vs minigolf config

AdminCourseEdit.tsx currently edits one courses row. It needs to:

Write venue fields (name, address, logo, hours) → whirlocal.venues
Write minigolf fields (daily_code, theme, giveaway) → minigolf.courses

This is the most complex frontend change.

Phase 6: Cleanup + Deprecation

Only after Phase 5 is fully deployed and verified.

Drop public.courses table (replaced by whirlocal.venues + minigolf.courses)
Drop public.holes, public.rounds, etc. (replaced by minigolf.* tables)
Drop public.course_contacts (replaced by whirlocal.contacts)
Drop backwards-compatibility views
Drop old RPC versions if any were versioned

Files to Modify (by phase)

Phase	Files	Type
1-3	`supabase/migrations/` (3 new files)	SQL
4	`supabase/migrations/` (1 new file — RPC updates)	SQL
5	`src/integrations/supabase/types.ts` (regenerated)	TS
5	`src/pages/AdminCourseEdit.tsx` (heaviest changes)	TSX
5	~19 other page/component files (query updates)	TSX
5	`src/hooks/useCourseScripts.ts`	TS
6	`supabase/migrations/` (1 cleanup file)	SQL

Verification Plan

After Phase 1-2 (new tables created):

SELECT count(*) FROM whirlocal.venues matches SELECT count(*) FROM public.courses
All venue slugs are unique
All organization FK references resolve
RLS: anon can SELECT active venues, cannot INSERT/UPDATE/DELETE
RLS: authenticated org member can SELECT their venues

After Phase 3 (minigolf schema):

minigolf.courses count matches public.courses
All minigolf.courses.id values match public.courses.id
minigolf.holes, rounds, scores counts all match public equivalents
Compat view public.courses_compat returns same shape as public.courses

After Phase 4 (RPCs updated):

get_round_data returns identical JSON for an existing round
start_round creates round in minigolf schema
finish_round completes round, updates minigolf.leaderboard_entries
get_public_leaderboard returns same results
get_course_sponsors still works

After Phase 5 (frontend updated):

Full E2E: visit course → start round → play holes → finish → leaderboard
Admin: edit venue info (name, address, logo) → saved to whirlocal.venues
Admin: edit minigolf config (daily code, theme) → saved to minigolf.courses
Public leaderboard pages render correctly
Shared round links still work
Sponsor displays unchanged
Contact list shows in admin

Future: WhirLocal Proper (Astro App)

After the DB restructure (Phases 1-6), the Astro app for WhirLocal Proper can be scaffolded at apps/whirlocal/. It will:

Use the same Supabase instance and root supabase/ config
Import shared types from packages/shared/
Handle SEO-critical pages: business directory, neighborhoods, venue profiles, reviews
Use Astro's static/SSR rendering for search engine visibility
Embed React islands for interactive components (pass claims, review forms)

This is a separate planning effort once the DB foundation is solid.

Risk Mitigation

Additive-only: New tables created alongside old. Nothing drops until Phase 6.
ID preservation: minigolf.courses.id = public.courses.id so all existing FKs work during transition.
Compat views: If frontend changes break, we can temporarily point back at views.
RPC shape preservation: Frontend RPC calls don't change — only internal JOINs do.
Rollback: At any phase, the old public.* tables still exist with the original data.

Xhynk/typed-squishing-cosmos.md