craftvia/prisma/migrations/20260821110000_mgmt_clauses/migration.sql

-- CreateTable
CREATE TABLE "kpis" (
    "id" TEXT NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "description" TEXT,
    "data_source" TEXT,
    "unit" TEXT,
    "target" TEXT,
    "cadence" TEXT NOT NULL DEFAULT 'monatlich',
    "owner_id" TEXT,
    "active" BOOLEAN NOT NULL DEFAULT true,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "kpi_values" (
    "id" TEXT NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "kpi_id" TEXT NOT NULL,
    "period" TEXT NOT NULL,
    "value" TEXT NOT NULL,
    "note" TEXT,
    "recorded_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "management_reviews" (
    "id" TEXT NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "review_date" TIMESTAMP(3) NOT NULL,
    "status" TEXT NOT NULL DEFAULT 'entwurf',
    "inputs" TEXT,
    "results" TEXT,
    "owner_id" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "management_review_decisions" (
    "id" TEXT NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "review_id" TEXT NOT NULL,
    "decision" TEXT NOT NULL,
    "owner_id" TEXT,
    "due_date" TIMESTAMP(3),
    "status" TEXT NOT NULL DEFAULT 'offen',

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

-- CreateTable
CREATE TABLE "nonconformities" (
    "id" TEXT NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "ref_no" TEXT NOT NULL,
    "source" TEXT NOT NULL,
    "description" TEXT NOT NULL,
    "immediate_correction" TEXT,
    "status" TEXT NOT NULL DEFAULT 'offen',
    "owner_id" TEXT,
    "detected_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "closed_at" TIMESTAMP(3),
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "corrective_actions" (
    "id" TEXT NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "nonconformity_id" TEXT NOT NULL,
    "root_cause" TEXT,
    "action" TEXT NOT NULL,
    "owner_id" TEXT,
    "due_date" TIMESTAMP(3),
    "status" TEXT NOT NULL DEFAULT 'geplant',
    "effectiveness_check" TEXT,
    "effectiveness_confirmed_at" TIMESTAMP(3),
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

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

-- CreateIndex
CREATE INDEX "kpis_tenant_id_idx" ON "kpis"("tenant_id");

-- CreateIndex
CREATE INDEX "kpi_values_tenant_id_idx" ON "kpi_values"("tenant_id");

-- CreateIndex
CREATE INDEX "kpi_values_kpi_id_idx" ON "kpi_values"("kpi_id");

-- CreateIndex
CREATE UNIQUE INDEX "kpi_values_tenant_id_kpi_id_period_key" ON "kpi_values"("tenant_id", "kpi_id", "period");

-- CreateIndex
CREATE INDEX "management_reviews_tenant_id_idx" ON "management_reviews"("tenant_id");

-- CreateIndex
CREATE INDEX "management_review_decisions_tenant_id_idx" ON "management_review_decisions"("tenant_id");

-- CreateIndex
CREATE INDEX "management_review_decisions_review_id_idx" ON "management_review_decisions"("review_id");

-- CreateIndex
CREATE INDEX "nonconformities_tenant_id_idx" ON "nonconformities"("tenant_id");

-- CreateIndex
CREATE UNIQUE INDEX "nonconformities_tenant_id_ref_no_key" ON "nonconformities"("tenant_id", "ref_no");

-- CreateIndex
CREATE INDEX "corrective_actions_tenant_id_idx" ON "corrective_actions"("tenant_id");

-- CreateIndex
CREATE INDEX "corrective_actions_nonconformity_id_idx" ON "corrective_actions"("nonconformity_id");

-- AddForeignKey
ALTER TABLE "kpi_values" ADD CONSTRAINT "kpi_values_kpi_id_fkey" FOREIGN KEY ("kpi_id") REFERENCES "kpis"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "management_review_decisions" ADD CONSTRAINT "management_review_decisions_review_id_fkey" FOREIGN KEY ("review_id") REFERENCES "management_reviews"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "corrective_actions" ADD CONSTRAINT "corrective_actions_nonconformity_id_fkey" FOREIGN KEY ("nonconformity_id") REFERENCES "nonconformities"("id") ON DELETE CASCADE ON UPDATE CASCADE;


-- AP4 — Row Level Security (F-04: FORCE + WITH CHECK) für die neuen Mandanten-Tabellen.
ALTER TABLE "kpis" ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON "kpis";
CREATE POLICY tenant_isolation ON "kpis"
  USING ("tenant_id" = current_setting('app.tenant_id', true))
  WITH CHECK ("tenant_id" = current_setting('app.tenant_id', true));
ALTER TABLE "kpis" FORCE ROW LEVEL SECURITY;
ALTER TABLE "kpi_values" ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON "kpi_values";
CREATE POLICY tenant_isolation ON "kpi_values"
  USING ("tenant_id" = current_setting('app.tenant_id', true))
  WITH CHECK ("tenant_id" = current_setting('app.tenant_id', true));
ALTER TABLE "kpi_values" FORCE ROW LEVEL SECURITY;
ALTER TABLE "management_reviews" ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON "management_reviews";
CREATE POLICY tenant_isolation ON "management_reviews"
  USING ("tenant_id" = current_setting('app.tenant_id', true))
  WITH CHECK ("tenant_id" = current_setting('app.tenant_id', true));
ALTER TABLE "management_reviews" FORCE ROW LEVEL SECURITY;
ALTER TABLE "management_review_decisions" ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON "management_review_decisions";
CREATE POLICY tenant_isolation ON "management_review_decisions"
  USING ("tenant_id" = current_setting('app.tenant_id', true))
  WITH CHECK ("tenant_id" = current_setting('app.tenant_id', true));
ALTER TABLE "management_review_decisions" FORCE ROW LEVEL SECURITY;
ALTER TABLE "nonconformities" ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON "nonconformities";
CREATE POLICY tenant_isolation ON "nonconformities"
  USING ("tenant_id" = current_setting('app.tenant_id', true))
  WITH CHECK ("tenant_id" = current_setting('app.tenant_id', true));
ALTER TABLE "nonconformities" FORCE ROW LEVEL SECURITY;
ALTER TABLE "corrective_actions" ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON "corrective_actions";
CREATE POLICY tenant_isolation ON "corrective_actions"
  USING ("tenant_id" = current_setting('app.tenant_id', true))
  WITH CHECK ("tenant_id" = current_setting('app.tenant_id', true));
ALTER TABLE "corrective_actions" FORCE ROW LEVEL SECURITY;