Capstone: One Batch, End to End

📍 Where we are: the last working chapter — we run a complete simulated fed-batch through every layer we built and end with one reviewable, signed batch record.

The simple version

Imagine you have spent the whole book building a factory's plumbing — pipes (sensors), tanks (the historian), labels on every valve (the namespace), a logbook that can't be erased (the audit trail), and a control room full of dashboards. The capstone is the day you finally turn the water on and watch one full batch flow from the inlet to a sealed, signed bottle at the far end. Nothing new gets built. We just press one button per step and prove the pipes actually connect — and that what comes out the other end is something a quality reviewer could sign.

What this chapter covers

This is the moment the platform stops being a pile of services and becomes a system. We take one deterministic, 14-day simulated fed-batch CHO + Protein A monoclonal-antibody (mAb) run and push it through the entire stack we assembled chapter by chapter: simulator → historian → ISA-88/95 batch model → contextualization → lab results → an ALCOA+ audit chain → the Raman→titer soft-sensor → a reviewable, FAIR-aligned dataset. We will run the real command sequence from the companion repo, look at the real SQL and Python that does the joining, and end on the honest question every reader has been waiting for: is this a batch record a regulator would accept? (Short answer: the data is; the validated system around it is the hybrid last mile — the residual slice where open-source tooling stops and you still need commercial, formally validated systems and signed procedures to satisfy a regulator. We use that phrase throughout, and the next chapter scores exactly where that line falls.)

The running case is the classic one. We use the industry's canonical worked example — an IgG1 mAb produced by fed-batch CHO culture with a Protein A capture step and a Quality-by-Design control strategy linking critical quality attributes to process parameters [8]. Everything below is one batch, BATCH-2026-001.

If fed-batch CHO culture, the Protein A capture step, and the IgG1 mAb itself are new terms, they are the physical process explained in Book 1, Biologic Drug Manufacturing — the production bioreactor and Protein A capture; here we only need that this is the run whose data we assemble.

One command surface: Make as the orchestrator

We never invented a bespoke pipeline engine. The book's whole "every claim is runnable" promise rests on the reader typing the exact commands the book prints, so the orchestrator is the oldest, most boring, most dependable tool we have: GNU Make. Make is a dependency-driven build tool — you declare targets and what they need, and it runs them in order [9]. For a laptop-scale, reproducible, end-to-end run that is exactly enough; for larger, branching scientific pipelines the same idea scales up into Python-based, dependency-aware workflow engines such as Snakemake, which carry the reproducibility story from a workstation to a cluster [10]. We stay with Make because the reader already has it and it keeps the command surface honest.

Here is the real top-level Makefile in examples/Makefile. Notice that every target documents the Docker Compose profile it needs, and that help auto-lists the targets from their ## comments — the command surface is the documentation.

# examples/Makefile
COMPOSE := docker compose -f platform/compose/compose.yaml
PY := sim/.venv/bin/python
export DATABASE_URL ?= postgresql://bioproc:bioproc@localhost:5432/bioproc

venv: ## create the Python env and install the simulator (uv)
	cd sim && uv venv --python 3.12 .venv && uv pip install --python .venv -e . "psycopg[binary]" "asyncua==2.0" scikit-learn

up: ## bring up the core stack (postgres+timescale, mosquitto, grafana)
	$(COMPOSE) --profile core up -d
	@echo "waiting for postgres..." && sleep 3
	@until docker exec bioprocess-data-stack-postgres-1 pg_isready -U bioproc >/dev/null 2>&1; do sleep 2; done
	@echo "core stack up."

seed: ## load the ISA-88/95 reference CHO line into postgres
	docker exec -i -e PGPASSWORD=bioproc bioprocess-data-stack-postgres-1 \
	  psql -U bioproc -d bioproc -q < platform/db/seed/seed_cho_line.sql

data: ## (re)generate every dataset deterministically + MANIFEST.sha256
	$(PY) -m bioproc_sim.generate --all

load: ## load the datasets into the running stack (historian + lab + genealogy)
	$(PY) tools/load_datasets.py

The end-to-end run is just those targets in order, followed by the contextualization, audit, and analytics targets. This is the literal capstone sequence, copied from the repo's README.md:

make venv          # Python env + the simulator (uv)
make data          # generate every dataset deterministically + MANIFEST.sha256
make up            # bring up the core stack (postgres+timescale, mosquitto, grafana)
make seed          # load the ISA-88/95 reference CHO line
make load          # load the datasets into the historian + lab tables
make contextualize # join time-series to batch phases (Ch 17)
make alcoa         # verify the ALCOA+ audit hash chain (0 = intact)
make soft-sensor   # train the Raman -> titer PLS soft-sensor (Ch 29)
make test          # the whole suite (determinism + db + analytics)

Each line is a layer from the book. Watch the data move.

Step 1 — generate the batch deterministically (make data)

make data runs python -m bioproc_sim.generate --all with the master seed SIM_SEED=2026, so the 14-day trace is byte-for-byte identical on your laptop and on the continuous-integration (CI) runner — the automated build server that re-runs every check on each change. This matters more than it sounds: reproducibility is the difference between "a demo that worked once" and an artifact you can re-derive on demand — the Findable/Accessible/Reusable spine of FAIR (Findable, Accessible, Interoperable, Reusable) data [11]. This chapter establishes the Findable/Accessible/Reusable spine; the Interoperable leg — turning these rows into a shared-vocabulary RDF knowledge graph — is delivered separately, where the same batch is loaded into a graph in Semantics & the Digital Thread. The generator writes a MANIFEST.sha256 and make test later asserts every file matches it. If a simulator drifts by a single float, CI fails loudly.

This single, deterministic source is also why the whole batch agrees with itself: the in-line titer (the antibody concentration the cells have made, in g/L), the offline assay titer, and the Raman spectra are all drawn from the same underlying kinetic state. That internal consistency is the "Consistent" in ALCOA+ — not a happy accident but a property of the design [3].

Determinism as a property, not a hope

"Reproducible" is easy to say and hard to keep. The capstone makes it a checked property in two layers. First, every generator draws from a single master seed: bioproc_sim.generate derives its randomness from SIM_SEED (default 2026), so two runs on two machines produce identical arrays — the simulator's own test asserts exactly that, np.array_equal(a, b) over two independent generations. Second, the generator hashes every file it writes — hashlib.sha256(p.read_bytes()).hexdigest() — into a MANIFEST.sha256, and make test re-derives the datasets and compares them against that manifest. A drift of a single float in any dataset changes a digest, the comparison fails, and CI goes red.

That is a meaningful design choice, not a flourish. It means the reviewable record is not just a dataset but the dataset — anyone can re-run make data and get byte-for-byte the same BATCH-2026-001, which is the Reusable spine of FAIR made operational [11]. It also draws the honest line: the simulator is deterministic because it is a simulator. A real instrument is not byte-reproducible; what transfers to a real plant is the discipline — fixed inputs, hashed outputs, an automated check — not the literal repeatability of the physics.

Step 2 — stand up the record-of-truth and load it (make up, make seed, make load)

make up boots the core stack — PostgreSQL + TimescaleDB, Mosquitto, Grafana — and blocks until Postgres answers pg_isready. PostgreSQL is the relational store that holds the contextualized, signed records and the audit trail; it is the system of record the reviewable batch record is assembled from [12]. make seed applies the ISA-88/95 reference CHO line: the enterprise→site→area→unit hierarchy, the recipe and its phases, and the BR101 production bioreactor.

make load runs the dataset loader in examples/tools/load_datasets.py. This is the one script that does, in a single pass, what Chapters 7–16 build up piece by piece: high-rate sensor readings stream into the historian by bulk COPY, and offline lab results land through the normal INSERT path so the audit trigger actually fires on each one.

# examples/tools/load_datasets.py
def load_timeseries(conn) -> int:
    df = pd.read_parquet(DATA / "fedbatch_timeseries.parquet")
    buf = io.StringIO()
    df[["ts", "tag", "value", "unit", "quality", "batch_id"]].to_csv(buf, index=False, header=False)
    buf.seek(0)
    with conn.cursor() as cur:
        cur.execute("TRUNCATE ts.sensor_reading")
        with cur.copy("COPY ts.sensor_reading (ts, tag, value, unit, quality, batch_id) "
                      "FROM STDIN WITH (FORMAT csv)") as copy:
            copy.write(buf.read())
    return len(df)

The offline loader deliberately sets an application user before writing, so the audit trail can attribute each result to a responsible actor rather than to an anonymous database connection — the Attributable in ALCOA+ [3]:

# examples/tools/load_datasets.py
def load_offline(conn) -> int:
    df = pd.read_csv(DATA / "offline_assays.csv", parse_dates=["sample_time"])
    n = 0
    with conn.cursor() as cur:
        cur.execute("SELECT set_config('app.user', 'loader', false)")
        for _, r in df.iterrows():
            cur.execute(
                "INSERT INTO lab.sample (sample_id, batch_id, sample_time, sample_point, sample_type) "
                "VALUES (%s,%s,%s,%s,'in_process') ON CONFLICT (sample_id) DO NOTHING",
                (r.sample_id, r.batch_id, r.sample_time.to_pydatetime(), r.sample_point))
            for col, (tid, unit) in OFFLINE_TESTS.items():
                cur.execute(
                    "INSERT INTO lab.result (sample_id, test_id, value, unit, analyst, status) "
                    "VALUES (%s,%s,%s,%s,'auto','verified') ON CONFLICT DO NOTHING",
                    (r.sample_id, tid, float(r[col]), unit))
                n += 1
    return n

When it finishes it prints a one-line receipt. The loader reads the whole campaign CSVs — all six batches for the lab, release, and genealogy totals — while the high-rate sensor trace is the golden batch only (BATCH-2026-001):

loaded: 322560 sensor readings, 1344 offline results, 66 release results, 30 genealogy edges

That is 322,560 high-rate sensor rows for the golden batch BATCH-2026-001, 1,344 offline results (168 in-process samples × the 8 assays in OFFLINE_TESTS), 66 release results from hplc_results.csv, and 30 genealogy edges from lot_genealogy.csv — the last three spanning all six campaign batches. The lot-genealogy excerpt below shows the five edges for BATCH-2026-001 alone; the loader writes the equivalent chain for every batch in the campaign.

The loader also writes the lot genealogy: the directed edges that chain a seed train to the bioreactor to the Protein A capture pool to drug substance to drug product. That chain is exactly what 21 CFR 211 — the US FDA's cGMP regulation for finished pharmaceuticals — expects when it asks you to trace a finished batch back to its component lots:

batch_id,child,child_type,parent,parent_type
BATCH-2026-001,SEED-001,seed_train,WCB-CHO-001,wcb
BATCH-2026-001,BATCH-2026-001,bioreactor,SEED-001,seed_train
BATCH-2026-001,PApool-001,capture_pool,BATCH-2026-001,bioreactor
BATCH-2026-001,DS-001,drug_substance,PApool-001,capture_pool
BATCH-2026-001,DP-001,drug_product,DS-001,drug_substance

One honest bioprocess simplification to flag in that chain: the single capture_pool → drug_substance hop collapses the whole downstream purification train Book 1 walks step by step. A faithful genealogy threads each unit operation as its own material node — the Protein A pool is polished on a second, orthogonal column that clears residual aggregate and host-cell DNA (polishing chromatography), then carried through two independent viral-safety steps, a low-pH or detergent viral inactivation and a 20-nanometre viral filtration, before UF/DF concentrates and buffer-exchanges it into the drug substance. Each step would add a derivedFrom edge and hang its own release-relevant evidence off that node — a viral-filtration step's log-reduction value, a UF/DF cycle's final concentration and diavolume count — exactly as the SEC release assays hang off the lot here. The chain gets longer, never structurally different, which is the whole point: the same trace-back works at five hops or twelve.

Step 3 — turn bare tags into knowledge (make contextualize)

A row in the historian — ('2026-01-12T03:00:05Z', 'BR101.DO.PV', 41.7, '%sat', 192, 'BATCH-2026-001') — is nearly meaningless on its own. Those six fields are exactly the historian columns (ts, tag, value, unit, quality, batch_id): a timestamp, the tag BR101.DO.PV (equipment BR101, dissolved-oxygen sensor DO, present value PV), the value 41.7 in %sat, a quality flag of 192 meaning a good reading (the OPC DA convention the historian uses: 192 Good, 64 Uncertain, 0 Bad), and the batch it belongs to. But which batch, on which equipment, during which recipe phase, is still implicit. The whole point of the platform is to answer that join. It lives in examples/platform/db/60-views.sql, and it is the move that makes the Process Analytical Technology (PAT) vision real — building quality on in-process understanding of the run, not on testing it in at the end [6]:

-- examples/platform/db/60-views.sql
-- A reading with its full batch + phase context.
CREATE OR REPLACE VIEW s88.v_batch_sensor AS
SELECT r.ts, r.tag, r.value, r.unit, r.quality, r.batch_id,
       b.product_id, b.recipe_id, b.unit_id,
       bp.phase_id, ph.name AS phase_name
FROM ts.sensor_reading r
JOIN s88.batch b              ON b.batch_id = r.batch_id
LEFT JOIN s88.batch_phase bp  ON bp.batch_id = r.batch_id
     AND r.ts >= bp.start_ts AND (bp.end_ts IS NULL OR r.ts < bp.end_ts)
LEFT JOIN s88.phase ph        ON ph.phase_id = bp.phase_id;

The temporal join on bp.start_ts/bp.end_ts is the clever part: each reading is matched to whatever ISA-88 phase (ISA-88 is the batch-control half of the combined ISA-88/95 model — it defines recipes and phases, while ISA-95 supplies the equipment hierarchy) was active at that instant. A second view, s88.v_phase_summary, rolls that up into the per-phase, per-tag statistics that become the "golden batch" reference. make contextualize simply queries the first view for one tag and groups by phase:

select phase_name, count(*) n, round(avg(value)::numeric,1) avg_DO
from s88.v_batch_sensor where batch_id='BATCH-2026-001' and tag='BR101.DO.PV'
group by phase_name order by min(ts);

 phase_name |  n   | avg_do
------------+------+--------
 Inoculate  |  720 |   39.8
 Growth     | 9360 |   38.9
 Production | 8640 |   35.1
 Harvest    | 1440 |   34.4

The four phases are exactly the ISA-88 phases the seed defines — Inoculate, Growth, Production, Harvest — and dissolved oxygen drifts gently downward across them (39.8 → 38.9 → 35.1 → 34.4 %sat) as the culture grows and the cells draw more oxygen, with the controller holding it close to its setpoint the whole time. It is a modest, roughly flat-to-declining trend, not a dramatic crash — which is precisely the point of a well-controlled fed-batch. What matters is that the trend is now legible per phase rather than buried in 322,560 anonymous rows. This is the contextualized record a review-by-exception workflow reads from.

DO is deliberately the least dramatic window on the run — a tightly-controlled critical process parameter (CPP). The offline assays for BATCH-2026-001 tell the bigger story behind those rows: viable cell density (VCD — how many living cells per millilitre, the measure of how much biomass is working) climbs from ~0.34 to 22.07e6 cells/mL (e6 is scientific shorthand for ×10⁶, so 22.07 million cells/mL), viability (the fraction of those cells still alive) falls from a healthy 96.6% to 68.0% by harvest as the ageing culture is deliberately pushed for product, and titer accumulates to 5.877 g/L — which is exactly why DO drifts gently down, as the growing biomass draws more oxygen.

The temporal join: matching a reading to its active phase

Look closely at the LEFT JOIN s88.batch_phase clause, because the whole contextualization rests on it: r.ts >= bp.start_ts AND (bp.end_ts IS NULL OR r.ts < bp.end_ts). That is a half-open interval match — [start, end) — between a reading's timestamp and the windows in s88.batch_phase. The seed writes four windows for the golden batch: Inoculate 00:00–12:00 on 2026-01-05, Growth to 2026-01-12, Production to 2026-01-18, Harvest to 2026-01-19. A reading is stamped with whatever phase was active at its own instant, so the same BR101.DO.PV tag belongs to "Growth" at one timestamp and "Production" at another — no phase column ever has to be written onto the high-rate rows.

Three details make this robust. The half-open interval (>= start, < end) means a reading on a phase boundary lands in exactly one phase, never two and never zero. The end_ts IS NULL branch keeps the currently running phase matchable before its end is known — important for a live batch. And it is a LEFT JOIN, so a reading with no matching window still survives the view with a NULL phase_name rather than vanishing; the database test treats four distinct non-null phases as the pass condition (count(distinct phase_name) >= 4). Get the join wrong — a closed interval, an inner join — and you would silently double-count boundary readings or drop the ones outside any window, and the per-phase averages would quietly lie.

A second view, s88.v_phase_summary, builds on the first: it filters to phase_name IS NOT NULL and rolls the readings up into per-batch, per-phase, per-tag count/avg/min/max. That summary is the "golden batch" reference an operator overlays a new run against — and it exists only because the temporal join put every reading in its right phase first.

Step 4 — prove the record is tamper-evident (make alcoa)

A batch record is only worth signing if you can trust it has not been quietly altered. Annex 11 — the EU GMP guideline on computerised systems — requires computerised systems to generate a secure, time-stamped audit trail, and to make that trail reviewable before a disposition decision [2]. Our audit layer, in examples/platform/db/50-alcoa.sql, hash-chains every change to a regulated table so any later edit breaks the chain:

-- examples/platform/db/50-alcoa.sql
-- chain hash = H(prev_hash || payload)
v_hash := encode(digest(
    coalesce(v_prev, '') || TG_TABLE_NAME || TG_OP ||
    coalesce(v_old::text, '') || coalesce(v_new::text, '') ||
    coalesce(v_app, '') || clock_timestamp()::text, 'sha256'), 'hex');

make alcoa runs the verifier and expects zero broken links:

select count(*) as broken_links from audit.verify_chain();

 broken_links
--------------
            0

Here is the honesty the book insists on, written into the schema comment itself: a superuser who disables the trigger can still bypass this — hash chaining makes tampering evident, not impossible. No open-source database gives you 21 CFR Part 11 — the US FDA rule on electronic records and signatures, a separate rule from the 21 CFR 211 cGMP batch-record requirements cited above — out of the box. What this layer gives you is detectability: an independent, recomputable check that the contemporaneous record is intact [3]. The validated-system burden — change control, e-signatures that legally bind a named person to a specific meaning such as approved or reviewed, SOPs (standard operating procedures), the GAMP 5 lifecycle — is the operator's, and it is the hybrid last mile the next chapter scores.

Anatomy of an audit-chain row

The chain is only as trustworthy as the row it appends. So it is worth dissecting one — the row the audit.log_change() trigger writes the instant the offline loader runs its first INSERT INTO lab.result. Every field below comes straight from the CREATE TABLE audit.change_log definition and the trigger body in 50-alcoa.sql; none of it is decoration.

One row of audit.change_log: who changed what, when, with which old and new values, hash-chained to the row before it — and the honest limit of what verify_chain() actually checks. Original diagram by the authors, created with AI assistance.

Read the row top to bottom and the ALCOA+ attributes fall out of it one by one. The seq is a bigint GENERATED ALWAYS AS IDENTITY, so rows can only ever be appended in order. The ts defaults to clock_timestamp() — the real wall-clock instant of the write, not the surrounding transaction's start time — which is what makes the entry contemporaneous. The trigger captures db_user (current_user, here the bioproc connection) and app_user, which the loader sets with SELECT set_config('app.user', 'loader', false) before it writes; that second column is the Attributable in ALCOA+, naming a responsible actor rather than an anonymous connection. table_name and action record what changed and how (lab.result / INSERT); row_key is a coalesce of batch_id then sample_id, the join key back to the record. old_row and new_row are full jsonb snapshots — NULL and to_jsonb(NEW) on an insert — so the Original value and the change are both preserved in full.

The last field, row_hash, is the chain itself. The trigger computes it as SHA-256(prev_hash || table || action || old || new || app_user || ts), where prev_hash is the row_hash of the row immediately before. Edit any earlier row and every later hash stops reproducing — the tamper is evident. make alcoa runs audit.verify_chain() and expects zero broken links.

But notice the limitation the figure draws in violet, lifted verbatim from the schema comment: verify_chain() checks link consistency only. It compares each stored prev_hash to the previous row's stored row_hash; it does not recompute row_hash from the payload. So a silent edit to old_row, new_row, or app_user in an existing row is not caught by this function, and a superuser who disables the trigger bypasses it altogether. That is the honest line again: this layer delivers detectability, not Part 11 — and writing that limitation into the schema rather than the marketing copy is the whole posture of the book.

Step 5 — predict release-relevant quality from process data (make soft-sensor)

make soft-sensor trains the Raman→titer Partial Least Squares (PLS) model in examples/analytics/soft_sensor.py. PLS is a regression method — it learns a numeric prediction (here, titer) from many correlated inputs (here, the hundreds of Raman-spectrum intensities). It learns titer from the in-line Raman spectra and validates on a held-out slice the model never trained on — and it must clear a hard floor or CI fails. Two standard scores summarise the fit: R2 (the coefficient of determination, where 1.0 is a perfect fit and the R2 > 0.85 gate demands the model explain at least 85% of the variation in titer) and RMSE (the typical prediction error, in the same g/L units as titer):

# examples/analytics/soft_sensor.py
if __name__ == "__main__":
    m = train()
    print(f"PLS soft-sensor (titer from Raman): R2={m['r2']} RMSE={m['rmse_g_L']} g/L "
          f"({m['n_components']} comps, {m['n_wavenumbers']} wavenumbers, "
          f"{m['n_train']} train / {m['n_test']} test)")
    assert m["r2"] > 0.85, f"soft-sensor R2 too low ({m['r2']}): dataset not predictive"
    print("ASSERT ok: R2 > 0.85 — the Raman dataset is genuinely predictive of titer.")

PLS soft-sensor (titer from Raman): R2=0.9923 RMSE=0.1498 g/L (6 comps, 701 wavenumbers, 235 train / 101 test)

This is the conceptual heart of real-time release testing (RTRT): evaluating product quality from in-process measurements and process data rather than from end-product testing alone — the model that lets a measured spectrum stand in for a titer assay [4]. We are not claiming a validated soft-sensor here (one Raman model rarely transfers across scales or cell lines without re-training), and the R2=0.9923 here is a within-batch random hold-out from a single batch, so it overstates real performance — a genuine calibration needs leave-one-batch-out validation across multiple batches. We are showing that the data path RTRT needs — spectra in, a defensible prediction out, logged and reproducible — runs end to end on the same batch the rest of the record describes. The modelling itself — what PLS is, how cross-validation and applicability domain keep a soft-sensor honest — is developed in Book 5, Machine Learning & AI for Biomanufacturing.

Why the 0.99 is a teaching number, in ML terms

It is worth being precise about why that R2 overstates, because the reason is the single most common way a bioprocess model lies to itself, and the genealogy we just loaded is the fix. A row-wise random hold-out splits the 336 spectra of one batch into train and test, but the spectra inside a batch are not independent — consecutive in-line readings minutes apart are near-duplicates drawn from the same slowly-evolving culture state. So the test set is effectively a copy of the train set, and the model is graded on data it has all but seen; this is data leakage, and it inflates the score toward a fantasy. The honest split is a grouped, leave-one-batch-out cross-validation: every spectrum from a batch goes wholly to train or wholly to test, so the model is scored on a genuinely unseen run — and the grouping key is exactly the derivedFrom lineage this record carries, the walk back to the shared WCB-CHO-001 that tells you which rows are siblings. The models-and-validation chapter turns that into GroupKFold and nested cross-validation; the data chapter frames the cold-start reality that the binding constraint is the number of independent batches, which grows by ones at the cost of weeks each — six batches, not 336 rows.

Two further disciplines a real RTRT method needs, both absent from this teaching run, hang off the same record. A deployed model must declare an applicability domain — the input region (cell line, scale, raw-material lots, operating window) over which it was qualified and outside which a prediction is extrapolation, untrusted by definition — so the soft-sensor can decline to guess on a batch that does not resemble its training set. And once it is in production it must distinguish model drift (the predictor going stale as the relationship it learned moves) from process drift (the living cells genuinely wandering run to run, a real manufacturing signal the digital thread should preserve), because conflating the two makes a monitor either cry wolf or miss a real shift; the MLOps chapter builds the leading (label-free input-distribution) and lagging (residual control-chart) detectors that tell them apart. Finally, a validated soft-sensor is itself a governed object whose lineage belongs in the same graph as the batch's: which dataset hash it trained on, which version is locked in production, which CQA it scored — triples like any other, so an audit can walk from a released lot to the exact frozen model that touched it. That is the model-as-validated-object discipline Book 5's MLOps chapter makes the default, and it is why make soft-sensor here is a data-path demonstration, not a release method.

What a batch record must contain

We have run five layers; now we ask what the assembled artifact actually owes a regulator. 21 CFR 211.188 is specific: a batch production and control record must reproduce the master production record and document, with dates and the identity of the individuals involved, that each significant step was accomplished [1]. That is a checklist of required content — and it is worth holding our assembled BATCH-2026-001 against it field-region by field-region.

Anatomy of the reviewable batch record

The reviewable record is not one table; it is six regions stitched from the layers we built, each satisfying a different requirement and sourced from a different table or view. Dissecting it the way we dissected a single historian reading earlier in the book makes the mapping explicit.

The assembled BATCH-2026-001 record, dissected into six field-regions — each one maps to a 21 CFR 211.188 element and to the source table or view that supplies it. Original diagram by the authors, created with AI assistance.

Region by region, here is where each piece comes from and which requirement it answers:

• Recipe + equipment context — s88.recipe (CHO-MAB-001 v1) and the s88.enterprise→site→area→unit hierarchy down to BR101. This is the reproduction of the master record the regulation opens with: which recipe, which equipment, which version.
• Per-phase in-line trace — s88.v_batch_sensor and its v_phase_summary rollup. This is the contemporaneous evidence that each significant step was accomplished, dated and attributed to a phase, not a wall of anonymous rows.
• Results vs spec — lab.result joined to lab.test, which carries spec_low/spec_high so each value renders PASS or FAIL in context. This covers both in-process and finished-product testing.
• Lot genealogy — s88.genealogy, the five directed child → parent edges for this batch. This is the trace-back to component lots that 211.188 (and 211.184) expect.
• Audit trail — audit.change_log with verify_chain() = 0, the tamper-evident, reviewable trail dissected above.
• Supporting analytics — the Raman→titer PLS model (R-squared 0.9923), the data that supports a disposition decision.

Notice what the green block in the figure asserts: with all six regions present and signed, the quality unit does not read 322,560 rows — it reviews by exception, with the system surfacing only the deviations. And notice the honest caveat it also carries: the data is genuinely ALCOA+, but the validated system plus procedures around it are still the hybrid last mile.

The same record as triples, a shape, and a competency question

The six regions are stitched here in SQL, which is exactly right for the relational record of truth. But the same artifact has a second, complementary expression that this book's Semantics and the Digital Thread chapter builds and Book 4 formalizes: the genealogy edges and the release CQAs are also RDF triples — subject–predicate–object facts — so the lineage you just loaded as five CSV rows is, equivalently, a chain of derivedFrom edges in a knowledge graph. The drug-substance lot and its monomer result become bp:DS-001 bp:derivedFrom bp:PApool-001 and bp:DS-001 bp:monomerPct 98.611, and the genealogy that 211.188 traces by hand is one SPARQL property-path walk, bp:DS-001 (bp:derivedFrom)+ ?step, that follows the chain back to the cell bank in a single query [12]. That walk is a competency question — a question the vocabulary must answer, paired with its expected result so the model is graded by a mechanical pass/fail — and it appears almost verbatim as CQ-01 in Book 4's competency-questions-as-queries suite, where the derivedFrom transitive relation is what makes the trace-back free.

The release check has the same dual life. The pass/fail panel we render as a SQL join against lab.test spec limits is, in RDF, a closed-world gate — a SHACL (Shapes Constraint Language) node shape. Where SQL leaves absence silent, a SHACL bp:ReleaseShape asserts that every released lot must carry exactly one in-range value for each required CQA and a signature, so a missing sterility result or an unsigned release is a violation, not an empty cell — the distinction Book 4's release gate and SHACL chapter turns on. The shape over BATCH-2026-001's panel reads like the spec it encodes:

# Illustrative — the release panel as a SHACL shape (see /ontology/the-release-gate-and-shacl).
bp:ReleaseShape a sh:NodeShape ;
    sh:targetClass bp:DrugSubstance , bp:DrugProduct ;
    sh:property [ sh:path bp:monomerPct ; sh:minCount 1 ; sh:maxCount 1 ;
                  sh:datatype xsd:float ; sh:minInclusive 95.0 ] ;
    sh:property [ sh:path bp:hcpPpm ; sh:minCount 1 ; sh:maxCount 1 ;
                  sh:datatype xsd:float ; sh:maxInclusive 100.0 ] ;
    sh:property [ sh:path bp:approvedBy ; sh:minCount 1 ] .

The point is not to rebuild the capstone in RDF — the relational store is the record of truth, and the graph is a derived view that must be re-loaded under change control or it drifts. The point is that the contextualized record we assembled, the app_user attribution, and even the hash chain's PROV-O-style "who changed what" all map onto a shared, machine-readable vocabulary the moment a second system needs to mean the same thing — which is the FAIR Interoperable leg this chapter deliberately hands to the semantics chapter. SHACL completeness, like our make alcoa gate, proves the record is well-formed and present, not that it is true: a plausible in-range falsehood passes both. That honest limit — completeness, not correctness — is the same one the ALCOA+ section drew, now stated in the ontology's own terms.

Back along the trilogy spine

This assembled record is the third view of one thing the trilogy — the first three books of the series, which together build the thing end to end (Books 4 and 5 then layer ontologies and machine learning on top) — has followed from the start. Book 1 framed the whole batch as the s88-style spine of manufacturing — the physical fed-batch CHO run that actually produces BATCH-2026-001. Book 2 turned that run into a queryable batch genealogy, the digital thread — the lot-to-lot trace and the open challenge of keeping it whole. This chapter is where that genealogy stops being a concept and becomes the s88.genealogy edges and s88.v_batch_sensor joins you just ran: the code and SQL that implement the physical step and its data-point.

Why audit-trail review actually matters — the field-failure evidence

It would be fair to ask whether tamper-evidence and audit-trail review are over-engineering for a data backbone. The inspection record says otherwise. A retrospective analysis of FDA Warning Letters issued to pharmaceutical manufacturers across 2010–2020 found that documentation and data-integrity deficiencies were a dominant cGMP citation category — on the order of one in five warning letters named documentation as a major deficiency, alongside process-validation and quality-control failings [13]. The recurring specifics are exactly the ones this layer targets: records that could be altered without a trace, audit trails that were never reviewed before disposition, and results that could not be attributed to a person or tied back to their source.

That is why the audit-chain row carries app_user, old_row/new_row, and a prev_hash link rather than just a value, and why make alcoa is a gate and not an afterthought. The companion repo even exercises the failure-adjacent path directly: the test_audit_captures_update test sets app.user and app.reason, performs an UPDATE on a lab.result row, and asserts both that the action was recorded as UPDATE with its old and new values and that verify_chain() still returns zero — i.e. a legitimate, attributed correction is captured in full and leaves the chain intact. The thing inspectors most often find missing is precisely the thing the test proves present.

The release results in context

Stitch the six regions together and you have something a quality unit can actually act on. The release results sit in lab.result against lab.test rows that carry the spec limits, so a reviewer sees pass/fail in context. Each test name decodes to a quality attribute the antibody must meet: SEC_monomer_pct and SEC_HMW_pct come from size-exclusion chromatography (SEC) and report the percentage of intact monomer versus unwanted high-molecular-weight (HMW) aggregates; CEX_main_pct from cation-exchange chromatography (CEX) reports the main charge-variant fraction; and HCP_ng_per_mg measures residual host-cell protein (HCP) impurity, in nanograms per milligram of product:

batch_id        | test            | value  | unit  | spec_low | spec_high | result
----------------+-----------------+--------+-------+----------+-----------+--------
BATCH-2026-001  | SEC_monomer_pct | 98.611 | %     |     95.0 |     100.0 | PASS
BATCH-2026-001  | SEC_HMW_pct     |  1.287 | %     |      0.0 |       3.0 | PASS
BATCH-2026-001  | CEX_main_pct    | 70.686 | %     |     60.0 |      80.0 | PASS
BATCH-2026-001  | HCP_ng_per_mg   | 28.203 | ng/mg |      0.0 |     100.0 | PASS

Because every layer is contextualized and signed, that review-by-exception pass has something concrete to surface: the system flags only the deviations — a day-7 temperature excursion, an out-of-trend metabolite (e.g. late-batch ammonia past ~10 mM), an audit link that does not verify — and the human attention goes where it is needed. This electronic-production-record, audit-trail-review model is precisely what GAMP 5 (2nd Edition) describes for compliant computerised systems [7]. And the whole deliverable is a product-realization output inside a pharmaceutical quality system that is meant to stay in a state of control and support release across the lifecycle [5].

The whole picture

Every layer the reader built, wired into one run: make data produces the deterministic batch; make load fans it into the TimescaleDB historian and the lab tables; make contextualize joins tags to ISA-88 phases; make alcoa proves the record is tamper-evident; make soft-sensor turns spectra into a release-relevant prediction — ending in one reviewable, FAIR-aligned dataset. Original diagram by the authors, created with AI assistance.

The same flow as a sequence, the way the data actually moves between services:

Why it matters

For most of the book, each layer stood alone — a collector here, a schema there, a dashboard somewhere else. It is easy to demo a layer; it is hard to make them interconnect such that a single value emitted by a sensor is still attributable, contextualized, and verifiable when it lands in front of a reviewer. The capstone is the proof that they do. It collapses "sensor to submission" from a slogan into a command sequence you can run on a laptop in minutes, and make test re-runs the entire thing on a clean CI runner — the book's adversarial evidence that the build is genuinely implementable, not aspirational.

It also makes the trilogy's argument land. The first book explained the process; the second explained governance; this one builds the thing — and shows that open source can carry a fed-batch CHO + Protein A run from the bioreactor all the way to a reviewable batch record without a single proprietary component in the critical path.

In the real world

A real release is heavier than make test. The contextualized dataset here is genuinely ALCOA+ in its data properties, but a regulator licenses a validated system plus procedures, not a dataset. The hash chain proves tamper-evidence, not Part 11 e-signature compliance; the soft-sensor is a teaching model, not a validated RTRT method; the offline results are simulated, not analyst-witnessed. Real review-by-exception at a site layers a manufacturing execution system (MES) holding the electronic batch record, a validated laboratory information management system (LIMS), and a quality management system on top of exactly this kind of data backbone.

This is where a pilot-scale current Good Manufacturing Practice (cGMP) manufacturing facility matters — the kind of place where an open data backbone meets the validated, physical reality of making a regulated product. The data architecture this book builds is the substrate such facilities run on, and the honest-hybrid boundary — open source for ~80%, commercial and validated systems for the GxP (Good x Practice — the family of GMP/GLP/GCP regulated-quality practices) last mile — is exactly the boundary a real pilot line negotiates. The next chapter scores that boundary tool by tool.

What a real release adds: qualification and tech transfer

Two manufacturing realities the simulated run cannot show are worth naming, because they are where the validated burden actually lands. The first is qualification. Before a single byte of BATCH-2026-001 counts toward release, the systems that produced it pass the same IQ/OQ/PQ ladder a chromatography skid does — installation qualification proves the historian, the loader, and the database were deployed to the specified versions; operational qualification proves the contextualization view, the audit trigger, and the soft-sensor behave to spec across their range; performance qualification proves the end-to-end thread reproduces a known batch's lineage and release verdict on real data. Under the FDA's shift from prescriptive CSV (Computerized System Validation — documented proof a system does what it should) to risk-based CSA (Computer Software Assurance), the depth of that testing is dialed by risk: the cut-point logic and the release-relevant soft-sensor earn rigorous scripted proof, while a read-only trend dashboard earns a lighter, unscripted check — the discrimination Book 2's CSV-to-CSA chapter builds and this book's validation chapter makes runnable as pytest evidence a CI re-executes. The audit trigger and the make alcoa gate are precisely the attributable, audit-trailed controls a Part 11 / Annex 11 inspection looks for, but the gate being green is necessary, not sufficient: a regulator licenses the validated system around the data, not the data alone.

The second is scale-up and tech transfer. This run is a fixed 14-day fed-batch at one scale; a real molecule travels from a development bioreactor to a 2000-litre production train, and the process that grew BATCH-2026-001 is re-qualified at every scale because mixing, oxygen transfer, and shear do not scale linearly with volume. The data backbone is what carries that move honestly: the ISA-88/95 model, the s88.v_batch_sensor contextualization, and the genealogy edges travel unchanged, so the receiving site re-qualifies the load and the equipment against its own systems rather than re-inventing the namespace — the same tag means the same thing on BR101 at pilot scale and on a production vessel, which is exactly the property that makes a "golden batch" comparison valid across a transfer rather than an apples-to-oranges overlay.

Key terms

• Capstone run — the single end-to-end exercise that drives one batch through every layer of the stack via the make command sequence.
• Lot genealogy — the directed chain of material lots (working cell bank → seed train → bioreactor → capture pool → drug substance → drug product) that lets you trace a finished batch back to its components.
• Contextualization — joining a raw historian reading to its batch, equipment, and active ISA-88 phase, via s88.v_batch_sensor.
• Review by exception — reviewing only the flagged deviations in a contextualized electronic record rather than every raw value.
• Real-time release testing (RTRT) — evaluating in-process and final quality from process data and measured attributes instead of end-product testing alone.
• ALCOA+ — the data-integrity attributes (Attributable, Legible, Contemporaneous, Original, Accurate, plus Complete, Consistent, Enduring, Available) the record is held to.
• FAIR — Findable, Accessible, Interoperable, Reusable: the design goals that make the capstone output a reusable dataset, not a one-off report.
• Deterministic generation — producing the same dataset byte-for-byte from a fixed seed (SIM_SEED=2026), checked against MANIFEST.sha256.
• Hash chain (tamper-evidence) — appending each audit row with a row_hash over the previous row's hash plus the change, so any later edit breaks the chain; verify_chain() checks the links and make alcoa expects zero broken ones. It makes tampering detectable, not impossible.
• Temporal join — matching each historian reading to the ISA-88 phase active at its own timestamp, via the half-open interval r.ts >= start_ts AND r.ts < end_ts in s88.v_batch_sensor.
• RDF triple / SPARQL competency question — the same genealogy and release facts expressed as subject–predicate–object triples, queried by a (bp:derivedFrom)+ property path; a competency question pairs that query with its expected answer so the model is graded by a mechanical pass/fail (Book 4's CQ-01).
• SHACL release shape — the closed-world expression of the release panel: a bp:ReleaseShape requiring exactly one in-range value per CQA plus a signature, so a missing result is a violation rather than a silent empty cell — completeness, not correctness.
• Grouped (leave-one-batch-out) cross-validation — scoring a model only on whole, unseen batches so within-batch near-duplicate spectra cannot leak across the train/test line; the derivedFrom lineage to the shared cell bank is the grouping key.
• Applicability domain — the input region (cell line, scale, raw-material lots, operating window) a model was qualified over, outside which a prediction is extrapolation and untrusted.
• Model drift vs process drift — a predictor going stale (a defect to detect) versus the living culture genuinely wandering run to run (a real signal the thread should preserve); conflating them breaks a monitor.
• Qualification (IQ/OQ/PQ) and CSA — the installation/operational/performance ladder that proves a system fit for use, with the testing depth risk-scaled under Computer Software Assurance rather than uniformly scripted.
• Tech transfer — moving a validated process to a new scale or site; the ISA-88/95 model, contextualization, and genealogy travel unchanged so the receiving site re-qualifies the load and equipment, not the vocabulary.

Where this leads

We have proven the open-source stack can carry a real batch end to end — and we have been honest, at each step, about where it stops being enough on its own. The final chapter, The Honest Verdict: Open Source vs Commercial, settles the account: a scored, layer-by-layer comparison of what pure open source gives you, what the GxP last mile demands, and exactly where the hybrid line should fall for a regulated mAb facility.