source/sphenixprod/create_production_run_reports.sql

0001 -- SQL draft for migrating generate_report.py CSV output into the production DB.
0002 -- One row represents one run-level report snapshot for one production rule.
0003 -- Intended initial source: generate_report.py for raw/event-combiner rules.
0004
0005 DO $$
0006 BEGIN
0007     IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'run_report_status') THEN
0008         CREATE TYPE run_report_status AS ENUM (
0009             'complete',       -- no missing events and no other report concerns
0010             'questionable',   -- no missing events, but one or more non-event concerns exist
0011             'incomplete'      -- missing events remain after expected bookkeeping allowances
0012         );
0013     END IF;
0014 END$$;
0015
0016 CREATE TABLE IF NOT EXISTS production_run_reports (
0017     id                              SERIAL                   PRIMARY KEY,
0018
0019     rule_name                       TEXT                     NOT NULL,
0020     runnumber                       INT                      NOT NULL,
0021     tag                             TEXT,
0022     dataset                         TEXT,
0023     dsttype                         TEXT,
0024
0025     possible_daqhosts               INT                      NOT NULL DEFAULT 0,
0026     total_daqhosts                  INT                      NOT NULL DEFAULT 0,
0027     missing_daqhosts                INT                      NOT NULL DEFAULT 0,
0028
0029     possible_segments               INT                      NOT NULL DEFAULT 0,
0030     total_segments                  INT                      NOT NULL DEFAULT 0,
0031     missing_segments                INT                      NOT NULL DEFAULT 0,
0032
0033     possible_events                 BIGINT                   NOT NULL DEFAULT 0,
0034     total_events                    BIGINT                   NOT NULL DEFAULT 0,
0035     expected_skipped_events         BIGINT                   NOT NULL DEFAULT 0,
0036     missing_events                  BIGINT                   NOT NULL DEFAULT 0,
0037
0038     error_codes                     INT[]                    NOT NULL DEFAULT '{}',
0039     incomplete_reasons              TEXT[]                   NOT NULL DEFAULT '{}',
0040     status                          run_report_status        NOT NULL,
0041
0042     report_source                   TEXT                     NOT NULL DEFAULT 'generate_report.py',
0043     generated_at                    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
0044     prod_as_of                      TIMESTAMP WITH TIME ZONE,
0045     raw_as_of                       TIMESTAMP WITH TIME ZONE,
0046     datasets_as_of                  TIMESTAMP WITH TIME ZONE,
0047     files_as_of                     TIMESTAMP WITH TIME ZONE,
0048     first_created_at                TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
0049     last_updated_at                 TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
0050     notes                           TEXT,
0051
0052     UNIQUE (rule_name, runnumber, generated_at),
0053
0054     CHECK (possible_daqhosts >= 0),
0055     CHECK (total_daqhosts >= 0),
0056     CHECK (missing_daqhosts >= 0),
0057     CHECK (possible_segments >= 0),
0058     CHECK (total_segments >= 0),
0059     CHECK (missing_segments >= 0),
0060     CHECK (possible_events >= 0),
0061     CHECK (total_events >= 0),
0062     CHECK (expected_skipped_events >= 0),
0063     CHECK (missing_events >= 0)
0064 );
0065
0066 COMMENT ON TABLE production_run_reports IS 'Run-level production completeness reports, initially mirroring generate_report.py CSV output.';
0067 COMMENT ON COLUMN production_run_reports.id IS 'Unique identifier for this report row.';
0068 COMMENT ON COLUMN production_run_reports.rule_name IS 'Production rule name from the YAML configuration; matches generate_report.py --rulename.';
0069 COMMENT ON COLUMN production_run_reports.runnumber IS 'DAQ run number summarized by this row.';
0070 COMMENT ON COLUMN production_run_reports.tag IS 'Output production triplet, e.g. ana561_2025p000_v000; nullable in the draft until the writer supplies it.';
0071 COMMENT ON COLUMN production_run_reports.dataset IS 'Dataset identifier, e.g. run3auau, run3pp, or run3oo.';
0072 COMMENT ON COLUMN production_run_reports.dsttype IS 'Output DST type or template represented by this report row.';
0073 COMMENT ON COLUMN production_run_reports.possible_daqhosts IS 'Number of daqhosts expected for this run from the raw DB universe.';
0074 COMMENT ON COLUMN production_run_reports.total_daqhosts IS 'Number of expected daqhosts with matching FileCatalog output coverage.';
0075 COMMENT ON COLUMN production_run_reports.missing_daqhosts IS 'Expected daqhosts without matching FileCatalog output coverage.';
0076 COMMENT ON COLUMN production_run_reports.possible_segments IS 'Expected number of output segments per daqhost, usually ceil(eventsinrun / neventsper).';
0077 COMMENT ON COLUMN production_run_reports.total_segments IS 'Number of segments considered complete per daqhost after applying report logic.';
0078 COMMENT ON COLUMN production_run_reports.missing_segments IS 'Expected per-daqhost segments not considered complete.';
0079 COMMENT ON COLUMN production_run_reports.possible_events IS 'Expected DST-entry event total across daqhosts. One physical event contributes once per daqhost.';
0080 COMMENT ON COLUMN production_run_reports.total_events IS 'Observed FileCatalog event total across daqhost-specific outputs.';
0081 COMMENT ON COLUMN production_run_reports.expected_skipped_events IS 'Known bookkeeping allowance, currently two skipped events per possible daqhost for event-combiner output.';
0082 COMMENT ON COLUMN production_run_reports.missing_events IS 'Event deficit after subtracting expected_skipped_events; this is the hard gate for incomplete status.';
0083 COMMENT ON COLUMN production_run_reports.error_codes IS 'Distinct nonzero production_jobs ExitCode values encountered for this run.';
0084 COMMENT ON COLUMN production_run_reports.incomplete_reasons IS 'Machine-readable concern labels such as missing_daqhosts, missing_segments, low_event_ratio, or error_codes.';
0085 COMMENT ON COLUMN production_run_reports.status IS 'complete, questionable, or incomplete. Incomplete requires missing_events > 0.';
0086 COMMENT ON COLUMN production_run_reports.report_source IS 'Tool or process that generated this row.';
0087 COMMENT ON COLUMN production_run_reports.generated_at IS 'Timestamp when this report content was generated by the report producer.';
0088 COMMENT ON COLUMN production_run_reports.prod_as_of IS 'Optional watermark for production DB state, currently production_jobs rows used for nonzero ExitCode reporting.';
0089 COMMENT ON COLUMN production_run_reports.raw_as_of IS 'Optional watermark for raw DB datasets rows used for expected daqhost and raw availability checks.';
0090 COMMENT ON COLUMN production_run_reports.datasets_as_of IS 'Optional watermark for FileCatalog datasets rows used for output dsttype and lastevent coverage.';
0091 COMMENT ON COLUMN production_run_reports.files_as_of IS 'Optional watermark for FileCatalog files rows; currently reserved for future report logic.';
0092 COMMENT ON COLUMN production_run_reports.first_created_at IS 'Timestamp when this database row was first inserted.';
0093 COMMENT ON COLUMN production_run_reports.last_updated_at IS 'Timestamp when this database row was last updated by an upsert or refresh.';
0094 COMMENT ON COLUMN production_run_reports.notes IS 'Free-text operator notes or migration comments.';
0095
0096 CREATE INDEX IF NOT EXISTS production_run_reports_rule_run_idx
0097     ON production_run_reports (rule_name, runnumber);
0098
0099 CREATE INDEX IF NOT EXISTS production_run_reports_status_idx
0100     ON production_run_reports (status);
0101
0102 CREATE INDEX IF NOT EXISTS production_run_reports_tag_dataset_dsttype_idx
0103     ON production_run_reports (tag, dataset, dsttype);