Supabase / DB Restructure — Tree Overview
A scannable map of the event-based plan. Full detail lives in supabase_roadmap.md; this is the one-screen version.
SUPABASE / DB RESTRUCTURE — organized by EVENT, not by date
VERDICT: keep Supabase as bare managed Postgres · cheapest · SOC2/ISO · pg_cron stays ⇒ no Go worker
│
├── GATE 0 · PREREQUISITE ───────────────────────────────────────────────
│ └── snapshot prod ground truth ...... triggers / funcs / policies / cron / indexes
│ the schema dump is untrustworthy — do this BEFORE any drop migration
│
├── NOW · the current restructure ──────────────────────────────────────
│ │
│ ├── N1 Purge billing/licensing surface ......... one migration + backend cleanup
│ │ drop org_licenses · user_licenses · subscription_link
│ │ drop check-expired-licenses cron + /webhook/license-expired + Vault/pg_net
│ │ drop LicenseChecker from AuthMiddleware
│ │ ⚠ fixes "orgs brick after 3 months" · interim = UNGATED until Stripe
│ │ ↳ NEXT STEP = OpenAPI contracts: the endpoints/fields removed here
│ │ must be reflected in the contracts (the keystone, pulled forward)
│ │
│ ├── N2 Fix room → recordings CASCADE ........... prod bug
│ │ recordings belong to ORG, not the room · room_id nullable, ON DELETE SET NULL
│ │
│ └── N3 Stripe middleware — EMPTY, contracts only
│ chain: auth → stripe(c.Next()) → rbac
│
├── EVENT-GATED ───────────────────────────────────────────────────────
│ │
│ ├── ◇ when REPO / DB-access layer is reworked (wks 3–5 BE tidy) ··· Event R
│ │ ├─ R1 re-point data-access layer at the post-purge schema
│ │ ├─ R2 delete dead repo methods (less surface to port)
│ │ ├─ R3 centralize tenant scoping (org_id/user_id) — sole guard post-RLS
│ │ └─ R4 scaffold organizations.stripe_customer_id
│ │
│ ├── ◇ when CENTRIFUGO lands (realtime cutover) ·················· Event C
│ │ │ (also gated on the frontend PostgREST → Go cutover)
│ │ │
│ │ ├─ C1 Disable RLS + Supabase realtime machinery
│ │ │ DISABLE RLS · drop 10 policies → JWT helpers → 9 RPCs · REVOKE authenticated
│ │ │ triggers: DROP enforce_defaults · rewrite/drop rate-limits
│ │ │ DROP notify_new_message · KEEP reply_count/touch (or fold into CTE)
│ │ │
│ │ ├─ C2 Remove user_presence · review room_presence
│ │ │ user_presence → gone · DROP clear-stale-presence cron · fix 2 org-fence readers
│ │ │ room_presence → KEEP (LiveKit-authoritative) · 🐞 fix ErrNotFound bug (needn't wait)
│ │ │ ⚠ stale-cleanup gap: cron dies & no Go worker — see open decisions
│ │ │
│ │ └─ C3 Centrifugo cutover: drop realtime.messages · publication entries
│ │ delete features/realtime · /realtime/token · SUPABASE_JWT_SECRET
│ │ backend publish post-commit · settle presence model (per-org vs global)
│ │
│ └── ◇ when STRIPE is adapted ···································· Event S
│ ├─ S1 RequireActiveSubscription mw (auth → sub → rbac · billingGroup opt-out)
│ ├─ S2 POST /webhooks/stripe (verify → dedup → 2xx → async · in Go)
│ ├─ S3 tables: org_subscriptions · stripe_event_inbox · user_orgs status+timestamps
│ ├─ S4 SyncStripeDataForOrg (single writer · re-fetch · never payload-apply)
│ ├─ S5 seat qty sync (single write path · proration · reconciliation)
│ ├─ S6 membership lifecycle (WorkOS activate/deactivate · seats = allocated)
│ │
│ ├─ ⚠ BLOCKERS FIRST: single membership write path · eventsync cursor fix ·
│ │ seat-limit race · membership history (status col)
│ └─ full design → "How to adapt Stripe" section in the main roadmap
│
├── LOOSE HORIZON · ≤ ~6 months · not urgent · not event-bound ─────────
│ ├── L1 confirm max pool / connection values (tier · IPv6 egress · pods×pool)
│ ├── L2 connection-config hygiene (simple_protocol · ConnMaxLifetime · timeouts)
│ ├── L3 index / constraint hygiene (users.email uniq · chat pagination · tenant FKs)
│ ├── L4 refresh-token rotation ⚠ LIKELY ACTIVE BUG — verify WorkOS single-use
│ ├── L5 migration baseline squash → vanilla Postgres · least-privilege role
│ └── L6 hygiene sweeps (eventsync retry · retention · concurrency fixes)
│
├── KEEP ON pg_cron (this is why no Go worker is needed) ───────────────
│ ├── reconcile-stale-recordings ...... else recordings stick in 'finalizing'
│ └── purge-expired-recordings ........ daily retention delete
│ DROPPED (not ported): check-expired-licenses (N1) · clear-stale-presence (Event C)
│
└── OPEN DECISIONS · settle before the relevant event ─────────────────
├── presence per-org vs global ................ before Event C
├── room_presence stale-cleanup: how? ......... before Event C (cron dropped, no Go worker)
├── user-deletion FK policy ................... N2 / before Stripe
├── past_due / paused grace behaviour ......... Event S
├── leave proration: credit vs period-end ..... Event S
└── org lapse: gate-only vs WorkOS deactivate . Event S
LEGEND ◇ event-gated (no date) ⚠ risk / caution 🐞 existing prod bug
SEQUENCE NOW (Gate 0 → N1 → N2 → N3) then R / C / S as each lands · L1–L6 anytime before pilot