source/sphenixprod/create_production_overview.sql

0001 -- Table and trigger for the production overview dashboard.
0002 -- Rows are auto-registered on first INSERT into production_jobs.
0003 -- Curators flip `active = true` to make a campaign visible on the dashboard.
0004
0005 CREATE TABLE IF NOT EXISTS production_overview (
0006     id               SERIAL                   PRIMARY KEY,
0007     tag              TEXT                     NOT NULL,
0008     production_type  TEXT                     NOT NULL,  -- 'tracking' or 'calo'
0009     active           BOOLEAN                  NOT NULL DEFAULT false,
0010     display_name     TEXT,
0011     priority         INT                      NOT NULL DEFAULT 0,
0012     started_at       TIMESTAMP WITH TIME ZONE,
0013     expected_end     TIMESTAMP WITH TIME ZONE,
0014     notes            TEXT,
0015     registered_at    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
0016     UNIQUE (tag, production_type),
0017     CHECK (production_type IN ('tracking', 'calo'))
0018 );
0019
0020 COMMENT ON TABLE  production_overview                  IS 'Curator-managed registry of production campaigns shown on the monitoring dashboard.';
0021 COMMENT ON COLUMN production_overview.tag              IS 'Matches tag in production_jobs.';
0022 COMMENT ON COLUMN production_overview.production_type  IS 'tracking or calo — one tag may have both.';
0023 COMMENT ON COLUMN production_overview.active           IS 'Set true by a curator to show this campaign on the dashboard.';
0024 COMMENT ON COLUMN production_overview.display_name     IS 'Optional human-readable label for dashboard panel titles.';
0025 COMMENT ON COLUMN production_overview.priority         IS 'Display order; lower numbers appear first.';
0026 COMMENT ON COLUMN production_overview.started_at       IS 'Campaign start time for ETA / progress panels.';
0027 COMMENT ON COLUMN production_overview.expected_end     IS 'Expected completion time for ETA / progress panels.';
0028 COMMENT ON COLUMN production_overview.notes            IS 'Free-text curator annotation surfaced as a dashboard tooltip.';
0029 COMMENT ON COLUMN production_overview.registered_at    IS 'Timestamp of the first job that triggered auto-registration.';
0030
0031 CREATE INDEX IF NOT EXISTS production_overview_active_type_idx
0032     ON production_overview (active, production_type);
0033
0034 -- ---------------------------------------------------------------------------
0035
0036 CREATE OR REPLACE FUNCTION auto_register_production_overview()
0037 RETURNS TRIGGER AS $$
0038 DECLARE
0039     v_prod_type TEXT;
0040 BEGIN
0041     v_prod_type := CASE
0042         WHEN NEW.dsttype LIKE 'DST_STREAMING_EVENT%' OR NEW.dsttype LIKE 'DST_TRKR_%'
0043             THEN 'tracking'
0044         WHEN NEW.dsttype LIKE 'DST_TRIGGERED_EVENT%'
0045           OR NEW.dsttype IN ('DST_CALOFITTING', 'DST_CALO', 'DST_JETS', 'DST_JETCALO')
0046             THEN 'calo'
0047         ELSE NULL
0048     END;
0049
0050     IF v_prod_type IS NULL THEN
0051         RAISE NOTICE
0052             'auto_register_production_overview: unrecognised dsttype "%" (tag=%, rulename=%) — skipping registration',
0053             NEW.dsttype, NEW.tag, NEW.rulename;
0054         RETURN NEW;
0055     END IF;
0056
0057     INSERT INTO production_overview (tag, production_type)
0058     VALUES (NEW.tag, v_prod_type)
0059     ON CONFLICT (tag, production_type) DO NOTHING;
0060
0061     RETURN NEW;
0062 END;
0063 $$ LANGUAGE plpgsql;
0064
0065 CREATE OR REPLACE TRIGGER trg_auto_register_overview
0066     AFTER INSERT ON production_jobs
0067     FOR EACH ROW EXECUTE FUNCTION auto_register_production_overview();