Database

Database — operating contracts

Three contracts this skill protects:

1. Applied migrations are immutable.
2. The RLS spec is the source of truth — implementations follow tests, not the reverse.
3. schemas/*.sql is the human-friendly description of the final shape.

supabase/AGENTS.md is the harder rule layer (RLS, grants, security boundaries). For function changes, follow its canonical RPC role contract, including effective privileges, caller tests, and coverage of new overloads. This skill covers the recurring workflow tasks. Read both.

For realistic optional Library data without changing schema history or the canonical base seed, use the opt-library skill. Return here when that work changes migrations, schemas, RLS, or grants.

Common mistake (read this first)

When asked to "merge migrations" or "consolidate", the temptation is to collapse every supabase/migrations/* into a single canonical file. This is wrong if any of those files have already been applied to a deployed environment. Rewriting an applied migration:

• Diverges file content from what supabase_migrations.schema_migrations records as already-run.
• Makes future db reset produce a different starting state for new contributors than what the existing environment holds.
• Can silently drop columns, policies, or grants the live system needs.

Before merging anything, classify each migration:

None of these signals replace user confirmation. The only ground truth is the deployed schema_migrations table on staging/prod, which the agent cannot read. git log and git status indicate likelihood, not certainty. Default to asking.

Old timestamps don't mean "applied" — they can be brand-new files added to fix an ordering bug.

/database compact local migration

Merge multiple local-only migration files into one (or a few) before the PR ships. Local development naturally accumulates many small migrations for fast iteration without db reset; production prefers one coherent migration per feature.

When to invoke

• Before opening a PR that adds 2+ migration files for the same feature.
• User says "merge migrations", "consolidate migrations", "clean up the migration directory".
• Reviewing a feature branch where migrations outnumber logical chunks.

Procedure

1. Classify every migration in the working tree. Output: two lists, applied (leave alone) and local-only (candidates).
2. Verify the classification with the user when uncertain. One confirmation message is cheaper than touching a shipped file.
3. Plan the merge. For each local-only migration, record:
   • what schema/table it touches,
   • dependencies on earlier local-only migrations,
   • whether a later local-only migration supersedes it (ADD COLUMN then DROP COLUMN — both vanish in the merged file).
4. Pick the consolidated filename. Use the timestamp of the latest local-only file in the chain so ordering relative to applied migrations stays intact. Rename if the merged content makes a different name more honest.
5. Write the consolidated SQL as if it were the only file in the chain — no ADD … then DROP churn, no superseded function defs. Idempotent forms (CREATE TABLE IF NOT EXISTS, CREATE OR REPLACE FUNCTION, ADD COLUMN IF NOT EXISTS) make local re-runs safe.
6. Delete the superseded local-only files. Only those — never touch the applied list.
7. supabase db reset locally. Run supabase test db if pgTAP covers the affected tables.
8. Note the fold in the PR description. Reviewers shouldn't have to diff timestamps to figure it out.

Worked example

Local-only (mergeable):

20260508120000_grida_billing_account_provisioning_uid.sql
20260508130000_grida_billing_metronome.sql
20260509120000_grida_billing_debit_cache.sql
20260509130000_grida_billing_alerts_multi_tier.sql

Applied (untouchable):

20260506132900_grida_billing.sql
20260507000000_grida_billing_backfill_provision.sql
20260507223000_grida_billing_security_invoker.sql

Right move: write one consolidated 20260508130000_grida_billing_metronome.sql (latest timestamp; v2 projector from alerts_multi_tier replaces v1 from metronome.sql), delete the other three local files, leave the applied trio untouched. Wrong move: cat all seven into one.

/database rls scenarios

Write or review RLS test scenarios for a tenant-scoped surface. Output is pgTAP coverage proving who can read/write what across personas. Not a description of the current implementation.

When to invoke

• New tenant-scoped table, view, or RPC.
• Existing RLS policies changing.
• Reviewing a security-sensitive PR ("what could go wrong here?").
• User runs /database rls scenarios <surface>.

The non-negotiable inversion

Implementation mirrors the test, not the other way around.

In RLS, the user journey is the spec. If a test says "a member of org A cannot read org B's project rows", that is a fact about how the product must behave. The implementation's job is to satisfy that fact. If the implementation currently leaks org B's rows, that's a security bug — fix the implementation, do not weaken the test.

Resist any pressure (including from yourself, mid-implementation):

• Soften an assertion because the policy doesn't quite cover it yet.
• Add SET LOCAL ROLE service_role to make a test pass.
• Drop a "no-membership cannot read" case because seeding is inconvenient.
• Replace is(count, 0) with ok(count >= 0).

A test failing because the policy is wrong is the test doing its job. A test failing because the test is wrong (mis-seeded fixture, typo'd UUID) gets fixed mechanically — never relax the assertion.