← back to Nationalrealestate
TK-10155 fix1: guard gone-from-feed withdrawal behind a per-source full-rescan signal (migration 015 source_ingest_run) — incremental-only sources (ColdwellBanker new-day sitemap) no longer fabricate withdrawals for live listings
fdb639e97ec49268f81f441e82a09e9dd1f2c026 · 2026-08-02 23:44:28 -0700 · Steve
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A db/migrations/015_source_ingest_run.sqlM src/ingest/listings/coldwellbanker.tsM src/ingest/listings/engine.tsM src/ingest/listings/realtytexas.tsM src/ingest/listings/types.tsM src/ingest/listings/weichert.tsM src/jobs/listing-lifecycle-sweep.tsM src/server/listing-lifecycle.ts
Diff
commit fdb639e97ec49268f81f441e82a09e9dd1f2c026
Author: Steve <steve@designerwallcoverings.com>
Date: Sun Aug 2 23:44:28 2026 -0700
TK-10155 fix1: guard gone-from-feed withdrawal behind a per-source full-rescan signal (migration 015 source_ingest_run) — incremental-only sources (ColdwellBanker new-day sitemap) no longer fabricate withdrawals for live listings
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
db/migrations/015_source_ingest_run.sql | 30 ++++++++++++++++++++++++++++++
src/ingest/listings/coldwellbanker.ts | 4 ++++
src/ingest/listings/engine.ts | 17 ++++++++++++++++-
src/ingest/listings/realtytexas.ts | 4 ++++
src/ingest/listings/types.ts | 12 ++++++++++++
src/ingest/listings/weichert.ts | 4 ++++
src/jobs/listing-lifecycle-sweep.ts | 26 +++++++++++++++++++++++++-
src/server/listing-lifecycle.ts | 27 +++++++++++++++++++++++++--
8 files changed, 120 insertions(+), 4 deletions(-)
diff --git a/db/migrations/015_source_ingest_run.sql b/db/migrations/015_source_ingest_run.sql
new file mode 100644
index 0000000..221f2e9
--- /dev/null
+++ b/db/migrations/015_source_ingest_run.sql
@@ -0,0 +1,30 @@
+-- TK-10155 (contrarian fix 1): per-source ingest-MODE ledger.
+--
+-- The false-withdrawal poison: some listing sources ingest via an INCREMENTAL / new-day
+-- sitemap (e.g. ColdwellBanker's sitemapindex-listings-new-day.xml) that structurally NEVER
+-- re-surfaces old-but-still-active listings. On those sources a listing's last_seen freezes
+-- and it falsely ages into stale/withdrawn — fabricating withdrawals + snapshots for LIVE
+-- properties. The detector must therefore only derive gone-from-feed → stale/withdrawn for a
+-- source that has actually been FULLY RE-SCANNED after the listing's last_seen.
+--
+-- This table records, once per ingest run, whether that run was a 'full' source rescan or an
+-- 'incremental' new-day pull, plus when it ran and how many listings it touched. classifyState
+-- reads MAX(started_at) over mode='full' runs for the source as the "last full rescan"
+-- reference; a listing whose last_seen predates that reference (and wasn't seen) is genuinely
+-- gone. Incremental-only sources never satisfy that test → never falsely withdraw.
+--
+-- Additive/idempotent only (CREATE ... IF NOT EXISTS); mutates no existing data. migrate.ts
+-- wraps each file in a transaction, so no BEGIN/COMMIT here.
+
+CREATE TABLE IF NOT EXISTS source_ingest_run (
+ id BIGSERIAL PRIMARY KEY,
+ source TEXT NOT NULL,
+ mode TEXT NOT NULL CHECK (mode IN ('full','incremental')),
+ started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+ finished_at TIMESTAMPTZ,
+ listing_count INT
+);
+
+-- The detector's hot path: newest FULL rescan per source.
+CREATE INDEX IF NOT EXISTS idx_sir_source_mode_started
+ ON source_ingest_run (source, mode, started_at DESC);
diff --git a/src/ingest/listings/coldwellbanker.ts b/src/ingest/listings/coldwellbanker.ts
index ca9cc45..eded3e1 100644
--- a/src/ingest/listings/coldwellbanker.ts
+++ b/src/ingest/listings/coldwellbanker.ts
@@ -11,6 +11,10 @@ const SITEMAP_INDEX =
export const coldwellbanker: ListingAdapter = {
source: 'coldwellbanker',
+ // INCREMENTAL: discovery is the *new-day* sitemap index, which lists only listings added
+ // that day and NEVER re-surfaces old-but-still-active listings. A CB listing therefore
+ // freezes its last_seen after its debut day, so gone-from-feed here does NOT mean withdrawn.
+ mode: 'incremental',
host: 'www.coldwellbanker.com',
listingsPathHint: '/tx/',
diff --git a/src/ingest/listings/engine.ts b/src/ingest/listings/engine.ts
index 4e35e21..d1ab256 100644
--- a/src/ingest/listings/engine.ts
+++ b/src/ingest/listings/engine.ts
@@ -151,7 +151,22 @@ async function runAdapter(a: ListingAdapter): Promise<{ upserted: number; skippe
return { upserted: 0, skipped };
}
await closeRun(runId, 'ok', { upserted, skipped, notes: `firm_id=${firmId}` });
- console.log(`[${a.source}] DONE upserted=${upserted} skipped=${skipped} firm_id=${firmId}`);
+
+ // TK-10155 fix 1: record this run's INGEST MODE so the lifecycle detector knows whether the
+ // source was FULLY re-scanned. A 'full' run here is what lets classifyState later treat a
+ // gone-from-feed listing as withdrawn; an 'incremental' run does NOT establish absence.
+ // Non-fatal — a ledger failure must never fail the ingest.
+ try {
+ await query(
+ `INSERT INTO source_ingest_run (source, mode, finished_at, listing_count)
+ VALUES ($1,$2, now(), $3)`,
+ [a.source, a.mode, upserted],
+ );
+ } catch (e: any) {
+ console.warn(`[${a.source}] source_ingest_run record skipped: ${e?.message || e}`);
+ }
+
+ console.log(`[${a.source}] DONE upserted=${upserted} skipped=${skipped} firm_id=${firmId} mode=${a.mode}`);
return { upserted, skipped };
} catch (e: any) {
await closeRun(runId, 'failed', { notes: String(e.message || e) });
diff --git a/src/ingest/listings/realtytexas.ts b/src/ingest/listings/realtytexas.ts
index e540edf..3af7938 100644
--- a/src/ingest/listings/realtytexas.ts
+++ b/src/ingest/listings/realtytexas.ts
@@ -11,6 +11,10 @@ const ROOT_SITEMAP = 'https://www.realtytexas.com/sitemap.xml';
export const realtytexas: ListingAdapter = {
source: 'realtytexas',
+ // FULL: discovery walks the per-county `sitemap_listings_onmarket_*` maps, i.e. the current
+ // ON-MARKET set. A listing absent from a fresh crawl is genuinely off-market → withdrawal
+ // detection is valid for this source.
+ mode: 'full',
host: 'www.realtytexas.com',
listingsPathHint: '/palestine/',
diff --git a/src/ingest/listings/types.ts b/src/ingest/listings/types.ts
index c29f47f..42faefd 100644
--- a/src/ingest/listings/types.ts
+++ b/src/ingest/listings/types.ts
@@ -26,6 +26,18 @@ export interface ListingFacts {
export interface ListingAdapter {
/** stored in listing.source and ingest_runs.source */
source: string;
+ /**
+ * INGEST MODE — declares whether a run of this adapter FULLY re-scans the source's live
+ * listing set ('full') or only pulls newly-added listings ('incremental', e.g. a new-day
+ * sitemap that never re-surfaces old-but-still-active listings).
+ *
+ * This is load-bearing for withdrawal detection (TK-10155 fix 1): a listing that goes
+ * "gone from feed" can only be treated as stale/withdrawn on a source that has had a FULL
+ * rescan AFTER the listing's last_seen. Incremental-only sources structurally cannot prove
+ * absence, so their listings must NOT be aged into withdrawn. Recorded per run into
+ * source_ingest_run (migration 015) and read back by classifyState.
+ */
+ mode: 'full' | 'incremental';
/** host used to resolve firm_id via firm_site.url domain match */
host: string;
/** robots.txt path the adapter reads listings under — used for the honor check */
diff --git a/src/ingest/listings/weichert.ts b/src/ingest/listings/weichert.ts
index 3414293..99fdf8d 100644
--- a/src/ingest/listings/weichert.ts
+++ b/src/ingest/listings/weichert.ts
@@ -19,6 +19,10 @@ function flatten(nodes: any[]): any[] {
export const weichert: ListingAdapter = {
source: 'weichert',
+ // FULL: discovery paginates the complete `sitemaplistings.ashx` (justlisted=False) set, i.e.
+ // every currently-listed property, so a fresh crawl re-surfaces still-active listings and a
+ // missing one is genuinely gone → withdrawal detection is valid for this source.
+ mode: 'full',
host: 'www.weichert.com',
listingsPathHint: '/137101627/', // representative PDP path for the robots honor check
diff --git a/src/jobs/listing-lifecycle-sweep.ts b/src/jobs/listing-lifecycle-sweep.ts
index c92178e..b31edc6 100644
--- a/src/jobs/listing-lifecycle-sweep.ts
+++ b/src/jobs/listing-lifecycle-sweep.ts
@@ -28,6 +28,18 @@ async function main() {
if (r.max_last_seen) sourceMax.set(r.source, new Date(r.max_last_seen));
}
+ // TK-10155 fix 1: per-source LAST FULL RESCAN time (max started_at over mode='full' runs).
+ // A source with no full run at all is absent from this map → lastFullRescanAt=null → its
+ // listings are NEVER aged into gone-from-feed withdrawal (incremental-only sources).
+ const fullRes = await query<{ source: string; last_full: string }>(
+ `SELECT source, MAX(started_at) AS last_full
+ FROM source_ingest_run WHERE mode = 'full' GROUP BY source`,
+ );
+ const lastFullRescan = new Map<string, Date>();
+ for (const r of fullRes.rows) {
+ if (r.last_full) lastFullRescan.set(r.source, new Date(r.last_full));
+ }
+
const rows = await query<LifecycleListing>(
`SELECT id, source, source_id, address, price, status, first_seen, last_seen
FROM listing
@@ -39,11 +51,18 @@ async function main() {
let events = 0;
let snapshots = 0;
let skippedNoRef = 0;
+ // # listings on incremental-only sources (no full rescan) that are structurally NOT eligible
+ // for gone-from-feed withdrawal — surfaced in the sweep output for transparency.
+ let incrementalNotEligible = 0;
for (const l of rows.rows) {
const ref = sourceMax.get(l.source);
if (!ref) { skippedNoRef++; continue; }
- const ctx = { sourceMaxLastSeen: ref, now };
+ // null when the source has never had a full rescan (incremental-only) → gone-from-feed
+ // withdrawal is suppressed for this listing.
+ const lastFullRescanAt = lastFullRescan.get(l.source) ?? null;
+ if (lastFullRescanAt === null) incrementalNotEligible++;
+ const ctx = { sourceMaxLastSeen: ref, lastFullRescanAt, now };
// tally the DERIVED state for every listing (the "counts per state" summary).
const derived: LifecycleState = classifyState(l, ctx);
@@ -62,6 +81,11 @@ async function main() {
console.log(`[lifecycle-sweep] new events written: ${events}`);
console.log(`[lifecycle-sweep] snapshots captured: ${snapshots}`);
if (skippedNoRef) console.log(`[lifecycle-sweep] skipped (no source ref): ${skippedNoRef}`);
+ // TK-10155 fix 1 transparency line.
+ console.log(
+ `[lifecycle-sweep] ${incrementalNotEligible} listings on incremental-only sources are NOT ` +
+ `eligible for gone-from-feed withdrawal (needs a full rescan).`,
+ );
await pool.end();
}
diff --git a/src/server/listing-lifecycle.ts b/src/server/listing-lifecycle.ts
index d3ca184..311a7ed 100644
--- a/src/server/listing-lifecycle.ts
+++ b/src/server/listing-lifecycle.ts
@@ -54,6 +54,17 @@ export interface LifecycleListing {
export interface LifecycleCtx {
/** per-source MOST-RECENT successful ingest reference (max last_seen for the source). */
sourceMaxLastSeen: Date;
+ /**
+ * TK-10155 fix 1 (false-withdrawal guard): the start time of the source's most-recent FULL
+ * rescan (MAX(started_at) over source_ingest_run WHERE mode='full'), or null if the source
+ * has NEVER had a full rescan (e.g. an incremental-only source like ColdwellBanker's new-day
+ * sitemap). Gone-from-feed → stale/withdrawn is ONLY derivable when a full rescan happened
+ * AFTER the listing's last_seen — otherwise absence from the feed is structural, not a pull.
+ * When null (or older than the listing's last_seen), gone-from-feed is suppressed and the
+ * listing stays active/new. Status-based withdrawn/closed is unaffected. Undefined = the
+ * legacy behavior (no guard), so old callers still compile.
+ */
+ lastFullRescanAt?: Date | null;
/** "now" reference — defaults to new Date(); injectable for tests. */
now?: Date;
}
@@ -92,8 +103,20 @@ export function classifyState(listing: LifecycleListing, ctx: LifecycleCtx): Lif
// last_seen sits. A listing seen in the latest ingest has age ~0 ⇒ currently seen.
const goneDays = lastSeen ? (ref.getTime() - lastSeen.getTime()) / DAY_MS : Infinity;
- if (goneDays >= WITHDRAWN_DAYS) return 'withdrawn';
- if (goneDays >= STALE_DAYS) return 'stale';
+ // TK-10155 fix 1: only allow gone-from-feed → stale/withdrawn if the source was FULLY
+ // re-scanned AFTER this listing's last_seen. Incremental-only sources (no full rescan, or
+ // the last full rescan predates last_seen) CANNOT prove the listing is gone — the feed just
+ // never re-surfaces it — so we must NOT fabricate a withdrawal. `undefined` = legacy callers
+ // with no guard (unchanged behavior); an explicit `null` = "no full rescan ever" (suppress).
+ const eligibleForGoneFromFeed =
+ ctx.lastFullRescanAt === undefined
+ ? true // legacy path — no ingest-mode signal supplied
+ : ctx.lastFullRescanAt != null && lastSeen != null && ctx.lastFullRescanAt >= lastSeen;
+
+ if (eligibleForGoneFromFeed) {
+ if (goneDays >= WITHDRAWN_DAYS) return 'withdrawn';
+ if (goneDays >= STALE_DAYS) return 'stale';
+ }
// currently seen in (or very near) the latest ingest window.
const newAgeDays = firstSeen ? (now.getTime() - firstSeen.getTime()) / DAY_MS : Infinity;
← 6193a68 TK-10155: listings.html — color-coded lifecycle state chip +
·
back to Nationalrealestate
·
TK-10155 fix2: wire listings ingest + lifecycle sweep into e d8955b6 →