Effort reporting and certification

Salary charged to a federal award is only defensible if the institution can show that the effort behind it was actually expended. When a principal investigator commits 25 percent of their academic-year effort to an NIH R01 in the proposal, payroll distributes 25 percent of their salary to that award’s fund, and — at the close of the effort period — the PI attests that the distribution reasonably reflects the work performed, the three numbers must agree, and the agreement must be recorded in a way no one can later alter. That reconciliation-and-attestation loop is what this guide automates, and it is one of the reporting subsystems anchored to the parent guide on Compliance Reporting & Budget Reconciliation. It reads the same validated grant records produced upstream by the Schema Validation Pipelines and shares its regulatory matrix with the University Policy Mapping Frameworks, so a certification statement rests on the same canonical field definitions as every other compliance deliverable.

University administrators, sponsored-programs staff, research compliance officers, and the Python automation developers who support them rely on this layer to replace the annual scramble of spreadsheet effort forms with a deterministic pipeline. By reconciling committed effort against payroll salary distribution before a statement is ever generated, the system catches over-the-cap charges and cost-share gaps at the point of measurement rather than during an audit, and it captures each PI sign-off as an immutable, hashed ledger entry that an auditor can reproduce years later.

Problem framing #

Effort reporting fails in quiet, expensive ways. A PI’s proposal commits 30 percent effort to a program-project grant, but a mid-year retroactive salary transfer moves only 22 percent of their pay to that fund — an 8-point variance that no one notices until a federal reviewer samples the award. A senior investigator’s institutional base salary exceeds the NIH salary cap, so the fraction of salary that may be charged to the federal award is smaller than their actual effort fraction, and a naive reconciliation flags a “variance” that is really a cap adjustment. A co-investigator contributes committed cost-shared effort that, by definition, is not charged to the federal fund and therefore never appears in payroll distribution — so a system that reconciles only against payroll reports the commitment as unmet.

The purpose of this layer is to make certification a measurement-and-attestation step rather than a data-entry exercise. Three contracts, implemented in the rest of this page, hold the line:

• Deterministic reconciliation. The same commitments and the same payroll distribution always produce the same variance and the same cert_status. Nothing depends on the order rows arrive in or on when the batch runs.
• Idempotent sign-off. Each certification is keyed on (person_id, award_id, period); a re-run of the pipeline neither generates a second statement for a period already certified nor overwrites a captured sign-off hash.
• Immutable trail. Every attestation is written once to an append-only ledger with a SHA-256 sign_off_hash over the certified figures, so the record an auditor reads is provably the record the PI signed.

Policy constraints #

The governing rule is 2 CFR 200.430 (compensation — personal services), the Uniform Guidance section that replaced the old A-21 effort-reporting regime. It does not mandate a particular form or piece of software; it requires that charges to federal awards for salaries and wages be based on records that accurately reflect the work performed, be supported by a system of internal control that provides reasonable assurance the charges are accurate, allocable, and reasonable, and be incorporated into official records. Critically, 200.430(g) contemplates that budget estimates alone do not qualify as support, but they may be used for interim accounting purposes provided the system produces an after-the-fact review that reconciles estimated to actual effort. That after-the-fact review is exactly the reconciliation and certification this guide produces.

Operational boundary. Policy dictates what must reconcile, who may certify, and how long the record is kept; the implementation performs the mechanical reconciliation, statement generation, and hashing. The engine must never silently move a variance to zero to make a statement “clean” — an unexplained variance is surfaced to a compliance officer, and a cost-shared or over-the-cap fraction is accounted for explicitly, not absorbed. No certification may be back-dated to hit a reporting deadline; the certified_at timestamp is the moment of attestation and nothing else. Credential scoping and network isolation for the certification workers follow the Security Boundary Configuration.

Data schema & field mapping #

A certification record is a versioned compliance artifact, not a transient row. It joins two independently sourced numbers — the commitment from the award and the charged fraction from payroll — with the outcome of the reconciliation and the attestation that closes it. Every field maps to a policy rule, and the system-owned fields (variance, sign_off_hash, certified_at) are computed, never entered by hand.

The variance, salary_cap_applied, certified_at, and sign_off_hash fields are system-owned; committed_pct and charged_pct are mapped from the award and payroll feeds respectively. Stamping the resolved figures into the hash at attestation time is what lets an auditor later prove which numbers the PI actually certified — indispensable when a retroactive salary transfer changes the payroll distribution after the fact.

Implementation #

The certification layer has three composable parts: a deterministic reconciliation function that applies the salary cap and computes variance from the two effort figures, a statement builder that materializes a per-period certification with a stable cert_status, and an idempotent sign-off ledger that records each attestation exactly once with a reproducible hash.

Deterministic reconciliation with the salary cap #

Reconciliation is pure arithmetic over Decimal, so a re-run over unchanged inputs yields a byte-identical result. The subtlety is the NIH salary cap: when a PI’s institutional base salary exceeds the annual cap, the fraction of salary chargeable to the federal award is smaller than their effort fraction, because effort above the cap must be paid from non-federal funds. Comparing the cap-adjusted charged fraction against the commitment is what keeps a cap adjustment from masquerading as a variance.

import hashlib
import json
import logging
from dataclasses import dataclass
from datetime import datetime, timezone
from decimal import Decimal, ROUND_HALF_UP

logging.basicConfig(level=logging.INFO, format="%(asctime)s [EFFORT] %(message)s")
logger = logging.getLogger(__name__)

CENT = Decimal("0.01")
# Material variance threshold (percentage points) before a statement is flagged.
VARIANCE_TOLERANCE = Decimal("2.00")


@dataclass(frozen=True)
class EffortInputs:
    person_id: str
    award_id: str
    period: str
    committed_pct: Decimal        # from the proposal / Notice of Award
    charged_pct: Decimal          # from payroll salary distribution
    institutional_base: Decimal   # annualized institutional base salary
    salary_cap: Decimal           # current NIH salary cap (annualized)


def reconcile(inp: EffortInputs) -> dict:
    """Deterministically reconcile committed vs charged effort, applying the
    NIH salary cap. Returns a fully-resolved certification record (unsigned)."""
    cap_applied = inp.institutional_base > inp.salary_cap
    if cap_applied:
        # Effort above the cap is charged to non-federal funds; the fraction of
        # salary the federal award may bear is scaled down by cap/base. The
        # commitment is measured against this cap-adjusted charged fraction so a
        # cap adjustment is not mistaken for unmet effort.
        cap_factor = (inp.salary_cap / inp.institutional_base)
        effective_charged = (inp.charged_pct * cap_factor).quantize(CENT, ROUND_HALF_UP)
    else:
        effective_charged = inp.charged_pct.quantize(CENT, ROUND_HALF_UP)

    variance = (effective_charged - inp.committed_pct).quantize(CENT, ROUND_HALF_UP)
    status = "reconciled" if abs(variance) <= VARIANCE_TOLERANCE else "escalated"

    return {
        "person_id": inp.person_id,
        "award_id": inp.award_id,
        "period": inp.period,
        "committed_pct": str(inp.committed_pct),
        "charged_pct": str(effective_charged),
        "variance": str(variance),
        "salary_cap_applied": cap_applied,
        "cert_status": status,
        "certified_at": None,
        "sign_off_hash": None,
    }

Reconciling in Decimal rather than binary float is a compliance requirement, not a stylistic one: a 0.01-point rounding drift compounded across hundreds of awards becomes a material misstatement in a federal financial report. Cost-shared effort — committed but deliberately not charged to the federal fund — is carried on a separate commitment record so it is never scored as a payroll shortfall; that reconciliation shares its rules with the Indirect Cost Recovery Reconciliation cluster.

Idempotent sign-off ledger #

An attestation must be recorded exactly once. The sign-off is keyed on (person_id, award_id, period), and the write is guarded so that a certification already carrying a sign_off_hash is never overwritten by a later run — the ledger is append-only, and the hash is the non-repudiation anchor.

from sqlalchemy import Column, String, Boolean, DateTime, Numeric, UniqueConstraint
from sqlalchemy.orm import declarative_base, Session
from sqlalchemy.dialects.postgresql import insert as pg_insert

Base = declarative_base()


class CertificationLedger(Base):
    __tablename__ = "effort_certifications"
    person_id = Column(String(64), primary_key=True)
    award_id = Column(String(32), primary_key=True)
    period = Column(String(16), primary_key=True)
    committed_pct = Column(Numeric(5, 2), nullable=False)
    charged_pct = Column(Numeric(5, 2), nullable=False)
    variance = Column(Numeric(5, 2), nullable=False)
    salary_cap_applied = Column(Boolean, nullable=False, default=False)
    cert_status = Column(String(16), nullable=False)
    certified_at = Column(DateTime(timezone=True))
    sign_off_hash = Column(String(64))
    __table_args__ = (UniqueConstraint("person_id", "award_id", "period"),)


def sign_off_digest(record: dict, certifier: str, certified_at: datetime) -> str:
    """Deterministic SHA-256 over the certified figures plus certifier identity.
    Recomputing this over the stored row must reproduce the same digest."""
    canonical = json.dumps(
        {
            "person_id": record["person_id"],
            "award_id": record["award_id"],
            "period": record["period"],
            "committed_pct": record["committed_pct"],
            "charged_pct": record["charged_pct"],
            "variance": record["variance"],
            "salary_cap_applied": record["salary_cap_applied"],
            "certifier": certifier,
            "certified_at": certified_at.isoformat(),
        },
        sort_keys=True,
        separators=(",", ":"),
    )
    return hashlib.sha256(canonical.encode("utf-8")).hexdigest()


def capture_sign_off(session: Session, record: dict, certifier: str) -> str:
    """Record a PI attestation exactly once. A period already signed is never
    re-signed, so replaying the batch is a no-op for certified statements."""
    now = datetime.now(timezone.utc)
    digest = sign_off_digest(record, certifier, now)

    stmt = pg_insert(CertificationLedger).values(
        certified_at=now,
        cert_status="certified",
        sign_off_hash=digest,
        **{k: record[k] for k in (
            "person_id", "award_id", "period", "committed_pct",
            "charged_pct", "variance", "salary_cap_applied",
        )},
    )
    # Idempotent guard: only rows that have NOT yet been signed may transition
    # to certified. An existing sign_off_hash is immutable.
    stmt = stmt.on_conflict_do_update(
        index_elements=["person_id", "award_id", "period"],
        set_={"cert_status": "certified", "certified_at": now, "sign_off_hash": digest},
        where=(CertificationLedger.sign_off_hash.is_(None)),
    )
    result = session.execute(stmt)
    session.commit()
    if result.rowcount:
        logger.info("certified %s/%s/%s", record["person_id"], record["award_id"], record["period"])
    return digest

The on_conflict_do_update guarded by sign_off_hash IS NULL is what makes attestation idempotent at the database layer: a pending statement transitions to certified exactly once, and any subsequent replay leaves the signed row — and its hash — untouched. The per-period reminder and escalation cadence that drives a PI to this sign-off is the subject of the child how-to, Automating Effort Certification Reminders for PIs.

Integration points #

Certification workers never write to the payroll or HR systems of record; they read salary distribution snapshots and commitment records, and they emit certification statements and ledger entries that adjacent systems consume by key. Each integration has an explicit contract:

• Payroll / salary distribution. The reconciliation engine consumes a period-close snapshot of each person’s charged fraction per fund. Because the snapshot is immutable once the period closes, a retroactive transfer opens a new period record rather than mutating a certified one.
• Award / commitments. Committed effort is read from validated award records that entered through the Schema Validation Pipelines, so a certification always references an award identifier that already passed format and policy checks.
• Audit reporting. Signed certification rows feed the Immutable Audit Report Generation cluster, which packages them into the tamper-evident deliverables a sponsor’s auditor reads.

An example payroll snapshot consumed at period close:

{
  "person_id": "HR-448120",
  "period": "2026-AY1",
  "institutional_base": "310000.00",
  "salary_cap": "221900.00",
  "distributions": [
    {"award_id": "NI-2026-004417", "charged_pct": "30.00"},
    {"award_id": "NS-2025-118842", "charged_pct": "15.00"}
  ]
}

Verification & audit #

Every attestation appends one row to the append-only effort_certifications ledger (the composite key, both effort figures, the variance, the cap flag, certified_at, and the sign_off_hash). This ledger is the artifact a compliance officer reconstructs an effort audit from, and it lets any certification run be verified or reproduced.

To confirm a run was correct:

1. Reconciliation determinism. Re-run reconcile over the same inputs; the variance, charged_pct, and salary_cap_applied must match the stored row exactly. A difference means a non-Decimal value or a mutated input leaked in.
2. Reproduce the sign-off hash. Recompute sign_off_digest over the stored certified figures, certifier, and certified_at; it must equal the stored sign_off_hash. An equal digest proves the row is the one the PI signed.
3. Idempotency dry-run. Replay capture_sign_off for a period already certified. The row count of affected rows must be zero, and the sign_off_hash and certified_at must be unchanged.

from sqlalchemy import select


def verify_certification(session: Session, person_id: str, award_id: str, period: str) -> bool:
    row = session.execute(
        select(CertificationLedger).where(
            CertificationLedger.person_id == person_id,
            CertificationLedger.award_id == award_id,
            CertificationLedger.period == period,
        )
    ).scalar_one()
    record = {
        "person_id": row.person_id, "award_id": row.award_id, "period": row.period,
        "committed_pct": str(row.committed_pct), "charged_pct": str(row.charged_pct),
        "variance": str(row.variance), "salary_cap_applied": row.salary_cap_applied,
    }
    recomputed = sign_off_digest(record, "certifier-of-record", row.certified_at)
    return recomputed == row.sign_off_hash

Because the ledger is append-only and hash-addressed, an auditor can pin any federal salary charge back to the exact commitment, payroll distribution, and moment of attestation that support it. All certification records must be retained for the period required by 2 CFR 200.333–.334 — generally seven years after final financial report submission — with cryptographic checksums to prevent tampering.

Failure modes & recovery #

When effort pipelines encounter cap edge cases or late payroll transfers, operators isolate the failure without invalidating already-signed certifications. Every recovery is idempotent-safe: replaying it cannot double-sign or overwrite a captured attestation.

Role boundaries. Compliance officers own the certification policy — cadence, certifier of record, variance tolerance — and adjudicate escalated variances; they do not edit pipeline code. Python automation developers own reconciliation determinism, the idempotent ledger, and reminder routing; they do not alter the salary cap or effort thresholds. Sponsored-programs staff own commitment accuracy at the source. When a payroll feed is delayed past a period close, buffer the reconciliation and escalate rather than certifying against stale figures; extended outages route through the Fallback Routing Protocols.

Frequently asked questions #

Related #

• Parent guide: Compliance Reporting & Budget Reconciliation
• Child how-to: Automating Effort Certification Reminders for PIs — the reminder cadence that drives sign-off
• Immutable Audit Report Generation — packaging signed certifications into sponsor-ready deliverables
• Indirect Cost Recovery Reconciliation — the sibling reconciliation that shares Decimal and cost-share rules
• University Policy Mapping Frameworks — the regulatory matrix these certifications encode