22 lines
1.2 KiB
SQL
22 lines
1.2 KiB
SQL
-- 021/022 are already reserved by timeslot comparison/obligation work in the canonical datastore.
|
|
-- Immutable public catalog snapshots; client runs reference their exact content hash.
|
|
CREATE TABLE assessor_reference_catalogs (
|
|
sha256 text PRIMARY KEY CHECK (sha256 ~ '^[a-f0-9]{64}$'),
|
|
data jsonb NOT NULL CHECK (jsonb_typeof(data) = 'object')
|
|
);
|
|
CREATE TABLE assessor_reference_runs (
|
|
id text PRIMARY KEY CHECK (btrim(id) <> ''),
|
|
project_id text NOT NULL REFERENCES assessor_projects(id) ON DELETE CASCADE,
|
|
assessment_id text NOT NULL REFERENCES assessor_assessments(id) ON DELETE CASCADE,
|
|
scan_run_id text NOT NULL REFERENCES assessor_scan_runs(id) ON DELETE CASCADE,
|
|
created_at timestamptz NOT NULL,
|
|
data jsonb NOT NULL CHECK (jsonb_typeof(data) = 'object')
|
|
);
|
|
CREATE INDEX assessor_reference_runs_scan_idx ON assessor_reference_runs(scan_run_id, created_at DESC, id DESC);
|
|
CREATE INDEX assessor_reference_runs_project_idx ON assessor_reference_runs(project_id);
|
|
CREATE TABLE assessor_reference_run_catalogs (
|
|
run_id text NOT NULL REFERENCES assessor_reference_runs(id) ON DELETE CASCADE,
|
|
catalog_sha256 text NOT NULL REFERENCES assessor_reference_catalogs(sha256),
|
|
PRIMARY KEY (run_id, catalog_sha256)
|
|
);
|