108 lines
4.1 KiB
PL/PgSQL
108 lines
4.1 KiB
PL/PgSQL
-- Migration: 001_CreateVerdictAttestations
|
|
-- Description: Create verdict_attestations table for storing signed policy verdict attestations
|
|
-- Author: Evidence Locker Guild
|
|
-- Date: 2025-12-23
|
|
|
|
-- Create schema if not exists
|
|
CREATE SCHEMA IF NOT EXISTS evidence_locker;
|
|
|
|
-- Create verdict_attestations table
|
|
CREATE TABLE IF NOT EXISTS evidence_locker.verdict_attestations (
|
|
verdict_id TEXT PRIMARY KEY,
|
|
tenant_id TEXT NOT NULL,
|
|
run_id TEXT NOT NULL,
|
|
policy_id TEXT NOT NULL,
|
|
policy_version INTEGER NOT NULL,
|
|
finding_id TEXT NOT NULL,
|
|
verdict_status TEXT NOT NULL CHECK (verdict_status IN ('passed', 'warned', 'blocked', 'quieted', 'ignored')),
|
|
verdict_severity TEXT NOT NULL CHECK (verdict_severity IN ('critical', 'high', 'medium', 'low', 'info', 'none')),
|
|
verdict_score NUMERIC(5, 2) NOT NULL CHECK (verdict_score >= 0 AND verdict_score <= 100),
|
|
evaluated_at TIMESTAMPTZ NOT NULL,
|
|
envelope JSONB NOT NULL,
|
|
predicate_digest TEXT NOT NULL,
|
|
determinism_hash TEXT,
|
|
rekor_log_index BIGINT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Create indexes for common query patterns
|
|
CREATE INDEX IF NOT EXISTS idx_verdict_attestations_run
|
|
ON evidence_locker.verdict_attestations(run_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_verdict_attestations_finding
|
|
ON evidence_locker.verdict_attestations(finding_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_verdict_attestations_tenant_evaluated
|
|
ON evidence_locker.verdict_attestations(tenant_id, evaluated_at DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_verdict_attestations_tenant_status
|
|
ON evidence_locker.verdict_attestations(tenant_id, verdict_status);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_verdict_attestations_tenant_severity
|
|
ON evidence_locker.verdict_attestations(tenant_id, verdict_severity);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_verdict_attestations_policy
|
|
ON evidence_locker.verdict_attestations(policy_id, policy_version);
|
|
|
|
-- Create GIN index for JSONB envelope queries
|
|
CREATE INDEX IF NOT EXISTS idx_verdict_attestations_envelope
|
|
ON evidence_locker.verdict_attestations USING gin(envelope);
|
|
|
|
-- Create function for updating updated_at timestamp
|
|
CREATE OR REPLACE FUNCTION evidence_locker.update_verdict_attestations_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Create trigger to auto-update updated_at
|
|
CREATE TRIGGER trigger_verdict_attestations_updated_at
|
|
BEFORE UPDATE ON evidence_locker.verdict_attestations
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION evidence_locker.update_verdict_attestations_updated_at();
|
|
|
|
-- Create view for verdict summary (without full envelope)
|
|
CREATE OR REPLACE VIEW evidence_locker.verdict_attestations_summary AS
|
|
SELECT
|
|
verdict_id,
|
|
tenant_id,
|
|
run_id,
|
|
policy_id,
|
|
policy_version,
|
|
finding_id,
|
|
verdict_status,
|
|
verdict_severity,
|
|
verdict_score,
|
|
evaluated_at,
|
|
predicate_digest,
|
|
determinism_hash,
|
|
rekor_log_index,
|
|
created_at
|
|
FROM evidence_locker.verdict_attestations;
|
|
|
|
-- Grant permissions (adjust as needed)
|
|
-- GRANT SELECT, INSERT ON evidence_locker.verdict_attestations TO evidence_locker_app;
|
|
-- GRANT SELECT ON evidence_locker.verdict_attestations_summary TO evidence_locker_app;
|
|
|
|
-- Add comments for documentation
|
|
COMMENT ON TABLE evidence_locker.verdict_attestations IS
|
|
'Stores DSSE-signed policy verdict attestations for audit and verification';
|
|
|
|
COMMENT ON COLUMN evidence_locker.verdict_attestations.verdict_id IS
|
|
'Unique verdict identifier (format: verdict:run:{runId}:finding:{findingId})';
|
|
|
|
COMMENT ON COLUMN evidence_locker.verdict_attestations.envelope IS
|
|
'DSSE envelope containing signed verdict predicate';
|
|
|
|
COMMENT ON COLUMN evidence_locker.verdict_attestations.predicate_digest IS
|
|
'SHA256 digest of the canonical JSON predicate payload';
|
|
|
|
COMMENT ON COLUMN evidence_locker.verdict_attestations.determinism_hash IS
|
|
'Determinism hash computed from sorted evidence digests and verdict components';
|
|
|
|
COMMENT ON COLUMN evidence_locker.verdict_attestations.rekor_log_index IS
|
|
'Rekor transparency log index (if anchored), null for offline/air-gap deployments';
|