64 lines
3.8 KiB
SQL
64 lines
3.8 KiB
SQL
CREATE TABLE assessor_policy_evaluation_runs (
|
|
id text PRIMARY KEY CHECK (btrim(id) <> ''),
|
|
project_id text NOT NULL REFERENCES assessor_projects(id) ON DELETE CASCADE,
|
|
aggregation_run_id text NOT NULL REFERENCES assessor_policy_snapshot_aggregation_runs(id) ON DELETE CASCADE,
|
|
environment_profile_id text NOT NULL,
|
|
evaluator_version text NOT NULL CHECK (btrim(evaluator_version) <> ''),
|
|
comparator_catalog_version text NOT NULL CHECK (btrim(comparator_catalog_version) <> ''),
|
|
dependency_rules_version text NOT NULL CHECK (btrim(dependency_rules_version) <> ''),
|
|
risk_rules_version text NOT NULL CHECK (btrim(risk_rules_version) <> ''),
|
|
run_status text NOT NULL CHECK (run_status IN ('complete', 'partial', 'blocked')),
|
|
projected_finding_count integer NOT NULL DEFAULT 0 CHECK (projected_finding_count = 0),
|
|
started_at timestamptz NOT NULL,
|
|
completed_at timestamptz NOT NULL CHECK (completed_at >= started_at),
|
|
data jsonb NOT NULL CHECK (jsonb_typeof(data) = 'object'),
|
|
FOREIGN KEY (project_id, environment_profile_id)
|
|
REFERENCES assessor_environment_profiles(project_id, id)
|
|
);
|
|
|
|
CREATE INDEX assessor_policy_evaluation_runs_project_idx
|
|
ON assessor_policy_evaluation_runs(project_id, completed_at, id);
|
|
CREATE INDEX assessor_policy_evaluation_runs_aggregation_idx
|
|
ON assessor_policy_evaluation_runs(aggregation_run_id, completed_at, id);
|
|
|
|
CREATE TABLE assessor_policy_evaluation_baseline_sources (
|
|
evaluation_run_id text NOT NULL REFERENCES assessor_policy_evaluation_runs(id) ON DELETE CASCADE,
|
|
baseline_analysis_id text NOT NULL REFERENCES assessor_microsoft_security_baseline_analyses(id) ON DELETE RESTRICT,
|
|
baseline_id text NOT NULL REFERENCES assessor_microsoft_security_baselines(id) ON DELETE RESTRICT,
|
|
PRIMARY KEY (evaluation_run_id, baseline_analysis_id)
|
|
);
|
|
|
|
CREATE INDEX assessor_policy_evaluation_baseline_source_idx
|
|
ON assessor_policy_evaluation_baseline_sources(baseline_analysis_id, evaluation_run_id);
|
|
|
|
CREATE TABLE assessor_policy_evaluation_results (
|
|
id text PRIMARY KEY CHECK (btrim(id) <> ''),
|
|
evaluation_run_id text NOT NULL REFERENCES assessor_policy_evaluation_runs(id) ON DELETE CASCADE,
|
|
aggregation_state_id text NOT NULL REFERENCES assessor_policy_snapshot_states(id) ON DELETE CASCADE,
|
|
identity_id text NOT NULL REFERENCES assessor_policy_identities(id) ON DELETE RESTRICT,
|
|
classification text NOT NULL CHECK (classification IN ('configured_match', 'confirmed_configured_deviation', 'review_required')),
|
|
comparator_kind text NOT NULL CHECK (comparator_kind IN ('equality', 'minimum', 'maximum', 'enum', 'bitmask', 'set', 'audit-flags')),
|
|
finding_eligible boolean NOT NULL,
|
|
effective_state text NOT NULL DEFAULT 'unknown' CHECK (effective_state = 'unknown'),
|
|
data jsonb NOT NULL CHECK (jsonb_typeof(data) = 'object'),
|
|
UNIQUE (evaluation_run_id, aggregation_state_id),
|
|
CHECK (NOT finding_eligible OR classification = 'confirmed_configured_deviation')
|
|
);
|
|
|
|
CREATE INDEX assessor_policy_evaluation_results_run_idx
|
|
ON assessor_policy_evaluation_results(evaluation_run_id, classification, id);
|
|
CREATE INDEX assessor_policy_evaluation_results_identity_idx
|
|
ON assessor_policy_evaluation_results(identity_id, evaluation_run_id);
|
|
|
|
CREATE TABLE assessor_policy_evaluation_evidence (
|
|
id text PRIMARY KEY CHECK (btrim(id) <> ''),
|
|
evaluation_result_id text NOT NULL REFERENCES assessor_policy_evaluation_results(id) ON DELETE CASCADE,
|
|
evidence_kind text NOT NULL CHECK (evidence_kind IN ('customer-observation', 'gpo-backup', 'baseline-setting', 'baseline-analysis', 'environment-context')),
|
|
source_id text NOT NULL CHECK (btrim(source_id) <> ''),
|
|
data jsonb NOT NULL CHECK (jsonb_typeof(data) = 'object'),
|
|
UNIQUE (evaluation_result_id, evidence_kind, source_id)
|
|
);
|
|
|
|
CREATE INDEX assessor_policy_evaluation_evidence_result_idx
|
|
ON assessor_policy_evaluation_evidence(evaluation_result_id, evidence_kind, id);
|