breakpilot-compliance/ai-compliance-sdk/migrations/002_dsgvo_schema.sql

-- DSGVO Schema Migration
-- AI Compliance SDK - Phase 4: DSGVO Integration

-- ============================================================================
-- VVT - Verarbeitungsverzeichnis (Art. 30 DSGVO)
-- ============================================================================

CREATE TABLE IF NOT EXISTS dsgvo_processing_activities (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES compliance_tenants(id) ON DELETE CASCADE,
    namespace_id UUID REFERENCES compliance_namespaces(id) ON DELETE SET NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    purpose TEXT NOT NULL,
    legal_basis VARCHAR(50) NOT NULL, -- consent, contract, legal_obligation, vital_interests, public_interest, legitimate_interests
    legal_basis_details TEXT,
    data_categories JSONB DEFAULT '[]',
    data_subject_categories JSONB DEFAULT '[]',
    recipients JSONB DEFAULT '[]',
    third_country_transfer BOOLEAN DEFAULT FALSE,
    transfer_safeguards TEXT,
    retention_period VARCHAR(255),
    retention_policy_id UUID,
    tom_reference JSONB DEFAULT '[]',
    dsfa_required BOOLEAN DEFAULT FALSE,
    dsfa_id UUID,
    responsible_person VARCHAR(255),
    responsible_department VARCHAR(255),
    systems JSONB DEFAULT '[]',
    status VARCHAR(50) DEFAULT 'draft', -- draft, active, under_review, archived
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID NOT NULL,
    last_reviewed_at TIMESTAMPTZ,
    next_review_at TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_dsgvo_pa_tenant ON dsgvo_processing_activities(tenant_id);
CREATE INDEX IF NOT EXISTS idx_dsgvo_pa_status ON dsgvo_processing_activities(tenant_id, status);
CREATE INDEX IF NOT EXISTS idx_dsgvo_pa_namespace ON dsgvo_processing_activities(namespace_id) WHERE namespace_id IS NOT NULL;

-- ============================================================================
-- DSFA - Datenschutz-Folgenabschätzung (Art. 35 DSGVO)
-- ============================================================================

CREATE TABLE IF NOT EXISTS dsgvo_dsfa (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES compliance_tenants(id) ON DELETE CASCADE,
    namespace_id UUID REFERENCES compliance_namespaces(id) ON DELETE SET NULL,
    processing_activity_id UUID REFERENCES dsgvo_processing_activities(id) ON DELETE SET NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    processing_description TEXT,
    necessity_assessment TEXT,
    proportionality_assessment TEXT,
    risks JSONB DEFAULT '[]',
    mitigations JSONB DEFAULT '[]',
    dpo_consulted BOOLEAN DEFAULT FALSE,
    dpo_opinion TEXT,
    authority_consulted BOOLEAN DEFAULT FALSE,
    authority_reference VARCHAR(255),
    status VARCHAR(50) DEFAULT 'draft', -- draft, in_progress, completed, approved, rejected
    overall_risk_level VARCHAR(20), -- low, medium, high, very_high
    conclusion TEXT,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID NOT NULL,
    approved_by UUID,
    approved_at TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_dsgvo_dsfa_tenant ON dsgvo_dsfa(tenant_id);
CREATE INDEX IF NOT EXISTS idx_dsgvo_dsfa_status ON dsgvo_dsfa(tenant_id, status);
CREATE INDEX IF NOT EXISTS idx_dsgvo_dsfa_pa ON dsgvo_dsfa(processing_activity_id) WHERE processing_activity_id IS NOT NULL;

-- ============================================================================
-- TOM - Technische und Organisatorische Maßnahmen (Art. 32 DSGVO)
-- ============================================================================

CREATE TABLE IF NOT EXISTS dsgvo_tom (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES compliance_tenants(id) ON DELETE CASCADE,
    namespace_id UUID REFERENCES compliance_namespaces(id) ON DELETE SET NULL,
    category VARCHAR(50) NOT NULL, -- access_control, encryption, pseudonymization, etc.
    subcategory VARCHAR(100),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    type VARCHAR(20) NOT NULL, -- technical, organizational
    implementation_status VARCHAR(50) DEFAULT 'planned', -- planned, in_progress, implemented, verified, not_applicable
    implemented_at TIMESTAMPTZ,
    verified_at TIMESTAMPTZ,
    verified_by UUID,
    effectiveness_rating VARCHAR(20), -- low, medium, high
    documentation TEXT,
    responsible_person VARCHAR(255),
    responsible_department VARCHAR(255),
    review_frequency VARCHAR(50), -- monthly, quarterly, annually
    last_review_at TIMESTAMPTZ,
    next_review_at TIMESTAMPTZ,
    related_controls JSONB DEFAULT '[]',
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_dsgvo_tom_tenant ON dsgvo_tom(tenant_id);
CREATE INDEX IF NOT EXISTS idx_dsgvo_tom_category ON dsgvo_tom(tenant_id, category);
CREATE INDEX IF NOT EXISTS idx_dsgvo_tom_status ON dsgvo_tom(tenant_id, implementation_status);

-- ============================================================================
-- DSR - Data Subject Requests / Betroffenenrechte (Art. 15-22 DSGVO)
-- ============================================================================

CREATE TABLE IF NOT EXISTS dsgvo_dsr (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES compliance_tenants(id) ON DELETE CASCADE,
    namespace_id UUID REFERENCES compliance_namespaces(id) ON DELETE SET NULL,
    request_type VARCHAR(50) NOT NULL, -- access, rectification, erasure, restriction, portability, objection
    status VARCHAR(50) DEFAULT 'received', -- received, verified, in_progress, completed, rejected, extended
    subject_name VARCHAR(255) NOT NULL,
    subject_email VARCHAR(255) NOT NULL,
    subject_identifier VARCHAR(255),
    request_description TEXT,
    request_channel VARCHAR(50), -- email, form, phone, letter
    received_at TIMESTAMPTZ NOT NULL,
    verified_at TIMESTAMPTZ,
    verification_method VARCHAR(100),
    deadline_at TIMESTAMPTZ NOT NULL,
    extended_deadline_at TIMESTAMPTZ,
    extension_reason TEXT,
    completed_at TIMESTAMPTZ,
    response_sent BOOLEAN DEFAULT FALSE,
    response_sent_at TIMESTAMPTZ,
    response_method VARCHAR(50),
    rejection_reason TEXT,
    notes TEXT,
    affected_systems JSONB DEFAULT '[]',
    assigned_to UUID,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_dsgvo_dsr_tenant ON dsgvo_dsr(tenant_id);
CREATE INDEX IF NOT EXISTS idx_dsgvo_dsr_status ON dsgvo_dsr(tenant_id, status);
CREATE INDEX IF NOT EXISTS idx_dsgvo_dsr_deadline ON dsgvo_dsr(tenant_id, deadline_at) WHERE status NOT IN ('completed', 'rejected');
CREATE INDEX IF NOT EXISTS idx_dsgvo_dsr_type ON dsgvo_dsr(tenant_id, request_type);

-- ============================================================================
-- Retention Policies - Löschfristen (Art. 17 DSGVO)
-- ============================================================================

CREATE TABLE IF NOT EXISTS dsgvo_retention_policies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES compliance_tenants(id) ON DELETE CASCADE,
    namespace_id UUID REFERENCES compliance_namespaces(id) ON DELETE SET NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    data_category VARCHAR(100) NOT NULL,
    retention_period_days INT NOT NULL,
    retention_period_text VARCHAR(255), -- Human readable
    legal_basis VARCHAR(100),
    legal_reference VARCHAR(255), -- § 147 AO, § 257 HGB, etc.
    deletion_method VARCHAR(50), -- automatic, manual, anonymization
    deletion_procedure TEXT,
    exception_criteria TEXT,
    applicable_systems JSONB DEFAULT '[]',
    responsible_person VARCHAR(255),
    responsible_department VARCHAR(255),
    status VARCHAR(50) DEFAULT 'draft', -- draft, active, archived
    last_review_at TIMESTAMPTZ,
    next_review_at TIMESTAMPTZ,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_dsgvo_retention_tenant ON dsgvo_retention_policies(tenant_id);
CREATE INDEX IF NOT EXISTS idx_dsgvo_retention_status ON dsgvo_retention_policies(tenant_id, status);
CREATE INDEX IF NOT EXISTS idx_dsgvo_retention_category ON dsgvo_retention_policies(tenant_id, data_category);

-- ============================================================================
-- Insert default TOM categories as reference data
-- ============================================================================

-- This is optional - the categories are also defined in code
-- But having them in the database allows for easier UI population

CREATE TABLE IF NOT EXISTS dsgvo_tom_categories (
    id VARCHAR(50) PRIMARY KEY,
    name_de VARCHAR(255) NOT NULL,
    name_en VARCHAR(255) NOT NULL,
    description_de TEXT,
    article_reference VARCHAR(50)
);

INSERT INTO dsgvo_tom_categories (id, name_de, name_en, description_de, article_reference) VALUES
    ('access_control', 'Zutrittskontrolle', 'Physical Access Control', 'Maßnahmen zur Verhinderung des unbefugten Zutritts zu Datenverarbeitungsanlagen', 'Art. 32 Abs. 1 lit. b'),
    ('admission_control', 'Zugangskontrolle', 'Logical Access Control', 'Maßnahmen zur Verhinderung der unbefugten Nutzung von DV-Systemen', 'Art. 32 Abs. 1 lit. b'),
    ('access_management', 'Zugriffskontrolle', 'Access Management', 'Maßnahmen zur Gewährleistung, dass nur befugte Personen Zugriff auf Daten haben', 'Art. 32 Abs. 1 lit. b'),
    ('transfer_control', 'Weitergabekontrolle', 'Transfer Control', 'Maßnahmen zur Verhinderung des unbefugten Lesens, Kopierens oder Entfernens bei der Übertragung', 'Art. 32 Abs. 1 lit. b'),
    ('input_control', 'Eingabekontrolle', 'Input Control', 'Maßnahmen zur Nachvollziehbarkeit von Eingabe, Änderung und Löschung von Daten', 'Art. 32 Abs. 1 lit. b'),
    ('availability_control', 'Verfügbarkeitskontrolle', 'Availability Control', 'Maßnahmen zum Schutz gegen zufällige oder mutwillige Zerstörung oder Verlust', 'Art. 32 Abs. 1 lit. b, c'),
    ('separation_control', 'Trennungskontrolle', 'Separation Control', 'Maßnahmen zur getrennten Verarbeitung von Daten, die zu unterschiedlichen Zwecken erhoben wurden', 'Art. 32 Abs. 1 lit. b'),
    ('encryption', 'Verschlüsselung', 'Encryption', 'Verschlüsselung personenbezogener Daten', 'Art. 32 Abs. 1 lit. a'),
    ('pseudonymization', 'Pseudonymisierung', 'Pseudonymization', 'Verarbeitung in einer Weise, dass die Daten ohne zusätzliche Informationen nicht mehr zugeordnet werden können', 'Art. 32 Abs. 1 lit. a'),
    ('resilience', 'Belastbarkeit', 'Resilience', 'Fähigkeit, die Verfügbarkeit und den Zugang bei einem Zwischenfall rasch wiederherzustellen', 'Art. 32 Abs. 1 lit. b, c'),
    ('recovery', 'Wiederherstellung', 'Recovery', 'Verfahren zur Wiederherstellung der Verfügbarkeit und des Zugangs', 'Art. 32 Abs. 1 lit. c'),
    ('testing', 'Regelmäßige Überprüfung', 'Regular Testing', 'Verfahren zur regelmäßigen Überprüfung, Bewertung und Evaluierung der Wirksamkeit', 'Art. 32 Abs. 1 lit. d')
ON CONFLICT (id) DO NOTHING;