A CI agent that can ship migrations, with the dangerous ones held

Client

a CI agent

Policy shape

migration safety with approval on DDL

An agent opens pull requests that contain migrations, and the failure you are worried about is a schema change that locks a table or drops a column in production.

Two independent controls, and they answer different questions. Migration safety asks whether this DDL is a shape known to hurt: a lock-taking ALTER, a destructive drop, a rewrite of a large table. Approval asks whether a person has said yes. Run both, because the first one has no opinion about a perfectly safe change to the wrong table.

Wire it up

One policy, two guardrails. Set the timeout deliberately: it is how long a held statement waits before the agent has to try again.

1. 1

   Create the policy

   block is the strict migration-safety setting, and warn is the one to start with if you want to see what would have been caught before it starts failing builds.

   TerminalCopy

   pgbeam policies create \
     --name ci-migrations \
     --mode read_write \
     --migration-safety block \
     --approval-mode ddl \
     --approval-timeout-seconds 900 \
     --approval-auto-max-rows 0 \
     --statement-timeout-ms 30000

2. 2

   Issue a short-lived credential for the pipeline

   A CI credential should outlive a run and not much more. Give it an expiry rather than relying on somebody remembering to revoke it.

   TerminalCopy

   pgbeam agents create --name ci-migrator --policy pol_xxx --expires 24h

3. 3

   Run the migration through it

   The pipeline keeps whatever migration tool it already uses. Only the connection string changes, so nothing about how migrations are authored or ordered has to move.

   CI environmentCopy

   DATABASE_URL=postgresql://ci_migrator:pba_...@<project>.proxy.pgbeam.app:5432/mydb

4. 4

   Decide on what gets held

   A held statement waits for a decision. Approvals are visible in the dashboard and the CLI, and the decision is recorded next to the statement it applies to.

   TerminalCopy

   pgbeam approvals list
   pgbeam audit list --event blocked --limit 20

Start with --migration-safety warn for a week and read the audit trail. It tells you which of your existing migrations would have been blocked, which is a better argument for the setting than any documentation.

What gets through, and what waits

Four migrations an agent might open a pull request for.

1  CREATE INDEX CONCURRENTLY idx_orders_created ON public.orders (created_at)
2  ALTER TABLE public.orders ADD COLUMN note text
3  ALTER TABLE public.orders ALTER COLUMN total TYPE bigint
4  DROP TABLE public.order_archive

1  held for approval (ddl), then applied
2  held for approval (ddl), then applied
3  Verdict: block    Rule: migration_block
4  Verdict: block    Rule: destructive_ddl

What this does not cover

• Migration safety recognises patterns. A destructive change written in an unusual way, or hidden inside a function body it cannot analyse, is not guaranteed to be classified, and an unrecognised statement kind is refused rather than guessed at.
• Approval is a human bottleneck by design. If nobody is watching, held statements expire on the timeout you set and the agent has to resubmit, which is the correct behaviour and is also a stalled pipeline at 3am.
• Neither control reviews intent. A migration that is safe to run and wrong for the product passes both, and that is what code review is for.

More recipes

Questions