OnRamp Docs

Appearance

Migrations — Expand-and-Contract ​

OnRamp runs zero-downtime deploys against a live, multi-tenant Postgres. Schema changes that lock tables or break existing queries will cause 500s during the deploy window. Expand-and-contract is the pattern that prevents this.

Reference: Prisma expand-and-contract guide

The four phases ​

PhaseWhat happensOnRamp example
ExpandAdd the new structure alongside the old. Both old and new app code work.Add nullable attempt_account_name_match column to or_ramps with server_default.
Migrate dataBackfill existing rows; run in batches to avoid lock contention.UPDATE or_ramps SET attempt_account_name_match = true WHERE ... in a follow-on migration.
ContractRemove the old structure once no app code references it. Separate PR, separate deploy — see Contract phase.
CleanupRemove any dual-write compatibility shims in application code.Delete fallback logic that read from both old and new column.

Anti-patterns Squawk catches ​

Click for the full rule-by-rule table
RuleWhy it breaks zero-downtimeExpand-and-contract alternative
ban-drop-columnExisting queries/ORMs break mid-deploy if they reference the column.Ship code removal first; drop column in a later contract-phase migration.
ban-drop-tableSame as above at table scope.Remove all references from app code before dropping.
ban-drop-not-nullDropping NOT NULL on a column used by active queries can change query plans and behavior unexpectedly.Audit all callers first.
renaming-columnAny in-flight query using the old name fails immediately.Expand: add new column, dual-write, migrate, contract.
renaming-tableSame as renaming-column at table scope.Expand: new table, dual-write, contract.
changing-column-typePostgres may rewrite the entire table, holding an ACCESS EXCLUSIVE lock.Add new typed column, backfill, update app, drop old.
adding-required-fieldNOT NULL without a default triggers a full table rewrite (pre-PG 11) or blocks reads on large tables.Nullable first, backfill, add constraint with NOT VALID, validate separately.
constraint-missing-not-validValidating a constraint inline locks the table.ADD CONSTRAINT ... NOT VALID, then VALIDATE CONSTRAINT in a separate transaction.
require-concurrent-index-creationCREATE INDEX without CONCURRENTLY holds a write-blocking lock for the duration.Always use CREATE INDEX CONCURRENTLY. Requires op.execute() outside a transaction block.
require-concurrent-index-deletionSame issue for DROP INDEX.DROP INDEX CONCURRENTLY.
disallowed-unique-constraintADD CONSTRAINT ... UNIQUE blocks writes during build.CREATE UNIQUE INDEX CONCURRENTLY, then ADD CONSTRAINT ... USING INDEX.
transaction-nestingWrapping a concurrent-index operation in a transaction negates the concurrency.Run concurrent DDL outside BEGIN/COMMIT blocks.
prefer-robust-stmtsMigrations that fail halfway leave the DB in a partial state.Use IF EXISTS / IF NOT EXISTS guards on all DDL statements.

Full rule documentation: https://squawkhq.com/docs/

Running locally ​

bash
make lint.migrations           # offline, no DB required — same as CI
make lint.migrations.local     # also pings 127.0.0.1:5432/onramp_local for connectivity sanity

Both render new migrations to SQL via uv run flask db upgrade <down>:<new> --sql, run the repo-specific checks (scripts/check-migration-*.ts — autocommit idempotency, non-superuser role safety, data-state preconditions), and pipe to bun run squawk. Neither needs schema knowledge from a live DB.

Contract phase — how to safely drop ​

TL;DR. Dropping is the dangerous half. Use two releases minimum: release N stops the app from reading/writing the column, release N+1 drops it. Squawk's ban-drop-column blocks accidents — the workflow below shows how to drop on purpose. Time waits are a floor, not a gate; the real proof is "zero traffic on the old path."

IMPORTANT

On prod/demo/gdpr-prod, migrations now run BEFORE the code that goes with them.

The release pipeline's migrate gate applies migrations to the real DB, and only then does duplo-deploy roll the app. So for the whole length of that rollout, every running pod is the OLD code against the NEW schema — not the brief per-pod overlap it used to be when each pod migrated itself at boot.

The two-release rule above is therefore not a style preference on these envs; it is what keeps the rollout window survivable. A contract-phase DROP COLUMN shipped in the same release as the code that stopped reading the column used to cost a few seconds of errors on one pod at a time. It now takes the env down for the length of the rollout, because the column disappears while every pod is still the old code.

Nothing enforces this: bun run lint.migrations checks autocommit-block idempotency, role privileges, data-state preconditions and Squawk — none of which can tell that a drop and its code change are riding the same release. That separation is on the author.

Quick start — the contract migration template ​

python
"""contract: drop or_ramps.attempt_account_name_match

Revision ID: ...
Revises: ...
Create Date: 2026-06-01 09:00:00.000000

Contract phase. Parent expand PR: #8543 (ONRAMP-4810).
Column removed from app code + ORM in PR #8612, deployed 2026-05-20.
Cooldown: 12 days; zero references in app/, onramp-agents/, app/ui-*.
"""

import sqlalchemy as sa
from alembic import op

revision = "..."
down_revision = "..."
branch_labels = None
depends_on = None


def upgrade():
    op.execute(
        "-- squawk-ignore ban-drop-column,require-timeout-settings,prefer-robust-stmts"
    )
    op.drop_column("or_ramps", "attempt_account_name_match")


def downgrade():
    # Re-add as nullable; backfill is lost.
    op.add_column(
        "or_ramps",
        sa.Column("attempt_account_name_match", sa.Boolean(), nullable=True),
    )

The five gates ​

A contract PR must satisfy all five before merge:

  • [ ] 1. Code path removed in a prior, already-deployed release. No reads, no writes, no analytics, no agent SQL-tool exposure.
  • [ ] 2. ORM ignores the column in that prior release. SQLAlchemy mapped_column deleted from *_models.py, or excluded via __mapper_args__. Done in release N — not the same PR as the drop.
  • [ ] 3. Cooldown observed. At minimum, the expand/migrate PR has been merged + deployed to prod ≥ 14 days. Preferably gated on a feature flag at 100% with zero error events.
  • [ ] 4. Grep evidence in PR description. rg "<column_name>" --type py --type vue --type ts onramp-agents/ app/ playwright/ returns zero hits outside migrations/.
  • [ ] 5. Second reviewer — platform / DBA owner, not the original author.

Each gate is independent. Skipping any one of them is how production incidents happen.

Why two releases? Why can't I drop in the same PR that removed the code?

Rolling deploys mean old pods keep serving traffic for minutes-to-hours after new pods come up. If release N+0 both removes the ORM mapping AND drops the column, the old pod's in-flight transactions still reference the column → 500s. Same applies to async workers, scheduled jobs, replication consumers, agent SQL-tool queries cached for a session.

Strong Migrations (Rails ecosystem) puts it this way: "Ignoring and dropping columns should not occur simultaneously in the same release — first ignore the column (release M), then drop it in the next release (release M+1)." Same logic applies to SQLAlchemy. (Strong Migrations docs)

Squawk's own ban-drop-column guidance: "Update your application code to no longer read or write the column," then "delete the column once queries no longer select or modify it" — explicitly two steps. (Squawk ban-drop-column)

The ignore-then-drop pattern in SQLAlchemy

Release N (expand-PR follow-up, before contract):

  1. Delete the mapped_column declaration entirely from app/api/<domain>/or_models.py. Or, if the column is referenced elsewhere transitively, mark it deferred=True and stop reading it.
  2. Remove every Model.column_name reference in services, helpers, controllers, DTOs, mappers.
  3. Remove every raw-SQL reference (search app/, onramp-agents/, migrations/ not-yet-applied).
  4. Ship + deploy to prod. Verify error rates clean for ≥ cooldown window.

Release N+1 (contract PR — the one in this template):

  • Migration file only. No app code changes. Drop the column.

Release N+2 (optional cleanup):

  • Remove any deferred markers or compat shims that bridged the gap.
Preferred: feature flag as the cooldown gate

Calendar waits are a heuristic. The real signal is zero traffic on the old path. A feature flag gives you that proof:

  1. Expand-phase PR introduces a flag (e.g., ramps.use-new-account-match-flow). Default off.
  2. Code reads/writes both columns when the flag is off; writes only the new column when on.
  3. Roll the flag to 1% → 10% → 100% over your normal feature-flag cadence.
  4. Observe error rates + agent-SQL-tool query patterns at 100% for 7 days.
  5. Open the contract PR. The "cooldown" is now provable: 7 days of 100%-flag traffic with no errors.

This pattern is the 2026 industry default — beats time-based cooldowns because it surfaces forgotten readers during rollout instead of after the drop. (Featureflow guide)

Extra safety: the _deprecated_ rename trick

For high-value columns (anything in or_projects, or_tasks, anything customer-facing), insert an extra ceremonial step between release N and release N+1:

  • Release N+0.5: rename column_name → _deprecated_column_name. Any forgotten reader will crash on a missing column instead of silently working until drop day. Cheap, durable canary.
  • Wait one deploy window.
  • Release N+1: drop the renamed column.

The rename itself triggers Squawk's renaming-column rule, so it needs the same -- squawk-ignore ceremony. Atlas v0.37+ supports this as a first-class pattern via the allow_column { match = "_deprecated_.+" } policy — we don't use Atlas, but the convention is the same. (Atlas v0.37 release notes)

Objective usage proof (advanced)

When grep + feature flag aren't enough — typically for columns referenced via dynamic SQL or agent-generated queries — query Postgres directly:

sql
-- Has this column been touched in the last 14 days?
SELECT query, calls, last_call
FROM pg_stat_statements
WHERE query ILIKE '%attempt_account_name_match%'
  AND last_call > now() - interval '14 days';

Zero rows = no live SQL traffic. Paste output into the contract PR description. Also worth scanning agent SQL-tool MLflow traces (see docs/sql-tool-architecture.md) since the agent generates SQL at runtime that won't appear in your grep.

Preventing forgotten contracts

The most common failure isn't dropping too early — it's never dropping at all, and the schema accumulates stale columns for years.

Suggested countermeasures:

  1. Auto-file a follow-up ticket on expand merge. When a PR labeled migration adds a new column intended to replace an old one, the merge bot opens an ONRAMP-#### "Contract or_ramps.<col>" ticket with due date = today + 21 days and assignee = expand-PR author.
  2. Quarterly stale-column report. Scheduled agent (we have AgentCore + intel runtime) runs the pg_stat_statements query above against every column not auto-classified in AUTO_EXPOSED_COLUMN_NAMES. Posts contract candidates to #engineering monthly.
  3. Tracking field in expand PR template. "Contract follow-up ticket: ONRAMP-####" — required before expand can merge.

None of these are implemented yet — file an issue if you want to take one on.

Reverting a migration that has already shipped ​

Never revert a merged migration by deleting its file. main auto-deploys to dev, so a migration that reached main has almost certainly been APPLIED — dev's alembic_version names it, and stage's does too if a promote went out. Deleting the file leaves those databases stamped at a revision no image will contain again: nothing in the alembic graph resolves, so flask db upgrade has no lineage to start from.

The damage is not the reverted migration — it is every migration authored afterwards. An unresolvable applied head looks exactly like the benign state it resembles (a database a NEWER image moved on, mid-rollout or on an older-tag re-promote), so the release migrate gate reads "at or ahead of this build's head", skips the upgrade, and reports success. Indefinitely, with every check green.

Do this instead ​

Write a new forward revision that undoes the change, and let it ship like any other migration:

  1. uv run flask db revision -m "revert <what>" — parented on the current head, not on the migration you are undoing.
  2. Put the inverse of the original's upgrade() in the new upgrade(), and the original's upgrade() in the new downgrade().
  3. Leave the original file in the tree. It is history: every database that applied it has a resolvable path forward, and one that never did applies both and lands in the same place.

Reverting the application code is a separate, ordinary revert. Do that first if the deploy is on fire — a column nothing reads costs nothing, and the schema revert can follow at its own pace.

If a deletion has already merged ​

scripts/check-migration-deletions.ts fails a PR that deletes a migration which already reached the base branch, so this should not recur. When it has already happened, repair the graph — do not reach for flask db stamp. A stranded database cannot be fixed by a migration alone, because alembic resolves alembic_version against the script directory before it runs anything: with the revision absent it raises KeyError: '<revision>' and no migration in the tree ever executes.

  1. Restore the deleted revision files — that is what makes the stranded head resolvable again, and you need every deleted revision in the chain, not just the stamped one (a merge revision names its parents, so it cannot resolve without them either).

    Then decide what body each one keeps, and the deciding question is why it was reverted:

    Why it was revertedBody
    The change was WRONG — you do not want it applied anywhere, ever againEmpty it. A tombstone: pass/pass, with a docstring saying the body was deliberately removed and pointing at the revision that cleans up databases which already ran it. The original is in git history.
    The change was fine, the timing was not — it is coming backKeep the original body.

    Emptying it is the common case, because a revert usually means the change was bad. Do not restore SQL the team has rejected just to make the file look like history: a fresh database would then apply the rejected change and immediately have it undone, writing to real tables for a change nobody wants. The docstring carries the history; the body carries only what should still happen.

  2. Add a new forward revision that cleans up what the original left behind, parented on the restored head. This is the only revision that should touch those rows. Every database then converges from wherever it is:

    • a stranded database resolves its head and applies the cleanup, removing the rows it really has;
    • a database that never applied the original passes through the tombstone and finds nothing to clean — the cleanup must be written idempotently so that is a no-op, not an error.

  3. Check what the deleted revision left behind that its own downgrade() did not cover. A soft-delete elsewhere in the stack is the common one: the boot catalog sync soft-deletes a permission the catalog stops declaring, but only marks or_permissions — it never touches or_role_permissions, so membership rows survive pointing at a soft-deleted permission. A stamp leaves those forever; a forward revert collects them.

  4. Prove both directions before shipping, offline and without a live database:

    bash
    uv run flask db upgrade --sql <stranded-head>:head   # resolves, and applies only the cleanup
uv run flask db upgrade --sql <parent>:head           # the fresh-database path

    On the second render, check that none of the reverted SQL appears — a tombstone that still carries its body shows up here as statements against real tables. The only writes a fresh database should make are alembic_version bookkeeping plus whatever the cleanup does idempotently.

Recording the revision in migrations/revoked_revisions.yaml is the fallback for a deletion you cannot repair this way — a revision whose body is genuinely unrecoverable. It makes the state visible rather than silent: resolve_schema_posture (app/api/utils/schema_cli.py) reports REVOKED instead of AHEAD, the migrate gate REFUSES the release instead of skipping it, the boot guard says so in the pod log, and the Admin → Application Details badge reads Diverged instead of Ahead. It is not a substitute for repairing the graph, and a revision listed there while its file is back in the tree is itself a failure the deletion gate reports.

Ledger entries are permanent while they stand, but an empty ledger is the healthy state: repairing the graph is what lets an entry come out, and it should.

Reverting an authz catalog version bump that has already shipped ​

The same trap, one subsystem over, and it does not involve alembic at all. CATALOG_VERSION (app/api/authz/catalog.py) and BUILTIN_ACCESS_VERSION (app/api/authz/builtin_access.py) order authz declarations across releases, and or_catalog_sync_state records the version each catalog was last reconciled at. flask reconcile-authz stands down when the database records a version higher than the build's.

Never revert a shipped version bump by taking the constant back DOWN. main auto-deploys to dev, so a bump that reached main has almost certainly been recorded — and once the constant is below what a database recorded, the reconciler stands down on every run, forever. The damage is not the reverted declaration: it is every version between the build's and the recorded one, all of them skipped by a reconciler that prints a skip, exits zero, and leaves the release migrate gate green.

git revert makes this worse in a way worth naming, because it looks like the correct tool: it takes the constant down and deletes the version's entry from app/api/authz/catalog_version_history.yaml. That history is the only thing that separates a database recording a version the build moved back from (broken) from one a newer release owns (benign, mid-rollout or an older-tag re-promote). Delete the entry and the broken reading becomes unreachable — the recorded version now sits above everything the build knows, which is the benign answer.

Do this instead ​

Go forward, exactly as with a migration:

  1. Restore the earlier declaration — the ordinary code revert, unchanged.
  2. Give it a new, higher version: set the constant above every version any database may have recorded (the reverted bump's value, at least), rather than back to the value it had.
  3. Append that version to catalog_version_history.yaml under its catalog, with the fingerprint the version tests print. A repeated fingerprint at a higher version is expected — it is what "this content again, but newer" looks like, and nothing rejects it.

Every database then converges on its next deploy with no operator step: the version it recorded is now below the build's, so the sync applies normally. --force needs nobody's shared-environment credentials, and no one has to remember which environments were stranded.

If a downward revert has already merged ​

scripts/check-authz-version-history.ts fails a PR that removes or rewrites a history entry which already reached the base branch, so a revert can no longer take the history with it. When the constant is already below what an environment recorded:

  • The reconciler reports AUTHZ_POSTURE=<catalog>:revoked and says so loudly in the pod log, naming the recorded version and the range being skipped.
  • The release migrate gate refuses that environment's release instead of finishing green.
  • The repair is still the forward bump above. It is what releases the stranded environments, and it is the only repair that needs no database access.

flask reconcile-authz --force is the last resort — for when a forward bump genuinely cannot be shipped. It makes the build's declaration authoritative and records the lower version, so subsequent syncs proceed. It has to be run against each stranded environment, which is precisely the operator step the forward bump avoids.

A deferred posture on builtin_access is not itself a fault: a builtin role names permissions the catalog sync must write first, so the builtin roles are never reconciled against a catalog a newer release wrote. Its own recorded version is still reported alongside, so a builtin-access version stranded above the build's cannot hide behind the catalog's stand-down.

Escape hatch syntax (mechanics) ​

The contract-migration template above uses Squawk's -- squawk-ignore directive. Mechanics worth knowing:

  • Directive applies to the next statement only.
  • Multiple rules comma-separated: -- squawk-ignore ban-drop-column,require-timeout-settings.
  • scripts/lint-migrations.sh strips the trailing ; and blank line that Alembic emits around op.execute(), so the directive lands flush against the target statement after rendering. You don't need to do anything special — write the comment, let the script normalize.
  • Never use -- squawk-ignore-file — too broad. Always name the specific rules.
  • The migration file header must reference the parent expand PR and Jira ticket (see template above).

If you find yourself adding squawk-ignore for any reason other than a contract-phase drop, stop and ask. There's almost always a more idiomatic expansion that doesn't need the ignore.

Further reading ​

Internal documentation — gated behind Cloudflare Access.

© OnRamp