EPCIS Repository Storage & Retention

Part of the Serialization Data Ingestion & EPCIS Event Sync pipeline, this guide covers the durable layer where validated EPCIS 2.0 events come to rest: the repository that must store every ObjectEvent, AggregationEvent, TransactionEvent, and TransformationEvent your line and your trading partners produce, keep them immutable for six years, and still answer a single-serial verification query in under a second. Under the Drug Supply Chain Security Act (DSCSA), the repository is not a passive archive — it is the system of record an inspector, a returning-product verification, or a suspect-product investigation will interrogate, and it must reconstruct any unit’s complete lifecycle on demand. The specific problem this area solves is designing storage that is simultaneously append-only, cheap to retain at multi-year scale, and fast to read at the granularity of one GTIN and one serial.

Architecture #

A DSCSA repository has two workloads pulling in opposite directions. Ingestion is write-heavy, ordered, and unbounded: events arrive continuously and must never be lost or mutated. Verification is read-heavy and latency-sensitive: a lookup by (01) GTIN and (21) serial has to return the current disposition before a partner’s request times out. The reference topology below resolves that tension with an append-only writer feeding a time-partitioned store, a composite index that serves both point lookups and full-history rebuilds, and a retention lane that ages data across storage tiers without ever making it mutable.

Figure — Append-only EPCIS repository with time partitioning, composite indexing, and tiered six-year retention.

The boundary to the left of the diagram matters as much as the store itself. Only records that have already cleared structural and business-rule checks reach the writer — the repository trusts its input because Schema Validation & Error Handling has already rejected malformed GTINs, missing SSCCs, and offset-less timestamps upstream. That separation of concerns is deliberate: storage does not re-validate syntax on the hot path, because doing so would tax every insert; it enforces only the integrity constraints that permanence itself demands — uniqueness, immutability, and timezone-aware chronology. Everything else is the ingestion layer’s responsibility, which keeps the writer lean enough to keep pace with a high-speed packaging line.

Three properties make this topology defensible under audit. First, writes are append-only: an event row is inserted once and never updated, so the physical history is the legal history and there is no in-place mutation for an attacker or a buggy job to exploit. Second, the store is partitioned by event_time, so a query for last week touches one small partition and a purge of expired data drops a whole partition rather than scanning billions of rows. Third, a single composite index on (gtin, serial, event_time, read_point) serves both the sub-second verification lookup and the ordered lifecycle rebuild, because both queries lead with the same GTIN-and-serial prefix. The retention lane below the divider is orthogonal to correctness: data moves from an SSD hot tier to compressed warm partitions to a cold object-storage archive as it ages, but it stays immutable and reconstructable for the full six years no matter which tier it lives on.

Foundational Concepts & Data Contracts #

The repository stores events, not packages, so its schema is shaped by the EPCIS 2.0 event model rather than by the physical DataMatrix. Every saleable unit still enters carrying the four GS1 Application Identifiers — the 14-digit GTIN (01), the serial (21), the expiration date (17) in YYMMDD, and the lot (10) — but by the time an event reaches storage those identifiers have been normalized into an EPC URI and a set of queryable columns. The GTIN and serial together form the SGTIN (urn:epc:id:sgtin:...), which is the unit’s unique fingerprint and the primary read key; the SSCC identifies cases and pallets; and the GLN identifies the read point and the trading-partner parties. Correct identifier syntax is assumed here — it is the province of upstream validation — and this layer’s job is to preserve it losslessly.

Four EPCIS event types carry DSCSA meaning and must be storable side by side in one table without losing their type-specific structure:

• ObjectEvent — commissioning, observation, shipping, receiving, and decommissioning of individual serials. This is the highest-volume event and the one most verification lookups resolve against.
• AggregationEvent — the parent-child relationship that nests units into cases and cases onto pallets, so a scan of an SSCC can expand into its child SGTINs.
• TransactionEvent — the change of ownership that binds serials to the Transaction Information and Transaction Statement a partner exchange requires.
• TransformationEvent — repackaging or relabeling, where input EPCs are consumed and output EPCs are produced, breaking the simple one-serial-one-history assumption.

A durable schema keeps the type-invariant fields as first-class indexed columns — event_type, gtin, serial, epc, event_time, record_time, biz_step, disposition, and read_point — and preserves the complete original event as a JSONB payload. That split is deliberate: the columns make queries fast and the payload makes the record reproducible in its exact original form, which is the DSCSA reproducibility obligation. event_time is when the physical event happened on the line; record_time is when the repository durably stored it. Both must be timezone-aware and stored in UTC, because an inspector reconciling your history against a partner’s needs an unambiguous chronology. The four AIs and the four event names must appear verbatim in schemas and code, never paraphrased, so that a record you store today still parses against the GS1 EPCIS standard years later.

Step-by-Step Implementation #

The following steps build the persistence layer as production Python (3.10+) using SQLAlchemy 2.0 with the asyncpg driver. Each step names the DSCSA or GS1 rule it satisfies.

Step 1 — Model the event as an append-only, indexed table #

The schema encodes the type-invariant contract as typed columns and carries the full event as JSONB. The composite index leads with gtin and serial so both the verification lookup and the lifecycle rebuild are index-served. This satisfies the DSCSA requirement that every product-identifier event be retained and reproducible in its original EPCIS structure.

from __future__ import annotations
import enum
from datetime import datetime
from sqlalchemy import String, DateTime, Enum, Index, BigInteger
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class EPCISEventType(str, enum.Enum):
    OBJECT = "ObjectEvent"
    AGGREGATION = "AggregationEvent"
    TRANSACTION = "TransactionEvent"
    TRANSFORMATION = "TransformationEvent"


class EPCISEvent(Base):
    __tablename__ = "epcis_event"

    event_id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    event_type: Mapped[EPCISEventType] = mapped_column(Enum(EPCISEventType, name="epcis_event_type"))
    gtin: Mapped[str] = mapped_column(String(14))
    serial: Mapped[str] = mapped_column(String(20))
    epc: Mapped[str] = mapped_column(String(128))
    event_time: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    record_time: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    biz_step: Mapped[str] = mapped_column(String(128))
    disposition: Mapped[str] = mapped_column(String(128))
    read_point: Mapped[str] = mapped_column(String(64))
    payload: Mapped[dict] = mapped_column(JSONB)

    __table_args__ = (
        Index("ix_epcis_verify", "gtin", "serial", "event_time", "read_point"),
    )

DSCSA/GS1 note: keeping gtin, serial, (17) expiry, and (10) lot recoverable from the stored record — the columns plus the JSONB payload — is what lets the repository answer both a verification query and a full audit reconstruction from one row.

Step 2 — Partition the store by event_time #

Native range partitioning turns the unbounded event table into a set of bounded monthly children. Queries prune to the relevant partition and expired data is dropped by detaching a whole partition rather than deleting rows. This directly serves the six-year retention obligation by making both retention and lookup operations scale with a month of data, not with the entire history.

-- Parent is declared partitioned; each month is a child partition.
CREATE TABLE epcis_event (
    event_id     bigserial,
    event_type   text        NOT NULL,
    gtin         varchar(14) NOT NULL,
    serial       varchar(20) NOT NULL,
    epc          varchar(128) NOT NULL,
    event_time   timestamptz NOT NULL,
    record_time  timestamptz NOT NULL,
    biz_step     varchar(128) NOT NULL,
    disposition  varchar(128) NOT NULL,
    read_point   varchar(64) NOT NULL,
    payload      jsonb       NOT NULL,
    PRIMARY KEY (event_id, event_time)
) PARTITION BY RANGE (event_time);

CREATE TABLE epcis_event_2026_07 PARTITION OF epcis_event
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

CREATE INDEX ix_epcis_verify_2026_07
    ON epcis_event_2026_07 (gtin, serial, event_time, read_point);

DSCSA/GS1 note: time partitioning is what makes a six-year, multi-billion-row repository operationally sustainable; the detailed rotation and archival policy is covered in enforcing six-year retention with Python.

Step 3 — Append an EPCIS event without ever updating #

The writer inserts exactly one immutable row per event. There is no update path: a correction is itself a new event (for example a decommission), which preserves the history an auditor relies on. Pooled async sessions keep the writer non-blocking under line-speed throughput.

import asyncio
from datetime import datetime, timezone
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine

engine = create_async_engine(
    "postgresql+asyncpg://svc:secret@db.internal/epcis",
    pool_size=20,
    max_overflow=10,
)
Session = async_sessionmaker(engine, expire_on_commit=False)


async def append_object_event(record: dict) -> int:
    """Append one immutable EPCIS ObjectEvent; never mutate an existing row."""
    async with Session() as session:
        event = EPCISEvent(
            event_type=EPCISEventType.OBJECT,
            gtin=record["gtin"],
            serial=record["serial"],
            epc=f"urn:epc:id:sgtin:{record['company_prefix']}.{record['item_ref']}.{record['serial']}",
            event_time=record["event_time"].astimezone(timezone.utc),
            record_time=datetime.now(timezone.utc),
            biz_step=record["biz_step"],
            disposition=record["disposition"],
            read_point=record["read_point"],
            payload=record["raw"],
        )
        session.add(event)
        await session.commit()
        return event.event_id

DSCSA/GS1 note: normalizing event_time to UTC on write while preserving the original offset inside payload keeps the chronology unambiguous when you later reconcile it against a partner’s clock, satisfying the EPCIS timestamp requirement.

Step 4 — Serve the sub-second verification lookup #

A verification request asks one question: is this serial in a good disposition right now? The query filters on the indexed (gtin, serial) prefix and returns the newest event. Because the index leads with exactly those columns, the planner does an index-only range scan and returns in single-digit milliseconds even against a month partition.

from sqlalchemy import select


async def latest_status(gtin: str, serial: str) -> EPCISEvent | None:
    """Sub-second verification lookup: newest event for one saleable unit."""
    stmt = (
        select(EPCISEvent)
        .where(EPCISEvent.gtin == gtin, EPCISEvent.serial == serial)
        .order_by(EPCISEvent.event_time.desc())
        .limit(1)
    )
    async with Session() as session:
        return (await session.execute(stmt)).scalars().first()

DSCSA/GS1 note: this is the read the industry Verification Router Service depends on; the index design that keeps it under a second is expanded in indexing EPCIS events for sub-second verification, and the router that issues it is described in the Verification Router Service Architecture.

Step 5 — Reconstruct a single serial’s lifecycle on demand #

When an inspector or a suspect-product investigation names one serial, the repository must produce the complete, ordered history — every commission, aggregation, shipment, and verification — with nothing skipped. The same composite index that serves the point lookup serves this ordered scan, because both lead with the GTIN-and-serial prefix.

async def reconstruct_lifecycle(gtin: str, serial: str) -> list[EPCISEvent]:
    """Return one serial's complete event history in chronological order."""
    stmt = (
        select(EPCISEvent)
        .where(EPCISEvent.gtin == gtin, EPCISEvent.serial == serial)
        .order_by(EPCISEvent.event_time.asc(), EPCISEvent.record_time.asc())
    )
    async with Session() as session:
        events = (await session.execute(stmt)).scalars().all()
    return list(events)

DSCSA/GS1 note: ordering by event_time then record_time breaks ties deterministically when two events share a physical timestamp, so the reconstructed lifecycle is stable and reproducible — the practical test of audit readiness the DSCSA imposes.

Validation & Error Handling #

The repository is the last line before permanence, so it enforces its own integrity even though upstream stages already validated structure. Three failure modes matter here, and none of them may halt ingestion. The first is the duplicate event: the same physical scan replayed after a broker redelivery or an idempotent batch reprocessing must not create two history rows. A unique constraint on the natural key — (gtin, serial, event_time, biz_step, event_type) — makes the second insert fail cleanly, and the writer treats that failure as a no-op quarantine rather than an error.

from sqlalchemy.exc import IntegrityError


async def append_idempotent(session, event: EPCISEvent, dlq) -> bool:
    """Insert once; route a duplicate to the dead-letter queue, never crash."""
    session.add(event)
    try:
        await session.commit()
        return True
    except IntegrityError:
        await session.rollback()
        await dlq.put({
            "reason": "duplicate_event",
            "epc": event.epc,
            "event_time": event.event_time.isoformat(),
        })
        return False

The second failure mode is the out-of-order event — a decommission arriving before the commission it supersedes. Because the repository is append-only and ordered by event_time, storage accepts both rows and lets the query layer resolve chronology; correctness comes from the ordered read, not from rejecting the write. Upstream ordering guarantees are the job of real-time event stream processing, which keys the stream by (gtin, serial) so per-unit events stay in sequence. The third failure mode is the timezone-naive timestamp: a write whose event_time lacks an offset is rejected at the writer boundary, because a naive timestamp silently corrupts every later chronological reconstruction. Each of these routes to a dead-letter queue with the original payload intact, so a bad record is captured evidence rather than a lost event, and the packaging line keeps running.

Performance & Scalability #

A DSCSA repository is a high-write, high-retention system, so the tuning levers are different from a typical transactional database. The dominant lever is partition pruning: because every hot query carries an event_time bound and every partition is a month, the planner reads one child table instead of the whole history. The second lever is index selectivity. The composite (gtin, serial, event_time, read_point) index answers the two hot queries with an index-only scan, so verification latency stays flat as the table grows into the billions of rows — the mechanics of this are the subject of indexing EPCIS events for sub-second verification. Archival scans, by contrast, are range scans over event_time with no serial filter, so a lightweight BRIN index on event_time in the cold tier gives near-free pruning at a fraction of the storage cost of a B-tree.

On the write side, throughput comes from batching and pooling rather than from single-row inserts. Bulk loads use a multi-row COPY or executemany path so a burst of serialized units commits as a few large transactions instead of thousands of small ones, and the async connection pool bounds concurrency so a shipping-window spike cannot exhaust the database. For sustained ingestion, folding persistence into the Async Batch Processing Pipelines lets the writer accept chunks and commit them at connection-pool-friendly sizes. Read scaling is orthogonal: verification traffic is served from read replicas so a query storm during a partner reconciliation never contends with the ingestion writer. The one anti-pattern that dominates incident reviews is over-indexing the write-hot table — every extra index taxes every insert, so the hot partition carries only the composite verification index and nothing speculative.

Audit & Compliance Checkpoints #

The repository is where DSCSA’s retention and reproducibility obligations become concrete. Transaction Information and Transaction Statements must be retained for six years and be reproducible in their exact original EPCIS structure — which is why the full event is stored as JSONB alongside the query columns, not discarded after extraction. Retention is enforced structurally: a partition is eligible for archival or purge only after its entire time range clears the six-year window, and the retention job operates on partitions, never on individual rows, so no record inside a live window can be deleted by mistake. The end-to-end policy, including legal holds that suspend purge, is detailed in enforcing six-year retention with Python.

Immutability is the other half of the obligation. Under 21 CFR Part 11 the electronic record must be tamper-evident, so the repository is append-only at the schema level, write access is restricted to the ingestion service role, and the archival tier uses WORM (write-once-read-many) object storage with object-lock retention, so even an administrator cannot rewrite an archived partition before its window elapses. Every event carries both event_time and record_time, giving auditors the physical chronology and the storage chronology independently. The practical audit test is unchanged from the broader DSCSA Compliance Architecture & Standards Mapping framework: when an inspector names one serial, the repository reconstructs that unit’s complete lifecycle — who touched it, where, when, and in what disposition — without manual reconciliation and within the six-year window. Per FDA guidance on the Drug Supply Chain Security Act, that reconstruction capability is the substance of interoperable, package-level traceability.

Troubleshooting #

Frequently Asked Questions #

Why store EPCIS events append-only instead of updating a current-status row? #

Because DSCSA requires the full, reproducible history of every serial, not just its latest state. An append-only design makes the physical record the legal record: there is no in-place update for a bug or an attacker to exploit, and the current status is simply the newest event returned by an ordered query. A mutable status table would satisfy the fast lookup but would destroy the lifecycle reconstruction an inspector can demand at any time.

What index makes single-serial verification sub-second at scale? #

A composite index leading with (gtin, serial) and continuing to (event_time, read_point). Both hot queries — the newest-event lookup and the ordered lifecycle rebuild — filter on the same GTIN-and-serial prefix, so one index serves both as an index-only range scan. Combined with monthly event_time partitioning, verification latency stays flat even as the repository grows into billions of rows.

How does time partitioning help meet the six-year retention rule? #

Partitioning by event_time turns retention into a metadata operation. Instead of scanning and deleting expired rows one at a time, the retention job detaches or archives a whole month partition once its entire time range clears the six-year window. That keeps both retention and lookups scaling with a month of data rather than the full history, and it makes it structurally impossible to delete a record still inside a live window.

Where do the four EPCIS event types fit in one repository? #

ObjectEvent, AggregationEvent, TransactionEvent, and TransformationEvent share one table with a discriminating event_type column and type-invariant columns for the identifier, time, and business context, while their type-specific structure lives in the JSONB payload. This keeps verification queries uniform across event types while preserving each event’s exact original form for reproduction.

How does the repository stay immutable for audit under 21 CFR Part 11? #

Writes are append-only, database write access is limited to the ingestion service role, and archived partitions live on WORM object storage with an object-lock period matching the retention window, so no one — including an administrator — can rewrite history before it expires. Every event records both its physical event_time and its storage record_time, giving auditors two independent chronologies to cross-check.

Related #

• Serialization Data Ingestion & EPCIS Event Sync — the parent pipeline this repository layer sits within.
• Indexing EPCIS events for sub-second verification — the composite-index design that keeps lookups fast.
• Enforcing six-year retention with Python — partition rotation, archival tiering, and legal holds.
• Verification Router Service Architecture — the router whose queries this repository answers.
• Real-Time Event Stream Processing — the ordered stream that feeds events into storage.