Evaluate Supabase CLI for local API database development

[teezeit]

Context

The API currently has two local database paths:

• apps/api/docker-compose.yml starts a raw postgres:16 container on localhost:5432 for persistent local work.
• apps/api/src/local.ts falls back to in-memory PGlite when DATABASE_URL is unset, which is convenient but loses data on restart and is not reachable from SQL clients.

Production docs already point DATABASE_URL at Supabase Supavisor, so local development is the outlier.

Supabase appears to provide a first-party local development stack via the Supabase CLI:

• supabase init creates a committed supabase/ project folder.
• supabase start runs local Supabase services with Docker, including Postgres, Studio, Auth, Storage, and local service credentials.
• The local DB URL is exposed as postgresql://postgres:postgres@localhost:54322/postgres.
• Migrations and seed data can be managed with supabase db reset, supabase migration up, and supabase/seed.sql.

Docs checked:

• https://supabase.com/docs/guides/local-development
• https://supabase.com/docs/guides/local-development/cli/getting-started
• https://supabase.com/docs/guides/cli/local-development
• https://supabase.com/docs/guides/local-development/seeding-your-database

Proposal

Evaluate switching local API database development from the custom raw Postgres compose setup, and possibly from the default in-memory PGlite fallback, to Supabase CLI local development.

A likely target setup:

• Add/commit supabase/config.toml and any needed local seed files.
• Keep Drizzle migrations as the app schema source, or document how Drizzle migrations map into the Supabase local DB workflow.
• Update apps/api/package.json scripts to include Supabase-backed commands, for example db:up, db:down, db:reset, and db:studio.
• Update apps/api/README.md so the recommended local path uses supabase start and DATABASE_URL=postgresql://postgres:postgres@localhost:54322/postgres.
• Decide whether PGlite should remain as a zero-setup smoke-test fallback or move to test-only usage.

Acceptance criteria

• A fresh checkout can start the API against Supabase local Postgres with documented commands.
• Local data persists across API restarts and can be inspected in Supabase Studio or a SQL client.
• Drizzle migrations apply cleanly to the Supabase local database.
• Existing API tests still pass.
• The docs clearly explain when to use Supabase local dev versus the PGlite fallback, if the fallback remains.

Notes / tradeoffs

Supabase local dev requires a Docker-compatible runtime, so PGlite may still be useful for quick smoke tests or environments without Docker. The upside of Supabase local is better parity with production Supabase, persistent local state, a built-in Studio UI, and a path to local Auth/Storage if the API grows into those services.