Database Migrations

Generate, check, and apply versioned Drizzle migrations with clear responsibility boundaries

Gencow Cloud uses versioned Drizzle migrations. Drizzle generates and executes the migration artifact, PostgreSQL decides whether the SQL is valid, and Gencow adds the managed-cloud controls needed to apply the exact plan to the correct app database.

The Responsibility Model

In short:

schema.ts
→ Drizzle Kit generates versioned SQL
→ Gencow verifies the target and immutable bundle
→ Drizzle ORM executes the pending plan
→ PostgreSQL determines the database result
→ Gencow verifies the journal outcome

Gencow does not convert migrations into a separate SQL dialect. Managed-cloud policy can still reject a request that targets the wrong app, changes protected platform objects, uses an unsupported protocol, or has an inconsistent migration history.

Ordinary tenant DROP, ALTER, and TRUNCATE operations are not rejected merely because they can remove data or take locks. Gencow reports non-blocking data-loss, lock, and rollout-compatibility warnings; your app team decides whether to proceed and owns migration review, recovery points, business impact, and timing.

Sources of Truth

Different files answer different questions:

Do not edit an applied migration or manually change the migration journal to make these sources appear consistent. Add a new migration for an application change. When remote state needs inspection, preserve the bundle and use the returned status or read-only inspection action first; support escalation is reserved for a confirmed platform security boundary or a platform reconciliation that cannot complete automatically.

Recommended Workflow

Projects with no database schema source do not need placeholder migrations. Cloud deploy skips migration generation and tenant database access for that case. When a schema source exists but its canonical generation produces no changes, Gencow treats it as a verified no-op. This is distinct from a failed generator or a missing initial v1 history, both of which stop before database mutation.

1. Change the schema

Edit your configured Drizzle schema files, usually gencow/schema.ts and gencow/schema-auth.ts.

2. Generate and review SQL

gencow db:generate

Drizzle Kit writes a versioned migration under gencow/migrations/. Review and commit the generated files with the schema change.

3. Check the cloud plan

gencow db:check        # DEV
gencow db:check --prod # Production

# When a diagnostic requests INSPECT_STATE
gencow db:check --inspect-state
gencow db:check --prod --inspect-state

db:check verifies the committed bundle, cloud target, migration history, pending plan, and current platform policy without running Drizzle Kit, writing project files, applying DDL, or changing the migration journal. Successful JSON output includes projectState: "UNCHANGED" and databaseState: "UNCHANGED".

--inspect-state uses the dedicated authenticated schema-inspection endpoint with the same immutable bundle. It is also read-only and returns a classified READY or BLOCKED outcome, the current reason code, and the next action. It does not ask you to query PostgreSQL catalogs or edit the migration journal manually.

For a new app whose tenant database does not exist yet, db:check validates the immutable bundle against an empty journal and returns databaseBootstrapRequired: true. This is a passing read-only bootstrap plan; db:check still does not create the database. A later authorized deploy or db:push creates it.

If the database exists but its managed Drizzle store is in an explicitly repairable bootstrap or grant-transition state, db:check remains read-only and returns migrationStorePreparationRequired: true. This includes an absent journal only when the Drizzle schema is platform-owned, the DDL role cannot create arbitrary objects there, no managed-store drift is known, and no prior applied lineage exists. The later authorized mutation path performs the state-aware reconciliation and verifies the ready postcondition. Unknown ownership, incompatible journal shape, known store drift, weakened journal guards, or any prior applied lineage remain blocked.

db:check is a read-only preflight, not a temporary-database execution rehearsal. A passing result does not prove application behavior, Production lock duration, or compatibility with live data. Test high-impact changes against representative PostgreSQL data before Production.

4. Apply the same immutable plan

gencow db:push --existing-bundle        # DEV
gencow db:push --prod --existing-bundle # Production, confirmation required

--existing-bundle uploads the same committed migration source without running a generator again. The platform verifies the bundle again, applies only pending migrations, and checks the exact journal result before reporting success. For a Production plan with pending migrations, Gencow attempts to create a fresh restore point after the immutable pending plan is confirmed and before it authorizes the journal or runs SQL. If automatic backup creation is unavailable, the CLI emits MIGRATION_BACKUP_NOT_CREATED as a non-blocking warning; verify your own recovery point before proceeding. An exact applied no-op does not create another migration backup.

Before db:push or a backend deploy sends a mutation request, the CLI states that pending SQL review and recovery readiness are user-owned and that automatic backups are best-effort. This notice is not a new approval flag: running the command is the decision to proceed. SQL risk warnings come from the exact journal-aware pending plan, not from already-applied migration files that remain in the archive.

gencow db:push without --existing-bundle keeps the one-step generate-and-push workflow for compatibility. Use the explicit existing-bundle form when applying a plan that already passed db:check.

The manifest keeps generator name and version only as hash-bound audit metadata. The platform does not reject a bundle because of its Drizzle Kit version; it validates the bundle protocol, ordered SQL hashes, ledger history, protected platform boundaries, and transaction/postcondition controls.

Constraint and Foreign-Key Changes

Foreign keys, indexes, enums, constraints, and relations use standard Drizzle and PostgreSQL behavior. For example, changing a foreign key action can require PostgreSQL's normal DROP CONSTRAINT followed by ADD CONSTRAINT pattern.

The responsibilities remain the same:

• Drizzle generates or preserves the versioned SQL.
• Your team reviews the application behavior change, such as whether deleting a parent row now cascades, sets a value to null, or fails.
• PostgreSQL validates and executes the constraint definition.
• Gencow verifies that the immutable plan belongs to the correct app and does not cross platform boundaries.

Do not rewrite valid migration SQL merely to bypass a platform diagnostic. Preserve the migration name/hash and follow the returned self-service action when a standard Drizzle/PostgreSQL migration is blocked by the managed-cloud compatibility layer. Retain correlation information for escalation only if status inspection or platform reconciliation cannot resolve it.

Failure and Retry Rules

Preflight service failures, a missing fresh database, an unavailable automatic backup, or lock/connection cleanup failure after a proven result do not by themselves create RECOVERY_REQUIRED. If tenant postconditions prove APPLIED while the control-plane receipt write is temporarily unavailable, the response preserves APPLIED with APPLIED_RESULT_PERSISTENCE_DEFERRED; do not rerun the migration to repair that observation.

Never use --force, raw Production SQL, or manual journal edits to bypass a migration failure.

Protected Platform Objects

Gencow manages _system_* and _gencow_* objects. Keep them out of your Drizzle schema and migration history:

import { defineConfig } from "drizzle-kit";

export default defineConfig({
    dialect: "postgresql",
    schema: ["./gencow/schema.ts", "./gencow/schema-auth.ts"],
    out: "./gencow/migrations",
    tablesFilter: ["!_system_*", "!_gencow_*"],
});

Do not declare, alter, truncate, or drop these platform-owned objects from app migrations.

Deploy and Rollback

gencow dev and gencow deploy generate migrations during packaging when a schema source exists and include gencow/migrations/ in the bundle. A schema import, config, or generator failure stops before upload; it is not downgraded to a warning and existing migration files do not mask it. Run gencow doctor to inspect the observed schema source, generator version, migration format, history count, and decision preview. The final empty/history-required decision still belongs to canonical generation, not to SQL text classification in Doctor.

A code rollback does not roll back the database. Keep old and new application versions compatible with the applied schema when you need safe code rollback.

Local direct push is different

gencow db:push --local uses a direct local desired-state push for development convenience. It does not use the managed-cloud owner, immutable bundle, pending journal, or source-promotion contract described above. Do not use local direct-push behavior to infer how a DEV or Production cloud migration will run.

Related Guides

• Schema — Drizzle tables, relations, and RLS
• Deployment — Cloud deploy and rollback
• CLI Reference — Command flags and structured diagnostics