`scripts/db/migrations/20260731_broker_firm_history.sql`

78 lines
-- Migration: broker_firm_history
-- Tracks a broker's tenure at each firm (current + past).
-- Idempotent: safe to re-run at any time.
-- Run with:
--   psql -h /tmp -d cre -f scripts/db/migrations/20260731_broker_firm_history.sql
-- On Kamatera (Steve-gated deploy):
--   psql -h /tmp -U <user> -d cre -f scripts/db/migrations/20260731_broker_firm_history.sql

BEGIN;

-- ── Table ─────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS broker_firm_history (
  id          serial        PRIMARY KEY,
  broker_id   int           NOT NULL REFERENCES broker(id) ON DELETE CASCADE,
  firm_id     int           REFERENCES firm(id),
  firm_name   text,                          -- denorm for rows where firm is not in firm table
  title       text,
  start_date  date,
  end_date    date,
  is_current  boolean       NOT NULL DEFAULT false,
  source      text,
  source_url  text,
  tier        int,
  found_at    timestamptz   DEFAULT now()
);

-- ── Indexes ───────────────────────────────────────────────────────────────────
CREATE INDEX IF NOT EXISTS idx_bfh_broker  ON broker_firm_history(broker_id);
CREATE INDEX IF NOT EXISTS idx_bfh_firm    ON broker_firm_history(firm_id);

-- ── Uniqueness guard (prevents duplicate backfill rows) ───────────────────────
-- One active (is_current=true) row per broker+firm, plus any number of past rows
-- identified by start_date. NULLs in a UNIQUE index are not equal in PG, so
-- past rows without a start_date would collide; use a partial unique index for
-- the current row only — that's all we need to make the backfill idempotent.
CREATE UNIQUE INDEX IF NOT EXISTS idx_bfh_current_uniq
  ON broker_firm_history(broker_id, firm_id)
  WHERE is_current = true;

-- firm_id can be NULL for enriched rows whose firm isn't in the firm table yet.
-- NULLs are distinct in a UNIQUE index, so the guard above does NOT stop duplicate
-- current rows for the same broker+firm when firm_id is NULL — cover that by firm_name.
CREATE UNIQUE INDEX IF NOT EXISTS idx_bfh_current_nullfirm_uniq
  ON broker_firm_history(broker_id, firm_name)
  WHERE is_current = true AND firm_id IS NULL;

-- ── Backfill: seed current-firm rows from broker table ────────────────────────
-- Every broker with a firm_id gets one is_current=true row.
-- ON CONFLICT DO NOTHING makes this re-runnable.
INSERT INTO broker_firm_history (broker_id, firm_id, firm_name, is_current, source)
SELECT
  b.id          AS broker_id,
  b.firm_id,
  f.name        AS firm_name,
  true          AS is_current,
  'backfill'    AS source
FROM broker b
JOIN firm f ON f.id = b.firm_id
WHERE b.firm_id IS NOT NULL
ON CONFLICT DO NOTHING;

-- ── Grants (hygiene; the app connects as the table owner, this is for dw_admin) ─
DO $$ BEGIN
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'dw_admin') THEN
    GRANT SELECT, INSERT, UPDATE, DELETE ON broker_firm_history TO dw_admin;
    GRANT USAGE, SELECT ON SEQUENCE broker_firm_history_id_seq TO dw_admin;
  END IF;
END $$;

COMMIT;

-- ── Row-count report (run after commit) ──────────────────────────────────────
SELECT
  count(*)                               AS total_rows,
  count(*) FILTER (WHERE is_current)    AS current_rows,
  count(*) FILTER (WHERE NOT is_current) AS past_rows
FROM broker_firm_history;