Preview Markdown

# PostgreSQL Engineering Protocol for AI-Assisted Development

## Supabase Manual SQL Editor Edition and Portable Master Guide

Version: 1.1<br>
Last researched: 2026-05-26<br>
Intended use: installable project protocol and publishable reference guide

This guide defines how a human and an AI assistant should design, change, secure,
test, and operate a PostgreSQL database. It has two layers:

1. **PostgreSQL Core**: portable rules for any serious PostgreSQL application.
2. **Supabase Overlay**: additional rules for Supabase Auth, Data API, Storage,
   Row Level Security (RLS), and migration tooling.

This is an engineering protocol for teams that deliberately use human-reviewed,
manual application of SQL migrations in the hosted Supabase SQL Editor. It is
not permission for autonomous production changes, a compliance certification, a
penetration test, a privacy/legal assessment, or a replacement for qualified
review of consequential production work. An AI may investigate, design, write
migrations, write tests, and prepare manual application instructions. A human
applies hosted migration SQL through the Supabase SQL Editor.

---

## 1. The Contract

### 1.1 Non-Negotiable Rules

- Schema state is owned by ordered SQL migration files in version control.
- A live database is deployed state, not the source of truth.
- Never edit an already-applied migration to change database behavior. Create a
  new migration.
- Every database object change owned by the application must be expressed in
  reviewable SQL: tables,
  columns, data migrations, constraints, indexes, views, grants, policies,
  triggers, functions, extensions, Storage RLS policies/indexes, and scheduled
  SQL jobs. Platform-managed configuration or object operations that must use a
  supported dashboard/API path must instead be recorded as reviewed deployment
  steps and verification evidence.
- No assistant may invent a data model without first understanding the use case,
  authorization boundary, sensitive data, and rollout impact.
- No assistant may silently broaden access. New access is deny-by-default and
  must be justified as a use-case requirement.
- No assistant may execute remote or hosted SQL migrations in a project adopting
  this protocol.
  The assistant may prepare complete SQL and verification instructions only; a
  human operator applies the SQL in Supabase SQL Editor.
- Under this workflow, no assistant may propose, initialize, or use
  `supabase db push`, local Supabase containers, Docker-based database workflows,
  or CLI-managed migration history as the deployment method. Deployment is
  manual SQL Editor execution unless the project owner first amends the adopted
  protocol.
- Migrations must be designed for failure: validate assumptions, limit lock
  impact, identify rollback or forward-fix strategy, and state irreversibility.
- Database security is tested as behavior, not assumed from the presence of RLS
  or a policy name.

### 1.2 What AI Must Do

For every database task, the AI must:

1. Read this guide and inspect existing migrations and relevant application code.
2. Classify the requested work as design, schema change, data change, access
   change, performance change, operational change, or deployment.
3. Establish the required behavior and security boundary before writing SQL.
4. Surface missing requirements whose wrong assumption could expose data,
   corrupt data, cause downtime, or create an incompatible API.
5. Prefer a small, additive, reversible change when it satisfies the need.
6. Produce the SQL migration, verification steps, security impact, and rollout
   notes.
7. Add or propose tests proportional to security and operational risk.

### 1.3 What AI Must Not Do

- Do not rely on a dashboard-only change or undocumented SQL Editor history.
- Do not make `anon`, `authenticated`, `PUBLIC`, or application roles more
  powerful merely to eliminate a permission error.
- Do not use `using (true)` or `with check (true)` for user-accessible writes
  unless the user has explicitly described a trusted operator-only model and the
  reasoning is recorded.
- Do not introduce `security definer`, `BYPASSRLS`, service-role usage, exposed
  views/functions, or public storage access as a convenience shortcut.
- Do not remove a constraint, RLS policy, grant restriction, or validation check
  without stating which invariant or protection is being removed.
- Do not assume a migration is safe because it succeeds on an empty database.
- Do not mutate rows in Supabase-managed `storage` tables to create, alter,
  move, overwrite, or delete Storage objects or buckets. Use supported Storage
  interfaces for object operations and track the required operator action.
- Do not alter Supabase-managed Auth schema objects as application schema.

---

## 2. Source of Truth and Manual Deployment Mode

### 2.1 Source of Truth and Applied-State Convention

The authoritative records are:

1. Ordered migration SQL files in version control: the reviewed intended
   database transitions.
2. Git-tracked per-environment deployment records: evidence of which immutable
   migration revision was applied and verified in each hosted database.
3. Git-tracked database tests, snapshots, seeds, generated API types, and
   documentation.
4. Live database introspection: confirmation of actual state and drift
   investigation.

A database dashboard is an observation and administration interface. It is not
schema version control.

For this workflow, an approved migration file is committed before hosted
application so that the human runs an immutable reviewed artifact. A migration
file alone does not mean the SQL has been applied. A corresponding deployment
record plus live verification establish applied state for an environment. Once
a migration has been approved for application, do not edit it; create a new
corrective migration if its behavior must change.

### 2.2 Manual Hosted SQL Application

This protocol deliberately uses a no-local-stack, SQL-first deployment workflow
as a manual alternative to Supabase's documented CLI-managed migration
workflow:

1. The AI or developer reads this protocol, existing migrations, and relevant
   application code.
2. The AI prepares a new local migration file plus a design/security/rollout
   explanation.
3. A human reviews the exact migration SQL and its verification plan.
4. Commit the reviewed migration before hosted application. Record its Git
   revision and SHA-256 checksum in the deployment worksheet.
5. One designated human operator confirms the hosted target environment and
   manually runs the exact committed SQL in its Supabase SQL Editor.
6. The human runs the supplied verification queries and applicable access-path
   tests.
7. Create and commit the deployment record for that environment. The record,
   not the migration file alone, states that application succeeded.

The SQL Editor is an execution mechanism in this mode. It is not where schema is
designed or remembered. Git-tracked SQL and deployment records remain the
reviewable record.

The AI must default its deliverable to:

```md
Migration file:
Committed migration revision and SHA-256:
Target environment alias:
Manual apply instructions:
Pre-apply checks:
SQL to run in Supabase SQL Editor:
Post-apply verification:
Security/access tests:
Rollback or forward-fix notes:
Deployment record file:
```

This mode is intentionally human-controlled and does not use Supabase CLI
migration history. It can avoid setting up a local container-based development
stack; it does not automatically provide the replay, migration-history, target
tracking, or team-coordination safeguards supplied by managed migration
tooling. This protocol replaces those safeguards with immutable reviewed SQL,
per-environment deployment records, hosted verification, and staging rehearsal
where risk warrants it. It does not claim that manual paste-based operation is
the best workflow for every team.

For SQL that changes authorization, existing data, constraints on populated
tables, indexes on active tables, destructive objects, or critical API
behavior, rehearse first in a separate hosted Supabase staging project unless
an accountable human explicitly records why the risk is accepted without
staging. Staging in this protocol means another hosted Supabase project where a
human applies the same committed migration using the same SQL Editor procedure;
it does not require local containers.

If applying a migration fails, do not change the approved migration silently or
claim it as applied. Record failed or partial effects when any may remain,
understand the state, and prepare reviewed corrective SQL or a replaced
unapplied revision according to whether the failed artifact has been executed
in any retained environment. If an applied migration later proves wrong, never
edit it; add a new corrective migration.