Real Estate Enrichment Automation Framework

Weekly probate filings land in Drive; what comes out the other end is a HubSpot CRM where dead leads are archived, live leads enriched from public records, and every record carries a routable contact channel.

Overview

This repository is the enrichment pipeline (Claude Cowork + GCP Cloud Run). The Rapid Upload - File Drop - Ingest (Google Apps Script) lives in its own repository, michaelbardy/RapidUpload. See the connector's README and DEPLOY.md for its setup.

Branches

Orchestrators — Claude CoWork vs GCP Cloud Run ADK

Both branches run the identical pipeline — the same parsers, schema, indications, round trips and adversarial-monitor lane. What differs is how a run starts and who is at the keyboard. In short: GCP Cloud Run ADK is no-touch, scheduled batch processing; Claude CoWork is on-demand, chat-enabled — or scheduled — orchestration.

The Two Layers

Two deployments that meet at three well-defined contracts and nowhere else. Each stands alone — the connector runs with no enrichment layer; the pipeline runs against any HubSpot portal populated by any means.

weekly .xlsx dropped in File Drop
   │
   ├─ RAPID UPLOAD - FILE DROP - INGEST ────────────────────
   │    header contract checked · rows validated · id minted (UID on new rows)
   │    appended to Warren Group Master · batch upserted to HubSpot
   │
   └─ ENRICHMENT PIPELINE ──────────────────────────────────
        S0  reconcile ── adopt lead_uid (sequential or UID), drop duplicates, snapshot
        A1  listing ──── Firecrawl /search: Zillow → Realtor.com → Redfin
        A2  auditor ──── Firecrawl /interact, parcel first, family name second
        B   recorder ─── Firecrawl /interact, recorded/released liens and TOD
                         (weekly default OFF — stage_b_engaged)
        C   probate ──── Firecrawl /interact then /extract (parties, costs, docket);
                         writes indication flags and lead status on the same upsert
        A2  on Open Deal  auditor + copy Contact → Deal
             │
             └─ Stage D contact enrichment · Stage E DNC review (Drive, not enrichment)
             └─ Friday 7pm ET: --mode update (A1 listing + C docket)
             └─ Daily 3am ET: new leads A1 then C

The master sheet is the ledger, not a staging area — rows land there before HubSpot is touched, making every load failure recoverable. Stage A1 is the cheapest filter and runs first; everything after sees only survivors.

Operating Modes

Stage Engagement Toggles

New-lead jobs run A1 then C. Stage B (recorder) is dormant on schedules. The A1 value gate is a number on the Stage Engagement card. Catch-up jobs ignore the B/C toggles and always run the named stage.

Card: uid=rapidupload-stage-engagement · title Stage Engagement · location crm.record.sidebar · objects Contacts and Deals. Pin the same card on the control contact lead_uid=PIPELINE_SETTINGS.

Resolution order — first hit wins

1. Env vars: RAPIDUPLOAD_STAGE_B_ENGAGED / RAPIDUPLOAD_STAGE_C_ENGAGED / RAPIDUPLOAD_MINIMUM_PROPERTY_VALUE
2. HubSpot card on lead_uid=PIPELINE_SETTINGS
3. config/stage_engagement.json
4. [engagement] / [tokens] in config/pipeline.toml

python3 scripts/create_hubspot_properties.py --object-type both --apply

Adversarial Dockets — Soft Hold

A will contest, exceptions, land sale or removal of fiduciary used to archive the record. They now stay live:

• engagement_lane=EMAIL_MONITOR + hs_lead_status=FUTURE_OPPORTUNITY while contested — email campaigns only, off the lead-review queue
• Listing status and the probate docket keep being re-checked (--mode update)
• When the docket returns to an estate case: engagement_lane=LEAD_REVIEW + hs_lead_status=PRIORITY_PROSPECT
• Closed listings hard-archive (archive_reason=SOLD, hs_lead_status=ARCHIVE_SOLD)
• Legacy archive_reason=ADVERSARIAL_ESTATE rows remapped to NOT_ARCHIVED on write

Where the Two Layers Meet

Quick Start

pip install -r requirements-dev.txt
python3 -m pytest                      # no network, no credentials
python3 scripts/validate_schema.py     # schema, agents, counties and code agree
python3 scripts/create_hubspot_properties.py   # dry-run HubSpot property plan
python3 scripts/run_enrichment.py --mode weekly --dry-run
python3 scripts/run_enrichment.py --mode catchup --stages A1 --limit 10 --oldest --dry-run
python3 scripts/run_enrichment.py --mode catchup --stages A2 --oldest --dry-run
python3 scripts/run_enrichment.py --mode catchup --stages B --oldest --dry-run
python3 scripts/run_enrichment.py --mode catchup --stages C --oldest --dry-run
python3 scripts/run_enrichment.py --mode catchup --stages C --county Medina --limit 10 --oldest --dry-run
python3 scripts/run_enrichment.py --mode update --dry-run

Repo Layout

Canonical routing field: engagement_lane. Indication flags and hs_lead_status write during Stage C. Filter lists on engagement_lane.

Scheduled Jobs

Timezone: America/New_York. ./deploy/setup.sh image rebuilds the image and upserts every job. ./deploy/setup.sh schedules creates/updates Scheduler. Turn off Apps Script ENRICHMENT_TRIGGER_URL — 3am Scheduler owns new leads.

Catch-up Jobs (Manual)

gcloud run jobs execute rapidupload-enrich-catchup-a1-trial \
  --region="$REGION" --project="$PROJECT_ID" --async

gcloud run jobs execute rapidupload-enrich-catchup-a1 \
  --region="$REGION" --project="$PROJECT_ID" --async

Common manual commands

# Manual Friday update
gcloud run jobs execute rapidupload-enrich-update --region="$REGION" --wait

# LOAD re-score after a CRM-logic change
gcloud run jobs execute rapidupload-enrich-load --region="$REGION" --async
python3 scripts/run_enrichment.py --mode catchup --stages LOAD --apply

# Prove file-drop does not trigger enrichment
gcloud storage cp dnc-return.csv "gs://${BUCKET}/file_drop/"
gcloud run jobs execute rapidupload-file-drop --region="$REGION" --wait

Catch-up Selection Criteria

Failed /scrape, /interact, /extract dump under gs://$BUCKET/failures/.

Cache (A1 URL + C docket pages)

First catch-up writes property_listing_url and probate case page URLs. --mode update reads those cached URLs first. A miss falls through to a live Firecrawl call.

Upsert / Export Jobs (keep)

Retired (deleted on next setup.sh jobs): enrich-weekly, enrich-daily, enrich-watch, combined enrich-catchup.

Operator reference for HubSpot property setup, schema regeneration, and the two enrichment branches. Catch-up / cache / schedule runbook is in the Ops Guide above.

Branches

Parsers, schema, indications and the HubSpot write path are identical on both live branches. Only the layer that calls them differs. The Rapid Upload - File Drop - Ingest lives in michaelbardy/RapidUpload.

Code 1 (Cloud Run exit 1) — First Live Run

HubSpot 400 on the first enrichment upsert. Complete these in order. Do not start catch-up until step 4 is green.

1. Pull this commit on both enrichment branches. S0 keeps sequential lead_uid; docket writes schema codes; A1 never writes CACHED.
2. Dry-run the property create (no portal writes):

   export HUBSPOT_PRIVATE_APP_TOKEN=...   # contacts + schemas read/write
   python3 scripts/create_hubspot_properties.py

   Expect ~118 create, 5 modify (hs_lead_status, enrichment_stage, postal/zip/bar).
3. Apply to the portal:

   python3 scripts/create_hubspot_properties.py --apply

   Creates missing A1/DNC/listing fields. Merges PENDING next to live Pending on enrichment_stage. Sets unique on lead_uid only.
4. Confirm in HubSpot — Settings → Properties → Contact:
   • property_listing_status exists (dropdown)
   • enrichment_stage has both Pending and PENDING as values
   • lead_uid → Require unique values is ON; lead_id unique stays off
5. Dry-run catch-up trial, then apply — one stage at a time:

   python3 scripts/run_enrichment.py --mode catchup --stages A1 --limit 10 --oldest --dry-run
   python3 scripts/run_enrichment.py --mode catchup --stages A1 --limit 10 --oldest --apply
   python3 scripts/run_enrichment.py --mode catchup --stages A2 --oldest --apply
   python3 scripts/run_enrichment.py --mode catchup --stages B --oldest --apply
   python3 scripts/run_enrichment.py --mode catchup --stages C --oldest --apply

   GCP: gcloud run jobs execute rapidupload-enrich-catchup-a1 --region="$REGION" --wait — only after the image includes this commit. Stage D/E are not catch-up.

HubSpot Properties

scripts/create_hubspot_properties.py is the only automated writer of RapidUpload contact properties. It is on both enrichment branches. Dry run by default; nothing hits the portal until --apply.

export HUBSPOT_PRIVATE_APP_TOKEN=...   # crm.objects.contacts.read/write
                                       # crm.schemas.contacts.read/write
python3 scripts/create_hubspot_properties.py             # plan (~118 create, 5 modify)
python3 scripts/create_hubspot_properties.py --apply     # write

What --apply does

1. Creates missing property groups
2. Creates every schema row with status NEW (409 = already exists → skip)
3. For an existing enumeration, PATCHes options and merges live portal values so ingest labels (Pending) and Python codes (PENDING) both write
4. PATCHes enrichment_stage and hs_lead_status option sets (status MODIFY)
5. Repairs hasUniqueValue on lead_uid

What it does not do (portal only)

• Change a property's type (number → text)
• Guarantee the unique-values flag if HubSpot rejects the PATCH
• Build saved views or lists
• Deploy Apps Script — paste .gs files from michaelbardy/RapidUpload manually

Schema Source of Truth

docs/reference/hubspot-field-schema-gap-analysis.xlsx
        │  python3 scripts/generate_schema_from_workbook.py
        ▼
config/schema/hubspot_properties.json     ← never hand-edit
config/schema/inbound_column_map.json
        │  python3 scripts/validate_schema.py
        │  python3 scripts/export_hubspot_field_catalog.py
        ▼
docs/reference/hubspot-field-catalog.xlsx  ← marketing / onboarding download

Edit the New Fields Required sheet in the workbook, regenerate, validate, export the catalog, and commit workbook + JSON + catalog together.

Portal Exceptions (Do by Hand)

HubSpot: Settings → Data Management → Properties → Contact properties.

Do not write these values (400 on the live portal)

Pipeline remaps: adversarial → NOT_ARCHIVED + EMAIL_MONITOR; closed listing → SOLD / ARCHIVE_SOLD; high-probability / resolved adversarial → LEAD_REVIEW + PRIORITY_PROSPECT.

Field Catalog & GCP Deploy

docs/reference/hubspot-field-catalog.xlsx — Cover, Field Catalog (130 contact properties), Dropdowns, Portal Exceptions.

python3 scripts/export_hubspot_field_catalog.py

For GCP step-by-step deployment: docs/gcp-deployment.md on the GCP branch. Cloud Run jobs and schedules are in the Ops Guide above.