Safe database changes with AI coding agents
A coding agent can write a correct migration and still lose data: by dropping something it did not know was in use, or by undoing a change after the app has written to it. This is how Backenly handles both, what it does on its own, and what it leaves to you.
Updated October 24, 2026Each section links the code it describes
Who may do what
Three authorities, never mixed. The agent asks, Backenly checks and records, and a person decides anything Backenly cannot prove safe.
| Who | May | May not |
|---|---|---|
| Your coding agent (MCP key) | Request changes, request a restore, read outcomes. | Approve anything, skip or weaken verification, touch checkpoints or the audit trail, or change governance settings without a person. |
| Backenly | Take checkpoints, verify, recover a failed change when recovery is proven lossless, escalate. | Recover when it cannot prove safety, or report success it has not verified. |
| A person (admin on the project) | Approve a risky restore, restore by hand, approve an agent’s request. | — |
Source: docs/CHANGE_RECOVERY.md
One change, step by step
What happens between an agent calling apply_migration and the change counting as done. The code is the migration parser, the change ledger and protected verification.
The agent sends SQL
apply_migrationparses the whole migration before anything runs, and refuses all of it if one statement cannot be mapped to a governed action. Each statement is then checked against the tables that exist. ADROPis refused withDESTRUCTIVE_REFUSEDand a pointer tobackend_chat, which parks the request for a person.Backenly takes a checkpoint
Under the project’s change lock, before the first statement, sized to the change’s risk (the table below). One change at a time per project: a second waits, then is refused.
The statements run
Execution stops at the first failure and the response lists what applied and what remains, so the agent can finish the job instead of replaying the lot.
Backenly verifies the result
Protected verification checks that each reported step really happened, that nothing disappeared as a side effect, that row-level security and API grants were not weakened, and that every touched table is still usable. The checks take nothing from the request: no argument an agent sends can skip or narrow them.
The change ends in a recorded state
healthywhen the checks confirm it. Recovered automatically when they contradict it and the way back is provably lossless. Otherwise it waits for a person in the approvals inbox. A check that could not run leaves the changeunverified, which is never recovered automatically.
Checkpoints, sized to the risk
Nothing a change removes is deleted. A dropped table or column moves into the change’s vault in the same transaction (lib/changes/preserve.ts), and a destructive change runs only after a person approves it.
| Risk | For example | What is kept |
|---|---|---|
| Additive | Create a table, column, index or constraint | A catalog restore point. |
| Transforming | Rename, retype, change nullability or a default | A restore point; a retype also keeps the original values and the values it produced. |
| Destructive | Drop a table or a column | A restore point, and the data moves into the change’s vault in the same transaction as the drop. |
| Data rewrite | Backfill, normalise, split, cast with data | A restore point, with the before and after images of the rewritten columns. |
Reversible is not the same as safe to reverse
Putting the schema back is usually possible. Whether it is safe depends on what the application wrote since the change, so before any restore Backenly measures exactly that. A restore plan for “the state before change N” checks that every table the change touched still looks exactly as it left it, orders the reverse statements, measures the impact, and proves the statements in a transaction that is rolled back. The impact question is whether any data written since would stop being available to the app:
- rows in a table the restore removes
- values in a column the restore removes, other than its default
- values updated since a retype or a data rewrite
- values that would not survive the reverse cast
- rows written since that the previous schema cannot hold, such as a restored NOT NULL column they have no value for
Any impact at all makes the plan need a person, even though what would leave the app is kept in the restore’s own vault: kept is not the same as available. When the restore runs, Backenly locks the tables, measures again, and refuses if the impact is worse than what the person approved. The code is lib/changes/recovery.ts.
| Plan | Meaning | Who may run it |
|---|---|---|
| lossless | No conflicts, no impact on data written since, and the proof passed. | Backenly automatically after a failed verification, or a person. |
| needs_approval | The proof passed, but data written since the change would stop being available to the app. | A person, who sees exactly what and how many. |
| blocked | A later change touched the same table, or the proof failed against today’s data. | Nobody, until the named cause is resolved. |
| unavailable | The restore point expired, was purged, or failed its integrity check. | Nobody. The console does not offer it. |
Automatic recovery is the lossless row only, and only right after a change failed its verification. A recovery that fails stops: no retries, the evidence stays, and a person decides. While any change in the project waits for a person, Backenly takes no further automatic recovery action there.
In production: consolidating a duplicated column
Restructuring a live table is risky because the app keeps reading and writing it while the change runs. Backenly does it as an expand/contract ladder of separately reversible steps, and only with the owner’s consent, bound to the exact plan. On 16 September 2026 the ladder ran against a disposable project on Backenly’s production database. The table had sessions.status and a duplicate, sessions.legacy_state, over 80 rows; the ladder consolidated them onto a new lifecycle_state.
| Step | Recorded outcome |
|---|---|
| add_structure | Added sessions.lifecycle_state; the table’s oid unchanged; 80 rows preserved. |
| carry_constraints | Derived the new column’s allowed values from status under upper(): ACTIVE, ARCHIVED, PENDING. |
| dual_write | Installed a trigger that writes both columns. |
| backfill | 80 rows updated in 1 batch, as a background job, through row-level security. |
| verify | 80 compared, none disagreed. |
| switch_readers | 9 Backenly-authored readers switched to the new column; 3 consumer classes cannot be enumerated and still read the old one. |
| contract | Awaiting a person. Never executed by Backenly. |
Undoing the switch restored the same bytes
Switching readers rewrites the functions that read the old column. Reverting put back the code recorded in the ledger, not regenerated code that happens to behave the same:
| Point | Code hash | Reads |
|---|---|---|
| Before the switch | a99ecbe4f30f8af4 | status |
| After the switch | 020adfff944c0364 | lifecycle_state |
| After the revert | a99ecbe4f30f8af4 | status |
What it proved, and what it did not
It proved the execution path: every gate, the background backfill, the reader switch and its revert. It was not evidence about detection, since the finding was created for the exercise and labelled as such. Consent behaved as designed: each step changed the catalog, which changed the plan version, and an approval for the old version stopped applying.
Three classes of consumer, such as clients that query the API or the database directly, cannot be enumerated, so they still read the old column. That is why the last step, dropping it, is always a person’s. The run also found seven silent defects, from a redaction regex that stopped working to five reads blind to row-level security, each caught by a gate or by a report contradicting itself.
The full record, with plan and image ids: docs/maintenance-production-acceptance.md. The ladder’s steps: lib/autonomy/maintenance/step.ts.
Where it stands today
The rule got stricter after this run. Since 21 September, Backenly schedules a ladder only if it can perform the undo of every step in the deployment it runs on, not merely describe one. The constraint step above has no undo yet, so this ladder is shown with that reason and not offered for approval until one exists. The restructuring offered today consolidates a table’s fragmented access policies, and its undo puts back the exact policy set that was there before. The rule is lib/autonomy/maintenance/rollback-capability.ts.
What is not automated
- Row data your application writes is not versioned; only data a change itself removed or rewrote is kept.
- A restore that crosses a later change to the same table is refused, not merged.
- A change interrupted by a crash is reconciled to “manual intervention required”, and a person decides.
- A restore that today’s rows would break, such as bringing back a constraint rows written since would violate, is never offered.
- Dropping the old column at the end of a restructuring is always a person’s step: Backenly cannot know that nothing outside it still reads the column.
Read next
- MCP serverWhat your agent can callThe tools, the install for each client, and what waits for you.
- DocsDatabaseTables, the REST API and row-level security.
- DocsAutonomyWhat Backenly fixes on its own, and what waits for your approval.
- GuideBest backend for Claude CodeWhat to check before an agent writes to production.
Try it on a live project
One free project, no credit card. Connect your agent and ask it to change the schema.