Agentic Document Extraction

Agentic Document Extraction & Underwriting Pipeline

Turns a commercial real estate Offering Memorandum and rent roll into an audited, investment-committee-ready underwriting memo — with every financial figure computed deterministically, never guessed by a model.

---

Overview

Underwriting a commercial property means reading two documents that actively disagree with each other: a glossy Offering Memorandum full of marketing claims, and the underlying rent roll / T-12 line items that actually produce income. Analysts spend days reconciling them by hand.

This project automates that reconciliation as a LangGraph state machine:

1. Ingest — layout-aware PDF parsing recovers text and spatial tables with bounding boxes.
2. Extract — narrative claims become strictly validated Pydantic models.
3. Calculate — every metric is derived in decimal.Decimal from the raw line items.
4. Audit — the broker's claims are re-derived and compared against the calculations at a 2% variance threshold.
5. Export — an investment-committee PDF memo, with optional PostgreSQL persistence.

🛡️ The zero-hallucination invariant

A language model may read text. It may never produce a number. StructuredExtractor can call OpenAI/Anthropic tool-calling, but its entire contract is to copy verbatim values into a Pydantic schema. All arithmetic — GPR, EGI, OpEx, NOI, cap rate, DSCR, debt yield, IRR — lives in internal/financial/ and uses decimal.Decimal. If the OM says NOI is $900,000 and the line items say $815,000, the pipeline reports the $85,000 discrepancy instead of quietly picking a number.

📖 Interactive diagrams · 🔍 System Architecture · 🔍 Data Lineage

---

✨ Key Features

---

🏗️ System Architecture

🔍 Explore the interactive Archify diagram — pan, zoom, filter by node type, inspect relationship labels, and export PNG/SVG/WebP.

Component breakdown

The pipeline separates into an entry point, a trust boundary, and optional side channels.

Outside the boundary — untrusted input and orchestration

Inside the trust boundary — internal/agent/graph.py

The orange dashed region is the LangGraph StateGraph. Every node takes a UnderwritingState and returns a partial dict that is merged back into it, so each stage is independently testable and its output is inspectable on the returned state.

Branches and optional paths

The first two rows are drawn in the diagram above. The third is an optional capability deliberately left out of the picture so the happy path stays legible.

---

🔄 Core Workflow — Data Lineage

🔍 Explore the interactive Archify diagram

Request lifecycle, step by step

1. Ingest the offering memorandum PDFLoader.load() walks each page with pdfplumber, extracting raw and normalised text plus every table's bounding box and cell grid. pypdf supplies document metadata. (source: internal/ingestion/pdf_loader.py)

2. Normalise the rent roll and T-12 TableExtractor locates the header row by fuzzy column matching (unit, rent, sq ft, status, …) and maps columns positionally, so column order may vary. Currency strings like (1,200.00) and - are sanitised into Decimal. If no CSV is supplied, the rent roll is recovered from the PDF's own tables. (source: internal/ingestion/table_extractor.py)

3. Extract typed deal metadata and broker claims StructuredExtractor emits a DealMetadata record (name, address, asset class, year built, units, rentable SF, asking price) and separately the broker's claimed NOI and occupancy. The claims are stored as assertions, quarantined from the calculation path. (source: internal/agent/extractors.py)

4. Compute the underwriting, deterministically UnderwritingEngine.underwrite() chains GPR → vacancy/credit loss → EGI → OpEx → NOI, then cap rate (NOI / price), DSCR (NOI / annual debt service) and debt yield. Annual debt service uses the standard PMT = P·[r(1+r)ⁿ] / [(1+r)ⁿ − 1] formula, or interest-only when the loan terms demand it. Every value is decimal.Decimal, rounded ROUND_HALF_UP. (source: internal/financial/engine.py)

5. Project the hold and terminal value CashFlowEngine.project() grows revenue and expenses independently over the hold period, capitalises the year-N+1 NOI at the exit cap rate, subtracts sales costs, and solves unlevered and levered IRR from the resulting cash-flow series. (source: internal/financial/cashflow.py)

6. Reconcile claims against line items CrossReconciliationAuditor.audit() checks the unit-conservation invariant, OM-vs-rent-roll unit count, rentable SF variance, claimed-vs-underwritten NOI, and claimed-vs-actual occupancy. Anything beyond VARIANCE_THRESHOLD (2.0%) becomes an is_violation=True flag. (source: internal/agent/auditor.py)

7. Export the Investment Committee memo MemoGenerator renders investment_memo.html with Jinja2 and converts it to PDF, falling back to xhtml2pdf and then ReportLab if WeasyPrint is unavailable. (source: internal/export/memo_generator.py)

8. Optionally persist the deal With --persist-db, DealRepository upserts the deal, batch-inserts every unit row, and stores the evaluation with its audit flags serialised as JSONB. (source: internal/repository/deal_repo.py)

Reading the audit output

Every reconciliation prints as a finding, whether it passes or fails — a clean run is evidence too:

AUDIT FLAGS (1 findings):
  * [VERIFIED ACCURATE] Physical Occupancy Rate: Occupancy verified at 94.0%

A violation looks like this (raised whenever a variance exceeds VARIANCE_THRESHOLD):

  * [FLAGGED VIOLATION] Net Operating Income (NOI): AUDIT WARNING: Claimed NOI
    ($900,000.00) diverges from underwritten NOI ($815,000.00) by 10.43%
    (Threshold: 2.0%)

---

🧰 Tech Stack

---

📁 Project Structure

.
├── cmd/
│   └── cli/
│       └── main.py                  # CLI entry point (argparse, orchestration, reporting)
├── internal/
│   ├── agent/                       # ── LangGraph workflow ──
│   │   ├── graph.py                 # 5-node StateGraph: ingest → extract → calculate → audit → export_memo
│   │   ├── state.py                 # UnderwritingState TypedDict shared between nodes
│   │   ├── extractors.py            # StructuredExtractor + OMExtractionSchema (LLM optional)
│   │   └── auditor.py               # CrossReconciliationAuditor + 2% variance threshold
│   ├── ingestion/                   # ── Document intake ──
│   │   ├── pdf_loader.py            # pdfplumber layout parser, table bboxes, metadata
│   │   └── table_extractor.py       # Header-mapping CSV/PDF table normalizer
│   ├── financial/                   # ── Deterministic math (no LLM) ──
│   │   ├── models.py                # Pydantic domain schemas + validators
│   │   ├── engine.py                # GPR/EGI/OpEx/NOI, cap rate, DSCR, debt yield
│   │   └── cashflow.py              # DCFParameters, projection, unlevered/levered IRR
│   ├── export/
│   │   ├── memo_generator.py        # Triple-engine PDF memo compiler (WeasyPrint/xhtml2pdf/ReportLab)
│   │   └── templates/
│   │       └── investment_memo.html # Jinja2 IC memo template
│   └── repository/                  # ── Persistence ──
│       ├── db.py                    # asyncpg pool + DatabaseConfig + migration runner
│       └── deal_repo.py             # DealRepository upserts
├── migrations/
│   └── 001_init_real_estate_schema.sql   # deals, rent_roll_records, underwriting_evaluations
├── scripts/
│   ├── migrate.py                   # Async CLI wrapper around run_migrations()
│   └── generate_sample_pdf.py       # Regenerates sample_data/sample_om.pdf
├── sample_data/
│   ├── sample_om.pdf                # 2-page synthetic OM with a unit mix + T-12
│   └── sample_rent_roll.csv         # 50 units, 47 occupied / 3 vacant
├── tests/                           # 6 files, 14 tests
├── docs/                            # ── Diagrams ──
│   ├── architecture.html            # Interactive system architecture (Archify)
│   ├── underwriting-lifecycle.html  # Interactive data lineage (Archify)
│   ├── architecture.delivery.json   # Provenance sidecar (SHA-256 of the verified artifact)
│   ├── underwriting-lifecycle.delivery.json
│   └── assets/
│       ├── architecture.png         # Static render used by this README
│       └── underwriting-lifecycle.png
├── .archify/                        # Archify candidate JSON + build receipts
├── output/                          # Generated memos (memo_<deal>.pdf, investment_memo.pdf)
├── docker-compose.yml               # PostgreSQL 16 + healthcheck + auto-applied migrations
├── Makefile                         # up / down / migrate / test / run-underwrite
└── pyproject.toml

---

🚀 Quick Start

Prerequisites

• Python 3.11+
• Docker Desktop (optional — only needed for PostgreSQL persistence)

1. Clone and install

git clone https://github.com/<your-username>/real-estate-underwriting-pipeline.git
cd real-estate-underwriting-pipeline

python -m venv .venv

# Windows (PowerShell)
.\.venv\Scripts\Activate.ps1
# macOS / Linux
source .venv/bin/activate

pip install -e ".[dev]"

2. Run the pipeline on the sample deal

python cmd/cli/main.py

======================================================================
REAL ESTATE AGENTIC DOCUMENT EXTRACTION & UNDERWRITING PIPELINE
======================================================================
OM PDF: ...\sample_data\sample_om.pdf
Rent Roll: ...\sample_data\sample_rent_roll.csv

[Executing LangGraph Pipeline: Ingest -> Extract -> Calculate -> Audit -> Memo]

----------------------------------------------------------------------
DEAL: Highland Heights Apartments
Address: 4200 Highland Park Way, Dallas, TX 75205 | Units: 50 | SF: 48,700
----------------------------------------------------------------------
Offering Price:               $12,500,000.00
Gross Potential Rent (GPR):   $1,088,520.00
Effective Gross Income (EGI):  $1,101,818.00
Total OpEx:                   $343,000.00
Net Operating Income (NOI):   $758,818.00
Underwritten Cap Rate:        6.07%
5-Year Unlevered IRR:         9.10%
5-Year Levered IRR:           9.10%
----------------------------------------------------------------------

AUDIT FLAGS (1 findings):
  * [VERIFIED ACCURATE] Physical Occupancy Rate: Occupancy verified at 94.0%

[MEMO EXPORT] Investment Committee Memo generated at:
  --> ...\output\investment_memo.pdf

Pass --loan-amount to also print DSCR and Debt Yield — the deterministic engine skips all debt metrics when no loan terms are supplied.

3. Run with your own deal and custom financing

python cmd/cli/main.py \
  --om path/to/offering_memorandum.pdf \
  --rent-roll path/to/rent_roll.csv \
  --loan-amount 8000000 \
  --interest-rate 0.0625 \
  --output-memo output/my_deal_memo.pdf

4. Enable PostgreSQL persistence (optional)

# Start PostgreSQL 16 (migrations auto-apply on first boot)
make up

# Apply migrations manually if the volume already exists
make migrate

# Run and persist
python cmd/cli/main.py --persist-db

5. Run the tests

make test
# or
pytest -v

14 passed

Makefile targets (POSIX note)

The Makefile defaults PYTHON to .venv/Scripts/python (Windows layout). On macOS/Linux:

make test PYTHON=.venv/bin/python

---

⚙️ CLI Reference

Environment variables

All variables are optional — the pipeline runs end-to-end with none of them set.

Copy-paste .env template

# .env  — only needed for --persist-db and optional LLM extraction
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_USER=postgres
POSTGRES_PASSWORD=postgres_password
POSTGRES_DB=cre_underwriting

# Optional: unlocks StructuredExtractor's LLM path
# OPENAI_API_KEY=sk-...
# ANTHROPIC_API_KEY=sk-ant-...

docker-compose.yml and internal/repository/db.py read these directly from the environment; export them in your shell or load them with your preferred .env tool before make up.

---

🗄️ Database

migrations/001_init_real_estate_schema.sql creates three tables with ON DELETE CASCADE from real_estate_deals:

All money is stored as NUMERIC, never floating point.

---

🧪 Testing

pytest -v

---

🎨 Diagrams

Both diagrams are authored as Archify JSON and render to self-contained, offline-capable HTML viewers.

Sources live in .archify/. Each diagram also ships a docs/*.delivery.json provenance sidecar recording the SHA-256 of the exact artifact that passed the automated gates. To regenerate after changing the architecture:

node <archify>/bin/archify.mjs finalize architecture \
  .archify/architecture-underwriting-pipeline-*/candidate.json \
  docs/architecture.html --quality showcase --json

Note for Windows contributors: Archify's atomic publisher compares fs.lstat() and fs.fstat() device IDs, which differ on Windows and cause a spurious changed-during-inspection failure. The collapsed section below shows a one-file workaround.

Regenerating diagrams on Windows

Node reports dev = 0 from lstat/stat but dev = <volume serial> from fstat on Windows. Archify treats that difference as a file-identity change, so every publish fails. A preload that normalizes only the fstat device ID to 0 restores the tool without weakening its integrity checks — ino, nlink, mode, size, mtime, ctime and the content digest are all still verified.

// win32-dev-normalize.mjs  (keep this outside the repository)
import fs from 'node:fs';

const normalizeDev = (s) => {
  if (!s || typeof s !== 'object' || typeof s.dev !== 'bigint' || s.dev === 0n) return s;
  try { Object.defineProperty(s, 'dev', { value: 0n, enumerable: true, configurable: true }); } catch { /* frozen */ }
  return s;
};

const fstatSync = fs.fstatSync;
fs.fstatSync = (file, options) => normalizeDev(fstatSync.call(fs, file, options));

const fstat = fs.fstat;
fs.fstat = (file, options, callback) => {
  if (typeof options === 'function') return fstat.call(fs, file, (e, s) => options(e, e ? s : normalizeDev(s)));
  return fstat.call(fs, file, options, (e, s) => callback(e, e ? s : normalizeDev(s)));
};

$env:NODE_OPTIONS = "--import file:///C:/path/to/win32-dev-normalize.mjs"
node <archify>/bin/archify.mjs finalize architecture <candidate.json> docs/architecture.html --quality showcase --json

---

🤝 Contributing

1. Never compute a number with a model. New financial logic belongs in internal/financial/ using decimal.Decimal, with a test asserting the exact value to the cent.
2. Keep extraction honest. StructuredExtractor copies verbatim values. If a field cannot be found, return None — do not infer it.
3. Add an audit rule, not a silent correction. New reconciliations belong in CrossReconciliationAuditor so the discrepancy is reported.
4. Exercise a node in isolation — every node is a single (UnderwritingState) -> dict function, so it should be callable with a hand-built state before it is wired into the graph.
5. Run pytest -v and ruff check . before opening a PR.

---

Built with pdfplumber, LangGraph, Pydantic and decimal — because the numbers in a term sheet should be reproducible.