SQL Sources & Installation

Edit database definitions in database/schema/ and native integrations in database/overlays/. The ordered database/manifest.json assembles them into the two complete SQL batches consumed in memory by Automatic Setup. Init, bootstrap, audits and tests do not require generated files. Export files only when you need to install manually; never edit those derived outputs directly.

Sources and outputs

SQL remains the DDL authority. Prisma is the derived client contract; it does not own migrations, functions, triggers, RLS or grants. Do not use Prisma Migrate or prisma db push for this workflow.

Find the right domain

DB and Auth overlays are separate because the providers are selected independently. Scheduler and Storage SQL live under database/overlays/integrations/. The repository's database/README.md is the detailed ownership and authoring index.

For an LLM or human reviewer, start with that index, search the affected domain, then read its callers, SQL tests and neighboring manifest entries. Historical redefinitions still exist: a search match may be superseded later in the manifest. Directory placement alone does not prove that a block is provider-independent.

Make a SQL change

Use the Node and pnpm versions pinned by the repository. From its root:

1. Find the owning source and understand its dependencies, permissions and callers.
2. Edit the owning domain source. Include constraints, indexes, owners, policies, grants/revokes, functions, triggers and required normalization. These sources and the ordered manifest are the sole DDL authority; the separate migration history has been removed for fresh installations.
3. If adding a source, insert it once at the correct position in the manifest. Preserve the explicit order; filenames and folders do not determine execution.
4. Review the source diff and validate assembly:

pnpm run db:schema:check

Commit the sources and manifest changes together. Check assembles SQL and its map in memory without writing files or connecting to a database. Inputs must be UTF-8 without a BOM and end with a newline; CRLF is normalized to LF.

Run the applicable SQL, RLS, type and repository checks. Changes to installation behavior require the fresh SQL installation proof. The assembly check validates inputs and deterministic composition; SQL correctness and provider certification require their separate proofs.

The existing fresh bootstrap also runs a catalog comparator before runtime provisioning. It fingerprints definitions and permissions in the project's public and private schemas, excluding managed schemas and extension-owned objects. It checks that fixture changes are detected, that recreating an object with a new OID preserves its fingerprint, and that rollback restores the complete catalog. Definitions and literal values are hashed in PostgreSQL; application rows and credentials are not collected. This is preparation for portable schema verification. It does not prove final composition parity, Prisma introspection parity, seed/data equivalence or execution effects. Catalogued dependencies also miss some PL/pgSQL and dynamic SQL references, which still need the dependency diagnostic and domain tests.

Install a fresh database

With init: pnpm run init validates the manifest and sources, then assembles SQL and its map in memory before connecting for schema installation. It runs core before CMS and keeps the schema acceptance and runtime-credential checks. Missing or invalid sources stop installation. Existing exports are ignored. If a previous run committed both atomic batches but failed before operational credentials were enabled, init can resume at credential provisioning only after the complete installed composition and every passwordless NOLOGIN role pass the same closed attestation. It never replays either SQL batch in that case. Partial, mismatched, populated or already-credentialed databases are rejected by default.

If schema installation succeeds but credential provisioning fails, init reports the provisioning substep and a safe PostgreSQL SQLSTATE when available, without printing credentials or raw database errors. A failure before activation commits can resume through the existing schema and inert-role checks. Do not reset just because credential provisioning failed.

Neon credential activation: Neon does not support pre-hashed password input for SQL role management. Init sends generated passwords over the encrypted owner connection, requests SCRAM storage and retains the server-generated verifiers only in memory for guarded rotation and cleanup. Supabase keeps its existing precomputed-SCRAM path. Avoid external SQL statement tracing during provisioning; init never prints credential statements or raw driver errors. See Neon's role documentation.

Failure after credentials were saved: settings and durable-job setup runs in one transaction with bounded statement and lock timeouts. If it fails, retain .env.local; the schema and operational logins are already installed. The wizard reports the configuration substep and SQLSTATE. Its uncredentialed resume path does not apply here: inspect the target before choosing a reviewed recovery or an explicitly confirmed reset/reinstall. Editing scheduler SQL locally does not update a function already installed in the database.

Neon native Auth/Data API: init preserves the managed pg_session_jwt Auth helpers and native API roles when their catalogue and role boundaries pass verification. Those roles are fenced out of the application's business schemas; the selected Better Auth or Supabase Auth adapter remains authoritative. Their presence alone is not a Supabase dependency and does not require deleting auth. Init also removes the installer's native API default grants in application schemas before creating objects, while preserving other owners' defaults and managed extension objects. A remaining native table/column grant is reported specifically; it is not an instruction to give the owner broader privileges.

For a Supabase or Neon target, the wizard can recover automatically after a DATABASE_COMPOSITION_DATABASE_NOT_EMPTY result. This path is disabled by default and requires the operator to type RESET exactly after the prompt names the detected project or branch target. It runs the provider-specific repository batch (database/reset/supabase-fresh-reset.sql or database/reset/neon-fresh-reset.sql) through the direct administrator PostgreSQL connection; it never invokes supabase db reset. Each reset is atomic, removes only the selected composition's roles, attests the empty state, and permits one fresh installation attempt. Supabase unschedules every cron.job, recreates public, drops private/authn, and deletes every auth.users row.

Neon uses an application-only reset: it retains public, managed Auth helpers and pg_session_jwt, all extensions, native roles, unrelated cron jobs, external Auth users and Object Storage objects. Non-extension application objects owned by the installer or attested application roles in public, private and authn are deleted, including their data. Only exact application cron commands for the current database and owner are unscheduled. A complete valid init-managed login set is preflighted and preserved for reactivation after the new schema passes attestation; invalid credentials stop the reset before deletion.

Protected ownership, dependencies outside that scope or unrecognized objects cause the entire reset to roll back. The wizard explains these blockers using credential-safe diagnostics. Review the dependency or use a separate empty database; do not delete managed Auth or extensions to bypass a refusal.

Local QA: use pnpm run qa:local:start or pnpm run qa:local:reset. The guarded runner validates the loopback target, installs the complete core and CMS batches, verifies them, then loads supabase/seed.sql. Start preserves an already complete stack; reset explicitly rebuilds the owned disposable stack. A partial install or failed seed requires reset. Direct Supabase CLI reset does not install application SQL because its migration and automatic seed loaders are disabled. There is no pending-migration or installed-version upgrade command.

Manual SQL diagnostics: check any fresh composition in memory, including its scheduler and Storage topology:

node scripts/build-database-schema.mjs --check --composition neon:better_auth --scheduler postgres_pg_cron --storage disabled

The generated manual export paths represent only the manifest's default composition. To export and verify that default locally:

pnpm run db:schema:build
pnpm run db:schema:check-export

On a new empty default-composition project, execute the complete supabase/schema.sql, then the complete supabase/cms-schema.sql, using the operator SQL environment. This performs the SQL step only; complete the configuration, credential provisioning and acceptance checks described in Automatic Setup before starting the application. Operator credentials stay outside the web/worker runtime.

Source fragments are not standalone scripts. Do not execute them individually, add per-file commits, or wrap the assembled files in an extra transaction. Existing explicit and implicit transactions, temporary grants and role changes depend on the complete query order.

Diagnose assembly and installer errors

For an error reported against line 100 of the generated core artifact:

pnpm run db:schema:locate supabase/schema.sql 100

Use supabase/cms-schema.sql for CMS errors. These are logical batch names; the command computes the map in memory and prints the owning source path and line. Use the same checkout that produced the installed SQL. SQL diagnostics expressed as a character position or a function-body line need to be interpreted in that context before using the map. Fix the source and rerun the affected verification. Re-export only for manual installation.

Scope and certification

The manifest assembles all four Supabase/Neon plus Supabase Auth/Better Auth fresh compositions, with topology overrides that can exclude native scheduler or Storage overlays. Runtime validates supported providers, isolated credentials and actual SQL permissions independently of release evidence. Release approval remains blocked until the complete local_only certification evidence is accepted by pnpm run qa:release. Init and Docker builds do not certify a release.

Future consolidation must prove equivalent database objects, permissions and reference data before removing superseded definitions. The product targets new installations: do not replay full schema artifacts as an upgrade of a populated deployment. Supporting installed-version upgrades requires a separate migration and verification contract.