# 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.

### 2.3 Excluded Deployment Methods and Tooling Facts

A project adopting this protocol does not use `supabase db push`,
Docker/local Supabase, or CLI-managed remote migration history for deployment.
Future AI work within such a project must not silently change that deployment
model.

For reader awareness only, Supabase documents these tooling characteristics:

| Operation | Docker-compatible container runtime required? | Relevance here |
| --- | --- | --- |
| `supabase start` and local development stack | Yes | Excluded |
| `supabase db reset` against the local stack | Yes, because it recreates local Postgres containers | Excluded |
| `supabase test db` in the documented CLI test workflow | It runs `pg_prove` in a container | Excluded |
| `supabase db diff` | It uses a containerized diff tool and shadow database, including linked comparisons | Excluded |
| `supabase db dump` | It runs `pg_dump` in a container | Excluded |
| `supabase db push` to a linked hosted project | The CLI reference requires a linked remote/database connection; it does not require the local stack to be running | Excluded |
| `supabase gen types ... --project-id ...` | It targets a hosted project; local stack is not required by the documented command | Allowed only as optional type generation, not deployment |

Supabase's migration guide recommends CLI migration management and warns that
remote SQL Editor schema changes bypass CLI migration history. Here, manual
execution is an intentional workflow choice because `supabase db push` is not
used. The compensating controls are: commit reviewed migration SQL before
application, manually run the same immutable SQL, record checksums and target
environment evidence, verify live behavior, and reconcile drift through new
reviewed SQL.

### 2.4 Migration File Names

New migration files should use an unambiguous sortable timestamp format:

```txt
supabase/migrations/YYYYMMDDHHMMSS_description.sql
```

Example:

```txt
supabase/migrations/20260525143000_create_project_memberships.sql
```

This is compatible with the convention generated by Supabase tooling, but does
not require using that tooling. When installing this guide in a project that
already has differently named applied historical migrations, do not rename
them merely to adopt the convention.

### 2.5 Recommended Database Files and Snapshots

Use a small, understandable project structure:

```txt
supabase/
  migrations/              # Ordered immutable reviewed SQL transitions
  deployments/             # Per-environment manual application records
  snapshots/               # Optional read-only reference snapshots
  README-db-workflow.md     # This AI and human protocol
```

Migration files are the authority for intended transitions. Deployment records
are the authority for recorded applications to hosted environments. A snapshot
is optional supporting context for review, AI orientation, or drift
investigation; it is not a migration, is not proof that SQL was applied, and
must not replace migration or deployment history.

If a snapshot is useful, record its source environment and capture date in the
file header, keep it free of row data and secrets, and regenerate it only as a
reviewed reference artifact. A project does not need a snapshot file for every
migration.

### 2.6 New Project Bootstrap

When an AI is told to adopt this protocol in a project that does not yet have
database workflow files, it must set up the minimal structure before producing
the first database change:

```txt
supabase/
  README-db-workflow.md
  migrations/
    .gitkeep
  deployments/
    .gitkeep
  snapshots/
    .gitkeep
```

The root agent-instruction file used by the project should also direct future
AI sessions to read this protocol before database, authorization, API-data, or
storage work. Section 13.3 provides the instruction text.

Bootstrap rules:

1. Inspect the repository for existing database SQL, Supabase configuration,
   generated types, application data-access code, and any evidence of an
   already deployed schema.
2. Create missing `supabase/migrations/`, `supabase/deployments/`, and
   `supabase/snapshots/` directories with `.gitkeep` files so the intended
   structure is preserved before they contain artifacts.
3. For a genuinely new empty database, create the first timestamped migration
   only when a real schema change is requested. Do not create a speculative
   initial schema.
4. For an existing hosted database without trustworthy migration history, do
   not treat it as empty and do not write a fictional initial migration. Ask
   for permission/access to inspect the hosted schema or request a sanitized
   schema export, create a reviewed baseline snapshot when appropriate, and
   state how future migrations will proceed from that baseline.
5. Do not invent historical deployment records for migrations unless
   application and verification evidence exists. If adopting the protocol after
   prior manual work, identify the first migration governed by the new record
   procedure and separately document any inspected baseline.
6. Do not create a snapshot merely to fill the folder. Keep `.gitkeep` until
   there is an inspected schema worth recording.
7. Apply all real migration SQL through the same manual SQL Editor workflow:
   commit reviewed SQL, human apply, human verify, then commit the deployment
   record as applied-state evidence.

### 2.7 Deployment Record Format

Store one record per migration application under a nonsecret environment alias:

```txt
supabase/deployments/<environment>/YYYYMMDDHHMMSS_description.md
```

For a public repository, use an alias such as `production` or `staging`, not a
project reference, URL, token, connection string, or customer identifier.

```md
# Migration Deployment Record

Environment alias:
Migration file:
Migration Git revision:
Migration SHA-256:
Applied by:
Applied at (UTC):
Execution method: Supabase SQL Editor (manual)
Result: applied | failed-partial | corrected
Pre-apply checks completed:
Post-apply verification completed:
Authorization/API-path tests completed:
Observed deviations or follow-up migration:
```

Compute the checksum from the committed migration file before pasting it, for
example:

```bash
shasum -a 256 supabase/migrations/YYYYMMDDHHMMSS_description.sql
```

A checksum is not a security review. It proves that the SQL selected for manual
execution matches the reviewed file when the operator verifies the same digest.

---

## 3. AI Requirements Discovery Protocol

An AI should design a database from a user's actual product and risk model, not
from a generic CRUD template. Before a material schema or security change, it
must obtain or explicitly infer the following.

### 3.1 Product and Data Questions

| Area | Required understanding |
| --- | --- |
| User goal | What action or workflow must become possible? |
| Entities | What real-world things exist and how are they related? |
| Ownership | Who owns, creates, edits, publishes, archives, or deletes each record? |
| Identity | Are actors anonymous visitors, signed-in users, staff, organization members, backend jobs, or third parties? |
| Tenancy | Is data global, per user, per organization, per project, or shared by invitation? |
| Visibility | What is public, private, shared, embargoed, draft, or admin-only? |
| Sensitivity | Does the data include personal, payment, credential, health, location, private media, or regulated information? |
| Lifecycle | Creation, publication, edits, soft deletion, hard deletion, retention, export, and audit requirements. |
| Workload | Primary reads/writes, filters, sort order, pagination, background work, and expected scale. |
| Integrations | API clients, webhooks, storage objects, generated media, search, billing, or analytics. |
| Failure impact | What must remain true if an operation partially fails or is replayed? |
| Rollout | Is existing production data present? Must old application code continue working during deploy? |

### 3.2 When the AI Must Pause for an Answer

The AI must ask for clarification before implementing when any unresolved
question changes:

- Whether a user can see or mutate another user's data.
- Whether content is public.
- Whether a signed-in user is an administrator.
- Whether data deletion is permissible or must be recoverable/audited.
- Whether a destructive migration would affect production data.
- Whether a secret, privileged key, or RLS bypass would be required.
- Whether two competing models encode materially different business behavior.

For low-risk implementation detail, the AI may proceed with conservative,
documented assumptions.

### 3.3 Required Design Note

Before SQL for a new feature, the assistant should summarize:

```md
## Database Design Note

Use case:
Actors and trust levels:
Data classification:
Public/API surface:
Authorization invariants:
Entities and relationships:
Expected access patterns:
Migration/rollout constraints:
Assumptions requiring confirmation:
```

For a small additive column or index, this may be abbreviated, but authorization
and destructive-impact questions never disappear.

---

## 4. Data Classification and Threat Model

### 4.1 Classify Before Exposing

Each table, view, function result, storage bucket, and generated API endpoint
must be classified:

| Class | Examples | Default exposure |
| --- | --- | --- |
| Public | Published catalog card, published article metadata | Read only, deliberately exposed |
| Account-private | Profile settings, draft content, user uploads | Owner or authorized staff only |
| Tenant-private | Organization records, internal project media | Membership-based access only |
| Sensitive | Email, addresses, invoices, access logs | Narrow purpose-limited access |
| Highly sensitive | Secrets, tokens, recovery material, regulated records | Not exposed through general data APIs |

Unknown data is treated as private.

### 4.2 Minimum Threat Model

Before approving a data model, consider:

- Anonymous enumeration of records, media paths, or predictable identifiers.
- Authenticated user attempting access to another user's or tenant's data.
- A normal user escalating into staff/admin behavior.
- A compromised browser key or stolen JWT.
- A backend key or service-role secret leaking to client code.
- Over-permissive SQL grants despite apparently restrictive UI behavior.
- RLS policy mistakes, including policy composition widening access.
- RLS information leaks through uniqueness/foreign-key errors or concurrency
  races in authorization lookups.
- `security definer` function abuse or `search_path` object substitution.
- Unsafe dynamic SQL or user-controlled filtering/sorting.
- Accidental publication of draft, failed-processing, or deleted content.
- Migration failure, lock contention, partial backfill, replay, or drift.
- Backups restoring metadata without restoring external/storage objects.

### 4.3 Security Invariants

Security requirements must be written as invariants that tests can prove:

```txt
Anonymous visitors can read published catalog cards only.
Signed-in non-admin users cannot create, update, or delete catalog content.
An editor can update only media belonging to projects they manage.
Unprocessed or failed media is never returned by public reads.
No client-accessible role can execute privileged helper functions directly.
```

Avoid vague statements such as "secured with RLS." RLS is a mechanism; the
invariant states the behavior.

---

## 5. PostgreSQL Schema Design Rules

These rules are portable PostgreSQL guidance unless marked Supabase-specific.

### 5.1 Schemas Are Security Boundaries

- Schema-qualify objects in migrations: `public.posts`, `private.is_admin()`.
- Keep internal tables and privileged helpers outside exposed API schemas where
  the platform permits it.
- Do not put untrusted writable schemas in the `search_path` of privileged code.
- Review `CREATE` privileges on shared schemas. PostgreSQL documents that a
  schema on `search_path` is effectively trusted if untrusted users can create
  objects within it.

Suggested conceptual layout:

```txt
api/public exposed schema   Objects intentionally accessible to API clients
private/internal schema     Authorization helpers, sensitive support data
extensions schema           Optional extension objects, platform permitting
```

An existing application may already expose Data API tables in `public`; that is
acceptable only when grants and RLS are explicit and tested.

### 5.2 Tables Express Invariants

- Give business entities stable primary keys.
- Use `not null` for required facts, not merely TypeScript non-null assertions.
- Use foreign keys for required relationships unless there is a documented
  operational reason not to.
- Add `check` constraints for bounded domain values and cross-column invariants
  that must always hold.
- Add uniqueness where duplicates would violate product behavior.
- Keep timestamps unambiguous with `timestamptz` when recording instants.
- Model publication/state transitions explicitly when visibility depends on
  state, for example `processing_status` and `is_published`.
- Avoid using unconstrained JSON as a replacement for understood relational
  structure; JSON is appropriate when attributes are genuinely open-ended or
  versioned and their query/integrity requirements are understood.

### 5.3 IDs and External Exposure

- Choose identifiers deliberately. UUIDs can reduce casual enumeration but are
  not authorization.
- Never depend on an unguessable URL, slug, UUID, or storage object key to keep
  private data private.
- Public slugs need uniqueness and format constraints; private records still
  need authorization checks.

### 5.4 Referential Integrity and Indexes

- Index columns used in common joins, filters, orderings, and RLS predicates
  when supported by actual access patterns.
- Review every referencing foreign-key column for an index. Primary/unique keys
  on the referenced side do not automatically solve lookup or delete/update work
  on the referencing side.
- Use partial indexes for hot subsets such as published/visible records when
  queries use the matching predicate.
- Do not add indexes reflexively: each index costs write work and storage.
- Validate material performance decisions with `explain (analyze, buffers)` in
  a safe environment using representative data.

### 5.5 Audit, Retention, and Deletion

For user or business-critical data, decide explicitly:

- Is deletion hard-delete, soft-delete, archive, anonymization, or cascade?
- Must changes be auditable, and if so, which actor and timestamp are required?
- Is restoration possible after accidental deletion?
- Does deletion need to remove external objects as well as database metadata?
- Are legal or business retention rules present?

An AI must not choose cascade deletion for important parent/child data without
stating its consequence.

### 5.6 Transactions, Concurrency, and Idempotency

Correct structure does not guarantee correct behavior under simultaneous
requests. For workflows such as bookings, publication, payment fulfillment,
media processing, invitations, quotas, and ordering, document:

- What invariant must hold when two requests race.
- Whether uniqueness, an atomic conditional update, row lock, advisory lock, or
  transaction isolation level enforces that invariant.
- Whether a retried request creates a duplicate side effect.
- Which external-operation identifier makes webhook/job processing idempotent.
- Whether status transitions are valid from every prior state.

Prefer constraints and atomic database operations over an application sequence
of "read, decide, write" that races under concurrency. An AI must not claim a
workflow is safe without identifying how concurrent execution preserves its
invariants.

---

## 6. SQL Authoring Standard for AI

This section governs the SQL itself. It is intended to prevent an AI from
producing syntactically plausible SQL that is semantically weak, inefficient,
unsafe under concurrency, or incompatible with long-lived production data.

### 6.1 Required SQL Reasoning Sequence

Before producing SQL for a new table, query, mutation, function, or index, the
AI must work through this sequence:

1. **State the user operation**: for example, "public visitors list published
   catalog cards in stable display order" or "an editor marks one media item
   ready after processing completes."
2. **State the invariant**: for example, "unready media is never publicly
   visible" or "one external webhook event is processed once."
3. **State the data shape**: entities, cardinalities, optionality, state
   transitions, ownership, deletion behavior, and sensitive fields.
4. **State the access path**: browser through Supabase Data API, server-only
   query, privileged background worker, manual operator query, or reporting job.
5. **State expected query shapes**: filters, joins, sort keys, pagination,
   uniqueness lookups, write frequency, and approximate scale.
6. **Choose enforcement mechanisms**: types, `not null`, defaults, `check`,
   unique constraints, foreign keys, RLS/grants, atomic statements, and indexes.
7. **Write the SQL**: schema-qualified, minimally privileged, and with no
   placeholders hidden behind prose.
8. **Attack and measure it**: identify authorization negative cases,
   concurrent/retry cases, migration failure cases, and performance validation.

If steps 1-6 are unknowable from the request and a wrong assumption matters, the
AI asks before writing production-oriented SQL.

### 6.2 SQL Quality Gate

Generated SQL is acceptable only if an expert reviewer can answer yes, where
applicable, to all of the following:

| Question | Failure mode prevented |
| --- | --- |
| Does the schema encode business invariants rather than leaving them only to UI code? | Corruption and impossible states |
| Are authorization and exposure stated separately from data modeling? | Accidental disclosure or mutation |
| Are columns typed for their meaning rather than for convenience? | Precision, timezone, validation, and query bugs |
| Are optional values deliberately nullable, with null behavior in queries understood? | Missing/incorrect results |
| Are relationships constrained and deletion semantics justified? | Orphans or accidental data loss |
| Does each query retrieve only required rows and columns? | Data exposure and wasted work |
| Are all user values bound/parameterized at the application boundary? | SQL injection |
| Is every mutation bounded and authorization-compatible? | Mass updates/deletes |
| Are retries and races safe for important writes? | Duplicate or inconsistent operations |
| Are indexes justified by concrete query predicates and ordering? | Slow queries or over-indexing |
| Is the query plan measurable on representative data? | Guess-driven performance claims |
| Can this change be safely applied manually and verified in the hosted environment? | Deployment drift and production surprise |

### 6.3 SQL Style and Naming

- Use lowercase unquoted identifiers with `snake_case`. Avoid quoted
  mixed-case names that force quoting forever.
- Schema-qualify persistent objects in migrations and security-sensitive SQL:
  `public.media_items`, not merely `media_items`.
- Name constraints, indexes, policies, and triggers predictably:

```txt
{table}_pkey
{table}_{column}_key
{table}_{column}_fkey
{table}_{purpose}_check
{table}_{query_purpose}_idx
{table}_{operation}_{actor}_policy
set_{table}_updated_at
```

- Use explicit column lists for `insert`, API-facing `select`, view definitions,
  and data movement. Do not use `select *` in durable application/API SQL.
- In examples, `$1`, `$2`, and similar markers describe parameterized
  application queries. Migration SQL manually executed in the SQL Editor must
  contain the reviewed concrete DDL/data operation and must not contain
  unresolved runtime parameters.
- Prefer one obvious query over compact but opaque SQL. A query must be
  maintainable by a future developer and a future AI session.
- Format joins, predicates, and mutation conditions so review of access scope is
  easy:

```sql
select
  image.id,
  image.object_key,
  image.width,
  image.height
from public.media_variants as image
join public.media_items as source
  on source.id = image.image_id
where source.processing_status = 'ready'
  and source.is_visible = true
order by source.sort_order asc, source.id asc;
```

### 6.4 Types Must Encode Meaning

The AI must justify unusual data types and should follow these defaults:

| Meaning | Default PostgreSQL representation | Rule |
| --- | --- | --- |
| External/public opaque identifier | `uuid` | Still require authorization; unpredictability is not access control |
| Internal high-volume sequential identifier | `bigint generated always as identity` | Use when opaque external IDs are unnecessary |
| Human text | `text` | Add `check` constraints for real length/format requirements, not arbitrary `varchar(n)` habit |
| Boolean fact | `boolean not null` with intentional default when applicable | Do not encode as strings |
| Timestamped instant | `timestamptz` | Use for events/deadlines recorded as moments in time |
| Calendar date | `date` | Use when time/zone is not part of meaning |
| Exact currency/decimal calculation | `numeric(p, s)` with domain-specific constraints | Never use floating point for exact money |
| Finite state | `text` plus named `check`, or a carefully justified enum | State evolution and migration cost must be considered |
| Arbitrary metadata/document fragments | `jsonb` | Not a substitute for understood relational columns |
| Ordered or unique collection relation | Child table | Prefer over arrays/JSON when elements need integrity, querying, or ownership |

Example:

```sql
create table public.processing_jobs (
  id uuid primary key default gen_random_uuid(),
  external_event_id text not null unique,
  status text not null default 'pending',
  attempts integer not null default 0,
  run_after timestamptz not null default now(),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint processing_jobs_status_check
    check (status in ('pending', 'processing', 'succeeded', 'failed')),
  constraint processing_jobs_attempts_nonnegative_check
    check (attempts >= 0)
);
```

The unique external event id encodes idempotency; the checks encode valid values.
An AI must identify these behavioral constraints rather than creating an
unconstrained status/text table.

### 6.5 Relational Modeling and Normalization

- Put one fact in one authoritative place unless deliberate denormalization is
  justified by measured access needs and has a synchronization strategy.
- Use a junction table for many-to-many relationships and enforce uniqueness of
  each relationship.
- Keep repeated child data in child tables rather than numbered parent columns
  such as `image_1`, `image_2`, `image_3`.
- Store derived totals, counts, display flags, or search vectors only when their
  consistency and update mechanism are explicit.
- Do not normalize away facts required for historical accuracy. For example, an
  invoice may need a captured price even when a product price later changes.

Pattern:

```sql
create table public.project_memberships (
  project_id uuid not null references public.projects(id) on delete cascade,
  user_id uuid not null references auth.users(id) on delete cascade,
  role text not null,
  created_at timestamptz not null default now(),
  constraint project_memberships_pkey primary key (project_id, user_id),
  constraint project_memberships_role_check
    check (role in ('owner', 'editor', 'viewer'))
);

create index project_memberships_user_id_idx
on public.project_memberships (user_id, project_id);
```

The composite primary key prevents duplicate membership; the reverse-direction
index supports listing projects for a user and common membership policy checks.

### 6.6 Constraints Are Executable Requirements

Before producing a table migration, the AI must evaluate:

- Primary key: what makes one row uniquely identifiable?
- Unique constraints: which duplicates would violate user expectations or
  idempotency?
- Nullability: does missing data have a valid meaning?
- Check constraints: which states, numeric ranges, formats, or relationships
  between fields must always be true?
- Foreign keys: what parent must exist and what should happen on parent deletion
  or identifier update?
- Exclusion/range rules: can two records overlap when the business says they
  cannot, such as bookings or reservations?

Example of state/date integrity:

```sql
create table public.posts (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  status text not null default 'draft',
  published_at timestamptz,
  deleted_at timestamptz,
  created_at timestamptz not null default now(),
  constraint posts_status_check
    check (status in ('draft', 'published', 'archived')),
  constraint posts_published_timestamp_check
    check (
      (status = 'published' and published_at is not null)
      or (status <> 'published')
    )
);
```

For production tables containing existing rows, adding a stricter constraint is
also a migration/validation problem; apply the staged rules in Section 9.

### 6.7 Foreign Keys and Deletion Semantics

The AI must choose an `on delete` action because it expresses product behavior,
not because it is convenient:

| Action | Use only when |
| --- | --- |
| `restrict` / default rejection | Child data must block parent deletion until deliberately addressed |
| `cascade` | Child has no valid independent meaning and deletion is intentionally irreversible or recoverable |
| `set null` | Historical child may remain meaningful without the parent and null is supported by queries/UI |

- Index foreign-key columns used for child lookup, joins, parent deletion, or
  RLS membership checks. PostgreSQL does not automatically create an index on
  the referencing column.
- Avoid cascading deletion across business-critical graphs without a deletion
  inventory and recovery analysis.
- Database deletion does not automatically delete external objects; Supabase
  Storage and any external object-store cleanup need their own reliable workflow.

### 6.8 Writing Read Queries

For each durable query or API view, document:

- Actor/access path.
- Required columns only.
- Filters, including tenant/owner/publication/deletion state.
- Stable order and tie-breaker.
- Expected cardinality and pagination strategy.
- Index supporting the dominant query shape, when needed.

Rules:

- Never return sensitive columns merely because they live on a readable row.
- Use explicit joins whose cardinality is understood; detect whether a join can
  multiply rows unexpectedly.
- Treat nullable predicates carefully: SQL three-valued logic means
  `column <> 'x'` does not include nulls.
- Do not hide authorization mistakes behind frontend filtering.
- Avoid application N+1 access loops; join or batch-fetch deliberately.

Public listing pattern:

```sql
select
  category.slug,
  category.title,
  image.object_key,
  image.width,
  image.height
from public.catalog_categories as category
join public.media_items as source
  on source.id = category.home_image_id
join public.media_variants as image
  on image.image_id = source.id
 and image.variant = 'web'
where category.is_visible = true
  and source.is_visible = true
  and source.processing_status = 'ready'
order by category.sort_order asc, category.id asc;
```

This query requires separate RLS/grant review; a correct predicate is not access
control by itself.

### 6.9 Pagination and Stable Ordering

- Small administrative lists may use bounded `limit`/`offset` when scale and
  consistency requirements allow it.
- For large or public scrolling lists, prefer keyset/cursor pagination because
  deep `offset` queries must skip prior rows and can shift under concurrent
  inserts/deletes.
- Every paginated ordering needs a unique deterministic tie-breaker.

```sql
select
  id,
  title,
  published_at
from public.posts
where status = 'published'
  and (published_at, id) < ($1::timestamptz, $2::uuid)
order by published_at desc, id desc
limit $3;
```

Supporting index:

```sql
create index posts_published_cursor_idx
on public.posts (published_at desc, id desc)
where status = 'published';
```

The partial-index predicate must align with the actual query predicate for
PostgreSQL to use it effectively.

### 6.10 Writing Insert, Update, and Delete Statements

- Use explicit insert column lists.
- Return only fields required by the caller.
- Bound every update/delete by a primary key, verified predicate, or intentional
  batch scope; an unqualified destructive statement is never emitted casually.
- For state transitions, include the permitted prior state in the mutation and
  check whether a row was returned.
- Preview data-changing manual SQL with an equivalent `select` or count whenever
  practical.

Atomic state transition:

```sql
update public.processing_jobs
set
  status = 'succeeded',
  updated_at = now()
where id = $1
  and status = 'processing'
returning id, status, updated_at;
```

If this returns no row, the caller must treat it as a conflict or replay rather
than assuming success.

Small-table manual backfill shape:

Use the single-statement pattern below only after verifying that the affected
table is small or inactive enough for one bounded maintenance operation. It is
not a safe default for an unknown or actively written production table.

```sql
-- Pre-apply verification:
select count(*) as rows_to_backfill
from public.media_items
where processing_status is null;

-- Migration:
update public.media_items
set processing_status = 'ready'
where processing_status is null;

-- Post-apply verification:
select count(*) as remaining_null_rows
from public.media_items
where processing_status is null;
```

For a material production backfill, the AI must instead design a resumable
batch procedure keyed by an indexed stable identifier, for example updating a
reviewed ID range or limited claimed set per execution. The procedure must
state the batch size decision, how progress is recorded, whether concurrent
writes remain correct, what is monitored between batches (runtime, locks, WAL
or service impact where observable), and how the operator stops or resumes it.

### 6.11 Upserts, Retries, and Duplicate Side Effects

- Use `insert ... on conflict` only when a corresponding unique constraint
  precisely identifies the conflict domain.
- An upsert does not automatically make an external side effect idempotent; save
  a unique webhook/event/request id before or together with work that can be
  replayed.
- Decide whether conflict means ignore, update a restricted subset, reject, or
  alert. Never update all columns blindly from untrusted/replayed input.

```sql
insert into public.webhook_receipts (
  provider,
  event_id,
  received_at
)
values ($1, $2, now())
on conflict (provider, event_id) do nothing
returning provider, event_id;
```

If no row is returned, that external event was previously claimed; the worker
must not perform its non-idempotent effect again.

### 6.12 Concurrent Work and Locks

- Keep transactions short and never hold row locks while waiting for user input
  or external HTTP/storage/payment operations.
- Acquire multiple locks in a deterministic order to reduce deadlock risk.
- Use atomic conditional updates for simple transitions before reaching for
  explicit locks.
- For a database-backed worker queue, `for update skip locked` can let workers
  claim independent pending jobs without blocking each other; it must still be
  paired with failure/retry and stale-job recovery rules.
- Use advisory locks only when coordination cannot naturally be expressed by row
  or uniqueness constraints, and define the lock-key namespace explicitly.
- Use stronger transaction isolation only after documenting the invariant and
  retry handling required for serialization failures.

Queue claim pattern:

```sql
with next_job as (
  select id
  from public.processing_jobs
  where status = 'pending'
    and run_after <= now()
  order by run_after asc, id asc
  for update skip locked
  limit 1
)
update public.processing_jobs as job
set
  status = 'processing',
  attempts = job.attempts + 1,
  updated_at = now()
from next_job
where job.id = next_job.id
returning job.id, job.attempts;
```

This belongs in a short transaction. Work outside the database happens after
the claim, with explicit retry/recovery behavior.

### 6.13 Index Synthesis from Query Shapes

An AI must not propose an index without the query it serves. Each index proposal
must identify:

```md
Query shape:
Cardinality/selectivity assumption:
Index SQL:
Why this column order/operator class/predicate:
Write/storage cost:
How to validate with a query plan:
```

Selection rules:

- B-tree: equality, range, sort order, uniqueness, and most ordinary lookups.
- Composite B-tree: place equality-constrained columns before range/order
  columns when that matches the dominant query; remember leftmost-prefix usage.
- Partial index: use when the dominant query reliably includes a stable
  predicate such as `is_visible = true` or `deleted_at is null`.
- `include` columns: consider for frequently read, relatively narrow results
  where an index-only scan is likely useful; included columns are not filtering
  keys and increase index size.
- GIN: suitable for supported JSONB containment, array membership, or full-text
  search operators; not a generic replacement for relational columns.
- GiST/SP-GiST: consider for ranges, geometry, nearest-neighbor, or specialized
  operator classes only when the query uses them.
- BRIN: consider for very large naturally correlated append-oriented data, such
  as timestamped events; validate usefulness for the actual distribution.
- Partitioning: not an early substitute for indexing; justify it from very large
  data volume, lifecycle management, or partition pruning needs.

Do not create duplicate/redundant indexes or index low-selectivity boolean/status
columns in isolation without a measured reason. Every index increases storage
and write/maintenance cost.

### 6.14 Query Plan and Performance Evidence

- Use `explain` to inspect a plan without executing a data-changing query.
- Use `explain (analyze, buffers)` only in a safe environment or on safe
  read-only queries after understanding that it executes the SQL.
- Evaluate estimated versus actual rows, scans on large relations, sort/hash
  spilling, repeated loops, heap fetches, buffer reads, planning time, and
  execution time.
- Measure with representative cardinality and data distribution; an empty or
  tiny development table cannot validate production index behavior.
- After material bulk changes, account for statistics and maintenance needs;
  PostgreSQL plans depend on statistics maintained by `analyze`/autovacuum.
- In Supabase, use dashboard query-performance/advisor tools as evidence, not as
  automatic permission to apply every proposed index.

An acceptable performance claim looks like:

```md
Query: public published catalog listing filtered by visible/ready state.
Before: representative plan and timing recorded in staging.
Change: partial composite index on the filter/order shape.
After: representative plan and timing recorded in staging.
Cost: additional index writes on publish/order changes.
Decision: retain/remove based on measured improvement.
```

### 6.15 JSONB, Full-Text Search, and Advanced Features

- Use ordinary relational columns for fields requiring foreign keys, uniqueness,
  stable filtering, RLS ownership, or frequent ordering.
- Use `jsonb` for legitimately flexible payload fragments; constrain required
  top-level behavior where needed and index only demonstrated query operators.
- For JSONB containment (`@>`) consider GIN; for a frequently queried extracted
  scalar consider an expression index if it remains appropriate as schema.
- Use PostgreSQL full-text search (`tsvector` plus an appropriate GIN index) for
  linguistic document search rather than `%term%` scanning over large content.
- Adopt generated columns, materialized views, partitioning, custom types, or
  extensions only when their update, security, migration, and operational
  implications are explained.

Example of a generated full-text search representation:

```sql
alter table public.posts
add column search_vector tsvector
generated always as (
  to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
) stored;

create index posts_search_vector_idx
on public.posts using gin (search_vector);
```

The language configuration and exposure of searchable text must match product
requirements; this is not automatic permission to expose private content.

### 6.16 Functions, Triggers, and Views

- Prefer declarative constraints and ordinary SQL before triggers.
- Use a trigger for cross-row or automatic behavior only when its hidden
  execution cost, recursion behavior, and security context are understood.
- A function must specify caller, volatility (`immutable`, `stable`, or
  `volatile`) correctly, privilege mode, `search_path` safety, inputs, output
  exposure, and failure semantics.
- A view is a durable interface: use explicit columns, treat it as an API
  contract, and apply the Supabase RLS/view rules in Section 8.
- Materialized views require an explicit refresh and staleness policy.

### 6.17 SQL Output Required From an AI

When the request involves SQL authoring, the AI response must include:

```md
Use case and invariant:
Assumptions requiring confirmation:
Tables/data touched:
Authorization and exposure:
Query shapes and indexes:
Migration filename:
Complete SQL:
Manual apply order:
Pre-apply verification SQL:
Post-apply verification SQL:
Negative authorization tests:
Concurrency/retry analysis:
Lock/data-loss/recovery notes:
```

For a query-only optimization or analysis, replace migration/application fields
with the original query, proposed query/index, and measurement procedure.

### 6.18 SQL Rejection Criteria

An AI must revise or refuse to recommend SQL that:

- Uses broad writes or reads without a justified actor and scope.
- Assumes frontend filtering supplies security.
- Uses a signed-in role as an admin role without proof.
- Omits required constraints while claiming an invariant is enforced.
- Adds an index without a concrete query shape and validation path.
- Implements "check then write" for a contested invariant without atomicity.
- Makes a destructive change without impact, backup/recovery, and manual
  verification steps.
- Hides complex state or authorization behavior inside a function/trigger
  without documenting privileges and tests.
- Claims a design is production-ready based solely on syntax or empty-database
  success.

---

## 7. Authorization and Privilege Model

### 7.1 Authorization Is Layered

Use multiple boundaries deliberately:

1. Network/API exposure determines whether an interface is reachable.
2. Schema `USAGE` and object grants determine which operations a role can try.
3. RLS determines which rows an eligible role can access or mutate.
4. Column grants or API design limit sensitive fields when needed.
5. Application logic handles workflows not safely represented as direct table
   access, such as payments, rate limits, approvals, and multi-step mutations.

RLS never substitutes for grants. Grants never substitute for RLS when rows
within an exposed table have different authorization rules.

### 7.2 Least Privilege and Deny by Default

- Grant only required operations, not `all`.
- Avoid table-wide writes for broad roles when only a controlled RPC or backend
  workflow should perform mutation.
- Do not grant `TRUNCATE`, `REFERENCES`, or `TRIGGER` to API roles as a byproduct
  of granting data operations.
- Treat functions separately: PostgreSQL grants function `EXECUTE` to `PUBLIC`
  by default unless revoked or default privileges are changed.
- Treat access changes as security migrations requiring tests.

### 7.3 Row Level Security Rules

- Enable RLS on tables exposed to untrusted or differently privileged users.
- If the application uses per-user or per-tenant access, encode those
  boundaries at the database layer where direct API access exists.
- Write operation-specific policies: `select`, `insert`, `update`, `delete`.
- Use both `using` and `with check` where update/insert authorization depends on
  the resulting row.
- In Supabase Data API workflows, ensure any authorized `update` also has the
  required `select` visibility; otherwise an allowed-looking update may affect
  no rows.
- Understand policy composition: permissive policies combine with `OR`; one
  broad policy can defeat several narrow policies.
- For every exposed table with more than one applicable policy, provide a policy
  composition table before deployment:

```md
Table and command:
Policies that apply:
Permissive policies and resulting OR access:
Restrictive policies and resulting AND restriction:
Unexpected widening risk:
Negative tests that prove denied cases:
```

- If restrictive policies are used, remember that they narrow access only after
  at least one permissive policy allows the operation.
- Remember that table owners normally bypass RLS and PostgreSQL roles with
  `BYPASSRLS` always bypass it. Test policies as actual client roles.
- Consider `force row level security` for appropriate non-platform-owned tables
  accessed by their owner, after confirming platform behavior and operational
  requirements.

### 7.4 Referential-Integrity Leaks and Authorization Races

PostgreSQL enforces uniqueness, primary-key, and foreign-key integrity even
when an RLS policy hides the conflicting or referenced row. A user may
therefore learn that a hidden identifier or relationship exists from a
constraint error. For sensitive or tenant-private data, an AI must:

- Assess whether user-supplied keys can collide with hidden rows or reference
  hidden parents and reveal information through success/error differences.
- Avoid claiming that RLS alone conceals existence when constraints create such
  a channel; redesign identifiers/API behavior or explicitly accept the
  disclosure only when the use case permits it.
- Include negative tests for create/update operations against hidden existing
  relationships when the risk applies.

Policies that read membership, role, entitlement, or revocation tables also
require concurrency analysis. A simultaneous authorization change and protected
read/write can produce results based on an earlier snapshot unless the design
controls the race. For material access revocation or elevation behavior, state
the required immediacy, choose a transaction/locking/session-revocation or
application mediation strategy, and test the race in hosted staging.

### 7.5 Policy Design Pattern

For user-owned rows in Supabase:

```sql
alter table public.documents enable row level security;

create policy "documents_select_own"
on public.documents
for select
to authenticated
using ((select auth.uid()) = owner_id);

create policy "documents_insert_own"
on public.documents
for insert
to authenticated
with check ((select auth.uid()) = owner_id);

create policy "documents_update_own"
on public.documents
for update
to authenticated
using ((select auth.uid()) = owner_id)
with check ((select auth.uid()) = owner_id);

create index documents_owner_id_idx
on public.documents (owner_id);
```

For public content, constrain the publicly readable state:

```sql
create policy "posts_select_published"
on public.posts
for select
to anon, authenticated
using (published_at is not null and deleted_at is null);
```

Public read does not imply public write.

### 7.6 Staff and Admin Access

`authenticated` means signed in, not administrator. If only staff should manage
content, an AI must not issue this policy without a verified trusted-user model:

```sql
-- Unsafe for a normal-user application: every signed-in user can write.
create policy "authenticated_can_update_everything"
on public.posts for update to authenticated
using (true) with check (true);
```

Use a verified membership/role model, a backend-only path, or a protected
authorization helper, and test an ordinary signed-in user as a negative case.

### 7.7 Functions and Privilege Escalation

- Default to `security invoker`.
- Use `security definer` only when its elevated behavior is required and
  justified.
- For a `security definer` function, set a safe `search_path`, schema-qualify
  referenced objects, revoke default execute privileges, and grant execution
  only where needed in the same migration/transaction.
- In Supabase, keep security-definer authorization helpers in a non-exposed
  schema when possible.
- Never expose a function returning secrets or authorization internals merely
  because a policy needs to call it.

Pattern:

```sql
begin;

create schema if not exists private;

revoke all on schema private from public;
revoke all on schema private from anon, authenticated;

create or replace function private.is_editor()
returns boolean
language sql
stable
security definer
set search_path = ''
as $$
  select exists (
    select 1
    from public.user_roles
    where user_id = (select auth.uid())
      and role = 'editor'
  );
$$;

revoke all on function private.is_editor() from public;
grant usage on schema private to authenticated;
grant execute on function private.is_editor() to authenticated;

commit;
```

Whether `authenticated` should execute this helper depends on whether it is
callable through an exposed API schema and whether its boolean result discloses
anything unacceptable. Before approving this pattern, verify that `private` is
not an exposed Data API schema and test that the function cannot be invoked as an
unintended exposed RPC. A policy helper is not automatically safe for direct
client invocation.

### 7.8 SQL Injection and Dynamic SQL

- Application queries must parameterize user-controlled values.
- User-controlled identifiers, sort columns, operators, and filter structure
  cannot generally be made safe by ordinary value binding; map them through an
  explicit allowlist.
- SQL/PLpgSQL dynamic SQL requires the same scrutiny; avoid it unless needed.
- RLS does not make SQL injection harmless. A privileged backend or function can
  still expose or damage data.

---

## 8. Supabase Security Overlay

This section applies when Supabase products or generated APIs touch the database.

### 8.1 Choose the Exposure Architecture

For every feature, declare one of these:

| Access path | Appropriate when | Security requirement |
| --- | --- | --- |
| Supabase Data API from browser | Clients need direct reads/writes | Strict grants plus RLS on exposed tables/views; controlled `execute` and security review for RPC functions |
| Server/Edge Function only | Workflow needs secrets or centralized authorization | Do not expose unnecessary tables/functions through Data API |
| Direct Postgres from trusted server | Controlled backend service owns data access | Credentials secret, narrow DB role, Data API disabled if unused |

If the app does not use REST, GraphQL, or Supabase client access to database
objects, disable the Data API rather than securing an unused public surface.

### 8.2 Exposed Schemas

- Supabase exposes `public` through its Data API by default.
- Prefer a dedicated exposed `api` schema for high-assurance systems, leaving
  internal tables and functions outside the exposed schema.
- For an existing app in `public`, explicitly inventory every API-reachable
  table, view, and function; do not assume obscurity.
- Do not create privileged functions in exposed schemas unless their exposure is
  required and safe.

### 8.3 Data API Grants and RLS

Supabase Data API access uses two separate checks:

1. Grants determine whether `anon`, `authenticated`, or `service_role` may call
   an operation on an object.
2. RLS determines which rows are permitted for eligible table operations.

Bundle RLS and grants in the same migration. A public read/admin write table
pattern might be:

```sql
begin;

create table public.posts (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  body text not null,
  owner_id uuid not null references auth.users(id) on delete restrict,
  published_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index posts_public_published_idx
on public.posts (published_at desc)
where published_at is not null;

alter table public.posts enable row level security;

revoke all privileges on table public.posts from anon, authenticated;
grant select on table public.posts to anon, authenticated;

create policy "posts_public_read_published"
on public.posts
for select
to anon, authenticated
using (published_at is not null);

-- Write policies and write grants must be added only after the editor/admin
-- authorization model is defined and tested.

commit;
```

Do not grant signed-in writes simply because an admin UI uses a logged-in user.
Prove which signed-in identities are editors.

Secret/service-role credentials and roles with `BYPASSRLS` are privileged
paths, not evidence that ordinary Data API access is safe. Never use a
service-role request as the positive or negative test for an end-user policy.

### 8.4 Default Privileges

Supabase documentation states that existing projects may automatically grant new
objects in `public` to API roles, making new objects reachable immediately. For
an opt-in exposure model, evaluate default-privilege revocations for the
object-creating role. The following removes future table/sequence access for
Supabase API roles in `public` and removes PostgreSQL's global default public
execution privilege for functions created by `postgres`:

```sql
alter default privileges for role postgres in schema public
  revoke select, insert, update, delete on tables
  from anon, authenticated, service_role;

alter default privileges for role postgres in schema public
  revoke execute on functions
  from anon, authenticated, service_role;

alter default privileges for role postgres in schema public
  revoke usage, select on sequences
  from anon, authenticated, service_role;

alter default privileges for role postgres
  revoke execute on functions from public;
```

Before running this on an existing project, confirm which role creates objects
and test application behavior. Default privileges affect newly created objects,
not a retrospective audit of every existing object.

Important PostgreSQL distinction:

- New functions normally receive `execute` for `PUBLIC`.
- A schema-scoped `alter default privileges ... in schema public revoke execute
  on functions from public` cannot remove that global built-in default. Per
  PostgreSQL documentation, per-schema defaults can add privileges or reverse a
  matching per-schema grant, but cannot revoke a globally granted default.
- The global function revoke shown above applies to future functions created by
  the specified creator role in every schema. If that is too broad for the
  project's role design, explicitly revoke and grant execution within the same
  transaction as each function creation instead.

For every created or replaced API-relevant function, verify actual privileges
after application; do not assume default-privilege configuration is correct.

### 8.5 Supabase Keys and Auth

- A publishable/legacy anonymous key may be present in frontend code only when
  the exposed surface is protected by correctly tested grants and RLS.
- A secret or service-role key must never be shipped to browsers or untrusted
  clients; it can bypass RLS.
- Do not use `raw_user_meta_data`/user-editable metadata as an authorization
  source. If JWT application metadata participates in authorization, validate
  the design around trusted `raw_app_meta_data`, token staleness, and required
  revocation behavior.
- Use higher assurance, such as MFA level checks, for truly sensitive actions
  when the product requires it.
- The `auth` schema is managed by Supabase and is not an ordinary application
  API schema. Keep application-readable profile/role data in application-owned
  protected tables; reference only supported managed identifiers such as the
  primary key of `auth.users` when needed for integrity.
- Do not alter Supabase-managed Auth tables, indexes, or constraints as
  application schema. Any trigger attached to `auth.users` can affect
  authentication workflows; test signup, user deletion, failure behavior, and
  rollback in hosted staging before relying on it.
- Distinguish unauthenticated requests using the database role `anon` from
  Supabase anonymous Auth users, which are signed-in users and use the
  `authenticated` role with an anonymous-user JWT claim.

### 8.6 RLS Performance

For Supabase RLS policies:

- Index columns used to enforce ownership/membership predicates.
- For request-stable JWT functions, prefer `(select auth.uid())` and
  `(select auth.jwt())` rather than per-row calls where appropriate.
- Filter application queries by the expected tenant/user/public-state columns
  as well as relying on RLS for security; RLS is not an application query
  optimization strategy.
- Measure policy-heavy query plans on realistic cardinality.

### 8.7 Storage

- Storage objects and database metadata are separate assets.
- Treat Supabase-managed `storage` table records and schema as read-only from
  application migration SQL. Do not create buckets by inserting into
  `storage.buckets`, and do not insert, update, move, or delete file metadata in
  `storage.objects` through SQL. Use the supported Storage API/dashboard path
  for bucket and object operations, and record/verify that operator step.
- Reviewed SQL may create the RLS policies needed on `storage.objects` and
  justified indexes supported by Supabase guidance; those policy/index changes
  still require real Storage API behavior tests.
- Bucket visibility and `storage.objects` policies must match the content
  classification and publication model.
- A public bucket is not suitable for private, draft, unprocessed, embargoed,
  or customer-specific media unless exposure is acceptable by design.
- Upload, overwrite/upsert, read/list, move, and delete are distinct operations;
  grant/policy only those required.
- A database backup does not restore deleted Storage API objects; design media
  backup and deletion recovery separately.

### 8.8 Security and Performance Advisors

Run Supabase Security Advisor and Performance Advisor after schema/security
changes. Treat warnings about the following as requiring resolution or written
acceptance:

- RLS disabled in exposed schemas.
- Policy exists while RLS is disabled.
- RLS enabled without policy when access was intended.
- Unindexed foreign keys or policy-critical columns.
- Mutable function `search_path` or exposed `security definer` behavior.
- Sensitive columns or public buckets exposed through APIs.
- Broad permissive policies such as `using (true)`.

### 8.9 Views and RPC Functions

- Treat a view or materialized view in an exposed schema as part of the public
  API surface; review what underlying data it reveals and how access is enforced.
- In PostgreSQL 15+ on Supabase, create an API-accessible view that must inherit
  underlying RLS with `security_invoker = true`. Without this setting, views
  normally execute with their creator's privileges and can bypass underlying
  table RLS:

```sql
create view public.published_posts
with (security_invoker = true)
as
select id, title, published_at
from public.posts
where published_at is not null;
```

- On older PostgreSQL versions, keep such views outside exposed schemas or
  revoke access from API roles unless a separately reviewed security model
  protects them.
- Treat an RPC/database function as an endpoint: specify inputs, caller roles,
  output sensitivity, mutation effects, and function execution grants.

### 8.10 Realtime, Edge Functions, and Scheduled Work

RLS and Data API request hooks do not automatically prove equivalent behavior
for every Supabase product. If a feature uses Realtime, classify it as
Postgres Changes, Broadcast, Presence, or a combination, and document:

- Which tables, topics, events, and payload fields can be observed or sent.
- Whether private channels are required and whether Realtime public access is
  disabled in project settings where required.
- Required policies on `realtime.messages` for Broadcast/Presence and the
  client requirement to join the intended private channel.
- Postgres Changes read authorization and whether all published row fields are
  appropriate for subscribers.
- How a revoked membership, expired/refreshed JWT, or already-connected client
  is expected to lose access, because connection authorization can be cached.

Test Realtime through an actual client connection as allowed and denied actors;
SQL catalog inspection alone is not enough. Apply the same product-specific
approach to Storage and Edge Functions. Treat secret/service-role use in server
or Edge Function code as privileged application behavior requiring its own
authorization and secret-handling review.

Track scheduled SQL/jobs in migrations or another reviewed infrastructure
source, and restrict privileged job logic as carefully as request-driven code.

---

## 9. Migration Engineering

### 9.1 Hosted Target and Version Preflight

Before producing or approving version-sensitive Supabase SQL, record the target
environment alias and inspect the hosted PostgreSQL/runtime assumptions. At
minimum, run:

```sql
select version();

select extname, extversion
from pg_extension
order by extname;
```

Also confirm dashboard-controlled settings relevant to the change, such as Data
API exposed schemas, Realtime public/private configuration, backups/PITR, and
Storage bucket visibility. A catalog query cannot prove all platform settings.
Current PostgreSQL documentation describes upstream behavior; it does not prove
which PostgreSQL major version or optional feature a particular Supabase
project is running.

### 9.2 Migration Header

Material migrations should begin with a short operational header:

```sql
-- Purpose: Add private documents owned by one authenticated user.
-- Authorization: Owner-only read/write; no anonymous access.
-- Data impact: Additive; no existing rows changed.
-- Lock/availability: Creates new objects only.
-- Rollback: Drop new objects only before application use; otherwise forward-fix.
```

### 9.3 Transaction Rules

- Use a transaction for related DDL/data/security changes when every statement
  can run transactionally and atomicity matters.
- Set transaction-local timeout parameters for production-sensitive operations
  where appropriate, based on observed workload and a deployment plan:

```sql
begin;
set local lock_timeout = '5s';
set local statement_timeout = '60s';
-- safe transactional statements
commit;
```

- `statement_timeout` applies to each statement, and `lock_timeout` applies to
  each lock acquisition wait; neither limits the total duration of an entire
  multi-statement migration. Plan and observe total execution duration
  separately. Use any additional version-dependent timeout only after target
  version confirmation.
- Do not blindly retry a timed-out migration. Inspect whether any statement
  applied and whether the migration is transactional.
- `create index concurrently` and `drop index concurrently` cannot run inside a
  transaction block. Isolate and document those migrations.

### 9.4 Expand, Migrate, Contract

For live systems, changes should generally follow:

1. **Expand**: add compatible schema, nullable column, new table, new index, or
   dual-read/dual-write path.
2. **Migrate**: backfill in controlled batches, validate data, observe behavior.
3. **Contract**: after old code no longer depends on it, enforce stricter
   constraints or remove obsolete columns/objects.

Do not combine a destructive contract phase with the initial feature deploy
unless the system is provably empty or downtime/data loss is explicitly accepted.

### 9.5 Safe Change Patterns

#### Add a Required Column to a Populated Table

Preferred staged approach:

1. Add nullable column or column with safe behavior for old code.
2. Write new data through the application.
3. Backfill existing rows deliberately and verify no missing values remain.
4. Add/validate constraint.
5. Make application assumptions only after enforcement is deployed.

#### Add a Foreign Key or Check Constraint

For large/active tables, evaluate adding the constraint as `not valid` and
validating it separately so existing-row validation has reduced lock impact.
New rows are still checked after a `not valid` constraint is added. Use only
after checking PostgreSQL version behavior and deployment requirements.

#### Add an Index

- On small or inactive tables, a normal transactional index may be suitable.
- On actively written production tables, consider `create index concurrently`,
  recognizing that it takes longer, performs additional work, cannot be inside a
  transaction, and failed builds can leave invalid indexes requiring cleanup.

#### Rename or Remove a Column

- Treat rename as API-breaking unless every consumer is controlled.
- Prefer adding a replacement column, migrating reads/writes, then removing the
  old column in a later release.
- A `drop column` migration must state whether data is permanently lost and
  whether a backup/restore plan exists.

#### Change Authorization

- Treat grant or policy widening like an API/security release.
- Add positive tests for intended actor access and negative tests for anonymous,
  unrelated authenticated, cross-tenant, and deactivated actors as applicable.
- Deploy authorization before application paths that depend on new access, and
  never temporarily open a table to get a frontend unstuck.

### 9.6 Data Migrations

- Make backfills deterministic, scoped, and idempotent or explicitly one-time.
- State the expected row set before executing an update/delete.
- For high-impact updates, run an equivalent `select`/count verification first.
- Avoid running unbounded AI-generated data updates directly against production.
- For nontrivial affected row counts or active tables, use a resumable,
  key-bounded batch plan with recorded progress rather than one large pasted
  `update`/`delete`.
- If external object creation/deletion participates, define retry and
  reconciliation behavior; a SQL transaction cannot atomically undo an external
  storage operation.

### 9.7 Rollback and Recovery

- Additive schema migrations usually use a forward fix after application traffic
  begins depending on them.
- Destructive data migrations require a backup/restore or compensating plan
  before deployment.
- A down migration is not a substitute for data recovery when it cannot restore
  deleted or transformed values.
- Confirm backups and restoration procedures before high-risk production work.

---

## 10. Verification and Testing

### 10.1 Definition of Done for a Database Change

A database change is complete only when applicable checks pass:

- Requirements and security invariants are documented.
- Migration SQL is stored in a new ordered file.
- Approved migration SQL is committed before manual hosted application and its
  checksum is included in the deployment worksheet.
- SQL has been reviewed for grants, RLS, functions, locks, data loss, and
  backward compatibility.
- Migration applies cleanly to an isolated/test database or an explicitly
  documented manual verification path has been used.
- Schema and security behavior are tested.
- Application code and generated types are updated where relevant.
- Production rollout and recovery notes exist for non-trivial changes.
- A deployment record is committed after each hosted application attempt that
  succeeded or may have left partial effects.
- Deployed state is verified and drift is not left unexplained.

### 10.2 Schema Inspection Queries

Use these to inspect an environment. They are diagnostics, not migration history.

#### Tables and Columns

```sql
select
  table_schema,
  table_name,
  column_name,
  data_type,
  is_nullable,
  column_default
from information_schema.columns
where table_schema in ('public', 'api', 'private')
order by table_schema, table_name, ordinal_position;
```

#### Constraints

```sql
select
  n.nspname as schema_name,
  c.relname as table_name,
  con.conname as constraint_name,
  con.contype as constraint_type,
  con.convalidated as is_validated,
  pg_get_constraintdef(con.oid) as definition
from pg_constraint con
join pg_class c on c.oid = con.conrelid
join pg_namespace n on n.oid = c.relnamespace
where n.nspname in ('public', 'api', 'private')
order by schema_name, table_name, constraint_name;
```

#### Indexes

```sql
select
  n.nspname as schema_name,
  t.relname as table_name,
  i.relname as index_name,
  x.indisready as is_ready,
  x.indisvalid as is_valid,
  pg_get_indexdef(i.oid) as definition
from pg_index x
join pg_class i on i.oid = x.indexrelid
join pg_class t on t.oid = x.indrelid
join pg_namespace n on n.oid = t.relnamespace
where n.nspname in ('public', 'api', 'private')
order by schema_name, table_name, index_name;
```

After any `create index concurrently`, require `is_ready = true` and
`is_valid = true`; a failed concurrent build may leave an invalid index that
still incurs write overhead.

#### RLS Status

```sql
select
  n.nspname as schema_name,
  c.relname as table_name,
  c.relrowsecurity as rls_enabled,
  c.relforcerowsecurity as rls_forced
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind in ('r', 'p')
  and n.nspname in ('public', 'api')
order by schema_name, table_name;
```

#### Policies

```sql
select
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual,
  with_check
from pg_policies
where schemaname in ('public', 'api')
order by schemaname, tablename, policyname;
```

#### Grants for API-Facing Objects

```sql
select
  table_schema,
  table_name,
  grantee,
  string_agg(privilege_type, ', ' order by privilege_type) as privileges
from information_schema.table_privileges
where table_schema in ('public', 'api')
  and grantee in ('anon', 'authenticated', 'service_role', 'PUBLIC')
group by table_schema, table_name, grantee
order by table_schema, table_name, grantee;
```

#### Functions and Function Security

```sql
select
  n.nspname as schema_name,
  p.proname as function_name,
  p.prosecdef as security_definer,
  p.proconfig as function_settings,
  pg_get_function_identity_arguments(p.oid) as arguments
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname in ('public', 'api', 'private')
order by schema_name, function_name;
```

#### Function Execute Grants

```sql
select
  routine_schema,
  routine_name,
  grantee,
  privilege_type
from information_schema.routine_privileges
where routine_schema in ('public', 'api', 'private')
  and grantee in ('PUBLIC', 'anon', 'authenticated', 'service_role')
order by routine_schema, routine_name, grantee;
```

Also inspect `pg_proc.proacl` for API/security-relevant functions because it
shows raw access-control state and helps identify unexpected `PUBLIC` execution:

```sql
select
  n.nspname as schema_name,
  p.proname as function_name,
  pg_get_function_identity_arguments(p.oid) as arguments,
  p.prosecdef as security_definer,
  p.proacl
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname in ('public', 'api', 'private')
order by schema_name, function_name, arguments;
```

#### Schema Privileges

```sql
select
  n.nspname as schema_name,
  role_name,
  has_schema_privilege(role_name, n.oid, 'USAGE') as has_usage,
  has_schema_privilege(role_name, n.oid, 'CREATE') as has_create
from pg_namespace n
cross join (
  values ('anon'), ('authenticated'), ('service_role')
) as roles(role_name)
where n.nspname in ('public', 'api', 'private')
order by schema_name, role_name;

select
  n.nspname as schema_name,
  case
    when acl.grantee = 0 then 'PUBLIC'
    else pg_get_userbyid(acl.grantee)
  end as grantee,
  acl.privilege_type,
  acl.is_grantable
from pg_namespace n
cross join lateral aclexplode(
  coalesce(n.nspacl, acldefault('n', n.nspowner))
) as acl
where n.nspname in ('public', 'api', 'private')
  and (
    acl.grantee = 0
    or pg_get_userbyid(acl.grantee) in ('anon', 'authenticated', 'service_role')
  )
order by schema_name, grantee, privilege_type;
```

No untrusted API role should have `create` in a schema trusted by privileged
code. Confirm configured Data API exposed schemas in Supabase project settings;
that platform configuration is not fully proven by catalog queries alone.

#### Views and Materialized Views

```sql
select
  schemaname,
  viewname,
  viewowner,
  definition
from pg_views
where schemaname in ('public', 'api')
order by schemaname, viewname;

select
  schemaname,
  matviewname,
  matviewowner,
  definition
from pg_matviews
where schemaname in ('public', 'api')
order by schemaname, matviewname;
```

For every API-accessible PostgreSQL 15+ view relying on underlying-table RLS,
inspect its reloptions and require `security_invoker=true`:

```sql
select
  n.nspname as schema_name,
  c.relname as view_name,
  c.reloptions
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'v'
  and n.nspname in ('public', 'api')
order by schema_name, view_name;
```

#### Sequence Grants

```sql
select
  object_schema,
  object_name,
  grantee,
  privilege_type
from information_schema.usage_privileges
where object_type = 'SEQUENCE'
  and object_schema in ('public', 'api')
  and grantee in ('PUBLIC', 'anon', 'authenticated', 'service_role')
order by object_schema, object_name, grantee, privilege_type;
```

#### Column Grants

```sql
select
  table_schema,
  table_name,
  column_name,
  grantee,
  privilege_type
from information_schema.column_privileges
where table_schema in ('public', 'api')
  and grantee in ('PUBLIC', 'anon', 'authenticated', 'service_role')
order by table_schema, table_name, column_name, grantee, privilege_type;
```

Use this to identify sensitive columns exposed independently of table-wide
expectations.

#### Default Privileges

```sql
select
  defaclrole::regrole as creator_role,
  coalesce(n.nspname, '(global)') as schema_name,
  defaclobjtype as object_type,
  defaclacl as default_acl
from pg_default_acl d
left join pg_namespace n on n.oid = d.defaclnamespace
order by creator_role::text, schema_name, object_type;
```

Review function defaults in particular: default function `PUBLIC` execution is
global unless correctly revoked for the creating role.

#### Supabase Storage Buckets and Policies

```sql
select
  id,
  name,
  public
from storage.buckets
order by id;

select
  schemaname,
  tablename,
  policyname,
  roles,
  cmd,
  qual,
  with_check
from pg_policies
where schemaname = 'storage'
  and tablename = 'objects'
order by policyname;
```

Verify the bucket's public/private classification separately from metadata-table
RLS. Storage upload, overwrite, listing, read, move, and delete behavior must be
tested using the actual actor/API path.

### 10.3 Policy Composition Review

For every exposed table with multiple applicable policies, complete and review:

```md
Table and command:
Policies that apply:
Permissive policies and resulting OR access:
Restrictive policies and resulting AND restriction:
Unexpected widening risk:
Negative tests that prove denied cases:
```

### 10.4 Authorization Test Matrix

Every exposed feature should have a table like this and tests for each required
cell:

| Actor | Read public | Read own/private | Read other user's | Create | Update own | Update other's | Delete |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Anonymous | Define | Deny | Deny | Deny | Deny | Deny | Deny |
| Normal authenticated user | Define | Define | Deny | Define | Define | Deny | Define |
| Editor/admin | Define | Define | Define narrowly | Define | Define | Define narrowly | Define narrowly |
| Backend worker | Define | Define | Define | Define | Define | Define | Define |

Fill `Define` from the use case. Never replace it with assumption.

### 10.5 Test Through the Actual Supabase Access Path

SQL Editor queries normally run in an elevated administrative context and do
not prove browser/client authorization behavior. For any access-surface change,
test the operation through each relevant real path:

- An unauthenticated Data API request using the publishable/anonymous client
  context when public access is intended or must be denied.
- An ordinary authenticated user token, including cross-user or cross-tenant
  negative cases.
- An anonymous Auth user token when the product allows anonymous sign-in,
  because that actor maps to `authenticated`, not the `anon` database role.
- Storage API, Realtime client channel, Edge Function, or backend request paths
  when the feature uses them.

Do not use a service-role or secret-key request to prove an end-user denial:
that is a privileged path that can bypass RLS. Keep test credentials, JWTs, and
private URLs out of committed deployment records.

### 10.6 Manual Hosted Staging Tests

In this no-Docker workflow, include repeatable SQL verification queries and
authorization tests with each security-sensitive or non-trivial migration.
Recommend rehearsal in a separate hosted Supabase staging project before
applying SQL that changes any of:

- RLS policies, grants, roles, functions, views, exposed schemas, storage
  buckets/policies, or authentication/authorization behavior.
- Existing data through a backfill, correction, deletion, or data-shape
  transformation.
- Constraints on populated tables, indexes on active tables, triggers,
  scheduled/background operations, or destructive/contract phases.
- API-visible types/columns/relationships or critical query behavior.

For a small additive change with a clear verification plan, the human operator
may proceed directly after review. Add application-level integration tests for
the generated API and storage behavior; database unit tests do not prove
frontend/backend wiring.

### 10.7 Type Generation

When application code uses Supabase generated APIs, regenerate database types
after manually applying the migration to the intended hosted project and then
type-check the application. This optional read/generation operation does not
require starting a local Supabase stack:

```bash
npx supabase gen types typescript --project-id "$SUPABASE_PROJECT_REF" > database.types.ts
```

Confirm the project reference before generating types. Generated TypeScript types
assist compile-time correctness; they are not access control.

### 10.8 Concurrency and Interruption Test Recipes

For concurrency- or retry-sensitive behavior, a review statement is insufficient;
demonstrate the relevant failure mode in hosted staging before production.

#### Competing State Transition or Quota Claim

1. Prepare two sessions operating as the intended actor(s).
2. Start the same permitted transition or quota-consuming operation from both
   sessions before either completes.
3. Verify exactly one succeeds when the invariant allows only one success, or
   verify the bounded final total when multiple successes are permitted.
4. Verify no unauthorized or invalid-state row exists afterward.

#### Replayed Webhook or External Event

1. Submit the same provider/event identifier twice.
2. Verify the idempotency receipt/unique constraint accepts only one claim.
3. Verify non-idempotent downstream effects happen once or are otherwise safely
   reconciled.

#### Worker Claim and Crash Recovery

1. Have one worker claim a job and simulate failure before completion.
2. Verify another worker does not process it concurrently while the claim is
   valid.
3. Verify the documented stale-job retry/recovery path restores progress without
   duplicate side effects.

#### Interrupted Manual Migration

1. Identify whether the migration is wholly transactional or contains statements
   that cannot be wrapped in one transaction.
2. In hosted staging, simulate interruption or statement failure at the relevant
   boundary where practical.
3. Verify whether rollback is complete or which forward-fix/reconciliation SQL is
   required.
4. Put the result in deployment notes before applying to production.

#### Authorization Membership Change Race

1. In hosted staging, establish a user who can see or mutate data by a
   membership/role policy that consults another table.
2. In competing sessions, revoke/change that authorization while the protected
   operation executes or waits on relevant rows.
3. Verify the product's required revocation semantics; if immediate revocation
   cannot be guaranteed, document the exposure window and mitigation.
4. Record the chosen locking, session/token refresh, or application mediation
   approach before production use.

---

## 11. Deployment, Drift, and Operations

### 11.1 Pre-Deployment Checklist

- Migration SQL exists as a reviewed committed file and is not an edit to an
  already approved or applied migration.
- Its Git revision and SHA-256 checksum have been recorded before execution.
- Target environment alias and one designated deployment operator are explicit;
  the operator verifies the target project in Supabase immediately before
  pasting SQL.
- No other migration application is in progress for that environment.
- Backward compatibility with currently deployed code is assessed.
- Data volume and expected lock/runtime impact are assessed.
- Required backup or PITR capability is confirmed for destructive work.
- Security tests cover new or changed exposure.
- Secrets and service-role keys are not present in client code or migration logs.
- For Supabase, Storage consequences are assessed separately from database
  restore capability.

### 11.2 Deployment Authority

Under this protocol, an AI assistant must not execute hosted database
migrations, even if technically able or requested conversationally. It may
prepare the exact SQL and verification plan only. A human operator applies the
committed migration SQL in the Supabase SQL Editor and confirms the observed
result.

Changing this human-only authority boundary requires an explicit amendment to
this protocol, not merely a request to "run" or "deploy" one migration.

### 11.3 Post-Deployment Checklist

- Verify the exact reviewed local migration SQL was applied in the intended
  environment by matching its recorded Git revision and checksum.
- Run targeted inspection and behavior checks.
- Test anonymous and ordinary authenticated access through actual API/product
  paths where exposure changed; do not use a service-role request to prove
  ordinary-client denial.
- Re-run Supabase Security and Performance Advisors where applicable.
- Confirm critical user flows and watch errors/performance.
- Record the outcome in `supabase/deployments/<environment>/`, including any
  failed or partial application that may have changed live state.
- Record deviations, emergency fixes, or manual operations as new migrations
  and linked deployment/incident notes; do not leave unexplained drift.
- After successful verification, commit the deployment record. Under this
  workflow, the deployment record plus verification records applied state; the
  migration file alone records reviewed intended SQL.

### 11.4 Drift Response

If the hosted schema differs from the migration and deployment records:

1. Stop additional schema changes.
2. Determine whether drift came from an unapplied migration, an unrecorded
   manual edit, an interrupted execution, or the wrong target project.
3. Capture and review the actual schema difference.
4. Reconcile with a new reviewed committed migration, manually apply and verify
   it, then commit its deployment record.
5. Do not erase evidence by modifying old committed migration files.

### 11.5 Backups and External Assets

- PostgreSQL systems require regular backups and a restoration plan; a backup
  never counts until restoration has been tested.
- For Supabase, database backups do not include the underlying objects stored
  through the Storage API; restoring database metadata does not restore deleted
  Storage objects. Maintain an object-retention/backup plan if media loss is
  unacceptable.
- Use point-in-time recovery where the business recovery objective requires it
  and the platform/plan supports it.

### 11.6 Monitoring and Incident Readiness

- Monitor authentication/authorization errors, failed storage operations,
  database timeouts, deadlocks, slow critical queries, and failed background
  processing without logging secrets or unnecessary personal data.
- For security-relevant mutation paths, retain enough audit information to
  determine actor, target, action, and time when the product requires it.
- Maintain an emergency process for revoking exposed credentials, stopping a
  faulty writer, disabling unintended public access, and restoring or
  reconciling affected data.
- After an authorization incident, fix the underlying database/API rule and add
  a negative regression test; do not rely solely on UI changes.

### 11.7 AI Provenance, Privacy, and Human Accountability

- AI-generated SQL is a draft until a human accepts responsibility for its
  semantics, access effects, operational impact, and licensing/provenance.
- Do not send production exports, customer data, credentials, access tokens,
  private storage URLs, proprietary schema details, or incident artifacts to an
  AI service unless that disclosure is explicitly authorized and appropriately
  protected.
- Attribute nontrivial copied examples, algorithms, policy text, or external
  guidance when incorporating them into a published/commercial guide.
- Review generated SQL for accidental reproduction of insecure patterns,
  proprietary code, secrets, hidden identifiers, or business-sensitive naming
  before committing or publishing it.
- Treat source citations and research dates as part of the artifact lifecycle:
  revalidate platform/security claims before publishing new editions or
  performing consequential production changes.

---

## 12. AI Deliverables Template

When asked to design a new database feature, the AI should produce:

````md
## Understanding

Use case:
Actors and authorization rules:
Sensitive data and public exposure:
Consistency/concurrency invariant:
Assumptions/questions:

## Design

Tables/relationships:
Data types and constraint justification:
Integrity constraints:
RLS/grants/function/storage design:
Expected read/write query shapes:
Indexes justified by those query shapes:

## Migration

File: `supabase/migrations/YYYYMMDDHHMMSS_description.sql`
Committed revision:
SHA-256:

```sql
-- Complete reviewable migration SQL
```

## Verification

- Manual SQL Editor apply order and target-environment confirmation
- Deployment record file under `supabase/deployments/<environment>/`
- Pre-apply SQL inspection and expected result
- Post-apply SQL inspection and expected result
- Authorization positive/negative cases
- Concurrency/retry cases, where applicable
- Query-plan measurement for material query/index changes
- Application/type-check and schema/advisor checks

## Rollout And Risk

- Compatibility and data migration strategy
- Lock/downtime considerations
- Recovery or forward-fix plan
- Whether hosted staging rehearsal is recommended and why
- AI/data privacy or provenance issues reviewed
- Human operator action required; AI does not execute hosted migration SQL
- Reminder: commit reviewed migration SQL before application; after human
  application and successful verification, commit its deployment record
````

For a narrow change, use the same headings briefly. For a security- or
data-sensitive change, do not omit them.

---

## 13. Installing This Protocol in Another Project

### 13.1 Portable Core

The following sections apply broadly to PostgreSQL hosted on Supabase, Neon,
RDS, Cloud SQL, Railway, self-hosted infrastructure, or another PostgreSQL
provider:

- The Contract
- Requirements Discovery Protocol
- Data Classification and Threat Model
- PostgreSQL Schema Design Rules
- SQL Authoring Standard for AI
- Authorization and Privilege Model, excluding platform-specific role names
- Migration Engineering
- Verification, Deployment, Drift, and Operations
- AI provenance, privacy, and human accountability controls

### 13.2 Replace the Provider Overlay

For a non-Supabase database, replace:

- `auth.uid()` and Supabase JWT rules with the application's database/session
  authorization context.
- `anon`, `authenticated`, and `service_role` with actual database/API roles.
- Supabase Data API exposure rules with the server/API gateway architecture.
- Supabase Storage policy rules with the chosen object store and signed-URL
  authorization model.
- Supabase product/testing/advisor behavior with the project's API,
  authorization, storage, database test, and observability architecture.

Never copy Supabase RLS expressions into another architecture unless the identity
context exists and is trusted in exactly the required way.

### 13.3 Agent Entry-Point Instruction

An AI does not reliably read arbitrary documentation merely because it exists.
Supabase has reported the same failure class in AI-assisted projects: agents
missing RLS, exposing views without `security_invoker`, and guessing invalid CLI
operations. Keep critical protections in the mandatory guide itself, not only in
optional reference links.

Add a root agent instruction, for example:

```md
# Database Instructions

Before designing, editing, or reviewing any PostgreSQL, API data-access,
authorization, or storage-backed feature, read
`supabase/README-db-workflow.md` and follow its AI-assisted database protocol.
The AI prepares SQL and verification only; a human applies hosted migration SQL.
Reviewed migration SQL is committed before application; successful application is
recorded under `supabase/deployments/<environment>/`.
```

Keep the detailed protocol in one canonical file; point tools and contributors
to it rather than copying contradictory fragments across prompts.

### 13.4 Publication Packaging

This protocol is intended to be copied into a project and can also be
published as a reusable guide. Before publishing an installed project copy,
remove production identifiers, environment/project references, deployment
records, secrets, unresolved project-specific findings, and proprietary schema
material.

Publication claims must remain precise:

- This is a manual-SQL-Editor-first engineering protocol for projects that opt
  out of CLI-managed deployment and local container workflows; it is not a
  claim that manual execution provides identical operational guarantees without
  the controls in this document.
- It does not require or assume a paid Docker subscription. Supabase documents
  container-compatible local workflows and CLI deployment; those are excluded
  by workflow choice here, not declared invalid or inherently unsuitable.
- It is not a security certification, compliance framework implementation,
  backup guarantee, or professional legal/privacy assessment.
- Cite and comply with licenses for copied external material; prefer original
  examples and attributed links over reproducing third-party prose.
- Re-check version-sensitive Supabase and PostgreSQL claims at publication time
  and on a maintained release schedule.

### 13.5 Publication Release Checklist

Publish the protocol as a generic artifact, not as a dump of an installed
application repository:

1. Publish this guide or an exact reviewed export of it, without application
   migration files, snapshots, deployment records, project URLs, project
   references, credentials, or customer-specific schema details.
2. Include the empty bootstrap layout and the agent-instruction snippet so a
   new project can adopt the workflow before its first migration.
3. Choose and publish an explicit license and attribution policy for the guide
   and any reused material.
4. Confirm every linked official reference resolves and refresh the research
   date/version note.
5. Have an independent database/security reviewer evaluate material SQL,
   security, and operational claims before labelling the release production or
   commercial-grade.
6. Mark releases with a version and changelog; document corrections when
   upstream PostgreSQL or Supabase behavior changes.

---

## 14. Authoritative References

References were checked on 2026-05-26. Provider behavior can change; re-check
current platform documentation before publishing a derived commercial guide or
running production security changes.

### PostgreSQL

- [PostgreSQL 18: Data Types](https://www.postgresql.org/docs/current/datatype.html)
- [PostgreSQL 18: Privileges](https://www.postgresql.org/docs/current/ddl-priv.html)
- [PostgreSQL 18: Schemas and `search_path`](https://www.postgresql.org/docs/current/ddl-schemas.html)
- [PostgreSQL 18: Row Security Policies](https://www.postgresql.org/docs/current/ddl-rowsecurity.html)
- [PostgreSQL 18: Constraints](https://www.postgresql.org/docs/current/ddl-constraints.html)
- [PostgreSQL 18: `SELECT`](https://www.postgresql.org/docs/current/sql-select.html)
- [PostgreSQL 18: `INSERT` and `ON CONFLICT`](https://www.postgresql.org/docs/current/sql-insert.html)
- [PostgreSQL 18: Transactions](https://www.postgresql.org/docs/current/tutorial-transactions.html)
- [PostgreSQL 18: Client Connection Defaults and Timeouts](https://www.postgresql.org/docs/current/runtime-config-client.html)
- [PostgreSQL 18: Transaction Isolation](https://www.postgresql.org/docs/current/transaction-iso.html)
- [PostgreSQL 18: `ALTER TABLE`](https://www.postgresql.org/docs/current/sql-altertable.html)
- [PostgreSQL 18: `CREATE INDEX`](https://www.postgresql.org/docs/current/sql-createindex.html)
- [PostgreSQL 18: Multicolumn Indexes](https://www.postgresql.org/docs/current/indexes-multicolumn.html)
- [PostgreSQL 18: Partial Indexes](https://www.postgresql.org/docs/current/indexes-partial.html)
- [PostgreSQL 18: Index Types](https://www.postgresql.org/docs/current/indexes-types.html)
- [PostgreSQL 18: JSON Types and Indexing](https://www.postgresql.org/docs/current/datatype-json.html)
- [PostgreSQL 18: Full Text Search](https://www.postgresql.org/docs/current/textsearch.html)
- [PostgreSQL 18: Table Partitioning](https://www.postgresql.org/docs/current/ddl-partitioning.html)
- [PostgreSQL 18: `EXPLAIN`](https://www.postgresql.org/docs/current/sql-explain.html)
- [PostgreSQL 18: Explicit Locking](https://www.postgresql.org/docs/current/explicit-locking.html)
- [PostgreSQL 18: `CREATE FUNCTION` and safe `SECURITY DEFINER`](https://www.postgresql.org/docs/current/sql-createfunction.html)
- [PostgreSQL 18: `ALTER DEFAULT PRIVILEGES`](https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html)
- [PostgreSQL 18: Backup and Restore](https://www.postgresql.org/docs/current/backup.html)

### Supabase

- [Local Development and CLI Container Requirement](https://supabase.com/docs/guides/local-development)
- [CLI Reference](https://supabase.com/docs/reference/cli/introduction)
- [Database Migrations](https://supabase.com/docs/guides/deployment/database-migrations)
- [Securing Your Data](https://supabase.com/docs/guides/database/secure-data)
- [Securing Your API](https://supabase.com/docs/guides/api/securing-your-api)
- [Row Level Security](https://supabase.com/docs/guides/database/postgres/row-level-security)
- [Print Postgres Version](https://supabase.com/docs/guides/database/postgres/which-version-of-postgres)
- [User Management and Auth-Linked Tables](https://supabase.com/docs/guides/auth/managing-user-data)
- [Database Functions](https://supabase.com/docs/guides/database/functions)
- [Query Optimization](https://supabase.com/docs/guides/database/query-optimization)
- [Managing Indexes](https://supabase.com/docs/guides/database/postgres/indexes)
- [Agent Skills](https://supabase.com/docs/guides/ai-tools/ai-skills)
- [Storage Access Control](https://supabase.com/docs/guides/storage/security/access-control)
- [Storage Schema](https://supabase.com/docs/guides/storage/schema/design)
- [Realtime Authorization](https://supabase.com/docs/guides/realtime/authorization)
- [Performance and Security Advisors](https://supabase.com/docs/guides/database/database-advisors)
- [Testing Overview](https://supabase.com/docs/guides/local-development/testing/overview)
- [Generating TypeScript Types](https://supabase.com/docs/guides/api/rest/generating-types)
- [Database Backups](https://supabase.com/docs/guides/platform/backups)
- [AI Agents Know About Supabase. They Do Not Always Use It Right (Supabase, 2026-04-09)](https://supabase.com/blog/supabase-agent-skills)

### Application Security

- [OWASP Authorization Cheat Sheet](https://cheatsheetseries.owasp.org/cheatsheets/Authorization_Cheat_Sheet.html)
- [OWASP SQL Injection Prevention Cheat Sheet](https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html)
- [OWASP Top 10 for Large Language Model Applications](https://owasp.org/www-project-top-10-for-large-language-model-applications/)
- [NIST AI Risk Management Framework](https://www.nist.gov/itl/ai-risk-management-framework)
- [NIST SP 800-218 Secure Software Development Framework](https://csrc.nist.gov/pubs/sp/800/218/final)

### Evidence Map

| Protocol requirement | Primary basis |
| --- | --- |
| Git-tracked SQL and per-environment deployment records replace CLI-managed history in the manual-only deployment mode | Engineering compensating control; Supabase's CLI migration history is intentionally not used |
| Manual SQL Editor deployment uses commit-review, checksum, human application, verification, and recorded outcome | Engineering control acknowledging Supabase Database Migrations guidance and OWASP excessive-agency concerns |
| Local Supabase replay/testing requires a Docker-compatible runtime; hosted `db push` is a separate CLI operation, and neither is this protocol's deployment method | Supabase Local Development and CLI Reference |
| SQL types and constraints must encode business meaning and invariants | PostgreSQL Data Types and Constraints |
| Reads, mutations, upserts, and concurrent work require explicit semantics rather than generic CRUD generation | PostgreSQL `SELECT`, `INSERT`, Transactions, Transaction Isolation, and Explicit Locking |
| Index proposals must be derived from query shapes and measured, not guessed | PostgreSQL index documentation and `EXPLAIN`; Supabase Query Optimization and Managing Indexes |
| JSONB, full-text search, and partitioning are deliberate operator/workload choices, not default modeling shortcuts | PostgreSQL JSON Types, Full Text Search, and Table Partitioning |
| Grants and RLS must be designed together | Supabase Securing Your API: grants govern object reachability; RLS governs permitted rows |
| RLS is deny-by-default once enabled, but owners and `BYPASSRLS` roles can bypass | PostgreSQL Row Security Policies |
| Referential-integrity checks bypass RLS and authorization-table policies can race under concurrency | PostgreSQL Row Security Policies |
| API-facing schemas and privileged functions require explicit exposure control | Supabase Securing Your API and PostgreSQL Schemas |
| `SECURITY DEFINER` needs safe `search_path` and controlled `EXECUTE` | PostgreSQL `CREATE FUNCTION` and Supabase Database Functions |
| Function `PUBLIC` execution defaults must be revoked globally for the creator role or explicitly per function, not assumed removed by a per-schema revoke | PostgreSQL `ALTER DEFAULT PRIVILEGES` |
| Exposed views must not silently bypass underlying RLS | Supabase Row Level Security: use `security_invoker = true` for applicable PostgreSQL 15+ views |
| User-editable auth metadata must not determine authorization | Supabase Row Level Security helper-function guidance |
| Managed Auth objects and Auth-linked triggers require platform-specific care | Supabase User Management |
| Index policy columns and use request-stable JWT calls carefully | Supabase Row Level Security performance recommendations |
| `not valid`/validation and concurrent index operations require deployment planning | PostgreSQL `ALTER TABLE` and `CREATE INDEX` |
| Storage-managed records are not application DML targets and objects need recovery planning beyond database backup | Supabase Storage Schema and Database Backups |
| Realtime Broadcast/Presence and cached client authorization require product-path review | Supabase Realtime Authorization |
| Security-critical Supabase AI instructions should be mandatory context | Supabase engineering article on Agent Skills and recurring AI-generated RLS/view/CLI failures |
| AI-produced SQL and published guides require human accountability, disclosure control, and refreshed evidence | NIST AI RMF and OWASP LLM guidance |
| Authorization must deny by default and be tested for each request path | OWASP Authorization Cheat Sheet |
| Dynamic/user-controlled SQL values require parameterization or safe allowlisting | OWASP SQL Injection Prevention Cheat Sheet |