Architecture
Declarative schema workflow, generated migrations, RLS placement, seeds, and generated types.
CentraKit uses Supabase declarative schema management.
supabase/schemas/ is the source of truth for database structure. Do not create or write migration files directly during normal schema work.
supabase/schemas/**/*.sql.supabase db diff -f <migration_name>.For rollbacks, revert declarative schema files and generate a new forward migration instead of rewriting history.
supabase/schemas/ starts with 010_extensions.sql, 015_shared/ and 020_types/, then one folder per domain (tenant, identity, crm, catalog, work, people, commerce, conversations, ai, automation, platform, portal), and ends with 090_grants.sql.
Every file keeps a global number that sets when it runs, whichever folder it lives in:
| Range | Layer |
|---|---|
035–053 | Tables, plus RLS that only needs tables |
054–059 | Functions |
060–067 | Triggers |
070–079 | RLS policies that need helper functions |
080–089 | Storage policies |
supabase/config.toml lists every file in schema_paths, sorted by file name. When a new file depends on another file with the same number, give it the next free number. Otherwise the alphabetical order decides which one runs first. Define each function in one file only; with two definitions, the file that runs last silently wins. After any move or rename, pnpm supabase:diff must report no schema changes.
Every table must have RLS enabled. Policies that reference later-defined schema or helper functions belong in dedicated RLS files rather than table definition files.
Tenant-table policies use the init-plan form organization_id in (select public.org_ids_with_permission('key')), which Postgres evaluates once per statement (a hashed SubPlan) instead of calling a plpgsql helper for every candidate row. Chat rows use thread_id in (select public.accessible_chat_thread_ids()). Keep public.has_org_permission(org_id, key) for single-row checks inside SQL functions and public.authorize_scope(...) for the general case.
A few policies still call public.has_org_permission per row because the permission key or the organization comes from the row itself: workflow_lifecycles_select in supabase/schemas/automation/078_rls_workflows.sql (the key depends on the subject type), the realtime_messages_select policy in supabase/schemas/conversations/078_rls_inbox.sql (the organization is parsed from the Realtime topic), and the contextual artifact policies in supabase/schemas/ai/078_rls_ai_platform.sql (the key is the row's origin_permission). Several RLS files, including supabase/schemas/catalog/073_rls_assets.sql, keep a header comment that mentions has_org_permission even though their policies already use the init-plan form.
Seed data lives under supabase/seeds/. After schema changes, regenerate Supabase TypeScript types for both public and graphql_public schemas so generated code stays complete.
Last updated on