Building a Lease Abstraction Database

CAM reconciliation fails the moment lease terms stay trapped in unstructured PDFs, scattered spreadsheets, and legacy ERP exports — the allocation engine ends up running on human interpretation instead of executable rules. A lease abstraction database is the fix: it turns static contractual language into typed, versioned, queryable records that every downstream calculation reads from. As the deterministic source of truth inside the broader CAM Architecture & Lease Clause Taxonomy reference architecture, this database supplies the pro-rata shares, base years, expense caps, gross-up thresholds, and exclusion lists that the reconciliation engine consumes. For property managers, real estate accountants, CRE developers, and Python automation builders, the goal of this page is a concrete, auditable data model — the schema, the Pydantic contracts, the temporal versioning, and the fallback routing — that makes a reconciliation reproducible three years after it was filed.

Every number the engine later produces — a tenant’s proportionate share, a capped controllable, a grossed-up variable cost — is a lookup against this model. If a recoverability flag is wrong or a base year is off by one, no amount of clean invoice data will produce a defensible statement. Getting the abstraction right is therefore the highest-leverage step in the pipeline.

Prerequisites & Data Contracts #

Before the abstraction database can hold a single lease, three upstream contracts must be settled, because the schema encodes decisions made elsewhere in the architecture.

First, a standardized expense taxonomy has to exist. The recoverability of a cost is a contractual attribute, not a global property of a GL account, so the abstraction stores a foreign key into the category tree defined by defining CAM expense categories in commercial leases. Across a multi-property portfolio those categories must be identical, which is the discipline enforced by standardizing CAM taxonomies across portfolios; otherwise the same janitorial line means “recoverable” at one asset and “excluded” at another, and consolidated reporting under FASB ASC 842 becomes unauditable.

Second, the measurement basis must be resolved. Every pro-rata share depends on rentable square footage computed against a BOMA measurement standard, including the load factor that converts usable to rentable area. The abstraction stores the tenant RSF and the building’s total rentable pool as fields, not as derived guesses, so the allocation stage never re-measures.

Third, the source documents must be parsed into candidate fields. The extraction of commencement dates, base years, caps, and exclusions from executed leases and amendments is its own problem, covered in the child guide automating lease abstract extraction with Python. The data contract between that extractor and this database is strict: extraction guarantees only that the correct text was read, while this layer owns typing, validation, and persistence.

The resulting field contract for a single lease record is deliberately narrow and typed:

Schema & Rule Design #

The foundation is a normalized relational schema whose tables map one-to-one onto CAM logic: tenants, leases, expense_categories, reconciliation_periods, and allocation_rules. Strict foreign keys between expense_categories and allocation_rules guarantee that every reconciled dollar resolves to a contractually valid recovery bucket, which is what turns ambiguous, hand-written SQL joins into precise, set-based allocation queries that scale across hundreds of tenants.

Resolving ambiguous NNN language into enumerated rules #

Triple-net leases are full of subjective phrasing — “operating expenses,” “common area maintenance,” “capital improvements.” The abstraction database resolves that ambiguity at ingestion instead of at reconciliation time. A clause_interpretation table stores an enumerated disposition for each parsed clause — ALLOWED, EXCLUDED, CONDITIONAL, or CAP_APPLIED — so the engine applies deterministic logic rather than a person’s reading. Conditional clauses such as “management fees capped at 3% of gross receipts” or “HVAC replacements excluded unless the aggregate exceeds $50,000” decompose into a boolean predicate plus a threshold column. That decomposition is what lets the allocation code evaluate eligibility without hard-coding property-specific exceptions.

The pro-rata share the abstraction must support #

The share fields exist to feed the allocation math directly. The base proportionate share for a tenant is rentable area over the pool, applied to the recoverable expense total:

where $A_t$ is tenant rentable SF, $A_b$ is the building rentable pool, and $E_r$ is the recoverable expense total for the period. When the lease specifies an expense stop against a base year, the recoverable amount is the increment over that base, $E_r = \max(0,\ E_{\text{period}} - E_{\text{base}})$. The abstraction stores $A_t$, $A_b$, and the base-year figure so that the full pro rata allocation logic — and the stateful cap enforcement in managing expense caps and controllable limits — reads fixed inputs rather than re-deriving them per run.

Temporal versioning for point-in-time reconciliation #

Leases amend. CAM pools expand, recovery methods shift, and caps get renegotiated mid-term. The database must therefore be temporal: each lease record carries effective_start, effective_end, and preceding_version_id, so a 2024 reconciliation resolves against exactly the terms in force during fiscal 2024, even when it is re-run in 2027. Point-in-time correctness is a compliance requirement — retroactively applying a 2026 amendment to a 2024 period is precisely the kind of restatement an auditor flags.

Python Implementation #

The persistence layer uses pydantic for the typed data contract, the decimal module for every monetary and share field (never float, because binary floating point accumulates sub-cent drift across thousands of lines), and sqlalchemy for transactional writes. The model below is the runnable core of the abstraction: it validates a parsed lease, enforces the field contract, and exposes the point-in-time lookup the reconciliation engine calls.

from __future__ import annotations

from datetime import date
from decimal import Decimal
from enum import Enum

from pydantic import BaseModel, Field, field_validator, model_validator


class RecoveryMethod(str, Enum):
    NNN = "NNN"
    MODIFIED_GROSS = "MODIFIED_GROSS"
    FULL_SERVICE = "FULL_SERVICE"


class CapType(str, Enum):
    CUMULATIVE = "CUMULATIVE"
    NON_CUMULATIVE = "NON_CUMULATIVE"
    ABSOLUTE = "ABSOLUTE"
    NONE = "NONE"


class LeaseAbstract(BaseModel):
    """A single, versioned lease record — the deterministic source of truth
    the reconciliation engine reads pro-rata shares and caps from."""

    lease_id: str
    tenant_id: str
    recovery_method: RecoveryMethod
    tenant_rsf: Decimal = Field(gt=0)          # rentable SF under the BOMA standard
    building_rsf: Decimal = Field(gt=0)        # denominator for the share
    base_year: int | None = None               # expense-stop base year
    expense_cap_type: CapType = CapType.NONE
    cap_value: Decimal | None = None           # percent or dollar ceiling
    exclusion_codes: list[str] = Field(default_factory=list)
    effective_start: date
    effective_end: date | None = None          # None == currently in force
    preceding_version_id: str | None = None

    @field_validator("tenant_rsf", "building_rsf", "cap_value")
    @classmethod
    def _quantize_money(cls, v: Decimal | None) -> Decimal | None:
        # Keep every stored figure at a fixed scale so math is reproducible.
        return v if v is None else v.quantize(Decimal("0.0001"))

    @model_validator(mode="after")
    def _check_invariants(self) -> "LeaseAbstract":
        if self.tenant_rsf > self.building_rsf:
            raise ValueError("tenant_rsf cannot exceed building_rsf")
        if self.effective_end and self.effective_end < self.effective_start:
            raise ValueError("effective_end precedes effective_start")
        if self.base_year and not (self.effective_start.year <= self.base_year):
            raise ValueError("base_year predates the lease term")
        if self.expense_cap_type is not CapType.NONE and self.cap_value is None:
            raise ValueError("cap_value is required when a cap type is set")
        return self

    def pro_rata_share(self) -> Decimal:
        """Fractional share A_t / A_b, quantized for deterministic allocation."""
        return (self.tenant_rsf / self.building_rsf).quantize(Decimal("0.00000001"))

The point-in-time selector is what makes the temporal model useful. Given a portfolio of versioned rows, it returns the single record whose validity window contains the reconciliation date:

from collections.abc import Iterable


def version_in_force(
    versions: Iterable[LeaseAbstract], as_of: date
) -> LeaseAbstract | None:
    """Return the lease version governing `as_of`, or None if the lease was
    not yet effective / already terminated on that date."""
    for v in versions:
        started = v.effective_start <= as_of
        not_ended = v.effective_end is None or as_of <= v.effective_end
        if started and not_ended:
            return v
    return None

Persisting the model is a transactional write through SQLAlchemy, with the foreign key into the shared category tree enforced at the database level rather than in application code:

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


class Base(DeclarativeBase):
    pass


class LeaseRow(Base):
    __tablename__ = "leases"

    lease_id: Mapped[str] = mapped_column(String, primary_key=True)
    tenant_id: Mapped[str] = mapped_column(ForeignKey("tenants.tenant_id"))
    recovery_method: Mapped[str] = mapped_column(String)
    tenant_rsf: Mapped[Decimal] = mapped_column(Numeric(14, 4))
    building_rsf: Mapped[Decimal] = mapped_column(Numeric(14, 4))
    base_year: Mapped[int | None] = mapped_column(nullable=True)
    effective_start: Mapped[date] = mapped_column(Date)
    effective_end: Mapped[date | None] = mapped_column(Date, nullable=True)


def persist(session: Session, abstract: LeaseAbstract) -> None:
    """Validate-then-commit: Pydantic has already enforced the invariants,
    so the write is the last gate before the record becomes queryable."""
    session.add(
        LeaseRow(
            lease_id=abstract.lease_id,
            tenant_id=abstract.tenant_id,
            recovery_method=abstract.recovery_method.value,
            tenant_rsf=abstract.tenant_rsf,
            building_rsf=abstract.building_rsf,
            base_year=abstract.base_year,
            effective_start=abstract.effective_start,
            effective_end=abstract.effective_end,
        )
    )
    session.commit()

Using Numeric at the column level and Decimal in the model keeps the stored value exact end to end — the same guarantee documented in the Python decimal module — so a share computed at ingestion ties out to the same figure at reconciliation.

Validation Rules & Edge Cases #

The abstraction layer is where malformed lease data must be caught, because every failure it lets through becomes a silent allocation error downstream. The known failure modes for this specific model:

• Share exceeds 100%. tenant_rsf > building_rsf is rejected by the model validator. In practice this catches a transposed RSF field or a building pool that was measured under a different BOMA standard than the tenant suite.
• Base year outside the term. A base_year earlier than effective_start means the expense stop references a period the lease did not exist in — a classic amendment-copy error.
• Cap type without a value. A lease tagged CUMULATIVE with a null cap_value would let controllable growth run uncapped; the validator forces the pair to be present together.
• Overlapping version windows. Two records for the same lease_id whose effective_start/effective_end windows intersect make the point-in-time query ambiguous. Enforce non-overlap with a check constraint or an ingestion-time interval test.
• Contradictory clause dispositions. A base lease that allows a category while an addendum excludes it must resolve by precedence — negotiated addenda supersede boilerplate — and when precedence cannot pick a single disposition, the line is routed to review rather than auto-allocated.

Not every lease arrives complete. When a required field is NULL or fails validation, the record enters a tiered fallback rather than crashing the batch:

1. Historical imputation. Pull the most recent valid record for the same tenant-and-property combination and carry the field forward with a lowered confidence score.
2. Portfolio defaulting. Apply the standardized default aligned with the BOMA measurement standard for a missing recovery method or measurement basis.
3. Manual review queue. Flag the record in a reconciliation_exceptions table with a severity level and route it to the responsible property manager or accountant.

This routing guarantees the ingestion job never fails silently. It produces a partial abstraction with explicit confidence scores, letting the accounting team triage high-impact exceptions while month-end close proceeds.

Integration Points #

The abstraction database is the hub the rest of the pipeline reads from and writes into, and each edge is a defined contract.

Upstream, the extractor described in automating lease abstract extraction with Python hands over candidate field values; this layer types and validates them. In parallel, parsed vendor invoices arriving through the automated invoice parsing pipeline are checked against the abstraction’s exclusion codes and category keys before allocation, using the same discipline as schema validation for parsed expense data.

Downstream, the expense allocation rule engines read pro_rata_share, base_year, and cap fields to compute each tenant’s charge, and GL code mapping for CAM expenses uses the category foreign keys to post recoveries to the correct accounts. Tenant-specific carve-outs feed exclusion mapping for tenant-specific CAM. Because the model is queried by version, the audit record can cite the exact preceding_version_id chain that produced any historical statement.

Because the database holds financially sensitive terms and tenant PII, every one of these edges is governed by role-based access and immutable audit logging, detailed in CAM reconciliation security & access controls: property managers get property-scoped read/write, accountants get read-only access to outputs and the exception queue, and developers operate against synthetic data in isolated staging.

Testing & Verification #

Lease math is only trustworthy if the abstraction that feeds it is covered by tests that assert exact values, not approximate ones. Two patterns carry most of the weight.

First, golden-value tests on the share calculation, comparing against Decimal literals so no floating-point tolerance is needed:

from decimal import Decimal
from datetime import date


def test_pro_rata_share_is_exact() -> None:
    lease = LeaseAbstract(
        lease_id="L-100",
        tenant_id="T-1",
        recovery_method=RecoveryMethod.NNN,
        tenant_rsf=Decimal("12500"),
        building_rsf=Decimal("100000"),
        base_year=2023,
        effective_start=date(2023, 1, 1),
    )
    assert lease.pro_rata_share() == Decimal("0.12500000")


def test_share_over_one_is_rejected() -> None:
    import pytest
    with pytest.raises(ValueError):
        LeaseAbstract(
            lease_id="L-101",
            tenant_id="T-2",
            recovery_method=RecoveryMethod.NNN,
            tenant_rsf=Decimal("120000"),
            building_rsf=Decimal("100000"),
            effective_start=date(2023, 1, 1),
        )

Second, point-in-time selection tests over a version chain, confirming the router returns the record whose window contains the reconciliation date and None outside every window:

def test_version_in_force_selects_the_right_amendment() -> None:
    base = LeaseAbstract(
        lease_id="L-200", tenant_id="T-3", recovery_method=RecoveryMethod.NNN,
        tenant_rsf=Decimal("10000"), building_rsf=Decimal("80000"),
        effective_start=date(2022, 1, 1), effective_end=date(2023, 12, 31),
    )
    amended = LeaseAbstract(
        lease_id="L-200", tenant_id="T-3", recovery_method=RecoveryMethod.NNN,
        tenant_rsf=Decimal("12000"), building_rsf=Decimal("80000"),
        effective_start=date(2024, 1, 1), preceding_version_id="L-200@v1",
    )
    chain = [base, amended]
    assert version_in_force(chain, date(2024, 6, 30)) is amended
    assert version_in_force(chain, date(2023, 6, 30)) is base
    assert version_in_force(chain, date(2021, 1, 1)) is None

Fixture strategy: build each portfolio scenario from a small factory that returns fully typed LeaseAbstract objects, and assert on Decimal equality throughout. The moment a test needs pytest.approx, treat it as a signal that a float has crept into the money path and remove it.

From Abstraction to Reconciliation #

A lease abstraction database is what converts a pile of executed leases into a system that can defend every number it produces. By pinning recoverability to the shared taxonomy, storing shares and caps as typed Decimal fields, versioning each record for point-in-time queries, and routing incomplete data through a tiered fallback instead of failing silently, the database gives the reconciliation engine fixed, auditable inputs. From here the records flow into pro rata allocation and expense-cap enforcement, get posted through GL code mapping for CAM expenses, and are locked down by role-based access controls — the rest of the CAM Architecture & Lease Clause Taxonomy architecture this database anchors.

Frequently Asked Questions #

Why store the recoverability flag on the lease abstraction instead of the GL chart of accounts? Recoverability is a contractual attribute that varies per lease. The same management fee line can be fully recoverable under one lease, capped under a second, and excluded under a third. Storing the disposition on the versioned abstraction lets the engine apply the correct treatment per tenant and reproduce it later; storing it on the GL account would force one global answer that is wrong for most tenants.

Why must every monetary and share field use decimal rather than float? Binary floating point cannot represent most decimal fractions exactly, so rounding error accumulates across thousands of line items and multiple fiscal periods until a sub-cent drift becomes a material, disputable variance. The decimal module gives exact base-10 arithmetic and explicit rounding control, which is what keeps a reconciliation penny-accurate and auditable.

How does temporal versioning keep a re-run reconciliation correct? Each record carries an effective-date window and a link to its predecessor. A reconciliation for a past fiscal year selects the version whose window contains that period’s date, so re-running it in a later year applies the terms that were actually in force — never a subsequent amendment. That reproducibility is exactly what an auditor tests.

What happens when a lease is missing a required CAM field? It enters the tiered fallback router rather than crashing the batch: historical imputation from the tenant’s prior valid period, then a BOMA-aligned portfolio default, then a flagged entry in the manual review queue. The record persists with an explicit confidence score so the team can triage it without blocking month-end close.

Related #

• Automating lease abstract extraction with Python — the upstream parser that produces the candidate fields this database types and stores.
• Defining CAM expense categories in commercial leases — the category tree the abstraction’s recoverability foreign keys point into.
• Standardizing CAM taxonomies across portfolios — keeping category codes identical across assets so consolidated reporting stays auditable.
• CAM reconciliation security & access controls — RBAC, column masking, and immutable audit logs for the sensitive terms this database holds.