e6201d5239
Implement full evidence integrity pipeline to prevent compliance theater: - Confidence levels (E0-E4), truth status tracking, assertion engine - Four-Eyes approval workflow, audit trail, reject endpoint - Evidence distribution dashboard, LLM audit routes - Traceability matrix (backend endpoint + Compliance Hub UI tab) - Anti-fake badges, control status machine, normative patterns - 2 migrations, 4 test suites, MkDocs documentation Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
38 lines
1.8 KiB
SQL
38 lines
1.8 KiB
SQL
-- Migration 077: Anti-Fake-Evidence Phase 2
|
|
-- Assertions table, Four-Eyes columns on Evidence, Audit-Trail performance index
|
|
|
|
-- 1A. Assertions table
|
|
CREATE TABLE IF NOT EXISTS compliance_assertions (
|
|
id VARCHAR(36) PRIMARY KEY DEFAULT gen_random_uuid()::text,
|
|
tenant_id VARCHAR(36),
|
|
entity_type VARCHAR(50) NOT NULL,
|
|
entity_id VARCHAR(36) NOT NULL,
|
|
sentence_text TEXT NOT NULL,
|
|
sentence_index INTEGER NOT NULL DEFAULT 0,
|
|
assertion_type VARCHAR(20) NOT NULL DEFAULT 'assertion',
|
|
evidence_ids JSONB DEFAULT '[]'::jsonb,
|
|
confidence FLOAT DEFAULT 0.0,
|
|
normative_tier VARCHAR(20),
|
|
verified_by VARCHAR(200),
|
|
verified_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS ix_assertion_entity ON compliance_assertions (entity_type, entity_id);
|
|
CREATE INDEX IF NOT EXISTS ix_assertion_type ON compliance_assertions (assertion_type);
|
|
CREATE INDEX IF NOT EXISTS ix_assertion_tenant ON compliance_assertions (tenant_id);
|
|
|
|
-- 1B. Four-Eyes columns on Evidence
|
|
ALTER TABLE compliance_evidence
|
|
ADD COLUMN IF NOT EXISTS approval_status VARCHAR(30) DEFAULT 'none',
|
|
ADD COLUMN IF NOT EXISTS first_reviewer VARCHAR(200),
|
|
ADD COLUMN IF NOT EXISTS first_reviewed_at TIMESTAMPTZ,
|
|
ADD COLUMN IF NOT EXISTS second_reviewer VARCHAR(200),
|
|
ADD COLUMN IF NOT EXISTS second_reviewed_at TIMESTAMPTZ,
|
|
ADD COLUMN IF NOT EXISTS requires_four_eyes BOOLEAN DEFAULT FALSE;
|
|
CREATE INDEX IF NOT EXISTS ix_evidence_approval_status ON compliance_evidence (approval_status);
|
|
|
|
-- 1C. Audit-Trail performance index
|
|
CREATE INDEX IF NOT EXISTS ix_audit_trail_entity_action
|
|
ON compliance_audit_trail (entity_type, action, performed_at);
|