supabase-setup

Supabase setup and schema changes

Two failures dominate Supabase work: a table shipped with row-level security off (every row readable by anyone holding the anon key), and a schema change made in the dashboard so it exists in production and nowhere in the repo. The order below prevents both.

When to activate

• Adding or changing tables, columns, constraints, indexes, policies.
• Wiring an app to Supabase for the first time.
• "the query returns nothing" / "permission denied for table" symptoms.

Order of operations

1. Credentials first. secrets_status → store what is missing with secrets_set → env_sync. Never paste a key into the chat (see secrets-hygiene).
   • SUPABASE_URL, SUPABASE_ANON_KEY — client-side, public.
   • SUPABASE_SERVICE_ROLE_KEY — server-only, bypasses RLS.
   • SUPABASE_PROJECT_REF / SUPABASE_ACCESS_TOKEN / SUPABASE_DB_URL — CLI + migrations.
2. preflight target=db. Clear every blocker before touching the database.
3. Schema as migration files, never dashboard edits. supabase migration new <name>, or supabase db diff --file <name> to capture drift that already happened. A dashboard-only change cannot be reviewed, replayed on a teammate's machine, or rolled back.
4. Table and its policies in the SAME migration. A table without a policy is either fully closed or fully open — never leave that to a follow-up.
5. Dry-run, then apply. supabase db push --dry-run and read the SQL. Apply to a hosted database only with explicit user approval and a rollback note.
6. Regenerate types. supabase gen types typescript --linked so the backend/frontend contract stays type-checked.
7. Seed realistic data. Idempotent seed script (upsert), a few rows per table, so tests and manual checks have something to read.

Schema rules

• Primary key on every table (id uuid primary key default gen_random_uuid() is a fine default). created_at / updated_at as timestamptz.
• Foreign keys with an explicit on delete choice — cascade / restrict / set null is a product decision, not a default.
• Index every column you filter, join or sort on. The referencing side of a foreign key is NOT indexed automatically.
• numeric for money, never float. check constraints or a real enum type for fixed sets. Prefer not null with a default over a nullable column every reader must handle.