ALTER TABLE orders ALTER COLUMN total TYPE integer; does two things you may not want. If total was numeric, every value is rounded to a whole number, so the cents are gone. And PostgreSQL rewrites the table under an ACCESS EXCLUSIVE lock, blocking reads and writes until it finishes. If the statement is still running, cancelling it rolls it back; if values were already rounded, the originals are only in a backup.
How to recover the data
- If the
ALTERis still running and blocking traffic, cancel it (SELECT pg_cancel_backend(pid)); the change rolls back. - If it finished and values were rounded or truncated, restore a point-in-time copy from just before it into a separate instance.
- Copy the original values back by primary key into a new column of the right type (for example
total_cents bigint), instead of converting the damaged column again.
Why it happens
A type change looks like a small edit to a migration, but most of them are not metadata-only: the database must convert and rewrite every row. Narrowing casts (numeric to integer, bigint to integer, text to varchar(n)) round, fail on out-of-range values or, on MySQL without strict mode, truncate with only a warning.
How to fix it
Use an additive migration: add a new column of the new type, backfill it in batches, move the code to it, and drop the old column later.
ALTER TABLE orders ADD COLUMN total_cents bigint;
UPDATE orders SET total_cents = round(total * 100) WHERE total_cents IS NULL;On PostgreSQL, set a lock_timeout on migrations so a blocked ALTER fails fast instead of queuing behind long transactions.
How to stop it from happening again
VERICTO-018 · ALTER TABLE ALTER COLUMN TYPE · Severity: High · AST node: AlterTableStmt > AlterColumnType
The statement the rule reports
-- migrations/0048_total_type.sql
ALTER TABLE orders ALTER COLUMN total TYPE integer;
Real CLI output
$ npx @vericto/vericto-cli@2.3.0 check --offline migrations/0048_total_type.sql
note: offline mode: 42 standard rules at default severities, engine 5.1.0. Custom rules, overrides, sensitive columns, allowlists and the audit trail apply online only.
! migrations/0048_total_type.sql FLAGGED VERICTO-018 high
AlterTableStmt > AlterColumnType
fix: Use an additive migration (new column + backfill + swap)
0 blocked · 0 allowed · 1 flagged · 0 monitored (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))
In a migration file the CLI marks it FLAGGED and exits with 0: a schema change in a migration is usually intended, so it is raised for review instead of blocked. With --fail-on flag (or fail_on = "flag" in .vericto.toml) it exits with 1 and the job fails. Outside a migration (--sql, stdin or another file) it blocks, as the proxy and MCP check_sql do.
The corrected version
-- migrations/0048_total_type.sql
ALTER TABLE orders ADD COLUMN total_cents bigint;
$ npx @vericto/vericto-cli@2.3.0 check --offline migrations/0048_total_type.sql
note: offline mode: 42 standard rules at default severities, engine 5.1.0. Custom rules, overrides, sensitive columns, allowlists and the audit trail apply online only.
0 blocked · 1 allowed · 0 flagged · 0 monitored (ruleset standard@engine-5.1.0 · mode: offline (engine 5.1.0, 42 standard rules, no workspace policy))
Try it free, no account
The CLI checks your .sql files on your machine with the 42 standard rules: no sign-up, no API key, and no SQL leaves your computer. It is open source (AGPL-3.0-only).
npx @vericto/vericto-cli@2.3.0 check --offline migrations/
On every pull request
The vericto/check-sql GitHub Action (Apache-2.0) runs the same offline check on the .sql files a PR changes and annotates the findings on the PR. Save it as .github/workflows/vericto.yml:
on: pull_request
jobs:
vericto:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v7
with: { fetch-depth: 0 }
- uses: vericto/check-sql@v1
with: { offline: true, upload-sarif: false }
At runtime, for AI agents
The SQL an AI agent writes never goes through a PR. The Vericto TCP proxy checks each statement between the application or agent and the database, and the MCP check_sql tool lets the agent check SQL before it runs it. Both use the runtime profile, which also blocks schema changes.
The AST condition in the engine
The rule is a deterministic predicate over the AST, in src/rules/evaluator.rs of the open source engine (AGPL-3.0-only), at this page's commit:
"VERICTO-018" => find(stmts, |s| {
s.kind == StatementKind::AlterTable
&& matches!(s.alter_table_kind, Some(AlterTableKind::AlterColumnType))
})
.map(|s| {
let mut v = violation(
rule,
s,
Some("Use an additive migration (new column + backfill + swap)".to_string()),
);
v.ast_node_path = "AlterTableStmt > AlterColumnType".to_string();
v
}),
Frequently asked questions
Related rules
- VERICTO-016: ALTER TABLE RENAME (table or column)
- VERICTO-017: ALTER TABLE DROP CONSTRAINT (FK/PK/CHECK)
- VERICTO-015: ALTER TABLE DROP COLUMN