Payment Schedule Sync

Payment schedule sync is the stage that turns a lease’s financial terms into rows a billing system will actually invoice against. It lives inside Downstream Automation & Sync, directly downstream of the ingestion boundary: it never parses a document. It subscribes to a validated canonical lease — base rent, an escalation rule, CAM estimates, abatement windows, and the term dates already resolved by upstream extraction — and expands those compact terms into an explicit, one-row-per-period schedule. Each row is then written into an accounts-receivable or billing ledger (Yardi, RealPage, NetSuite, or a custom AR table) so the money the lease promises becomes money the system will bill. The specific problem this page solves is doing that expansion and that write deterministically and idempotently, so re-running the sync a second time never double-bills a tenant and an amendment to future rent reconciles cleanly against what was already posted.

The hard part is not the arithmetic; it is that money and dates are unforgiving. A schedule generated with binary float rent drifts by a cent over sixty months and fails audit. A proration that miscounts the days in a partial first month invoices the wrong amount on move-in. A re-sync that lacks a stable key inserts a duplicate July charge the moment a worker retries. Everything below is the engineering of a schedule generator that treats every amount as Decimal, every date as ISO-8601, and every row as an idempotent upsert keyed on (lease_id, period) so the same lease expanded twice produces byte-for-byte the same ledger.

Where this fits

This stage is strictly downstream of the canonical model and strictly upstream of the ledger, and it holds a narrow contract:

• Upstream of it: the lease data models that produce a validated canonical lease, and the escalation formula mapping that resolves a clause’s raw prose into a machine-readable escalation rule. Recurring CAM terms arrive already normalized by the work described in handling CAM charge variations.
• This stage: expand the term into explicit periods, compute each period’s rent, proration, abatement, and CAM in Decimal, assign a stable period key, and upsert the rows into the AR system so the ledger matches the lease.
• Downstream of it: the invoices, cash application, and dunning that the billing system runs on those rows — none of which this stage touches. When a period’s computed amount contradicts what was already invoiced, the row diverts through fallback routing logic to a reconciliation queue rather than being silently rewritten.

A generator that also tried to decide whether an amendment supersedes a base lease, or to classify a CAM charge, would be impossible to reason about when the numbers are wrong. It answers one question — “given this canonical lease, what does each period owe, and is that already posted?” — and hands everything else off.

Prerequisites and environment setup

The reference implementation targets Python 3.11+ with a small, auditable dependency set. Money never touches float; date stepping never rolls its own calendar math.

Install with pip install "pydantic>=2.6" python-dateutil. Two invariants must hold before any row is computed. First, every monetary input — base rent, CAM estimate, abatement amount — enters as a Decimal constructed from a string, never from a float; Decimal("2500.00") is exact, Decimal(2500.00) inherits binary drift. Second, all amounts are quantized to two places with ROUND_HALF_UP at the moment they are written, using one shared context, so the same computation on any machine yields the same cent. Dates are ISO-8601 date objects, and month boundaries are always derived with relativedelta, because naive +30 days arithmetic silently corrupts February and any 31-day month.

Schedule shape and expansion rules

Before the code, the decision table each row obeys. The engine walks the term one period at a time; for each period it resolves rent from the escalation rule, then applies proration, abatement, and CAM as independent modifiers.

Each generated row therefore carries a period key, a rent amount, a CAM amount, and a flag set (prorated, abated, true_up) that makes the reason for any non-obvious amount auditable. The period key is the ISO year-month string ("2026-08"), which sorts lexicographically, is stable across re-runs, and forms half of the idempotency key (lease_id, period).

Primary implementation

The generator below expands a canonical lease into a list of validated PaymentScheduleRow objects. It uses Decimal for every amount, relativedelta for month stepping, and a single quantization helper so rounding is identical everywhere. The escalation rule is deliberately modeled as normalized data the mapper already produced — this stage consumes it, it does not interpret lease prose.

from __future__ import annotations
from calendar import monthrange
from datetime import date
from decimal import Decimal, ROUND_HALF_UP
from typing import Literal, Optional

from dateutil.relativedelta import relativedelta
from pydantic import BaseModel, Field, field_validator

CENTS = Decimal("0.01")

def money(value: Decimal) -> Decimal:
    """Quantize any Decimal to two places with a single deterministic rule."""
    return value.quantize(CENTS, rounding=ROUND_HALF_UP)

class EscalationRule(BaseModel):
    kind: Literal["fixed_step", "fixed_percent"]
    # For fixed_step: absolute new base per anniversary year (index 0 = year 1).
    # For fixed_percent: annual rate compounded each anniversary, e.g. Decimal("0.03").
    percent: Optional[Decimal] = None
    steps: Optional[list[Decimal]] = None

class CanonicalLease(BaseModel):
    lease_id: str
    start_date: date
    end_date: date
    base_monthly_rent: Decimal
    escalation: EscalationRule
    cam_monthly_estimate: Decimal = Decimal("0.00")
    abatement_periods: set[str] = Field(default_factory=set)  # ISO year-months

    @field_validator("base_monthly_rent", "cam_monthly_estimate")
    @classmethod
    def _non_negative(cls, v: Decimal) -> Decimal:
        if v < Decimal("0"):
            raise ValueError("monetary inputs must be non-negative")
        return v

class PaymentScheduleRow(BaseModel):
    lease_id: str
    period: str            # ISO year-month, e.g. "2026-08" — stable sort + upsert key
    period_start: date
    rent_amount: Decimal
    cam_amount: Decimal
    prorated: bool = False
    abated: bool = False

    @property
    def upsert_key(self) -> tuple[str, str]:
        return (self.lease_id, self.period)

    @property
    def total_amount(self) -> Decimal:
        return money(self.rent_amount + self.cam_amount)

    @field_validator("rent_amount", "cam_amount")
    @classmethod
    def _no_negative_rows(cls, v: Decimal) -> Decimal:
        if v < Decimal("0"):
            raise ValueError("schedule rows must never carry a negative amount")
        return money(v)

def _rate_for_year(lease: CanonicalLease, year_index: int) -> Decimal:
    """Escalated base rent for the Nth anniversary year (0-based)."""
    rule = lease.escalation
    if rule.kind == "fixed_step" and rule.steps:
        idx = min(year_index, len(rule.steps) - 1)
        return rule.steps[idx]
    if rule.kind == "fixed_percent" and rule.percent is not None:
        factor = (Decimal("1") + rule.percent) ** year_index
        return money(lease.base_monthly_rent * factor)
    return lease.base_monthly_rent

def _prorate(rent: Decimal, occupied_days: int, total_days: int) -> Decimal:
    return money(rent * Decimal(occupied_days) / Decimal(total_days))

def expand_schedule(lease: CanonicalLease) -> list[PaymentScheduleRow]:
    """Expand a canonical lease into one deterministic row per calendar month."""
    rows: list[PaymentScheduleRow] = []
    cursor = date(lease.start_date.year, lease.start_date.month, 1)

    while cursor <= lease.end_date:
        period = f"{cursor.year:04d}-{cursor.month:02d}"
        total_days = monthrange(cursor.year, cursor.month)[1]
        month_end = cursor + relativedelta(day=31)
        year_index = relativedelta(cursor, lease.start_date).years

        rent = _rate_for_year(lease, year_index)
        cam = lease.cam_monthly_estimate
        prorated = False

        # Proration for a partial first or last month.
        occ_start = max(cursor, lease.start_date)
        occ_end = min(month_end, lease.end_date)
        occupied = (occ_end - occ_start).days + 1
        if occupied < total_days:
            rent = _prorate(rent, occupied, total_days)
            cam = _prorate(cam, occupied, total_days)
            prorated = True

        # Free-rent / abatement window zeroes rent but keeps CAM.
        abated = period in lease.abatement_periods
        if abated:
            rent = Decimal("0.00")

        rows.append(PaymentScheduleRow(
            lease_id=lease.lease_id,
            period=period,
            period_start=cursor,
            rent_amount=money(rent),
            cam_amount=money(cam),
            prorated=prorated,
            abated=abated,
        ))
        cursor += relativedelta(months=+1)

    return rows

The upsert is what makes re-syncing safe. Rather than deleting and re-inserting, each row is written on its natural key and compared against any already-posted period. The write is a no-op when nothing changed, an insert when the period is new, and a flagged mismatch when a computed amount contradicts a period the billing system already invoiced — never a silent overwrite of money a tenant may have already paid against.

from dataclasses import dataclass

@dataclass
class SyncOutcome:
    inserted: int = 0
    unchanged: int = 0
    conflicts: list[tuple[str, str]] = None  # (period, reason)

def sync_rows(rows: list[PaymentScheduleRow], ledger: dict) -> SyncOutcome:
    """Idempotent upsert keyed on (lease_id, period). `ledger` maps key -> posted row."""
    outcome = SyncOutcome(conflicts=[])
    for row in rows:
        key = row.upsert_key
        posted = ledger.get(key)
        if posted is None:
            ledger[key] = row               # new period: insert
            outcome.inserted += 1
        elif posted["total"] == row.total_amount:
            outcome.unchanged += 1          # replay: no-op
        elif posted.get("invoiced"):
            outcome.conflicts.append((row.period, "posted amount differs"))
        else:
            ledger[key] = row               # future period, not yet billed: update
            outcome.inserted += 0
    return outcome

Because the key is (lease_id, period) and the ledger enforces it as a unique constraint, calling sync_rows twice on the same schedule leaves the ledger byte-for-byte identical — the second pass reports every row unchanged. That is the property that lets a retried worker, a replayed event, or a nightly full re-sync run without ever double-billing.

Reconciling an amendment

When an amendment changes future rent, the canonical lease is re-expanded and re-synced. Periods that already invoiced and whose amount is unchanged stay untouched; future, not-yet-billed periods update in place; and any already-invoiced period whose amount the amendment would change lands in conflicts, where a human decides whether to issue a credit or adjustment rather than rewriting posted history. The append-only nature of that decision trail belongs upstream in amendment versioning; this stage only surfaces the mismatch. CAM true-ups follow the same discipline: the prior year’s actual-minus-estimate is written as one additional reconciling row on a single period, never smeared retroactively across twelve already-posted months.

Validation and quality gates

Correctness here is enforced at the row boundary and at the batch boundary, not discovered later in an invoice dispute. Four gates work together:

• Decimal exactness. Every amount is a Decimal quantized once with ROUND_HALF_UP. The gate rejects any row constructed from a float, and a test asserts that expanding a 60-month lease and summing the rows equals the closed-form total to the cent — no drift accumulates.
• Idempotent upsert. The (lease_id, period) key is unique. A test runs sync_rows twice and asserts the second pass reports zero inserts and zero conflicts, proving replays are no-ops. This is the same idempotency contract the ingestion side applies at commit; here it protects the ledger instead of the canonical store.
• No negative rows. The field_validator refuses any negative rent_amount or cam_amount. Abatement zeroes rent, it never makes it negative; a CAM credit is modeled as a distinct true-up row, keeping every schedule line non-negative and invoice-safe.
• Sum reconciliation. After expansion, the sum of rent across the term is checked against an independent expected total derived straight from the escalation rule, and prorated boundary months are checked against a hand-computed day count. A divergence fails the batch before any row reaches the billing system.

Rows that cannot be reconciled — a period the amendment contradicts, a true-up that would push a line negative — are routed to review with their full context rather than forced into the ledger, exactly as metadata normalization standards demand a typed, auditable value at every boundary.

Troubleshooting

Performance and scale notes

Expansion is cheap: a 10-year lease is 120 rows, and even a 50,000-lease portfolio is a few million rows — well within a single batched transaction path. The cost is in the sync, not the math. Fetch the already-posted rows for a lease in one query keyed on lease_id, diff in memory, and issue the upserts as a single batched statement so a portfolio re-sync is bounded by round-trips, not row count. Because the operation is idempotent, it is also safe to shard by lease_id across workers and to checkpoint per lease — a worker that dies mid-portfolio simply re-runs its shard, and every completed lease reports unchanged. When the volume of change events (amendments, new leases) grows past what a nightly full sync can absorb, subscribe to canonical change events and re-expand only the affected lease, keeping the same expansion and upsert semantics per lease.

Frequently asked questions

How do I keep a re-sync from double-billing a tenant? Key every row on (lease_id, period) and make the ledger write an upsert against a unique constraint on that pair. Expanding and syncing the same lease twice then leaves the ledger identical — the second pass reports every row unchanged. A duplicate charge is only ever possible if the period key is missing or non-deterministic, so make the key the ISO year-month string, which is stable across runs.

Why use Decimal instead of float for rent and CAM? Binary float cannot represent most cent values exactly, so summing sixty monthly amounts drifts by a cent or more and fails audit. Constructing every amount as a Decimal from a string and quantizing once with ROUND_HALF_UP makes the arithmetic exact and identical on every machine, which is non-negotiable for money that will be invoiced.

What happens when an amendment changes future rent after some months are already billed? Re-expand the canonical lease and re-sync. Already-invoiced periods whose amount is unchanged stay untouched, future not-yet-billed periods update in place, and any already-invoiced period the amendment would change is flagged as a conflict for human reconciliation. The stage never rewrites posted history; it surfaces the mismatch so a credit or adjustment can be issued deliberately.

How should CAM estimates and true-ups be scheduled? Add the monthly CAM estimate as a separate amount on each period so it can be prorated and abated independently of rent. At year close, compute actual-minus-estimate and post it as one reconciling true-up row on a single period, never by retroactively editing the twelve estimate rows, which would corrupt periods a tenant has already paid.

How is proration for a partial first or last month computed? Count occupied days inclusively as (occupied_end - occupied_start).days + 1, divide by the real number of days in that month from calendar.monthrange, and multiply the escalated rent by that fraction under Decimal. Never hard-code 30 days — that off-by-one is the most common source of a wrong move-in invoice.

Related

• Syncing Rent Schedules to Accounts-Receivable Systems — the AR-adapter detail for writing these rows into Yardi, RealPage, NetSuite, or a custom ledger idempotently.
• Generating Payment Schedules from Escalation Rules — the deep dive on fixed-step and compounding escalation math that feeds each period’s rate.
• Escalation Formula Mapping — how a lease’s escalation clause becomes the normalized rule this generator consumes.
• Accounting System Integration — the sibling stage that turns the same schedule into ASC 842 / IFRS 16 journal entries and compliance exports.
• Calendar Event Generation — the sibling stage that turns the same lease’s temporal terms into renewal, option, and notice deadlines.

← Back to Downstream Automation & Sync

← Back to Downstream Automation & Sync