Traceability Data Models for Regulated Inventory

Regulated inventory is unforgiving. A single missing batch record in a pharmaceutical cold chain can trigger a full recall. A mismatched lot number in an aerospace parts store can ground a fleet. Getting the data model right before you write a line of application code is the difference between a system that passes audit and one that generates findings.

What Makes Regulated Inventory Different from General Stock Management

Standard inventory systems care about quantity and location. Regulated inventory cares about provenance. Who touched it, when, under what conditions, with what authorisation, and what was the state of the item at each transition point.

The core regulatory frameworks that drive these requirements are GS1 EPCIS 2.0 (Electronic Product Code Information Services), FDA 21 CFR Part 11 for electronic records, EU FMD (Falsified Medicines Directive), and ISO 13485 for medical devices. Each of these mandates an auditable, tamper-evident record of every significant state change an item goes through.

The practical implication: your data model cannot treat a stock movement as a simple delta on a quantity field. It must treat it as an immutable event with a subject (the item or lot), a verb (observed, commissioned, aggregated, disaggregated, transformed), a location in time and space, and an actor with verified identity.

The Core Data Primitives You Actually Need

Items, Lots, and Serialisation

Most regulated inventory operates across three levels of granularity simultaneously.

• Lot/batch level: a group of units produced under identical conditions, identified by a lot number scoped to a GLN (Global Location Number) or similar facility identifier
• SSCC (Serial Shipping Container Code) level: a physical container aggregating one or more lots, used for logistics handoffs
• SGTIN (Serialised Global Trade Item Number) level: an individual saleable unit with a unique serial number

Your schema needs to represent all three and the containment relationships between them. A flat lot-number column in a products table does not do this.

A workable starting point for the item hierarchy in PostgreSQL or a similar relational store:

lot (lot_id PK, gtin, lot_number, manufactured_at, expires_at, facility_gln)
serial_unit (serial_id PK, gtin, serial_number, lot_id FK, commissioned_at)
container (sscc PK, packed_at, facility_gln)
container_content (sscc FK, item_ref, item_type ENUM['serial_unit','lot','container'])

The container_content table uses a polymorphic reference because a container can hold individual units, lot-level packs, or other containers. If you find this pattern uncomfortable, a separate junction table per content type is cleaner to query.

Events as the Source of Truth

The item hierarchy tells you what exists. The event log tells you what happened to it.

EPCIS 2.0 defines four core event types: ObjectEvent, AggregationEvent, TransactionEvent, and TransformationEvent. Model these directly. Do not try to flatten them into a generic inventory_movement table with nullable columns for each event variant. Nullable columns in regulated data models become audit findings because they make it impossible to distinguish "this field does not apply" from "this field was not recorded."

A TransformationEvent is particularly important. It covers manufacturing: multiple input lots combined to produce output lots. If you are building for pharma or food manufacturing, this is the event that carries your bill-of-materials traceability.

Each event record needs, at minimum:

1. A unique event ID (UUID v4 is fine; EPCIS uses URN format but you can translate at the API boundary)
2. Event type from the EPCIS vocabulary
3. event_time and record_time as separate timestamps (event_time is when it happened; record_time is when your system received it; they diverge in offline scenarios)
4. Business step (from GS1's CBV vocabulary: e.g., urn:epcglobal:cbv:bizstep:shipping)
5. Disposition (the state the item is in after the event: e.g., active, recalled, in_transit)
6. Source and destination GLNs
7. Actor identity, tied to your auth system

Handling Custody Transfers

A custody transfer is a TransactionEvent with a linked business transaction document (a purchase order, a despatch advice). The tricky part is that custody is a legal concept, not just a location change. Your schema needs to distinguish "physically moved" from "legally transferred." These often happen at different times, and in pharmaceutical distribution under DSCSA (Drug Supply Chain Security Act), both timestamps are required.

How Should You Handle Immutability in a Relational Database?

You do not need a blockchain for immutability in most regulated scenarios. What you need is a design that prevents updates and deletions, combined with a cryptographic audit trail.

The practical pattern: all event records are INSERT-only. No UPDATE or DELETE permissions on the events table for the application role. Corrections are new events with a correction_of foreign key to the original. The original event is never changed.

For the cryptographic layer, each event row gets a SHA-256 hash of its own fields concatenated with the hash of the previous event in the sequence for that item. This gives you a chain you can verify offline, without a distributed ledger. PostgreSQL's pgcrypto extension handles the hashing. Storing the hash in a prev_hash column and computing it at insert time in a trigger or application layer is sufficient for most regulatory audits.

Where you do need a blockchain is when multiple untrusting parties need to write to a shared ledger. In pharmaceutical track-and-trace across a multi-tier supply chain, a permissioned chain like Hyperledger Fabric can anchor EPCIS events such that no single party can retroactively alter records without consensus. The trade-off is operational complexity: you now have a distributed system to run, and Fabric in particular requires meaningful infrastructure effort. If all the writers are within your own organisation or a single trusted partner network, a well-designed relational store with immutability controls is simpler and equally auditable.

What Does a Recall Query Actually Look Like?

Recall readiness is the practical test of any traceability data model. Regulators often ask for a "one-up, one-down" trace: given a lot number, show every direct supplier lot that contributed to it, and every customer shipment it contributed to. DSCSA requires this within 24 hours. The EU FMD requires it within a matter of seconds for point-of-dispense verification.

A recursive CTE in PostgreSQL handles one-up, one-down cleanly:

WITH RECURSIVE upstream AS (
  SELECT input_lot_id FROM transformation_events WHERE output_lot_id = $target_lot
  UNION ALL
  SELECT te.input_lot_id FROM transformation_events te
  JOIN upstream u ON te.output_lot_id = u.input_lot_id
)
SELECT * FROM upstream;

The equivalent downstream trace flips the join direction. Index input_lot_id and output_lot_id separately. For large catalogues, a materialised path column that stores the full ancestry as an array (lot_ancestry ltree in PostgreSQL) can reduce recall query time from seconds to milliseconds, at the cost of maintaining it on every insert.

Versioning, Amendments, and the Regulatory Paper Trail

Regulated data does not stay static. Expiry dates get corrected. Lot numbers get re-keyed due to transcription errors at goods-in. The question is not whether amendments happen; it is whether your model handles them without breaking the audit trail.

The pattern used in FDA-compliant systems is the amendment event: a new record that references the original and carries the corrected values, the reason for correction, and the identity of the approver. Some frameworks require a two-person approval for certain correction types. If that is a requirement for you, model it as a state machine: DRAFT, PENDING_APPROVAL, APPROVED, REJECTED. The amendment only takes legal effect when it reaches APPROVED.

Never soft-delete via an is_deleted flag on a regulated event record. It is not auditable by itself and has repeatedly failed FDA 21 CFR Part 11 inspections.

Conclusion

If you are starting a new regulated inventory system, settle the event schema and the immutability strategy before you build any application logic. The item hierarchy and the EPCIS event types give you a vocabulary that aligns with what auditors and integration partners already speak. The recursive CTE pattern and hash-chaining give you recall readiness and tamper evidence without unnecessary infrastructure.

The next concrete step: map your specific regulatory requirement (DSCSA, EU FMD, ISO 13485, or a combination) to the EPCIS business steps and dispositions you actually need. That mapping should drive your schema, not the other way around.

---

FAQ

Do I need a blockchain to meet FDA 21 CFR Part 11? No. Part 11 requires tamper-evident electronic records with audit trails and controlled access, not a distributed ledger. A well-designed relational database with INSERT-only event tables, cryptographic hash-chaining, and role-based access controls meets the technical requirements. Blockchain becomes relevant when multiple untrusting organisations need to write to a shared record.

What is the difference between EPCIS 2.0 and the older 1.2 standard? EPCIS 2.0, published in 2022, adds JSON-LD and REST API support alongside the legacy XML/SOAP interface. It also introduces sensor data elements, which matter for cold-chain monitoring. EPCIS 1.2 is still widely deployed; if you are integrating with legacy systems, expect to support both serialisation formats at your API boundary.

How do we handle traceability for items that exist offline, such as in a warehouse with no connectivity? Use an event buffer on the local device (a SQLite store is adequate for most offline scenarios) and sync on reconnection. The key is that event_time and record_time are stored separately, so you can reconstruct the true sequence of events even after a delayed upload. EPCIS 2.0 explicitly accommodates this pattern.

Can a single data model cover both batch-level and unit-level traceability? Yes, but only if you design for serialisation from the start. Adding serial-unit tracking to a system built around lot numbers requires significant schema changes and often a data migration. If there is any chance your regulatory requirements will expand to unit-level serialisation, model the serial_unit table now, even if you leave it empty initially.

What is the minimum viable schema for a regulated inventory pilot? At minimum: a lot table, an event table with EPCIS-aligned event types, an event_item junction table, and a custody_chain view built on top. Hash-chaining and the full SSCC/SGTIN hierarchy can be added incrementally, but the event-sourced core must be in place from day one. Retrofitting it is expensive and often breaks regulatory continuity.

Have a project in mind? Contact Sodio Technologies to discuss your requirements and explore the right technology solution for your business.