Automating Period-End Reconciliation Reports

A period-end reconciliation report earns its authority only when a second person, running the same code against the same ledger a month later, reproduces it byte-for-byte and lands on the identical closing accrual — and when that closing figure ties exactly to the general ledger control account. Most reporting pipelines fail one of those two tests: a float sum drifts by a cent, an unpinned query re-reads a ledger that moved overnight, or the report totals to a number the GL never saw. This page documents the exact procedure for building a report that passes both tests, enforcing the accrual identity opening + new − paid − written-off = closing at every step and carrying row-level lineage so any figure can be drilled to its source lines. It is the implementation-level companion to the reconciliation reporting automation topic, which governs the broader reporting layer inside Settlement, Deduction & Financial Close.

Prerequisites

Before automating the report, confirm the following are in place:

• An immutable ledger snapshot. The report reads from a content-addressed snapshot of the settlement sub-ledger — never a live table. The snapshot id pins the exact set of rows in force at close, so an accrual posted at 23:59 on the last day cannot change the report after the fact. Snapshotting is upstream of this page and typically produced by the month-end close sequencing freeze step.
• A pinned config_version. The measure definitions — which movement_type values roll into opening, new_accrual, paid, and written_off — are versioned. Two runs with different definitions are not the same report, so the version is a first-class report parameter.
• A GL control-account balance for the period. The closing accrual must tie to a specific general ledger account; that balance comes from the accrual posting GL integration layer and is the external anchor the report validates against.
• Python 3.11+ with pydantic>=2.6 and polars>=0.20. All aggregation uses decimal.Decimal; no monetary value is ever parsed as a float. pandas works identically where noted.
• Access role: read access to the ledger snapshot and the GL balance table, and write-once access to the report artifact store. The reporting role publishes artifacts but cannot mutate the sub-ledger or the GL.

Step-by-Step Implementation

Step 1 — Pin the source snapshot and config_version

Model the run parameters as a typed record so an unpinned or malformed request fails at construction rather than producing an unreproducible report. The ledger_snapshot_id and config_version together make the run deterministic; without both, the output cannot be replayed.

from decimal import Decimal
from pydantic import BaseModel, field_validator

class ReportParams(BaseModel):
    entity_id: str
    fiscal_period: str          # 'YYYY-MM'
    ledger_snapshot_id: str     # content-addressed, immutable
    config_version: str         # semantic version of the measure definitions
    gl_account: str
    reporting_timezone: str = "UTC"
    tolerance: Decimal = Decimal("0.00")

    @field_validator("fiscal_period")
    @classmethod
    def _period_shape(cls, v: str) -> str:
        year, _, month = v.partition("-")
        if not (len(year) == 4 and month and 1 <= int(month) <= 12):
            raise ValueError("fiscal_period must be 'YYYY-MM'")
        return v

    @field_validator("tolerance", mode="before")
    @classmethod
    def _no_float_tolerance(cls, v):
        if isinstance(v, float):
            raise ValueError("tolerance must be a string or Decimal, not float")
        return v

Validation check: construct ReportParams with an empty ledger_snapshot_id or a missing config_version and assert it raises ValidationError. A report parameterized on a live query rather than a pinned snapshot must never reach aggregation.

Step 2 — Aggregate measures with Decimal-safe sums

Load the pinned snapshot and fold each movement into its measure bucket using decimal.Decimal throughout. Amounts are stored as strings and parsed with Decimal, so no double-precision rounding ever enters the total. The measure map is the versioned definition referenced by config_version.

import polars as pl
from decimal import Decimal, getcontext

getcontext().prec = 34   # wide enough for ledger-scale exact sums

MEASURE_MAP = {                # governed by config_version
    "opening_balance": "opening",
    "accrual":         "new_accrual",
    "payment":         "paid",
    "write_off":       "written_off",
}

def load_snapshot(path: str) -> pl.DataFrame:
    # amount kept as text so a float never touches the ledger
    return pl.read_parquet(path).with_columns(pl.col("amount").cast(pl.Utf8))

def aggregate(df: pl.DataFrame) -> dict[str, Decimal]:
    out = {k: Decimal("0") for k in ("opening", "new_accrual", "paid", "written_off")}
    for mtype, amount in zip(df["movement_type"], df["amount"]):
        bucket = MEASURE_MAP.get(mtype)
        if bucket:
            out[bucket] += Decimal(amount)     # str -> Decimal, exact
    return out

def closing_accrual(m: dict[str, Decimal]) -> Decimal:
    return m["opening"] + m["new_accrual"] - m["paid"] - m["written_off"]

Validation check: assert every non-null amount in the snapshot parses to Decimal without exception, and assert that the sum of the four bucket totals equals the sum over the snapshot filtered to the mapped movement_type values. A movement type absent from MEASURE_MAP must be surfaced as an unclassified-movement error, never silently dropped.

Step 3 — Assert the report ties to the sub-ledger and GL

A computed closing figure is meaningless until it reconciles to the GL control account. Raise a hard exception on any variance above tolerance so a broken report stops the pipeline instead of publishing a wrong number.

class ReconciliationBreak(Exception):
    pass

def assert_ties_to_gl(closing: Decimal, gl_balance: Decimal,
                      tolerance: Decimal) -> Decimal:
    variance = (closing - gl_balance).quantize(Decimal("0.01"))
    if abs(variance) > tolerance:
        raise ReconciliationBreak(
            f"closing {closing} does not tie to GL {gl_balance} "
            f"(variance {variance}, tolerance {tolerance})"
        )
    return variance

Validation check: feed a gl_balance that differs from closing by one cent with tolerance = Decimal("0.00") and assert ReconciliationBreak is raised; feed a matching balance and assert the returned variance is Decimal("0.00"). The internal identity from Step 2 and the external GL tie are both required — passing one is not passing the other.

Step 4 — Attach row-level lineage

Every aggregated figure must be drillable to the exact source lines that composed it. Build a lineage index that partitions the snapshot’s line_id values across the four buckets, sorted for reproducibility. This is what turns a total into an auditable claim.

def build_lineage(df: pl.DataFrame) -> dict[str, list[str]]:
    lineage: dict[str, list[str]] = {
        "opening": [], "new_accrual": [], "paid": [], "written_off": []
    }
    # sort by line_id so the artifact is byte-stable across runs
    for line_id, mtype in sorted(zip(df["line_id"], df["movement_type"])):
        bucket = MEASURE_MAP.get(mtype)
        if bucket:
            lineage[bucket].append(line_id)
    return lineage

def assert_partition(df: pl.DataFrame, lineage: dict[str, list[str]]) -> None:
    mapped = sum(len(v) for v in lineage.values())
    expected = df.filter(pl.col("movement_type").is_in(list(MEASURE_MAP)))
    assert mapped == expected.height, "lineage does not cover all mapped lines"
    flat = [lid for v in lineage.values() for lid in v]
    assert len(flat) == len(set(flat)), "a line_id appears in more than one measure"

Validation check: run assert_partition and confirm every mapped line appears in exactly one bucket and no line_id is duplicated across buckets. A drill-down from any figure to its line_id set must sum back to that figure under Decimal; if it does not, the lineage is not a faithful decomposition of the total.

Step 5 — Freeze an immutable report artifact

Serialize the parameters, the four measures, the closing figure, the GL tie, and the lineage into a single record; hash it deterministically; and write it once. The report_hash is the reproducibility contract — the same snapshot and config_version must always produce the same hash.

import hashlib, json

def freeze_report(p: ReportParams, m: dict[str, Decimal],
                  closing: Decimal, gl_balance: Decimal,
                  variance: Decimal, lineage: dict[str, list[str]]) -> dict:
    body = {
        "entity_id": p.entity_id,
        "fiscal_period": p.fiscal_period,
        "ledger_snapshot_id": p.ledger_snapshot_id,
        "config_version": p.config_version,
        "gl_account": p.gl_account,
        "opening": str(m["opening"]),
        "new_accrual": str(m["new_accrual"]),
        "paid": str(m["paid"]),
        "written_off": str(m["written_off"]),
        "closing_accrual": str(closing),
        "gl_balance": str(gl_balance),
        "variance": str(variance),
        "lineage": lineage,
    }
    blob = json.dumps(body, sort_keys=True, separators=(",", ":"))
    body["report_hash"] = hashlib.sha256(blob.encode()).hexdigest()
    return body   # persisted write-once; a second write to the same key is rejected

Validation check: run freeze_report twice against the same snapshot and config_version and assert the two report_hash values are identical; then bump config_version and assert the hash changes. A non-stable hash on identical inputs means an unpinned read or an unordered structure leaked into the artifact.

Common Failure Modes and Fixes

1. float in aggregation. Parsing amount as a JSON number or casting to pl.Float64 reintroduces double-precision drift that pushes the closing figure a cent or two off the GL and fails the tie. Fix: keep amounts as strings at rest, parse with decimal.Decimal, and set a wide getcontext().prec; never let a float reach the sum (Step 2).
2. Non-reproducible ordering. Folding an unordered groupby result or serializing a dict without sort_keys produces a different report_hash on each run even when the numbers match. Fix: sort lineage by line_id (Step 4) and serialize with sort_keys=True (Step 5) so the artifact is byte-stable.
3. Totals not tying to GL. An unposted accrual, a payment booked to the wrong control account, or a partial GL sync leaves the internal closing figure correct but out of line with the ledger. Fix: raise ReconciliationBreak on any variance above tolerance (Step 3) and route the break to investigation rather than publishing; reconcile against the balance from accrual posting GL integration.
4. Missing config_version. Running the report without pinning the measure definitions lets a silent change to which movement_type rolls into paid alter history without a trace. Fix: make config_version a required parameter (Step 1) and fold it into the report_hash so any definition change is visible as a new artifact.
5. Timezone and period-boundary leakage. A movement timestamped in local time but bucketed in UTC — or vice versa — can fall into the adjacent fiscal period, so the same row is counted twice or not at all across two reports. Fix: normalize all movement timestamps to the reporting_timezone before assigning a fiscal_period, and assert that the snapshot’s period boundaries are half-open [start, end) so no row lands in two periods.

Operational Checklist

• The report reads a content-addressed ledger_snapshot_id, never a live table.
• config_version is a required parameter and is folded into the report hash.
• Every amount is parsed with decimal.Decimal; no float enters any sum.
• The four measures satisfy opening + new_accrual − paid − written_off = closing.
• Closing ties to the GL control account within tolerance, or a ReconciliationBreak halts the run.
• Lineage partitions every mapped line_id into exactly one measure, with no duplicates.
• Each figure drills down to a line_id set that sums back to that figure under Decimal.
• Period bucketing uses half-open boundaries in the declared reporting_timezone.
• The artifact is written once with a stable report_hash that replays identically.

Frequently Asked Questions

Why read a pinned snapshot instead of querying the ledger directly? A live query is not reproducible: an accrual posted or a payment cleared after the report ran would silently change any re-run, and the audit trail could no longer explain the published number. Pinning a content-addressed ledger_snapshot_id fixes the exact row set in force at close, so the report is a deterministic function of the snapshot and the config_version. Anyone can replay it months later and land on the identical closing accrual and report_hash.

How does the report tie to the general ledger? The internal identity is computed first — opening plus new accruals minus payments minus write-offs — and its closing figure is then compared to the GL control-account balance for the period. If the variance exceeds the configured tolerance, the pipeline raises a reconciliation break and stops rather than publishing. Passing the internal identity is necessary but not sufficient; the external GL tie is the independent anchor that catches unposted entries and mis-booked accounts.

Why store amounts as strings and use decimal.Decimal? JSON and float columns are double-precision, and their rounding drift is immaterial on one line but accumulates across a full period into a variance that breaks the GL tie by cents. Holding amounts as strings at rest and folding them with decimal.Decimal under a wide precision context keeps every total exact and keeps the report_hash identical across environments, which is what makes the report reproducible and audit-defensible.

What makes the report artifact immutable and reproducible? The artifact serializes its parameters, measures, closing figure, GL tie, and lineage with sorted keys, hashes the result with SHA-256, and is written once to a store that rejects a second write to the same key. Because the snapshot id and config_version are part of the hashed body, the same inputs always yield the same report_hash, and any change to the measure definitions or the underlying data surfaces as a distinct artifact rather than a silent overwrite.

Related

• Parent topic: Reconciliation Reporting Automation — the reporting layer this procedure implements.
• Posting Rebate Accruals to the General Ledger — how the GL control-account balance this report ties to is produced.
• Accrual Posting & GL Integration — the broader posting layer that owns the control account.
• Month-End Close Sequencing — the freeze step that produces the immutable ledger snapshot this report reads.

Up one level: Reconciliation Reporting Automation