TK-10155 fix4: backfill writes ONE snapshot per listing (baseline only) — add recordLifecycle suppressSnapshot opt, de-dup the 427 duplicate 'new' backfill snapshots (1026 to 599), respect fix-1 rescan guard in backfill

37575d87162b38747ca06d63400aaf2e0f34577e · 2026-08-02 23:47:08 -0700 · Steve

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Files touched

M db/backfill-listing-lifecycle.ts
M src/server/listing-lifecycle.ts

Diff

commit 37575d87162b38747ca06d63400aaf2e0f34577e
Author: Steve <steve@designerwallcoverings.com>
Date:   Sun Aug 2 23:47:08 2026 -0700

    TK-10155 fix4: backfill writes ONE snapshot per listing (baseline only) — add recordLifecycle suppressSnapshot opt, de-dup the 427 duplicate 'new' backfill snapshots (1026 to 599), respect fix-1 rescan guard in backfill
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 db/backfill-listing-lifecycle.ts | 22 ++++++++++++++++------
 src/server/listing-lifecycle.ts  | 15 ++++++++++++++-
 2 files changed, 30 insertions(+), 7 deletions(-)

diff --git a/db/backfill-listing-lifecycle.ts b/db/backfill-listing-lifecycle.ts
index 21f7e9e..720077d 100644
--- a/db/backfill-listing-lifecycle.ts
+++ b/db/backfill-listing-lifecycle.ts
@@ -7,9 +7,10 @@
  * when the latest event already matches the derived state, so running twice does not double-seed
  * (a second run captures no baseline snapshot because the state is unchanged).
  *
- * NOTE: this baseline intentionally captures a snapshot for EVERY listing regardless of state
- * (via captured_reason='baseline'), then lays down the derived-state event. That guarantees a
- * preserved copy exists for every listing from the start — the withdrawal-snapshot promise.
+ * NOTE: this baseline captures EXACTLY ONE snapshot per listing (captured_reason='baseline'),
+ * then lays down the derived-state event with suppressSnapshot=true so recordLifecycle does NOT
+ * fire a SECOND ('new') snapshot on top of it (fix 4 — that double-count made 599 listings write
+ * 1026 snapshots). One preserved copy per listing from the start = the withdrawal-snapshot promise.
  *
  *   npm run lifecycle:backfill
  *   npx tsx db/backfill-listing-lifecycle.ts
@@ -36,6 +37,13 @@ async function main() {
   const sourceMax = new Map<string, Date>();
   for (const r of maxRes.rows) if (r.max_last_seen) sourceMax.set(r.source, new Date(r.max_last_seen));
 
+  // fix 1: last full rescan per source (null = incremental-only → no gone-from-feed withdrawal).
+  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 ORDER BY id`,
@@ -50,7 +58,7 @@ async function main() {
   for (const l of rows.rows) {
     const ref = sourceMax.get(l.source);
     if (!ref) continue;
-    const ctx = { sourceMaxLastSeen: ref, now };
+    const ctx = { sourceMaxLastSeen: ref, lastFullRescanAt: lastFullRescan.get(l.source) ?? null, now };
     const derived: LifecycleState = classifyState(l, ctx);
     stateCounts[derived] = (stateCounts[derived] || 0) + 1;
 
@@ -82,8 +90,10 @@ async function main() {
       }
     }
 
-    // 2. derived-state event (+ any state-triggered snapshot via recordLifecycle).
-    const res = await recordLifecycle(query, l, ctx);
+    // 2. derived-state EVENT only. fix 4: suppressSnapshot=true so we do NOT also fire the
+    //    state-triggered ('new') snapshot — step 1 already wrote exactly one 'baseline' snapshot
+    //    per listing. Previously this double-counted (599 listings → 1026 snapshots).
+    const res = await recordLifecycle(query, l, ctx, { suppressSnapshot: true });
     if (res.eventInserted) events++;
     if (res.snapshotInserted) stateSnaps++;
   }
diff --git a/src/server/listing-lifecycle.ts b/src/server/listing-lifecycle.ts
index 311a7ed..3069c27 100644
--- a/src/server/listing-lifecycle.ts
+++ b/src/server/listing-lifecycle.ts
@@ -169,10 +169,22 @@ export interface RecordLifecycleResult {
  * again (returns to active/new); no snapshot is captured for reappearance (it's coming BACK,
  * not being lost).
  */
+export interface RecordLifecycleOpts {
+  /**
+   * TK-10155 fix 4: when true, still write the lifecycle EVENT but SKIP the state-triggered
+   * snapshot. The backfill uses this because it already lays down one 'baseline' snapshot per
+   * listing itself — without this, a listing deriving to 'new' would get a SECOND ('new')
+   * snapshot, inflating the store (599 listings → 1026 snapshots pre-fix). Normal ingest/sweep
+   * callers leave this false so real transitions (withdrawn/closed/stale) still snapshot.
+   */
+  suppressSnapshot?: boolean;
+}
+
 export async function recordLifecycle(
   db: QueryFn,
   listing: LifecycleListing,
   ctx: LifecycleCtx,
+  opts: RecordLifecycleOpts = {},
 ): Promise<RecordLifecycleResult> {
   let state = classifyState(listing, ctx);
 
@@ -225,7 +237,8 @@ export async function recordLifecycle(
 
   // preserve a FULL snapshot on transitions INTO a snapshot state (incl. first-ever 'new').
   // 'reappeared' is intentionally NOT a snapshot state — the listing is coming back, not lost.
-  if (SNAPSHOT_STATES.has(state) && listing.id != null) {
+  // fix 4: opts.suppressSnapshot lets the backfill skip this (it writes its own baseline snapshot).
+  if (!opts.suppressSnapshot && SNAPSHOT_STATES.has(state) && listing.id != null) {
     const snap = await buildSnapshot(db, Number(listing.id));
     if (snap) {
       await db(

← d8955b6 TK-10155 fix2: wire listings ingest + lifecycle sweep into e · back to Nationalrealestate · usre: coverage-gate the full-rescan marker — downgrade to 'p 3709d7d →