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>
69 lines
2.9 KiB
SQL
69 lines
2.9 KiB
SQL
-- AlterTable
|
|
ALTER TABLE "policy_documents" ADD COLUMN "next_review_at" TIMESTAMP(3),
|
|
ADD COLUMN "review_cycle" TEXT;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "policy_acknowledgements" (
|
|
"id" TEXT NOT NULL,
|
|
"tenant_id" TEXT NOT NULL,
|
|
"policy_document_id" TEXT NOT NULL,
|
|
"version" TEXT NOT NULL,
|
|
"user_id" TEXT NOT NULL,
|
|
"acknowledged_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "policy_acknowledgements_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "policy_document_versions" (
|
|
"id" TEXT NOT NULL,
|
|
"tenant_id" TEXT NOT NULL,
|
|
"policy_document_id" TEXT NOT NULL,
|
|
"version" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"change_note" TEXT,
|
|
"created_by" TEXT,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "policy_document_versions_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "policy_acknowledgements_tenant_id_idx" ON "policy_acknowledgements"("tenant_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "policy_acknowledgements_policy_document_id_version_idx" ON "policy_acknowledgements"("policy_document_id", "version");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "policy_acknowledgements_tenant_id_policy_document_id_user_i_key" ON "policy_acknowledgements"("tenant_id", "policy_document_id", "user_id", "version");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "policy_document_versions_tenant_id_idx" ON "policy_document_versions"("tenant_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "policy_document_versions_policy_document_id_idx" ON "policy_document_versions"("policy_document_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "policy_documents_tenant_id_next_review_at_idx" ON "policy_documents"("tenant_id", "next_review_at");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "policy_acknowledgements" ADD CONSTRAINT "policy_acknowledgements_policy_document_id_fkey" FOREIGN KEY ("policy_document_id") REFERENCES "policy_documents"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "policy_document_versions" ADD CONSTRAINT "policy_document_versions_policy_document_id_fkey" FOREIGN KEY ("policy_document_id") REFERENCES "policy_documents"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
|
|
-- AP5 — Row Level Security (F-04) für die neuen Mandanten-Tabellen.
|
|
ALTER TABLE "policy_acknowledgements" ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation ON "policy_acknowledgements";
|
|
CREATE POLICY tenant_isolation ON "policy_acknowledgements"
|
|
USING ("tenant_id" = current_setting('app.tenant_id', true))
|
|
WITH CHECK ("tenant_id" = current_setting('app.tenant_id', true));
|
|
ALTER TABLE "policy_acknowledgements" FORCE ROW LEVEL SECURITY;
|
|
ALTER TABLE "policy_document_versions" ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation ON "policy_document_versions";
|
|
CREATE POLICY tenant_isolation ON "policy_document_versions"
|
|
USING ("tenant_id" = current_setting('app.tenant_id', true))
|
|
WITH CHECK ("tenant_id" = current_setting('app.tenant_id', true));
|
|
ALTER TABLE "policy_document_versions" FORCE ROW LEVEL SECURITY;
|