SQL vs Document Store for Lease Canonical Models

The decision this page resolves is narrow and consequential: for the canonical layer of your lease data model — the identity-stable record that rent rolls, reconciliations, and compliance exports all read from — do you persist it in a relational store like Postgres or a document store like MongoDB (or Postgres JSONB used document-style)? This is not the how do I design the schema question answered by how to structure a lease abstraction database for multi-property portfolios; that page lays out the full temporal schema and indexing. This page is the store-selection decision that comes before it: which storage model best fits a canonical lease record, and what you trade away by picking one.

The short answer, defended below, is a hybrid: a relational canonical core for identity, financial schedules, and the join keys reconciliation depends on, with a typed JSONB column carrying the variable clause payloads that resist a fixed schema. Lead with why.

Architectural context

The canonical record is where every upstream stage converges. Clauses arrive already labeled by clause classification systems and already typed by the metadata normalization standards layer; downstream, the stored record drives escalation formula mapping for rent computation and supplies the access-scoped views defined in security & access boundaries. The store you choose has to serve two very different shapes at once. Identity, financial schedules, and rent-roll joins are highly relational and constraint-hungry: a base rent that must reconcile to the penny wants foreign keys, CHECK constraints, and NUMERIC precision. Clause payloads are the opposite — heterogeneous, sparsely populated, and forever sprouting new attributes (a co-tenancy kick-out clause shares almost no fields with a percentage-rent clause). Forcing both shapes into one storage model is the mistake; matching each to what it needs is the design.

The comparison: SQL vs document store vs hybrid

Each row below is a real requirement a canonical lease model has to meet. Read it as which store makes this cheap, and which makes it your problem to solve in application code.

The pattern is stark: relational wins every row that touches money, joins, and constraints, and document stores win exactly one row — heterogeneous clause schemas — but it is the row that hurts most if you get it wrong, because clause shapes genuinely are open-ended. The hybrid takes the relational column wherever it wins and borrows the document store’s one strength precisely where it is needed.

Recommended implementation: relational core with a typed JSONB clause column

The core tables carry the constraints; a single JSONB column carries the clause variability. The DDL below is the canonical core.

CREATE TABLE lease (
    lease_id          TEXT PRIMARY KEY CHECK (lease_id ~ '^LSE-\d{6}$'),
    tenant_entity     TEXT NOT NULL,
    premises_id       TEXT NOT NULL,
    commencement_date DATE NOT NULL,
    expiration_date   DATE NOT NULL,
    CHECK (expiration_date > commencement_date)
);

CREATE TABLE rent_schedule (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    lease_id        TEXT NOT NULL REFERENCES lease(lease_id),
    base_rent       NUMERIC(14,2) NOT NULL CHECK (base_rent > 0),
    effective_start DATE NOT NULL,
    effective_end   DATE NOT NULL DEFAULT DATE '9999-12-31',
    source_doc_id   TEXT NOT NULL
);

CREATE TABLE clause (
    clause_id     TEXT PRIMARY KEY CHECK (clause_id ~ '^CLS-\d{4}$'),
    lease_id      TEXT NOT NULL REFERENCES lease(lease_id),
    clause_type   TEXT NOT NULL,
    effective_date DATE NOT NULL,
    payload       JSONB NOT NULL DEFAULT '{}'::jsonb    -- variable, typed per clause_type
);

CREATE INDEX idx_clause_payload ON clause USING GIN (payload);
CREATE INDEX idx_rent_window ON rent_schedule (lease_id, effective_start, effective_end);

The relational core enforces what SQL is good at — the NUMERIC(14,2) on base_rent gives exact money, the foreign keys keep every rent row and clause tied to a real lease, and the date CHECK rejects an impossible term at write time. The payload JSONB column is where the document store’s flexibility lives: a co-tenancy clause and a percentage-rent clause store completely different keys in the same column, and adding a new clause type needs no migration. The GIN index keeps that column queryable.

The application-side contract is a pydantic v2 canonical model that maps one-to-one onto the core and types the JSONB payload through a discriminated union, so the schema-flexible column is not a schema-less free-for-all — it is strongly typed per clause_type at the boundary.

from datetime import date
from decimal import Decimal
from typing import Literal, Union, Annotated
from pydantic import BaseModel, Field, ConfigDict

class CoTenancyPayload(BaseModel):
    model_config = ConfigDict(extra="forbid")
    clause_type: Literal["co_tenancy"] = "co_tenancy"
    occupancy_threshold: float = Field(ge=0, le=1)
    kickout_after_months: int = Field(gt=0)

class PercentageRentPayload(BaseModel):
    model_config = ConfigDict(extra="forbid")
    clause_type: Literal["percentage_rent"] = "percentage_rent"
    breakpoint: Decimal = Field(gt=0, decimal_places=2)      # exact money
    overage_rate: float = Field(ge=0, le=1)

# Discriminated union: the JSONB payload is typed by clause_type, not free-form
ClausePayload = Annotated[
    Union[CoTenancyPayload, PercentageRentPayload],
    Field(discriminator="clause_type"),
]

class Clause(BaseModel):
    clause_id: str = Field(pattern=r"^CLS-\d{4}$")
    lease_id: str = Field(pattern=r"^LSE-\d{6}$")
    clause_type: str
    effective_date: date
    payload: ClausePayload

    def to_row(self) -> dict:
        """Serialize for the relational core: scalar columns + one JSONB blob."""
        return {
            "clause_id": self.clause_id,
            "lease_id": self.lease_id,
            "clause_type": self.clause_type,
            "effective_date": self.effective_date,
            "payload": self.payload.model_dump(mode="json"),   # -> JSONB
        }

    @classmethod
    def from_row(cls, row: dict) -> "Clause":
        """Rehydrate: the discriminated union re-validates the JSONB on read."""
        return cls.model_validate({**row, "payload": row["payload"]})

to_row flattens the model into scalar columns plus a single JSONB blob; from_row rehydrates it, and because payload is a discriminated union, the JSONB is re-validated on read — a malformed payload written out-of-band is caught at load time rather than silently trusted. Money stays Decimal end to end, exactly as the metadata normalization standards require, so nothing about choosing a document-flavored column for clauses weakens numeric precision where it matters.

Edge cases specific to commercial leases

• Heterogeneous clause schemas. A percentage-rent clause carries a breakpoint and overage rate; a co-tenancy clause carries an occupancy threshold and a kick-out window. In a pure relational schema this becomes sparse columns or EAV sprawl; in the hybrid, each is a typed variant in the JSONB column, and a brand-new clause type is a new pydantic model plus zero DDL.
• As-of temporal queries. “What was the controlling rent on 2025-03-01?” is an indexed range scan on the relational rent_schedule — WHERE effective_start <= :d AND :d <= effective_end with the latest effective_start winning on overlap. In a pure document store you either denormalize a snapshot per date or filter an embedded array in application code; the relational core makes it a single query.
• Reconciliation joins vs embedded docs. Rent-roll reconciliation joins schedules to leases to premises and sums exact NUMERIC. Embedding clauses and financials inside one document removes the join but also removes the foreign key that guarantees no schedule is orphaned — you trade a cheap join for hand-written integrity checks and weaker aggregate arithmetic.
• Schema migration on a live portfolio. Adding a field to the core is a deliberate migration you plan and lock around; adding a field to a clause payload needs no DDL at all — write the new key, and extra="forbid" on the payload models tells you which historical rows predate it so read-time coercion can backfill a default. This split is the hybrid’s payoff: the risky migrations are confined to a small, stable core.
• Eventual consistency. A document store that replicates asynchronously can serve a stale lease immediately after an amendment write, so a reconciliation run reads pre-amendment rent. The relational core is transactionally consistent, so an amendment and its new rent row commit atomically — the reason money and versioning belong in the core, not the flexible column. Amendment ordering and provenance are resolved in amendment versioning & ledgers.

When to escalate

The hybrid is the default, not a law. Revisit the store choice when the workload outgrows the assumptions:

• Query or consistency needs exceed the chosen store. If clause-payload queries become first-class analytics — ranking every lease by a nested payload field across the portfolio — promote that field out of JSONB into a real indexed column, or reconsider the split. If a workload genuinely needs multi-region low-latency writes over strong consistency, that pressure argues for a different store and should be decided explicitly, not discovered in a reconciliation bug.
• Escalation math reaches past storage. Once payloads start driving computed rent, the logic belongs in escalation formula mapping, not in the store — keep the canonical layer about persistence.
• Provenance and audit dominate. When “who changed this rent, from what, and when” becomes a hard requirement, layer an append-only amendment versioning & ledgers trail over the relational core rather than versioning documents in place.

Frequently asked questions

Should the canonical lease model live in SQL or a document store? Use a hybrid: a relational core for identity, financial schedules, and rent-roll joins, plus a typed JSONB column for the variable clause payloads. Relational wins every dimension that touches money, joins, and constraints; the document model wins only heterogeneous clause schemas, which the JSONB column covers without giving up integrity elsewhere.

Why not store the whole lease as one document to avoid joins? Embedding removes joins but also removes the foreign keys that guarantee no orphaned rent schedule, the column constraints that reject bad money, and the transactional consistency reconciliation depends on. You trade a cheap indexed join for hand-written integrity checks and as-of queries reconstructed in application code.

Does putting clauses in JSONB weaken validation? No, if you type it. A pydantic discriminated union keyed on clause_type validates each payload on both write and read, so the JSONB column is schema-flexible per clause type rather than schema-less. Money stays Decimal and the core columns keep their CHECK constraints.

How do I evolve the clause schema on a live portfolio? Adding a clause attribute is a new field on the relevant payload model and needs no DDL, because the column is JSONB. Historical rows that predate the field are caught by strict payload models so read-time coercion can backfill a default. Reserve planned, locked migrations for the small relational core.

Related

• Lease Data Models — the canonical record and validation seam this storage decision serves.
• How to Structure a Lease Abstraction Database for Multi-Property Portfolios — the full temporal schema and indexing that follows once the store is chosen.
• Amendment Versioning & Ledgers — append-only provenance and audit layered over the relational core.
• Metadata Normalization Standards — the typed-decimal contract the canonical core enforces.

← Back to Lease Data Models

← Back to Lease Data Models