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.

Can I rebase migrations?

There is no safe command for rebasing migration history that has already been applied to a cloud database. Keep every applied migration immutable and make the correction with a new forward migration.

You may regenerate only a local pending tail when read-only inspection proves all of the following:

• The remote journal is an exact prefix of the local migration history.
• Every migration you will replace is absent from the remote journal in every target environment.
• No partial application, missing applied file, hash mismatch, mixed legacy history, or recovery-required result exists.

Back up or commit the project first. Remove only the confirmed pending migration directories, run gencow db:generate, run pnpm exec drizzle-kit check, review the generated SQL, and then run gencow db:check --prod before deploying. Never delete or modify the applied prefix.

For an ordinary correction, edit the schema and run gencow db:generate to create the forward migration. For a data backfill or DDL that Drizzle cannot express safely in one generated step, create a custom forward migration with pnpm exec drizzle-kit generate --custom --name=<repair-name>. Changes such as adding NOT NULL to populated data, narrowing a type, adding a uniqueness or foreign-key constraint, or dropping a column are usually safer as expand, backfill, and contract steps.

drizzle-kit up upgrades snapshot metadata; it does not rebase applied history. Do not use drizzle-kit pull --init, direct cloud drizzle-kit push, --ignore-conflicts, raw Production SQL, or manual journal edits as a Gencow migration recovery path.

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.

If you change a generated Better Auth schema or enable a durable Auth capability, the matching forward migration must be present in the same bundle. Gencow stops the request before database mutation when the bundle declares durable rate_limit storage without its creation migration. Keep the schema source and migration files together, review both, and create a fresh bundle after fixing the source.

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".

For durable Auth bundles, the platform also read-checks the tenant catalog after the migration decision: the rate_limit table shape, its unique key, and the runtime role's required DML privileges must be present before the candidate can activate. Legacy apps that do not declare this capability remain on their existing four-table compatibility contract.

--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.

Managed migration-store failures return PLATFORM_ACTION_REQUIRED with INSPECT_STATE when no automatic recovery job is active. They do not include a retry delay, because repeating the command alone cannot repair ownership, journal, or lineage state. A WAIT response is reserved for an actual platform recovery operation that can be followed to a terminal status. In either case, keep the immutable migration files unchanged and never edit the managed 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.

Current CLI releases also bind code generation and packaging to one versioned artifact identity. The platform independently derives that identity from the uploaded archive before any tenant database statement runs. Keep the generated files and migration bundle together and use one installed CLI artifact for generation and deploy. If the CLI detects mixed generation/package provenance, create a fresh bundle with the selected CLI instead of editing the manifest or disabling integrity checks.

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

Compatibility and integrity diagnostics are intentionally distinct:

• MIGRATION_BUNDLE_PROTOCOL_UNSUPPORTED or MIGRATION_BUNDLE_PLAN_VERSION_UNSUPPORTED: use a CLI version supported by the target Platform.
• MIGRATION_BUNDLE_INTEGRITY_MISMATCH: rebuild the bundle from trusted, unchanged source.
• PLATFORM_MIGRATION_ARTIFACT_CONTRACT_MISMATCH: preserve the correlation and use the requested state-inspection action; this is a Platform-owned contract incident, not a reason to edit migration SQL repeatedly.

All three classes stop before tenant database mutation. They do not authorize --force, manual journal edits, or raw Production SQL.

When the CLI asks you to update

An UPDATE_CLI or DEPLOY_CLI_UPGRADE_REQUIRED diagnostic means the target Platform cannot safely accept this CLI's deploy or migration contract. No tenant database change has been made and the serving release is preserved. Run the exact command shown by the CLI (for example, bunx gencow@latest db:push) and then rerun the same command. Node.js users can use npx gencow@latest db:push. Do not use --force, edit the migration journal, or retry with the old CLI.

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