ca_contractors: date-everything provenance (source_as_of, cslb_last_update) + auto updated_at trigger (TK-10488)

0ef7a6b7da32f8eab670c0a2e6aa7152ecde65b3 · 2026-08-12 10:27:55 -0700 · Steve Abrams

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

Files touched

A db/migrations/019_ca_contractors_dating.sql

Diff

commit 0ef7a6b7da32f8eab670c0a2e6aa7152ecde65b3
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed Aug 12 10:27:55 2026 -0700

    ca_contractors: date-everything provenance (source_as_of, cslb_last_update) + auto updated_at trigger (TK-10488)
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 db/migrations/019_ca_contractors_dating.sql | 32 +++++++++++++++++++++++++++++
 1 file changed, 32 insertions(+)

diff --git a/db/migrations/019_ca_contractors_dating.sql b/db/migrations/019_ca_contractors_dating.sql
new file mode 100644
index 0000000..3cd7244
--- /dev/null
+++ b/db/migrations/019_ca_contractors_dating.sql
@@ -0,0 +1,32 @@
+-- TK-10488: "date everything so we know when things happen in each record" (Steve, 2026-08-12).
+-- The registry already has created_at + updated_at (OUR ingest lifecycle) and the license
+-- issue/reissue/expire dates. This adds the SOURCE-side provenance dates so every row also
+-- records CSLB's own timeline + the file vintage, and makes updated_at self-maintaining so
+-- "when did this record last change" is always trustworthy (not dependent on the loader).
+-- No BEGIN/COMMIT here — migrate.ts wraps each file in a transaction.
+
+ALTER TABLE ca_contractors
+  ADD COLUMN IF NOT EXISTS cslb_last_update DATE,   -- CSLB's per-record LastUpdate (file col 2)
+  ADD COLUMN IF NOT EXISTS source_as_of      DATE;  -- CSLB file vintage ("Updated as of M/D/YYYY")
+
+COMMENT ON COLUMN ca_contractors.created_at       IS 'When THIS row was first ingested into the registry';
+COMMENT ON COLUMN ca_contractors.updated_at       IS 'When THIS row was last modified (auto-maintained by trigger)';
+COMMENT ON COLUMN ca_contractors.cslb_last_update IS 'CSLB''s own last-change date for the license (file LastUpdate col)';
+COMMENT ON COLUMN ca_contractors.source_as_of     IS 'Vintage of the CSLB master file this row came from';
+
+-- Self-maintaining updated_at: any UPDATE stamps the row so change-time is always honest.
+CREATE OR REPLACE FUNCTION ca_contractors_touch_updated_at() RETURNS trigger AS $$
+BEGIN
+  NEW.updated_at := now();
+  RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS trg_ca_contractors_touch ON ca_contractors;
+CREATE TRIGGER trg_ca_contractors_touch
+  BEFORE UPDATE ON ca_contractors
+  FOR EACH ROW EXECUTE FUNCTION ca_contractors_touch_updated_at();
+
+-- Fast "what changed / what's freshest" reads for admin surfaces + freshness canaries.
+CREATE INDEX IF NOT EXISTS idx_ca_contractors_updated  ON ca_contractors (updated_at DESC);
+CREATE INDEX IF NOT EXISTS idx_ca_contractors_cslb_upd ON ca_contractors (cslb_last_update DESC);

← c9daafc usre: shared CSLB contractor API (search/detail/match) + int · back to Nationalrealestate · contractors API: default license_status filter to CLEAR (CSL 2f6c9cd →