Richy Koshy

Final DocsTreat an investor as data, not code.

Investors that buy PRMI’s loans need their final documents; First American ships them. Each investor reports exceptions its own way, and Final Docs reconciles them against First American’s records to show what is missing, where. I owned the architecture and build.

Role
Architecture and build, end to end
Team
With PRMI’s closing, compliance and development teams
Impact
4 investors in one month; onboarding cut from 2-4 weeks to days
Stack
  • Python
  • Pandas
  • SQLAlchemy
  • YAML config
  • OpenPyXL
  • Encompass API
  • Strategy pattern
Problem
Every new investor meant bespoke code, tests, a test environment in the loan system, UAT and a release: two sprints before its first report. Growth of the investor network was capped by engineering capacity, not by the business.
Approach

One reconciliation engine, with each investor described in YAML: where its report lives, how its columns map to a shared schema, and whether loan numbers need recovering.

The few investors that need custom logic name a registered handler instead of adding a branch.

All investors run through the same pipeline into one table, so several can be onboarded and tested together.

Outcome

Onboarding a new investor fell from 2 to 4 weeks to days, about 75% less.

Four new investors were integrated in the month after the refactor, work that previously took a full quarter of engineering capacity, and two other automation initiatives adopted the design as their template.

Next
Batch the loan-number lookups instead of calling the loan system once per row, and report a record with neither a loan number nor a document type once, not twice.
Prepare: configuration-driven Reconcile: PRMI Loan Number as the key config 1 per investor resolve persist custodian data YAML configuration one file per investor Investor batch runner runs the requested investors together One run per investor same steps for every investor Investor reports CSV, Excel, format varies Per-investor pipeline load → normalize columns → resolve PRMI Loan Number → assign + standardize document types → persist Encompass loan identity lookup Database common Final Docs schema ← the processing boundary First American data document custodian records Reconciliation merge on PRMI Loan Number rerun-safe, nothing silently dropped Missing / Defect report Excel sheet + database table Mismatch report no loan number, not at First American, duplicates Prepare: configuration-driven config 1 per investor resolve persist YAML configuration one file per investor Investor batch runner runs the requested investors together One run per investor same steps for every investor Investor reports CSV, Excel, format varies Encompass loan identity lookup Per-investor pipeline load → normalize columns → resolve PRMI Loan Number → assign + standardize document types → persist Database common Final Docs schema the processing boundary Reconcile: PRMI Loan Number custodian data First American data custodian records Reconciliation merge on PRMI Loan Number rerun-safe, nothing silently dropped Missing / Defect report Excel sheet + database table Mismatch report no loan number, not at First American, duplicates
Configuration decides how each investor is processed; the database is the boundary between preparation and reconciliation.

Same business concepts, a different format from every investor

After a mortgage loan closes, PRMI must deliver post-closing document packages to the investor banks that purchased the loans. Trailing documents arrive late, and delays put funding lines and regulatory standing at risk.

Every investor reports final-document exceptions differently:

  • different source files and report locations
  • different column names for the same business field
  • different ways of identifying loans
  • investor-specific document-type vocabulary
  • in some cases data that has to be generated from another system rather than read from a file at all.

The downstream business process needs exactly one consistent representation.

The original implementation absorbed those differences as branching logic inside a monolith, so every new investor meant 2-4 weeks (two sprints) of bespoke coding and QA.

Growth of the investor network was capped by engineering capacity, not by business appetite.

So there were two problems to solve, not one: convert heterogeneous investor data into a unified internal representation, and reconcile it against First American custodian data to identify missing documents and discrepancies.

What used to be code became configuration

Each investor is described by one YAML file:

  • source directory and report name
  • a column map that translates the investor’s column names into the common schema
  • whether loan-number resolution is required
  • document-type mappings
  • where needed, the name of a custom processing hook.

The processor reverses the configured mapping, renames the incoming DataFrame into the standardized field names, and adds any missing columns as nulls, so the output structure is identical regardless of the input shape.

The core modules treat an investor as data, not as hard-wired branches.

  1. Bots fetch each investor’s exception report into a dated folder. A few investors need a custom handler instead, such as a second-liens list built from the loan system.
  2. Map the investor’s columns onto one shared schema.
  3. Recover PRMI’s loan number from the loan system when the investor gives only its own.
  4. Read the document type out of free-text comments.
  5. Standardize document names, so a dozen spellings become one.
  6. Store the day’s investor records: the line between preparing and reconciling.
  7. Bring in First American’s imaged-not-shipped list, falling back to the previous Monday’s.
  8. Keep the earliest copy of each record, so reruns are safe.
  9. Match on PRMI loan number and sort every record:
    • Missing/Defect: the action list.
    • Mismatch, with a reason: no loan number, not at First American with no document type, or a duplicate.
  10. Publish an Excel feedback report with a summary sheet, and keep the history in the database.
From investor report to action list. Steps 2 to 5 follow each investor’s configuration; step 6 is the line between preparing and reconciling.

Custom paths without touching the core

Not every investor can be processed by reading a file. Some need custom data generation: one path builds a second-liens dataset by querying the loan origination system (Encompass) directly and filtering loans on their milestone history.

Those paths live in a dispatch table: the configuration names a handler, and the processor looks it up and calls it.

The alternative is the familiar failure mode: if investor A, elif investor B, and a central workflow that grows a branch per counterparty.

The registry keeps the central processor stable; adding a custom path means registering a function and naming it in configuration, never editing the dispatch core. Open for extension, closed for modification.

Two normalization problems, solved before reconciliation

  • Loan identity. Some investors report only their own loan number. A resolver queries Encompass with the investor loan number plus the borrower name and recovers the PRMI Loan Number.

    Ambiguous or empty results are marked unresolved rather than guessed, and surface later in the Mismatch report instead of disappearing.

  • Document vocabulary. Free-text comments are inspected for known keywords to infer a standardized document type.

    Existing document-type values are normalized through a second mapping, so a dozen spellings of a title policy all become one standard name.

    Reconciliation then operates on standardized business terminology, not investor-specific variations.

Every record lands in a category

The consolidated investor data is compared with First American custodian data, joined on the PRMI Loan Number. Findings separate into business categories, and nothing is silently dropped:

  • Missing/Defect, the action list: every record that matches First American’s data, plus records First American doesn’t hold that still carry a document type.
  • Mismatch, each with its reason: no PRMI Loan Number, not at First American and no document type, or a duplicate loan-and-document-type combination.
  • Rerun-safe: same-day duplicates from re-runs are collapsed before reconciliation, earliest record wins, so reprocessing is idempotent.
  • Missing input file: logged and a notification goes out, instead of the run silently proceeding as if the file existed.
  • Custodian data missing for the day: the workflow falls back to the most recent weekly snapshot rather than failing the run.
  • Database failures: logged and re-raised. A write that fails is a run that fails loudly.

The output is:

  • an Excel workbook with a Missing/Defect sheet, a Mismatch sheet and the consolidated investor report, plus a summary sheet
  • the same findings persisted to the database so downstream systems can consume the results without touching the spreadsheet.

Prepare, persist, reconcile

The system separates four responsibilities:

  • Batch orchestration decides which investors to process.
  • Per-investor processing decides how one investor’s data becomes the common schema.
  • Reconciliation decides what is missing or inconsistent.
  • Reporting decides what the business sees.

An investor batch runner loads the YAML configuration and starts one run per requested investor, so several investors can be processed and tested together. Every run goes through the same pipeline:

  1. Load
  2. Normalize columns
  3. Resolve the PRMI Loan Number where required
  4. Assign and standardize document types
  5. Persist

The consolidated data lands in the database in the common Final Docs schema, and reconciliation compares it with First American data on the PRMI Loan Number.

Investor expansion at deal speed

  • Days to onboard a new investor, down from 2-4 weeks (about 75% less): a YAML change, not a code release.
  • 4 new investors integrated in the month after the refactor.
  • 2 other automation initiatives adopted the architecture as their template.

Onboarding a new investor fell from 2-4 weeks of bespoke coding to a configuration exercise measured in days: a new YAML file, a few parameter tweaks, and a handler only if the investor needs something genuinely new.

In the month after the refactor, completed in Q1 2025, four new investors were integrated, work that previously consumed a full quarter of engineering capacity, and the architecture was adopted as the template for two other automation initiatives.

“PRMI now treats investor expansion as a business decision, not a dev-capacity constraint.”

Corban Wells, VP of Technology Product Management, PRMI
Read Corban Wells’s full letter

What I’d do next: faster lookups, cleaner exceptions

  • Batch the loan-number lookups. Recovery calls the loan system once per row, one at a time; batching them, or running them concurrently, would cut run time on large reports.
  • Report every unresolved record once. In the version I shipped, a record with neither a loan number nor a document type lands in the Mismatch report twice.