Database

Database

Schema files, migrations, and type generation. Architecture and core rules are in the builder agent.

---

Schema File Organization

supabase/database/
├── cluster/
│   └── extensions/                     # one file per extension (cluster-level)
│       ├── pg_graphql.sql
│       ├── pg_net.sql
│       ├── pg_cron.sql
│       └── pgmq.sql
├── rbac/                               # RBAC reference DATA (rows, not schema)
│   ├── roles.sql                       # synced by the RBAC reconcile, NOT by db apply
│   ├── permissions.sql
│   └── role_permissions.sql
├── config/                             # role-level settings pg-delta can't model
│   └── db_pre_request.sql              # ALTER ROLE authenticator SET pgrst.db_pre_request
└── schemas/
    ├── api/
    │   ├── schema.sql                  # CREATE SCHEMA api + grants / default privileges
    │   ├── tables/
    │   │   └── agentlink_tasks.sql     # PGMQ queue
    │   ├── functions/
    │   │   ├── tenant_create.sql       # one RPC per file
    │   │   ├── profile_get.sql
    │   │   └── chart_create.sql        # custom api.chart_create
    │   └── cron/
    │       └── process-stale-tasks.sql # cron jobs
    └── public/
        ├── schema.sql                  # public schema-level grants (e.g. supabase_auth_admin USAGE)
        ├── tables/
        │   ├── profiles.sql            # one table + its grants/RLS/indexes/triggers
        │   ├── tenants.sql
        │   └── charts.sql              # custom entity table (example)
        └── functions/
            ├── _auth_tenant_id.sql     # one function per file
            ├── _internal_admin_handle_new_user.sql
            └── _hook_before_user_created.sql

Files are one object per file — each table (with its grants, RLS, indexes, triggers) and each function lives in its own file, grouped by Postgres schema (schemas/public/, schemas/api/) and kind (tables/, functions/). Statement ordering is handled automatically: db apply resolves dependency order at apply time, so file count and order are irrelevant. The top-level cron/, storage/, rbac/, and config/ folders are imperative — applied at deploy, not by db apply's schema diff (see below).

Conventions:

• schemas//tables/.sql — one table, named for the table (plural): charts.sql, contains the table + its indexes, triggers, grants, RLS policies
• schemas//functions/.sql — one function, named for the function: _auth_chart_owner.sql, _internal_admin_handle_new_user.sql, chart_create.sql
• schemas//schema.sql — the CREATE SCHEMA (for api) plus that schema's grants / default privileges; public/schema.sql holds only public's schema-level grants (public already exists, so it's not CREATEd)
• cluster/extensions/.sql — one file per extension (cluster-level, not schema-scoped)
• Even tables with FK dependencies get their own files — db apply orders the CREATE statements by dependency, so tenants.sql, memberships.sql, and invitations.sql can each be separate

Where to put new objects (create / edit guidelines)

When creating or editing schema objects, put each in its own file under supabase/database/. db apply resolves dependency order at apply time, so don't worry about file naming for ordering.

| Creating / editing | File | |---|---| | A table (+ its grants, ENABLE ROW LEVEL SECURITY, policies, indexes, triggers — all in this one file) | supabase/database/schemas//tables/.sql | | An RPC or any function (+ its REVOKE/GRANT EXECUTE) | supabase/database/schemas//functions/.sql | | A new extension | supabase/database/cluster/extensions/.sql | | A new schema, or schema-level grants / default privileges | supabase/database/schemas//schema.sql | | A cron job (cron.schedule(...)) | supabase/database/cron/.sql (imperative — see below). The job's body calls public._internal_admin_call_edge_function('internal-'); it never makes the outbound HTTP itself — pg_net only wakes the worker. See the [edge-functions](../edge-functions/SKILL.md) outbound-HTTP rule and [recipes.md](../../agents/references/recipes.md) for worked examples | | A storage bucket + its storage.objects policies | supabase/database/storage/.sql (imperative — see below) | | RBAC reference data — roles / permissions / role→permission bindings (rows) | supabase/database/rbac/.sql (imperative — see below) | | A role-level setting / ALTER ROLE … SET (e.g. pgrst.db_pre_request) | supabase/database/config/.sql (imperative — see below) | | Seed / default rows (any other INSERT/UPDATE/DELETE) | NOT a schema file — see the DDL-only rule below |

🛑 Declarative schema files are DDL ONLY — never put seed/data DML in them. Files under supabase/database/schemas/ define structure (CREATE/ALTER of tables, functions, policies, …). A standalone INSERT/UPDATE/DELETE/MERGE/TRUNCATE in a schema file is a mistake: db apply only applies structure (table/function/policy definitions), not row data, so the statement is silently dropped and the data never reaches the database (the CLI now hard-errors on it, naming the file + line). This is exactly why rbac/ exists — reference data is rows, not schema. Seed/default data belongs in one of:

• supabase/seed.sql — local dev seed, replayed by db rebuild / supabase db reset. Local only.
• A migration — reference data that must reach prod (author the INSERT directly in the migration; idempotent ON CONFLICT DO NOTHING).
• supabase/database/rbac/ — roles / permissions / role→permission bindings (the dedicated reference-data reconcile).
• (Inside a function body, INSERT/UPDATE is fine — that's part of the function's DDL, not a standalone seed.)

Imperative folders — cron/, storage/, rbac/, config/. These four top-level folders under supabase/database/ are excluded from db apply's schema diff and from the migration diff, and applied imperatively by the deploy step on every path — db apply (local/dev), db rebuild, and every env deploy (all envs incl. prod, which is migrations-only). Reason: the cron and storage schemas are excluded from db apply's schema diff and from migrations, so cron.schedule(), buckets, and storage policies never survive a migration; RBAC is reference DATA, not DDL; and role-level settings (ALTER ROLE … SET) aren't catalog objects pg-delta can diff or generate. This deploy step is the only path that reliably reaches prod — do not hand-append these to migration files. To apply just these folders without a full db apply (e.g. after editing a cron job or bucket), run pnpm exec agentlink db resources.

• cron/ and storage/ must be IDEMPOTENT (they re-run on every deploy): cron.schedule(name, …) upserts by job name (cron.unschedule(name) to remove); storage buckets use INSERT … ON CONFLICT (id) DO UPDATE; storage policies use DROP POLICY IF EXISTS + CREATE POLICY. Each folder's files run in sorted order, one transaction per folder.
• rbac/ is reference DATA, not schema. The roles/permissions/role_permissions tables live in schemas/public/tables/ (structure only). Their rows live in rbac/.sql, each filling an rbac_desired staging table, converged to exactly the declared set: full reconcile for permissions + bindings (a removed row is REVOKED everywhere — the only way revokes reach prod); roles are upsert-only (a referenced role can't be deleted: memberships.role FKs into roles(name)).
• config/ is role-level settings pg-delta can't model — idempotent, non-catalog SQL. It ships config/db_pre_request.sql (ALTER ROLE authenticator SET pgrst.db_pre_request = 'public._auth_pre_request'; NOTIFY pgrst, 'reload config';), which wires the per-request multitenancy resolver. 🛑 An ALTER ROLE … SET / db_pre_request line has no schema-file home: it's a role-level GUC, not a catalog object, so db apply and db migrate ignore it — dropped into a schemas/**.sql file it is silently never applied. On prod that means PostgREST never runs _auth_pre_request, so every request resolves no workspace → _auth_tenant_id() is NULL → RLS matches no rows and _auth_has_permission() is false → the whole app fails deny-by-default. Put such statements in config/ (idempotent: ALTER ROLE … SET is last-write-wins), where the deploy step applies them on every env incl. prod.

🛑 Editing cron/, storage/, rbac/, or config/? The workflow is: edit the file → APPLY it. A change to these folders does nothing until applied, and they are excluded from the schema diff — so db apply's schema step won't carry them, and a cron.schedule()/bucket/policy/RBAC row/ALTER ROLE dropped into a schemas/ file silently never runs. After editing:

• pnpm exec agentlink db apply applies them alongside your schema (the normal dev loop already covers them — db apply runs the imperative step too), or
• pnpm exec agentlink db resources applies only config/ + storage/ + cron/ + rbac/ (no schema diff, no type-gen) — reach for this when that's all you changed (db rbac-sync is the rbac-only subset).

On deploy they go out with every env deploy. Concretely:

| You're changing… | Edit | Then | |---|---|---| | A cron job | cron/.sql (cron.schedule(...)) | db apply or db resources | | A storage bucket or its storage.objects policies | storage/.sql | db apply or db resources | | An RBAC permission key, or a role→permission binding | rbac/permissions.sql, rbac/role_permissions.sql | db apply or db resources | | A role-level setting (ALTER ROLE … SET, e.g. pgrst.db_pre_request) | config/.sql | db apply or db resources |

🛑 Removing an already-deployed cron/ or storage/ resource — deprecate the file, don't just delete it. Deleting a cron/ or storage/ .sql file does nothing to a database that already has the resource: the imperative step only applies the files present — unlike rbac/, it never reconciles deletions. The job keeps firing / the bucket keeps existing on local, dev, and prod. Use a tombstone so the removal travels through the normal deploy path:

1. Rename .sql → deprecated-.sql (one resource per file, so the file is the resource).
2. Comment out the original cron.schedule(...) / bucket INSERT + policies so the imperative step stops re-creating it.
3. Add a header line: why it's deprecated, and "safe to delete this file after the next release, once every env has deployed past it."
4. Then, by resource type:

• Cron — append an idempotent unschedule. It re-runs on every deploy, so it must no-op when the job is already gone. The bare cron.unschedule('') THROWS when absent → rolls back the whole cron folder on the next run; use the jobid form instead:

``sql SELECT cron.unschedule(jobid) FROM cron.job WHERE jobname = ''; ` This removes the job from every env on the next db apply / env deploy`, then cleanly no-ops.

• Storage — add NO removal SQL. Deleting a bucket in SQL (DELETE FROM storage.buckets) orphans its objects, which keep counting against the user's Supabase Storage usage (a common, hard-to-diagnose billing/support issue). Instead, tell the user to delete the bucket from the Supabase dashboard (Storage → Buckets) so its objects cascade properly. The leftover storage.objects policy is inert once the bucket is gone (it filters on a bucket_id that matches no rows) — leave it, or have the user remove it in the dashboard too.

1. Apply the tombstone like any imperative change (db apply or db resources; it also ships with every env deploy). Once every long-lived env has deployed it, the deprecated-* file is safe to delete.

Two different "permissions" — don't confuse them:

• A GRANT on a table/function (who may SELECT / EXECUTE it) is DDL → it lives in that object's own schemas//{tables,functions}/.sql file and applies with db apply. Changing who can run an RPC at the SQL level = edit its function file + db apply.
• The RBAC permission model (the auth_verify_access('entity.action') keys your RPCs check, and their role bindings) is reference data → it lives in rbac/permissions.sql + rbac/role_permissions.sql and applies with db resources (or db apply / env deploy). Adding a new gated capability = add the permission key + binding here, and call auth_verify_access(...) in the RPC (which is a schema-file change).

• One object per file. A table file is self-contained: table definition + constraints + indexes + ENABLE ROW LEVEL SECURITY + policies + triggers + grants all live together.
• To edit an existing object, edit its file in place (don't create a parallel file) — then run pnpm exec agentlink db apply.

Migrating an existing supabase/schemas/ project to supabase/database/

Older projects keep declarative SQL under supabase/schemas/. The home moved to supabase/database/ (matching Supabase's db … generate default). On --force-update, the CLI recreates the scaffolded objects under supabase/database/ and leaves your old supabase/schemas/ exactly where it is — untouched, but no longer applied (db apply reads supabase/database/ only). It then asks the user to have you finish the move. When asked, do this:

1. Move each CUSTOM object — anything the app added, i.e. NOT the scaffolded set (tenants/memberships/invitations/profiles/roles/permissions/role_permissions, the api.* RPCs, _auth_* / _internal_* / _hook_* functions, agentlink_tasks, process-stale-tasks), which already exists under database/. Place each in one object per file:

• table → supabase/database/schemas//tables/.sql (table + its grants, ENABLE ROW LEVEL SECURITY, policies, indexes, triggers — all together)
• function / RPC → supabase/database/schemas//functions/.sql
• extension → supabase/database/cluster/extensions/.sql
• CREATE SCHEMA / schema-level grants → supabase/database/schemas//schema.sql
• cron job → supabase/database/cron/.sql (imperative)
• storage bucket + policies → supabase/database/storage/.sql (imperative)

Split any consolidated/multi-object files into one-object-per-file as you go. db apply resolves dependency order, so file naming/order doesn't matter.

1. Apply: pnpm exec agentlink db apply — confirm the database/ tree applies cleanly.
2. Delete supabase/schemas/ once everything is migrated and applying — nothing else references it.

Never relocate supabase/schemas/ into .agentlink/.incoming/ — that directory is gitignored and cleared on the next update.

Schema File Style Rules

• No DROP statements in schema files — clean declarations only
• Use: CREATE TABLE IF NOT EXISTS, CREATE OR REPLACE FUNCTION, CREATE INDEX IF NOT EXISTS, plain CREATE POLICY, plain CREATE TRIGGER
• Exception: use DROP POLICY IF EXISTS + CREATE POLICY for idempotent policies (policies don't support CREATE OR REPLACE)
• Use record type in DECLARE blocks (not public.tablename%rowtype) — avoids db apply dependency-ordering issues
• DROP statements belong in migrations only (for renaming/cleanup)
• Reason: schema files represent the desired state db apply converges the database to; unnecessary drops create phantom diffs

Customizing CLI-shipped files

There are no inline annotations — never add -- @agentlink comments. Plain SQL comments are always fine:

-- Creates a new chart for the authenticated user
CREATE OR REPLACE FUNCTION api.chart_create(...)

To customize a CLI-shipped file, just edit its per-object file in place (e.g., supabase/database/schemas/public/functions/_internal_admin_handle_new_user.sql), keep the same function name and schema, and run `pnpm exec agentlink db

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: tomaspozo
• Source: tomaspozo/agentlink
• License: MIT
• Homepage: https://agentlink.sh

Install and usage instructions live in the source repository linked above.