The database schema migration decision record: why the migration execution model you configured determines your zero-downtime constraint and your schema drift detection gap

A 28-person SaaS company built a project management platform for architecture and engineering firms — a tool for coordinating design reviews, tracking change orders, and managing approval workflows for construction projects. The platform ran on PostgreSQL. The engineering team used a migration tool to manage schema changes: migration files were written in SQL, committed to the repository alongside application code, and applied automatically as part of the deployment pipeline. The pattern was consistent: the pipeline applied migrations first, then deployed the new application version. The team had followed this order since the first deploy and had never had a problem with it.

In the company's third year, the product team added a feature that linked every change order to the project manager who approved it. The database schema required adding a foreign key column to the change_orders table pointing to the project_managers table. The column was NOT NULL — every change order needed an approver, and the product team reasoned that a nullable column would allow inconsistent data to accumulate during the transition period. The migration added the column with a NOT NULL constraint and no default value, because every existing change order needed to be associated with the project manager who had approved it and that association was being backfilled by a separate data migration step that ran before the column addition. The schema migration ran. The backfill ran. The column existed in the production database with values for all pre-existing rows.

The new application version was deployed via a rolling update — the deployment replaced pods one at a time, with the old application version continuing to serve traffic from the remaining pods until each new pod passed its readiness check and was added to the load balancer rotation. The rolling update took four minutes. During those four minutes, both the old application version and the new application version were running simultaneously, serving incoming requests from the same PostgreSQL database that now had the NOT NULL foreign key column.

The old application version did not know the new column existed. Its INSERT statements for new change orders did not include the approver_id field. Every INSERT attempted by the old application version during the four-minute rolling update window failed with a NOT NULL constraint violation. The failure was not immediately obvious — the old pods were still receiving traffic, responding to requests, but any operation that wrote a new change order was returning an error. Forty-seven change order creation attempts failed during the four-minute window. Users received an error message that the engineering team had placed as a fallback for database errors: "Unable to save. Please try again in a moment." Many users retried. The retries also failed. By the time the rolling update completed and all pods were running the new application version, eleven users had submitted the same change order two or three times, and three users had abandoned the form. The engineering team discovered the failures in the error logs twenty minutes after the deploy when a support ticket arrived from a project manager who had received a duplicate approval request.

The investigation revealed the gap: the schema-first deploy order, which the team had used for three years without incident, had always been safe because all previous migrations were backward-compatible with the old application code — adding nullable columns that the old code would ignore, adding indexes that the old code would benefit from without knowing they existed, extending enum types with new values that the old code would not generate but also would not fail on. The NOT NULL foreign key was the first migration in three years that was not backward-compatible with the old application version. The team had a migration execution model — schema first, then application — but they had never examined its correctness guarantee, which was not "all migrations are safe during rolling updates" but "migrations that are backward-compatible with the old application version are safe during rolling updates." The constraint that the migration execution model required of the migration itself — backward compatibility with the deployed application version throughout the full rollout window — had never been written down as a decision, and the review that would have caught the NOT NULL column without default never happened because the team did not know such a review was necessary.

A 34-person SaaS company built a customer data platform — a tool for unifying customer profiles across marketing, sales, and support systems. The platform ran on PostgreSQL. The database schema had accumulated over three years and included several tables with column names inherited from early design decisions that had since been reconsidered. One table, the customer profile table, had a column named user_reference that the team had consistently wished was named customer_id to match the naming convention adopted after the first year. The rename was discussed several times in engineering planning sessions and deferred each time because it required a migration that touched a high-traffic table.

In the company's third year, a new senior engineer joined and offered to handle the rename as part of their onboarding technical work. The rename migration was designed as a three-step sequence in a single migration file: add a new column customer_id with the same type as user_reference; copy all existing values from user_reference to customer_id; drop the user_reference column. The migration was tested in development, applied to staging, and reviewed by a second engineer who confirmed the migration ran cleanly on the staging data. The migration was applied to production on a Tuesday morning during a low-traffic window. The application was simultaneously updated to reference customer_id instead of user_reference. The deploy completed. The migration was marked complete in the migration history table. The new naming convention was finally consistent throughout the schema.

Four hours after the deploy, the team discovered a data processing bug in the new application version. A query in the customer deduplication job was producing incorrect results because of an interaction between the renamed column and a column alias in a subquery that the code review had not caught. The bug was causing incorrect customer profile merges — a significant data quality issue in a customer data platform. The engineering team's immediate response was to roll back to the previous application version, which would restore the query to the version that had been working correctly before the deploy. The rollback of the application code completed in three minutes.

The rolled-back application version referenced user_reference, not customer_id. The production database no longer had a column named user_reference — it had been dropped as the third step of the migration. Every query that ran after the application rollback and referenced user_reference produced a column-does-not-exist error. The deduplication job that had been producing incorrect results was now failing entirely. Customer profile reads in the main application were returning errors on any query that touched the profile table. The rollback had not resolved the incident; it had replaced one bug with a complete service failure on the affected table.

The engineering team had two recovery paths. The first was to re-apply the new application version, accept the deduplication bug, and fix it forward as a patch — which meant the incorrect merges from the four hours the bug ran would need to be identified and reversed through a separate data remediation process. The second was to apply a corrective migration that added back the user_reference column, copied values from customer_id back to it, and re-created any indexes that had been on the original column — and then keep the rolled-back application version running until a patch for the deduplication bug was ready. The team chose the second path. The corrective migration took twenty-two minutes to write, test on staging, and apply to production. During the twenty-two minutes, the production service was in a degraded state: the main application was returning errors on any path that read or wrote the profile table, and the deduplication job was paused. The four hours of data written exclusively to customer_id — and not to user_reference — was recovered by the corrective migration's backfill step. No production data was lost. But the incident consumed six hours of engineering time across four engineers and left a remediation backlog of incorrect customer profile merges from the four-hour bug window that required two days of data quality work to identify and correct. The rename migration had never been reviewed against the question of what the rollback path would look like if the application needed to revert to the pre-migration version. The answer — "rollback requires a corrective forward migration and cannot be achieved by simply reverting the migration file" — was not written down, not communicated to the team, and not part of the migration review process.

A 41-person SaaS company built a workflow automation platform — a tool that connected enterprise business systems through trigger-and-action pipelines, similar to Zapier but focused on ERP and CRM integrations for mid-market manufacturers. The platform ran on PostgreSQL, with three environments: development (each engineer's local machine), staging (a shared pre-production environment), and production. Migrations were managed with a migration framework that tracked applied migrations in a schema_migrations table in each database. The CI pipeline ran all pending migrations against the staging database before every deploy to staging, and the production deploy pipeline ran pending migrations against the production database before the application update.

Over the course of eighteen months, the production database received three manual schema changes applied directly to the production database connection by engineers responding to incidents. The first was a composite index added to the workflow_executions table during a production performance incident — a query was causing table scans on a high-volume table and the on-call engineer added the index directly in a database console session at 2 a.m. to stop the performance degradation. The index was noted in the incident retrospective as something to "add to the migrations," but the follow-up was never completed. The second was a nullable retry_metadata column added to the pipeline_runs table when an engineer was debugging a production issue and needed to store diagnostic data temporarily — the column was intended to be temporary but was never removed. The third was an index dropped from the trigger_events table by an engineer who was investigating a deadlock and suspected the index was contributing to lock contention — the deadlock was resolved, the index was not restored, and the migration that had originally created the index remained in the migrations directory but the index no longer existed in the production database.

The three manual changes were not reflected in any migration file. The schema_migrations table recorded only the migrations applied through the migration framework — it had no record of the manually applied index, the manually added column, or the manually dropped index. The staging database, which received migrations exclusively through the CI pipeline, had a different schema from production: it was missing the composite index on workflow_executions, it was missing the retry_metadata column on pipeline_runs, and it had the index on trigger_events that production had lost.

Eighteen months after the first manual change, a feature team wrote a migration that added a composite index on workflow_executions to improve query performance for a new reporting feature. The migration was written, tested in development, applied to staging successfully, reviewed in a pull request, and approved. The production deploy pipeline ran the migration against the production database. The migration failed with a "relation already exists" error: the index already existed in the production database because it had been added manually eighteen months earlier. The production deploy stopped at the migration step. The new application version was not deployed. The CI/CD pipeline flagged the production deploy as failed. The on-call engineer received a PagerDuty alert at 3:47 p.m. on a Thursday.

The investigation required two hours to trace the cause: the composite index existed in production but not in the migrations directory, not in staging, and not in any migration file. Searching the incident retrospectives and the slack history from the performance incident eighteen months earlier eventually surfaced the note that the index had been added manually. The immediate fix was to modify the migration to use CREATE INDEX IF NOT EXISTS, which resolved the immediate deployment blocker. But the investigation surfaced the two other manual changes — the retry_metadata column and the missing index — revealing that staging and production had diverged in at least three places. The team did not know whether there were additional divergences they had not found. A full schema comparison between staging and production using pg_dump revealed four additional differences beyond the three known manual changes, including a column type change applied to production during a data migration that had not been propagated to staging. The team spent three days writing migrations to bring all environments into alignment with production as the canonical schema source. The schema drift had accumulated over eighteen months, one manual change at a time, each individual change made for a legitimate reason during a stressful operational moment, none of them documented in a way that the migration tool could track or the CI pipeline could detect.

Structural properties set by the schema migration decision

Three structural properties are determined when an engineering team establishes — or fails to establish — a database schema migration decision record: how safely the schema can change while the application is being deployed, how far the rollback path extends for migrations that have already written data to new schema structures, and how quickly schema drift between environments can be detected before it causes a production failure. None of these are labeled as decisions during the initial migration tooling setup — they emerge from the execution order conventions that became habits, the emergency manual changes that were never captured as migrations, and the assumptions about rollback that were never tested against destructive schema changes.

Property 1: The migration execution model and the zero-downtime constraint. The execution model for schema migrations determines whether structural schema changes can be applied during a rolling application update or require a planned downtime window. The constraint is not "can the migration be applied while the database is running" — it is "can the old application version run correctly against the new schema for the duration of the rolling update window." A migration that passes the first test can still fail the second: adding a NOT NULL column without a default passes the database execution test (the database accepts the migration) but fails the backward-compatibility test (the old application's INSERT statements omit the new column and receive constraint violations). The decisions never written down in the schema migration domain include not just which migration tool to use but what backward-compatibility requirement each migration must satisfy relative to the currently deployed application version — and what the review process is for catching migrations that violate the backward-compatibility requirement before they reach production. The new CTO onboarding problem in the migration domain is the incoming technical leader who asks "what is our process for zero-downtime schema changes?" and discovers the answer is "schema first, then application" — a process that is zero-downtime only for backward-compatible schema changes and that has never been validated for the class of structural changes that are not backward-compatible. The database migration strategy decision record connects at the tooling and organization layer: the strategy record covers which migration tool is in use, how migration files are named and organized, and how migrations are applied — the schema migration decision record covers what backward-compatibility requirements each migration must satisfy and what the execution model is for migrations that cannot satisfy those requirements without a coordinated deploy sequence.

Property 2: The rollback policy and the data loss window for destructive migrations. A schema migration that removes or renames data-bearing structures — columns, tables, or the association between old and new column names in a rename sequence — creates a window after which a schema rollback would discard data written exclusively to the new structure. The rollback window closes as soon as any application code writes data to the new structure and not the old one — which, in the case of a column rename, is the moment the new application version begins deploying and writing to the new column name. The team's expected rollback path — revert the application code to the previous version, revert the migration — produces a data loss if the new application version has been writing for any period of time, because the reverted schema removes the column that the new application was writing to. The rollback policy for destructive migrations must distinguish between migrations that can be reversed without data loss (those where all new data is being written to both old and new structures simultaneously, as in the expand phase of an expand-contract migration) and migrations that cannot be reversed without data loss once the old application version stops writing to the old structure. The release process decision record connects at the rollback protocol layer: the emergency deploy procedure specifies how quickly the team can roll back an application version; the schema migration rollback policy must align with this timeline and specify which class of migration can be rolled back by reverting the application version and which class requires a forward-only corrective migration; the release process runbook should explicitly cover the scenario of a failed deploy where a destructive migration has already been applied to production, so that the on-call team does not discover the rollback limitation during an active incident. The disaster recovery decision record connects at the schema version alignment layer: a database backup restored from a point-in-time snapshot will reflect the schema at the time the snapshot was taken, which may be an older schema than the current migration history; the schema migration decision record should specify how the restored database is brought forward to the current schema version as part of the recovery procedure, and how the recovery procedure handles a scenario where the current application version is not compatible with the schema version in the restored backup.

Property 3: The schema drift detection gap and the multi-environment divergence failure mode. The migration history table is a record of which migration files have been applied — it is not a record of the actual schema state. Any schema change applied outside the migration tool — a manual DDL statement run directly against the database, a migration file that was modified after being applied (so the file and the applied version diverge), or a migration applied to production but not to staging — creates a divergence between the migration history and the actual schema that the migration history table cannot detect. The schema drift detection gap is the interval between when the divergence is created and when it is discovered, which in practice is determined by whether schema comparison runs as part of the deployment pipeline or only manifests as a production deployment failure. The CI/CD pipeline decision record connects at the automated verification layer: the pipeline configuration determines whether a schema comparison check runs before each migration step, whether the comparison produces a blocking failure or an advisory warning, and how the comparison result is surfaced to the engineer approving the production deploy; without an automated comparison check, schema drift accumulates silently until a migration fails in production because it encounters unexpected schema state. The test data management decision record connects at the migration testing layer: migration correctness testing on realistic data volumes requires a test data set that covers the data patterns present in production — migration failures caused by constraint violations on existing data (for example, a NOT NULL addition that fails because some rows have null values) are only detected in a pre-production environment if that environment's data includes rows with the same null patterns as production; a test data management practice that produces sanitized production snapshots for staging exposes this class of migration failure before the migration runs in production. The database vendor decision record connects at the schema change tooling layer: different vendors offer different online schema change mechanisms with different lock behavior profiles; PostgreSQL's native support for concurrent index creation (CREATE INDEX CONCURRENTLY) and transactional DDL for most schema changes, MySQL's requirement for external tools like pt-online-schema-change for table alterations on large tables, and SQLite's limited ALTER TABLE support all shape what migration execution strategies are available and what the zero-downtime constraint looks like for a given stack; the schema migration decision record cannot be written without reference to the vendor's DDL concurrency model. The WhyChose extractor finds the schema migration discussions in your AI session history — the conversation where the first migration framework was configured and someone asked whether schema or application should deploy first, the incident retrospective where a manual index was added to production and the follow-up task to add a migration was assigned to nobody in particular, the planning session where a column rename was discussed and a senior engineer said "make sure you test the rollback" without specifying what testing the rollback meant for a destructive migration — and surfaces those discussions so you can evaluate which migration safety assumptions are still in place as your database size, deployment frequency, and environment count grow.

The schema migration ADR: five sections

Section 1: Migration execution model and backward-compatibility requirement. Specify the deployment ordering convention — whether migrations are applied before the application update, after, or in a coordinated sequence that depends on the migration type — and the backward-compatibility requirement that every migration must satisfy relative to the currently deployed application version. The backward-compatibility requirement is the binding constraint that determines when the standard schema-first execution order is safe and when a more complex execution sequence is required. Write the requirement explicitly: a migration is backward-compatible with the deployed application version if the deployed application can operate correctly against the new schema throughout the full rolling update window, without errors on any read or write path that the deployed application exercises. Apply this test to each migration during review: check every INSERT, UPDATE, and DELETE statement in the deployed application version for paths that reference tables affected by the migration. NOT NULL column additions without database-level defaults fail this test for any deployed code that inserts into the affected table without providing the new column value. Foreign key additions that the old code's INSERT statements do not supply fail this test. Column removals that old code reads or writes fail this test. Record the execution sequence required for migrations that do not pass the backward-compatibility test: the expand-contract pattern, which splits the migration into two phases each of which is independently backward-compatible, or a planned maintenance window with simultaneous migration-and-deploy coordinated to minimize the window during which old code runs against new schema. Connect this section to the release process decision record for the rolling update window duration — the length of time old and new application versions run simultaneously — which determines how long the backward-compatibility requirement must hold.

Section 2: Rollback policy for destructive migrations. Specify the rollback policy for each class of migration by the reversibility of the schema change after application code has deployed against it. Classify migrations into three categories: reversible migrations (those where the schema change can be undone without data loss regardless of how long the new schema has been live — adding an index can be dropped, adding a nullable column can be dropped if it contains only null values, adding a new table can be dropped if it contains no rows); conditionally reversible migrations (those where the schema change can be undone without data loss only within a specific window — before the new application code has written any data to the new structure exclusively — typically only the expand phase of an expand-contract migration); and forward-only migrations (those where the schema change cannot be undone without data loss once the new application code has been live for any period — dropping a column discards all its data, completing the contract phase of an expand-contract rename means the old column name's data is now in the new column name and the old column is gone). For forward-only migrations, specify what the recovery path is if the new application version deployed alongside the migration has a bug: the recovery path is a forward corrective migration, not a migration revert; the corrective migration must be designed and written before the forward-only migration is applied to production; the on-call runbook for the deployment must reference the corrective migration so that a post-deploy incident does not require the team to write a corrective migration under pressure. Connect this section to the database migration strategy decision record for the migration tooling's rollback support — whether the migration framework has a built-in down migration concept, whether down migrations are required for all migrations or only reversible ones, and how the migration tool handles the scenario where a down migration would produce data loss.

Section 3: Schema drift detection and multi-environment verification. Specify the schema drift detection procedure that runs on each environment before migrations are applied and on a recurring schedule to catch drift that accumulates between deployments. The detection procedure must compare the actual schema of each environment against a canonical schema source that represents the expected state — not just the migration history table, which records which migration files were applied but not manual changes applied outside the migration tool. The canonical schema source is either the schema output of a clean migration run (apply all migrations to an empty database and dump the resulting schema) or a version-controlled schema snapshot file that is updated as part of each migration commit (a file in the repository that contains the full CREATE TABLE definitions for every table, regenerated by the migration author as part of writing the migration). The comparison should be performed using the database's schema introspection tool — pg_dump --schema-only for PostgreSQL, mysqldump --no-data --no-create-info for schema-only MySQL output — and the diff should be reviewed before any migration is applied to the environment. Any difference between the actual schema and the canonical schema source is schema drift and must be resolved before the migration runs: either by writing a migration that captures the manual change and applies it to environments that don't have it, or by removing the manual change from environments where it should not exist. Specify the emergency procedure for direct database changes during incidents: manual schema changes applied during an incident must be captured in a migration file within 24 hours of the incident resolution, applied to all non-production environments, and documented in the incident retrospective. The migration file created after an emergency manual change must be idempotent — it must apply the change if the change is absent and do nothing if the change is already present — because it will be applied to environments that already have the manual change and environments that do not. Connect this section to the CI/CD pipeline decision record for the pipeline step that runs the schema comparison check and the configuration that makes it a blocking failure rather than an advisory warning.

Section 4: Migration testing requirements and pre-production verification. Specify what testing is required before a migration is applied to production and what environment it must be tested in. The testing requirements differ by migration class. For backward-compatibility testing: apply the migration to a staging environment that has a copy of the production schema, then run the current deployed application version against the post-migration staging database for long enough to exercise all write paths; any constraint violation, missing column error, or type mismatch in the deployed application version during this test is a backward-compatibility failure that must be resolved before the migration can be deployed using the standard rolling update order. For data-volume testing: apply the migration to a staging environment with a representative production data snapshot and measure the migration duration; a migration that takes forty seconds on a 100,000-row staging table may take forty minutes on a 50-million-row production table; the acceptable execution duration threshold — and the plan for migrations that exceed it — must be specified before the migration is approved; for large-table migrations, online schema change tools that avoid full-table lock acquisition must be used when the estimated duration exceeds the acceptable lock window. For correctness testing: verify that the post-migration schema state is exactly the expected state by running the canonical schema comparison against the staging database after the migration completes; any unexpected difference between the expected and actual schema after the migration indicates a migration that did not produce the intended result and must be investigated before proceeding to production. Connect this section to the test data management decision record for the procedure that produces a representative production data snapshot for the staging environment — the snapshot must be recent enough to include data patterns present in current production data, and sanitized enough to remove personally identifiable information before loading into a shared staging environment.

Section 5: Emergency migration procedure and production-only change protocol. Specify the procedure for schema changes that must be applied to production outside the normal migration pipeline — during active incidents, when a query plan change has caused a performance emergency, or when a constraint violation is blocking production operations. The procedure must balance the operational need for rapid schema changes during incidents against the drift accumulation risk of undocumented manual changes. The minimum acceptable protocol for emergency schema changes has three parts: authorization (who must approve a direct database change outside the migration pipeline — at minimum, the on-call lead and one additional engineer who can verify the change is limited in scope to what the incident requires); documentation (the change must be described in the incident channel in real time, with the exact DDL statement that was executed, the reason it was necessary, and the expected result — this creates a record that survives even if the migration file is never written); and migration capture (a migration file capturing the emergency change must be created within 24 hours and submitted for review, applied to all non-production environments, and committed to the repository before the next production deploy). Specify that the migration capture step for an emergency change must use IF NOT EXISTS, IF EXISTS, or equivalent idempotent DDL syntax so that the migration applies cleanly to environments that already received the change directly and to environments that did not. Specify the escalation path when an emergency schema change turns out to have introduced schema drift that is not straightforward to capture in a migration — for example, a manual type change on a large table that would require a full table rewrite to apply to staging — and how the team tracks and eventually resolves that drift. Connect this section to the disaster recovery decision record for the schema version alignment procedure used when restoring from a database backup: a restored backup may not have any emergency schema changes that were applied after the backup was taken, and the recovery procedure must include a step that identifies and re-applies any schema changes that are in the migrations directory but absent from the restored database's schema.

FAQ