Generating FDA Sortable Spreadsheet Exports from KDE Records

The FSMA 204 deliverable that an investigator actually opens is a spreadsheet, and the two failure modes that quietly sink it are always the same: the columns are in the wrong order or carry the wrong headers, and the rows come out in a different order every time the export runs. Both break the “sortable electronic” promise the rule requires. This guide is the writer that the FDA 24-hour response automation orchestrator calls in its format-and-deliver stage: it takes a list of reconstructed Key Data Element records and turns them into a CSV or XLSX file with the exact FDA column layout, UTF-8 encoding, and a byte-for-byte reproducible row order.

Determinism is the whole point. A response that produces a slightly different file on each run cannot be verified, cannot be diffed against a prior drill, and cannot be defended if an investigator asks why two copies of “the same” export disagree. The fix is a fixed column contract, a total ordering over the rows via an explicit sort key, and a writer that never relies on dictionary insertion order or the incidental ordering of a database result set. Below is the root cause, a minimal reproducible example of the broken version, the corrected implementation for both CSV and XLSX, and the verification step that reopens the file and asserts the headers and row order before it is ever handed over.

Root Cause: Why Exports Drift

Two independent sources of nondeterminism converge on this file. The first is column order. When rows are Python dicts assembled from a query, it is tempting to let the writer derive its header from row.keys(). That works until a KDE is missing on one row and present on another, or until someone reorders the model fields, at which point the header silently changes and the FDA layout is broken. The FDA sortable spreadsheet has a specific expected column sequence drawn from the shipping and receiving KDEs in 21 CFR Part 1 Subpart S; the export must impose that sequence explicitly, not inherit it from whatever the upstream KDE Field Mapping Guide records happen to contain.

The second source is row order. A trace reconstruction gathers Critical Tracking Events from a graph walk or a set of database queries, and neither guarantees a stable order — result sets without an ORDER BY come back in whatever order the planner chose, and a graph traversal orders by discovery, not by any business key. If the export writes rows in that incidental order, two runs over identical data produce two different files. The cure is a total sort key: order every row by a tuple that is guaranteed unique and stable — Traceability Lot Code, then event date, then event type, then a tie-breaking sequence identifier — so the same input always yields the same bytes. A third, quieter culprit is encoding: writing without an explicit UTF-8 declaration lets a product description with an accented character or a degree sign corrupt on an auditor’s machine, so the encoding is pinned as deliberately as the column order.

Minimal Reproducible Example

The broken export below reads reasonable but produces a nondeterministic file. It derives the header from the first record’s keys and writes rows in whatever order they arrive, so a reordered query result or a record with a different key set silently changes both the header and the row sequence.

import csv

# Reconstructed KDE records, in graph-discovery order (not sorted).
records = [
    {"traceability_lot_code": "TLC-A-002", "event_date": "2026-02-10",
     "event_type": "Shipping", "product_description": "Romaine, chopped",
     "quantity": "40", "unit_of_measure": "case", "seq": 7},
    {"traceability_lot_code": "TLC-A-001", "event_date": "2026-02-09",
     "event_type": "Receiving", "product_description": "Romaine, chopped",
     "quantity": "40", "unit_of_measure": "case", "seq": 3},
    {"traceability_lot_code": "TLC-A-001", "event_date": "2026-02-09",
     "event_type": "Shipping", "product_description": "Romaine, chopped",
     "quantity": "40", "unit_of_measure": "case", "seq": 2},
]

# BUG 1: header taken from arbitrary first row -> order not guaranteed.
# BUG 2: rows written in arrival order -> not reproducible.
with open("recall_export.csv", "w") as fh:  # BUG 3: no encoding pinned.
    writer = csv.DictWriter(fh, fieldnames=list(records[0].keys()))
    writer.writeheader()
    writer.writerows(records)

Run this twice against a result set that the database returns in a different order, and the row sequence differs; add a record that is missing an optional KDE, and csv.DictWriter raises on the unexpected key or emits a shifted header. Neither file is defensible as “the sortable electronic spreadsheet.”

Fix Implementation

The corrected writer pins three things: the FDA column contract as an explicit ordered list, a total sort key over the rows, and UTF-8 encoding. The same shaped rows can be emitted as CSV for maximum portability or as XLSX when the investigator asked for a workbook; both share the identical column order and sort so the two formats are row-for-row equivalent.

from __future__ import annotations

import csv
from collections.abc import Iterable, Mapping
from decimal import Decimal

# The FDA sortable-spreadsheet column contract, in required order. Each tuple is
# (output header, source KDE key). Optional columns are always emitted.
FDA_COLUMNS: list[tuple[str, str]] = [
    ("Traceability Lot Code", "traceability_lot_code"),
    ("Traceability Product Description", "product_description"),
    ("Quantity", "quantity"),
    ("Unit of Measure", "unit_of_measure"),
    ("Location Description", "location_description"),
    ("Event Type", "event_type"),
    ("Event Date", "event_date"),
    ("Reference Document Type", "reference_document_type"),
    ("Reference Document Number", "reference_document_number"),
]

# Total ordering: guaranteed-unique, stable across identical inputs.
SORT_KEYS: tuple[str, ...] = (
    "traceability_lot_code",
    "event_date",
    "event_type",
    "seq",
)


def _sort_key(row: Mapping[str, object]) -> tuple[str, ...]:
    # Coerce every key part to str so mixed types never crash the sort and the
    # ordering is deterministic regardless of the source column type.
    return tuple(str(row.get(k, "")) for k in SORT_KEYS)


def _cell(row: Mapping[str, object], key: str) -> str:
    value = row.get(key)
    if value is None:
        return ""  # optional KDE absent -> explicit empty, column stays present
    if isinstance(value, Decimal):
        return format(value, "f")  # no scientific notation for quantities
    return str(value)


def shape_rows(records: Iterable[Mapping[str, object]]) -> list[list[str]]:
    """Sort deterministically and project into the fixed FDA column order."""
    ordered = sorted(records, key=_sort_key)
    return [[_cell(row, key) for _, key in FDA_COLUMNS] for row in ordered]


def write_csv(records: Iterable[Mapping[str, object]], path: str) -> str:
    header = [label for label, _ in FDA_COLUMNS]
    rows = shape_rows(records)
    # newline="" + explicit utf-8 keeps line endings and non-ASCII text stable.
    with open(path, "w", newline="", encoding="utf-8") as fh:
        writer = csv.writer(fh)
        writer.writerow(header)
        writer.writerows(rows)
    return path


def write_xlsx(records: Iterable[Mapping[str, object]], path: str) -> str:
    from openpyxl import Workbook  # openpyxl >= 3.1

    header = [label for label, _ in FDA_COLUMNS]
    rows = shape_rows(records)
    wb = Workbook()
    ws = wb.active
    ws.title = "Traceability"
    ws.append(header)
    for row in rows:
        ws.append(row)
    ws.freeze_panes = "A2"  # keep the header visible when the investigator sorts
    wb.save(path)
    return path

Because shape_rows is shared, the CSV and XLSX outputs are guaranteed to carry the same columns in the same order and the same rows in the same sequence. sorted with an explicit key replaces the incidental arrival order with a total ordering, _cell renders every optional KDE as an explicit empty string so the column count never varies, and format(value, "f") keeps a Decimal quantity out of scientific notation. Pinning encoding="utf-8" and newline="" makes the CSV byte-stable across platforms.

Verification Steps

Never trust a write you have not read back. The verification step reopens the file, asserts the header equals the FDA contract exactly, and asserts the rows are in the same total order the writer promised. Running this before delivery is what converts “the code should be deterministic” into “this specific file is correct.”

import csv

EXPECTED_HEADER = [
    "Traceability Lot Code", "Traceability Product Description", "Quantity",
    "Unit of Measure", "Location Description", "Event Type", "Event Date",
    "Reference Document Type", "Reference Document Number",
]


def verify_csv(path: str) -> None:
    with open(path, newline="", encoding="utf-8") as fh:
        reader = csv.reader(fh)
        header = next(reader)
        assert header == EXPECTED_HEADER, f"header drift: {header}"
        body = list(reader)

    # Rows must be non-decreasing on (lot code, event date, event type).
    keys = [(r[0], r[6], r[5]) for r in body]
    assert keys == sorted(keys), "rows are not in the promised sort order"
    print(f"OK: {len(body)} rows, header and order verified")


# Example run against the fixed writer:
# write_csv(records, "recall_export.csv")
# verify_csv("recall_export.csv")
# -> OK: 3 rows, header and order verified

A green run prints the row count and confirms both invariants; a header drift or an out-of-order row raises immediately, before the file reaches an investigator. Wiring this assertion into the response’s delivery stage means a regression in the upstream reconstruction can never silently ship a malformed spreadsheet, and the same check doubles as the pass/fail gate inside the mock recall drills that rehearse the whole pipeline.

Related Edge Cases

Three variants are worth checking next. First, quantity precision: if quantities arrive as floats rather than Decimal, 0.1 + 0.2 style representation error can make a total look wrong to an auditor, so coerce to Decimal at the source and let _cell render it. Second, timezone-stamped event times: this export writes an event_date, but if a request needs the full timestamp, render it as timezone-aware ISO 8601 so the sort remains total and unambiguous — the same rule the Schema Validation Rules enforce at ingestion. Third, duplicate sort keys: if two events share lot code, date, type, and sequence, the order between them is still undefined; guarantee a unique seq per lot at reconstruction time, or add the source event’s primary key as a final tie-breaker so the total ordering is genuinely total.

Frequently Asked Questions

Related

• FDA 24-hour response automation — the orchestrator whose format-and-deliver stage calls this writer.
• One-up, one-back reconstruction — produces the reconstructed KDE records this export consumes.
• Lot-level recall scoping — determines which lots’ events appear in the exported file.
• Audit log export — records the delivered file and its hash as compliance evidence.
• Schema Validation Rules — enforces the timezone-aware, well-typed KDEs that make a deterministic export possible.

Up: FDA 24-Hour Response Automation — this export is the deliverable-writing step of the parent 24-hour response pipeline.