Exporting Audit Logs to FDA Submission Templates in Python

The most common way an audit export fails FDA intake is not a missing record — it is a shape mismatch. Your internal audit table has its own column names and order, and the FDA submission template has a fixed set of headers in a fixed sequence. Ship the rows in your internal shape and the intake validation rejects the file for an unexpected header, a reordered column, or an unescaped comma that split one field into two. This guide, part of the Structured Audit Log Export component, walks through the exact defect and the deterministic fix: an explicit column map, a locked header order, full field escaping, and asserts that verify the output before you attach it to a submission.

The scenario is concrete. An investigator has scoped a lot and asked for the access history of its Key Data Elements (KDEs). You run a query against the audit store, dump the rows to CSV, and hand them over — and the file bounces because your ts and lot columns are not the Event Date/Time (UTC) and Traceability Lot Code headers the template expects, and one of your actor notes contained a comma that shifted every field after it one column to the right. None of the data is wrong; the projection is.

Root Cause: Internal Shape Leaks Into the Submission

Audit tables are designed for writes, not for the FDA. They use short column names, store enums as integers, keep timestamps in whatever the write path produced, and order columns by whatever was convenient when the schema was created. The FDA template is the opposite: a small, fixed set of human-readable headers in a mandated order, with every value a string. When export code serializes the internal row directly — csv.writer(rows) over a raw SELECT * — three defects follow at once.

First, the header names are wrong: actor_id reaches the file instead of Performed By. Second, the column order is unstable: SELECT * returns columns in schema order, which changes if anyone adds a column, so the file that validated last month fails this month. Third, field values are unescaped: a free-text note or a KDE payload that contains a comma, a double quote, or a newline breaks the row into the wrong number of fields, and CSV parsers on the FDA side either reject the row or, worse, silently misalign it. The fix is to stop letting the internal shape leak out and instead project every row through an explicit template contract.

Minimal Reproducible Example

Here is the broken exporter and a payload that triggers all three defects. The actor_note on the second row contains a comma; the internal keys are short; the order comes from the dict as written.

import csv
import io

# Internal audit rows straight from the store — short names, enum as int,
# a free-text field with a comma waiting to break the CSV.
rows = [
    {"ts": "2024-06-01T09:15:00+00:00", "lot": "LOT-2024-06-01-AB1",
     "rec": "cte-3390", "act": 5, "who": "user:jlin",
     "actor_note": "routine export"},
    {"ts": "2024-06-01T11:40:00+00:00", "lot": "LOT-2024-06-01-AB1",
     "rec": "cte-3391", "act": 3, "who": "user:psen",
     "actor_note": "corrected quantity, per supplier email"},
]

def export_broken(rows: list[dict]) -> str:
    buffer = io.StringIO()
    writer = csv.writer(buffer)
    writer.writerow(rows[0].keys())          # internal header names leak out
    for r in rows:
        writer.writerow(r.values())          # raw values, enum still an int
    return buffer.getvalue()

print(export_broken(rows))

The output has the header ts,lot,rec,act,who,actor_note — none of which the FDA template recognizes — the act column is the integer 5 instead of EXPORT, and the second row’s note is unquoted, so corrected quantity, per supplier email is read as two fields and every column after it shifts. Intake validation rejects the file.

Fix Implementation

The fix introduces one explicit contract — an ordered list of (internal_key, FDA_header, transform) tuples — and drives the whole export from it. The header order is fixed by the list, the header names come from the template, enum decoding happens in the transform, and csv.DictWriter with QUOTE_ALL escapes every value so commas, quotes, and newlines survive intact.

import csv
import io
from typing import Callable

# Decode the internal action code to the FDA-facing verb.
ACTION_LABELS = {1: "READ", 2: "CREATE", 3: "UPDATE", 4: "DELETE", 5: "EXPORT"}

# The template contract: internal key -> (FDA header, value transform).
# The ORDER of this list IS the mandated column order.
TEMPLATE: list[tuple[str, str, Callable[[object], str]]] = [
    ("ts",  "Event Date/Time (UTC)", lambda v: str(v)),
    ("lot", "Traceability Lot Code", lambda v: str(v)),
    ("rec", "Reference Record",      lambda v: str(v)),
    ("act", "Action Type",           lambda v: ACTION_LABELS[int(v)]),
    ("who", "Performed By",          lambda v: str(v)),
]

FDA_HEADERS: list[str] = [header for _, header, _ in TEMPLATE]


def project_row(row: dict) -> dict[str, str]:
    """Map one internal row onto the FDA template columns, in order."""
    projected: dict[str, str] = {}
    for key, header, transform in TEMPLATE:
        if key not in row or row[key] is None:
            raise ValueError(f"row missing required field {key!r}")
        projected[header] = transform(row[key])
    return projected


def export_fda_csv(rows: list[dict]) -> str:
    buffer = io.StringIO()
    # QUOTE_ALL wraps every field, so an embedded comma or newline cannot
    # split a row; lineterminator is fixed so output is byte-stable.
    writer = csv.DictWriter(buffer, fieldnames=FDA_HEADERS,
                            quoting=csv.QUOTE_ALL, lineterminator="\n")
    writer.writeheader()
    for row in rows:
        writer.writerow(project_row(row))
    return buffer.getvalue()

Three things changed. The header row is now FDA_HEADERS, so the names and their order are the template’s, not the table’s. The act transform decodes 5 to EXPORT, so the FDA reads a verb, not a code. And QUOTE_ALL wraps every field in quotes, so the comma in corrected quantity, per supplier email stays inside one cell. The actor_note column is simply not in TEMPLATE, so internal free-text never reaches the submission at all. This mirrors the deterministic exporter in the parent Structured Audit Log Export guide, where the same column map drives both CSV and JSON.

Verification Steps

Do not eyeball the CSV. Parse it back and assert on structure — the header must equal the template exactly, and every row must have the right field count with the enum decoded. These asserts are cheap enough to run in the export job itself and become part of the submission manifest.

import csv
import io

output = export_fda_csv(rows)
parsed = list(csv.reader(io.StringIO(output)))

# 1. Header is the template, in the mandated order.
assert parsed[0] == FDA_HEADERS, f"header mismatch: {parsed[0]}"

# 2. Every row has exactly len(FDA_HEADERS) fields — no comma leaked a split.
for i, line in enumerate(parsed[1:], start=1):
    assert len(line) == len(FDA_HEADERS), f"row {i} has {len(line)} fields"

# 3. The action code was decoded to a verb, not left as an integer.
reader = csv.DictReader(io.StringIO(output))
first = next(reader)
assert first["Action Type"] == "EXPORT", first["Action Type"]

# 4. The embedded comma survived inside one cell (round-trips cleanly).
reader = csv.DictReader(io.StringIO(output))
rows_back = list(reader)
assert rows_back[1]["Performed By"] == "user:psen"
assert len(rows_back) == 2

print("all export assertions passed")

If assert 2 ever fires, a value contained a delimiter that escaping did not cover — check that QUOTE_ALL is set and that you are not post-processing the string. If assert 1 fires, someone edited TEMPLATE order or a stray header slipped in. Running these four checks before attaching the file turns a silent intake rejection into a caught error you fix before submission.

Related Edge Cases

• Newlines inside a payload. A KDE note that contains a literal newline will break naive line-splitting even with quoting if a downstream tool re-parses on \n. Keep the file quoted end-to-end and never split on raw newlines; use a real CSV parser to read it back.
• Timezone drift in the timestamp column. If ts arrives as a naive or local-time string, the Event Date/Time (UTC) column is mislabeled. Normalize to UTC with an explicit offset before export — the same discipline the retention-period query patterns guide applies to window boundaries.
• Unicode in actor identifiers. International names or facility labels must round-trip; keep ensure_ascii handling explicit and test one non-ASCII actor so encoding is not discovered during an inspection.

Frequently Asked Questions

Related

• Structured Audit Log Export for FDA Submission — the full export component this how-to fixes one stage of
• Retention-period query patterns for audit logs — selecting the right window before you project it to the template
• Data Retention Policies — the two-year window the exported events are bounded by
• FDA 24-Hour Response Automation — where the validated export is attached to a traceability record request
• Append-Only Ledger & Hash Chaining — the integrity anchor referenced in the export manifest

Up: Structured Audit Log Export for FDA Submission — this guide details the template-mapping stage of the parent export pipeline.