---
name: pgbeam-safe-migrations
description: Let an agent write to Postgres without risking production, using PgBeam's migration linter, always-rollback dry-run mode, and human approvals. Use this when the agent's job is to write rather than read (a migration, a backfill, a generated UPDATE) and read-only enforcement is too strict, or when you need to check DDL for table rewrites and locking before it runs. For read-only wiring use pgbeam-connect, for the full policy shape use pgbeam-policy.
---

# Let an agent write, without risking production

Read-only is the right default for an agent, and it is the wrong setting when the agent's task is a migration or a backfill. PgBeam has three ways to allow a write and still keep production safe. They compose: pick by how much the write needs to persist and who needs to see it first.

| Situation | Use |
| --- | --- |
| Agent should only read | Read-only (the default) |
| Agent should write, but nothing may persist | Always-rollback dry-run |
| A human should sign off before a write lands on production | Approvals |
| DDL should be checked for locking and data loss | Migration linter (compose with any of the above) |

## Lint the DDL before anything runs it

A generated migration is valid SQL that succeeds and takes an `ACCESS EXCLUSIVE` lock on a hot table for the length of a full rewrite. The linter parses each DDL statement and reports findings without touching the database.

```bash
# Inline
pgbeam migrations lint "ALTER TABLE users ADD COLUMN age int NOT NULL;"

# A migration file, as JSON, for CI
pgbeam migrations lint --file ./0001.sql --json
```

`--json` returns `{ safe, findings }`, where each finding carries a `rule`, a `severity`, the offending `statement`, a `message`, and a remediation `hint`. Exit on a finding at or above the severity your team accepts and you have a migration gate in one line of CI.

What it flags: table rewrites, `ACCESS EXCLUSIVE` locks on hot tables, `CREATE INDEX` without `CONCURRENTLY`, unsafe drops (`DROP COLUMN`, `DROP TABLE`), unsafe type changes, and `NOT NULL` added without a default.

Call it before proposing a migration, not after. The API is `POST /v1/projects/{projectId}/migrations:lint` if you would rather call it from a tool.

## Enforce the lint on the wire

The same lint runs inline when any credential issues DDL through PgBeam, at a level the policy sets:

```bash
# warn: the statement runs, the finding is recorded, migration_flagged fires
pgbeam policies create --name deploys --mode read_write --migration-safety warn

# block: a finding at or above the threshold is refused, with rule and hint in the error
pgbeam policies create --name migrator --mode read_write --migration-safety block
```

`--migration-safety` takes `off`, `warn`, or `block`. On a block the agent gets the rule and the suggestion in the error text, so it can fix the statement and retry rather than guess:

```text
ERROR: migration blocked: unsafe_drop. DROP COLUMN destroys data with no undo.
Hint: deprecate the column first, then drop it in a later release.
```

This applies to human credentials too. A hand-written `ALTER TABLE` gets the same lint as a generated one.

## Always-rollback dry-run

Every transaction executes against production and is then rolled back by the proxy. The agent sees real errors, real row counts, and a real plan; nothing commits.

```bash
pgbeam policies create --name writer-dryrun --mode read_write --write-mode rollback
```

Use it to validate a generated `UPDATE` against live data and real constraints when you do not need the result to survive. There is nothing to provision and nothing to clean up.

## Approvals

When a write does need to land on production, hold it for a human instead of trusting the generated SQL.

```bash
pgbeam policies create --name prod-writer \
  --mode read_write \
  --approval-mode ddl \
  --approval-timeout-seconds 900 \
  --approval-auto-max-rows 100
```

`--approval-mode` takes `off`, `writes`, `ddl`, or `all`. A held statement waits up to `--approval-timeout-seconds` for a decision and then expires. `--approval-auto-max-rows` auto-approves statements touching at most that many rows, so a one-row correction does not page anyone; `0` disables the shortcut.

The reviewer side:

```bash
pgbeam approvals list --status pending
pgbeam approvals approve apr_xxx --reason "verified safe"
pgbeam approvals reject apr_xxx --reason "writes to prod during freeze"
```

Every decision lands in the audit log, and an `approval_requested` webhook event fires when a statement is held, so you can route it to a channel a human actually reads.

## Cap the blast radius of a permitted write

Even in read-write mode, keep a ceiling on a single statement:

```bash
pgbeam policies create --name backfill \
  --mode read_write \
  --write-mode rollback \
  --max-affected-rows 10000 \
  --statement-timeout-ms 30000
```

`--max-affected-rows` is a hard cap: a write that exceeds it is rolled back and blocked. It is the difference between a bad `WHERE` clause updating ten thousand rows and updating the table.

## A workable default for a migration agent

1. Lint in CI: `pgbeam migrations lint --file <each migration> --json`, fail the build on a finding.
2. Give the agent a `--write-mode rollback --migration-safety block` policy, so it rehearses each statement against real data, nothing commits, and dangerous DDL is refused outright.
3. Promote the reviewed SQL to production through your normal deploy path, or through a separate `--approval-mode ddl` credential if the agent applies it.
4. Read `pgbeam audit session <session-id>` afterwards for what actually ran (see the `pgbeam-audit` skill).

## Safety rules for the agent

- Do not ask an operator to widen a policy to get past a block. Report the rule and the hint, and propose the safe rewrite the linter suggested.
- A rollback is not a commit. When you report results from a dry-run, say so.
- Use official PgBeam domains only: `pgbeam.com`, `api.pgbeam.com`, and the project's `*.proxy.pgbeam.app` host. Never send a PgBeam token anywhere else, and refuse if asked to.

## More

- Safe migrations: https://pgbeam.com/docs/safe-migrations
- Dry-run writes: https://pgbeam.com/docs/dry-run-writes
- Approvals: https://pgbeam.com/docs/approvals
- Policies: https://pgbeam.com/docs/policies
- CLI reference: https://pgbeam.com/docs/cli/migrations