Decision — Supabase as the app DB + auth layer

Parent: Architecture · Status: adopted — built. Facts verified against current Supabase docs/pricing on 2026-07-04; the integration shipped across felix-app phases 0–8 as designed here. The doc deltas this decision originally queued have all been applied upstream (see the closed Pending doc updates).

1. Question and recommendation

The architecture currently assumes self-hosted Postgres+pgvector (docker-compose in dev, a managed or self-run instance in prod) and Auth.js in the Next.js app. The user wants a third-party auth provider. Question: can Supabase be the entire app-DB + auth layer?

Recommendation: yes — adopt Supabase for both. One vendor replaces two self-managed concerns (Postgres operations, auth) with managed Postgres that keeps our exact schema and a hosted auth server (GoTrue) that is itself open source. Everything in the current design that touches Postgres — pgvector embeddings, RLS, user_id-on-every-row, the brain's direct SQL access — carries over unchanged. The Parquet lake stays on R2 (explicitly not Supabase Storage — §4). Start on the Free plan while building; move to Pro ($25/mo) when this becomes the daily driver.

2. Verified capabilities (July 2026)

3. Integration design

The tenancy rule stays exactly as written in ARCHITECTURE.md; Supabase just gives each plane a first-class implementation.

3.1 Web app — Drizzle CRUD, RLS from day 1

The Next.js app uses @supabase/ssr with the anon/publishable key for auth and session management — sign-in flows, token refresh — and keeps it available for client-side features later (e.g. realtime); it is not the CRUD path. Interactive reads/writes (positions, actions inbox, chat threads, settings) stay on the web server's Drizzle connection, per doc 03's API boundary. To make RLS bind there, that connection uses a dedicated non-privileged Postgres role (not the service role, not the postgres superuser), and each request's queries run inside a transaction that sets the authenticated role and the user's JWT claims (request.jwt.claims) — the standard Drizzle-on-Supabase pattern, so the auth.uid() policies apply to the server path exactly as they would to a supabase-js client. This pairs naturally with the existing user_id on every row design: every app-facing table gets an RLS policy tying rows to the authenticated user's ID. One deliberate change: the current docs say "RLS when multi-user" — with the anon key living in the browser, RLS is enabled from day 1. This is a hardening win, not extra scope: the policies are one-liners precisely because every table already carries user_id.

3.2 Python brain — service-role connection, RLS bypassed

The brain (FastAPI, analytics, agents) connects directly to Postgres via Supavisor as a privileged role and never goes through the PostgREST API. RLS does not constrain it — tenancy is enforced in code by the existing user-context pattern (the lake module and every per-user function take an explicit user context and refuse cross-user access). Requests arriving from the web app carry the user's Supabase access token; the brain verifies it locally against the project JWKS (ES256) and maps the sub claim to user_id. LiteLLM's own virtual-key/budget tables can live in the same Supabase Postgres under a separate schema — one database to operate instead of two.

3.3 Pipelines — pooler connections

Cron jobs (prices, news, snapshots) connect via Supavisor: session mode (5432) for the long-running nightly chain, transaction mode (6543) acceptable for short bursty jobs (with asyncpg's prepared-statement cache disabled). At Micro compute (60 direct / 200 pooler client connections) the nightly chain plus web traffic uses a small fraction of the budget; Small (~$5/mo net after credit) buys 90/400 if we ever crowd it.

3.4 BYOK keys — Supabase Vault

Store user provider keys in Vault rather than hand-rolling app-layer crypto: encryption key lives outside the DB, backups stay encrypted, nothing to implement. Access to the decrypted view is granted only to the brain's role; the anon/authenticated roles can never read it, and keys are never returned through any API. Fallback if Vault's alpha status becomes a concern: envelope encryption in the brain with the key in VPS env — the schema (a byok_keys table with ciphertext) is identical either way, so switching later is contained.

3.5 Local dev and migrations

supabase start replaces the Postgres+pgvector service in docker-compose; MinIO and LiteLLM stay in compose (the lake and gateway are not Supabase concerns). The supabase CLI's job is the local development stack — Postgres, the auth emulator, Studio, local SMTP — not migrations: the Drizzle TS schema in platform/packages/db owns the DDL (doc 03), and drizzle-kit generate emits the plain-SQL migration files applied against the Supabase database (the CLI's local Postgres in dev, the project database in prod), so there is one schema authority rather than two competing migration tools. Row types for the web app come from the Drizzle schema itself. Pydantic models in platform/packages/schemas remain the source of truth for API DTOs; the DB-side types complement rather than replace them.

4. The lake stays on R2 — explicitly

Supabase Storage's S3 API would technically serve DuckDB, but the lake should stay on R2/S3 (MinIO in dev) as designed:

• Egress economics. R2 egress is free; Supabase egress is metered (250 GB/mo on Pro, then $0.09/GB). Analytics re-scans of Parquet are exactly the workload you don't want on a metered pipe.
• Blast radius. The lake is the replay/audit layer; keeping it on a separate, dumb object store means an app-DB incident can never touch raw history.
• Maturity. R2's S3 surface is battle-tested for data tooling; Supabase's is newer and lacks versioning.
• Zero migration. The lake module seam means nothing downstream would notice either way — so choose on economics.

User CSV uploads (users/{id}/raw/imports/…) also stay on R2 under the existing prefix rules — one object store, one credential set, one lake module. Supabase Storage ends up unused in this architecture; that's fine.

5. Caveats and risks

• Free-tier pausing. Projects pause after 1 week of inactivity. Daily cron traffic prevents this in practice, but a pause (e.g. during a vacation gap in a dev project) silently breaks the nightly chain. Acceptable while building; move to Pro before relying on the daily briefing. The 500 MB free DB will also be outgrown by news_stories + embeddings within months.
• Connection limits. Hard-coded per compute tier (Micro: 60 direct / 200 pooler). Fine for us, but all three consumers (web serverless functions, brain, pipelines) must use the pooler endpoints, not direct connections, to stay comfortably inside it.
• Transaction-mode sharp edge. Port 6543 doesn't support prepared statements; async Python drivers need their statement cache disabled or jobs pinned to session mode. Cheap to handle, annoying to debug if forgotten.
• Passkeys are beta. Experimental API, may change. Launch with password + social + TOTP; add passkeys when GA.
• Vault is alpha-stage. Mechanism is sound and the fallback (§3.4) is drop-in, but track its maturity before multi-user BYOK launch.
• Lock-in assessment. Postgres is portable: standard Postgres + standard pgvector, pg_dump/logical replication out at any time; our schema doesn't change shape. Auth is the sticky part — but less sticky than proprietary auth SaaS: the auth.users table (including password hashes) lives in our database, and GoTrue is open source and self-hostable, so a credible exit path exists. What genuinely couples us: OAuth app registrations, RLS policies referencing auth.uid(), @supabase/ssr session plumbing, and the brain's JWT verification. Mitigations, all cheap now: keep a profiles table keyed by the auth UUID and point all domain foreign keys at it (never at auth.users directly); confine auth.uid() to policy definitions; keep JWT verification in one brain module.
• Egress meter. App-DB JSON traffic is small, but remember Supabase egress is metered while R2's is not — another reason no bulk data lives in Supabase.

Alternatives considered. Neon + Clerk/Auth0: good serverless Postgres, but auth becomes a second vendor with per-MAU pricing and no in-database RLS integration — two integrations instead of one. Keep self-hosted Postgres, swap only Auth.js → managed auth: keeps the DB ops burden that motivated this question. Self-hosted Supabase: viable (it's all OSS) but reintroduces the ops burden; better kept as the exit hatch than the starting point.

6. Cost

$0 during build (Free plan). $25/mo on Pro once it's the daily driver; the $10 compute credit covers the Micro instance, so $25 is the realistic steady-state number until compute needs to grow (Small: +$5/mo net; Medium: +$50/mo net). No paid add-ons needed (skip phone MFA, PITR, custom API domain). Feeds into docs/cost-model.md.

Pending doc updates

Closed — all applied. The eleven deltas originally queued here (Supabase Auth in the overview's Stack section, RLS-from-day-1 wording, Vault for BYOK, the roadmap's phase-0/phase-8 rewording, the Supabase-Storage-rejected notes and Tiingo price correction in doc 01, and the JWKS/Supavisor/litellm-schema integration notes in docs 02–05) have been folded into the architecture overview and component docs 01–05, which now carry them directly. The build (felix-app phases 0–8) implemented the design as decided; two as-built confirmations worth recording here: Vault custody shipped and was proven live (a pasted secret exists nowhere but Vault — api_keys keeps last4 only), and the LiteLLM proxy's tables do live in a litellm schema in the same Supabase Postgres. Prod prerequisite carried forward: the brain verifies ES256/RS256 only — a legacy HS256-signed Supabase project would fail every brain request (runbook preflight in felix-app).