Design a Supabase Database Schema

Problem this prompt solves

AI SaaS MVPs often start with a single “documents” table and no plan for usage events, job status, or ownership. Later you bolt on columns, skip indexes, and leave RLS policies that allow authenticated users to read everything. You need a schema that matches the product workflow — including async AI jobs — before the UI hard-codes the wrong shape.

When to use it

• You know the primary objects in your MVP (e.g. workspace, project, generation) and need SQL + RLS before building screens.
• You already have UI mock data and need to replace it with a real Supabase model.
• You need to log AI runs (tokens, model, status) without mixing them into the user content table.
• You are about to add team/shared access and want a clear ownership column strategy even if v1 is single-user.

Design and implement a Supabase (Postgres) schema for this AI SaaS MVP. Work from the existing product code and README; if the domain objects are unclear, ask up to five clarifying questions, then proceed.

Design principles:
- Model the smallest set of tables that support the core workflow end to end
- Every user-owned row has a clear owner (user_id UUID references auth.users)
- Prefer explicit status enums for AI work (e.g. pending | running | succeeded | failed)
- Separate “user content” from “AI run / usage” records
- Enable RLS on all business tables; default deny; add policies for select/insert/update/delete as needed
- Add indexes for foreign keys and common list queries (owner + created_at desc)
- Use timestamptz for timestamps; include created_at/updated_at with a trigger or application updates
- Avoid premature multi-tenant orgs unless the product already requires them; if single-user ownership is enough, document how you would add org_id later

Typical MVP tables (adapt names to the product):
1) profiles — 1:1 with auth.users
2) primary resource (projects/documents/etc.) — owned by user
3) ai_runs or generations — belongs to the resource; stores model, prompt summary or hash, output ref, token usage, latency_ms, error
4) optional: api_usage_daily rollup for cost controls later

Deliverables:
- SQL migration(s) under supabase/migrations (or a single clearly ordered SQL file if the repo has no Supabase CLI yet)
- RLS policies written in SQL, not hand-waved
- TypeScript types or generated type guidance matching the schema
- Brief ER diagram in Mermaid in the PR/README
- Notes on what not to store (raw secrets, full prompts if privacy-sensitive) and how to handle soft deletes if relevant

Constraints:
- Do not disable RLS
- Do not use service role in client code to “make inserts work”
- Keep migrations idempotent where practical and safe to run on a fresh project

Expected result

Versioned SQL that creates the MVP tables, indexes, and RLS policies; TypeScript-facing types; a Mermaid ER sketch; and clear ownership rules so the app can CRUD safely with the anon key + user session.

Implementation notes

• Name enums and status values the UI will actually filter on — renaming later breaks dashboards and prompts.
• Store token usage on ai_runs from day one even if you do not bill yet; you will need it for cost control.
• If outputs are large, store them in Storage or as JSONB with a size budget — do not bloating list queries with huge columns.
• Use on delete cascade carefully: cascading user delete is good for profiles; cascading product data may be right for MVP privacy.
• Write policies for update that prevent changing user_id / ownership columns.
• Run the migration on a fresh Supabase project once to catch ordering and trigger errors before tying UI to it.

Testing checklist

• Apply migrations to a clean Supabase project with zero errors.
• As user A, insert a primary resource and an ai_run; confirm select returns them.
• As user B, confirm select/update/delete on user A’s rows all fail.
• Confirm insert fails when user_id is set to another user’s UUID under RLS.
• Verify list query by owner + created_at uses the index (explain analyze in SQL editor for sanity).
• Delete a user in Auth (test project) and confirm expected cascade/cleanup behavior.
• Generate or update TypeScript types and ensure the app compiles against new columns.

Common mistakes

• One wide table for content + AI metadata + billing flags — queries and RLS become unreadable.
• RLS policies using auth.uid() incorrectly (or not at all) so authenticated role can read all rows.
• Missing updated_at discipline — you cannot debug “stale AI output” races.
• Indexing only primary keys while the app lists by user_id and status.
• Storing API keys or full system prompts in Postgres plaintext “for convenience.”
• Designing full multi-org RBAC on day one and never shipping the MVP.