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>
133 lines
4.5 KiB
SQL
133 lines
4.5 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "AssetType" AS ENUM ('INFORMATION', 'SYSTEM', 'APPLICATION', 'LOCATION', 'SUPPLIER', 'PERSON', 'DATA');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "AssetStatus" AS ENUM ('ACTIVE', 'PLANNED', 'RETIRED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ProcessAssetRole" AS ENUM ('PRIMARY', 'SECONDARY');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "assets" (
|
|
"id" TEXT NOT NULL,
|
|
"tenant_id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"type" "AssetType" NOT NULL,
|
|
"status" "AssetStatus" NOT NULL DEFAULT 'ACTIVE',
|
|
"owner_id" TEXT,
|
|
"location" TEXT,
|
|
"tags" TEXT[] DEFAULT ARRAY[]::TEXT[],
|
|
"confidentiality" INTEGER NOT NULL DEFAULT 1,
|
|
"integrity" INTEGER NOT NULL DEFAULT 1,
|
|
"availability" INTEGER NOT NULL DEFAULT 1,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
"created_by" TEXT,
|
|
|
|
CONSTRAINT "assets_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "asset_relations" (
|
|
"id" TEXT NOT NULL,
|
|
"tenant_id" TEXT NOT NULL,
|
|
"asset_id" TEXT NOT NULL,
|
|
"related_asset_id" TEXT NOT NULL,
|
|
"type" TEXT NOT NULL DEFAULT 'depends_on',
|
|
|
|
CONSTRAINT "asset_relations_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "processes" (
|
|
"id" TEXT NOT NULL,
|
|
"tenant_id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"owner_id" TEXT,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
"created_by" TEXT,
|
|
|
|
CONSTRAINT "processes_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "process_assets" (
|
|
"id" TEXT NOT NULL,
|
|
"tenant_id" TEXT NOT NULL,
|
|
"process_id" TEXT NOT NULL,
|
|
"asset_id" TEXT NOT NULL,
|
|
"role" "ProcessAssetRole" NOT NULL DEFAULT 'SECONDARY',
|
|
|
|
CONSTRAINT "process_assets_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "bia_entries" (
|
|
"id" TEXT NOT NULL,
|
|
"tenant_id" TEXT NOT NULL,
|
|
"process_id" TEXT NOT NULL,
|
|
"rto_hours" INTEGER,
|
|
"rpo_hours" INTEGER,
|
|
"mtd_hours" INTEGER,
|
|
"impact_c" INTEGER NOT NULL DEFAULT 1,
|
|
"impact_i" INTEGER NOT NULL DEFAULT 1,
|
|
"impact_a" INTEGER NOT NULL DEFAULT 1,
|
|
"criticality" INTEGER NOT NULL DEFAULT 1,
|
|
"notes" TEXT,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "bia_entries_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "assets_tenant_id_idx" ON "assets"("tenant_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "assets_tenant_id_type_idx" ON "assets"("tenant_id", "type");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "asset_relations_tenant_id_idx" ON "asset_relations"("tenant_id");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "asset_relations_asset_id_related_asset_id_type_key" ON "asset_relations"("asset_id", "related_asset_id", "type");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "processes_tenant_id_idx" ON "processes"("tenant_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "process_assets_tenant_id_idx" ON "process_assets"("tenant_id");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "process_assets_process_id_asset_id_key" ON "process_assets"("process_id", "asset_id");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "bia_entries_process_id_key" ON "bia_entries"("process_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "bia_entries_tenant_id_idx" ON "bia_entries"("tenant_id");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "assets" ADD CONSTRAINT "assets_owner_id_fkey" FOREIGN KEY ("owner_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "asset_relations" ADD CONSTRAINT "asset_relations_asset_id_fkey" FOREIGN KEY ("asset_id") REFERENCES "assets"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "asset_relations" ADD CONSTRAINT "asset_relations_related_asset_id_fkey" FOREIGN KEY ("related_asset_id") REFERENCES "assets"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "processes" ADD CONSTRAINT "processes_owner_id_fkey" FOREIGN KEY ("owner_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "process_assets" ADD CONSTRAINT "process_assets_process_id_fkey" FOREIGN KEY ("process_id") REFERENCES "processes"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "process_assets" ADD CONSTRAINT "process_assets_asset_id_fkey" FOREIGN KEY ("asset_id") REFERENCES "assets"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "bia_entries" ADD CONSTRAINT "bia_entries_process_id_fkey" FOREIGN KEY ("process_id") REFERENCES "processes"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|