dataflow/database/migrate_overrides_column.sql

--
-- Migration: add overrides column to records
--
-- Separates the three data layers:
--   data        — original import values, never mutated
--   transformed — rule/mapping output fields only (delta)
--   overrides   — manual user overrides (highest precedence)
--
-- Consumers merge as: data || COALESCE(transformed,'{}') || COALESCE(overrides,'{}')
--
-- Safe to run multiple times (IF NOT EXISTS guards).
--

SET search_path TO dataflow, public;

-- 1. Add overrides column
ALTER TABLE dataflow.records
    ADD COLUMN IF NOT EXISTS overrides JSONB;

-- 2. Add partial GIN index (only indexes rows that have overrides)
CREATE INDEX IF NOT EXISTS idx_records_overrides
    ON dataflow.records USING gin(overrides)
    WHERE overrides IS NOT NULL;

-- 3. Redeploy functions (CREATE OR REPLACE — non-destructive)
\i functions.sql

-- 4. Reprocess all sources to strip stale data keys from transformed
--    (apply_transformations now writes only rule additions, not data || additions)
DO $$
DECLARE
    src TEXT;
    result JSON;
BEGIN
    FOR src IN SELECT name FROM dataflow.sources ORDER BY name LOOP
        SELECT dataflow.reprocess_records(src) INTO result;
        RAISE NOTICE 'Reprocessed %: %', src, result;
    END LOOP;
END;
$$;