ADR-001: Database — Supabase Postgres
Status
Accepted
Context
We needed a data home that felt like Postgres (because our team speaks SQL fluently) but came with batteries included: ACID guarantees, row-level security, backups, and a frictionless way to sync schema changes from local dev to production. Supabase Postgres checked those boxes and bundled Auth plus Edge Functions, which lined up with the ingestion story we wanted.
Options considered
- Supabase Postgres — Managed Postgres with RLS, migrations, dashboards, and the
@supabase/supabase-jsclient we already use on the frontend. - PlanetScale / MySQL — Tempting branching workflow, but weaker story for RLS and JSON workflows.
- SQLite/libSQL — Great for single-user apps, but we needed multi-tenant isolation and cron-friendly extensions.
Decision
We anchored on Supabase Postgres. All user-scoped data lives in public.* tables guarded by RLS policies like:
create policy "Users can view own sources" on public.sources
for select using (auth.uid() = user_id);
Ingestion scripts authenticate with the service role key, so they bypass RLS while still logging everything in jobs.
Consequences
Upside
- RLS enforces tenant isolation without sprinkling
user_id =checks across queries. - The Supabase dashboard, advisors, and CLI keep migrations honest (
supabase/migrations/0001_init.sql). - Edge Functions sit next to the database, so ingestion doesn’t pay an extra network hop.
Trade-offs
- We’re tied to Supabase’s feature cadence (e.g., pg_cron isn’t universally enabled yet).
- Moving to raw Postgres later would require rewriting auth + storage glue.