AI Prompt: Database: Declarative Database Schema

1
# Database: Declarative Database Schema
2
3
Mandatory Instructions for Supabase Declarative Schema Management
4
5
## 1. **Exclusive Use of Declarative Schema**
6
7
- **All database schema modifications must be defined within `.sql` files located in the `supabase/schemas/` directory**
8
- **Do not** create or modify files directly in the `supabase/migrations/` directory unless the change falls under the known caveats below. Generate migration files through the CLI instead
9
- **Do not** modify the database directly through Studio, the SQL editor, or `psql`. The schema files are the source of truth. Direct database changes are invisible to the diff and are silently dropped
10
11
## 2. **Prerequisites**
12
13
- The declarative workflow requires the `pg-delta` diff engine, which is the default. Ensure `supabase/config.toml` does not opt out of it. `supabase init` writes the default explicitly:
14
  ```toml
15
  [experimental.pgdelta]
16
  enabled = true
17
  ```
18
- Do not set `enabled = false`. That switches the project to the legacy `migra` engine and disables the `db schema declarative` commands. If a project has already opted out, remove the opt-out, or pass the `--experimental` flag for a single command run
19
20
## 3. **Schema Declaration**
21
22
- For each database entity (e.g., tables, views, functions), create or update a corresponding `.sql` file in the `supabase/schemas/` directory
23
- Ensure that each `.sql` file accurately represents the desired final state of the entity
24
25
## 4. **Migration Generation**
26
27
- After editing files in `supabase/schemas/`, generate and apply a migration in one step:
28
  ```bash
29
  supabase db schema declarative sync -f <migration_name>
30
  ```
31
  Replace `<migration_name>` with a descriptive name for the migration
32
- The command offers to apply the generated migration to the local database. Pass `--no-apply` to only generate the file, or `--apply` (or the global `--yes`) to apply it without prompting
33
- **Do not** run `supabase stop` first. The local stack can stay running because `sync` compares your schema files against your migration history, not the live database
34
- **Do not** use `supabase db diff` to generate migrations from schema files. It diffs a live database against your migrations and never uses `supabase/schemas/` as its baseline
35
- Review every generated migration file before committing it
36
37
## 5. **Schema File Organization**
38
39
- File names and directory layout are for readability only. The engine analyzes dependencies between statements (foreign keys, views over tables, functions used by triggers) and orders them automatically. Do not rely on lexicographic file ordering to manage dependencies
40
- When adding new columns, append them to the end of the table definition to prevent unnecessary diffs
41
- Reserve the `supabase/schemas/_custom/` directory for hand-authored SQL covering object kinds the engine does not track (see known caveats). Create the directory yourself. The CLI never creates, overwrites, or prunes it
42
43
## 6. **Rollback Procedures**
44
45
- To revert a change that has **not** been deployed, reset the local database to a previous migration version, then edit the schema files and regenerate a single migration:
46
  ```bash
47
  supabase db reset --version <timestamp>
48
  supabase db schema declarative sync -f <migration_name>
49
  ```
50
- To revert a change that **has** been deployed, first update the relevant `.sql` files in `supabase/schemas/` to reflect the desired state, then generate a new forward migration:
51
  ```bash
52
  supabase db schema declarative sync -f <rollback_migration_name>
53
  ```
54
- Review the generated migration file carefully to avoid unintentional data loss. Down migrations are usually destructive
55
56
## 7. **Known caveats**
57
58
Schema diffs are generated by the `pg-delta` engine, which models most database entities, including tables, views, materialized views, functions, triggers, RLS policies, grants, comments, domains, partitions, and publications. The cases below are not captured.
59
60
### Data manipulation language
61
62
- DML statements such as `insert`, `update`, and `delete` are never captured by a schema diff. This includes storage buckets, which are rows in `storage.buckets`, not schema objects
63
- A DML statement inside a declarative schema file is an error. Keep data changes in seed files or hand-written versioned migrations
64
65
### Untracked object kinds
66
67
- The engine does not track: casts, operators, operator classes and families, text search configurations, dictionaries, parsers, and templates, statistics objects, languages, transforms, and parameter ACLs. The engine reports them as warnings instead of silently dropping them
68
- To use these objects, put their SQL in the reserved `supabase/schemas/_custom/` directory so dependent objects still resolve, and deliver the change itself through a versioned migration. That migration must sort before the generated migration that depends on the object, or `db reset` and later `sync` runs fail
69
- Exception: manage parameter ACLs through versioned migrations only. Do not put them in `_custom/`
70
71
### Supabase-managed schemas
72
73
- Platform objects in Supabase-managed schemas (such as `auth` and `storage`) are excluded from diffs, but your own customizations on top of them are captured, such as triggers on managed tables and RLS policies on `storage.objects`, `storage.buckets`, and `realtime.messages`. Other objects you create inside these schemas, such as your own functions or indexes, are not diffed. Manage those through versioned migrations
74
75
### Extension-managed objects
76
77
- Objects that extensions create and manage, such as partitions maintained by `pg_partman` and queue tables created by `pgmq`, are never emitted as raw `create table` or `drop table` statements. Changes to them appear as calls to the extension's own API (for example `select pgmq.drop_queue('q');`), and the CLI flags those as destructive. Create and change these objects through the extension's own functions and review any generated API calls
78
79
### Legacy `migra` engine
80
81
- If `config.toml` sets `[experimental.pgdelta] enabled = false`, the project uses the legacy `migra`-based workflow (`supabase stop`, then `supabase db diff -f <migration_name>`), which has additional limitations around view owners and grants, security invoker on views, `alter policy`, column privileges, comments, publication membership, roles, and domains. Remove the opt-out instead of following that flow
82
83
---
84
85
**Non-compliance with these instructions may lead to inconsistent database states and is strictly prohibited.**