Skip to content

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
Stack
Python · PostgreSQL 17 · Docker · SFTP · systemd timers
FIG. 01NIGHTLY CLERK SYNC19:30 CTSample run log of the nightly county clerk sync: file downloaded, filings loaded, heartbeat written, trustee roster refreshed an hour later.

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.

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

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.