A database schema change can break an integration even when the application still loads and every deployment step reports success. A renamed column, tightened type, removed field, or changed nullability can leave older code, exports, webhooks, and reporting jobs unable to read or write records correctly.
The safest way to change a shared database is to treat the migration as a compatibility project. Introduce the new shape while the old shape still works, move readers and writers deliberately, then remove the old shape only after the surrounding systems have caught up.
Why schema changes are integration changes
A database rarely serves one program. A customer record may be written by a website, read by an API, exported to a CRM, copied into a warehouse, and summarized in a reporting workflow. Each consumer may deploy on a different schedule and may not fail loudly when a field changes.
That means a migration plan needs more than a SQL statement. It needs an inventory of clients, a compatibility window, a rollout sequence, and a way to confirm that the intended data still reaches each destination.
For example, imagine replacing a single full_name column with first_name and last_name. A new application version can use the separate fields, but an older export job may still select full_name. If the old field disappears first, the export fails. If both fields remain but only one is updated, the export may succeed while sending stale data.
Use expand, migrate, then contract
The expand-contract pattern creates a controlled transition between two compatible shapes.
- Expand: add the new columns, tables, indexes, or API fields without removing the old ones.
- Migrate: update writers and readers, backfill existing records, and compare the old and new representations.
- Contract: remove the old structure only after usage has stopped and the evidence supports removal.
The pattern is useful because each step can be deployed and observed separately. It also gives the team a recovery path. If the new reader exposes a problem, you can pause the rollout while the old representation remains available.
Start with the dependency map
Before writing the migration, list every known dependency on the structure being changed:
- Application code, background jobs, and scheduled reports
- REST or GraphQL endpoints and webhook payloads
- Imports, exports, spreadsheets, and file-based handoffs
- CRM, payment, fundraising, marketing, and analytics integrations
- Database views, stored procedures, dashboards, and ad hoc queries
- Validation rules, documentation, tests, and operational runbooks
Search source code and configuration for the table and field names. Review query logs or database usage metrics when they are available. Ask the owners of downstream systems how they consume the data. A dependency map is not proof that the list is complete, but it makes unknown risk visible before the change reaches production.
Make the expanded schema backward-compatible
During the expand step, add the new structure in a way that old code can continue to use. Adding nullable columns is often safer than adding required columns immediately, because existing writes may not provide the new value yet.
Do not assume that a new column is harmless just because old queries ignore it. New indexes can affect write performance. New constraints can reject records that previously passed. Triggers can create unexpected side effects. Large backfills can compete with normal traffic and hold locks longer than expected.
For a sensitive change, separate the database change from the application release. First confirm that the database can support both shapes. Then deploy code that knows how to use the new shape.
Keep dual writes deliberate and observable
When both old and new representations must be written, define one authoritative value and make the relationship explicit. A dual-write period should answer three questions:
- Which field is the source of truth while the transition is active?
- What happens when one write succeeds and the other fails?
- How will the team find records where the values disagree?
For the name example, the application might accept structured name fields, derive a display value for legacy consumers, and record a metric when the values cannot be reconciled. Avoid silently duplicating business logic in several services. If the transformation is important, centralize it or document the exact rule and version it with the migration.
Backfill in bounded, restartable batches
Existing records usually need to be moved into the new structure. A single unrestricted update may create long locks, large transaction logs, replication lag, or a difficult rollback. Prefer bounded batches with a stable ordering key.
A practical backfill should record:
- The range or cursor processed by each batch
- The number of rows read, updated, skipped, and rejected
- The migration version and code version used
- Start time, completion time, and error details
- A safe resume point after interruption
Make the operation idempotent where possible. Re-running a completed batch should not create a second business record or overwrite a newer source value. For records that do not map cleanly, preserve them in a review queue or exception table instead of forcing a guess.
Move readers before removing the old shape
Once the new data is available, change consumers in stages. Start with a read path that can compare the new value to the legacy value without changing the user-visible result. Then switch the authoritative read after the comparison is stable.
For integrations, validate more than the query response. Check that the destination receives the intended value, that identifiers remain stable, and that downstream transformations still behave correctly. An API response with a successful status can still contain a missing field, a default value, or a stale record.
Keep compatibility at the boundary when it reduces coordination risk. An API can expose a new field while retaining an old field for a documented deprecation period. That is often safer than requiring every client to update on the same day.
Contract the schema with evidence
Removing the old column or endpoint should be the last step, not the first cleanup task. Before contracting, confirm:
- No application, job, export, report, or integration still reads the old structure.
- New writes remain complete and the old and new values have no unexplained drift.
- Backfill exceptions have owners and an agreed resolution.
- Monitoring shows no deprecated-field access during the compatibility window.
- Backups and recovery procedures cover the post-migration state.
Announce the deprecation, set a removal date, and make the deadline visible to downstream owners. If the old structure is removed without usage evidence, the team is turning an observed migration into a discovery exercise in production.
Test the failure paths, not only the happy path
A migration test plan should include the cases that create partial or misleading success:
- An old client reads while a new writer is active.
- A new writer fails after the legacy write succeeds.
- A backfill stops halfway and resumes from its checkpoint.
- A record changes between backfill batches.
- A downstream API accepts the request but stores an incomplete value.
- A report runs during the compatibility window and counts both representations.
- A rollback restores application code but leaves the expanded schema in place.
Test with production-shaped data, including nulls, duplicates, long values, unusual characters, old records, and records owned by more than one system. Validate counts, field values, timestamps, identifiers, and reconciliation results. Keep a small set of known records that a person can inspect end to end.
Plan the migration as an operational change
A good schema migration has an owner, a change window, a rollback or pause decision, a monitoring plan, and a communication path for downstream teams. It also has a definition of done that includes data correctness, not just a green deployment.
DigitalWerks can help review a schema change across the application, database, APIs, background jobs, CRM handoffs, and reporting layer. We can map dependencies, design a staged rollout, add reconciliation checks, and document the compatibility period so the migration remains understandable after the original implementers move on.