b7c1a5da1a
All checks were successful
CI / go-lint (push) Has been skipped
CI / python-lint (push) Has been skipped
CI / nodejs-lint (push) Has been skipped
CI / test-go-ai-compliance (push) Successful in 36s
CI / test-python-backend-compliance (push) Successful in 31s
CI / test-python-document-crawler (push) Successful in 23s
CI / test-python-dsms-gateway (push) Successful in 18s
5-Phasen-Migration: Go consent-service Proxies durch native Python/FastAPI ersetzt. Phase 1 — DSR (Betroffenenrechte): 6 Tabellen, 30 Endpoints, Frontend-API umgestellt Phase 2 — E-Mail-Templates: 5 Tabellen, 20 Endpoints, neues Frontend, SDK_STEPS erweitert Phase 3 — Legal Documents Extension: User Consents, Audit Log, Cookie-Kategorien Phase 4 — Banner Consent: Device-Consents, Site-Configs, Kategorien, Vendors Phase 5 — Cleanup: DSR-Proxy aus main.py entfernt, Frontend-URLs aktualisiert 148 neue Tests (50 + 47 + 26 + 25), alle bestanden. Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
119 lines
9.0 KiB
SQL
119 lines
9.0 KiB
SQL
-- Migration 027: E-Mail-Templates — Benachrichtigungsvorlagen fuer DSGVO-Compliance
|
|
-- Zentrale Verwaltung von E-Mail-Templates fuer DSR, Consent, Breach-Notifications etc.
|
|
|
|
-- Template-Definitionen
|
|
CREATE TABLE IF NOT EXISTS compliance_email_templates (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL DEFAULT '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e',
|
|
template_type TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
category TEXT NOT NULL DEFAULT 'general',
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
variables JSONB DEFAULT '[]'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_email_tpl_tenant ON compliance_email_templates(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_email_tpl_type ON compliance_email_templates(template_type);
|
|
CREATE INDEX IF NOT EXISTS idx_email_tpl_category ON compliance_email_templates(category);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_email_tpl_tenant_type ON compliance_email_templates(tenant_id, template_type);
|
|
|
|
-- Versionierte Template-Inhalte
|
|
CREATE TABLE IF NOT EXISTS compliance_email_template_versions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
template_id UUID NOT NULL,
|
|
version TEXT NOT NULL DEFAULT '1.0',
|
|
language TEXT NOT NULL DEFAULT 'de',
|
|
subject TEXT NOT NULL,
|
|
body_html TEXT NOT NULL,
|
|
body_text TEXT,
|
|
status TEXT NOT NULL DEFAULT 'draft',
|
|
submitted_at TIMESTAMPTZ,
|
|
submitted_by TEXT,
|
|
published_at TIMESTAMPTZ,
|
|
published_by TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_by TEXT DEFAULT 'system'
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_email_tpl_ver_template ON compliance_email_template_versions(template_id);
|
|
CREATE INDEX IF NOT EXISTS idx_email_tpl_ver_status ON compliance_email_template_versions(status);
|
|
|
|
-- Approval-Workflow
|
|
CREATE TABLE IF NOT EXISTS compliance_email_template_approvals (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
version_id UUID NOT NULL,
|
|
action TEXT NOT NULL DEFAULT 'approve',
|
|
comment TEXT,
|
|
approved_by TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_email_tpl_appr_version ON compliance_email_template_approvals(version_id);
|
|
|
|
-- Audit-Trail gesendeter E-Mails
|
|
CREATE TABLE IF NOT EXISTS compliance_email_send_logs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL DEFAULT '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e',
|
|
template_type TEXT NOT NULL,
|
|
version_id UUID,
|
|
recipient TEXT NOT NULL,
|
|
subject TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'sent',
|
|
variables JSONB DEFAULT '{}'::jsonb,
|
|
error_message TEXT,
|
|
sent_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_email_logs_tenant ON compliance_email_send_logs(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_email_logs_type ON compliance_email_send_logs(template_type);
|
|
CREATE INDEX IF NOT EXISTS idx_email_logs_sent ON compliance_email_send_logs(sent_at);
|
|
|
|
-- Globale Einstellungen (Branding)
|
|
CREATE TABLE IF NOT EXISTS compliance_email_template_settings (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL DEFAULT '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e',
|
|
sender_name TEXT DEFAULT 'Datenschutzbeauftragter',
|
|
sender_email TEXT DEFAULT 'datenschutz@example.de',
|
|
reply_to TEXT,
|
|
logo_url TEXT,
|
|
primary_color TEXT DEFAULT '#4F46E5',
|
|
secondary_color TEXT DEFAULT '#7C3AED',
|
|
footer_text TEXT DEFAULT 'Datenschutzhinweis: Diese E-Mail enthaelt vertrauliche Informationen.',
|
|
company_name TEXT,
|
|
company_address TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_email_settings_tenant ON compliance_email_template_settings(tenant_id);
|
|
|
|
-- Default-Templates einfuegen
|
|
INSERT INTO compliance_email_templates (id, tenant_id, template_type, name, description, category, sort_order, variables)
|
|
VALUES
|
|
(gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'welcome', 'Willkommen', 'Willkommens-E-Mail fuer neue Nutzer', 'general', 1, '["user_name", "company_name", "login_url"]'::jsonb),
|
|
(gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'verification', 'E-Mail-Verifizierung', 'Verifizierungs-Link fuer E-Mail-Adressen', 'general', 2, '["user_name", "verification_url", "expiry_hours"]'::jsonb),
|
|
(gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'password_reset', 'Passwort zuruecksetzen', 'Link zum Zuruecksetzen des Passworts', 'general', 3, '["user_name", "reset_url", "expiry_hours"]'::jsonb),
|
|
(gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'dsr_receipt', 'DSR Eingangsbestaetigung', 'Bestaetigung fuer eingehende Betroffenenanfrage', 'dsr', 10, '["requester_name", "reference_number", "request_type", "deadline"]'::jsonb),
|
|
(gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'dsr_identity_request', 'DSR Identitaetsanfrage', 'Anforderung zur Identitaetspruefung', 'dsr', 11, '["requester_name", "reference_number"]'::jsonb),
|
|
(gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'dsr_completion', 'DSR Abschluss', 'Benachrichtigung ueber abgeschlossene Anfrage', 'dsr', 12, '["requester_name", "reference_number", "request_type", "completion_date"]'::jsonb),
|
|
(gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'dsr_rejection', 'DSR Ablehnung', 'Benachrichtigung ueber abgelehnte Anfrage', 'dsr', 13, '["requester_name", "reference_number", "rejection_reason", "legal_basis"]'::jsonb),
|
|
(gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'dsr_extension', 'DSR Fristverlaengerung', 'Benachrichtigung ueber verlaengerte Bearbeitungsfrist', 'dsr', 14, '["requester_name", "reference_number", "new_deadline", "extension_reason"]'::jsonb),
|
|
(gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'consent_request', 'Einwilligungsanfrage', 'Anfrage zur Einwilligung in Datenverarbeitung', 'consent', 20, '["user_name", "purpose", "consent_url"]'::jsonb),
|
|
(gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'consent_confirmation', 'Einwilligungsbestaetigung', 'Bestaetigung der erteilten Einwilligung', 'consent', 21, '["user_name", "purpose", "consent_date"]'::jsonb),
|
|
(gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'consent_withdrawal', 'Widerruf bestaetigt', 'Bestaetigung des Widerrufs einer Einwilligung', 'consent', 22, '["user_name", "purpose", "withdrawal_date"]'::jsonb),
|
|
(gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'consent_reminder', 'Einwilligungs-Erinnerung', 'Erinnerung an auslaufende Einwilligung', 'consent', 23, '["user_name", "purpose", "expiry_date"]'::jsonb),
|
|
(gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'breach_notification_authority', 'Datenpanne Aufsichtsbehoerde', 'Meldung an Datenschutzbehoerde (Art. 33)', 'breach', 30, '["incident_date", "incident_description", "affected_count", "measures_taken", "authority_name"]'::jsonb),
|
|
(gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'breach_notification_affected', 'Datenpanne Betroffene', 'Benachrichtigung betroffener Personen (Art. 34)', 'breach', 31, '["user_name", "incident_date", "incident_description", "measures_taken", "contact_info"]'::jsonb),
|
|
(gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'breach_internal', 'Datenpanne intern', 'Interne Meldung einer Datenschutzverletzung', 'breach', 32, '["reporter_name", "incident_date", "incident_description", "severity"]'::jsonb),
|
|
(gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'vendor_dpa_request', 'AVV-Anfrage', 'Anforderung eines Auftragsverarbeitungsvertrags', 'vendor', 40, '["vendor_name", "contact_name", "deadline", "requirements"]'::jsonb),
|
|
(gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'vendor_review_reminder', 'Vendor-Pruefung Erinnerung', 'Erinnerung an faellige Dienstleisterpruefung', 'vendor', 41, '["vendor_name", "review_due_date", "last_review_date"]'::jsonb),
|
|
(gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'training_invitation', 'Schulungseinladung', 'Einladung zu Datenschutz-Schulung', 'training', 50, '["user_name", "training_title", "training_date", "training_url"]'::jsonb),
|
|
(gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'training_reminder', 'Schulungs-Erinnerung', 'Erinnerung an ausstehende Pflichtschulung', 'training', 51, '["user_name", "training_title", "deadline"]'::jsonb),
|
|
(gen_random_uuid(), '9282a473-5c95-4b3a-bf78-0ecc0ec71d3e', 'training_completion', 'Schulung abgeschlossen', 'Bestaetigung und Zertifikat nach Schulungsabschluss', 'training', 52, '["user_name", "training_title", "completion_date", "certificate_url"]'::jsonb)
|
|
ON CONFLICT DO NOTHING;
|