← back to Ventura Claw Leads

yolo tick 7: bug fix — drop DEFAULT now() from business_interest.delivered_at

d4d9dd22fc28c94e3fd72b233cf8e40dcadf71d1 · 2026-05-06 17:44:18 -0700 · Steve Abrams

The original 001_init.sql migration set delivered_at TIMESTAMPTZ DEFAULT now()
on business_interest. Surfaced when v0.2 #6 CSV export shipped: every newly
captured lead showed Delivered=created_at even though lead-delivery cron
hadn't actually run.

Lead-delivery cron is unaffected (gates on delivery_msg_id IS NULL, which is
the correct sentinel). But every read path (CSV export, admin inbox UI,
billing reconciliation) was misled.

Fix:
  ALTER TABLE business_interest ALTER COLUMN delivered_at DROP DEFAULT;
  UPDATE business_interest SET delivered_at = NULL
   WHERE delivery_msg_id IS NULL AND delivered_at IS NOT NULL;

Verified locally + applied on prod. Now: insert without delivered_at →
delivered_at = NULL until cron runs and writes it.

Files touched

• A db/migrations/005_fix_delivered_at_default.sql

Diff

commit d4d9dd22fc28c94e3fd72b233cf8e40dcadf71d1
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed May 6 17:44:18 2026 -0700

    yolo tick 7: bug fix — drop DEFAULT now() from business_interest.delivered_at
    
    The original 001_init.sql migration set delivered_at TIMESTAMPTZ DEFAULT now()
    on business_interest. Surfaced when v0.2 #6 CSV export shipped: every newly
    captured lead showed Delivered=created_at even though lead-delivery cron
    hadn't actually run.
    
    Lead-delivery cron is unaffected (gates on delivery_msg_id IS NULL, which is
    the correct sentinel). But every read path (CSV export, admin inbox UI,
    billing reconciliation) was misled.
    
    Fix:
      ALTER TABLE business_interest ALTER COLUMN delivered_at DROP DEFAULT;
      UPDATE business_interest SET delivered_at = NULL
       WHERE delivery_msg_id IS NULL AND delivered_at IS NOT NULL;
    
    Verified locally + applied on prod. Now: insert without delivered_at →
    delivered_at = NULL until cron runs and writes it.
---
 db/migrations/005_fix_delivered_at_default.sql | 16 ++++++++++++++++
 1 file changed, 16 insertions(+)

diff --git a/db/migrations/005_fix_delivered_at_default.sql b/db/migrations/005_fix_delivered_at_default.sql
new file mode 100644
index 0000000..a65ebef
--- /dev/null
+++ b/db/migrations/005_fix_delivered_at_default.sql
@@ -0,0 +1,16 @@
+-- Bug fix: business_interest.delivered_at had DEFAULT now() in 001_init.sql,
+-- so every newly-captured lead had delivered_at populated at capture time
+-- with the same value as created_at — even though the lead-delivery cron
+-- hadn't run yet. The cron's gate is `delivery_msg_id IS NULL` (which is
+-- correct), but every other consumer of the field (CSV export, admin
+-- inbox UI, billing reconciliation) saw misleading "Delivered" timestamps
+-- on undelivered rows.
+--
+-- Fix: drop the default + backfill NULL on rows that have no delivery_msg_id.
+
+ALTER TABLE business_interest ALTER COLUMN delivered_at DROP DEFAULT;
+
+UPDATE business_interest
+   SET delivered_at = NULL
+ WHERE delivery_msg_id IS NULL
+   AND delivered_at IS NOT NULL;

← 81d7dec yolo tick 6: /admin/leads.csv export + mobile audit clean  ·  back to Ventura Claw Leads  ·  yolo tick 8: vertical-themed hero panels (no external image 1a8b9b4 →