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-js client 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.
  • Migration source: supabase/migrations/0001_init.sql
  • Schema overview: docs/schema.md
  • Related calls: ADR-003, ADR-005