How to Structure a Lease Abstraction Database for Multi-Property Portfolios

When a commercial real estate portfolio scales past roughly twenty assets, spreadsheet-based lease abstractions fracture under amendment chains, prorated rent schedules, and conditional escalation triggers. The precise engineering decision this page resolves is concrete: what physical schema do you persist abstraction output into so that point-in-time rent, CAM, and exposure queries stay correct and fast across hundreds of leases? The answer used in production PropTech platforms is a temporally-aware relational schema that decouples static entity metadata from time-bound obligations, augmented by JSONB columns for clause attributes and guarded by deterministic validation before any row is committed.

Architectural context

This page is the storage-layer companion to the lease data models work within Core Architecture & Lease Taxonomy. Those models define the logical canonical record — its pydantic contract, amendment precedence, and idempotent writer — and this page specifies the physical tables, indexes, and constraints that record lands in. By the time a payload reaches the schema below it has already passed through clause classification systems for typed labels and through metadata normalization standards so dates, currencies, and entity names arrive canonical. Once stored, these rows feed the escalation formula mapping engine and the CAM charge reconciliation workflow. The database does one job — store an identity-stable, point-in-time-reconstructible lease — and hands every interpretation step off.

Schema spec: tables, keys, and temporal columns

A multi-property database needs a strict separation between static entities and time-bound obligations. Four core tables anchor the design — properties, leases, tenants, and lease_financials — with clause extractions and abatements in their own child tables. Every time-sensitive row carries a validity window (effective_start, effective_end), with effective_end defaulting to the sentinel 9999-12-31 for the currently-controlling row. This pattern eliminates overlapping amendment conflicts and gives you point-in-time portfolio snapshots without complex window functions.

The table below is the field-mapping spec that turns canonical abstraction output into physical storage.

Two spec rules carry most of the weight. First, clause extraction is never flattened into wide columns — a lease_clauses table with a clause_type_code (e.g., RENT_ESC, CO_TENANCY, CAM_CAP), a verbatim raw_text field, and a parsed_attributes JSONB column lets engineers run targeted JSON-path queries while preserving the original text for audit. Second, escalations are stored as discrete rows, not concatenated formula strings: each lease_financials row holds trigger_type, base_value, cap_value, floor_value, index_reference, and calculation_method, and a database-level CHECK constraint forbids both cap_value and floor_value being null when calculation_method = 'INDEXED'.

Recommended implementation: temporal validation in Python

Schema design alone does not guarantee integrity. Real abstraction output needs programmatic validation to catch temporal overlaps, malformed JSONB payloads, and arithmetic inconsistencies before a row reaches the reporting layer. The pipeline below uses pydantic v2 — the convention established across this site’s data-model code — to validate financial rows and resolve point-in-time state. It is designed to run as a pre-commit gate inside the ingestion worker, ahead of any write.

from __future__ import annotations
from datetime import date
from decimal import Decimal
from enum import Enum
from typing import Optional, Any
from pydantic import BaseModel, Field, field_validator, model_validator


class CalculationMethod(str, Enum):
    FIXED = "fixed"
    INDEXED = "indexed"
    PERCENTAGE = "percentage"


class EscalationRow(BaseModel):
    """One discrete lease_financials row carrying its own validity window."""
    lease_id: str = Field(pattern=r"^LSE-\d{6}$")
    trigger_type: str
    base_value: Decimal = Field(gt=0, decimal_places=2)
    cap_value: Optional[Decimal] = Field(default=None, ge=0)
    floor_value: Optional[Decimal] = Field(default=None, ge=0)
    index_reference: Optional[str] = None
    calculation_method: CalculationMethod
    effective_start: date
    effective_end: date = date(9999, 12, 31)   # open until superseded

    @model_validator(mode="after")
    def enforce_indexed_bounds(self) -> "EscalationRow":
        # An INDEXED escalation with neither a cap nor a floor can drift
        # unbounded against the published index — reject it at the boundary.
        if self.calculation_method is CalculationMethod.INDEXED:
            if self.cap_value is None and self.floor_value is None:
                raise ValueError("INDEXED rows require at least a cap or a floor")
            if self.index_reference is None:
                raise ValueError("INDEXED rows require an index_reference (e.g. CPI_U_ALL)")
        if self.effective_end <= self.effective_start:
            raise ValueError("effective_end must strictly follow effective_start")
        return self


class LeaseClause(BaseModel):
    clause_id: str = Field(pattern=r"^CLS-\d{4}$")
    clause_type_code: str
    raw_text: str                       # preserved verbatim for audit
    parsed_attributes: dict[str, Any] = Field(default_factory=dict)

    @field_validator("parsed_attributes")
    @classmethod
    def required_keys_present(cls, v: dict[str, Any]) -> dict[str, Any]:
        for key in v.get("_required", []):
            if key not in v:
                raise ValueError(f"parsed_attributes missing required key: {key}")
        return v


def assert_no_overlap(rows: list[EscalationRow]) -> None:
    """No two financial rows for one lease may share a validity window."""
    ordered = sorted(rows, key=lambda r: r.effective_start)
    for current, nxt in zip(ordered, ordered[1:]):
        if current.effective_end > nxt.effective_start:
            raise ValueError(
                f"Temporal overlap on {current.lease_id}: "
                f"{current.effective_start}..{current.effective_end} "
                f"collides with {nxt.effective_start}"
            )


def controlling_rent(rows: list[EscalationRow], as_of: date) -> Optional[Decimal]:
    """Point-in-time lookup: latest effective_start whose window contains as_of."""
    live = [r for r in rows if r.effective_start <= as_of <= r.effective_end]
    return max(live, key=lambda r: r.effective_start).base_value if live else None


if __name__ == "__main__":
    rows = [
        EscalationRow(
            lease_id="LSE-100482", trigger_type="cpi",
            base_value=Decimal("10000.00"),
            cap_value=Decimal("11500.00"), floor_value=Decimal("10200.00"),
            index_reference="CPI_U_ALL",
            calculation_method=CalculationMethod.INDEXED,
            effective_start=date(2024, 1, 1), effective_end=date(2024, 12, 31),
        )
    ]
    assert_no_overlap(rows)
    print("controlling rent:", controlling_rent(rows, date(2024, 6, 1)))

The assert_no_overlap gate is what keeps the temporal model honest: amendment chains append new rows, and overlap means two rows claim the same day. The controlling_rent lookup is the same precedence rule expressed at read time — later effective_start wins, so an amendment supersedes the base lease without ever deleting it.

Indexing and audit specifics

Query performance during portfolio-wide CAM reconciliations collapses without targeted indexes. Build a composite B-tree index on (property_id, effective_start) for temporal filtering, and a GIN index with jsonb_path_ops on parsed_attributes to accelerate @> containment queries against clause JSONB. Do not index the whole parsed_attributes column unless your queries consistently filter on deeply nested keys — a broad GIN index on a wide JSONB column adds real write overhead on every ingest.

Audit compliance requires the append-only lease_audit_log table: capture record_id, changed_by, previous_state, current_state, and ts, and never UPDATE or DELETE a historical lease row directly. Each amendment instead closes the predecessor’s effective_end and inserts a successor row. That single discipline is what keeps financial exposure models and the broader lease data models fully reconstructible for lender covenants and internal compliance reviews.

Edge cases specific to commercial leases

• Amendment riders that re-open a closed window. A late-dated rent commencement letter can land with an effective_start before an already-committed successor row. Resolve by re-running assert_no_overlap across the full lease before commit and rejecting the batch to manual review rather than silently truncating windows.
• Co-tenancy and percentage-rent triggers. These are conditional, not scheduled — store them as lease_clauses rows with the condition in parsed_attributes (e.g., {"trigger": "anchor_vacancy", "threshold_pct": 30}), not as lease_financials rows, since they fire on external state, not on a calendar window.
• Partial-month abatements against TI completion. The abatement_schedule row’s milestone_date rarely aligns to a month boundary; persist the daily proration basis explicitly so cash-flow forecasts reconcile against actual construction milestones rather than assuming whole-month free rent.
• Multi-currency portfolios. Keep currency (ISO 4217) on every lease_financials row, not on properties — sale-leasebacks and cross-border tenants can put two currencies under one asset, and Decimal money columns must never be summed across currencies without conversion.

When to escalate

This relational-plus-JSONB schema is the right default, but route around it when its assumptions break. If abstraction output arrives from scanned or photographed leases, the structured fields will be sparse and low-confidence — push those documents back through OCR preprocessing workflows before they ever reach the schema, because storing garbage with a clean primary key is worse than rejecting it. When a record assembles below the classification confidence threshold, divert it through fallback routing logic to a manual-review queue instead of committing it as ground truth. And when a portfolio’s clause shapes are so heterogeneous that more than a handful of clause_type_code values would need bespoke columns, that is the signal to evaluate a document-oriented store for the clause layer while keeping the relational identity and financial tables intact. Access scoping for any of these review queues should follow the security & access boundaries model.

Frequently asked questions

Related

• Lease Data Models — the logical canonical record and pydantic contract this physical schema persists.
• Metadata Normalization Standards — the normalization layer that guarantees fields arrive in the types these tables expect.
• Escalation Formula Mapping — how a stored lease_financials row becomes a computed rent.
• Handling CAM Charge Variations in Lease Taxonomy Design — the polymorphic CAM model that reconciles against these stored rows.
• Fallback Routing Logic — where low-confidence and schema-invalid records are diverted for review.

← Back to Lease Data Models

← Back to Lease Data Models