`db/migrations/019_ca_contractors_dating.sql`

33 lines
-- 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);