Gather, augment, integrate: a technical vision for public-records data

Problem

Public-records products die in one of two traps: either every new feed becomes a one-off spaghetti path into the UI, or the platform is so rigid that onboarding a weird PDF portal takes a quarter. You need maximum technical flexibility at the edges (sources and extraction tools) and a stable contract at the center (one place buyers and APIs can trust)—including a clear answer to when a scraped event becomes a valid property signal.

Options considered

Source-shaped product — each portal or file type gets its own tables, screens, and export. Flexible for the first three sources; unusable at thirty.
Premature universal schema — force every feed into one model on day one. Looks clean; blocks real-world messiness and slows onboarding.
Staged pipeline with a serving table — gather anything, augment by extracting and linking each signal to a real property (which qualifies it), then integrate into an entity-centric flat dataset that overlays signals from many contexts.

Decision

We build around four layers:

Gather — small, fast-to-implement collectors that tolerate heterogeneous sources and structures.
Augment — a tool-agnostic extraction and enrichment stage (custom code, rules, OCR such as Tesseract, local LLMs via an Ollama-compatible client, third-party parsers, even UI agents) that turns raw artifacts into fields and links each signal to a property. A concrete path for court dockets: OCR attachment PDFs → schema-constrained LLM extraction (parcel / legal / parties / amounts) → property locators → property↔signal link. That link is the qualification gate: it is how a candidate event is validated and qualified as a real-property signal—matched to a known parcel / entity type the product can sell. Unlinked extraction stays provisional and unqualified; only property-linked signals are product-eligible.
Integrate — a medallion-style dbt path on PostgreSQL (bronze → silver → gold, plus a separate gold_ml schema for features/scores) that lands in a One Big Table (OBT)-style gold dataset. In plain terms, an OBT here is one wide, denormalized, entity-centric serving table at property grain (parcel × county × state), amplified with previously extracted, property-linked signals from many contexts. Silver holds the property spine and signal work; gold is what the marketplace API binds to—not a place that re-joins five signal tables per request.
Serve — human UI today; API as a first-class consumer of the same gold contract (not a second warehouse).

That OBT / gold serving table is not “dump everything in a lake.” It is one wide, denormalized serving model where overlays and aggregations are explicit columns (or attached signal blocks) on a single grain.

Important nuance on grain and cardinality:

What OBT is not: it does not mean “one row forever equals one parcel no matter what.”
Multi-signal reality: if the business allows multiple signals per entity, grain must reflect that somewhere (entity×signal rows, or a signals satellite)—otherwise MERGE-style incrementals fail for the wrong reason. Wide denormalization and correct cardinality are separate decisions; both matter.
Silver vs gold grain: we keep event signals at their own grain in silver (then verify and roll flags onto the property) and serve a property-grain OBT in gold with boolean flags plus selected detail columns.

Gold stays denormalized for serve (search, mask, export in one query). Star-schema joins belong upstream or in silver—not as an API orchestration tax on every download. Small dim_* lookup tables may decorate gold for display labels; they are not a star-schema product API.

How it works

flowchart TB
  src[Any source / structure]
  gather[GATHER<br/>raw warehouse schemas]
  augment[AUGMENT<br/>OCR / local LLM / parsers<br/>property↔signal link]
  integrate[INTEGRATE<br/>dbt on Postgres]

  subgraph silver_work [Silver]
    bronze[bronze: reference dims]
    spine[property spine]
    signals[signal identify → verify]
    flags[signal flags]
    integ_view[integrate view<br/>property flags]
  end

  gold[gold OBT<br/>property marketplace table<br/>+ gold_ml / dims]
  serve[SERVE<br/>UI + API]
  search[optional search<br/>derived read-model]

  src --> gather --> augment --> integrate
  integrate --> bronze
  integrate --> spine
  spine --> signals --> flags --> integ_view --> gold --> serve
  serve -.-> search

Gather optimizes for speed of implementation and source diversity: files, portals, APIs, dumps. Ownership stops at “raw is in the warehouse.” Gather does not decide whether an event is a sellable property signal.

Augment has two jobs that must stay together: (1) pull information out with whatever tool fits, and (2) link that output to a property. The property↔signal link is the qualification gate—address / APN (parcel id) / legal description / case-party resolution (or equivalent) that asserts “this signal is about a real property we recognize.” Unlinked or unresolved extractions stay provisional and unqualified; only property-linked signals are product-eligible. For docket attachments, the stack is often OCR for text, then a local LLM agent for structured fields (Pydantic / JSON-schema constrained outputs, chunked over long PDFs), then deterministic validation (reject hallucinated parcel ids) and locator resolution into the property link. Non-LLM taggers and rules run first when they suffice. The contract is outputs (fields, property-linked signal events, confidence), not a single extraction technology. Swapping Tesseract for another OCR, or swapping the local model behind the same client, does not redesign the gold table. Failing to resolve a property leaves the signal unqualified; it does not silently enter gold.

Integrate (dbt) is where genealogy and standardization live. At a high level:

Bronze — shared reference shapes (county codes, ZIP, street suffixes, …).
Silver property spine — statewide assessor/base integration models (designed for multi-state: per-state integration models union into property silver), with optional fresher county CAMA overlays where direct feeds beat the statewide file.
Silver signals — event families (court, auction, tax, …) typed/identified, then verified against property silver; a property signal-flags model—a compact flag table that packs multiple boolean signal attributes (sometimes called a junk dimension)—computes attribute signals and LEFT JOINs verified events so downstream joins once. Verification confirms an already-linked signal matches a known property; it does not replace the Augment qualification gate.
Gold OBT — denormalized property marketplace table (filters + data + signal flags/details), incremental on an ETL run cursor; gold_ml for training/scoring tables; small dims for labels.

Incrementals use a shared run-id cursor (sys_etl_run_id): prefer strategies that match grain (delete+insert for property-shaped models; merge where a case/event row legitimately updates in place). Schema changes are deliberate full-refresh, not silent on_schema_change. Application migrations (Alembic) stay out of this path.

Nuance — silver can identify signals too: once gather/augment have landed standardized rows, some product signals are cheapest as silver transforms. Two common shapes: (1) computed flags on the property grain from clean attributes (no Augment step—the property already is the entity, so the signal is already on a known property), and (2) rollup of verified, already property-linked event signals into one property-level flag/detail table so gold joins once. Augment still owns messy extraction and the property↔signal qualification gate on heterogeneous artifacts; silver finishes typing, verifies links against property silver, and materializes the property-centric signal surface. Do not force every rule into Augment when the warehouse already holds typed columns.

A pattern carried from prior platforms: many source-shaped tables feed a unified analytics / serving table that keeps source lineage / run ids so you can reconcile counts across staging and product layers without guessing. That is OBT with honesty about provenance.

Serve reads the same integrated contract through UI and API—typically the gold property table as the marketplace datasource (with field categories such as identifier / filter / data / metadata). Search indexes, if used, are only a derived read model built from gold-shaped data for discovery latency and relevance. The gold OBT remains the single source of truth for the marketplace contract (filters, masking, export)—never a second system of record.

Why this maximizes flexibility

Any source, any structure — gatherers absorb heterogeneity; the product schema does not.
Qualification gate in Augment — the property↔signal link qualifies the signal; unlinked extractions stay unqualified. Silver may verify and roll up already-linked signals; it does not move the gate.
Easier evolution of processing — standardization, lineage, and observability attach to stages and run IDs, not to one mega-script.
Signal combination — overlay and aggregate already-qualified signals from unrelated contexts onto one entity without forking the UI per source.
Any client consumption — UI, API, exports, and future partners all bind to gold/OBT (and documented field contracts), not to gatherer-private tables.
Tool independence at Augment — choose the cheapest reliable extractor per artifact type (rules, OCR, local LLM, agents); keep outputs fungible as long as the property link is produced.
Ops clarity — batch schedules and fail-fast jobs map to stages; a broken OCR/LLM path does not require rewriting the marketplace; an unresolved property link fails closed (unqualified), not into the marketplace.
Cross-layer sanity checks — same grain + lineage/run ids lets you compare “raw landed” vs “qualified/linked” vs “gold countable” vs “search indexed” without opening every source UI.
Medallion + OBT together — bronze/silver keep transforms testable; gold OBT is the product contract (plus gold_ml for model features, not a second marketplace system of record).

Hard parts

Grain discipline — OBT only works if everyone agrees what one row means (e.g. one property, one case-property link). Wrong grain creates fake “flat” chaos.
Link quality — property↔signal resolution in Augment (address, APN, legal description, case-party; often via OCR + local LLM locators) and verification in silver against known properties: ambiguous addresses, messy or partial legals, multi-parcel cases, weak identifiers, and model hallucinations create false positives or leave valid signals unqualified.
Local model ops — self-hosted LLM inference is a batch dependency (timeouts, GPU freezes, retries); treat it like any other fail-fast stage, not like the marketplace API.
Signal collisions — multiple contexts can contradict; overlays need precedence, timestamps, or explicit multi-signal shapes—not silent last-write-wins.
Temptation to short-circuit — writing gatherer output (or unlinked extraction) straight into the UI skips both lineage and the qualification gate, and breaks the “one contract” promise.
Augment sprawl — every new tool must still emit into known interfaces including a property link (or explicit unlink/unqualified state), or the integration layer becomes a junk drawer.
Batch vs serve coupling — prior systems needed explicit locks / pauses when rebuilding analytics tables; we keep rebuilds off the request path and schedule them as batch jobs for the same reason.

What you’d change

As API consumers grow, we would publish the gold/OBT field contract more formally (versioned, documented)—including what “property-linked / qualified signal” means—while keeping gather and extraction tooling deliberately messy at the edges. If signal graphs densify beyond wide columns, we might add normalized signal satellites—still projected into the serving table for product paths that need flatness. Multi-state expansion should keep the same medallion names and push differences into sources / per-state integration models / macros—not a second warehouse philosophy.