The fourth front remains, and it is the one that breaks the most deployments. Everything built over three modules rests on a property the database schema does not have: the artifact reservalia/api:a3f9c21 is immutable and can be swapped for the previous one in four minutes, but a deleted column does not come back. The rollback.yml from 03-05 knows how to return to a previous digest; it does not know how to undo a DROP COLUMN, which is why that very lesson wrote it down as the only reason the go-back button might not work. This lesson closes that gap and, with it, the module. We will see why the schema is shared state and what that implies; how migrations are versioned and why they are run from the pipeline and never from Diego's laptop; we will develop the expand and contract pattern step by step over a real case — renaming start_time to start_utc without any service interruption; we will classify schema changes into safe, dangerous and forbidden while live; we will understand why an ALTER TABLE can bring production down and how to avoid it; we will place the migration in its exact spot within cd.yml; and we will finish with test data, anonymisation and a compliance warning worth reading in full.

1. The schema is shared state and it does not revert
2. Versioned migrations: convention, tool and control table
3. Why they run from the pipeline and never from a laptop
4. Expand and contract: the start_time → start_utc case
5. Classifying schema changes
6. Migrations and locks: how an ALTER TABLE brings production down
7. Batched backfill without saturating the database
8. Where the migration fits in cd.yml
9. Test data and anonymisation
10. Common Mistakes and Tips
11. Exercises
12. Conclusion and close of the module

Compare the two halves of the system Reservalia deploys:

The last row is the key to the whole lesson. During a rolling update from 03-04 — or during a canary at 10% — two versions of the code talking to a single database coexist in production. If the migration leaves the schema in a state the old version does not understand, half the requests fail for the duration of the deployment. And if the code has to be reverted, the old version will find a schema that is no longer its own. That is why rollback.yml does not save you from a destructive migration, and it is worth looking at the concrete case. Suppose a deployment runs ALTER TABLE appointments DROP COLUMN start_time and deploys the new code. Ten minutes later a serious failure is detected and Nuria launches the rollback: the previous image comes back in four minutes… and fails on every request, because its code asks for a column that no longer exists. The artifact rollback has completed successfully and the service is still down. Restoring a backup is not the answer either: it means losing every booking created since the DROP.

Nuria: "A deployment you cannot undo in five minutes is not a deployment, it is a bet." And a destructive migration turns any deployment into exactly that.

The conclusion that orders the rest: the schema must change in a way that breaks no deployed version, neither the new one nor the previous one. That is not a pipeline constraint, it is a design constraint on every change.

A migration is a schema change written as a versioned file in the repository, not as a command typed into psql. Reservalia keeps them in apps/api/src/db/migrations/ — the path that already appeared in the CODEOWNERS from 02-07, requiring review by Marta or Nuria — with this naming convention:

Timestamp + description in snake_case. The timestamp orders execution deterministically and avoids the classic problem of sequential numbers: two people working at the same time both create 005_, and the conflict only shows up on merging. With timestamps, two simultaneous migrations order themselves. The tool — node-pg-migrate at Reservalia, but the mechanism is universal (Flyway, Liquibase, Alembic, ActiveRecord) — maintains a control table inside the database itself:

1. The name as the primary key is what makes migration idempotent: on start-up, the tool compares the repository's files with this table's rows and runs only what is missing. Re-running the job repeats nothing, which is the requirement from 03-02.
2. The content hash catches the most dangerous mistake: somebody editing an already applied migration. If the file's hash does not match the recorded one, the tool stops with an explicit error. An applied migration is history: it is corrected with a new migration, never by editing the previous one, because the environment where it already ran will not read it again.
3. And duration_ms is not there out of curiosity. A migration that takes 200 ms in staging and 40 seconds in prod is telling you the table is much bigger and that the lock from section 6 is going to hurt.

In 01-04, the Friday ritual included Diego launching migrations by hand in psql connected to production. That has gone, and it is worth listing why it must not come back even "just this once":

The decisive argument is the third. The environment drift from 03-03 — the problem Terraform solved for infrastructure — exists in the schema too, and it is worse: a migration that ran in staging with one variant and in prod with another means the staging tests stop meaning anything, and nobody discovers it until the day of the incident. If the schema is not under version control, you do not have equivalent environments no matter how much Terraform you write.

Reservalia's real problem: the appointments.start_time column is a timestamp without a time zone, and with businesses starting to operate outside mainland Spain the calculateSlots calculations are going wrong around the clock changes. It has to move to start_utc of type timestamptz. On the face of it, that is an ALTER TABLE ... RENAME COLUMN. And doing it that way would break the service: at the instant of the rename, every task still running the old version starts failing.

The expand and contract pattern solves this by turning an incompatible change into a sequence of compatible changes. Four phases, each deployable and reversible separately:

Phase 1 · Expand. The new column is added without touching the old one, and a trigger keeps the two in sync while they coexist:

1. Nullable and with no default value: that is what makes this ALTER TABLE instantaneous and stops it rewriting the table (section 6).
2. The old code carries on writing start_time and the trigger fills in start_utc. The old version notices nothing.
3. The new code will write start_utc and the trigger will fill in start_time, so that if the code has to be reverted, the old column is up to date. This direction of the trigger is what makes the rollback safe, and it is precisely the one people forget. After this migration the old code, which writes start_time, and the new one once deployed, which will write start_utc, coexist without trouble.

Phase 2 · Migrate the data. The trigger only covers rows that get touched; the 640,000 historical appointments have to be filled in with a batched backfill (section 7). By the end, start_utc is complete. Phase 3 · Deploy the new code. Now, and not before, apps/api/src/domain/schedule.ts switches to reading and writing start_utc. The deployment is a normal rolling update from 03-04: for a few minutes the two versions coexist, and both work because both columns are complete and in sync. And here is the property that justifies the whole pattern: if the canary detects a problem, rollback.yml works without touching the database.

Phase 4 · Contract. Only once several days have passed, the new code is stable in prod and it has been verified that nobody reads the old column any more:

1. The NOT NULL constraint is added at the end, once you know there are no nulls. Putting it in phase 1 would have broken every write from the old code.
2. This is the only irreversible step in the process, which is why it goes in a separate migration, a week later and with its own review. Before running it, an objective check is advisable: search for start_time in the deployed code and check the logs for any query mentioning it in the last seven days.

The cost is real and should be stated: four pull requests and a week for what looked like a one-minute rename. In exchange, zero seconds of downtime and a rollback available at every point except the final five seconds of phase 4. For a table with 640,000 rows and 340 paying businesses, the trade is obvious.

Two nuances about the trickiest rows. ADD COLUMN with a default depends on the version and the kind of default: PostgreSQL 11 and later handle a constant default without rewriting the table, but a volatile default — now(), gen_random_uuid() — still forces every row to be rewritten under an exclusive lock. And the NOT VALID trick works for almost every constraint: you add the constraint without validating what already exists (instantaneous, and it already applies to new rows) and then run VALIDATE CONSTRAINT, which scans the table with a far gentler lock that allows writes to continue.

This is the mechanism worth really understanding, because it explains incidents that look inexplicable. An ALTER TABLE needs an exclusive lock (ACCESS EXCLUSIVE) on the table, and to get it, it has to wait for the in-flight transactions to finish. So far, nothing serious. The problem is what happens while it waits:

PostgreSQL's lock queue respects arrival order, so a waiting ALTER TABLE blocks everyone arriving after it, including plain SELECTs. The result is a complete outage caused by a migration that has not executed anything yet. Three defences, and all three are cheap:

1. lock_timeout makes the migration fail fast instead of blocking the queue. A clean failure after three seconds is infinitely better than an outage: you retry later, when there is no long query in flight. It is the line that prevents the most incidents in the whole lesson.
2. statement_timeout protects against the opposite case: a statement that did get the lock but spends ten minutes rewriting the table.
3. The third defence is CREATE INDEX CONCURRENTLY, essential on large tables: it builds the index without blocking writes, at the cost of taking longer and of two passes over the table. It has two peculiarities worth knowing: it cannot run inside a transaction — most migration tools wrap each file in one, so it has to be explicitly disabled for that migration — and if it fails it leaves an invalid index that has to be dropped by hand before retrying.

Filling in 640,000 rows with a single UPDATE is the other classic way of bringing production down: a giant transaction that locks rows for minutes, grows the WAL, drives up replica lag and saturates the CPU. The alternative is boring and it works: small batches, with a pause between them.

1. Batches of 1,000 rows and a 200 ms pause: each transaction lasts milliseconds and releases its locks straight away. The values are tuned by watching the p95 latency on the reservalia-api-prod dashboard while it runs.
2. FOR UPDATE SKIP LOCKED avoids waiting for rows another transaction is touching: they are skipped and picked up on the next pass. Without this, the backfill competes with real traffic.
3. The stop condition is "no rows left pending", not a counter. That makes the script resumable: if it is interrupted halfway, you relaunch it and it carries on where it was. It is the same idempotency from 03-02, applied to data.
4. The pause is what makes the backfill polite. Without it the loop saturates the database even though each batch is small. A backfill that takes two hours and nobody notices is preferable to a four-minute one that fires the ApiBookingLatency alert.

And two more rules: the backfill runs separately from the deployment, as an on-demand job, so that a two-hour process does not block the pipeline; and it is watched while it runs, with the dashboard open and a willingness to stop it, which is trivial because it is resumable.

The migration is a job of its own, before the code deployment and with its own rules:

1. Its own environment, prod-migrations, with its own list of reviewers. Approving a code deployment and approving a schema change are different decisions: the first is undone in four minutes, the second may never be undone. Separating them means the approver sees the SQL before saying yes.
2. migrate:status before applying prints which migrations are about to run. It is information for the approver and it stays in the run's log: the answer to "what was changed on the 15th?".
3. The timeouts are set per connection with PGOPTIONS, so they apply to every statement in the migration without having to write them into each file.
4. The migration goes before the code deployment, and this is only correct because the pattern in section 4 guarantees the new schema is compatible with the old code. If a migration does not have that property, the order does not save it: the problem is the migration.

What to do if it fails halfway. First, understand exactly what that means: the tool runs each file inside a transaction, so an individual migration is atomic, it either applies entirely or not at all. What is not atomic is the series: if there are three pending and the second fails, the first was applied and recorded. The applied_migrations table says exactly where it stopped. And the procedure, which is worth having written down before you need it: (1) do not retry blindly — if it failed on lock_timeout, retrying in a quiet moment is right; if it failed on a SQL error, retrying gives the same error; (2) the code deployment has not run, because deploy-prod depends on migrate-prod, so production is still on the previous version and the previous schema plus whatever was applied: if the pattern was respected, that is a functional state; (3) fix it with a new migration, never by editing the one that failed; and (4) record it as an incident in the incidents table from 03-06, because it counts towards the change failure rate. One special case: a CREATE INDEX CONCURRENTLY cannot go inside a transaction, so that migration is not atomic and, if it fails, leaves an invalid index that must be dropped before retrying. It deserves a comment inside the file itself for whoever finds it at three in the morning.

The quickest way to have a realistic staging is to copy the production database. You do not do it. And not just because of regulation:

The alternative is generating synthetic data with a script that creates businesses, opening hours and appointments with distributions similar to the real ones — including the hard cases: split opening hours, clock changes, overlapping bookings — but with invented data. The added advantage is that they are reproducible: the E2E tests from 02-04 can assume the demo business exists with a known schedule.

1. Clearly fake names: if something leaks into a log or a screenshot, it is instantly obvious that it is not real.
2. Phone numbers from an unassignable range, so an accidental SMS send reaches nobody.
3. Dates deliberately chosen around a clock change, which is exactly the case that motivated this lesson's migration.

If you do still have to start from real data — sometimes it is the only way to reproduce a performance problem at real volume — then anonymise before the data leaves production: replace names, phone numbers and email addresses with generated values, taking care to preserve the statistical properties that matter (how many appointments per business, what the hourly distribution is). And with two cautions: badly done anonymisation is reversible — a preserved identifier can reconstruct the identity — and the process must run in an environment as protected as production, because for a while it handles real data.

Warning. This section explains technical principles, not legal advice. The processing of personal data is regulated — GDPR in the European Union, plus additional sector rules if health data is involved — and decisions about what may be copied, anonymised or retained must be taken with the judgement of a data protection professional and your organisation's compliance officer. A technical course does not replace that review.

Mistake 1: doing a RENAME COLUMN or DROP COLUMN in a normal deployment. It breaks every task still running the previous version and leaves the rollback useless. Mistake 2: editing an already applied migration instead of writing a new one: the environment where it already ran will not read it again, and the environments diverge in silence.

Mistake 3: contracting on the same day you expand. The entire value of the pattern is in the wait between phase 3 and phase 4; without it you have only written more SQL for the same risk. Mistake 4: ALTER TABLE with no lock_timeout, which turns a waiting migration into a complete outage through the lock queue. Mistake 5: a backfill in a single UPDATE, with long locks, WAL blowing up and replica lag; do it in batches with a pause.

Mistake 6: CREATE INDEX without CONCURRENTLY on a large production table. Mistake 7: running migrations from a laptop "just this once", which is exactly how environments start to diverge. Mistake 8: copying production data to staging, with everything that implies in legal and leak risk.

Tip 1: review migration SQL with the same attention as code, with CODEOWNERS to guarantee it. Tip 2: rehearse the migration against a copy at production volume and note how long it takes; it is the only figure that predicts the lock. Tip 3: write into every destructive migration a comment with the date the column stopped being used and who verified it, and Tip 4: have the failure procedure written down before you need it.

Diego needs to add to appointments a status column of type text, mandatory and with the default value 'confirmed', on a table of 640,000 rows with live traffic. Write the complete sequence of migrations and deployments, saying which code versions coexist at each moment and at exactly which point rollback stops being possible.

At 10:15 on a Tuesday, a deployment runs CREATE INDEX idx_appointments_business ON appointments(business_id) on a table of 640,000 rows. After 40 seconds, the API stops responding completely, including requests that do not touch appointments. Explain the exact mechanism of the failure, why it affects requests unrelated to that table and how it would have been avoided.

A team wants to reproduce in staging a performance problem that only appears at real volume, and proposes restoring a production backup there "just for two days". Argue the answer and propose a concrete alternative that resolves the technical need.

Solution 1. The initial mistake would be a single migration with ADD COLUMN status text NOT NULL DEFAULT 'confirmed'. Even though the default is constant and modern PostgreSQL does not rewrite the table, the immediate NOT NULL breaks the old code, which does INSERTs without that column… and in fact it does not, because the default fills it in; the real problem is the inverse and subtler one: if the code later has to be reverted, nothing happens, but if the column were NOT NULL without a default, every INSERT from the old code would fail. The safe sequence, which works in both cases, is:

1. Migration 1 (expand): ALTER TABLE appointments ADD COLUMN status text; — nullable, no default. Instantaneous. Coexisting: old code (ignores the column) and new code if it were deployed. Rollback: trivial.
2. Batched backfill: fill in status = 'confirmed' on the 640,000 existing rows with the script from section 7. No long locks, resumable.
3. Deploy the new code: it always writes status and tolerates reading NULL on rows not yet filled in if the backfill is still running. Both versions coexist and both work; the rollback is still safe.
4. Migration 2 (harden): once the backfill has finished and the new code has been stable for days, ALTER TABLE appointments ALTER COLUMN status SET DEFAULT 'confirmed'; and then SET NOT NULL — preceded, on a large table, by a CHECK (status IS NOT NULL) NOT VALID + VALIDATE CONSTRAINT, so as not to scan the table under an exclusive lock.

Rollback stops being possible at step 4, and only partially: reverting the code still works — the old version simply ignores the column — but the NOT NULL prevents going back to code that inserted rows with no status. That is why step 4 goes separately and several days later.

Solution 2. The mechanism, in three stages. (1) CREATE INDEX without CONCURRENTLY takes a lock that prevents writes on appointments for the whole of the index build, which on 640,000 rows is tens of seconds. (2) Every write on appointments queues behind it. (3) Each queued request holds a connection from the pool; within forty seconds, at Tuesday morning traffic, the pool runs out. And there is the answer to why it affects requests unrelated to appointments: the connection pool is a resource shared by the whole application, so a request to /api/businesses that only reads another table cannot get a connection either and fails just the same. It is exactly the saturation signal 03-06 described as the only one that warns you beforehand: the ConnectionPoolHigh alert would have fired, although with a fifteen-minute window probably too late.

How it would have been avoided, in three layers: (a) CREATE INDEX CONCURRENTLY, which does not block writes — remembering that it cannot go inside a transaction and that if it fails it leaves an invalid index to drop; (b) SET lock_timeout = '3s', which would have made the migration fail cleanly instead of queueing the whole application, turning an outage into a red job; and (c) having rehearsed the migration against a copy at production volume, which would have shown the 40 seconds of build time before anything was touched. The first is the solution, the second is the safety net and the third is what avoids the surprise.

Solution 3. The answer is no, and the argument is not merely regulatory. Reservalia's appointment data includes names, phone numbers and sometimes the reason for the visit — potentially health data; staging has fewer access controls, more people with permissions and more verbose logs; and there is a specific, very concrete risk: if the environment has the SMS provider configured, a test cycle can send reminders to real customers, with the sms_reminders flag switched on without thinking. "Just two days" does not help either: two days become two months, and a staging backup taken during that window can survive for years. The alternative that does resolve the technical need — which is legitimate: reproducing a performance problem requires realistic volume — has three parts. (1) Generate synthetic volume: the script from section 9 scaled to 640,000 appointments with the same distributions as production (appointments per business, hourly concentration, proportion of split opening hours). Performance depends on the volume and shape of the data, not on the names being real. (2) If the problem depends on a very specific distribution, extract only the statistics from production — histograms, cardinalities — and use them to parameterise the generator; you take the numbers, not the data. (3) If none of the above is enough, an anonymised-at-source dump, inside the production perimeter, with irreversible replacement of every personal field, with an automatic expiry date on the environment, with external integrations disabled by configuration, and with prior approval from the data protection officer. It is the last resort, not the first, and it is precisely the decision a course cannot take for you.

The fourth front is closed. Reservalia no longer treats the schema as just another artifact: it knows it is shared state, that a single database serves every deployed version of the code at once, and that is why the rollback.yml from 03-05 — which returns a digest in four minutes — cannot undo a DROP COLUMN. Its migrations are versioned files in apps/api/src/db/migrations/, with a timestamp in the name, mandatory review through CODEOWNERS and an applied_migrations table that makes them idempotent and detects by hash if somebody edits one that has already been applied. They run from the pipeline and never from a laptop, in a migrate-prod job with its own approval environment — because approving code and approving a schema change are different decisions — with migrate:status in front of the approver and lock_timeout and statement_timeout set per connection. And above all, Reservalia knows how to design the change so that no version breaks. The expand and contract pattern turned the rename of start_time to start_utc into four compatible phases — expand with a bidirectional trigger, migrate the data in batches, deploy the code, and contract a week later — so that at every moment the rolling update from 03-04 and the canary coexist with a schema both versions understand and rollback remains possible. Around that pattern there is a clear classification of what is safe, dangerous and forbidden while live, with the alternative for each case; an understanding of why a waiting ALTER TABLE brings an entire application down through the lock queue and the connection pool; a batched, paused, resumable backfill; and a test data policy that rules out copying production and generates synthetic data, with the warning that this decision is taken with a data protection professional and not with a course.

With this, the four fronts module 3 left open are resolved:

And the number that sums it up, measured with the same yardstick as module 1's baseline: the change failure rate has come down from 6.5% to 3.8%, below the 5% target that was the only one still unmet. All four DORA metrics are now green — 12 deployments a week, 3.5 hours of lead time, 3.8% failures and 9 minutes of recovery — and the failures that remain are no longer process failures: they are the ones every team will always have, the ones you only discover when software meets real users.

Marta: "I would rather deploy ten times a day and have every deployment be boring." That was the goal from the very first lesson, and the work of these three modules consisted, precisely, of making boring what used to take three hours of a Friday.

What comes next changes register. Up to here we have built one pipeline for one product, taking at each point the decision that suited Reservalia: a Node monorepo, GitHub Actions, containers on ECS, trunk-based with squash. Every one of those decisions was reasoned, but none is universal. Module 5, Implementing CI/CD in Real Projects, takes what has been learned and subjects it to contexts that look nothing like Reservalia's: a mobile application with signing, stores and reviews that take days; a microservices system where the deployment graph has dozens of nodes; and a legacy project with no tests that has to be brought into CI/CD without being rewritten. It starts with the closest one, Case Study: Web Project, which lands the complete pipeline on a real web application from start to finish — including the apps/web part we have always treated as the API's companion — and serves as a bridge between the system we have built and the three cases that will put it to the test.