Syncing Rent Schedules to Accounts-Receivable Systems

Once Payment Schedule Sync has expanded a canonical lease into an explicit one-row-per-period schedule, that schedule still lives in your own store. This page solves the next problem: pushing those rows into an external accounts-receivable or billing system — Yardi, RealPage, NetSuite, QuickBooks — that already holds its own charges, its own record IDs, and its own notion of what has been invoiced. The generator answered “what does each period owe?”; the reconciler here answers a narrower and messier question: “given the charges the AR system already has for this lease, which ones must I create, which must I update, and which must I void — without ever posting a duplicate or rewriting a period a tenant has paid?” The arithmetic is done. What remains is the integration seam, and it is unforgiving in a completely different way.

Architectural context

This is the last hop before the money leaves your control. Upstream, the schedule rows arrive already validated — every amount a Decimal, every period an ISO year-month key — as produced by generating payment schedules from escalation rules and quantized under the discipline the parent Payment Schedule Sync cluster defines. This layer never recomputes rent; it treats the desired schedule as ground truth and the external ledger as a mutable target to converge toward.

The core difficulty is that the AR system is a foreign store. Your (lease_id, period) natural key means nothing to Yardi; Yardi returns a charge_id it minted. So an upsert into an external ledger needs a translation layer: a local external-charge-id mapping table that records, per (lease_id, period), the opaque ID the AR system assigned when you first posted that period. Without it, a re-sync cannot tell an update from a create, and the second run duplicates every charge. Charges that fall out of tolerance — an amendment that would rewrite an already-invoiced month — divert through fallback routing logic to a reconciliation queue rather than being forced onto the ledger, exactly as the canonical boundary in metadata normalization standards refuses to silently coerce a value it cannot trust.

Three ways to reconcile — and why the id-map wins

There are three defensible strategies for making an external ledger match a desired schedule. They differ sharply in how they behave on the second run, on a partial failure, and against a period a tenant has already paid.

Full replace is disqualified immediately: it treats the ledger as disposable, but a real AR system accrues cash applications, credits, and manual adjustments that a delete-and-repost silently erases. Natural-key upsert is clean in theory, but Yardi, RealPage, and NetSuite mint their own charge IDs and rarely let you post against your composite key as a unique constraint — so you cannot ask them “do you already have (LSE-001, 2026-08)?” and get a reliable answer. The external-id map is the only strategy that survives contact with a foreign ledger: you own a small table mapping each (lease_id, period) to the charge_id the AR system returned, so on every subsequent run you know precisely which charge to update and which period is genuinely new.

Canonical row → AR charge field mapping

Each AR system names its fields differently; the adapter’s job is a deterministic translation from your canonical row to the target payload. The mapping is data, not prose, so it stays auditable:

Keeping rent and CAM as separate charge lines matters downstream: it lets an amendment adjust one without voiding the other, and it keeps a CAM true-up as its own line rather than smearing it into rent.

The reconciler

The reconciler takes the desired rows, the charges currently posted in the AR system, and the external-id map, and emits an ordered list of create, update, and void operations. It never talks to the network itself — it is pure and testable — so the same inputs always yield the same operation plan. Money stays Decimal throughout; amounts are compared exactly.

from __future__ import annotations
from dataclasses import dataclass, field
from decimal import Decimal
from enum import Enum
from typing import Optional


class Op(str, Enum):
    CREATE = "create"
    UPDATE = "update"
    VOID = "void"
    REVIEW = "review"   # already-invoiced period the schedule contradicts


@dataclass(frozen=True)
class DesiredRow:
    lease_id: str
    period: str                 # ISO year-month, e.g. "2026-08"
    amount: Decimal             # rent + CAM already summed per charge line


@dataclass(frozen=True)
class PostedCharge:
    charge_id: str              # opaque id minted by the AR system
    period: str
    amount: Decimal
    invoiced: bool              # True once the AR system has billed it


@dataclass(frozen=True)
class ChargeOp:
    op: Op
    lease_id: str
    period: str
    amount: Decimal
    charge_id: Optional[str] = None   # present for UPDATE and VOID
    reason: str = ""


@dataclass
class ReconcilePlan:
    ops: list[ChargeOp] = field(default_factory=list)

    def counts(self) -> dict[str, int]:
        out = {o.value: 0 for o in Op}
        for op in self.ops:
            out[op.op.value] += 1
        return out


def reconcile(
    lease_id: str,
    desired: list[DesiredRow],
    posted: list[PostedCharge],
    id_map: dict[tuple[str, str], str],
) -> ReconcilePlan:
    """Diff the desired schedule against posted AR charges into create/update/void ops.

    Pure and deterministic: given the same three inputs it always returns the
    same plan, so a retried or replayed sync is a no-op past the first run.
    """
    plan = ReconcilePlan()
    posted_by_period = {c.period: c for c in posted}
    desired_periods = {r.period for r in desired}

    # 1. Every desired period: create, update, no-op, or flag for review.
    for row in desired:
        charge = posted_by_period.get(row.period)
        if charge is None:
            plan.ops.append(ChargeOp(Op.CREATE, lease_id, row.period, row.amount))
        elif charge.amount == row.amount:
            continue                                    # already correct: no-op
        elif charge.invoiced:
            # The amount changed on a period the AR system already billed.
            # Never overwrite posted money — surface it for reconciliation.
            plan.ops.append(ChargeOp(
                Op.REVIEW, lease_id, row.period, row.amount,
                charge_id=charge.charge_id,
                reason=f"invoiced {charge.amount} != desired {row.amount}",
            ))
        else:
            plan.ops.append(ChargeOp(
                Op.UPDATE, lease_id, row.period, row.amount,
                charge_id=charge.charge_id,
            ))

    # 2. Posted periods no longer in the schedule (e.g. an amendment shortened
    #    the term): void the future ones, flag invoiced ones for review.
    for charge in posted:
        if charge.period in desired_periods:
            continue
        if charge.invoiced:
            plan.ops.append(ChargeOp(
                Op.REVIEW, lease_id, charge.period, Decimal("0.00"),
                charge_id=charge.charge_id,
                reason="invoiced period dropped from schedule",
            ))
        else:
            plan.ops.append(ChargeOp(
                Op.VOID, lease_id, charge.period, charge.amount,
                charge_id=charge.charge_id,
            ))

    return plan

The writer applies the plan against the AR API, and this is where idempotency is sealed. A create that succeeds but whose response is lost to a timeout must not create a second charge on retry, so each write carries an idempotency token derived from (lease_id, period) and the AR system’s returned charge_id is persisted to the id-map before the next operation runs. Writes are batched and rate-limited because these APIs cap requests hard:

import time


def apply_plan(plan: ReconcilePlan, client, id_map: dict, *, batch_size: int = 50,
               max_per_second: int = 8) -> dict[str, int]:
    """Apply create/update/void ops in rate-limited batches; persist new ids."""
    applied = {o.value: 0 for o in Op}
    interval = 1.0 / max_per_second
    writes = [op for op in plan.ops if op.op is not Op.REVIEW]

    for start in range(0, len(writes), batch_size):
        for op in writes[start:start + batch_size]:
            token = f"{op.lease_id}:{op.period}:{op.op.value}"  # idempotency key
            if op.op is Op.CREATE:
                charge_id = client.create_charge(
                    lease_id=op.lease_id, period=op.period,
                    amount=op.amount, idempotency_key=token,
                )
                id_map[(op.lease_id, op.period)] = charge_id   # persist before next op
            elif op.op is Op.UPDATE:
                client.update_charge(op.charge_id, amount=op.amount, idempotency_key=token)
            elif op.op is Op.VOID:
                client.void_charge(op.charge_id, idempotency_key=token)
            applied[op.op.value] += 1
            time.sleep(interval)   # respect the AR API's rate ceiling

    return applied

Because reconcile is pure and apply_plan keys every write on (lease_id, period), running the whole sync twice produces zero operations on the second pass — every desired period now matches a posted charge at the same amount.

Edge cases specific to commercial leases

• Partial-period proration mismatch. The expansion engine prorates a move-in month by inclusive day count, but an AR system may prorate by its own convention (30-day month, or actual/actual). The two agree to the cent only if the AR side is configured to accept the exact amount you post rather than recomputing it. Always post the fully-resolved Decimal amount and disable the AR system’s own proration for these charges, or the diff will show a perpetual one-cent mismatch that never converges.
• Amendment reduces future rent — void vs credit. If the reduced period has not been invoiced, the reconciler voids the old charge and creates the new one. If it has been invoiced, voiding would erase billed history; the correct move is a credit, which is an accounting decision, not a sync decision — so the row goes to REVIEW. The distinction is entirely charge.invoiced, and getting it wrong either double-charges or destroys an audit trail.
• Duplicate postings on retry. A create whose HTTP response is lost looks like a failure but may have succeeded. Never blind-retry a create; either carry an idempotency token the AR API honours, or re-fetch the lease’s posted charges and let reconcile see the now-existing charge as a no-op. The id-map is the durable record that closes this gap.
• Currency and rounding. Multi-currency portfolios must post in the lease’s currency and quantize with ROUND_HALF_UP before the payload is built, never letting the AR system round a full-precision float. A charge posted as 1234.5 where the ledger stores 1234.50 still compares equal under Decimal, but a value carrying a third decimal will diff forever.
• Already-invoiced periods are immutable. Any period the AR system has billed is treated as frozen. The reconciler may compare against it but never emits an update or void for it — only a REVIEW op — so cash already applied against that invoice is never orphaned.

When to escalate

The reconciler is designed to converge silently; when it cannot, the fallback is human, not automatic:

• Reconciliation mismatch beyond tolerance. When the summed difference between desired and posted for a lease exceeds a configured tolerance (say, more than a rounding cent per period), stop applying and route the whole lease to review through fallback routing logic. A large aggregate gap usually means a bad upstream expansion, not a stale ledger — do not paper over it with writes.
• Any REVIEW op on an invoiced period. These are never auto-resolved. An amendment that would rewrite billed history needs a person to choose credit, adjustment, or dispute; the sync’s job ends at surfacing the contradiction with full context.
• Repeated AR API failures. If a batch fails past its retry budget, checkpoint the id-map, mark the lease incomplete, and re-drive it later — never leave a partial plan half-applied without a record of where it stopped.

Frequently asked questions

Why do I need an external-charge-id mapping table instead of upserting on (lease_id, period)? Because the AR system mints its own charge IDs and usually will not let you post against your composite key as a unique constraint. Without a local table mapping each (lease_id, period) to the AR-returned charge_id, a re-sync cannot distinguish an update from a create and duplicates every charge. The map is what makes the sync idempotent against a foreign ledger.

How does the reconciler decide between an update and a void? It diffs the desired schedule against the posted charges per period. A period present in both with a changed amount and not yet invoiced becomes an update; a posted period that has dropped out of the schedule and is not yet invoiced becomes a void. Any period the AR system has already invoiced is never updated or voided — it is flagged for review so billed history is never rewritten.

How do I prevent duplicate charges when a create times out? Carry an idempotency token derived from (lease_id, period) on every write so the AR API collapses a retried create into the original, and persist the returned charge_id to the id-map before the next operation runs. If the API offers no idempotency key, re-fetch the lease’s posted charges and re-run the pure reconciler, which will see the now-existing charge as a no-op.

What tolerance should trigger manual reconciliation rather than an automatic write? Start at a single rounding cent per period; a diff larger than that across a lease usually signals a bad upstream expansion, not a stale ledger. When the aggregate difference exceeds tolerance, route the lease to review instead of writing, because forcing the ledger to match a wrong schedule turns a detectable mismatch into silently mis-billed rent.

Should rent and CAM be one charge or two in the AR system? Two separate charge lines. Keeping them distinct lets an amendment adjust rent without touching CAM, keeps a CAM true-up as its own reconciling line rather than smeared into rent, and makes each line independently prorated, voided, or credited — which matches how billing systems and tenants actually read an invoice.

Related

• Generating Payment Schedules from Escalation Rules — the sibling that produces the desired schedule rows this reconciler pushes; it does the rent math, this page does the ledger sync.
• Payment Schedule Sync — the parent cluster that expands a canonical lease and defines the (lease_id, period) idempotency contract this adapter extends to an external system.
• Accounting System Integration — the sibling stage that turns the same schedule into ledger journal entries rather than AR charges.
• Exporting Lease Schedules to ASC 842 Compliance Systems — the compliance-export path for the same rows, where the target is a lease-accounting standard rather than a billing ledger.

← Back to Payment Schedule Sync

← Back to Payment Schedule Sync