Supabase Schema Plan
Design a correct Supabase schema: tables, relationships, RLS policies, and verification queries.
The route
5 steps to Done
- 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
- 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
- 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
- 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
- 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