[object Object]

← back to Nationalrealestate

TK-10155 (Cody FIX-FIRST): guarantee every listing has >=1 preserved snapshot

ae4eb1122d3eb770ccc4b77ead7793e1539d72c4 · 2026-08-24 19:55:22 -0700 · Steve Abrams

Cody's contrarian panel returned FIX FIRST on the top surviving hole: a listing whose
first-see snapshot hook FAILED (caught+logged) and that then derives straight to 'active'
(first_seen already > NEW_DAYS, so it skips 'new') sits in the table with NO snapshot at all.
'active' is deliberately not a SNAPSHOT_STATE, so recordLifecycle never captures one. The
'data survives after the listing vanishes' promise then rested on 'nothing ever deletes a
listing' (true today — no DELETE FROM listing exists in the pipeline) rather than on the design.
The day a prune job lands, that listing's data would be lost (ON DELETE SET NULL just nulls an
event row pointing at nothing).

Fix — make preservation a design property:
- export ensureBaselineSnapshot(db, listing): cheap NOT-EXISTS guard that captures ONE 'baseline'
  snapshot iff the listing has zero snapshots; idempotent, append-only, no-op otherwise.
- sweep calls it for every listing → every listing is guaranteed >=1 snapshot before it can ever
  leave the table; reports 'baseline snapshots backfilled' count.
- dbtest: reproduce the exact gap (active listing, event but no snapshot) and assert
  ensureBaselineSnapshot rescues it + is idempotent.

Verified on live local usre: 796 listings / 796 distinct snapshotted / 0 without a snapshot;
sweep backfilled 0 (steady-state no-op). tsc clean, both self-tests PASS.

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

Files touched

Diff

commit ae4eb1122d3eb770ccc4b77ead7793e1539d72c4
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Mon Aug 24 19:55:22 2026 -0700

    TK-10155 (Cody FIX-FIRST): guarantee every listing has >=1 preserved snapshot
    
    Cody's contrarian panel returned FIX FIRST on the top surviving hole: a listing whose
    first-see snapshot hook FAILED (caught+logged) and that then derives straight to 'active'
    (first_seen already > NEW_DAYS, so it skips 'new') sits in the table with NO snapshot at all.
    'active' is deliberately not a SNAPSHOT_STATE, so recordLifecycle never captures one. The
    'data survives after the listing vanishes' promise then rested on 'nothing ever deletes a
    listing' (true today — no DELETE FROM listing exists in the pipeline) rather than on the design.
    The day a prune job lands, that listing's data would be lost (ON DELETE SET NULL just nulls an
    event row pointing at nothing).
    
    Fix — make preservation a design property:
    - export ensureBaselineSnapshot(db, listing): cheap NOT-EXISTS guard that captures ONE 'baseline'
      snapshot iff the listing has zero snapshots; idempotent, append-only, no-op otherwise.
    - sweep calls it for every listing → every listing is guaranteed >=1 snapshot before it can ever
      leave the table; reports 'baseline snapshots backfilled' count.
    - dbtest: reproduce the exact gap (active listing, event but no snapshot) and assert
      ensureBaselineSnapshot rescues it + is idempotent.
    
    Verified on live local usre: 796 listings / 796 distinct snapshotted / 0 without a snapshot;
    sweep backfilled 0 (steady-state no-op). tsc clean, both self-tests PASS.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 src/jobs/listing-lifecycle-sweep.ts    | 10 ++++++++
 src/server/listing-lifecycle.dbtest.ts | 46 ++++++++++++++++++++++++++++++++--
 src/server/listing-lifecycle.ts        | 37 +++++++++++++++++++++++++++
 3 files changed, 91 insertions(+), 2 deletions(-)

diff --git a/src/jobs/listing-lifecycle-sweep.ts b/src/jobs/listing-lifecycle-sweep.ts
index b31edc6..fe8cea4 100644
--- a/src/jobs/listing-lifecycle-sweep.ts
+++ b/src/jobs/listing-lifecycle-sweep.ts
@@ -13,6 +13,7 @@
 import { pool, query } from '../../db/pool.ts';
 import {
   recordLifecycle,
+  ensureBaselineSnapshot,
   classifyState,
   type LifecycleListing,
   type LifecycleState,
@@ -50,6 +51,7 @@ async function main() {
   const stateCounts: Record<string, number> = {};
   let events = 0;
   let snapshots = 0;
+  let baselinesBackfilled = 0; // TK-10155 Cody FIX-FIRST: snapshots created for listings that had none
   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.
@@ -71,6 +73,13 @@ async function main() {
     const res = await recordLifecycle(query, l, ctx);
     if (res.eventInserted) events++;
     if (res.snapshotInserted) snapshots++;
+
+    // TK-10155 Cody FIX-FIRST gate: guarantee EVERY listing has >=1 preserved snapshot, so the
+    // "data survives after the listing vanishes" promise is a design property, not a side-effect of
+    // "nothing ever deletes a listing." Closes the gap where a listing whose first-see snapshot
+    // FAILED and that derives straight to 'active' (not a snapshot state) would sit with NO snapshot.
+    // No-op once any snapshot exists — so this only writes for the leaked cases, not every sweep.
+    if (await ensureBaselineSnapshot(query, l)) baselinesBackfilled++;
   }
 
   console.log('\n[lifecycle-sweep] derived state (all listings):');
@@ -80,6 +89,7 @@ async function main() {
   console.log(`[lifecycle-sweep] listings scanned:   ${rows.rows.length}`);
   console.log(`[lifecycle-sweep] new events written: ${events}`);
   console.log(`[lifecycle-sweep] snapshots captured: ${snapshots}`);
+  console.log(`[lifecycle-sweep] baseline snapshots backfilled (had none): ${baselinesBackfilled}`);
   if (skippedNoRef) console.log(`[lifecycle-sweep] skipped (no source ref): ${skippedNoRef}`);
   // TK-10155 fix 1 transparency line.
   console.log(
diff --git a/src/server/listing-lifecycle.dbtest.ts b/src/server/listing-lifecycle.dbtest.ts
index 1cedbcb..223093e 100644
--- a/src/server/listing-lifecycle.dbtest.ts
+++ b/src/server/listing-lifecycle.dbtest.ts
@@ -12,7 +12,12 @@
  * that the preserved snapshot can reconstruct the listing after the row itself is deleted.
  */
 import { pool, query } from '../../db/pool.ts';
-import { recordLifecycle, type LifecycleListing, type LifecycleCtx } from './listing-lifecycle.ts';
+import {
+  recordLifecycle,
+  ensureBaselineSnapshot,
+  type LifecycleListing,
+  type LifecycleCtx,
+} from './listing-lifecycle.ts';
 
 const SOURCE = 'selftest-tk10155';
 const SID = 'SNAP-1';
@@ -110,8 +115,45 @@ async function main() {
     preserved && { address: preserved.address, price: preserved.price },
   );
 
+  // 6. Cody FIX-FIRST gate: the 'active'-with-no-snapshot gap. A listing whose first-see snapshot
+  //    hook failed and that derives straight to 'active' (not a snapshot state) has NO snapshot —
+  //    ensureBaselineSnapshot (called by the sweep) must rescue it, and be idempotent.
+  const SID2 = 'SNAP-2';
+  await query(`DELETE FROM listing_snapshot WHERE source=$1 AND source_id=$2`, [SOURCE, SID2]);
+  await query(`DELETE FROM listing_lifecycle_event WHERE source=$1 AND source_id=$2`, [SOURCE, SID2]);
+  await query(`DELETE FROM listing WHERE source=$1 AND source_id=$2`, [SOURCE, SID2]);
+  const ins2 = await query<{ id: number }>(
+    `INSERT INTO listing (source, source_id, address, price, first_seen, last_seen)
+     VALUES ($1,$2,'9 Nowhere Rd, Ogdenville',321000,$3,$3) RETURNING id`,
+    [SOURCE, SID2, new Date(now.getTime() - 300 * DAY).toISOString()],
+  );
+  const id2 = ins2.rows[0].id;
+  const active: LifecycleListing = {
+    id: id2, source: SOURCE, source_id: SID2, address: '9 Nowhere Rd, Ogdenville', price: 321000,
+    status: null, first_seen: new Date(now.getTime() - 300 * DAY), last_seen: new Date(now),
+  };
+  // record only an 'active' event (simulating first-see snapshot hook having failed → no snapshot).
+  // classifyState → 'active' (first_seen 300d ago, currently seen); 'active' is NOT a snapshot state,
+  // so recordLifecycle writes the event but NO snapshot — reproducing the exact gap Cody flagged.
+  const ra = await recordLifecycle(query, active, ctxSeen);
+  check(ra.state === 'active' && ra.eventInserted && !ra.snapshotInserted,
+    "recordLifecycle on an 'active' listing writes event, NO snapshot (reproduces the gap)", ra);
+  const snapCountBefore = await query<{ n: string }>(
+    `SELECT count(*)::text n FROM listing_snapshot WHERE source=$1 AND source_id=$2`, [SOURCE, SID2]);
+  check(Number(snapCountBefore.rows[0].n) === 0, "active-derived listing starts with NO snapshot (the gap)");
+  const rescued = await ensureBaselineSnapshot(query, active);
+  check(rescued === true, 'ensureBaselineSnapshot rescues the snapshot-less listing');
+  const snapCountAfter = await query<{ n: string }>(
+    `SELECT count(*)::text n FROM listing_snapshot WHERE source=$1 AND source_id=$2`, [SOURCE, SID2]);
+  check(Number(snapCountAfter.rows[0].n) === 1, 'exactly one baseline snapshot now exists');
+  const again = await ensureBaselineSnapshot(query, active);
+  check(again === false, 'ensureBaselineSnapshot is idempotent (no-op when a snapshot exists)');
+  await query(`DELETE FROM listing_snapshot WHERE source=$1 AND source_id=$2`, [SOURCE, SID2]);
+  await query(`DELETE FROM listing_lifecycle_event WHERE source=$1 AND source_id=$2`, [SOURCE, SID2]);
+  await query(`DELETE FROM listing WHERE source=$1 AND source_id=$2`, [SOURCE, SID2]);
+
   await purge();
-  console.log(`\n${failed === 0 ? 'PASS — withdrawal snapshot survives listing deletion' : `FAIL — ${failed} assertion(s) failed`}\n`);
+  console.log(`\n${failed === 0 ? 'PASS — withdrawal snapshot survives listing deletion + no-snapshot gap closed' : `FAIL — ${failed} assertion(s) failed`}\n`);
   await pool.end();
   process.exit(failed === 0 ? 0 : 1);
 }
diff --git a/src/server/listing-lifecycle.ts b/src/server/listing-lifecycle.ts
index 3069c27..f9aa71e 100644
--- a/src/server/listing-lifecycle.ts
+++ b/src/server/listing-lifecycle.ts
@@ -152,6 +152,43 @@ async function buildSnapshot(db: QueryFn, listingId: number): Promise<Record<str
   return r.rows[0] ?? null;
 }
 
+/**
+ * TK-10155 (Cody FIX-FIRST gate): guarantee EVERY listing has at least one preserved snapshot,
+ * so preservation is a property of the DESIGN — not a side-effect of "nothing ever deletes a
+ * listing." Without this, a listing whose first-see snapshot hook FAILED (caught+logged) and
+ * that then derives straight to 'active' (e.g. first_seen already > NEW_DAYS) sits in the table
+ * with NO snapshot at all; if a future prune job ever hard-deletes it, its data is lost forever
+ * (the FK just SET-NULLs an event row that points at nothing). The sweep calls this for every
+ * listing: if the listing has ZERO snapshots, capture one 'baseline'. Cheap NOT-EXISTS guard,
+ * idempotent (no-op once any snapshot exists), append-only. Returns true iff it wrote one.
+ */
+export async function ensureBaselineSnapshot(
+  db: QueryFn,
+  listing: LifecycleListing,
+): Promise<boolean> {
+  if (listing.id == null) return false;
+  const exists = await db<{ n: string }>(
+    `SELECT count(*)::text n FROM listing_snapshot WHERE source = $1 AND source_id = $2`,
+    [listing.source, listing.source_id],
+  );
+  if (Number(exists.rows[0]?.n ?? '0') > 0) return false; // already preserved — nothing to do
+  const snap = await buildSnapshot(db, Number(listing.id));
+  if (!snap) return false;
+  await db(
+    `INSERT INTO listing_snapshot
+       (listing_id, source, source_id, address_key, snapshot, captured_reason)
+     VALUES ($1,$2,$3,$4,$5,'baseline')`,
+    [
+      Number(listing.id),
+      listing.source,
+      listing.source_id,
+      computeAddressKey(listing.address, null) || null,
+      JSON.stringify(snap),
+    ],
+  );
+  return true;
+}
+
 export interface RecordLifecycleResult {
   state: LifecycleState;
   prev_state: LifecycleState | null;

← 83c8fb2 TK-10155: lifecycle self-tests + fix withdrawal-snapshot FK  ·  back to Nationalrealestate  ·  auto-data-snapshot: 2026-08-25T03:23:00 (1 data files) — dat b408a56 →