Market Data Migration

Move the Delta county brain — parcel spine, 34 layers, 3.1M facts — onto Flue/Turso by importing what we already scraped, then keep it fresh with Worker-native scrapers. Validated build spec · 2026-06-24

In one breath: We don't re-scrape — we import. The old delta-market cell's populated brain is alive on its VM (822 MB · 18,970 parcels · 3,114,247 facts across all 34 layers) and we have admin on the target Turso DB, so the initial fill is a bulk copy, not a 10-hour backfill. The one thing the old DB doesn't hold is embeddings (the vector table is empty) — so those we generate fresh via Gemini (key found, 768-dim, round-trip proven in Turso). The scrapers then become the keep-fresh layer, not the initial-fill layer: we live-validated 33 of 34 sources today (only Tyler-recorder deeds is captcha-gated — parked to the end of the roadmap), including the Google-Maps key that was hiding in the cell's own .env. This spec is the build: Phase 0 import the brain + generate embeddings, Phase 1 stand up the keep-fresh scrapers behind a chunked beat, Phase 2 author the deep Brief + comps.

1 · What we validated 2026-06-24, live

Before writing a line of build, we proved every load-bearing assumption against live systems. This is the de-risk.

1.1 · The source data exists and is reachable

The brain is at egg-c5e25a:/root/skills/home0/delta-co-market.db (reached via well exec). Counts: 18,970 parcels · 3,114,247 parcel_facts · 482 parcel_listings · 387 pending_resolution · 0 embeddings (the vec0 table was scaffolded but never populated). Top layers by fact volume: tax_parcels 531k, poi 449k, epa 405k, soils 239k, climate 215k, zillow 210k, groundwater 170k, cdl 155k, frost 151k, ndvi 131k, mineral 101k, wildlife 62k, landcover 56k, sales 52k, fire/drought 37k each, zoning 24k, assessor 22k, distress 16k, water_rights 7.7k, tax_history 6.7k.

1.2 · The target Turso DB is confirmed, with admin

The live Flue market store is the Turso DB homezero-market (libsql://homezero-market-peteknowsai…) — it already holds listings (170), ingest_runs, market_reports. We have turso-CLI admin (account peteknowsai), so we load straight in — no Worker-secret juggling.

1.3 · The scrapers still work (keep-fresh layer)

1.4 · The proof — a real slice, end to end

2 · Architecture

2.1 · Source → target

2.2 · Target schema (added alongside the existing listings)

CREATE TABLE IF NOT EXISTS parcels (
  parcel_id TEXT PRIMARY KEY, geometry TEXT,            -- GeoJSON polygon
  centroid_lat REAL, centroid_lon REAL,
  bbox_min_lat REAL, bbox_min_lon REAL, bbox_max_lat REAL, bbox_max_lon REAL,
  acres REAL, updated_at INTEGER );
CREATE INDEX IF NOT EXISTS parcels_bbox ON parcels(bbox_min_lat,bbox_max_lat,bbox_min_lon,bbox_max_lon);

CREATE TABLE IF NOT EXISTS parcel_facts (
  parcel_id TEXT NOT NULL, source TEXT NOT NULL, fact_key TEXT NOT NULL,
  fact_value TEXT, observed_at INTEGER NOT NULL,
  PRIMARY KEY (parcel_id, source, fact_key, observed_at) );
CREATE INDEX IF NOT EXISTS facts_by_parcel ON parcel_facts(parcel_id);
CREATE INDEX IF NOT EXISTS facts_by_source ON parcel_facts(source);

CREATE TABLE IF NOT EXISTS parcel_listings (
  parcel_id TEXT, source TEXT, source_id TEXT, lifecycle TEXT,
  first_seen INTEGER, last_seen INTEGER, last_active_price INTEGER,
  resolution TEXT, confidence REAL, PRIMARY KEY (source, source_id) );

CREATE TABLE IF NOT EXISTS pending_resolution (
  id INTEGER PRIMARY KEY AUTOINCREMENT, kind TEXT, payload TEXT, reason TEXT, created_at INTEGER );

CREATE TABLE IF NOT EXISTS parcel_embeddings (
  parcel_id TEXT, kind TEXT, embedding F32_BLOB(768), embedded_at INTEGER,
  PRIMARY KEY (parcel_id, kind) );
CREATE INDEX IF NOT EXISTS parcel_emb_idx ON parcel_embeddings(libsql_vector_idx(embedding));

-- ingest_runs already exists in homezero-market; extend it with a resume cursor:
ALTER TABLE ingest_runs ADD COLUMN cursor TEXT;     -- (no-op-guard in code if already present)

2.3 · Secrets to set on the homezero-flue Worker

• GOOGLE_MAPS_API_KEY — lifted from egg-c5e25a:/root/skills/home0/.env (validated). Unlocks elevation / solar / roads / address-validation / aerial / imagery.
• GEMINI_API_KEY — already in ~/.cells/secrets.json, validated 768-dim; set as Worker secret for the embedding-generation beat.
• Existing: TURSO_DATABASE_URL / TURSO_AUTH_TOKEN (point at homezero-market), BRIGHTDATA_API_TOKEN (Zillow), CONVEX_*, AUTH_PASSWORD, ANTHROPIC_API_KEY.

All secrets piped in via wrangler secret put without ever echoing the value (the Slack-secret pattern).

2.4 · The chunked study beat (keep-fresh runtime)

A Durable-Object alarm gets one Worker invocation's budget, so refresh work is chunked and cursor-resumable. study picks the most demand-relevant stale source, processes one bounded batch, records progress in ingest_runs.cursor, and re-arms until current.

async study(){
  const job = await pickSource();              // demand-weighted: listed-parcel sources first
  if (!job) return;                            // nothing stale → idle
  const { facts, pending, nextCursor } =
        await job.adapter.ingest(manifest, { cursor: job.cursor, batch: BATCH });
  await writeFacts(facts); await recordPending(pending);
  if (nextCursor){ await saveCursor(job.source, nextCursor); this.scheduleOnce(POLITE_GAP,'study'); }
  else await finishRun(job.source);
}

Two modes per source: daily-incremental (listed parcels only — assessor/sales/reconcile/distress, cheap, in the sweep tail) and periodic refresh (the static layers, rarely). The county-wide backfill mode that the old VM ran for hours is not needed — Phase 0 imports it.

Phase 0 — migrate the data, generate the embeddings

This is the move that makes the whole thing cheap. Nothing here scrapes; it copies what we already have and mints the one thing we don't (vectors).

1. Add the schema to the Flue store
   Extend ensureSchema() in flue/src/market/store.ts with the §2.2 tables + the ingest_runs.cursor guard. Test: applies idempotently against a :memory: client and against homezero-market (additive — does not touch listings).
2. Pull the populated DB off the VM
   well exec -s egg-c5e25a -- sh -c 'gzip -c /root/skills/home0/delta-co-market.db' > delta.db.gz → gunzip locally (822 MB → ~a few hundred MB gz). Verify counts match (18,970 / 3,114,247).
3. Transform → load into a staging Turso DB
   Dump parcels / parcel_facts / parcel_listings / pending_resolution from the SQLite file, map columns to §2.2 (rename fact_value_json→fact_value; derive bbox_* from geometry; drop the parcels_rtree/vec0 shadow tables). Bulk-load into a fresh homezero-market-staging via batched @libsql inserts (chunks of ~1–2k rows; 3.1M facts = a one-time run). Test: staging row counts equal source counts; a spot-check parcel's facts match.
4. Verify the staging brain, then promote ✓ done
   Staging built via turso db import (cleaned snapshot; vec0/rtree dropped), counts verified == source. Promoted to live homezero-market via libSQL batch() (additive, in-place, resumable) — prod counts match exact, listings/market_reports untouched. Staging scaffolding destroyed.
5. Embeddings — on-the-fly, not bulk ✓ machinery shipped
   Decision (Pete, 2026-06-25): generate on demand. The agent embeds a property's description via Gemini gemini-embedding-2 @ 768 the moment there's real buyer interest in it — no county-wide pre-compute. The table + ANN index are live and empty; embed.ts (canonical Gemini impl) + upsertParcelEmbedding + vectorSearchParcels are the on-the-fly path, proven end-to-end on real Turso (NL query → Gemini → vector_top_k → ranked parcels). Note the Gemini per-minute rate cap (~1300) if a bulk pass is ever wanted.
6. Resolve listings → parcels
   For the live Zillow listings, resolve zpid→parcel by bbox-prefilter + JS point-in-polygon (port the ≤50 m nearest fallback → pending_resolution). Test: a listing inside a parcel binds; report the resolved/pending split.
7. Retire the delta-market cell
   Once the brain reads from Turso, the egg-c5e25a cell is dead weight — fold its teardown into the cells-retirement plan. Keep one cold archive of delta-co-market.db in R2 first.

Phase 1 — port the 33 live sources behind the study beat

The import gives us history; the scrapers keep it current and cover new listings/parcels. Port the 27 adapters from the cells/market/ blueprint into Flue (Worker-safe fetch, writes via store.ts, invoked by study). Build in value order.

1. The study beat + demand-weighted scheduler
   Wire study (§2.4) into the market agent; scheduleEvery daily + self-continue while a cursor is open. pickSource() ranks listed-parcel sources and advisor-requested sources first. Add the daily-incremental tail to sweep. Test: stale source picked → one batch ingests → cursor advances → re-arms; current sources idle.
2. EagleWeb cluster — assessor, sales, tax_history
   Port the guest-login JSESSIONID session + account.jsp parse (validated working). Account numbers pre-resolved from tax_parcels. Daily-incremental over listed parcels only (keeps datacenter-IP volume low). Test: a fixture account.jsp parses to the expected value/sales/tax facts.
3. Free national bulk — ArcGIS + REST (validated 23 live)
   Port the ArcGIS-query / raster-sample / rest-point adapters (collapse ArcGIS onto a shared helper). Honor the Overpass home0/1.0 UA and PAD-US backoff. Periodic refresh cadence. Test one adapter per fetch-pattern against a fixture.
4. Distress chain — foreclosures, notices, distress, reconcile
   Public Trustee __VIEWSTATE scrape (validated; drop the old TLS-ignore hack), Column notices API, the pure-SQL distress deriver, and reconcile-listings (sold/failed-to-sell lifecycle → feeds distress). Test: a parcel with a lien + estate notice scores the expected distress level.
5. Google Maps layers — now unblocked
   With GOOGLE_MAPS_API_KEY set: elevation, solar, roads, address-validation, aerial, imagery — listings-only, bounded. Address-validation also strengthens listing→parcel resolution. Skip cleanly if the key is ever absent. Test: key-present → elevation fact written for a listed parcel.
6. Per-source freshness in marketHealth
   Every adapter run writes ingest_runs; extend marketHealth to report per-source age so the staff desk + silent-death guard see layer staleness. Test: a stale source surfaces in marketHealth.reasons.

Phase 2 — turn facts into the deep Brief + comps

1. Author the deepDive from the facts
   Replace the hardcoded {status:'not-started'} with a beat that reads a parcel's facts and authors the 34-layer appendix. The drain/briefing flow gains read_parcel_facts so verdicts are grounded in assessed value, recorded sales, distress, water, hazards — not just Zillow. Test: a briefed home publishes a deepDive with layersExamined > 0 and real sections.
2. Comps over the embeddings
   Add semanticSearch over parcel_embeddings (the proven vector_top_k path) for revealed-substitution / comp-set. Test: a query parcel returns nearest neighbors by cosine.
3. Surface depth to the agents & staff
   Staff desk gets a parcel_facts read; the advisor's scout can lens on real signals (water, distress, hazard); the billboard read can cite record truths. Public surfaces stay real-data-only, institutional-names-only. Test: staff read_listing returns joined parcel facts.

3 · The 34 layers — full inventory (validated)

import comes in via Phase 0 (already scraped)   free worker   cookie EagleWeb session   maps key Google (key in hand)   derived   human-fed parked

Net: 33 layers fill from the import + refresh live; only deeds has no automated path and is parked. Every other layer's keep-fresh fetch was validated live on 2026-06-24.

4 · Roadmap order

1. M0 ✓
   Import the brain. DONE (2026-06-25). Snapshot → clean (vec0/rtree dropped) → turso db import staging → verify → additive libSQL batch() into live homezero-market. Embeddings on-the-fly. Listings→parcels links imported (482, already resolved). Outcome: the deep-Brief data is live in Turso. Remaining: retire egg-c5e25a (keep a cold R2 archive of delta-co-market.db first) — don't kill until prod is proven in practice.
2. M1
   Keep-fresh runtime. The study beat + EagleWeb daily-incremental + the free ArcGIS/REST refreshers + distress chain. Outcome: the brain stays current without the VM.
3. M2
   Maps + depth. Set the Maps key; wire elevation/solar/roads/address-validation. Author the deepDive off the facts; comps over embeddings. Outcome: record-deep Briefs + comp sets.
4. M3
   Imagery embeddings (optional). Street-view/satellite multimodal vectors (needs the Maps key + Gemini multimodal). Outcome: visual comps.

Parked — at the very end of the roadmap:

1. P·deeds
   Tyler recorder deeds. reCAPTCHA-gated, blocks any IP — no automated path. Revisit last, as either a staff paste-tool (accept hand-pulled recorder rows) or a captcha-solver integration if it ever earns its keep. The imported deeds facts (currently 0) mean nothing is lost by waiting. Not on the critical path.

5 · Decisions & open items

• ✓ done Prod load complete — additive into live homezero-market (exact counts, live tables untouched).
• Fix on port: the water.ts/waterrights.ts source='water_rights' collision + DELETE-wipe — give well-permits its own source='well_permits'.
• Imagery embeddings: M3 / optional — text comps ship first; visual comps when wanted.
• Set secrets: copy GOOGLE_MAPS_API_KEY (from the VM .env) + GEMINI_API_KEY into the homezero-flue Worker.

Validated 2026-06-24 against live systems: the populated DB on egg-c5e25a, the homezero-market Turso target, 33/34 source endpoints, the Maps key, and a real 150-parcel/3,000-fact load with native-vector comps. Sources: live code audit of flue/src/market/*; the retired cells/delta-market/home0/ CLI; the cells/market/ adapter blueprint. Sibling specs: agent-model-on-flue.html, flue-slice2-market-spec.html, cells-retirement-plan.html. · Validated build spec.