craftvia/prisma/migrations/20260731152213_tisax_m1_m4_consolidated/migration.sql

-- AlterTable
ALTER TABLE "assets" ADD COLUMN     "label" "InfoLabel" NOT NULL DEFAULT 'NONE',
ADD COLUMN     "normalized_name" TEXT;

-- AlterTable
ALTER TABLE "tasks" ADD COLUMN     "effective_until" TIMESTAMP(3),
ADD COLUMN     "recurrence" TEXT,
ADD COLUMN     "remind_at" TIMESTAMP(3);

-- CreateTable
CREATE TABLE "process_catalog" (
    "id" TEXT NOT NULL,
    "code" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "category" "ProcessCategory" NOT NULL,
    "suggested_asset_types" "AssetType"[],
    "suggested_risk_codes" TEXT[] DEFAULT ARRAY[]::TEXT[],
    "suggested_info_labels" "InfoLabel"[],
    "order_idx" INTEGER NOT NULL DEFAULT 0,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "process_catalog_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "task_participants" (
    "id" TEXT NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "task_id" TEXT NOT NULL,
    "user_id" TEXT NOT NULL,
    "raci" "RaciKind" NOT NULL,

    CONSTRAINT "task_participants_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "control_domain_map" (
    "id" TEXT NOT NULL,
    "tenant_id" TEXT,
    "control" TEXT NOT NULL,
    "domain" "Domain" NOT NULL,
    "raci" "RaciKind" NOT NULL DEFAULT 'RESPONSIBLE',
    "function_key" TEXT,

    CONSTRAINT "control_domain_map_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "evidence" (
    "id" TEXT NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "title" TEXT NOT NULL,
    "kind" TEXT NOT NULL,
    "file_ref" TEXT,
    "task_id" TEXT,
    "control" TEXT,
    "valid_from" TIMESTAMP(3),
    "valid_until" TIMESTAMP(3),
    "created_by_id" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "evidence_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "project_function_assignments" (
    "id" TEXT NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "function_key" TEXT NOT NULL,
    "user_id" TEXT,
    "domain" "Domain",
    "invited_email" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "project_function_assignments_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "process_catalog_code_key" ON "process_catalog"("code");

-- CreateIndex
CREATE INDEX "task_participants_tenant_id_user_id_idx" ON "task_participants"("tenant_id", "user_id");

-- CreateIndex
CREATE UNIQUE INDEX "task_participants_tenant_id_task_id_user_id_raci_key" ON "task_participants"("tenant_id", "task_id", "user_id", "raci");

-- CreateIndex
CREATE INDEX "control_domain_map_tenant_id_control_idx" ON "control_domain_map"("tenant_id", "control");

-- CreateIndex
CREATE INDEX "control_domain_map_control_idx" ON "control_domain_map"("control");

-- CreateIndex
CREATE INDEX "evidence_tenant_id_control_idx" ON "evidence"("tenant_id", "control");

-- CreateIndex
CREATE INDEX "evidence_tenant_id_task_id_idx" ON "evidence"("tenant_id", "task_id");

-- CreateIndex
CREATE INDEX "project_function_assignments_tenant_id_function_key_idx" ON "project_function_assignments"("tenant_id", "function_key");

-- CreateIndex
CREATE UNIQUE INDEX "assets_tenant_id_normalized_name_key" ON "assets"("tenant_id", "normalized_name");

-- AddForeignKey
ALTER TABLE "task_participants" ADD CONSTRAINT "task_participants_task_id_fkey" FOREIGN KEY ("task_id") REFERENCES "tasks"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "evidence" ADD CONSTRAINT "evidence_task_id_fkey" FOREIGN KEY ("task_id") REFERENCES "tasks"("id") ON DELETE SET NULL ON UPDATE CASCADE;


-- ── Tenant-Isolation (RLS) für neue mandantenbezogene Tabellen ───────────────
-- Muster wie seit F-04: ENABLE + FORCE + Policy mit USING *und* WITH CHECK.
-- Globale Tabellen (process_catalog) und die tenant-optionale control_domain_map
-- (globale Defaults mit tenant_id = NULL) sind bewusst ausgenommen.
ALTER TABLE "project_function_assignments" ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON "project_function_assignments";
CREATE POLICY tenant_isolation ON "project_function_assignments"
  USING ("tenant_id" = current_setting('app.tenant_id', true))
  WITH CHECK ("tenant_id" = current_setting('app.tenant_id', true));
ALTER TABLE "project_function_assignments" FORCE ROW LEVEL SECURITY;

ALTER TABLE "task_participants" ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON "task_participants";
CREATE POLICY tenant_isolation ON "task_participants"
  USING ("tenant_id" = current_setting('app.tenant_id', true))
  WITH CHECK ("tenant_id" = current_setting('app.tenant_id', true));
ALTER TABLE "task_participants" FORCE ROW LEVEL SECURITY;

ALTER TABLE "evidence" ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON "evidence";
CREATE POLICY tenant_isolation ON "evidence"
  USING ("tenant_id" = current_setting('app.tenant_id', true))
  WITH CHECK ("tenant_id" = current_setting('app.tenant_id', true));
ALTER TABLE "evidence" FORCE ROW LEVEL SECURITY;