FanRacingVerse — Motorsport Social Platform
FanRacingVerse is a production-deployed, full-stack social platform and encyclopedia for motorsport fans — taken from an empty database to a live URL on a custom domain. It combines per-series community forums, a navigable encyclopedia built from real Formula 1 data (f1db), a competitive grid-prediction game, Instagram-style social profiles, and real-time chat. Its defining trait is a security-first architecture where the database — not the client — is the source of truth and the enforcer of every rule: Row Level Security on 100% of tables, SECURITY DEFINER RPCs for every sensitive operation, and insert-only ledgers for the in-app economy. The hard problems live in the data model and the security boundary, not the UI.
Project metrics
4 key metricsRow Level Security enforced on all ~43 tables — authorization is never a client concern
Versioned migrations documenting every schema, policy, and trigger decision
Across encyclopedia, per-series forums, the prediction game, social profiles, and admin
Aggregated RPCs return everything a page needs in a single query — no N+1, controlled egress
Problem statement
A fan platform sounds simple until you enumerate what it actually has to do: per-series communities, an encyclopedia from real F1 data, a secret-until-locked prediction game, a social graph, real-time chat, and an admin panel — all of which must be safe even against direct API calls. That last point reframed the whole project: the hard part of a multi-tenant social app isn't the screens, it's authorization. So I designed the data layer first and made one rule non-negotiable — the client is never trusted.
Multiple racing series, each behaving like its own community with its own membership, identity, and access rules
An encyclopedia of drivers, teams, cars, circuits and races — thousands of cross-linked records imported from real data
A prediction game where picks must stay secret until a race locks, then be scored fairly and automatically with no double-scoring
A social graph (follow → approve), profiles, an activity footprint, and real-time chat and notifications
An admin and moderation panel over the entire catalog — reports, penalties, feedback, and a help center
A user must never read another user's private data, award themselves points, or post where they shouldn't — even by hitting the API directly
Constraints
- The client is never trusted — every rule is enforced by the database (RLS + RPCs), not the UI
- Runs on a free Supabase tier that pauses after ~7 days idle — egress and connection budget must be controlled by design
- Production from day one — real authentication, transactional email, a custom domain, and security headers
Approach & modules
I inverted the usual approach and put authorization in the database, not the UI. Row Level Security is enabled on 100% of tables; sensitive operations only mutate through SECURITY DEFINER RPCs with privileges revoked from public; ledgers are insert-only and recomputable. Pages render server-side from a single aggregated RPC, real-time is scoped per user and per topic, and whole modules ship behind build-time feature flags — letting me launch a focused MVP without deleting future work.
Security at the Database
RLS on 100% of tables decides what every role can read and write; the UI only hides what the database already denies. Sensitive operations mutate exclusively through SECURITY DEFINER RPCs with REVOKE … FROM public, and XP/coins live in insert-only, auditable ledgers maintained by internal functions
Postgres Does the Work
Generated columns power full-text search (tsvector) and a stored engagement score; triggers lazily create discussion topics, grant achievements, and activate entities on first comment. Idempotent f1db ingestion upserts by external_id — application logic that is cheaper, safer, and more consistent as database logic
Grid-Prediction Game
One prediction per user/race via a partial unique index; picks are invisible — even via the API — until a server-time lock, because the rule lives in the row policy. When results land, a statement-level trigger fires idempotent, set-based, podium-weighted scoring that no race condition can double-count
Social & Real-time
Instagram-style follow → approve, profiles, and an activity footprint, with Supabase Realtime scoped per user and per topic — never global — so the connection budget scales with active users. Per-account chat preferences are persisted server-side with optimistic, save-on-demand UX
System design
A security boundary sits between the application and the data: reads flow through React Query and are filtered by RLS policies; writes flow through Server Actions into SECURITY DEFINER RPCs that validate inside the database. PostgreSQL is the single source of truth and the enforcer — the anon/authenticated roles can never touch internal functions or another user's rows.
Process visualization
Step-by-step workflow showing how data and operations flow through the system.
User submits a top-10 grid guess; a partial unique index enforces exactly one prediction per user per race
Picks stay invisible to everyone — even via direct API calls — until a server-time lock, because an RLS policy hides them
Official race results land in the database, ingested idempotently by external_id
A statement-level trigger fires idempotent, set-based scoring with a podium-weighted formula — no race condition can double-count
XP and coins are appended to insert-only ledgers; balances are caches recomputed by internal functions, never written by a client
Scores and leaderboards are revealed; an aggregated RPC returns the whole page in a single round trip
Project journey
Key milestones and phases throughout the project lifecycle.
Schema & RLS Foundation
Designed ~43 tables and enabled Row Level Security on 100% of them. Role-based access via a role column with fn_is_admin() / fn_is_moderator() helpers to avoid policy recursion, plus soft deletes with physical DELETE blocked by RLS
RPCs & Insert-only Ledgers
Built SECURITY DEFINER RPCs for economy, predictions, and moderation with REVOKE … FROM public — least privilege by construction. XP/coins became append-only ledgers with balances cached by internal functions only
f1db Ingestion & Search
Idempotent import pipeline upserting by external_id, generated tsvector columns for full-text search, a stored engagement score, and fully cross-linked drivers, teams, cars, circuits and races
Predictions, Profiles & Real-time
Prediction integrity via a partial unique index and a statement-level scoring trigger; Instagram-style follow → approve; scoped Supabase Realtime channels per user/topic; build-time feature flags to gate whole modules
Deploy & Hardening
Shipped to Vercel on a custom subdomain with Supabase Auth/Realtime/Storage and Resend SMTP (verified domain, SPF/DKIM). Patched a flagged Next.js version, fixed password-reset 429 rate limits, and a mobile CTA overflow
Schema & RLS Foundation
Designed ~43 tables and enabled Row Level Security on 100% of them. Role-based access via a role column with fn_is_admin() / fn_is_moderator() helpers to avoid policy recursion, plus soft deletes with physical DELETE blocked by RLS
RPCs & Insert-only Ledgers
Built SECURITY DEFINER RPCs for economy, predictions, and moderation with REVOKE … FROM public — least privilege by construction. XP/coins became append-only ledgers with balances cached by internal functions only
f1db Ingestion & Search
Idempotent import pipeline upserting by external_id, generated tsvector columns for full-text search, a stored engagement score, and fully cross-linked drivers, teams, cars, circuits and races
Predictions, Profiles & Real-time
Prediction integrity via a partial unique index and a statement-level scoring trigger; Instagram-style follow → approve; scoped Supabase Realtime channels per user/topic; build-time feature flags to gate whole modules
Deploy & Hardening
Shipped to Vercel on a custom subdomain with Supabase Auth/Realtime/Storage and Resend SMTP (verified domain, SPF/DKIM). Patched a flagged Next.js version, fixed password-reset 429 rate limits, and a mobile CTA overflow
Dive into the documentation
Detailed implementation guides, architecture decisions and technical specifications for this project.