Modeling grant closeout state transitions in SQLAlchemy

Problem statement #

You need an explicit grant lifecycle state machine persisted in SQLAlchemy where an award can only advance through legal transitions — pre-award to active, active to closeout, and so on — where an illegal jump is refused rather than silently written, and where every accepted transition is applied in one transaction and appended to an immutable audit ledger with its previous state, new state, actor, and a verifying hash, so no award is ever posted for final financial reporting from a state it never legally reached.

This task sits under Grant Lifecycle Architecture Design, part of Core Architecture & Policy Mapping for Research Grants. The parent guide argues that the lifecycle is a state machine; this page is the concrete SQLAlchemy implementation of the closeout portion of that machine. It assumes the canonical award record already exists — populated by the mapping pipeline in How to Map NIH Grant Schemas to Internal Databases — and focuses narrowly on governing how that record’s state is allowed to change.

Prerequisites #

• Python 3.10+ (uses enum, datetime.now(timezone.utc), and hashlib.sha256).
• Libraries: SQLAlchemy>=2.0 with psycopg[binary] for transactional PostgreSQL and row locking. Install with pip install "SQLAlchemy>=2.0" "psycopg[binary]".
• Environment variables (never hard-code credentials, per Security Boundary Configuration):
  • GRANT_DB_URL — the SQLAlchemy connection string for the award and ledger tables.
• Policy config: the allowed-transitions map and the closeout-window rules, kept in version-controlled config alongside your University Policy Mapping Frameworks. The 2 CFR 200.344 closeout deadline drives the transition into and out of the closeout-pending state.

Step-by-step implementation #

The lifecycle has five states — PRE_AWARD, ACTIVE, AT_RISK, CLOSEOUT_PENDING, and CLOSED — and a transition is legal only if the target appears in the current state’s allowed set. The transition function loads the award under a row lock, validates the requested move against the map, applies it and appends a hash-chained ledger row in a single transaction, and refuses anything illegal. CLOSED is terminal: nothing leaves it.

Figure: an award advances only along mapped edges; CLOSED is terminal and every edge appends one ledger row.

Step 1 — Declare the states and the allowed-transitions map #

The states are an enum and the legal moves are a dictionary from each state to the set of states it may advance to. This map is the policy: an examiner can read it directly. PRE_AWARD may only become ACTIVE; CLOSED maps to an empty set, making it terminal by construction.

from enum import Enum


class GrantState(str, Enum):
    PRE_AWARD = "PRE_AWARD"
    ACTIVE = "ACTIVE"
    AT_RISK = "AT_RISK"
    CLOSEOUT_PENDING = "CLOSEOUT_PENDING"
    CLOSED = "CLOSED"


# The allowed-transitions map is the machine-readable statement of policy.
# Any edge NOT listed here is illegal and will be refused, not written.
ALLOWED: dict[GrantState, set[GrantState]] = {
    GrantState.PRE_AWARD: {GrantState.ACTIVE},
    GrantState.ACTIVE: {GrantState.AT_RISK, GrantState.CLOSEOUT_PENDING},
    GrantState.AT_RISK: {GrantState.ACTIVE, GrantState.CLOSEOUT_PENDING},
    GrantState.CLOSEOUT_PENDING: {GrantState.CLOSED},
    GrantState.CLOSED: set(),  # terminal: no award ever leaves CLOSED
}

Step 2 — Declare the award record and the immutable ledger #

The Grant row holds the current state only; history lives in the append-only GrantTransition ledger. Each ledger row records prev_state, new_state, the acting actor, and a SHA-256 entry_hash chained over the previous row’s hash — so tampering with any historical transition breaks the chain and is detectable.

from datetime import datetime, timezone

from sqlalchemy import Column, DateTime, Integer, String
from sqlalchemy.orm import declarative_base

Base = declarative_base()


class Grant(Base):
    __tablename__ = "grants"
    id = Column(Integer, primary_key=True)
    award_id = Column(String(50), unique=True, nullable=False)
    state = Column(String(20), nullable=False, default=GrantState.PRE_AWARD.value)
    period_end = Column(DateTime(timezone=True), nullable=False)
    updated_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc))


class GrantTransition(Base):
    __tablename__ = "grant_transitions"
    id = Column(Integer, primary_key=True)
    award_id = Column(String(50), nullable=False, index=True)
    prev_state = Column(String(20), nullable=False)
    new_state = Column(String(20), nullable=False)
    actor = Column(String(120), nullable=False)          # who authorized the move
    prev_hash = Column(String(64), nullable=False)
    entry_hash = Column(String(64), nullable=False)      # SHA-256 over the row + prev_hash
    recorded_at = Column(DateTime(timezone=True), nullable=False)

Step 3 — Apply a transition atomically under a row lock #

The transition function is the only path that mutates state. It loads the award FOR UPDATE so two concurrent transitions serialize, validates the requested edge against ALLOWED, and — only if legal — updates the state and appends a hash-chained ledger row inside one transaction. An illegal edge raises before any write.

import hashlib

from sqlalchemy import select
from sqlalchemy.orm import Session


class IllegalTransition(Exception):
    """Requested edge is not in the allowed-transitions map."""


def transition(session: Session, award_id: str, target: GrantState, actor: str) -> dict:
    """Validate and apply one lifecycle transition atomically; append to the ledger."""
    now = datetime.now(timezone.utc)

    # Row lock: SELECT ... FOR UPDATE serializes concurrent transitions so two
    # workers cannot both advance the same award from the same state.
    grant = session.execute(
        select(Grant).where(Grant.award_id == award_id).with_for_update()
    ).scalar_one()

    current = GrantState(grant.state)
    if target not in ALLOWED[current]:
        # Illegal jump (e.g. PRE_AWARD -> CLOSED) is refused, never written.
        raise IllegalTransition(f"{current.value} -> {target.value} not permitted")

    # Hash-chain: the new entry_hash covers the prior hash, so any later edit to
    # a historical row breaks the chain and is detectable on verification.
    prev = session.execute(
        select(GrantTransition.entry_hash)
        .where(GrantTransition.award_id == award_id)
        .order_by(GrantTransition.id.desc()).limit(1)
    ).scalar_one_or_none() or "0" * 64

    canonical = f"{award_id}|{current.value}|{target.value}|{actor}|{now.isoformat()}|{prev}"
    entry_hash = hashlib.sha256(canonical.encode("utf-8")).hexdigest()

    grant.state = target.value
    grant.updated_at = now
    session.add(GrantTransition(
        award_id=award_id, prev_state=current.value, new_state=target.value,
        actor=actor, prev_hash=prev, entry_hash=entry_hash, recorded_at=now,
    ))
    session.commit()
    return {"award_id": award_id, "from": current.value, "to": target.value, "entry_hash": entry_hash}

The CLOSEOUT_PENDING → CLOSED edge is only taken once the final financial report is filed, and the 120-day clock that governs it feeds the reporting deliverables owned by Compliance Reporting & Budget Reconciliation. If a downstream system that must observe the new state is unreachable, the transition still commits and the notification is retried through the Fallback Routing Protocols.

Schema and field reference #

Verification #

1. Legal path: advance a fixture award PRE_AWARD → ACTIVE → CLOSEOUT_PENDING → CLOSED and confirm each call succeeds and appends exactly one ledger row.
2. Illegal jump refused: call transition for PRE_AWARD → CLOSED and assert it raises IllegalTransition and writes no row — the award’s state is unchanged.
3. Terminal state: attempt any transition out of CLOSED and confirm it is refused, since ALLOWED[CLOSED] is empty.
4. Hash chain intact: walk the ledger for an award recomputing each entry_hash from its row plus the prior prev_hash; a mismatch localizes a tampered or reordered row.
5. Concurrency: issue two simultaneous transitions on the same award; the row lock must serialize them so exactly one of two conflicting advances succeeds.

Troubleshooting #

• An illegal transition still lands. If an award reaches a state it should not, some path is mutating Grant.state directly instead of going through transition. Make the transition function the sole writer of the state column, and add a database CHECK or trigger as a backstop; the ALLOWED map should be the only definition of a legal edge.
• Two workers advance the same award at once. Without the row lock, two transitions can both read ACTIVE, both validate, and both write — double-posting a closeout. SELECT ... FOR UPDATE serializes them so the second worker re-reads the already-advanced state and its now-illegal edge is refused. Never validate against a state read outside the locking transaction.
• Closeout deadline is missed silently. The 2 CFR 200.344 window requires the final financial report within 120 days of the period-of-performance end. Model that as a scheduled check that moves an overdue CLOSEOUT_PENDING award to AT_RISK and escalates, rather than leaving it to advance to CLOSED on paper while the report is late. The deadline is a policy input, not an implementation detail.

Frequently asked questions #

Related #

• Up to the parent topic: Grant Lifecycle Architecture Design
• How to Map NIH Grant Schemas to Internal Databases — the mapping that populates the award record
• Compliance Reporting & Budget Reconciliation — the closeout deliverables the 120-day window drives
• Fallback Routing Protocols — retrying downstream notification of a committed transition