Managing Change: Process Changes, Equipment Swaps & Schema Evolution

📍 Where we are: Part VI, operating at scale. The platform runs, holds a tamper-evident audit trail (Chapter 20), and respects jurisdictional residency (Chapter 23). Now the process itself changes — and we must change the data without breaking the record.

A bioprocess platform is never finished. Six months after go-live the science team lifts the production pH setpoint by a tenth, the maintenance team swaps a worn-out Protein A skid for a newer model, and an instrument vendor ships a firmware update that quietly renames a column in the exported assay file. Each of these is, on the floor, a routine event. Each of them, in your database, is a chance to silently corrupt the historical record — to make BATCH-2026-001 look like it ran on a recipe it never ran, or to orphan three years of chromatography data behind a tag name that no longer exists.

This chapter treats change as what it really is: a first-class data problem with a regulatory deadline attached. We will version a recipe without overwriting history, swap a skid while keeping its genealogy intact, and migrate a changed data format with verification and a working rollback — all under the discipline that the audit trail must survive every one of these moves.

The simple version

Think of your database as a published book that regulators can re-read at any time. You are never allowed to erase a printed page. When the recipe changes, you do not paint over the old setpoint — you add a dated errata page that says "from 12 March, read pH 7.05 instead of 6.95," and the old page stays legible forever. When you replace a machine, you do not throw out its chapters — you write "this story continues in the new machine" and keep both. When the file format changes, you keep the old edition on the shelf until you have proven, line by line, that the new edition says exactly the same thing. Change control is just the rule that nobody edits the book without a signed, dated, reversible note saying what changed and why.

What this chapter covers

• Why change control is a GMP requirement, not a nicety, and how Annex 11, ICH Q10, and ICH Q12 frame process, equipment, and data changes.
• Effective-dated recipes: versioning a setpoint in place using valid_from/valid_to, and the PostgreSQL exclusion constraint that stops two versions from overlapping in time.
• Reversible, validated schema migrations with Sqitch (and how Flyway compares) so the schema evolves without a break in the chain.
• Swapping a skid or instrument while preserving genealogy and re-mapping tags so years of history stay joinable.
• A data-format migration — legacy CSV to Parquet — with byte-level verification and a rollback path, plus where lakeFS/DVC fit.
• Why pure open source gets you most of the way here, and where the GxP last mile stays hybrid.

Change is a regulated event

Before any code, the framing. In a GMP shop you are not free to change a production system on a whim. Annex 11 of the EU GMP guidelines — the European counterpart to Part 11 — is explicit: computerised systems must run a documented change and configuration management process (clause 10), and when data is transferred to another format or system, that migration must be checked to confirm the value and meaning of the data were not altered (clause 4.8) [1]. ICH Q10 makes this structural rather than incidental: a change management system is one of the four named elements of a pharmaceutical quality system, sitting alongside process performance monitoring, corrective action, and management review [2]. And ICH Q12 gives the post-approval machinery — Established Conditions that define what is legally fixed, and Post-Approval Change Management Protocols (PACMP) that pre-agree how a future change will be made and reported [3].

For us, three engineering rules fall straight out of those documents:

1. Never destroy history. A change adds a new, dated truth; it does not overwrite the old one. The FDA's data-integrity guidance defines the audit trail as a secure, computer-generated, time-stamped record that allows reconstruction of the creation, modification, and deletion of a record — a property every migration and equipment swap must preserve, not break [4].
2. Make every change reversible. If a migration fails verification, you must be able to return to the prior, known-good state.
3. Prove old data is still readable. PIC/S PI 041-1 is direct here: when software is updated, the firm must confirm that old data can still be read — either in its existing format or by a validated migration to a new one — and must retain the old system where migration is not possible [5].

The rest of the chapter is those three rules, in SQL and Python.

Effective-dating a recipe, for real

Recall the recipe-parameter table from Chapter 3. It was built effective-dated from day one precisely so this chapter could exist. From examples/platform/db/10-isa88-95.sql:

-- examples/platform/db/10-isa88-95.sql  (effective-dated recipe parameters)
-- effective-dated recipe parameters (Ch 24 versions these in place)
CREATE TABLE s88.recipe_parameter (
    recipe_id  text NOT NULL REFERENCES s88.recipe,
    name       text NOT NULL,
    value      numeric NOT NULL,
    unit       text NOT NULL,
    valid_from timestamptz NOT NULL DEFAULT now(),
    valid_to   timestamptz NOT NULL DEFAULT 'infinity',
    PRIMARY KEY (recipe_id, name, valid_from)
);

The whole point of valid_from/valid_to is that a recipe change is an INSERT plus an UPDATE, never a destructive UPDATE alone. Suppose on 12 March 2026 the science team raises the production-phase pH setpoint from 6.95 to 7.05 under change control CC-2026-018. The correct move is to close the old row by setting its valid_to, then open a new row:

-- close the outgoing version at the effective instant, open the new one
UPDATE s88.recipe_parameter
   SET valid_to = '2026-03-12T00:00:00Z'
 WHERE recipe_id = 'CHO-MAB-001' AND name = 'pH_setpoint'
   AND valid_to = 'infinity';

INSERT INTO s88.recipe_parameter (recipe_id, name, value, unit, valid_from, valid_to)
VALUES ('CHO-MAB-001', 'pH_setpoint', 7.05, 'pH', '2026-03-12T00:00:00Z', 'infinity');

The history now reads cleanly. The question "what pH setpoint applied when BATCH-2026-001 started, back on 5 January?" is a point-in-time query, and it returns 6.95 because that is what was true then:

SELECT value, unit
  FROM s88.recipe_parameter
 WHERE recipe_id = 'CHO-MAB-001' AND name = 'pH_setpoint'
   AND '2026-01-05T00:00:00Z' >= valid_from
   AND '2026-01-05T00:00:00Z' <  valid_to;
--  value | unit
-- -------+------
--   6.95 | pH

Two rows now exist for the same parameter, and that is exactly right — both are true, each in its own window. A batch keeps showing the setpoint that governed it, and an auditor can reconstruct the recipe as of any date without a separate archive.

Stopping overlaps at the database

There is a subtle failure mode: a fat-fingered migration could leave two rows whose [valid_from, valid_to) windows overlap, and now the point-in-time query returns two pHs and the model lies. A plain UNIQUE constraint cannot catch this, because the conflict is not equality — it is range overlap. PostgreSQL's answer is an exclusion constraint, which uses a GiST index to guarantee that no two rows satisfying the predicate can have overlapping values under an operator you choose [6]. Paired with a range type — tstzrange and friends model a span with inclusive/exclusive bounds and an overlap operator && — it does exactly what we need; the documentation is explicit that UNIQUE is unsuitable for ranges while an exclusion constraint enforcing non-overlap is the right pattern [7].

In the companion stack we add this as a migration (below) rather than baking it into the day-one schema, because it is precisely the kind of integrity tightening that arrives after go-live. Because the equality predicates on recipe_id and name share the index with a range overlap, the GiST index needs the btree_gist extension — without it PostgreSQL rejects the constraint with "text has no default operator class for access method gist" — so the migration enables it first:

-- examples/platform/db/migrations/deploy/recipe_param_no_overlap.sql
-- btree_gist lets the text equality predicates share one GiST index with the range overlap
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- a GiST exclusion constraint: no two versions of the same parameter may overlap in time
ALTER TABLE s88.recipe_parameter
  ADD CONSTRAINT recipe_parameter_no_overlap
  EXCLUDE USING gist (
    recipe_id WITH =,
    name      WITH =,
    tstzrange(valid_from, valid_to, '[)') WITH &&
  );

Now the database itself refuses to accept a second pH_setpoint row whose window touches an existing one. The bitemporal discipline stops being a convention engineers must remember and becomes a rule the engine enforces — which is exactly the posture a data-integrity reviewer wants to see.

Reversible, validated migrations with Sqitch

Adding that constraint is a schema change, and schema changes need the same change-control rigour as recipe changes. The companion repo manages them with Sqitch, a database-change framework whose entire model is built around the three rules above. Each change is a named trio of scripts — a deploy that applies it, a revert that undoes it, and a verify that asserts it actually took. By default sqitch deploy does not run verify scripts; with sqitch deploy --verify (or with deploy.verify enabled in sqitch.conf) Sqitch runs each verify during the deploy and reverts the change in the same run if a verify fails [8]. The companion sqitch.conf enables deploy.verify, so the gate is on by default for the reader. Sqitch is MIT-licensed, which is why the book ships it rather than the commercial-adjacent alternative.

The migration directory lives at examples/platform/db/migrations, managed by Sqitch, and ships a real sqitch.conf and sqitch.plan with the recipe_param_no_overlap change committed. A change is added with sqitch add recipe_param_no_overlap -n 'enforce non-overlapping recipe versions', which scaffolds the trio. The deploy script holds the ALTER TABLE shown above; the revert and verify are its bookends. The committed scripts (Sqitch wraps each change in its own transaction on PostgreSQL, so there is no explicit BEGIN/COMMIT — that would interfere with the auto-revert this section relies on):

-- examples/platform/db/migrations/deploy/recipe_param_no_overlap.sql
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE s88.recipe_parameter
  ADD CONSTRAINT recipe_parameter_no_overlap
  EXCLUDE USING gist (
    recipe_id WITH =, name WITH =,
    tstzrange(valid_from, valid_to, '[)') WITH &&
  );

-- examples/platform/db/migrations/revert/recipe_param_no_overlap.sql   (the reversibility rule, in one line)
ALTER TABLE s88.recipe_parameter DROP CONSTRAINT recipe_parameter_no_overlap;

-- examples/platform/db/migrations/verify/recipe_param_no_overlap.sql   (assert the change actually took)
SELECT 1 / CASE WHEN count(*) = 1 THEN 1 ELSE 0 END  -- divides by zero (fails) unless the constraint exists
  FROM pg_constraint
 WHERE conname = 'recipe_parameter_no_overlap';

The operator runs sqitch deploy --verify db:pg://..., and Sqitch applies the deploy, immediately runs the verify, and — if the verify raises — reverts in the same transaction so the database is never left half-changed. To back the change out deliberately you run sqitch revert --to @HEAD^1. This is the engineering expression of "reversible, validated": every forward step has a tested backward step, and the verify is a gate, not a hope.

It is worth being honest about the alternative. Flyway applies versioned migrations exactly once, fingerprints each with a checksum so an already-applied script cannot be silently edited, and offers paired Undo (U-prefixed) scripts — but its own documentation cautions that undo plus a restorable backup are both needed for true reversibility, because some DDL is not cleanly undoable [9]. That caveat applies to Sqitch too. The mature posture is: reversible migration script and a point-in-time-recovery backup taken immediately before the change. We configure that backup in the next chapter.

Swapping a skid without orphaning history

The hardest change is physical. In March, PA01 — the Cytiva ÄKTA process Protein A skid seeded back in Chapter 3 — is retired and replaced by a newer unit, PA02. Three things must remain true after the swap: every old batch must still point at the equipment that actually made it; new batches must point at the new skid; and the time-series tags from the old skid must remain joinable to the years of history they carry.

The equipment hierarchy makes the first two trivial, because unit_id is a stable business key and batches reference it. We never rename PA01; we retire it and add PA02:

-- retire the old skid (keep the row — old batches still reference it), add the new one
INSERT INTO s88.unit VALUES
  ('PA02', 'DOWNSTREAM', 'Protein A Capture Skid 2', 'chromatography', 'Cytiva', 'AKTA pcc 80')
  ON CONFLICT DO NOTHING;

-- record the equipment lineage so reports know PA02 succeeded PA01
INSERT INTO s88.genealogy (batch_id, child, child_type, parent, parent_type)
VALUES (NULL, 'PA02', 'equipment', 'PA01', 'equipment');

BATCH-2026-001 keeps pointing at PA01; the April batches point at PA02; and the genealogy edge records that PA02 succeeded PA01 so an equipment-history report can walk the lineage. Nothing was overwritten.

The genuinely fiddly part is tag re-mapping. The old skid published tags like PA01.UV280.PV; the new skid publishes PA02.UV280.PV. The governed tag dictionary from Chapter 4 (gov.tag_dictionary) is the single place that decides what is a legal tag — but as shipped (examples/platform/db/40-gov.sql) it keys on tag and carries no retire or effective-date columns, so it cannot by itself express "this signal used to be called something else." The swap therefore needs a companion table, added as a Sqitch migration the reader deploys (examples/platform/db/migrations/deploy/tag_alias.sql):

-- examples/platform/db/migrations/deploy/tag_alias.sql  (records old->new tag correspondence)
CREATE TABLE gov.tag_alias (
    old_tag   text NOT NULL,                 -- PA01.UV280.PV
    new_tag   text NOT NULL,                 -- PA02.UV280.PV
    effective timestamptz NOT NULL,          -- when the new skid took over
    reason    text,                          -- e.g. CC-2026-024 (skid swap)
    PRIMARY KEY (old_tag, new_tag)
);

The new tags are registered in the dictionary and the old ones are left in place (the dictionary has no retire column, and the old rows must stay so historic tags remain legal). The companion repo carries a small remapper, examples/tools/tag-remap/tag_remap.py, that reads an old→new mapping CSV, validates it, and applies it to gov.tag_alias (cloning the old tag's governed metadata onto the new tag). The idea of "one physical signal, several names over time" is the same logical-asset aliasing ISA-95 Part 7 formalises in its Alias Service Model, exercised in the naming-and-UNS chapter. The mapping file itself is plain, reviewable data:

# examples/tools/tag-remap/remap_PA01_to_PA02.csv  (old_tag,new_tag,effective,reason)
old_tag,new_tag,effective,reason
PA01.UV280.PV,PA02.UV280.PV,2026-03-15T00:00:00Z,CC-2026-024 skid swap
PA01.Cond.PV,PA02.Cond.PV,2026-03-15T00:00:00Z,CC-2026-024 skid swap
PA01.pH.PV,PA02.pH.PV,2026-03-15T00:00:00Z,CC-2026-024 skid swap

Crucially, we do not rewrite the historian. The 18 months of PA01.UV280.PV rows in ts.sensor_reading stay exactly as recorded — rewriting them would be the very destruction of history the audit trail forbids. Instead the alias table lets a cross-changeover query resolve both names to one logical measurement:

-- read 'Protein A UV280' across the swap without rewriting a single historic row
SELECT ts, value
  FROM ts.sensor_reading
 WHERE tag IN ('PA01.UV280.PV', 'PA02.UV280.PV')   -- old + new, joined via gov.tag_alias
 ORDER BY ts;

History is preserved, the new skid is live, and a query that needs "the Protein A UV trace" no longer has to know that a skid was swapped on 15 March. That is the difference between a platform that ages gracefully and one that accretes scar tissue.

Three kinds of change — a versioned recipe, a swapped skid, and a migrated data format — each handled by adding a dated truth rather than erasing an old one, so the audit trail (the unbroken line beneath) survives every move.

Original diagram by the authors, created with AI assistance.

Migrating a data format, with verification and rollback

The last and most error-prone change is a data-format migration. An instrument vendor's firmware update changes the offline-assay export from a loosely typed legacy CSV to a self-describing columnar file, and we want to standardise the historical archive on Apache Parquet — partly for size and speed, partly because Parquet files embed their own schema in Thrift metadata, so the file is its own documentation and supports schema evolution and verifiable round-tripping [10]. PIC/S is unambiguous that this is allowed only with a validated migration that proves the value and meaning of the data are unchanged, and that the old format is retained until that proof exists [5].

The companion repo's examples/tools/format-migrate/format_migrate.py follows a strict convert-then-verify-then-promote sequence, and it refuses to delete the source. The shape of it:

# examples/tools/format-migrate/format_migrate.py  (convert -> verify -> promote; never delete source)
import pandas as pd

def migrate(csv_path: str, parquet_path: str) -> None:
    src = pd.read_csv(csv_path, dtype={"sample_id": "string", "batch_id": "string"})
    src.to_parquet(parquet_path, engine="pyarrow", index=False)

    # VERIFY: read the new file back and assert it is value-identical to the source
    back = pd.read_parquet(parquet_path)
    assert list(back.columns) == list(src.columns), "schema drift on migration"
    pd.testing.assert_frame_equal(
        src.reset_index(drop=True), back.reset_index(drop=True),
        check_dtype=False,            # CSV is untyped; compare values, not storage dtype
    )
    # ROLLBACK is implicit: the source CSV is never touched, so failure leaves it intact.

Verification is the heart of it. We read the freshly written Parquet back, assert the column set is unchanged, and assert every value round-trips. If assert_frame_equal raises, the migration aborts and the original CSV is untouched — rollback is "do nothing destructive in the first place." Only after verification passes does the tool's --promote step move the CSV into a retained-archive location; it is never deleted. The first rows of the real source — which the Parquet must reproduce byte-for-byte in value — look like this:

# examples/datasets/offline_assays.csv  (first rows; identical values after migration to Parquet)
sample_id,batch_id,sample_time,sample_point,VCD_e6_per_mL,viability_pct,glucose_g_L,lactate_g_L,glutamine_mM,ammonia_mM,osmolality_mOsm_kg,titer_g_L,pH_offline
BATCH-2026-001-OFF-001,BATCH-2026-001,2026-01-05 06:00:00+00:00,BR101,0.34,96.6,6.18,0.13,4.13,0.68,293,0.002,7.06
BATCH-2026-001-OFF-002,BATCH-2026-001,2026-01-05 18:00:00+00:00,BR101,0.43,96.6,6.26,0.19,4.31,0.38,292,0.008,7.04
BATCH-2026-001-OFF-003,BATCH-2026-001,2026-01-06 06:00:00+00:00,BR101,0.56,99.0,6.01,0.32,3.83,0.45,287,0.014,7.05

For migrations at the dataset scale — many files in object storage rather than rows in Postgres — the book reaches for Git-like data versioning. lakeFS gives object-storage datasets commit/branch/merge/revert with zero-copy branching, so you stage the whole format migration on a branch, run verification against it, and either merge it or revert to the prior immutable commit if the verify fails — a true, atomic rollback for terabytes [11]. DVC takes a lighter approach, capturing each dataset version as a small .dvc pointer file committed to Git, so the history of the data lives beside the history of the code and you can git checkout your way back to the exact prior content [12]. Both preserve dataset genealogy across change; lakeFS suits a shared S3-style store, DVC suits a repo-centric workflow. Either way the principle is identical to the SQL migrations: stage, verify, then promote — and keep a path back.

Why it matters

Every other capability in this book — the historian, the contextualization views, the knowledge graph, the soft-sensor — assumes the data underneath it is stable and truthful. Change is where that assumption goes to die. A recipe setpoint overwritten in place makes every historical batch report subtly wrong. A skid renamed instead of retired orphans years of chromatography traces. A format migration without verification can transpose two columns and nobody notices until a regulator does. The techniques here — effective-dating, exclusion constraints, reversible migrations, alias-based tag re-mapping, convert-verify-promote — are not gold-plating. They are the difference between a platform a quality unit will trust and one they will quarantine. And because every one of them adds a dated truth rather than erasing an old one, the audit trail you built in Chapter 20 survives intact through every change, which is precisely what Annex 11, ICH Q10, and the data-integrity guidance demand.

In the real world

In a validated GMP environment, none of these changes happens because an engineer feels like it. Each is preceded by a change-control record — a quality-managed document that states what is changing, the risk assessment, the validation impact, the approvals, and the back-out plan — and that is true whether your stack is open source or a wall of commercial systems. ICH Q12's Established Conditions and PACMP machinery exist precisely so that recurring changes (a feed-strategy tweak, a column resin re-qualification) can be pre-agreed with the regulator rather than re-litigated each time [3]. The tooling shown here implements the technical half of that; the SOPs, approvals, and validation deliverables are the operator's burden and are not something any download confers.

The honest open-source verdict for this chapter is comparatively kind. Schema migration is one area where OSS is genuinely strong: Sqitch and Flyway are mature, widely used, and produce exactly the deploy/revert/verify evidence a validation lifecycle wants — Flyway's checksumming even gives you tamper-evidence on the migration scripts themselves. PostgreSQL's range types and exclusion constraints are a first-class, no-extension answer to effective-dating that many expensive systems lack. The places where pure OSS still falls short are the familiar ones: the change-control workflow itself (electronic approvals, e-signatures on the change record, linkage to a validated quality system) is not something Sqitch provides — that lives in a commercial quality-management system or the signing-service-plus-Keycloak hybrid the book builds in Chapter 21 — and automatic, validated point-in-time recovery as the safety net behind every migration leans on the backup machinery configured in the next chapter. NIIMBL's standards-based posture and pilot-scale cGMP facilities such as SABRE — the NIIMBL / University of Delaware facility that broke ground in April 2024 — depend on exactly this kind of versioned, reversible change management, because a shared, multi-partner data platform that cannot evolve without breaking its own history is not a platform anyone will build a process on. (cGMP here is current Good Manufacturing Practice, the regulatory expectation that production changes are controlled and documented.)

Key terms

• Change control — the GMP-mandated, quality-managed process for proposing, assessing, approving, and recording any change to a validated system or process. An element of the pharmaceutical quality system under ICH Q10.
• Effective-dating (bitemporal versioning) — storing a value with valid_from/valid_to so a recipe or mapping can be versioned without overwriting history; a point-in-time query returns whatever was true on a given date.
• Exclusion constraint — a PostgreSQL constraint (EXCLUDE USING gist ... WITH &&) that, unlike UNIQUE, forbids two rows whose time ranges overlap — the database-level guard for non-overlapping versions.
• Range type — PostgreSQL's tstzrange/daterange types modeling a span with bounds and an overlap operator, the basis for effective-dated validity periods.
• Sqitch — an MIT-licensed database-change framework; each change is a paired deploy/revert/verify trio, verified during deploy and auto-reverted on failure.
• Flyway — a versioned-migration tool that applies each script once with a checksum and offers U-prefixed Undo migrations (but advises pairing undo with a restorable backup).
• Established Conditions / PACMP — ICH Q12 mechanisms defining what is legally fixed in a process and pre-agreeing how future changes will be made and reported.
• Tag re-mapping / alias — recording an old→new tag correspondence (per ISA-95 Part 7's Alias Service Model) so a measurement keeps one logical identity across an equipment swap, without rewriting historic readings.
• Apache Parquet — a self-describing columnar file format that embeds its own schema, enabling schema evolution and verifiable format migration.
• lakeFS / DVC — Git-like versioning for datasets: lakeFS gives commit/branch/revert over object storage with zero-copy branching; DVC tracks data versions via lightweight .dvc pointer files in Git.
• Convert-verify-promote — the safe data-migration pattern: write the new format, read it back and assert value-identity, and only then promote it, never deleting the validated source.

Where this leads

The platform can now evolve — recipes versioned, skids swapped, formats migrated — without ever breaking the record. But evolution is only half of "operating at scale." A change is only as safe as the backup behind it, the monitoring that catches a failed migration at 3 a.m., the network segmentation that keeps the OT side isolated, and the supply-chain discipline that keeps a pinned image from becoming a vulnerability. In the next chapter, Operating, Scaling & Securing the Platform, we turn this from a thing that runs on a laptop into a thing you could responsibly run in production: backup and point-in-time recovery, TLS and zone-and-conduit segmentation, self-monitoring, and the CVE-watch runbook that treats even the security scanners as validated suppliers.