0073 — Multi-source ingestion & reporting platform (learn-a-config, gated, reconciled)

• Status: Accepted — shipped to production (see Implementation status below)
• Date: 2026-06-30 (accepted 2026-07-01)
• Deciders: Fede
• Motivating context: driven by a real multi-manager, multi-PMS affordable-housing operator. The client identity, property/manager roster, and GTM/sales strategy live in the gtm client repo — this ADR is deliberately client-agnostic product architecture.

✅ Validated on real data (2026-06-30), through the real automated pipeline — reproducible, not by hand. The generic structure-detect → config-driven extract → tie-out-judge pipeline (§13) — not a bespoke script — has run end-to-end against 13 properties across 8 structurally-different source formats in one workbook: four Yardi box-score variants (Asset Living / MRR / Ross / Ridge), a scalar label/value summary (Citrus Glen), a single-row portfolio rollup (Orchard Walk), a weekly time-series (Eagle Villas), and a two-tab-summed-plus-financials format (The Ashley) — property names anonymized, roster in the gtm repo. Result: 155 of 159 comparable fields tie to the cent against the operator's own published Weekly Report, 0 unexpected mismatches, overall tiedOut = true; a generated Weekly Report .xlsx built from the parsed data independently reproduces 155/182 cells to the cent, with every remaining cell honestly classified (5 mismatches + 22 source-gaps) rather than silently coerced. Three genuine source discrepancies were caught and held for review, not hidden or guessed past — see §3a. Full numbers in §3a/§9; §13 answers the learn-a-format engine (including the new non-tabular keyValue block kind), the verification layers, and onboarding (full spec in the companion ../architecture/clara-ingestion-platform.md).

Implementation status (2026-07-01): Accepted and shipped. The learn-a-config engine and the customer-facing Portfolio Intelligence feature this ADR describes are live in production — no longer a prototype. Shipped across:

• #2740 — Phase-0 box-score scaffolding + gate engine (the original prototype referenced in earlier drafts of this note).
• #2767 / #2779 / #2792 — customer feature: generic config-driven parser (no hardcoded per-format parsers), the setup/learn-a-layout wizard, and the jargon-free presentation layer; LLM maps columns/structure only, never values.
• #2804 / #2805 — period-keyed append-only snapshot store; config-driven master-report .xlsx renderer.
• #2810 / #2811 — admin-gated surface; coverage-gated verdicts (no vacuous "all figures matched").
• #2828 — recognition hardening: sample configs never match real uploads, duplicate-mapping rejection, garbage-header plausibility guards, honest save state.
• #2827 — output-format learning: the operator teaches Clara the weekly rollup shape they build today, and the master report reproduces it.
• #2829 — deterministic self-healing learn loop with a strategy-escalation ladder (flat table → keyValue/transposed → multi-block → row-aggregation), best-attempt persistence, and cause-specific plain-English failure messages.
• #2835 — pre-demo UX: staged pipeline-progress loader, honest occupancy rounding, brand-token sweep.
• #2831 — real per-org data in the master-report download (was serving demo fixtures), reject path on the recognized-layout screen, box-score honest-nulls + dedup, read-after-write consistency, and a "layouts Clara knows" management view.
• #2839 — layout learning runs on Opus (decoupled from the conversational AGENT_MODEL); weekly re-reads make no model call, so steady-state cost is unchanged.

Known gaps carried forward as follow-ups (tracked, not hidden): golden-set learn rate is ~16–17/21 on the real corpus with honest holds on the rest; multi-block composition (joining unrelated blocks across a sheet, e.g. Subsidy Delinquency) is the one strategy still descoped; a box-score sheet that splits one property across multiple sub-sections (Ash1BxScr) converges on only the first block; email Mode B ingestion is not yet built (upload-only). Phase 1 (Temporal-orchestrated per-report arrival) remains as described below.

1. Strategic intent (why this exists)

Client-specific GTM strategy (who we sell, pricing, stakeholders, competitive positioning) lives in the gtm client repo. This section states only the product motivation.

• Goal: a resilient, multi-tenant platform capability — ingest a portfolio's weekly manager reports (multiple PMS instances, divergent formats, some managers with no PMS at all → emailed tables) and produce the operator's portfolio report + a grounded intelligence layer. Not a per-client one-off build.
• Why a platform beats DIY office-automation (Power Automate / Copilot / hand-built scripts): those paths are brittle to format drift, have no schema/reconciliation gate, no HITL, no regression eval, and need a technical owner to maintain — so they confidently emit plausible-but-wrong numbers. The platform owns the resilience, the correctness gates, the intelligence layer, and the run-cost.
• Land-and-expand: email-based ingestion now → direct PMS integration later → adjacent report types → owner-facing intelligence (chat / voice / MCP).

2. Problem & requirements

• Input (source class 1 — PMS reports): property reports, arriving by email initially — xlsx attachments + tables pasted into email bodies, in multiple formats across several managers. (Direct PMS API later carries a significant annual license cost — figure pending a real quote; not cited here to avoid stating an estimate as fact — so prefer email/browser-agent until volume justifies it.)
• Input (source class 2 — utility / billing platforms): monthly utility data from platforms with no API (e.g. Conservice) — acquired via browser agents, then fed through the same learn-a-config → canonical → gate pipeline. Browser agents do acquisition only; parsing/reconciliation stays deterministic (consistent with §5b, which flags browser-agents as a false friend for the parsing pipeline — they're fine as a fetch layer). Same problem shape (arbitrary vendor formats), same engine.
• Output:
  1. A portfolio-level weekly report in the operator's exact format (occupancy, payables, delinquency, move-ins/outs) — replacing a multi-hour manual build across several analysts.
  2. An intelligence layer — portfolio insights grounded strictly in validated report fields (no invented numbers).
• Non-negotiables: correctness & consistency above all; resilient to slight format changes (must not break); compliant (fair-housing, PII); fully auditable.
• Interfaces (once ingestion is solid): Clara chat and (preferred) a specialized MCP server the operator connects to their Claude Team instance — tools/resources tailored to asset-manager workflows.
• Anti-goal: "thin LLM wrapper over some files." Depth = specialized, resilient, precise, compliant agents + workflows.

3. Proposed architecture (strawman — to be validated by the research fleet)

Layered, deterministic-first, agent-escalation-as-fallback, correctness-gated:

Cross-cutting guardrails: fair-housing/PII handling, full audit log, and a golden-dataset regression eval so a parser change can't silently regress — this is the core format-drift defense.

3a. The reliability architecture — the GATE STACK (precision · determinism · consistency)

Governing principle: correctness and consistency over everything else. The LLM is the last resort, never the default. A layer may only pass data forward if it provably satisfies the canonical schema. Anything uncertain is held for a human — never guessed. We would rather produce no number than a wrong number.

The pipeline is a ladder of gates. Each gate either passes clean data forward or stops it. Most weeks never touch an LLM.

Why the numbers are deterministic/reproducible:

1. The LLM (G3) only ever decides which column = which field — it never emits a value. Values always come from deterministic extraction of the raw cell. Same input → same output.
2. LLM calls are temperature 0 + schema-constrained (Anthropic structured outputs / strict tool use), so malformed mappings can't occur.
3. Every layer is idempotent and cached on (manager, week).

Why this beats their Excel AND the Microsoft DIY path — and it's provable in their own file:

• Their Excel wires each metric to an absolute cell address (='<Manager Sheet>'!G7). When a manager's export shifts one row, the formula keeps computing — silently wrong. We extract occupied units by meaning, then G5 cross-checks the arithmetic, so drift is caught, not propagated.
• Power Automate + Copilot has no schema gate, no reconciliation, no HITL, no regression eval — it will confidently produce plausible-but-wrong numbers. Our entire design is "fail loud, never guess." That is the platform we charge a subscription for.

✅ Validated on real operator data (2026-06-30), through the real automated pipeline — reproducible, not by hand. The pipeline is the generic structure-detect → config-driven extract → tie-out-judge described in §13, run end-to-end (not a bespoke script) against 13 properties across 8 structurally-different source formats in one workbook (property names anonymized; the roster lives in the gtm repo):

• Asset Living / MRR / Ross / Ridge — four Yardi box-score variants: occupied + vacant + notice + model + down + admin = units holds to the unit on every row; occupancy reproduces the published % Occ to the basis point.
• Citrus Glen — a label/value scalar summary, no table at all — read via the new keyValue block kind (§13), same canonical schema as the tabular formats.
• Orchard Walk — a single-row portfolio rollup.
• Eagle Villas — a weekly time-series — the engine selects the row matching the report period.
• The Ashley — two per-floor-plan box-score tabs, summed, plus a separate financials tab.
• Cross-tab tie-out — all 8 formats → the operator's own published Weekly Report, same week (the real proof): 155 of 159 comparable fields tie to the cent, 0 unexpected mismatches, overall tiedOut = true. A generated Weekly Report .xlsx built from the parsed data reproduces 155/182 cells to the cent; every remaining cell is honestly classified — 5 mismatches + 22 source-gaps — never silently coerced to a plausible-looking number.

Honest exceptions — surfaced, not hidden (live evidence for the hold-for-review design, G7):

• Eagle Villas: its bespoke weekly tracker's delinquency does not reconcile to the Weekly Report — off by a consistent +$1,721.61 on both aging lines. Held for human review; never emitted as a silent number.
• Dahlia Village: the Weekly Report's payables value equals Salt River Flats' exact payables, and Dahlia's own source has no A/P line at all — a probable copy error in their sheet, flagged rather than guessed at.
• Orchard Walk: the Weekly Report stores a rounded occupancy (0.9314) against our more-precise derived value (0.931373) — judged to the Weekly Report's own stored precision rather than flagged as a mismatch.

This is the central thesis (§9) proven end-to-end on real files through the real pipeline — reproducible, not asserted — and it only works once the real Model/Down/Admin columns (and, for keyValue sources, the real labels) are present. The earlier scrubbed fixtures had tokenized headers away and were a different week than the answer key, which is exactly why an intermediate run once appeared to fail every real file — tying every source to the same-week Weekly Report closes it (see §6, test-fixture hygiene).

3b. Reverse-engineered target — the Weekly Report output spec

The Weekly Report tab (their literal output) stacks ~26 weekly blocks top-to-bottom (newest first = built-in history). Each block:

• Title (Weekly Report MM/DD) → header row → ~13 property rows → TOTAL row → a Week-over-Week DIFFERENCE block.
• Columns & rules (per property):

• Canonical per-property record = {property, units, vacant, leased, ntv, moveIns, moveOuts, delinq0_30, delinq30plus, cash, payables}; everything else is derived. TOTAL row = SUM, with occupancy as a unit-weighted SUMPRODUCT.
• ~13 properties, mapped to ~6 structurally-distinct manager source layouts (availability/box-score, AR-aging, and variants). Each layout = one learned config profile (G1 / §13). (The specific property/manager roster lives in the gtm client repo, not here.)

4. Open research questions (the fleet is filling these)

1. ▢ OSS ingestion/parsing toolkits (xlsx / email / PDF) — reuse vs build.
2. ▢ Deterministic-first + agentic-escalation patterns; structured-output + self-repair; consistency/eval harnesses; format-drift resilience.
3. ▢ AI-native ingestion platforms (build-vs-buy) and fit for a per-customer PM SaaS.
4. ▢ Temporal + HITL patterns for document pipelines — is Temporal warranted here, or lighter-weight?
5. ▢ Asset-manager domain + MCP interface design (what AMs analyze, report contents, tool shapes).
6. ▢ Existing PropFlow email pipeline — strengths / weaknesses / what's reusable (inspection).

5. Decision (validated by the research + pipeline-inspection fleet, 2026-06-30)

Build ~90% on PropFlow's existing primitives — extend, don't greenfield, and don't buy a document-AI platform. The deterministic→LLM→Vision parsing cascade, sender-trust + idempotency + resilience infra, and the auto-publish-vs-HITL gate already exist and transfer directly. Reuse map by layer:

Only 3 net-new pieces, all small: canonical schema (L3), reconciliation engine (L4), report renderer (L6).

Sequencing:

• Phase 0 (pilot) = POC #1: run parse → schema → reconcile → HITL gate inline / manually-triggered against several real (scrubbed) weeks of operator data. Prove correctness cheaply.
• Phase 1 (production): wrap the proven steps in a Temporal workflow (signal per manager-report arrival + PM approval/edit; timer for late-manager escalation).

Build-vs-buy: do not buy Docling/Unstructured/LlamaParse/Sensible/Extend — none beats what we have, and the real differentiator (Yardi semantics + cross-manager reconciliation + the operator's format + no-invented-numbers intelligence) isn't purchasable. Only possible buy: narrow OCR fallback (AWS Textract, already in our stack) iff PDF is in scope — and the real files are multi-sheet xlsx/tabular, so PDF is likely out and no purchase is needed.

5a. Honest current-state correction (code-verified 2026-06-30)

A deep code read corrected the optimistic "~90% built" framing. Reuse is narrower and more specific than a layer table implies — and the component whose name matches our need is the weakest. The regulated-correctness core is genuinely net-new and hard.

• ❌ properties/reports/weekly-parser.ts is a cautionary anti-example, NOT a foundation. Hard-coded to ONE manager (ConAm/Yardi label matching, fixed sheet names), dispatched purely by file extension (no manager/format detection at all), zero tests, auto-parse path dev-only. Extending it as-is would bake in the exact brittleness we sell against. A warning, not a base.
• ⚠️ The PMS adapter registry (domain/pms/registry.ts) is proven at n=1 — only AppFolio implemented; Yardi/RealPage/OneSite are enum stubs returning null. Good interface design, but "handles N divergent backends" is unverified. Don't model format detection on it (it does credential/transport selection for a typed API, not document-format detection).
• ✅ The real detection precedent is leasing/rent-roll/registry.ts — confidence-scored detect() + tiered fallback (regex → LLM-column-map → vision), header-alias normalization, an isResultValid() sanity gate, 115 tests. Right model for G1–G4. Caveat: one parser today (genericCsvParser) — proven on header-drift within one CSV shape, not across 6 structurally-different named formats. Our 6 adapters are real new work.
• ✅ Directly reusable gem — the unmapped-value ledger (data/dynamo/unmapped-values.ts): on an unrecognized value, return a safe default AND record it to a durable counter for operator review, then extend the enum in code. Fail-soft + drift observability — wire straight into G2/G5 (record any column/section that doesn't map). This is the single most transferable correctness primitive in the repo.
• ✅ Reusable testing methodology — the turnover "gauntlet": pure-function invariants over (input, output), axis-tagged committed regression corpus, frozen-real-failures as permanent fixtures, deterministic seeded-PRNG synthetic generator grounded in a checked-in taxonomy. The proven shape for G9. BUT invariants are dictation-specific → we write a parallel tabular-report invariant set from scratch ("occupied + vacant = units", "renamed/reordered column still resolves", "no column silently dropped", "delinquency total reconciles"). The big adversarial-generation run was ad hoc/ephemeral, not a reusable script.

Net: reuse the fail-soft ledger + the detection/testing shapes; design the canonical weekly-report schema, the 6 per-manager adapters, cross-property reconciliation, and a tabular invariant suite from scratch. That core is the build — and it's where the regulated stakes live, not in the plumbing.

5b. Newer PropFlow patterns to draw on — and the net-new-hard list (code-verified)

The email pipeline is one of the older parts. Newer subsystems offer better inspiration, assessed honestly:

Transferable disciplines (adopt deliberately):

• Correctness philosophy — the most valuable thing in the repo for us: ADR-0065 (deep self-discovering evals: claim→manifestation graph, completeness critic, "never weaken a gate to pass") + ADR-0064 (independent read-verified truth; never echo a write back as confirmation). This is the doctrine for G5/G9.
• The "guarantee" pattern — ADR-0068/0070: synchronous idempotent mint + loud failure (logCritical/alert) + a standing reconciler that re-scans every cycle → "guarantee every property's weekly report ingests-or-escalates; never silently drop one."
• Calibrated abstention — ADR-0059 command ladder: LLM proposes from a closed vocabulary, deterministic ladder disposes, abstains when unsure → strengthens G3.
• "Ask, don't guess" terminal state (turnover taxonomy): a parse may legitimately end in "needs review / needs value," never a guessed number → G7.
• Temporal HITL template = tenant-confirmation-review-workflow.ts (reviewer approves a specific fact, never auto-approves) — NOT renewal's batch classifier. Signal + condition(predicate, timeout) + eventId dedup + 4-layer idempotency.

Hard prerequisite if we use Temporal (learned the painful way): ADR-0052 — a deploy missing a patched() guard wedged every in-flight workflow for 13 days, silently, zero alerts. Fix = a replay-corpus CI gate (replays PII-scrubbed prod histories against new code). For a regulated weekly pipeline, build that gate day one. Temporal here is production-partial, not mature (4 real incidents in 7 weeks; flagship customer still holdover-only).

False friends — do NOT import:

• Browser-agent / L4 (Browserbase) solves "no API → drive the UI like a human" — inherently non-deterministic, "trust-but-verify-after" control model. The opposite of a deterministic report pipeline. Category error.
• TraceCollector truncates tool I/O (2–4k chars), not immutable → fine for debugging, not a regulatory audit trail.

Genuinely hard & net-new — nothing in the codebase solves it for us:

1. A registry that confidently dispatches among several competing structured parsers (6 manager formats), not "one structured parser + AI fallback." Rent-roll registry is a skeleton, unproven at our scale.
2. A batch / portfolio-wide "workflow per weekly ingestion run" — everything on Temporal today is reactive/per-entity.
3. A field-level, immutable, retention-compliant audit store for HUD/LIHTC — TraceCollector doesn't meet this bar.
4. A hard pass/reject extraction bar. Sobering proof it's hard: the closest existing analog — turnover dictation extraction — still has most of its 27 adversarial failure classes RED after sustained investment, and its dominant bug is exactly ours (multi-item value→line-item misassociation). Their tolerance model ("mostly right, PM catches it at recap") is invalid for LIHTC reporting, which has no per-number human confirmation step.

Honest stakes picture: the platform gives a running start on plumbing and a library of hard-won correctness disciplines — but reliable structured extraction from messy multi-format input is hard even for PropFlow with months of investment, and this regulated bar is higher than anything shipped. That is the build, and it's why it's a retainer, not a weekend.

6. Consequences / risks

• weekly-parser.ts is untested and hard-coded to one Yardi/ConAm wording — it will silently break across 3 manager formats unless generalized into per-manager profiles with golden tests before pilot, not after.
• No portfolio/cross-property concept exists anywhere (repo-wide grep) — L3/L4 are real design work, easy to under-estimate as "mostly reuse."
• Do not reuse the Vercel after()-chained pattern — it lost ~34/35 signals in the 2026-06-10 production incident on a shorter pipeline; this multi-day, wait-for-3-managers + human-approval job is exactly Temporal's signal/timer sweet spot.
• No-invented-numbers now applies to a customer-facing, recurring artifact for the first time — one hallucinated trend claim is uniquely trust-destroying given we win this on correctness.
• MCP scope: keep it read-mostly against approved reports only; the single write path (flag_data_issue) routes through HITL — never let a Claude Team agent loop bypass the gate and mutate canonical data.
• Test-fixture hygiene (learned 2026-06-30): the initial PII scrub false-tokenized real column headers (e.g. Occupied No Notice, Net Occupancy → Resident_00XX), corrupting the very data used to validate ingestion and making real files look unparseable. The scrub must preserve headers + numbers and only tokenize genuine PII. Validate ingestion against header-preserving fixtures.
• Client-file credential exposure (compliance — action item): the client's weekly workbook ships with a Login Info tab holding live plaintext PMS credentials (usernames, passwords, portal URLs across multiple instances) — it travels with every file they share. Action: flag to the client to strip that tab + rotate; on our side, never persist or commit that tab, and add credential/URL detection to the scrub (§11).

7. Open decisions for Fede (business calls, not engineering defaults)

1. Reconciliation conflict policy — when managers overlap on a property with conflicting numbers: one authoritative manager, or any conflict → HITL? (recommend HITL for pilot)
2. Late-manager SLA — publish a partial portfolio report, or hold + escalate? (recommend hold + timer escalation)
3. Scope — client-specific config, or the first instance of a general "portfolio report" product for all customers? (determines whether schema + manager-profiles are multi-tenant from day 1)
4. MCP access model — read-only against approved reports (recommended), or also in-flight/pending-review data?
5. Pilot mailbox — shared clara@ SES (reuse plumbing) or dedicated client alias (cleaner blast radius for a high-correctness customer)?
6. Resolved by the real files: input format = multi-sheet xlsx/tabular, not PDF → no OCR purchase needed for the weekly report. (Utility PDFs are the separate use case.)

8. Scaling across clients & formats without hardcoding forever (the key scaling answer)

The worry: every client/property has a different format — hand-coding 6 adapters per client doesn't scale to N clients. The answer is a growing template-signature registry + a rule-harvesting loop, not infinite bespoke parsers:

⭐ This is now formalized as the platform vision — see ../architecture/clara-ingestion-platform.md. The north star: a self-authoring parser-config engine (the platform LEARNS and stores a parser config per source — data, not code, authored by the platform, never an engineer), driven by a two-mode flow (onboarding learns once → steady-state email path reuses), with input/output symmetry (learn the client's output template too) and a period-keyed completeness gate (no master report until every property for the period validates). Correction to earlier framing: we do NOT hand-code N adapters; PR #2740's box-score adapter is a Phase-0 stand-in for a learned config. The gate stack, canonical schema, and grounded intelligence carry over unchanged.

• Classify/fingerprint BEFORE parsing — the single highest-leverage move. Identify (PMS, report-type, template-version) from structural signatures before extraction. Applying the wrong parser is the dominant failure mode and produces plausible-but-wrong output you can't catch downstream. (This is a new stage ahead of G1 — add it.)
• Two-speed by signature: a known signature → deterministic parser (fast, free, auditable). An unknown/drifted signature → LLM proposes a structural mapping, a human confirms once, and we promote it to a new template signature. Flexible to format change without paying LLM cost/variance on every file forever.
• LLM checkers are the R&D layer that discovers new deterministic rules. When a checker keeps flagging the same issue class, promote it to a hard-coded rule. Coverage grows; per-file LLM spend shrinks. The registry compounds (PRODA's actual moat — §12).
• Net: build the classification + canonical-schema + reconciliation core once; per-format knowledge accretes as signatures + rules and becomes more deterministic over time, not less.

9. The hard semantic gate — reconciliation tie-outs (expands G5/G6)

Structural correctness ("did I read the cells right?") and semantic correctness ("do the numbers tie?") are separate gates; both must pass before the reasoning layer sees anything. Tie-outs:

• Aging buckets sum to total AR (0-30 + 31-60 + … = TOTAL); Total + Prepays = Balance. ✅ Verified exact across the 8-format/13-property automated run, 2026-06-30 (§3a) — 155/159 comparable fields to the cent, 0 unexpected mismatches.
• Unit count ties to the property: occupied + vacant + notice + model + down + admin = units (the naive occupied + vacant = units is wrong for real files with model/down/admin excluded units). ✅ Verified exact across the same automated run.
• Sum of charges reconciles to the GL balance; beginning + activity = ending.
• Cross-document: delinquency report and rent roll agree on who's behind; summary totals tie to per-property detail.
• Occupied unit with no rent recorded = reconciliation smell → flag.
• No reconcile → does NOT persist → quarantine queue for human review. A clean dead-letter path with no silent drops is non-negotiable. ✅ Proven live, not hypothetical (§3a): the automated run caught and held three genuine discrepancies instead of emitting them — Eagle Villas' delinquency off by a consistent +$1,721.61 on both aging lines; Dahlia Village's payables matching Salt River Flats' exact figure though Dahlia's own source has no A/P line; Orchard Walk's occupancy judged to the Weekly Report's own rounded precision (0.9314) rather than flagged against our more-precise derived value (0.931373). All three held/flagged for a human, never silently resolved.

10. LLM-checker discipline (use AI without it lying to us)

A stochastic checker validating a stochastic extractor gives correlated errors dressed as confidence. Rules:

• Never use an LLM for anything arithmetic — a rule is 100% reliable and free; an LLM there is a 98% regression with latency. Reconciliation stays deterministic.
• LLM job = plausibility/smell, not math: "studio renting at $8k in a $1.5k market," "header says April, dates are March," "labeled 'balance' but values look like sqft." Expert judgment, not computation.
• Don't let the extractor grade its own homework — a separate adversarial grounding pass sees only (source region, extracted value): "is this output supported by this input?" Catches a different error class.
• Give the checker an "I don't know / flag for human" exit — forcing binary pass/fail makes it confabulate.
• Measure it — a golden set (known-good + known-bad, hand-labeled, per report-type) calibrates precision/recall. An unmeasured checker is "vibes with extra steps."
• LLM sits at: classification, extraction-faithfulness, and a final pre-reasoning coherence gate — sampled/near-threshold/aggregate, never row-by-row.

11. Provenance, idempotency, time (expands G9 — and the regulated-grade audit store)

• Every canonical value traces file → sheet → cell — every number an AM sees is auditable and the intelligence layer cites its source. (This is exactly what TraceCollector does NOT do — it truncates; §5b.)
• Idempotent reprocessing: same email in → same result (content-hash dedup; the same report arrives 2–3× constantly).
• Restatements are new versions, not overwrites: a corrected April rent roll arriving in June is the norm → as-of date is part of the primary key; store period snapshots (MoM/trend is the whole point of the intelligence layer).
• Ingest & quarantine: persist the raw artifact immutably, parse a copy, never in place.

12. Landscape & build-vs-buy (externally validated)

• PRODA (London; $8M Series A 2022 — ING Ventures/JLL Spark/Blackstone Innovations/Concrete VC; clients incl. Blackstone/Hines/M&G) — closest analog: standardizes messy rent rolls via a layered schema (raw → normalized → validated → enriched) with hundreds of validation rules (PRODA's own hiring copy: "thousands of variables… over 400 business-logic rules"). ⚠️ Correction: the widely-repeated "800-point model" traces to a single JLL-Spark (investor) blog sentence, uncorroborated by PRODA's own materials — do not cite it as fact. The lesson still holds: the schema IS the product, and it compounds — validates our canonical-schema-first bet. But PRODA is read-only, acquisitions-underwriting-oriented, institutional/top-down, and doesn't touch AP or leases. CleanRoll ("PRODA for the rest of us") = downmarket read-only clone at an SMB price point (public pricing not verified here — confirm before citing) with a good UI pattern: shows which rent column it picked + a confidence score + override. DDee claims rent-roll↔T-12↔lease reconciliation but publishes no methodology and has no discoverable company background — treat its specifics as unverified marketing. (PRODA/DDee internals are not public; we can't literally copy them — so we build the canonical model from verified PMS field dictionaries + HUD/IRS primary docs instead (canonical schema + checks to be captured in a follow-up doc).)
• General IDP: Sensible (SenseML = layout-rules + LLM prompts = our two-speed approach as config), Docsumo/Azure/Google/Textract (field-level confidence thresholds = HITL built in) — where we'd shop if we ever didn't want to own extraction.
• OSS primitives: Docling (IBM, MIT, unified model, local/air-gapped — attractive given PII/SOC-2) for the PDF edge only; Unstructured / Marker-PDF; Unstract (pluggable extractor orchestration); local VLMs (Qwen2.5-VL…) for on-prem plausibility checks.
• Honest read: the operator's inbound is native xlsx → we barely need the extraction tier; direct cell parsing + classification + canonical schema + reconciliation gets us most of the way — the part nobody sells well because it's specific to our canonical model + tie-out rules. Buy extraction only for degraded PDF (minority). Reconciliation + reasoning stay proprietary — that's the moat.
• Differentiation vs PRODA/CleanRoll for the room: they stop at read-only standardization for institutional acquisitions; we serve the operational, mid-market asset manager, ground every number, and (roadmap) act — chat/voice/MCP on validated facts, not just clean data handed back. PRODA stops exactly where our value begins.

13. How the engine learns a format · the verification layers · onboarding (the reviewer's three questions)

The three questions a reviewer asks — how does it learn a format, how is it verified, how does a client onboard — answered end-to-end. (Full spec: companion ../architecture/clara-ingestion-platform.md.)

1 · The engine that learns the format (no hard-coded parser). Format handling is learned by the platform, per source, and stored as a reusable config — data, not code, never authored by an engineer per client. On an unseen file:

• Fingerprint (PMS, report-type, template-version) from structure before parsing (a new stage ahead of G1 — applying the wrong parser is the dominant, un-catchable failure mode).
• Known signature → replay the stored config deterministically (fast, free, auditable, zero LLM).
• Unknown / drifted signature → an LLM proposes the column→canonical-field mapping (header location, sign conventions, parens-as-negative, merged cells, date formats) — it maps schema, never values — a human confirms once, and it's promoted to a stored config. Every later file from that source reuses it with no LLM cost or variance.
• Two block kinds cover tabular and non-tabular sources alike. The existing table kind (rows/columns, header-aliased) handles box-score/AR-aging exports. A new, equally generic keyValue kind reaches everything else validated in §3a — label/value scalar summaries, vertical rollups, per-property column bands, and time-series-select-by-period — with no hardcoded cell addresses and no per-format parser code; both kinds are pure config/data, never a bespoke function per source.
• Per-format knowledge accretes as signatures + rules and becomes more deterministic over time (§8). This is why it scales to N clients without N bespoke parsers, and why PR #2740's box-score adapter is explicitly a Phase-0 stand-in for a learned config, not the design.

2 · The multiple layers of verification (the gate stack, §3a — proven on real data above). The learned mapping only ever decides which column = which field; it never emits a number — values are read deterministically from the raw cells, so same input → same output. Then the ladder: G2 sanity/confidence → G5 hard reconciliation (the full unit identity + aging tie-outs, verified exact across the 8-format/13-property automated run, §3a) → G6 cross-property reconciliation → G7 human-in-the-loop for anything not fully green → G8 grounded intelligence (every number cites its source field; no invented numbers) → G9 immutable audit + golden-eval regression. Governing rule: produce no number rather than a wrong one.

3 · Onboarding — two modes.

• Mode A (onboarding — one-time per source, human-assisted): the client drops one example file → the format-learning engine infers the mapping and shows it back for a single confirm → the output template is learned the same way (input↔output symmetry), so the master report renders in their exact format.
• Mode B (steady-state — fully automated): subsequent files arrive by email → the stored config parses them with no human and no re-learning → a period-keyed completeness gate holds the master report until every property for the period validates (never a partial/silent-drop).
• A working prototype of Mode A exists (drop a file → watch it learn each format's columns → assemble the portfolio report), now grounded in all 8 real formats validated end-to-end in §3a. This prototype is separate from the merged PR #2740 box-score adapter — see the Implementation status note at the top of this ADR for what's shipped vs. in progress.