Generating Statutory PDF and CSV Export Packages

Problem statement

A regulator does not accept the audit ledger; it accepts a document. The final step of any compliance filing is rendering a slice of the ledger into the exact PDF and CSV shapes a jurisdiction requires — its columns, its headers, its retention statement — and bundling them into a package. The trap here is subtle: a report that changes on every run cannot be reconciled against a copy already on file. If a producer files a package in July and regenerates it in November to answer a question, the two must be byte-identical, or the discrepancy itself becomes an audit finding.

Determinism is therefore the central requirement, and it is easy to lose. PDF libraries stamp a creation timestamp into the document metadata by default; dictionary iteration can reorder columns; a float rate can render as 1.9500000000000002; a locale can flip a decimal separator. Any one of these makes two renders of the same data differ. This guide builds a rendering path that eliminates each source of nondeterminism, validates every row against a jurisdiction schema before it is written, and produces a manifest hash that reproduces exactly. The ledger these records come from, and the seasonal workflow that calls this renderer, are described in the parent Audit & Reporting topic.

Two output formats exist because two audiences consume the package. The CSV is the machine-readable record an agency’s intake system parses and a spreadsheet reconciles column by column; the PDF is the human-readable statutory document an inspector reads and a producer files. They must carry the same rows, in the same order, formatted the same way, so that a value questioned in one can be found unchanged in the other. Rendering them from a single validated row set with one shared formatter — rather than two independently written code paths — is what keeps them from silently drifting apart across a library upgrade or a schema revision.

Parameter reference

Runnable implementation

The module below renders a validated ledger slice into a deterministic CSV and a reportlab PDF, then assembles them into a hashed package. It targets Python 3.10+, is fully typed, and depends on reportlab>=4.0; the CSV path uses only the standard library. Every source of nondeterminism — row order, number formatting, and PDF metadata timestamps — is pinned explicitly.

from __future__ import annotations

import csv
import hashlib
import io
import logging
from dataclasses import dataclass
from datetime import datetime, timezone
from decimal import Decimal

from reportlab.lib.pagesizes import LETTER
from reportlab.pdfgen import canvas

logger = logging.getLogger("audit.export")

# Jurisdiction column order is fixed here, not derived from dict iteration.
JURISDICTION_COLUMNS: dict[str, tuple[str, ...]] = {
    "EPA-FED": ("applied_at", "field_id", "epa_reg_no", "rate", "unit"),
}


@dataclass(frozen=True)
class ExportRow:
    applied_at: datetime      # tz-aware UTC
    field_id: str
    epa_reg_no: str
    rate: Decimal             # regulated: never float
    unit: str


def _validate(rows: list[ExportRow], columns: tuple[str, ...]) -> None:
    """Reject any row missing a required field before rendering begins."""
    for i, r in enumerate(rows):
        for col in columns:
            if getattr(r, col, None) in (None, ""):
                logger.error('{"event":"row_rejected","index":%d,"column":"%s"}', i, col)
                raise ValueError(f"row {i} missing required column {col!r}")


def _fmt(value: object, decimal_places: int) -> str:
    """Deterministic cell formatting: fixed-scale Decimals, ISO-UTC datetimes."""
    if isinstance(value, Decimal):
        return f"{value:.{decimal_places}f}"          # fixed scale, no float noise
    if isinstance(value, datetime):
        return value.astimezone(timezone.utc).isoformat()
    return str(value)


def render_csv(rows: list[ExportRow], jurisdiction: str,
               decimal_places: int = 4) -> bytes:
    """Byte-stable CSV: fixed column order, unix dialect, sorted rows."""
    columns = JURISDICTION_COLUMNS[jurisdiction]
    ordered = sorted(rows, key=lambda r: (r.applied_at, r.field_id))
    buf = io.StringIO()
    writer = csv.writer(buf, dialect="unix", lineterminator="\n")
    writer.writerow(columns)
    for r in ordered:
        writer.writerow([_fmt(getattr(r, c), decimal_places) for c in columns])
    return buf.getvalue().encode("utf-8")


def render_pdf(rows: list[ExportRow], jurisdiction: str,
               creation_date: datetime, decimal_places: int = 4) -> bytes:
    """Deterministic PDF: metadata date pinned to a data-derived value."""
    columns = JURISDICTION_COLUMNS[jurisdiction]
    ordered = sorted(rows, key=lambda r: (r.applied_at, r.field_id))
    buf = io.BytesIO()
    c = canvas.Canvas(buf, pagesize=LETTER)
    # Pinning the metadata date is what makes two renders byte-identical.
    c.setTitle(f"{jurisdiction} Pesticide Use Report")
    stamp = creation_date.astimezone(timezone.utc).strftime("D:%Y%m%d%H%M%SZ")
    c._doc.info.title = f"{jurisdiction} Pesticide Use Report"
    c._doc.info.producer = "crop-planning-export/1"
    c._doc.info.creator = "crop-planning-export/1"
    c._doc.info.CreationDate = stamp
    c._doc.info.ModDate = stamp
    y = 740
    c.setFont("Helvetica-Bold", 11)
    c.drawString(56, 760, f"{jurisdiction} Statutory Pesticide Use Record")
    c.setFont("Helvetica", 8)
    c.drawString(56, y, " | ".join(columns))
    for r in ordered:
        y -= 14
        if y < 56:                                   # fixed pagination threshold
            c.showPage()
            c.setFont("Helvetica", 8)
            y = 760
        line = " | ".join(_fmt(getattr(r, col), decimal_places) for col in columns)
        c.drawString(56, y, line)
    c.showPage()
    c.save()
    return buf.getvalue()


@dataclass(frozen=True)
class ExportPackage:
    csv_bytes: bytes
    pdf_bytes: bytes
    manifest_hash: str


def build_package(rows: list[ExportRow], jurisdiction: str,
                  creation_date: datetime, decimal_places: int = 4) -> ExportPackage:
    """Validate, render both formats, and hash the concatenated manifest."""
    columns = JURISDICTION_COLUMNS[jurisdiction]
    _validate(rows, columns)
    csv_bytes = render_csv(rows, jurisdiction, decimal_places)
    pdf_bytes = render_pdf(rows, jurisdiction, creation_date, decimal_places)
    manifest = hashlib.sha256()
    manifest.update(csv_bytes)
    manifest.update(pdf_bytes)
    digest = manifest.hexdigest()
    logger.info(
        '{"event":"package_built","jurisdiction":"%s","rows":%d,"manifest_hash":"%s"}',
        jurisdiction, len(rows), digest[:12],
    )
    return ExportPackage(csv_bytes, pdf_bytes, digest)

The reportlab CreationDate and ModDate are pinned to creation_date — a data-derived value such as the latest ledger timestamp — rather than the wall clock, because a live datetime.now() in PDF metadata is the single most common reason two otherwise-identical renders produce different bytes and a mismatched manifest hash.

The manifest hash is computed over the concatenation of both rendered artifacts, so it is a single fingerprint for the whole package: change one cell in either the CSV or the PDF and the hash moves. That fingerprint is what makes the filing archivable and later reconcilable. The recommended practice is to store the manifest_hash alongside the submission timestamp in the audit ledger itself, as its own chained entry, so the fact that this exact package was filed on this date becomes part of the tamper-evident record. When a regulator later asks a question, the producer regenerates the package from the same ledger slice, recomputes the hash, and compares it to the archived value — an exact match is proof that the document under discussion is the one that was filed, byte for byte, and not a later reconstruction that happens to look similar.

Log patterns and observable signals

A package built cleanly:

{"event": "package_built", "jurisdiction": "EPA-FED", "rows": 1284, "manifest_hash": "c4d90ab1f277"}

A row failed schema validation before rendering — nothing was written:

{"event": "row_rejected", "index": 57, "column": "epa_reg_no"}

Confirming reproducibility (two runs of the same slice):

{"event": "package_built", "jurisdiction": "EPA-FED", "rows": 1284, "manifest_hash": "c4d90ab1f277"}
{"event": "package_built", "jurisdiction": "EPA-FED", "rows": 1284, "manifest_hash": "c4d90ab1f277"}

Identical manifest_hash values across runs are the observable proof the export is deterministic. A row_rejected line means a required field was empty at the source, and the render was refused rather than emitting a blank cell a regulator would read as a missing record. If two runs disagree on the hash, a nondeterministic element leaked into rendering and must be found before the package is filed.

Safe override protocol

Occasionally a filing needs a format variant the standard template does not cover — a state agency that requires an extra column, or a supplemental annex. The override adds a template, never a special-case data path.

Guard conditions, all mandatory:

1. New jurisdiction, new template entry. A variant is registered as its own key in JURISDICTION_COLUMNS with an explicit column tuple; rows are never hand-edited to fit an existing template.
2. Validation still runs. The new template’s required columns pass through the same _validate gate, so an added column cannot silently emit blanks.
3. Reproducibility re-checked. The variant is rendered twice and the two manifest_hash values are asserted equal before it is used for a real filing.
4. Audit reference retained. The template revision and the operator who added it are recorded, consistent with EPA pesticide recordkeeping and USDA FSA retention expectations, so the exact format of a historical filing is reconstructable.

Troubleshooting

• Two renders of the same data produce different manifest hashes. Root cause: a live timestamp in PDF metadata, or ledger iteration order leaking into row order. Remediation: pin CreationDate/ModDate to a data-derived value and always sort rows by the fixed sort_by key before writing.
• Rates render with a long float tail (e.g. 1.9500000000000002). Root cause: a rate was a float before formatting. Remediation: keep rates as Decimal end to end and format with a fixed scale via _fmt; never let a float reach the writer.
• CSV opens correctly on one platform and shifts rows on another. Root cause: the Excel dialect’s \r\n terminator or a locale decimal separator changed the bytes. Remediation: force the unix dialect with an explicit \n terminator and ASCII-safe number formatting.
• row_rejected blocks the whole package. Root cause: a required column is empty at the ledger source. Remediation: fix the record upstream so the field is populated before it enters the ledger slice; do not disable strict_schema, which would file a blank cell a regulator reads as a data gap.
• A regenerated package no longer matches the filed one after a library upgrade. Root cause: a reportlab or CSV formatting default changed between versions. Remediation: pin the export library versions, keep formatting explicit rather than default-driven, and re-verify the manifest hash against the archived filing after any upgrade.
• The CSV and PDF disagree on a value for the same field. Root cause: the two formats were rendered from separately transformed row sets, so a fix applied to one path never reached the other. Remediation: render both from the single validated row set through the shared _fmt formatter, and add a reconciliation test that asserts every CSV cell appears in the PDF for the same row.

Frequently asked questions

Why must exports be byte-deterministic? A filed package may be regenerated to answer a question; if the copies differ, the discrepancy is itself a finding. Pinned ordering, formatting, and PDF metadata make two renders share one manifest hash.

Most common cause of a nondeterministic PDF? A live datetime.now() in the PDF metadata. Pin CreationDate/ModDate to a data-derived value; unordered rows and float rates are next.

Why validate rows before rendering? A blank required cell reads as a missing record to a regulator. Rejecting the row forces an upstream fix instead of filing an incomplete document.

Related

• Cryptographic Hash Chaining for Tamper-Evident Logs — the chained entries this renderer reads from and never mutates.
• Seasonal Compliance Report: From Telemetry to EPA Submission — the end-to-end workflow that calls this renderer at its final stages.

Up: Audit & Reporting · Agricultural Automation System Architecture & Compliance