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. -**Do not\*\* create or modify files directly in the `supabase/migrations/` directory unless the modification is about the known caveats below. Migration files are to be generated automatically through the CLI.
8
9
## 2. **Schema Declaration**
10
11
-For each database entity (e.g., tables, views, functions), create or update a corresponding `.sql` file in the `supabase/schemas/` directory
12
-Ensure that each `.sql` file accurately represents the desired final state of the entity
13
14
## 3. **Migration Generation**
15
16
- Before generating migrations, **stop the local Supabase development environment**
17
  ```bash
18
  supabase stop
19
  ```
20
- Generate migration files by diffing the declared schema against the current database state
21
  ```bash
22
  supabase db diff -f <migration_name>
23
  ```
24
  Replace `<migration_name>` with a descriptive name for the migration
25
26
## 4. **Schema File Organization**
27
28
- Schema files are executed in lexicographic order. To manage dependencies (e.g., foreign keys), name files to ensure correct execution order
29
- When adding new columns, append them to the end of the table definition to prevent unnecessary diffs
30
31
## 5. **Rollback Procedures**
32
33
- To revert changes
34
  - Manually update the relevant `.sql` files in `supabase/schemas/` to reflect the desired state
35
  - Generate a new migration file capturing the rollback
36
    ```bash
37
    supabase db diff -f <rollback_migration_name>
38
    ```
39
  - Review the generated migration file carefully to avoid unintentional data loss
40
41
## 6. **Known caveats**
42
43
The migra diff tool used for generating schema diff is capable of tracking most database changes. However, there are edge cases where it can fail.
44
45
If you need to use any of the entities below, remember to add them through versioned migrations instead.
46
47
### Data manipulation language
48
49
- DML statements such as insert, update, delete, etc., are not captured by schema diff
50
51
### View ownership
52
53
- view owner and grants
54
- security invoker on views
55
- materialized views
56
- doesn’t recreate views when altering column type
57
58
### RLS policies
59
60
- alter policy statements
61
- column privileges
62
- Other entities#
63
- schema privileges are not tracked because each schema is diffed separately
64
- comments are not tracked
65
- partitions are not tracked
66
- alter publication ... add table ...
67
- create domain statements are ignored
68
- grant statements are duplicated from default privileges
69
70
---
71
72
**Non-compliance with these instructions may lead to inconsistent database states and is strictly prohibited.**