Versioning Lease Amendments in a Lease Database

When a CAM reconciliation for a prior year is closed and signed, every recoverable amount in it must resolve against the lease terms that were legally in force during that year — never against an amendment a tenant negotiated afterward. Getting that right is a versioning problem, and it belongs in the schema of the lease abstraction database rather than in ad-hoc “as-of” logic scattered through the reconciliation code. This page shows how to store lease terms bitemporally — one axis for when a term is in force, a second for when you learned about it — so that a point-in-time query returns exactly the pro-rata share, expense cap, or exclusion rule that governed a given reconciliation period, and a closed statement never silently changes when a later amendment lands.

Context & When to Use This Approach #

Commercial leases rarely stay still. Over a ten-year term a tenant might sign a first amendment adjusting the pro-rata share after a suite expansion, a second amendment adding a controllable-expense cap, and a third redefining the fiscal year. Each amendment changes the inputs a reconciliation consumes, and — critically — each one takes effect on a specific date, not retroactively to the start of the lease. A single-row-per-lease schema that you UPDATE in place cannot answer the only question reconciliation ever asks: what did this term say on the date the recovery accrued?

Reach for bitemporal versioning when any of the following are true:

• You reconcile prior periods. A year-end true-up for 2023 runs in early 2024 and must use the 2023 terms. If a 2024 amendment has already overwritten the row, the true-up is wrong and no one can see why.
• Amendments arrive out of order. A retroactive amendment signed in June that is effective the previous January is routine. You need to record when you learned it separately from when it applies, or a statement you closed in April will appear to have always known about it.
• Closed statements must stay closed. Once a tenant statement is issued, its numbers are a legal position. A later correction should produce a new, dated adjustment — not a quiet mutation of the figures the tenant already received.
• An audit can ask “as of” anything. Regulators and tenant auditors reconstruct the state of the record at a past instant. That is only possible if history is never destroyed.

The two time axes are the whole idea. Valid time (effective_from, effective_to) is when a term governs the real world — the dates written into the amendment. Transaction time (recorded_at) is when your system committed the fact. Modeling both is what lets a reconciliation resolve the rule in force during a period as it was known at close, which is exactly the guarantee that keeps a signed statement stable. If your leases never change and you never reconcile a prior year, a flat schema is simpler and this is overkill; the design earns its keep the moment amendments and closed periods coexist.

Step-by-Step Implementation #

Step 1 — Model the bitemporal schema #

Store each lease term as an immutable assertion: this clause_type had this value over this valid-time range, and we recorded it at this instant. Give money and shares a Numeric column so Decimal round-trips losslessly, and never reuse a row — every change is a new insert.

from __future__ import annotations

from datetime import date, datetime
from decimal import Decimal

from sqlalchemy import Date, DateTime, Numeric, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class LeaseTermVersion(Base):
    """One immutable assertion about a single lease term over a valid-time range."""

    __tablename__ = "lease_term_versions"

    id: Mapped[int] = mapped_column(primary_key=True)
    lease_id: Mapped[str] = mapped_column(String, index=True)
    # e.g. "pro_rata_share", "controllable_cap_pct", "cam_base_year"
    clause_type: Mapped[str] = mapped_column(String, index=True)
    value: Mapped[Decimal] = mapped_column(Numeric(12, 6))

    # Valid time: when the term governs the real world.
    effective_from: Mapped[date] = mapped_column(Date)
    effective_to: Mapped[date | None] = mapped_column(Date, nullable=True)  # None = open-ended

    # Transaction time: when this assertion was committed to the record.
    recorded_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    source_document: Mapped[str] = mapped_column(String)  # original lease or amendment id

A term that is currently open-ended carries effective_to = None; the resolver treats that as “runs forever until superseded.” Because a row is an assertion rather than a mutable record, two rows can describe the same valid-time window with different recorded_at values — that is precisely how a later correction supersedes an earlier belief without erasing it.

Step 2 — Record an amendment as a new version #

An amendment is not an edit; it is two inserts. First, append a corrected assertion that closes the previously open term at the amendment’s effective date. Second, append the new term running from that date forward. Both share a single recorded_at, so the pair is atomic in transaction time.

from datetime import timedelta, timezone


def record_amendment(
    session,
    *,
    lease_id: str,
    clause_type: str,
    new_value: Decimal,
    effective_from: date,
    source_document: str,
    recorded_at: datetime | None = None,
) -> None:
    """Append the two assertions that represent one amendment (append-only)."""
    recorded_at = recorded_at or datetime.now(timezone.utc)

    prior = resolve_term(
        session,
        lease_id=lease_id,
        clause_type=clause_type,
        on_date=effective_from - timedelta(days=1),
    )
    if prior is not None and (prior.effective_to is None or prior.effective_to > effective_from):
        # Bound the prior term at the amendment date via a fresh, later-known assertion.
        session.add(
            LeaseTermVersion(
                lease_id=lease_id,
                clause_type=clause_type,
                value=prior.value,
                effective_from=prior.effective_from,
                effective_to=effective_from,
                recorded_at=recorded_at,
                source_document=prior.source_document,
            )
        )

    session.add(
        LeaseTermVersion(
            lease_id=lease_id,
            clause_type=clause_type,
            value=new_value,
            effective_from=effective_from,
            effective_to=None,
            recorded_at=recorded_at,
            source_document=source_document,
        )
    )
    session.commit()

Nothing is updated or deleted. The prior open-ended row still exists; it is simply out-voted for its overlapping window by a newer assertion that bounds it. That is the mechanism behind a retroactive amendment: record it today with an effective_from back in January, and every query dated after today sees the new boundary while every earlier reconstruction does not.

Step 3 — Query a term at a point in time #

Resolution takes two dates: the on_date in valid time (which period is accruing) and as_known_at in transaction time (what did we know then). Select the assertions that bracket on_date, keep only those committed on or before as_known_at, and take the one with the latest recorded_at — the most current belief that was available at that knowledge point.

from sqlalchemy import select


def resolve_term(
    session,
    *,
    lease_id: str,
    clause_type: str,
    on_date: date,
    as_known_at: datetime | None = None,
) -> LeaseTermVersion | None:
    """Return the single version of a term in force on ``on_date`` as known at a knowledge date."""
    as_known_at = as_known_at or datetime.now(timezone.utc)

    rows = session.scalars(
        select(LeaseTermVersion).where(
            LeaseTermVersion.lease_id == lease_id,
            LeaseTermVersion.clause_type == clause_type,
        )
    ).all()

    candidates = [
        row
        for row in rows
        if row.recorded_at <= as_known_at
        and row.effective_from <= on_date
        and (row.effective_to is None or on_date < row.effective_to)
    ]
    if not candidates:
        return None
    # Latest knowledge wins for the same valid-time window.
    return max(candidates, key=lambda row: row.recorded_at)

Formally, for a set of versions $V$, query date $d$, and knowledge date $k$, the resolver returns:

The half-open interval $[effective\_from,\ effective\_to)$ is deliberate: an amendment effective on 2022-01-01 owns that day, and the prior term ends the instant before it, so no date ever matches two versions.

Step 4 — Make history immutable #

Bitemporal correctness collapses the moment someone runs an UPDATE. Enforce append-only at the ORM boundary with a before_flush guard, and back it in production with a database REVOKE on UPDATE/DELETE for the application role plus an append-only audit trigger.

from sqlalchemy import event
from sqlalchemy.orm import Session


@event.listens_for(Session, "before_flush")
def block_term_mutations(session: Session, flush_context, instances) -> None:
    """Refuse any in-place change to a recorded lease term; corrections must be new rows."""
    offending = [obj for obj in session.dirty if isinstance(obj, LeaseTermVersion)]
    offending += [obj for obj in session.deleted if isinstance(obj, LeaseTermVersion)]
    if offending:
        raise ValueError(
            "lease_term_versions is append-only; record a correction as a new assertion "
            "instead of mutating history"
        )

Treating the table as write-once is what makes the record tamper-evident and reproducible. It also dovetails with the platform’s CAM reconciliation security and access controls: the same principle that forbids editing a lease term underpins immutable access logging, and both rely on the database role — not application good manners — to enforce the rule.

Step 5 — Resolve the rule in force for a closed reconciliation #

The payoff is a helper the reconciliation engine calls once per term. Pass the accrual date as on_date and the statement’s close date as as_known_at. Any amendment recorded after the close is invisible to it, so re-running the statement reproduces the exact figures the tenant received — even years later.

def term_for_closed_period(
    session,
    *,
    lease_id: str,
    clause_type: str,
    period_date: date,
    statement_closed_at: datetime,
) -> Decimal:
    """Resolve a lease term as it stood for an already-closed reconciliation statement."""
    version = resolve_term(
        session,
        lease_id=lease_id,
        clause_type=clause_type,
        on_date=period_date,
        as_known_at=statement_closed_at,  # freeze knowledge at the close
    )
    if version is None:
        raise LookupError(f"no {clause_type} in force for {lease_id} on {period_date}")
    return version.value

Pin as_known_at to the moment the statement was frozen and the closed period becomes genuinely closed. To re-open and re-issue after a retroactive amendment, you run the resolver with today’s knowledge date instead, and the difference between the two runs is the adjustment you owe the tenant — computed, not guessed.

Gotchas & Known Limitations #

• Do not overload valid time with transaction time. An amendment that is signed late but effective early has a past effective_from and a present recorded_at. Collapse the two into one column and out-of-order amendments will corrupt every prior reconstruction.
• Half-open intervals only. Store ranges as [from, to). If both endpoints are inclusive, the amendment date belongs to two versions and the resolver’s max silently masks the ambiguity instead of failing.
• Guard the open-ended tail. Exactly one assertion per term should carry effective_to = None in the latest knowledge. Two open tails means an amendment forgot to bound its predecessor; add a uniqueness check to your verification pass.
• UPDATE is the enemy. A convenient “just fix the row” bypasses the whole model. Keep the before_flush guard and the database REVOKE; the ORM guard alone will not stop a raw SQL patch.
• Loading every row does not scale forever. The resolver above reads all versions for a term for clarity. On a large portfolio, push the predicate into SQL with indexes on (lease_id, clause_type, effective_from) and filter recorded_at in the query.
• Money stays Decimal. A pro-rata share or cap read back as float will drift and fail an audit tie. Keep the Numeric column and the Decimal type end to end.

Verification #

Prove the two axes independently. First, a valid-time check: the same term resolves to different versions across an amendment boundary. Second, a transaction-time check: a closed statement is immune to an amendment recorded after its close.

from decimal import Decimal


def verify_versioning(session) -> None:
    lease_id = "L-4021"
    clause = "pro_rata_share"

    # Original 4.75% from 2020, amended to 6.20% effective 2024-01-01.
    close_2023 = datetime(2024, 3, 31, tzinfo=timezone.utc)

    v_2023 = resolve_term(
        session, lease_id=lease_id, clause_type=clause,
        on_date=date(2023, 6, 30), as_known_at=close_2023,
    )
    v_2024 = resolve_term(
        session, lease_id=lease_id, clause_type=clause,
        on_date=date(2024, 6, 30),
    )
    assert v_2023 is not None and v_2024 is not None
    # Valid time: 2023 accrual keeps the original share; 2024 gets the amendment.
    assert v_2023.value == Decimal("0.047500"), v_2023.value
    assert v_2024.value == Decimal("0.062000"), v_2024.value

    # Transaction time: a retroactive amendment recorded in 2025 does not
    # change what the 2023 statement, closed in early 2024, resolves to.
    frozen = resolve_term(
        session, lease_id=lease_id, clause_type=clause,
        on_date=date(2023, 6, 30), as_known_at=close_2023,
    )
    assert frozen is not None and frozen.value == v_2023.value, "closed period must be stable"

Beyond assertions, run a portfolio-wide invariant sweep: for every (lease_id, clause_type), confirm that the currently-known versions tile their valid-time axis with no gaps and no overlaps, and that at most one tail is open-ended. A gap means an amendment failed to bound its predecessor; an overlap means two live assertions claim the same day. Both are caught here rather than in a disputed tenant statement. The versioned terms this produces are the trustworthy inputs that automating lease abstract extraction with Python writes into the lease abstraction database in the first place — extraction fills the schema, and this versioning keeps it honest over time.

Related #

• Building a Lease Abstraction Database — the parent topic whose schema this versioning model extends so every abstracted term carries its own history.
• Automating Lease Abstract Extraction with Python — the extraction step that populates the versioned rows this page keeps immutable.
• CAM Reconciliation Security and Access Controls — the access and immutability controls that enforce append-only history at the database role, not just the ORM.