flow_nodes_backfill.sql

BEGIN;

-- Ensure unique mapping per flow/external node
CREATE UNIQUE INDEX IF NOT EXISTS flow_nodes_flow_external_idx
  ON flow_nodes(flow_id, external_id);

-- Temp columns
ALTER TABLE flow_filters  ADD COLUMN IF NOT EXISTS node_id_new UUID;
ALTER TABLE flow_messages ADD COLUMN IF NOT EXISTS node_id_new UUID;
ALTER TABLE flow_kbs      ADD COLUMN IF NOT EXISTS node_id_new UUID;

-- Seed flow_nodes from existing data (idempotent)
INSERT INTO flow_nodes (flow_id, external_id, name, type)
SELECT DISTINCT flow_id, node_id, node_id AS name,
       COALESCE(NULLIF(ff.type::text,''), 'action')::t_flow_nodes_type
FROM flow_filters ff
WHERE node_id IS NOT NULL
ON CONFLICT DO NOTHING;

INSERT INTO flow_nodes (flow_id, external_id, name, type)
SELECT DISTINCT flow_id, node_id, node_id AS name, 'action'::t_flow_nodes_type
FROM flow_messages fm
WHERE node_id IS NOT NULL
ON CONFLICT DO NOTHING;

INSERT INTO flow_nodes (flow_id, external_id, name, type)
SELECT DISTINCT flow_id, node_id, node_id AS name, 'action'::t_flow_nodes_type
FROM flow_kbs fk
WHERE node_id IS NOT NULL
ON CONFLICT DO NOTHING;

-- Backfill new UUID columns
UPDATE flow_filters ff
SET node_id_new = fn.id
FROM flow_nodes fn
WHERE fn.flow_id = ff.flow_id AND fn.external_id = ff.node_id;

UPDATE flow_messages fm
SET node_id_new = fn.id
FROM flow_nodes fn
WHERE fn.flow_id = fm.flow_id AND fn.external_id = fm.node_id;

UPDATE flow_kbs fk
SET node_id_new = fn.id
FROM flow_nodes fn
WHERE fn.flow_id = fk.flow_id AND fn.external_id = fk.node_id;

-- Safety check: halt if anything failed to resolve
DO $$
DECLARE v_missing int;
BEGIN
  SELECT count(*) INTO v_missing FROM flow_filters  WHERE node_id_new IS NULL;
  IF v_missing > 0 THEN RAISE EXCEPTION 'Unresolved flow_filters.node_id_new: %', v_missing; END IF;

  SELECT count(*) INTO v_missing FROM flow_messages WHERE node_id_new IS NULL;
  IF v_missing > 0 THEN RAISE EXCEPTION 'Unresolved flow_messages.node_id_new: %', v_missing; END IF;

  SELECT count(*) INTO v_missing FROM flow_kbs      WHERE node_id_new IS NULL;
  IF v_missing > 0 THEN RAISE EXCEPTION 'Unresolved flow_kbs.node_id_new: %', v_missing; END IF;
END $$;

-- Enforce NOT NULL + FKs
ALTER TABLE flow_filters  ALTER COLUMN node_id_new SET NOT NULL;
ALTER TABLE flow_messages ALTER COLUMN node_id_new SET NOT NULL;
ALTER TABLE flow_kbs      ALTER COLUMN node_id_new SET NOT NULL;

ALTER TABLE flow_filters  ADD CONSTRAINT flow_filters_node_id_new_fkey
  FOREIGN KEY (node_id_new) REFERENCES flow_nodes(id) ON DELETE CASCADE;
ALTER TABLE flow_messages ADD CONSTRAINT flow_messages_node_id_new_fkey
  FOREIGN KEY (node_id_new) REFERENCES flow_nodes(id) ON DELETE CASCADE;
ALTER TABLE flow_kbs      ADD CONSTRAINT flow_kbs_node_id_new_fkey
  FOREIGN KEY (node_id_new) REFERENCES flow_nodes(id) ON DELETE CASCADE;

-- Swap columns
ALTER TABLE flow_filters  DROP COLUMN node_id;
ALTER TABLE flow_messages DROP COLUMN node_id;
ALTER TABLE flow_kbs      DROP COLUMN node_id;

ALTER TABLE flow_filters  RENAME COLUMN node_id_new TO node_id;
ALTER TABLE flow_messages RENAME COLUMN node_id_new TO node_id;
ALTER TABLE flow_kbs      RENAME COLUMN node_id_new TO node_id;

-- Indexes on new columns
CREATE INDEX IF NOT EXISTS flow_filters_node_id_idx  ON flow_filters(node_id);
CREATE INDEX IF NOT EXISTS flow_messages_node_id_idx ON flow_messages(node_id);
CREATE INDEX IF NOT EXISTS flow_kbs_node_id_idx      ON flow_kbs(node_id);

COMMIT;