Supabase Schema Plan

The route

5 steps to Done

1. 01

   Model the data

   List entities, fields, and relationships before writing SQL.

   Preview prompt + verify gate ▾Hide ▴

   Design my Supabase data model. I will describe the app below. Produce: each table with columns (correct Postgres types, uuid primary keys with defaults, created_at timestamptz defaults), foreign key relationships with on-delete behavior, unique constraints, and which tables are user-owned (need a user_id referencing auth.users). Explain each relationship choice in one line. MY APP: [describe here]

   • ✓Every table has proper keys and types
   • ✓On-delete behavior is chosen deliberately
   • ✓User-owned tables have user_id to auth.users
2. 02

   Write the schema SQL

   Turn the model into runnable, ordered SQL.

   Preview prompt + verify gate ▾Hide ▴

   Write the complete schema SQL for the model: CREATE TABLE statements in dependency order; uuid defaults (gen_random_uuid()); timestamptz defaults; foreign keys with the chosen on-delete; unique constraints; and the indexes needed for the app's main query patterns (list each index with the query it serves). Make the script idempotent where possible (IF NOT EXISTS).

   • ✓Tables create in dependency order
   • ✓Defaults and constraints included
   • ✓Each index maps to a real query
3. 03

   Enable RLS with policies

   Security lives here: lock every table, then open exactly what is needed.

   Preview prompt + verify gate ▾Hide ▴

   Write the RLS layer: ALTER TABLE ... ENABLE ROW LEVEL SECURITY on every table; then for each - policies for select/insert/update/delete stating exactly who is allowed (owners via auth.uid() = user_id, public read where intended, admin patterns if needed). No USING(true) on private data. Include WITH CHECK on writes so users cannot insert rows for other users.

   • ✓Every table has RLS enabled
   • ✓Write policies include WITH CHECK
   • ✓No permissive true-policies on private data
4. 04

   Verify the policies

   Prove allowed works and denied fails - from real user contexts.

   Preview prompt + verify gate ▾Hide ▴

   Verify the RLS policies: as user A insert and read own rows (works); as user A attempt to read/update/delete user B's rows (must return nothing/fail); as anonymous, attempt private reads (must fail) and any intended public reads (work); attempt inserting a row with someone else's user_id (WITH CHECK must block). Record each attempt and result; fix and re-verify any policy that leaks.

   • ✓Cross-user access verifiably fails
   • ✓Spoofed-owner inserts are blocked
   • ✓Anonymous access matches intent
5. 05

   Wire and test app queries

   The schema is done when the app's real queries work through it.

   Preview prompt + verify gate ▾Hide ▴

   Wire the app to the schema and test the real queries: each main app operation (list mine, create, update, delete, any joins) executed through the client as a logged-in user; verify results respect RLS; check query performance on list endpoints (indexes being used); document the final schema (tables, policies, indexes) as the reference.

   • ✓Each app operation tested through the client
   • ✓Results are ownership-scoped
   • ✓Final schema documented