msolarczekandClaude Opus 5
c8e6f30a27
Unveränderter Stand von certvia/dev (a48c5fb) plus Craftvia-Spezifikation und Brandbook unter docs/craftvia/. ISMS-Module werden im Folgecommit entfernt. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
68 lines
2.7 KiB
SQL
68 lines
2.7 KiB
SQL
-- IM-D — E-Mail-to-Ticket (Inbound) + Provisionierung.
|
|
-- 1) incidents.inbound_message_id: Idempotenz gegen Doppel-Ticket beim erneuten
|
|
-- IMAP-Abholen derselben Mail (Dedupe per Message-ID, KONZEPT §2).
|
|
-- 2) incident_intake_config: mandantengebundene Intake-Konfiguration (RLS).
|
|
-- 3) incident_inbound_review: plattformweite Review-Queue (KEINE RLS — tenant-los).
|
|
|
|
-- AlterTable
|
|
ALTER TABLE "incidents" ADD COLUMN "inbound_message_id" TEXT;
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "incidents_inbound_message_id_idx" ON "incidents"("inbound_message_id");
|
|
|
|
-- CreateTable (mandantengebunden, RLS)
|
|
CREATE TABLE "incident_intake_config" (
|
|
"id" TEXT NOT NULL,
|
|
"tenant_id" TEXT NOT NULL,
|
|
"token" TEXT NOT NULL,
|
|
"allowlist_domains" TEXT[] DEFAULT ARRAY[]::TEXT[],
|
|
"source_address" TEXT,
|
|
"status" TEXT NOT NULL DEFAULT 'weiterleitung_ausstehend',
|
|
"verified_at" TIMESTAMP(3),
|
|
"notify_email" TEXT,
|
|
"notify_locale" TEXT NOT NULL DEFAULT 'de',
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "incident_intake_config_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "incident_intake_config_tenant_id_key" ON "incident_intake_config"("tenant_id");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "incident_intake_config_token_key" ON "incident_intake_config"("token");
|
|
|
|
-- CreateTable (plattformweit, KEINE RLS — tenant-los)
|
|
CREATE TABLE "incident_inbound_review" (
|
|
"id" TEXT NOT NULL,
|
|
"message_id" TEXT,
|
|
"sender" TEXT NOT NULL,
|
|
"subject" TEXT,
|
|
"recipient" TEXT,
|
|
"token" TEXT,
|
|
"reason" TEXT NOT NULL,
|
|
"status" TEXT NOT NULL DEFAULT 'offen',
|
|
"tenant_id" TEXT,
|
|
"received_at" TIMESTAMP(3) NOT NULL,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "incident_inbound_review_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "incident_inbound_review_status_idx" ON "incident_inbound_review"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "incident_inbound_review_message_id_idx" ON "incident_inbound_review"("message_id");
|
|
|
|
-- Row Level Security auf der mandantengebundenen Intake-Konfiguration (analog übrige
|
|
-- Mandanten-Tabellen; FORCE + WITH CHECK für den scharfen isms_app-Pfad, F-04). Die
|
|
-- Review-Queue bleibt bewusst OHNE RLS (plattformweite Betreiber-Sicht).
|
|
ALTER TABLE "incident_intake_config" ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation ON "incident_intake_config";
|
|
CREATE POLICY tenant_isolation ON "incident_intake_config"
|
|
USING ("tenant_id" = current_setting('app.tenant_id', true))
|
|
WITH CHECK ("tenant_id" = current_setting('app.tenant_id', true));
|
|
ALTER TABLE "incident_intake_config" FORCE ROW LEVEL SECURITY;
|