90 lines
3.7 KiB
SQL
90 lines
3.7 KiB
SQL
-- Additive Realisierung von RESULT_CONTRACT 1.0.0 auf assessor_finding_occurrences.
|
|
-- Siehe docs/RESULT_CONTRACT.md ("Abweichungen der ASSESSOR-Realisierung") und
|
|
-- src/domain/result-contract-mapping.ts (RESULT_CONTRACT_MAPPING_VERSION 1.0.0),
|
|
-- dessen Ableitungslogik dieser Backfill in SQL nachbildet.
|
|
--
|
|
-- result_type und is_ioe sind nie aus einem rohen Quellstring abgeleitet,
|
|
-- sondern ausschließlich aus tool_id sowie den bereits normalisierten Spalten
|
|
-- outcome_normalized / technical_execution_state (Migration 007). Es entsteht
|
|
-- keine neue Haupttabelle; result_compliance/result_deviation werden nicht
|
|
-- vorgebaut, da beide noch keinen Producer haben.
|
|
|
|
ALTER TABLE assessor_finding_occurrences
|
|
ADD COLUMN result_type text,
|
|
ADD COLUMN is_ioe boolean,
|
|
ADD COLUMN ioe_rule_id text,
|
|
ADD COLUMN ioe_rule_version text,
|
|
ADD COLUMN ioe_derivation_method text,
|
|
ADD COLUMN exposure_state text;
|
|
|
|
-- result_type: ausschließlich von tool_id abhängig (Quellen-Mapping-Tabelle in
|
|
-- docs/RESULT_CONTRACT.md). Unbekannte oder generische Werkzeuge bleiben
|
|
-- 'unknown', analog zum generischen Fallback von ASSESSOR.
|
|
UPDATE assessor_finding_occurrences
|
|
SET result_type = CASE lower(btrim(tool_id))
|
|
WHEN 'pingcastle' THEN 'exposure_finding'
|
|
WHEN 'purple-knight' THEN 'exposure_finding'
|
|
WHEN 'maester' THEN 'exposure_finding'
|
|
WHEN 'cis-cat' THEN 'compliance_check'
|
|
ELSE 'unknown'
|
|
END;
|
|
|
|
-- exposure_state: nur für exposure_finding gesetzt, aus outcome_normalized
|
|
-- abgeleitet (vgl. deriveExposureState).
|
|
UPDATE assessor_finding_occurrences
|
|
SET exposure_state = CASE
|
|
WHEN result_type <> 'exposure_finding' THEN NULL
|
|
ELSE CASE outcome_normalized
|
|
WHEN 'fail' THEN 'detected'
|
|
WHEN 'pass' THEN 'not_detected'
|
|
WHEN 'not_evaluated' THEN 'not_evaluated'
|
|
WHEN 'informational' THEN 'not_evaluated'
|
|
WHEN 'not_run' THEN 'not_evaluated'
|
|
WHEN 'not_applicable' THEN 'not_evaluated'
|
|
WHEN 'execution_error' THEN 'execution_error'
|
|
ELSE 'unknown'
|
|
END
|
|
END;
|
|
|
|
-- is_ioe: ausschließlich exposure_finding + exposure_state=detected -> true.
|
|
-- Niemals per Default true für compliance_check oder policy_deviation
|
|
-- (RESULT_CONTRACT.md, Invariante 3).
|
|
UPDATE assessor_finding_occurrences
|
|
SET
|
|
is_ioe = (result_type = 'exposure_finding' AND exposure_state = 'detected'),
|
|
ioe_rule_id = 'result-contract-ioe-default',
|
|
ioe_rule_version = '1.0.0',
|
|
ioe_derivation_method = 'exposure-state-default';
|
|
|
|
ALTER TABLE assessor_finding_occurrences
|
|
ALTER COLUMN result_type SET DEFAULT 'unknown',
|
|
ALTER COLUMN result_type SET NOT NULL,
|
|
ALTER COLUMN is_ioe SET DEFAULT false,
|
|
ALTER COLUMN is_ioe SET NOT NULL,
|
|
ADD CONSTRAINT assessor_occurrences_result_type_check CHECK (
|
|
result_type IN (
|
|
'exposure_finding', 'compliance_check', 'policy_deviation',
|
|
'informational', 'unknown'
|
|
)
|
|
),
|
|
ADD CONSTRAINT assessor_occurrences_exposure_state_check CHECK (
|
|
exposure_state IS NULL OR exposure_state IN (
|
|
'detected', 'not_detected', 'not_evaluated', 'execution_error', 'unknown'
|
|
)
|
|
),
|
|
ADD CONSTRAINT assessor_occurrences_exposure_state_type_check CHECK (
|
|
exposure_state IS NULL OR result_type = 'exposure_finding'
|
|
),
|
|
ADD CONSTRAINT assessor_occurrences_ioe_default_check CHECK (
|
|
NOT (is_ioe AND result_type NOT IN ('exposure_finding'))
|
|
);
|
|
|
|
CREATE INDEX assessor_occurrences_result_type_idx
|
|
ON assessor_finding_occurrences(result_type);
|
|
CREATE INDEX assessor_occurrences_is_ioe_idx
|
|
ON assessor_finding_occurrences(is_ioe);
|
|
CREATE INDEX assessor_occurrences_ioe_severity_idx
|
|
ON assessor_finding_occurrences(is_ioe, severity);
|
|
CREATE INDEX assessor_occurrences_exposure_state_idx
|
|
ON assessor_finding_occurrences(exposure_state) WHERE exposure_state IS NOT NULL;
|