Skip to content
Spotcraft

Database

Drizzle owns the schema (src/db/schema.ts) and generates SQL; the Supabase CLI applies it. There is one migration history, in supabase/migrations/ (Supabase filename format).

Changing the schema

Section titled “Changing the schema”
Terminal window
# 1. edit src/db/schema.ts
pnpm db:generate --name=add_something   # writes supabase/migrations/<ts>_add_something.sql
# 2. review the generated SQL
pnpm db:migrate                         # apply locally (or pnpm db:reset to start fresh)
  • Never hand-edit generated migrations. Don’t use drizzle-kit push / drizzle-kit migrate or schema edits in Studio — they bypass Drizzle’s journal.
  • For raw SQL (functions, triggers, grants): pnpm db:generate --custom --name=<name>, then fill in the empty file.
  • pnpm db:check checks the migration journal; pnpm db:types regenerates the Supabase types.
  • Applying migrations to a hosted project: see Self-hosting.

Conventions: bigint identity primary keys, timestamptz, indexed foreign keys, money as integer cents, enums via pgEnum, casing: "snake_case" (camelCase in TypeScript, snake_case in SQL).

Seeds

Section titled “Seeds”
File Contains Runs
supabase/seed.sql Shared seed data (empty for now; never accounts) db:start / db:reset
supabase/dev/seed-users.sql One user per role — see Getting started pnpm db:seed:dev (local only)

supabase/dev/seed-users.sql is deliberately not in [db.seed] sql_paths: files there can be pushed to production with supabase db push --include-seed.

Access

Section titled “Access”
  • Drizzle (src/db/client.server.ts) connects as postgres and bypasses row-level security. Use it only from *.server.ts code called by server functions, and authorize there.
  • Supabase clients (src/lib/supabase/):
    • getSupabaseServerClient() — per request, acts as the signed-in user (cookies).
    • getSupabaseAdminClient() — secret key, bypasses RLS, server only (user management).
    • getSupabaseBrowserClient() — browser singleton.
  • App tables have RLS enabled with no policies and no anon / authenticated grants, so the Data API can’t read them. Add policies and grants deliberately if a table should be exposed.
  • The pool (pg, max 5, 5 s idle timeout) is registered with attachDatabasePool from @vercel/functions, which closes idle connections before a Vercel instance suspends.