breakpilot-compliance/backend-compliance/migrations/029_banner_consent.sql

-- =========================================================
-- Migration 029: Banner Consent — Device-basierte Cookie-Consents
-- Fuer Einbettung in Kunden-Websites (Consent-Banner SDK)
-- =========================================================

-- compliance_banner_consents: Anonyme Geraete-Consents
CREATE TABLE IF NOT EXISTS compliance_banner_consents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    site_id TEXT NOT NULL,
    device_fingerprint TEXT NOT NULL,
    categories JSONB DEFAULT '[]',
    vendors JSONB DEFAULT '[]',
    ip_hash TEXT,
    user_agent TEXT,
    consent_string TEXT,
    expires_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_banner_consent_tenant ON compliance_banner_consents(tenant_id);
CREATE INDEX IF NOT EXISTS idx_banner_consent_site ON compliance_banner_consents(site_id);
CREATE INDEX IF NOT EXISTS idx_banner_consent_device ON compliance_banner_consents(device_fingerprint);
CREATE UNIQUE INDEX IF NOT EXISTS idx_banner_consent_site_device ON compliance_banner_consents(site_id, device_fingerprint);

-- compliance_banner_consent_audit_log: Immutable Audit
CREATE TABLE IF NOT EXISTS compliance_banner_consent_audit_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    consent_id UUID,
    action TEXT NOT NULL,
    site_id TEXT NOT NULL,
    device_fingerprint TEXT,
    categories JSONB DEFAULT '[]',
    ip_hash TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_banner_audit_tenant ON compliance_banner_consent_audit_log(tenant_id);
CREATE INDEX IF NOT EXISTS idx_banner_audit_site ON compliance_banner_consent_audit_log(site_id);
CREATE INDEX IF NOT EXISTS idx_banner_audit_created ON compliance_banner_consent_audit_log(created_at);

-- compliance_banner_site_configs: Site-Konfiguration (UI-Theme, DSB-Info)
CREATE TABLE IF NOT EXISTS compliance_banner_site_configs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    site_id TEXT NOT NULL,
    site_name TEXT,
    site_url TEXT,
    banner_title TEXT DEFAULT 'Cookie-Einstellungen',
    banner_description TEXT DEFAULT 'Wir verwenden Cookies, um Ihnen die bestmoegliche Erfahrung zu bieten.',
    privacy_url TEXT,
    imprint_url TEXT,
    dsb_name TEXT,
    dsb_email TEXT,
    theme JSONB DEFAULT '{}',
    tcf_enabled BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_banner_site_config ON compliance_banner_site_configs(tenant_id, site_id);

-- compliance_banner_category_configs: Consent-Kategorien pro Site
CREATE TABLE IF NOT EXISTS compliance_banner_category_configs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    site_config_id UUID NOT NULL REFERENCES compliance_banner_site_configs(id) ON DELETE CASCADE,
    category_key TEXT NOT NULL,
    name_de TEXT NOT NULL,
    name_en TEXT,
    description_de TEXT,
    description_en TEXT,
    is_required BOOLEAN NOT NULL DEFAULT FALSE,
    sort_order INTEGER NOT NULL DEFAULT 0,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_banner_cat_config ON compliance_banner_category_configs(site_config_id);

-- compliance_banner_vendor_configs: Third-Party-Vendor-Tracking
CREATE TABLE IF NOT EXISTS compliance_banner_vendor_configs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    site_config_id UUID NOT NULL REFERENCES compliance_banner_site_configs(id) ON DELETE CASCADE,
    vendor_name TEXT NOT NULL,
    vendor_url TEXT,
    category_key TEXT NOT NULL,
    description_de TEXT,
    description_en TEXT,
    cookie_names JSONB DEFAULT '[]',
    retention_days INTEGER DEFAULT 365,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_banner_vendor_config ON compliance_banner_vendor_configs(site_config_id);