← back to Nationalrealestate
Contrarian fixes: honest public-record links (deep vs search), no future years, baths/beds guards, forward-cursor resumability, history completeness flags
bb6c7e8da3635183b7c43c7cb6f76e31a343b2c3 · 2026-07-26 07:38:18 -0700 · Steve Abrams
Files touched
A db/migrations/011_ingest_cursor.sqlM src/ingest/parcels/sandiego_ca.tsM src/server/property.ts
Diff
commit bb6c7e8da3635183b7c43c7cb6f76e31a343b2c3
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sun Jul 26 07:38:18 2026 -0700
Contrarian fixes: honest public-record links (deep vs search), no future years, baths/beds guards, forward-cursor resumability, history completeness flags
---
db/migrations/011_ingest_cursor.sql | 10 +++++++
src/ingest/parcels/sandiego_ca.ts | 52 ++++++++++++++++++++++++-------------
src/server/property.ts | 17 +++++++++---
3 files changed, 58 insertions(+), 21 deletions(-)
diff --git a/db/migrations/011_ingest_cursor.sql b/db/migrations/011_ingest_cursor.sql
new file mode 100644
index 0000000..c5cd8ef
--- /dev/null
+++ b/db/migrations/011_ingest_cursor.sql
@@ -0,0 +1,10 @@
+-- M-PH2: resumable ingest cursor — replaces the count%total offset that wrapped
+-- to 0 on full coverage and re-fetched the first pages every hour. Each paging
+-- source advances next_offset forward and wraps cleanly, so a covered source
+-- does a light rolling refresh instead of re-hammering the endpoint.
+CREATE TABLE ingest_cursor (
+ source TEXT PRIMARY KEY,
+ next_offset INT NOT NULL DEFAULT 0,
+ total INT,
+ updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
diff --git a/src/ingest/parcels/sandiego_ca.ts b/src/ingest/parcels/sandiego_ca.ts
index 9a7dd79..fe4406f 100644
--- a/src/ingest/parcels/sandiego_ca.ts
+++ b/src/ingest/parcels/sandiego_ca.ts
@@ -37,8 +37,16 @@ const OUT_FIELDS = [
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; };
+// 2-digit year -> full year, pivoting at the CURRENT 2-digit year so we never
+// emit a future date (yy=30 => 1930, not 2030; yy=24 => 2024). Shared by deeds + built-year.
+const THIS_YY = new Date().getFullYear() % 100;
+const fullYear = (yy: number): number => (yy <= THIS_YY ? 2000 + yy : 1900 + yy);
+// SanGIS encodes baths in TENTHS for HOMES (45 => 4.5). On multi-unit/commercial
+// the field is an aggregate whole count (547 total baths) — /10 there is nonsense,
+// so treat raw>100 (=>10+ baths) as an un-decodable aggregate and null it.
+const bathsVal = (v: unknown): number | null => { const x = intCode(v); return x == null || x > 100 ? null : x / 10; };
+// Beds: literal for homes; a huge count is a multi-unit aggregate, not a home attr -> null.
+const bedsVal = (v: unknown): number | null => { const x = intCode(v); return x == null || x > 12 ? null : x; };
/** SanGIS docdate is MMDDYY -> ISO. 2-digit year: >30 => 19xx else 20xx. */
function docDate(v: unknown): string | null {
@@ -46,14 +54,13 @@ function docDate(v: unknown): string | null {
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')}`;
+ return `${fullYear(yy)}-${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;
+ return fullYear(+t);
}
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)]
@@ -87,27 +94,30 @@ async function fetchPage(offset: number): Promise<any[]> {
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})` },
- ];
+// Only ONE verified per-parcel deep link exists (the SanGIS record — the county
+// gated online APN search under AB 1785, and the assessor/recorder/tax portals
+// 404/403/503 on every APN-parameterized URL we tested). So we persist ONLY the
+// real deep link; the property API constructs honestly-labeled SEARCH portals
+// from the county + APN (not stored 16k× as identical homepages).
+function links(apn: string): 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 (authoritative)' }];
}
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}`);
+ // Forward-advancing cursor (M-PH2): resume where the last run stopped; wrap to
+ // 0 only when the whole set is covered, so we never re-fetch page 0 every tick.
+ const cur = await query<{ next_offset: number }>(`SELECT next_offset FROM ingest_cursor WHERE source=$1`, [SOURCE]);
+ let offset = cur.rows[0]?.next_offset ?? 0;
+ if (offset >= total) offset = 0;
+ console.log(`[${SOURCE}] Poway total=${total}, resume offset=${offset}`);
const rows: ParcelUpsertRow[] = [];
const events: any[] = [];
@@ -128,7 +138,7 @@ export async function ingestSanDiego(): Promise<{ upserted: number }> {
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,
+ beds: bedsVal(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),
@@ -138,7 +148,7 @@ export async function ingestSanDiego(): Promise<{ upserted: number }> {
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 });
+ for (const l of links(apn)) linkRows.push({ apn, ...l });
}
fetched += feats.length;
offset += feats.length;
@@ -154,7 +164,7 @@ export async function ingestSanDiego(): Promise<{ upserted: number }> {
`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,
+ 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 })]),
);
}
@@ -168,6 +178,12 @@ export async function ingestSanDiego(): Promise<{ upserted: number }> {
);
}
+ // persist the forward cursor (wrap on full coverage)
+ const nextOffset = offset >= total ? 0 : offset;
+ await query(
+ `INSERT INTO ingest_cursor (source, next_offset, total) VALUES ($1,$2,$3)
+ ON CONFLICT (source) DO UPDATE SET next_offset=$2, total=$3, updated_at=NOW()`,
+ [SOURCE, nextOffset, total]);
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`);
diff --git a/src/server/property.ts b/src/server/property.ts
index d280ca3..d5a1227 100644
--- a/src/server/property.ts
+++ b/src/server/property.ts
@@ -338,11 +338,19 @@ export function mountProperty(app: Express) {
`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 }>(
+ const storedLinks = (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;
+ [county, p.source_id])).rows.map(r => ({ ...r, deep_link: r.kind === 'gis' }));
+ // Honest search portals (NOT deep links — the county gated per-APN URLs) built with a dashed APN.
+ const dashApn = (apn: string) => county === '06073' && /^\d{10}$/.test(apn)
+ ? `${apn.slice(0, 3)}-${apn.slice(3, 6)}-${apn.slice(6, 8)}-${apn.slice(8, 10)}` : apn;
+ const searchPortals = county === '06073' ? [
+ { kind: 'assessor', deep_link: false, url: 'https://www.sdarcc.gov', label: `Search SD Assessor (ARCC) — APN ${dashApn(p.source_id)}` },
+ { kind: 'recorder', deep_link: false, url: 'https://arcc-acclaim.sdcounty.ca.gov/', label: `Search ARCC Official Records — APN ${dashApn(p.source_id)}` },
+ { kind: 'tax', deep_link: false, url: 'https://iwr.sdttc.com/', label: `Search SD Treasurer-Tax Collector — APN ${dashApn(p.source_id)}` },
+ ] : [];
return res.json({
- public_records: publicRecords,
+ public_records: [...storedLinks, ...searchPortals],
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,
@@ -354,6 +362,9 @@ export function mountProperty(app: Express) {
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,
+ recorded_documents: deedEvents.length,
+ // honest completeness signal: SanGIS gives only the LAST recorded doc, not the full chain
+ full_chain_available: deedEvents.length > 1 || sales.length > 1,
note: meta.historyNote,
},
tax_history: {
← a2c4ea6 Poway property-history pilot: SanGIS parcels + deed events +
·
back to Nationalrealestate
·
Property page: render deed events + public-record links (dee f195028 →