By the end of this chapter you can

• Describe the laboratory workflow from registering a sample to a signed certificate of analysis.
• Say what M10 leaves to other modules, and which standards it is built to.
• Name the sample types, sampling points, statuses and custody actions, and say what makes a chain of custody complete.

What a LIMS does

A laboratory tests samples and reports results that other people act on: release a lot, reject a delivery, change a process. A LIMS holds the whole path. A sample is registered and labelled, received, stored, assigned to an analyst and an instrument, tested, judged against a specification, reviewed, approved, and reported on a certificate of analysis (CoA). Every step names the person and the time, so a result can be traced back to the sample and forward to the lot it released ^[1][2].

The laboratory standard behind this is ISO/IEC 17025:2017, which sets general requirements for the competence of testing and calibration laboratories, including how results are recorded, data are controlled and results are reported ^[4]. ASTM E1578 is the Standard Guide for Laboratory Informatics, and the public LIMSpec requirement set is mapped to it ^[3].

Where the module stops

M10 owns the sample, the test and the result. It does not own the equipment's calibration, the analyst's training or the instrument's own data system. It reads their state before it lets work go ahead where those modules are installed; where they are not, the analyst records a manual check on the test.

The standards, and what each is used for

You do not need to own every standard to build the module. You need to know which rule each design choice serves, and to ask your quality manager what your own accreditation body or regulator expects. ASTM and ISO standards are paid documents; their price and edition are on the publisher's own page, and ASTM's page for E1578 lists an earlier edition (-06) than the -18 this module maps to.

Eleven capabilities by tier

The module library marks each capability MVP (must have before a laboratory can replace its tool), STD (parity with mainstream tools) or BIC (a best-in-class difference).

Samples, sampling points and custody

A sampling point is a place a sample is drawn: a tank outlet, a receiving dock, a water tap, an air or surface location. Its type is one of process, raw_receipt, finished, environmental, utility_water, air or surface. It may carry a schedule written as a recurrence rule, so sampling can be planned and not remembered.

A sample has a type: raw, in_process, finished, stability, environmental, water, retain, complaint or investigation. It has a priority (routine, rush or stat), a number issued by the system, and links to the item, the lot, the sampling point and the specification. A child sample (an aliquot, a portion taken for a separate test) points at its parent. Samples are logged in singly, in a batch (the same route once for each vial) or on a schedule (a planned sample created from the sampling point's recurrence rule, which moves to collected when it is drawn), and each gets a label that carries its number as a barcode.

The chain of custody is the unbroken record of who held a sample and where it was. Each hand-over is a row in lims_custody_event with one of seven actions: collected, transferred, received, stored, retrieved, consumed or disposed. Rows are added and never changed; a mistake is corrected by a new row. A chain is complete when the events begin with collected, include received and stored, run in time order, and each from-person equals the previous to-person.

References

1. LIMSwiki: LIMSpec primary laboratory workflow (mapped to ASTM E1578-18). https://limswiki.org/index.php/LII:LIMSpec/Primary_Laboratory_Workflow
2. LIMSwiki: LIMSpec sample management. https://limswiki.org/index.php/Template:LIMSpec/Sample_management
3. ASTM: E1578 Standard Guide for Laboratory Informatics. https://store.astm.org/e1578-06.html
4. ISO/IEC 17025:2017 General requirements for the competence of testing and calibration laboratories. https://www.iso.org/standard/66912.html
5. LIMSwiki: the most important features of a LIMS. https://limswiki.org/index.php/LIMS_Q%26A:What_are_the_most_important_features_of_a_laboratory_information_management_system_(LIMS)%3F

By the end of this chapter you can

• Name the fourteen lims_ tables and say what each holds, including the columns that carry allowed values.
• Explain how methods and specifications are versioned, and what each status means.
• Say why a sample is pinned to one specification version and what goes wrong when it is not.

Fourteen tables on the shared spine

Every table starts with the standard columns every platform table has: id, tenant_id, created_at, created_by, updated_at, updated_by, row_version, archived_at and ext. The module keeps no item list, lot list, people table or signature store of its own. It points at core_item, core_lot, core_person, core_equipment, core_storage_location, core_party, core_uom, core_attachment and core_e_signature. Its own tables share the prefix lims_.

The table below is the full definition: every column the module adds to the standard ones, with its type and whether it is required (req). Types are text, int, num (a decimal number, never a float), bool, date, ts (a timestamp with time zone), uuid and json; an arrow means a hard foreign key to that table. Four columns are soft links, plain uuid columns with no foreign key, because the table they point to belongs to a module that may not be installed: sop_doc_ref and protocol_doc_ref (document control), production_order_ref (production) and quality_event_ref (quality).

Look at the allowed values before you design a screen. They are check constraints, rules the database enforces on every row whatever program wrote it. lims_sample.specification_id is not marked required in the table definition, because a planned sample (one created from a sampling point's schedule) may not have one yet, but the rule written in Session 2 refuses any other sample without one, and a planned sample gets its one specification when it is collected.

Methods and specifications are versioned

A test method says how an analyte is measured and calculated. It has a code and a version. When a method changes, the old version is retired and a new one becomes active; results already recorded stay tied to the version they used. In the seed, ASSAY-01 version 1 is retired, version 2 is active, and PH-01 version 1 is active.

A specification says what a good result is, for one item at one stage (raw, in_process, release, stability, customer or environmental), optionally for one customer. Each version has a status that only moves forward: draft (being written, limits can still change), approved (signed by the approver), effective (in force from its effective date), superseded (replaced by a later version). Its limits live in lims_spec_limit: one row per analyte with a lower limit (lsl), an upper limit (usl), an optional target, a unit, and a flag saying whether the analyte is reportable. A limit may be one-sided, or text only (for example a colour or an appearance). Once a version leaves draft its limits do not change; a change is a new version.

The pin

Pinning means the sample stores the id of the specification version that was in force when it was logged in, and that id can never change. Every later flag (in specification, OOS, OOT) is computed against that version, whatever is published afterwards. The database enforces it: a trigger refuses a sample with a specification of another item, one that is still draft or approved or takes effect after the collection time, and any later change to specification_id. Choosing the right version among those that could be in force, the latest one effective at the collection time, is the register route's job.

Exercise · Model one specification and pin a sample to it20 minutes

You need: docs/lims-tables.md (or the table in this chapter), a SQL console on a local or branch database, and your AI coding agent

Use made-up values, as in the seed: no real products, customers or readings. If you have not built the tables yet, do steps 1 to 3 on paper and the rest after Session 2.

1. 1. Write one method with two versions (the older retired, the newer active) and the analyte each one measures.
2. 2. Write one specification with two versions for one item at stage release: version 1 effective 1 January, version 2 effective 1 July, with a limit row for each analyte (lsl, usl, unit, and whether the limit is one-sided).
3. 3. Choose a result value that is inside version 1 and outside version 2. In the seed this is the assay 98.2 % against 98.0 to 102.0 and 98.5 to 101.5.
4. 4. Log in two samples: one collected on 10 June pinned to version 1, one collected on 8 July pinned to version 2. Check each screen or row shows the version beside the sample.
5. 5. In SQL, try to change the first sample's specification_id to version 2. The database must refuse. Then try to log in a sample collected before version 2's effective date and pinned to version 2: refused too.
6. 6. Write two lines: the verdict of your chosen value against version 1 and against version 2, and which one the stored flag shows and why.

Outcome: A two-version specification with limits, two samples each pinned to the version in force when they were collected, two refused changes, and a written explanation of why the stored flag follows the pin.

References

1. ASTM: E1578 Standard Guide for Laboratory Informatics. https://store.astm.org/e1578-06.html
2. LIMSwiki: LIMSpec sample management. https://limswiki.org/index.php/Template:LIMSpec/Sample_management
3. ISO/IEC 17025:2017 General requirements for the competence of testing and calibration laboratories. https://www.iso.org/standard/66912.html
4. eCFR: 21 CFR Part 211, Current Good Manufacturing Practice for Finished Pharmaceuticals (includes the laboratory controls and records). https://www.ecfr.gov/current/title-21/chapter-I/subchapter-C/part-211

By the end of this chapter you can

• Enter a result with its source, unit and raw data, and say why nobody types a flag.
• Judge a value against a specification, and apply the OOT run rule to a series.
• Correct a wrong result by superseding it, and walk an OOS result through Phase I and Phase II.

Entering a result

A result is a row in lims_result, tied to one test. It has an analyte, a numeric value (value_num) or, for a qualitative result, a text value (value_text), and a unit. Its source is manual (typed by an analyst), instrument (parsed from an instrument file) or calculated (worked out on the server from entered values by the formula in the method's calculations column). It always names the person who entered it and the time. Where an instrument file or a notebook page is the raw data, it is attached, and the platform stores the file with a checksum, a fingerprint that changes if one byte changes.

Values are numbers, not text, and not floating point: a float would store 99.1 as an approximation. How many figures are reported, and how a value is rounded, is a laboratory decision written in its own SOP. The module applies that rule; it does not invent one, so write the rule down where the agent can read it.

The flags are computed, not typed

When a result is inserted, a database trigger calls a function (lims_evaluate) that fills three flags from the limit row of the sample's pinned specification. Nobody can type them.

• in_spec: the value is at least the lower limit and at most the upper limit, whichever are given.
• oos (out of specification): the value is outside those limits.
• oot (out of trend): the value is in specification, but the limit's trend rule says the series is drifting.

The trend rule is stored on the limit row as JSON. The rule used in this course is a run of three: a result is OOT when it is in specification and it and the two latest earlier results of the same item and analyte, in the order they were entered and leaving out superseded ones, all rise or all fall. Because it needs two earlier results, it cannot fire for the first results of an analyte, for example right after a cut-over.

A wrong result is never overwritten

A laboratory result is a record other people relied on. Corrections to electronic records must not hide what was there before: Part 11 expects secure, computer-generated, time-stamped audit trails and says a change must not obscure previously recorded information ^[2]. The FDA's data integrity guidance sets out its expectations for audit trails and original data in the same spirit ^[3].

So lims_result has database rules: a delete is refused, and an update may change only the review and approval columns and superseded_by_id. The value, analyte, test and who entered it can never change. A correction is a new result on the same test and analyte, with a signature whose reason is not empty, and the old result gets superseded_by_id pointing at the new one. Certificates and trends use only results that are not superseded; the old one stays retrievable with its reason and signer.

Out of specification: two phases

An OOS result opens an investigation (lims_oos_investigation). The FDA's guidance on investigating OOS results for pharmaceutical production describes a Phase I laboratory investigation and, if that finds no assignable cause, a Phase II full-scale investigation ^[1]. In the module:

1. Phase I, in the laboratory. The analyst writes a hypothesis and checks the raw data, instrument, reagents and calculation. The question is whether an assignable cause can be shown, from the raw data and not assumed. If so, the analyst writes the conclusion and closes Phase I (status phase_1_closed); the wrong result is then corrected by superseding it with a signed reason, and a different person closes the investigation.
2. Phase II, full scale. If no assignable cause is shown, the investigation escalates (status escalated, phase phase_2_full) and a quality event or action item is raised in the quality module. It closes with a conclusion and a retest plan.
3. Approval waits. While any investigation on a sample's results is not closed, none of its results can be approved and the sample cannot be approved or rejected. The sample is also refused approval or rejection while any of its results that is not superseded is still unapproved. The person who opened an investigation may not close it.

Retesting is not a way to make an OOS result disappear. What may be retested, how many times, and who decides are set by the investigation plan and the guidance, and the original result and every retest stay on record ^[1].

Exercise · Judge five results by hand, then by the rule20 minutes

You need: Pen and paper, then the lims_evaluate function from Session 2 (or a spreadsheet formula if you have not built it yet)

Use the assay limits of SPEC-P100 version 2: at least 98.5 and at most 101.5 %. The values are made up.

1. 1. Write the rule in one line: a value is in specification when it is at least the lower limit and at most the upper limit.
2. 2. Judge by hand: 91.9, 98.5, 101.5, 101.6 and 99.1. Write in specification or OOS for each.
3. 3. Write the series 98.2, 99.0, 98.8, 99.1, 99.5. For each result take it and the two before it; mark it OOT if all three rise or all three fall. The first two results cannot be marked: say why.
4. 4. Change the third value from 98.8 to 99.2 and mark the series again. Which result is OOT now?
5. 5. Run the same values through your function or spreadsheet and compare with your hand answers. If they differ, find out which is wrong and fix it before going on.
6. 6. Write the one line that a correction of the first value needs: which fields the new result must share with the old one, and what the signed reason must say.

Outcome: Five hand verdicts that match the function (91.9 OOS, 98.5 in, 101.5 in, 101.6 OOS, 99.1 in), the OOT mark on the fifth result of the original series and on the third result of the changed series, and a written rule for a correction.

References

1. FDA: Investigating Out-of-Specification (OOS) Test Results for Pharmaceutical Production. https://www.fda.gov/regulatory-information/search-fda-guidance-documents/investigating-out-specification-oos-test-results-pharmaceutical-production
2. eCFR: 21 CFR Part 11, Electronic Records; Electronic Signatures. https://www.ecfr.gov/current/title-21/chapter-I/subchapter-A/part-11
3. FDA: Data Integrity and Compliance With Drug CGMP, Questions and Answers. https://www.fda.gov/regulatory-information/search-fda-guidance-documents/data-integrity-and-compliance-drug-cgmp-questions-and-answers
4. ISO/IEC 17025:2017 General requirements for the competence of testing and calibration laboratories. https://www.iso.org/standard/66912.html

By the end of this chapter you can

• Describe the roles and the rules between them: who enters, who reviews, who approves.
• Say what a certificate of analysis may and may not contain, and what makes it refuse a lot.
• Define turnaround, backlog, OOS rate and right-first-time, and count them by hand on the seed.

Two levels, three different people

Review and approval are separate steps by separate people. The reviewer checks the result against the raw data and the method; the approver, usually the laboratory lead, accepts it for release. Each signs, and the database refuses the shortcuts: the person who entered a result cannot review it, the reviewer cannot approve it, a review needs a review signature and an approval needs an approval signature and a prior review.

An electronic signature is a row in core_e_signature: who signed, what it meant (authored, reviewed, approved, released or rejected), which record, a hash of the record's text, when, how they authenticated, their printed name and a comment. Part 11 expects a signed record to show the signer's printed name, the date and time, and the meaning of the signature ^[5]. The record hash is a fingerprint of the signed text: if the value is changed afterwards, it no longer matches.

The module's seven screens follow the roles: /lims/request, /lims/bench, /lims/review, /lims/approve, /lims/investigations, /lims/methods and /lims/board. Your access matrix lists them in Session 3; a screen missing there is refused.

Disposition and the certificate of analysis

When every result of a sample is approved, the sample is approved with a disposition of pass, fail or conditional, and the module publishes two events for other modules: lims.sample.approved and lims.lot.disposition. The quality module can place a hold on a lot or release it from that event; the laboratory does not release stock itself.

A certificate of analysis (lims_coa) is built for one lot against one specification, optionally in the form a customer asks for. It has one line per approved result that is not superseded, with its limits, and is signed (meaning released) and stored as a PDF. Three rules make it trustworthy:

• It is built only from approved results of an approved sample on the same specification. A rejected lot gets no certificate.
• It never shows a superseded value. After the correction in chapter 3 the certificate for that lot has two lines and never the 91.9.
• It is append-only: a correction is a new certificate row, never an edit of the old one.

Stability, environment and reagents

Three further capabilities complete the laboratory. They are marked BIC or STD in the capability table. For the features laboratories usually look for in a LIMS, see the LIMSwiki list ^[4].

Stability studies. A protocol names the item and lot, the storage conditions and the time points in months. Each point becomes a lims_stability_pull row with a scheduled date; when it is pulled, a sample of type stability is created, pinned to the specification of stage stability, tested, and trended. ICH Q1A(R2) sets the general testing frequencies for long-term studies: normally every 3 months over the first year, every 6 months over the second year, and annually thereafter through the proposed shelf life ^[2]. The module library also lists shelf-life estimation: using the trended stability results to propose how long the product stays within its specification. It is BIC and optional in this course; the statistical method is set by your protocol and the stability guidance, and the lessons do not build it.

Environmental monitoring. Rooms, air, water and surfaces are sampling points of types environmental, air, utility_water or surface, each with a recurrence rule so sampling is scheduled. Their results are judged against a specification of stage environmental. The module library lists alert and action limits for these locations; an excursion, a result beyond a limit, is flagged and followed up as any OOS result is.

Reagents and standards. Each reagent, standard or prepared solution has a lot number, an expiry date and a status (available, quarantine, expired or consumed). Before tests are assigned, the reagent a method uses must be available and expire after today, so a result can be traced back to the reagent that produced it. The instrument's calibration and the analyst's qualification are checked at the same point: read from M05 and M07 where they are installed, otherwise a manual check noted on the test.

The four numbers

Write these definitions before you compare two systems. A different start time for turnaround (collected, received or logged in) or calendar against working hours makes the old and new numbers disagree for no real reason. Test each query on the seed, where you can count the answer by hand.

Moving from the old LIMS

The module library's plan is: export methods, specifications and historical results; load them; and validate by re-evaluating a sample of history against the migrated specifications. Take the export yourself, count it, map each old value to an allowed value (never by guess), load the version in force with the approver's signature, and compare the new verdicts with the old tool's own flags. Every difference needs a cause. Computerised system validation should be risk-based; ISPE's GAMP 5 (second edition) is the guide the library names ^[3], and EU Annex 11 sets expectations for computerised systems in regulated settings ^[1].

Exercise · Hand-count the four numbers on the seed20 minutes

You need: Pen and paper or a spreadsheet, then your database console with the Session 2 seed loaded

Received and approved times (UTC) of the five approved samples: 0001 received 2026-06-10 09:00, approved 2026-06-11 15:00; 0002 received 2026-07-08 09:00, approved 2026-07-09 09:00; 0003 received 2026-07-15 10:00, approved 2026-07-16 16:00; 0004 received 2026-07-22 09:00, approved 2026-07-23 21:00; 0005 received 2026-07-29 09:00, approved 2026-07-31 09:00. SMP-2026-0006 was received on 2026-07-30 at 09:00 and is not approved.

1. 1. Subtract received from approved for each sample, in hours, and write the five turnarounds.
2. 2. Sort them and take the middle value. This is the median turnaround.
3. 3. Work out the age of the backlog sample, SMP-2026-0006, as at 2026-08-01 12:00 UTC.
4. 4. Count the seed's results: how many are OOS, how many are reportable, and so the OOS rate; and how many were approved without correction, and so right-first-time.
5. 5. Redo sample 0001's turnaround counting from its collection time (2026-06-10 07:30) instead of its received time, and note how the number changes.
6. 6. Write each of the four queries and run them on the seed. If a query disagrees with your hand count, fix the query, not the count.

Outcome: Hand counts that the queries reproduce: median turnaround 30 hours, backlog one sample of 51 hours, OOS rate 0 of 10, right-first-time 10 of 10, and a written note that counting from collection gives 31.5 hours for sample 0001, so the definition must be stated.

References

1. European Commission: EudraLex Volume 4, EU GMP guidelines (includes Annex 11, computerised systems). https://health.ec.europa.eu/medicinal-products/eudralex/eudralex-volume-4_en
2. ICH: Q1A(R2) Stability Testing of New Drug Substances and Products. https://database.ich.org/sites/default/files/Q1A%28R2%29%20Guideline.pdf
3. ISPE: GAMP 5 Guide, 2nd Edition. https://ispe.org/publications/guidance-documents/gamp-5-guide-2nd-edition
4. LIMSwiki: the most important features of a LIMS. https://limswiki.org/index.php/LIMS_Q%26A:What_are_the_most_important_features_of_a_laboratory_information_management_system_(LIMS)%3F
5. eCFR: 21 CFR Part 11, Electronic Records; Electronic Signatures. https://www.ecfr.gov/current/title-21/chapter-I/subchapter-A/part-11

Choose one answer for each question, then submit. You will see the right answer and why for every question.

1. What does a LIMS hold that a spreadsheet of results usually does not?

AOnly the final numbers, kept tidy in one columnBThe laboratory's invoices and supplier contractsCThe path from sample to approved result and certificate, with who did each step and whenDA scanned copy of each notebook page, filed by date

2. Which of these does M10 leave to another module?

AExecution of instrument calibration (the LIMS checks its status)BChain of custody of a sampleCAutomatic in-spec and OOS evaluationDBuilding a certificate of analysis

3. Stability studies and environmental monitoring are marked BIC in the capability map. What does BIC mean?

AA capability a laboratory must have before it can replace its toolBParity with mainstream toolsCA capability left out of the moduleDA best-in-class difference beyond what mainstream tools offer

4. Why is each sample pinned to one specification version when it is logged in?

ASo that the printed label can show a shorter numberBSo its result is judged against the limits in force at collection, even after a newer version is publishedCSo the reviewer can choose the more favourable version laterDSo the specification can be edited without a new version

5. SPEC-P100 version 1 allows an assay of 98.0 to 102.0 % and version 2 allows 98.5 to 101.5 %. A sample pinned to version 1 has a result of 98.2 %. What does its stored result say?

AIn specification, because the pin is version 1BOOS, because version 2 is the current oneCOOT, because 98.2 is close to a limitDNothing; the flags are filled in when the certificate is printed

6. An analyst typed 91.9 for an assay; the raw-data file shows 99.1. How is it corrected?

AEdit the value and add a commentBDelete the row and enter a new oneCAsk the database administrator to change the valueDEnter a new result with a signed reason and supersede the old one, which stays on file

7. Under a run-of-three rule, when is a result out of trend (OOT)?

AWhenever it is outside its limitsBWhen it is in specification and it and the two latest earlier results of the same item and analyte all rise or all fallCWhenever it differs from the previous resultDOnly when an analyst marks it by hand

8. Who may approve a result?

AThe analyst who entered it, after a pauseBAny user with access to the sampleCA person who neither entered nor reviewed itDThe reviewer, in the same step as the review

9. A result is flagged OOS. What should the Phase I investigation look for first?

AA retest that passes, to replace the resultBA way to approve the sample while the investigation runsCWhich analyst to blameDAn assignable cause in the laboratory, shown from the raw data and not assumed

10. What does a certificate of analysis for a lot show?

AEvery result entered, including superseded onesBA line for each approved result that is not superseded, against the specificationCOnly the results the customer asked about, even if not approvedDThe analyst's notes on each test

11. Five approved samples took 30, 24, 30, 36 and 48 hours from receipt to approval. What is the median turnaround?

A33.6 hoursB24 hoursC30 hoursD48 hours

12. How is a migration of specifications from the old LIMS validated?

ARe-evaluate a sample of historical results against the migrated specifications and explain every differenceBCompare the number of rows exported and loadedCAsk the vendor to confirm the exportDLoad the specifications and wait for the first complaint

Submit answers