First Feature Walkthrough

Before SQL, establish who performs the operation, which data is public or private, who owns it, whether deletion is recoverable, what concurrent requests could race, and whether data already exists. If a wrong assumption could expose or destroy data, the AI must pause for an answer.

We need private user-owned documents with optional public publishing.

Read `AGENTS.md` and `supabase/README-db-workflow.md` first. Before writing
SQL, establish the actors, ownership, public visibility, deletion behavior,
sensitive fields, expected reads/writes, and whether existing production data
exists.

Then prepare a new migration file, RLS/grant design, pre-apply checks,
post-apply verification SQL, API-path authorization tests, concurrency/risk
analysis, and manual Supabase SQL Editor instructions.

Do not apply hosted SQL. I will apply reviewed SQL manually.

The following demonstrates the shape of an additive migration after the requirement has been confirmed. It deliberately creates constraints, an ownership index, explicit grants, RLS, and operation-specific policies together.

-- Purpose: Add user-owned private documents.
-- Authorization: Authenticated owners can read and write their own rows only.
-- Data impact: Additive; no existing rows modified.
-- Lock/availability: Creates new objects only.
-- Rollback: Drop only before application data depends on this table.
begin;

create table public.documents (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid not null references auth.users(id) on delete cascade,
  title text not null check (char_length(btrim(title)) between 1 and 160),
  body text not null default '',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index documents_owner_created_idx
on public.documents (owner_id, created_at desc);

alter table public.documents enable row level security;
revoke all privileges on table public.documents from anon, authenticated;
grant select, insert, update, delete on table public.documents to authenticated;

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 policy "documents_delete_own"
on public.documents for delete to authenticated
using ((select auth.uid()) = owner_id);

commit;

This is an instructional example, not SQL to paste into every application. Public publishing, staff administration, collaborative editing, retention, and Storage uploads all change the authorization model.

• Can you state the allowed and denied actor behavior in plain language?
• Do constraints encode required facts, relationships, state, and deletion behavior?
• Do grants permit only required operations, with RLS limiting permitted rows?
• Does each policy include the correct using and with check behavior?
• Is every proposed index tied to an expected query or policy predicate?
• Are lock, failure, rollback or forward-fix, and verification notes included?