Field notes01 of 04
Public records, loaded every night
A nightly pipeline that keeps 35.7 million county filings and four appraisal rolls current in one database, and scores every property for distress.
- System
- Nightly public-records pipeline
- Industry
- Real Estate & Investment
- Solutions
- Stack
- Python · PostgreSQL 17 · Docker · SFTP · systemd timers
Figures show illustrative sample data. Names, companies and values in them are invented.
01
The situation
The client sources commercial property deals from public data. The signals are on file with the county: notices of foreclosure sale, liens, deed transfers, and the appraisal rolls that say who owns what. They arrive as nightly SFTP drops and yearly bulk exports, in formats built for archives, not for a CRM.
Before this work each county lived in its own SQLite file. The yearly rebuild deleted and recreated the file, taking every derived table with it, and the nightly clerk sync was scheduled from a laptop.
02
What we built
Component 1: Nightly clerk sync
At 19:30 Central a scheduled job downloads the day's filings over SFTP, loads them into PostgreSQL, writes a heartbeat and logs the run. An hour later a second job refreshes the roster of foreclosure trustees. Both run as systemd timers pinned to Central time, and a run missed while the server was down catches up when it comes back.
Component 2: One database, four counties
Appraisal rolls from four counties in three states sit in one PostgreSQL database, a schema per county. Jobs write through one role; analysis and dashboards read through a read-only one.
Component 3: Loads proved before they publish
A declarative loader stages each raw file, proves the staged tables against what is live, and swaps them in one transaction. Derived tables are rebuilt afterwards by their own scripts, so a new roll never wipes them.
Component 4: Distress scoring, 0 to 100
Every property gets a score with its components shown: open pre-foreclosure notices, completed foreclosures, tax liens and other filings, ordered by how close the property is to an irreversible sale. Precision gates hold back affiliate-owned properties and stale records.
Component 5: Tested like production code
The nightly sync has 97 checks that run against throwaway schemas in a separate test database, never the live one.
03Stack
- Python
- PostgreSQL 17
- Docker
- SFTP
- systemd timers
04
Results
35.7 million clerk filings indexed, from December 1960 to the previous day.
More than 3.8 million appraisal records from four counties in one database.
New filings loaded every evening at 19:30 Central; the trustee roster refreshed at 20:30.
The move from SQLite to PostgreSQL verified by three nights of parity checks before the old sync was switched off.
Every property scored for distress from 0 to 100, with the components behind each score.
More field notes
Your system could be the next note.
Bring the part of your business the CRM can't see yet. We'll show you how we build it.