Practical engineering with agents · · 15 min read
Database migrations with an agent: rehearse the transition and recovery
Preserved values, concurrent writers, resumable backfills, integrity checks and a demonstrated restore before a live change.
An agent can produce a plausible database migration while leaving the hardest part unstated: how existing data and independently deployed application versions survive the transition. A successful schema command does not establish that old clients still write correctly, that a backfill preserved meaning or that a backup can actually be restored. Treat a migration as a change to a living contract. The source diff is important, but the acceptance conditions must describe stored records, active readers and writers, and the recovery path if the next step cannot be completed.
This workshop follows a hypothetical order service renaming delivery_note to delivery_instructions without changing the customer's text. The example is deliberately modest so the transition can be understood. It is not a ready-to-run migration for your production database and does not describe a new GrantTap database-management feature. GrantTap can help supervise the Task, requests, files and available results. Your database tools and release process still need to perform the actual rehearsal and verification. The useful outcome is a concrete migration packet a reviewer can assess before any live data is changed.
Start with the invariant, not the SQL
Write what must remain true. In the workshop, every existing order keeps its identity and exact delivery text; supported old and new application versions can read and write during the planned transition; no unrelated records change. These are stronger requirements than the new column exists. Decide whether whitespace, empty text and a missing value have distinct meanings. If the application currently preserves them, a rename should not silently trim or normalize them. The agent needs a semantic target before it chooses schema operations or a data transformation.
Add explicit non-goals. The exercise does not redesign order history, rewrite customer instructions with a model or introduce a new text-length policy. If one of those changes is valuable, it deserves its own acceptance conditions. Keeping the rename narrow makes the verification understandable and reduces the number of explanations needed if a fixture fails. Through GrantTap, preserve the invariant in the Task brief so a later execution does not interpret a short message to continue as permission to expand the migration into a broad cleanup of nearby data.
Inventory the real readers and writers
Find the application versions, background jobs, importers and administrative tools that use the old field. A repository search is a starting point, not proof that every deployed consumer has been found. The important question is which writers can still reach the database during the transition. An older mobile client or delayed worker may survive longer than the server release. Record ownership and update constraints for those consumers. If a consumer is unknown, preserve that gap instead of assuming it disappears because the latest source no longer references the old column.
Build a small compatibility plan describing the combinations that matter: old application with expanded schema, new application with expanded schema and the eventual contracted schema with only supported consumers. The exact matrix depends on your system. Ask the agent to identify which combinations will be tested and which require operational confirmation. GrantTap's Graph and Health guide can help navigate reported relationships, but an absent graph edge does not establish that no external writer exists. Verify the actual contracts and deployment inventory before choosing when removal becomes safe.
Inspect the database and migration tool you use
Pin the database engine, relevant version and migration tooling for the rehearsal. Do not apply a generic SQL recipe across systems with different transactional and locking behavior. Read the generated migration before running it. Determine whether the tool wraps steps in a transaction, what it records as applied and how it behaves after an interruption. A migration file is an executable plan whose effects depend on the target system, not a passive document that becomes safe because the agent generated it from a schema declaration.
For PostgreSQL, the version 18 ALTER TABLE reference documents operation-specific behavior and locking considerations. Use the relevant section for the exact change rather than assuming every schema operation has the same runtime cost. This article does not prescribe a universal lock budget or claim that a particular alteration is instant on your table. Inspect the actual engine, table characteristics and live workload through an authorized rehearsal. The result should explain the selected operation and its practical limits without inventing timing measurements from the small synthetic example.
Prefer a staged contract when consumers cannot move together
For the workshop, consider adding the new field before removing the old one. The application can then be adapted while existing consumers still have their expected representation. This is the general expand-and-contract approach described in the Prisma migration guide. The guide is a reference for the pattern; the commands and version-specific mechanisms must match your own tooling. Do not copy a current ORM command into an unrelated repository and assume it implements the same guarantees for your database or release process.
Write the stages explicitly: expand the schema, establish compatible application behavior, transfer existing values, verify consistency, move readers and writers, then consider contraction. Some systems require a different order or a coordinated maintenance period. The important property is that every stage has a defined supported state and a way to assess it. A single migration that renames and drops the old interface immediately can be appropriate only when the consumer and recovery assumptions actually allow it. Let the real deployment constraints determine the strategy rather than a blanket preference for the shortest SQL file.
Design concurrent-write behavior before the backfill
Copying old values once is insufficient if old writers can still update them afterward. Decide how the two representations remain coherent during the transition. You may need compatible application writes, a controlled synchronization mechanism or a deliberate quiescence period. Each option has tradeoffs and requires implementation-specific checks. Do not let the agent invent an unreviewed trigger or a permanent dual-write system merely because it makes a fixture pass. The chosen mechanism should belong to the project's actual architecture and authorized migration scope.
For the example, prepare a fixture where an old consumer changes the delivery note while the backfill is in progress. Define which value should be visible to each supported reader afterward. Also test a new consumer's edit so the backfill cannot overwrite newer text with an older copy. If these outcomes cannot be explained clearly, the transition is not ready for live execution. A backfill that succeeds on an idle database establishes less than one that preserves the intended behavior under the concurrent writes the migration plan says remain supported.
Build representative fixtures without copying private production data
Include records with ordinary text, empty text, missing values, Unicode content and unusually long instructions within the currently supported range. Keep stable fictional order identities so before-and-after comparisons are easy. A representative fixture should exercise the semantic distinctions in the invariant. It does not need real customer names or addresses. If a production characteristic matters, model that characteristic safely rather than exporting a whole database into a development environment simply because doing so is convenient for the agent.
Keep the fixture's expected values independent from the migration code. For the rename, the expected delivery text is exactly the recorded original under the chosen missing-value rule. A test that derives expectations using the same transformation can repeat its mistake. Record the fixture schema version and setup steps so another developer can recreate the rehearsal. Distinguish a small semantic fixture from a scale rehearsal: the first checks meaning, while the second may be needed to evaluate runtime and operational behavior. Neither should be presented as proving the other without the relevant observations.
Make the data transfer resumable under stated assumptions
If the transfer can outlive one short transaction, define how progress is recorded and how a repeated batch avoids changing already-correct data incorrectly. Choose stable iteration keys appropriate to the dataset. A mutable timestamp can be a poor cursor when updates are happening during the migration. The exact implementation depends on the database and concurrent-write strategy, so keep the algorithm explicit. Resumability is a behavior to test, not a property conferred by naming a script backfill or adding a progress counter to its output.
Interrupt a fixture run partway through and continue using the recorded progress. Compare the final state with an uninterrupted run and verify the invariant. Also repeat a completed batch where the design says repetition is allowed. The result should not duplicate records, erase newer values or turn a missing value into an unintended empty string. Keep operational limits such as batch size and pauses tied to actual observations. Do not invent throughput estimates from a handful of fixture rows or imply that the exercise establishes how long a production-sized migration will take.
Separate transaction boundaries from workflow boundaries
A transaction can protect the operations it actually contains, while a multi-stage application rollout spans more than one transaction and process. A successful transaction does not make the entire migration workflow atomic. Conversely, a failed application rollout does not necessarily mean the already committed schema change was undone. Ask the agent to describe what remains after each failure point. This gives the reviewer a realistic recovery map rather than an assumption that the word transaction reverses every effect across database, application and external service.
For the fixture, identify where the migration tool records completion and what happens if the connection disappears around commit. Recovery should inspect the recorded and actual database state before repeating a potentially non-idempotent step. The GrantTap evidence guide explains why a request and a reported result are separate observations. Apply that precision here: losing the response does not establish that the database rejected the change. Use the migration system's supported inspection path and preserve uncertainty until the actual state can be established safely.
Check integrity with more than row counts
Before-and-after row counts are useful, but equal counts do not prove that each record retained the right text. Compare stable identities and the fields relevant to the invariant. For the rename, verify exact values under the documented null and empty semantics. Check that unrelated fields remain unchanged where the plan requires it. A broad count can hide swapped values, truncation or a transformation applied to the wrong subset. Have the agent explain why each chosen comparison can detect a plausible failure in the proposed migration.
Keep the verification query and its scope reviewable. If the check samples records, call it a sample and explain what a full invariant check still requires. Avoid placing private row contents in the chat. Aggregate mismatch counts and safe fixture examples can be enough for a review, while authorized detailed inspection remains in the appropriate environment. A zero mismatch result establishes the conditions of the query that actually ran; it is not a universal guarantee about all tables or future writes. Preserve the source revision and migration identity alongside the observation.
Verify backups by restoring them somewhere appropriate
A backup command reporting success is evidence about that command, not proof that restoration works. Define the recovery objective and use a suitable isolated destination for a restore rehearsal. Verify the restored schema, representative data and the application behavior needed for recovery. Do not point a restore command at a live environment as a casual test. The destination, credentials and ownership must be explicit. This workshop proposes a rehearsal; it does not authorize any reader or agent to mutate production data without the relevant permission and prepared plan.
Record what the backup covers and what it does not, including external objects or services if they matter. A database snapshot alone may not restore files stored elsewhere. Keep timing claims tied to measured results from the actual rehearsal, and state differences from the live environment. If recovery was not tested, mark it as unverified rather than writing backup available as shorthand for a completed restoration. Through GrantTap, the compact review packet should identify the recovery evidence without copying the database, credentials or sensitive customer content into the conversation.
Distinguish rollback of code from recovery of data
Reverting a source commit does not necessarily restore the previous data representation or preserve new writes. Before each stage, identify which older application remains compatible with the current schema and data. In the expanded stage, a compatible previous build may be a useful recovery option. After contraction, that option may no longer exist. Do not describe rollback as a single button unless the actual release system and migration design support the full effect the user expects. A clear plan names the target state and the checks that establish it.
For the delivery-note example, keep the old representation until the plan has established that supported readers and writers no longer need it. If new writes contain information the old format cannot represent, a reverse transformation becomes another design problem. Even this simple rename needs the concurrent-write contract to remain clear. A recovery drill should exercise the intended older application against the database state it would actually encounter. Starting an old binary successfully is weaker than verifying that it can perform the relevant read and write without losing or misinterpreting the customer's instructions.
Review the migration from a phone as a concrete packet
Prepare a summary with the invariant, target environment, migration identity, stages, rehearsal outcomes and recovery state. Link the schema and data changes where available. Highlight destructive operations and unresolved consumers because they materially affect the decision. A phone reviewer should not have to infer a column removal from a generic message saying the database has been updated. Keep the proposal concrete before requesting any required final permission. The agent can finish the reviewable plan and safe rehearsal without pretending that a live execution has already occurred.
Use the correct Task and execution when inspecting results in GrantTap. A local fixture success from one computer does not establish that the production database on another route has changed. If a handoff occurs, preserve the migration identity and actual stage so the next agent does not restart from the first command. The handoff guide describes the product's continuity boundary; the database workflow needs its own supported state inspection. Distinguish prepared, rehearsed, applied and verified in the final summary, and connect every stronger statement to an observation from the intended environment.
Contract only after the compatibility evidence exists
Removing the old field should be a deliberate stage with its own acceptance condition. Confirm the supported consumer set and the absence or handling of old writers according to the actual plan. A repository search showing no old field in the latest source is not enough when independently deployed clients remain active. Keep the removal separate from an unrelated refactor so its effect can be reviewed clearly. If the required evidence is unavailable, leave the expanded compatible state intentionally rather than forcing contraction merely to make the migration appear finished.
After contraction, run the relevant application journey and integrity checks again. Preserve the actual result and any remaining operational limitations. A completed migration record explains not only which commands ran but why each transition was permitted. That becomes useful maintenance knowledge for the next schema change. The practical benefit of agent assistance is faster preparation, inspection and repetition of meaningful checks. The human still needs a coherent account of data semantics and supported consumers, because a syntactically correct migration can be an incorrect product change when those assumptions are left implicit.
Turn the rehearsal into a repeatable workflow
Keep the smallest reusable pieces: a representative fixture, explicit invariant checks, interruption recovery and a verified restoration path where required. Avoid building a new generic database administration product inside the Task. Integrate the useful checks with the repository's existing migration and release tooling. The next agent should find the same contract close to the behavior it owns. If the tooling changes, repeat the checks affected by that change rather than assuming a familiar filename carries the previous guarantees into a different execution mechanism.
A strong next request through GrantTap can now name the work precisely: inspect consumers, prepare expansion, rehearse the transfer and verify preserved values before proposing live application or contraction. The result is a reviewable engineering change rather than a confident SQL file. It remains useful after a computer change or a conversation interruption because the invariant, stage and evidence stay explicit. That is the standard worth aiming for whenever an agent helps change persistent data: the team can explain what survives, what changes and how the intended recovery has actually been established.