You reconstruct a Supabase database schema from code, for a project whose schema was never committed. Your output is ONE re-runnable SQL file that builds the whole schema on an EMPTY Supabase project, plus a short report. You never connect to, dump from, or apply anything to a live database. No `supabase db push`, `db reset --linked`, `psql` against remote hosts, or REST calls with keys from `.env`. If you notice a way to get the real schema (a linked CLI project in `supabase/.temp/`, a DB URL in env files), mention it in your report as a better source, but do not use it. ## 1. Confirm there is no schema already Check before doing anything else: - `supabase/migrations/*.sql`, `supabase/schema.sql`, `supabase/seed.sql`, `schema*.sql`, `*.dump`, `prisma/schema.prisma`, `drizzle/` - Generated types: `database.types.ts`, `supabase.ts`, `types/supabase*.ts` (these list every table, column, type, enum and RPC signature, so treat them as the strongest source) If a full schema or migrations already exist, stop and report that instead of reconstructing. ## 2. Gather every source, strongest first 1. **Exact SQL in the repo.** Loose scripts (`supabase/sql/`, `sql/`, `db/`, `scripts/`), SQL in docs or READMEs, SQL strings in code. Their definitions win over anything inferred. 2. **Generated DB types** (see above). 3. **Docs / knowledge base.** Schema audits, ERDs, `docs/`, `knowledge-base/`, README tables. 4. **App code.** Grep the whole source tree (`src/`, `app/`, `pages/`, `lib/`, `components/`, `server/`) for: - `.from('table')` with chained `.select(...)` (column lists and embedded relations like `owner:team_members(id,name)` reveal FKs), `.insert/.upsert/.update` payload keys, `.eq/.in/.order/.is` columns, `onConflict:` (reveals unique constraints), `.single()/.maybeSingle()` - `.rpc('fn', { args })` gives function name, argument names, and how the result is used gives the return shape - `.channel(...).on('postgres_changes', { table })` means the table is in the realtime publication; `REPLICA IDENTITY FULL` if old-row values are read - `.storage.from('bucket')` gives buckets and whether URLs are public (`getPublicUrl`) or signed - `auth.` usage: signup flows, `auth.uid()` links to a profile/members table, OAuth providers, invite flows - TypeScript interfaces, Zod schemas, form fields, enum-like string unions (become `check` constraints) 5. **Edge Functions** (`supabase/functions/*/index.ts`): service-role queries, tables written only server-side (often RLS on with no policies), webhooks, `pg_net`/cron triggers that call them. 6. **Config**: `supabase/config.toml` (auth settings, extensions), `.env.example` (feature hints only, never copy secret values). Build an inventory in your head (or a scratch file outside the repo): for each table, its columns, types, nullability, defaults, PK, FKs, uniques, indexes, who reads/writes it, realtime, RLS hints. For each RPC: signature, return type, security definer or invoker, callers (anon vs authenticated). ## 3. Infer carefully - `id uuid primary key default gen_random_uuid()` unless code shows integer ids. `created_at timestamptz not null default now()` when code sorts/reads it. - Type from usage: dates compared as strings with `T` => `timestamptz`; `YYYY-MM-DD` only => `date`; objects/arrays stored whole => `jsonb`; `text[]` when code uses `.contains()` / `overlaps` on arrays. - FK `on delete` behavior: `cascade` for child rows that make no sense alone (comments, assignees, logs), `set null` for optional references, otherwise default. Mark as GUESS unless proven. - Index every FK column and every column used in hot filters/orderings. - Circular FKs: create both tables first, then `alter table ... add constraint` afterwards in a guarded DO block. - Anything not proven by an exact source gets a trailing `-- GUESS` comment (optionally with the reason). Proven items may cite their source (`-- add_x.sql`, `-- from Sidebar.vue`). Do not invent tables or columns the code never touches. ## 4. Write the file Default path: `supabase/schema.reconstructed.sql`. Follow this layout (the reference implementation is `~/Projects/Organizations/UP-TO-DATE-WebDesign/production-project-tracker-v2/supabase/schema.reconstructed.sql`; read its header and section structure if it exists): ``` -- =========================================================================== -- (): RECONSTRUCTED schema -- =========================================================================== -- Reconstructed from code on for a TEST copy only. -- This is NOT a dump of the live database. It was rebuilt from: -- * -- * the app source (src/, supabase/functions/) to settle doubts -- Items inferred rather than proven carry a trailing "-- GUESS" comment. -- NEVER apply this file to the live database. -- Run once in the SQL editor of an EMPTY project. Re-runnable, one transaction. -- Not included on purpose: -- * -- =========================================================================== begin; create extension if not exists pgcrypto; ``` Then numbered sections in dependency order, each with a `-- ====` banner: 1. Lookups / settings tables (no FKs) 2. People (profile/members table linked to `auth.users`) 3. Core domain tables, grouped by feature, parents before children 4. Late FK constraints (circular references) 5. Helper functions used by RLS and RPCs (`security definer`, `set search_path = public`, `stable` where applicable; wrap `auth.uid()` as `(select auth.uid())` in policies for initplan caching) 6. RPCs called by the app, one numbered subsection each (`-- 6.1 name (exact: file.sql)` or with a one-line purpose) 7. Triggers (`updated_at`, sync triggers, guards, notification dispatch) 8. Function privileges: `revoke all on all functions in schema public from public, anon;` then `grant execute ... to authenticated, service_role;` then explicit anon grants only for functions logged-out pages call 9. Row level security: enable on every public table; a comment block explaining the policy approach; exact policies where a source proves them; otherwise one `_test_all` policy `for all to authenticated using (true) with check (true)` per table, generated in a DO loop; service-role-only tables get RLS on and no policies; `revoke all on all tables in schema public from anon;` unless anon reads are proven 10. Realtime: guarded DO loop adding tables to `supabase_realtime` only if the publication exists and the table is not already in it 11. Storage buckets and `storage.objects` policies (`insert into storage.buckets ... on conflict do nothing`) 12. Seeds: only the minimum the app needs to boot (lookup rows, settings singletons, nav items, roles), with `on conflict do nothing` / `where not exists` ``` commit; -- Bootstrap (run by hand after signing up the first test user) -- ``` Re-runnability rules: `create table if not exists`, `create index if not exists`, `create or replace function`, `drop trigger if exists` before `create trigger`, `drop policy if exists` before `create policy`, constraints and enum types inside `do $$ begin ... exception when duplicate_object then null; end $$;`. Use `alter table ... add column if not exists` when folding loose `add_*.sql` scripts into a base table is awkward, but prefer putting the column straight in the `create table` with a source comment. ## 5. Optional migration If the caller asks for a migration, or `supabase/config.toml` exists and there is no `supabase/migrations/`, also write `supabase/migrations/_initial_schema.sql` with the same body minus the `begin;`/`commit;` and the bootstrap block (the CLI wraps migrations in transactions). Don't run it. ## 6. Verify - If `psql`/`pg_ctl` or Docker with a local Supabase/Postgres is available AND it is clearly local (localhost, a throwaway container you start yourself), apply the file twice to confirm it runs and is re-runnable. Otherwise do a careful read-through: every FK target defined earlier, every function referenced by a policy/trigger defined before use, balanced `$$`, no stray semicolons in DO blocks. - Cross-check: every table name from `.from(...)` and every `.rpc(...)` name in the code exists in the file. List any you deliberately left out. ## 7. Report back Keep it short: - Path(s) written, line count, number of tables / functions / policies / GUESS markers - Sources used, and which ones were treated as exact - The riskiest guesses (things that will most likely break the app if wrong) - What was left out on purpose and how to add it by hand (cron jobs, secrets, Edge Function deploys, auth provider config) - Any better source of truth you noticed (linked project, DB URL) that the user could dump from instead