Your purchase-approval tool is already in daily use. Now users need a second approver. How do you introduce that change without interrupting requests or letting older code approve a purchase too early?
Backward compatible database migrations let you change stored data while keeping supported application releases working. But adding a table is only part of the job. You also need to control which code can write records, how existing decisions move across, and which release remains safe to restore.
This tutorial follows a hypothetical approval tool through that transition. The aim is to keep releases small without putting valid approval decisions at risk.
In this article:
- What changes when one approver becomes an approval path?
- How does expand and contract keep releases small?
- How do you add the new structures without disrupting live work?
- How do you copy existing approvals while requests keep changing?
- When is it safe to enable the second approver?
- What should the migration tests try to break?
- What can you roll back, and what needs repair or restoration?
- When can you remove the old fields?
- When is a maintenance window the simpler choice?
What changes when one approver becomes an approval path?
Imagine a purchase-approval prototype built with AI assistance. Its first users needed one simple workflow: submit a request, assign an approver, and record a decision.
The prototype stored two fields on each purchase request:
purchase_requests.approver_id
purchase_requests.approval_status
User feedback confirmed that the workflow was useful. The team added production safeguards, including access controls, tests, backups, and support ownership, but retained the single-approver model. There was no business reason to replace it yet.
Later, users ask for certain purchases to go through a department manager and then finance. That changes what “approved” means. Older code may treat one approval as permission to complete the purchase. It cannot safely interpret two separate decisions just because the database now stores them.
For this tutorial, the business rule is explicit:
- Existing requests keep their original single-approver path.
- Eligible new requests receive two steps only after the new behavior is enabled.
- Requests outside that eligible group continue to need one approver.
Each request keeps the path assigned when it was created. Changing the feature setting later does not silently change an existing request’s obligations.
The safety rule is that a request cannot become approved until every required step in its assigned path is approved. For a two-step request, finance must also wait for the manager’s approval.
That rule needs to govern screens, background jobs, administrative actions, and any process that creates a purchase order.
How does expand and contract keep releases small?
Expand and contract means making room for the new model before removing the old one.
First, add structures that existing code can tolerate. Next, move data and application behavior across. Finally, remove structures that no supported code needs.
Here, the team adds an approval_steps table while retaining the original approver and status fields. Four operations remain separate:
- Schema deployment: add the database structures.
- Backfill: fill those structures from existing records.
- Behavior change: switch the application to the new model and enable two-step requests.
- Cleanup: remove obsolete code and fields.
Separating these operations makes failures easier to diagnose. A slow data-copying job does not have to coincide with a new approval workflow.
Define three application releases:
- R0: the original application, which understands only the legacy fields.
- R1: a compatibility release that can use either model, depending on a controlled setting. In its new-model mode, it can safely process both one-step and two-step requests, although creation of two-step requests remains disabled.
- R2: the release that exposes the new workflow and allows its controlled activation.
R1 is a real recovery target, not just a release that tolerates an extra table.
| Phase | Schema | Allowed application versions | Existing-record updates | Enabled behavior | Recovery options |
|---|---|---|---|---|---|
| Expand | Old fields plus new structures | R0 and R1 in legacy mode | Not started | Single approver | Return to R0 if compatibility checks pass |
| Copy | Both representations retained | R0 and R1 in legacy mode | Backfill and reconciliation | Single approver | Stop copying; legacy fields remain authoritative |
| Switch | Both retained | R1 only, switching to new-model mode | Final reconciliation during a brief write pause | Single approver through steps | Keep R1; return to R0 only if legacy data is verified current |
| Enable | Both retained | R1 and R2 in new-model mode | Monitor consistency | New eligible requests get two steps | Return to R1 without discarding multi-step records |
| Contract | New model only | Releases verified not to need old fields | Complete | Assigned approval paths | Use only fallbacks compatible with the reduced schema |
Compatibility is a promise about specific releases in specific phases. It does not mean supporting R0 forever.
How do you add the new structures without disrupting live work?
Start with an additive change. Do not rename or remove the original approver fields in the same deployment.
A simplified schema sketch looks like this:
Before
purchase_requests
id
approver_id
approval_status
After expansion
purchase_requests
id
approver_id retained
approval_status retained
approval_path_kind single-step or manager-then-finance
approval_steps
id stable step identifier
request_id reference to purchase_requests
position order within the request
approver_id
status
Rules
unique(request_id, position)
each step belongs to an existing request
The stable step identifier gives approval actions a consistent target. The uniqueness rule prevents retries from creating two steps at the same position. Neither rule alone enforces the whole approval workflow.
The assigned path tells the application how many steps should exist. This matters because one approved row must not make an incompletely created two-step request look complete.
R0 must still be able to create single-step requests. The new path field therefore needs a tested default or another compatible treatment for inserts that omit it. Check object mapping, bulk imports, and queries that assume a fixed column layout as well.
Keep reads and writes using the legacy model during this initial expansion. Adding the schema should not activate new behavior.
The operational risk depends on the selected database and version. Adding a table, index, default, or constraint can involve different locks and transaction behavior. Do not describe a migration as “online” based only on its syntax.
Rehearse the actual migration with representative data volume and concurrent activity. Record:
- Lock waits and query latency.
- Application errors and failed writes.
- Effects on background workers.
- What happens when the schema operation is interrupted.
- Whether retrying requires inspection or manual cleanup.
Set measurable stop conditions using your service’s existing limits. Define the database-specific abort procedure too. Sending a cancel command is not proof that every lock has been released or that no schema change took effect.
How do you copy existing approvals while requests keep changing?
A backfill copies existing information into the new structures. In this example, every existing request becomes a single-step path, preserving its approver and current decision.
Before writing the backfill, define the status mapping. If the old system distinguishes pending, rejected, canceled, and approved requests, copying only the approved records is not enough. Unknown or inconsistent states need investigation rather than a guessed conversion.
During R0 and R1 coexistence, the legacy fields remain the source of truth for single-step requests. The new steps are a copy, not yet the basis for approval decisions.
R1 can write both representations in one transaction, so either both changes commit or neither does. But that does not solve the old-writer problem: R0 instances and older workers still update only legacy fields.
For this example, use a simple synchronization plan:
- Copy records in bounded batches while legacy fields remain authoritative.
- Deploy R1 everywhere and stop R0 processes from returning.
- Briefly pause approval writes, including jobs and administrative scripts.
- Wait for in-progress writes to finish, then reconcile all remaining differences.
- Switch R1 to the new model before reopening writes.
This is not a zero-downtime design. It accepts a brief write pause to avoid maintaining a more involved synchronization mechanism. If even that pause is unacceptable, a tested change-capture approach, which records and applies database changes, may be appropriate instead.
The backfill still needs protection against concurrent updates. Otherwise, it might copy “pending,” pause, and then overwrite a decision that has since become “approved.”
Use a transaction or version-check strategy tested on the selected database. For example, a batch can lock each source request while reading and copying it, provided that this correctly serializes with application writes in the actual database setup. Keep transactions small enough to avoid holding large groups of requests hostage.
Checkpoints should record completed work, and retries must be safe. Running a batch again must not create duplicate positions or overwrite a newer decision with a stale snapshot.
Reconciliation compares meaning, not just totals. Confirm that each request has the expected path, approver, and decision state. Track skipped, failed, and mismatched records explicitly.
Once steps become authoritative, retire the legacy-to-step copier. A delayed retry must not overwrite a new-model decision using obsolete legacy data.
When is it safe to enable the second approver?
First prove that the new model can run the old workflow.
R1 should create and process single-step requests through approval_steps before the team enables two-step creation. This separates “can we use the new storage model?” from “does the new business behavior work?”
Account for every process that touches approval data:
- Web application instances.
- Queue workers and scheduled jobs.
- Reports and integrations.
- Administrative scripts and manual correction tools.
Draining an old worker means letting it finish safely or stopping and retrying its work under compatible code. Removing an old web instance is not enough if an earlier job can still write a decision.
Before enabling the second approver, require a clean reconciliation result and a rehearsed rollback to R1 in new-model mode. R1 must preserve two-step meaning even if it lacks R2’s new screens or setup options.
Use a feature flag, a setting that controls whether behavior is available, to govern creation of new two-step requests. If the tool can reliably identify a small eligible group, enable that group first.
Create the request, its assigned path, and all required steps atomically: they should appear together or not at all. Approval actions also need transaction and concurrency checks so competing actions cannot bypass the required order.
Watch for requests that stop progressing, attempts to approve finance early, and overall status changes that disagree with the required steps. Downstream purchase processing must use the same completion rule.
Turning the flag off stops new two-step requests. It does not convert existing ones back to one step.
Once multi-step records exist, R0 is not a safe fallback for this design. A different design could isolate those records from R0 and route them to another supported process, but that isolation would need its own tests. Here, R1 is the minimum fallback.
What should the migration tests try to break?
Test the transition, not just the final schema.
A useful matrix covers these states:
| Data and schema state | Releases to test | Main question |
|---|---|---|
| Original schema | R0 | Does the baseline workflow still pass? |
| Expanded schema, no backfill | R0 and R1 in legacy mode | Can ordinary work continue before steps exist? |
| Partial backfill | R0 and R1 in legacy mode | Can old writes and copying coexist safely? |
| Fully reconciled data | R1 in new-model mode | Do single-step requests retain their decisions and behavior? |
| Active multi-step requests | R1 and R2 in new-model mode | Can both releases enforce every required step? |
| Legacy fields removed | Every retained release | Does anything still require the removed fields? |
Also test that deployment checks prevent a release from entering a phase it does not support. R1 need not work on the original schema if expansion is an enforced deployment prerequisite.
The most valuable tests create awkward timing:
- Let an R0 instance update an approval while a backfill batch processes that request.
- Interrupt a batch before and after its transaction commits, then restart it.
- Retry step creation and submit competing approval actions.
- Attempt a finance approval before the manager has approved.
- Complete the first step and verify that no purchase order is released.
- Reconcile requests with rejected, canceled, and missing legacy values.
- Roll back to R1 with both single-step and multi-step requests in progress.
For every retry, verify that it neither duplicates a step nor erases a decision. Existing single-step requests must keep working without acquiring a new finance requirement.
Run schema changes and backfill under representative load, including long-running transactions and worker activity where relevant. Functional tests can pass while the real migration causes unacceptable lock waits.
AI assistance can help draft migration code, fixtures, and edge-case tests. Human review still needs to examine the approval rules, transaction boundaries, and recovery assumptions. Generated code and generated tests can share the same mistaken assumption, so passing those tests alone does not establish safety.
What can you roll back, and what needs repair or restoration?
A useful recovery plan distinguishes three actions.
Code rollback changes the running application. It does not undo records created by the newer release.
Data repair corrects specific records while preserving valid activity.
Restoration recovers database state from a backup or recovery point. It can remove valid writes made after that point.
Before multi-step activation, returning to R0 may be acceptable if the legacy fields are still current and R0 tolerates the expanded schema. Do not assume they remain current after switching to steps. If R1 has stopped updating legacy fields, that fallback requires a separately tested reconciliation procedure.
After activation, roll back only to a release that understands multi-step requests. In this example, that is R1 in new-model mode. If no safe release is available, pause the affected actions and prepare a reviewed forward fix rather than run code that could authorize a purchase incorrectly.
A reverse schema migration is not automatically a recovery plan. Collapsing a manager’s approval and finance’s pending decision into one status loses meaning. Dropping the steps may also destroy evidence of who decided what.
For data repair, identify affected requests from logs and decision history. Preserve that history, document the corrections, and validate each repaired request against its assigned path. Avoid a broad update that simply marks stuck requests approved.
Restoration needs a wider view. A database backup cannot retract a purchase order already sent to a supplier. Rehearse recovery and plan how to reconcile external actions before replaying jobs or reopening writes.
Keep an executable runbook with these essentials:
- Stop conditions: errors or approval-rule violations that trigger a pause.
- Authority: who can disable creation, pause approvals, and stop workers.
- Fallback: the exact supported application release and required settings.
- Diagnostics: checks for incomplete paths, mismatched states, and stalled requests.
- Recovery approval: who authorizes data repair or restoration.
- Reopening checks: proof that approval records and external actions agree.
The migration owner and on-call team should be able to use this without reconstructing the design during an incident.
When can you remove the old fields?
Remove the legacy fields when they no longer support live behavior or a retained recovery path.
Confirm that no supported application, worker, report, integration, or script still reads or writes them. Inspect actual usage as well as code references. A rarely used export can remain invisible during ordinary application testing.
The new representation should also have passed reconciliation and operated correctly through an observation period chosen for your workload. A tool with occasional finance approvals may need a different observation period from one processing requests throughout the day. There is no universal waiting period.
Retire old rollback targets explicitly. If a release needs the original fields to start or operate, removing those fields also removes that recovery option.
Drop legacy reads and writes in application code before scheduling destructive schema cleanup as a separate change. Update tests and deployment checks so obsolete code cannot quietly return.
Review audit and archival requirements before deleting historical information. Preserve required evidence without assuming that keeping obsolete database columns is the only way to do so.
Finally, rehearse the cleanup on the selected database version. Locking, constraints, and interrupted operations still matter during removal.
This last step protects future delivery speed. Temporary compatibility code is useful, but leaving two competing models indefinitely makes each later change harder to understand.
When is a maintenance window the simpler choice?
Not every internal tool needs a multi-release migration.
If users can accept a planned pause, stopping work and changing the model in one controlled window may cost less than maintaining compatibility modes, synchronization, duplicate data, and a larger test matrix.
That decision should reflect the tool’s availability needs, not a preference for the most elaborate deployment method.
A maintenance-window approach can be straightforward:
- Announce the pause and explain what users cannot do.
- Stop all writers, including workers, integrations, and administrative scripts.
- Confirm recovery readiness and capture the agreed recovery point.
- Migrate the schema and existing records.
- Deploy the application and validate approval behavior.
- Reopen access only after the checks pass.
Rehearse the duration and set a failure decision point before starting. Decide when to stop troubleshooting and begin the tested recovery procedure, leaving enough time to complete it.
Downtime does not make destructive changes reversible. Lost decision history and purchase orders already sent still require careful handling.
Choose the least complicated approach that meets your availability needs and protects approval records. That leaves more time for useful user feedback and small improvements, rather than permanent migration machinery.
Ready to start?
Before your next schema change, map three things: which application versions may run together, which representation is authoritative in each phase, and where rollback stops being safe.
That map is a practical review tool for developers, process owners, and the people supporting the application.
If your team needs help testing those boundaries, discuss the migration with Droptica. Start with one workflow and a clear recovery plan, then keep improving the tool without putting daily work at unnecessary risk.




