# Database

> Schema files, migrations, and type generation for Supabase Postgres. Use when the task involves creating or modifying tables, columns, indexes, triggers, RLS policies, grants, or database functions, or the imperative cron/storage/rbac/config folders (cron jobs, storage buckets, RBAC reference data, role-level settings like db_pre_request). Activate whenever the task touches supabase/database/, su…

- **Type:** Skill
- **Install:** `agentstack add skill-tomaspozo-agentlink-database`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [tomaspozo](https://agentstack.voostack.com/s/tomaspozo)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [tomaspozo](https://github.com/tomaspozo)
- **Source:** https://github.com/tomaspozo/agentlink/tree/main/skills/database
- **Website:** https://agentlink.sh

## Install

```sh
agentstack add skill-tomaspozo-agentlink-database
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# 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 `CREATE`d)
- `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.
5. 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.
2. **Apply:** `pnpm exec agentlink db apply` — confirm the `database/` tree applies cleanly.
3. **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:

```sql
-- 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](https://github.com/tomaspozo)
- **Source:** [tomaspozo/agentlink](https://github.com/tomaspozo/agentlink)
- **License:** MIT
- **Homepage:** https://agentlink.sh

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

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** yes
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-tomaspozo-agentlink-database
- Seller: https://agentstack.voostack.com/s/tomaspozo
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.