← back to Nationalrealestate
Poway property-history pilot: SanGIS parcels + deed events + public-record links + property-page surfacing
a2c4ea6b39823e42469f63ecc6d841630b30f9df · 2026-07-26 07:28:57 -0700 · Steve Abrams
Files touched
A db/migrations/010_parcel_event.sqlM src/ingest/parcels/engine.tsA src/ingest/parcels/sandiego_ca.tsM src/jobs/hourly_loop.tsM src/server/property.ts
Diff
commit a2c4ea6b39823e42469f63ecc6d841630b30f9df
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sun Jul 26 07:28:57 2026 -0700
Poway property-history pilot: SanGIS parcels + deed events + public-record links + property-page surfacing
---
db/migrations/010_parcel_event.sql | 38 ++++++++
src/ingest/parcels/engine.ts | 1 +
src/ingest/parcels/sandiego_ca.ts | 179 +++++++++++++++++++++++++++++++++++++
src/jobs/hourly_loop.ts | 1 +
src/server/property.ts | 32 +++++--
5 files changed, 243 insertions(+), 8 deletions(-)
diff --git a/db/migrations/010_parcel_event.sql b/db/migrations/010_parcel_event.sql
new file mode 100644
index 0000000..fc250e6
--- /dev/null
+++ b/db/migrations/010_parcel_event.sql
@@ -0,0 +1,38 @@
+-- M-PH1: property historical-event log — the "all historical data" layer.
+-- No BEGIN/COMMIT here — migrate.ts wraps each file in a transaction.
+--
+-- The `parcel` table holds only CURRENT state (one last_sale, current values).
+-- parcel_event is the time-series: every recorded deed/sale, assessment year,
+-- permit, and ownership change for a parcel, each carrying a source_url that
+-- links straight back to the public record it came from. Keyed to a parcel by
+-- (county_fips, source_id=APN) so it joins to `parcel` and the property page.
+
+CREATE TABLE parcel_event (
+ id BIGSERIAL PRIMARY KEY,
+ county_fips TEXT NOT NULL,
+ source_id TEXT NOT NULL, -- APN / BBL / PIN (matches parcel.source_id)
+ event_type TEXT NOT NULL, -- deed | sale | assessment | permit | ownership
+ event_date DATE,
+ amount NUMERIC, -- price / assessed value / permit valuation
+ doc_type TEXT, -- recorder doc type code
+ doc_number TEXT, -- recorder document number (links to ARCC)
+ detail JSONB, -- source-specific extra (buyer/seller, use, etc.)
+ source TEXT NOT NULL, -- ingest source id, e.g. 'sandiego_ca'
+ source_url TEXT, -- PUBLIC-RECORD link back to the property/document
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ UNIQUE (county_fips, source_id, event_type, event_date, doc_number)
+);
+CREATE INDEX idx_parcel_event_parcel ON parcel_event (county_fips, source_id, event_date DESC);
+CREATE INDEX idx_parcel_event_type ON parcel_event (event_type);
+
+-- Per-parcel public-record deep links (assessor / recorder / GIS), so the
+-- property page can always point the user at the authoritative source even
+-- when the deep history itself is behind a paid/in-person wall (AB 1785).
+CREATE TABLE parcel_links (
+ county_fips TEXT NOT NULL,
+ source_id TEXT NOT NULL,
+ kind TEXT NOT NULL, -- gis | assessor | recorder | tax | permits
+ url TEXT NOT NULL,
+ label TEXT,
+ PRIMARY KEY (county_fips, source_id, kind)
+);
diff --git a/src/ingest/parcels/engine.ts b/src/ingest/parcels/engine.ts
index 7cc3f1a..397630c 100644
--- a/src/ingest/parcels/engine.ts
+++ b/src/ingest/parcels/engine.ts
@@ -11,6 +11,7 @@ const ADAPTERS: Record<string, () => Promise<{ run: () => Promise<{ upserted: nu
'nyc-acris': async () => ({ run: (await import('./nyc_acris.ts')).ingestNycAcris }),
king: async () => ({ run: (await import('./king_wa.ts')).ingestKing }),
cook: async () => ({ run: (await import('./cook_il.ts')).ingestCook }),
+ sandiego: async () => ({ run: (await import('./sandiego_ca.ts')).ingestSanDiego }),
};
async function main() {
diff --git a/src/ingest/parcels/sandiego_ca.ts b/src/ingest/parcels/sandiego_ca.ts
new file mode 100644
index 0000000..9a7dd79
--- /dev/null
+++ b/src/ingest/parcels/sandiego_ca.ts
@@ -0,0 +1,179 @@
+/**
+ * San Diego County parcels + recorded-deed history from SanGIS/SANDAG ($0 public
+ * ArcGIS REST). Pilot scope = the city of POWAY (situs_community='POWAY',
+ * ~16,422 parcels); the same adapter generalizes to any SD community / all of
+ * county_fips 06073 by widening POWAY_WHERE.
+ *
+ * Source: geo.sandag.org Hosted/Parcels FeatureServer/0. Per parcel we get the
+ * situs address parts, assessed values (land/impr/total), structure (year/beds/
+ * baths/sqft), zoning, and the LAST recorded document (doctype/docnmbr/docdate)
+ * — the deed/sale reference. That doc becomes a parcel_event (event_type='deed')
+ * with a source_url back to the public record. Sale PRICE + full deed CHAIN are
+ * behind ParcelQuest/ARCC (AB 1785 removed online APN recorder search 2024-12) —
+ * a paid/gated follow-up; this pilot lands the free authoritative base + links.
+ *
+ * Resumable: each run ingests the next MAX_PARCELS_PER_RUN by apn offset, so the
+ * hourly loop paginates through Poway across ticks, then wraps to refresh.
+ *
+ * npm run ingest:parcels -- sandiego
+ */
+import { query } from '../../../db/pool.ts';
+import { openRun, closeRun } from '../run.ts';
+import { upsertParcels, normAddress, registerParcelSource, type ParcelUpsertRow } from './upsert.ts';
+
+const FIPS = '06073';
+const SOURCE = 'sandiego_ca';
+const LAYER = 'https://geo.sandag.org/server/rest/services/Hosted/Parcels/FeatureServer/0';
+const POWAY_WHERE = "situs_community='POWAY'";
+const PAGE = 2000; // SanGIS maxRecordCount
+const MAX_PER_RUN = Number(process.env.SD_MAX_PER_RUN || 4000);
+const OUT_FIELDS = [
+ 'apn', 'apn_8', 'parcelid', 'nucleus_situs_from_nbr', 'situs_pre_dir', 'situs_street',
+ 'situs_suffix', 'situs_post_dir', 'situs_zip', 'situs_community', 'asr_land', 'asr_impr',
+ 'asr_total', 'asr_zone', 'nucleus_zone_cd', 'asr_landuse', 'year_effective', 'total_lvg_area',
+ 'bedrooms', 'baths', 'garage_stalls', 'pool', 'doctype', 'docnmbr', 'docdate', 'taxstat', 'ownerocc',
+].join(',');
+
+const s = (v: unknown): string | null => { const t = v == null ? '' : String(v).trim(); return t ? t : null; };
+const n = (v: unknown): number | null => { const x = Number(v); return Number.isFinite(x) && x !== 0 ? x : null; };
+const intCode = (v: unknown): number | null => { const x = parseInt(String(v ?? ''), 10); return Number.isFinite(x) && x !== 0 ? x : null; };
+// SanGIS encodes baths in TENTHS (45 => 4.5 baths); beds are literal.
+const bathsVal = (v: unknown): number | null => { const x = intCode(v); return x == null ? null : x / 10; };
+
+/** SanGIS docdate is MMDDYY -> ISO. 2-digit year: >30 => 19xx else 20xx. */
+function docDate(v: unknown): string | null {
+ const t = String(v ?? '').trim();
+ if (!/^\d{6}$/.test(t)) return null;
+ const mm = +t.slice(0, 2), dd = +t.slice(2, 4), yy = +t.slice(4, 6);
+ if (mm < 1 || mm > 12 || dd < 1 || dd > 31) return null;
+ const yr = yy > 30 ? 1900 + yy : 2000 + yy;
+ return `${yr}-${String(mm).padStart(2, '0')}-${String(dd).padStart(2, '0')}`;
+}
+/** year_effective is a 2-digit year. */
+function effYear(v: unknown): number | null {
+ const t = String(v ?? '').trim();
+ if (!/^\d{2}$/.test(t) || t === '00') return null;
+ const yy = +t; return yy > 30 ? 1900 + yy : 2000 + yy;
+}
+function composeAddr(a: any): string | null {
+ const parts = [s(a.nucleus_situs_from_nbr), s(a.situs_pre_dir), s(a.situs_street), s(a.situs_suffix), s(a.situs_post_dir)]
+ .filter(Boolean);
+ const addr = parts.join(' ').replace(/\s+/g, ' ').trim();
+ return addr && addr !== '0' ? addr : null;
+}
+/** rough polygon centroid (avg of first ring) -> [lng,lat] in 4326. */
+function centroid(geom: any): [number | null, number | null] {
+ const ring = geom?.rings?.[0];
+ if (!Array.isArray(ring) || !ring.length) return [null, null];
+ let sx = 0, sy = 0;
+ for (const [x, y] of ring) { sx += x; sy += y; }
+ return [+(sx / ring.length).toFixed(6), +(sy / ring.length).toFixed(6)];
+}
+
+async function fetchPage(offset: number): Promise<any[]> {
+ const u = new URL(LAYER + '/query');
+ u.searchParams.set('where', POWAY_WHERE);
+ u.searchParams.set('outFields', OUT_FIELDS);
+ u.searchParams.set('orderByFields', 'apn ASC');
+ u.searchParams.set('resultOffset', String(offset));
+ u.searchParams.set('resultRecordCount', String(PAGE));
+ u.searchParams.set('returnGeometry', 'true');
+ u.searchParams.set('outSR', '4326');
+ u.searchParams.set('f', 'json');
+ const res = await fetch(u, { signal: AbortSignal.timeout(60_000) });
+ if (!res.ok) throw new Error(`SanGIS ${res.status}: ${(await res.text()).slice(0, 160)}`);
+ const j: any = await res.json();
+ if (j.error) throw new Error(`SanGIS error: ${JSON.stringify(j.error).slice(0, 160)}`);
+ return j.features || [];
+}
+
+function links(apn: string, docnmbr: string | null): Array<{ kind: string; url: string; label: string }> {
+ return [
+ { kind: 'gis', url: `${LAYER}/query?where=apn%3D%27${apn}%27&outFields=*&f=html`, label: 'SanGIS parcel record' },
+ { kind: 'assessor', url: 'https://www.sdarcc.gov/content/arcc/home/divisions/assessor.html', label: `SD Assessor (APN ${apn})` },
+ { kind: 'recorder', url: 'https://arcc-acclaim.sdcounty.ca.gov/', label: docnmbr ? `ARCC Official Records (doc ${docnmbr})` : 'ARCC Official Records' },
+ { kind: 'tax', url: 'https://iwr.sdttc.com/', label: `SD Treasurer-Tax Collector (APN ${apn})` },
+ ];
+}
+
+export async function ingestSanDiego(): Promise<{ upserted: number }> {
+ const runId = await openRun(SOURCE, LAYER);
+ try {
+ const existing = await query<{ n: string }>(`SELECT COUNT(*)::text AS n FROM parcel WHERE county_fips=$1`, [FIPS]);
+ const total = await (async () => {
+ const u = new URL(LAYER + '/query');
+ u.searchParams.set('where', POWAY_WHERE); u.searchParams.set('returnCountOnly', 'true'); u.searchParams.set('f', 'json');
+ const j: any = await (await fetch(u, { signal: AbortSignal.timeout(30_000) })).json();
+ return Number(j.count || 0);
+ })();
+ let offset = Number(existing.rows[0].n) % Math.max(total, 1); // wrap to refresh once covered
+ console.log(`[${SOURCE}] Poway total=${total}, have=${existing.rows[0].n}, starting offset=${offset}`);
+
+ const rows: ParcelUpsertRow[] = [];
+ const events: any[] = [];
+ const linkRows: any[] = [];
+ let fetched = 0;
+
+ while (fetched < MAX_PER_RUN) {
+ const feats = await fetchPage(offset);
+ if (!feats.length) break;
+ for (const f of feats) {
+ const a = f.attributes; const apn = s(a.apn); if (!apn) continue;
+ const [lng, lat] = centroid(f.geometry);
+ const addr = composeAddr(a);
+ const sale = docDate(a.docdate);
+ rows.push({
+ county_fips: FIPS, source_id: apn,
+ address: addr, norm_address: addr ? normAddress(addr) : null,
+ city: 'Poway', zip: s(a.situs_zip),
+ lat, lng,
+ year_built: effYear(a.year_effective), sqft: n(a.total_lvg_area),
+ beds: intCode(a.bedrooms), baths: bathsVal(a.baths), units: null,
+ use_desc: s(a.asr_landuse) ? `Land use ${s(a.asr_landuse)}` : null,
+ land_value: n(a.asr_land), improvement_value: n(a.asr_impr), total_value: n(a.asr_total),
+ tax_year: null, owner_name: null, zoning: s(a.asr_zone) || s(a.nucleus_zone_cd),
+ last_sale_date: sale, last_sale_price: null,
+ extra: JSON.stringify({ apn_8: s(a.apn_8), parcelid: s(a.parcelid), doctype: s(a.doctype),
+ docnmbr: s(a.docnmbr), asr_landuse: s(a.asr_landuse), taxstat: s(a.taxstat),
+ ownerocc: s(a.ownerocc), garage_stalls: s(a.garage_stalls), pool: s(a.pool), community: 'POWAY' }),
+ });
+ if (sale) events.push({ apn, date: sale, doctype: s(a.doctype), docnmbr: s(a.docnmbr) });
+ for (const l of links(apn, s(a.docnmbr))) linkRows.push({ apn, ...l });
+ }
+ fetched += feats.length;
+ offset += feats.length;
+ if (feats.length < PAGE) break; // last page
+ }
+
+ const up = await upsertParcels(rows);
+
+ // deed events (idempotent) + public-record links
+ for (let i = 0; i < events.length; i += 500) {
+ const chunk = events.slice(i, i + 500);
+ await query(
+ `INSERT INTO parcel_event (county_fips, source_id, event_type, event_date, doc_type, doc_number, source, source_url, detail)
+ VALUES ${chunk.map((_, j) => { const b = j * 8; return `($${b+1},$${b+2},'deed',$${b+3}::date,$${b+4},$${b+5},$${b+6},$${b+7},$${b+8}::jsonb)`; }).join(',')}
+ ON CONFLICT (county_fips, source_id, event_type, event_date, doc_number) DO NOTHING`,
+ chunk.flatMap(e => [FIPS, e.apn, e.date, e.doctype, e.docnmbr, SOURCE,
+ `${LAYER}/query?where=apn%3D%27${e.apn}%27&outFields=*&f=html`, JSON.stringify({ recorded: e.date })]),
+ );
+ }
+ for (let i = 0; i < linkRows.length; i += 400) {
+ const chunk = linkRows.slice(i, i + 400);
+ await query(
+ `INSERT INTO parcel_links (county_fips, source_id, kind, url, label) VALUES ${
+ chunk.map((_, j) => { const b = j*5; return `($${b+1},$${b+2},$${b+3},$${b+4},$${b+5})`; }).join(',')
+ } ON CONFLICT (county_fips, source_id, kind) DO UPDATE SET url=EXCLUDED.url, label=EXCLUDED.label`,
+ chunk.flatMap(l => [FIPS, l.apn, l.kind, l.url, l.label]),
+ );
+ }
+
+ await registerParcelSource(FIPS, LAYER, 'San Diego (SanGIS) — Poway pilot; deeds via SanGIS last-doc, price/chain gated (ParcelQuest/ARCC)');
+ await closeRun(runId, 'ok', { upserted: up, notes: `Poway: ${up} parcels, ${events.length} deed events, ${linkRows.length} links (offset ${offset})` });
+ console.log(`[${SOURCE}] done: ${up} parcels, ${events.length} deeds, ${linkRows.length} links`);
+ return { upserted: up };
+ } catch (e: any) {
+ await closeRun(runId, 'failed', { notes: e.message });
+ throw e;
+ }
+}
diff --git a/src/jobs/hourly_loop.ts b/src/jobs/hourly_loop.ts
index 24aa2e7..876e04e 100644
--- a/src/jobs/hourly_loop.ts
+++ b/src/jobs/hourly_loop.ts
@@ -43,6 +43,7 @@ const JOBS: Job[] = [
{ name: 'discover', script: 'src/enrich/firm_website_discovery.ts', args: [], minIntervalHours: 4, runSources: ['firm_discovery'] },
{ name: 'brokers', script: 'src/jobs/broker_rotate.ts', args: [], minIntervalHours: 6, runSources: ['%_dre', '%_trec', '%_dos', '%_dbpr', '%_idfpr', '%_dcp', '%_dpr'] },
{ name: 'commercial', script: 'src/ingest/commercial/engine.ts', args: ['la'], minIntervalHours: 24, runSources: ['la_assessor'] },
+ { name: 'parcels-sd', script: 'src/ingest/parcels/engine.ts', args: ['sandiego'], minIntervalHours: 1, runSources: ['sandiego_ca'] },
];
/** hours since this job's freshest OK run across its runSources (∞ if never). */
diff --git a/src/server/property.ts b/src/server/property.ts
index 2a1f2c9..d280ca3 100644
--- a/src/server/property.ts
+++ b/src/server/property.ts
@@ -203,6 +203,15 @@ const PG_COUNTY_META: Record<string, PgCountyMeta> = {
ownerNote: 'Owner/taxpayer of record from Cook County parcel addresses dataset.',
zoningNote: 'Zoning from the Cook County parcel record.',
},
+ '06073': {
+ assessor: { name: 'San Diego County Assessor/Recorder/County Clerk (ARCC)', phone: '(619) 236-3771',
+ website: 'https://www.sdarcc.gov' },
+ detailsNote: 'San Diego County parcels from SanGIS/SANDAG (situs, structure, assessed values).',
+ historyNote: 'Last recorded document (deed) date + reference from SanGIS. Sale PRICE and full deed chain are via ARCC Official Records / ParcelQuest — AB 1785 removed online APN recorder search (Dec 2024); see Public Records links.',
+ taxNote: 'Assessed land + improvement + total from the SanGIS assessor roll.',
+ ownerNote: 'Owner name is on the ARCC secured roll (not in the SanGIS public layer); see Public Records links.',
+ zoningNote: 'Assessor zoning (asr_zone) from the SanGIS parcel record.',
+ },
default: {
assessor: { name: 'County Assessor', phone: '', website: '' },
detailsNote: 'County parcel record.', historyNote: 'Sale history pending for this county.',
@@ -324,7 +333,16 @@ export function mountProperty(app: Express) {
const total = p.total_value != null ? +p.total_value : null;
const extra = p.extra ?? {};
const sales: any[] = Array.isArray(extra.sales) ? extra.sales : [];
+ // Historical events + public-record deep links (populated for counties with a parcel_event/parcel_links feed, e.g. San Diego).
+ const deedEvents = (await query<{ event_type: string; date: string; amount: string | null; doc_type: string | null; doc_number: string | null; source_url: string | null }>(
+ `SELECT event_type, to_char(event_date,'YYYY-MM-DD') AS date, amount::text, doc_type, doc_number, source_url
+ FROM parcel_event WHERE county_fips=$1 AND source_id=$2 ORDER BY event_date DESC NULLS LAST LIMIT 100`,
+ [county, p.source_id])).rows;
+ const publicRecords = (await query<{ kind: string; url: string; label: string | null }>(
+ `SELECT kind, url, label FROM parcel_links WHERE county_fips=$1 AND source_id=$2 ORDER BY kind`,
+ [county, p.source_id])).rows;
return res.json({
+ public_records: publicRecords,
county, region_key: ctx.region?.canonical_key ?? null,
property_details: {
ain: p.source_id, address: p.address, use: p.use_desc, use_category: p.use_desc,
@@ -332,14 +350,12 @@ export function mountProperty(app: Express) {
sqft: p.sqft || null, bedrooms: p.beds, bathrooms: p.baths, units: p.units,
note: meta.detailsNote,
},
- property_history: sales.length
- ? {
- last_recording_date: p.last_sale_date, sale_price: p.last_sale_price != null ? +p.last_sale_price : null,
- sales: sales.map(s => ({ date: s.date, price: s.price, buyer: s.buyer || null, seller: s.seller || null })),
- note: meta.historyNote,
- }
- : { last_recording_date: p.last_sale_date, sale_price: p.last_sale_price != null ? +p.last_sale_price : null,
- note: meta.historyNote },
+ property_history: {
+ last_recording_date: p.last_sale_date, sale_price: p.last_sale_price != null ? +p.last_sale_price : null,
+ sales: sales.map(s => ({ date: s.date, price: s.price, buyer: s.buyer || null, seller: s.seller || null })),
+ events: deedEvents,
+ note: meta.historyNote,
+ },
tax_history: {
rolls: [{ roll_year: p.tax_year, total_value: total,
land_value: p.land_value != null ? +p.land_value : null,
← aa800ba Per-state broker health + West-Coast-first geographic Places
·
back to Nationalrealestate
·
Contrarian fixes: honest public-record links (deep vs search bb6c7e8 →