linusandClaude Sonnet 5
8224dff26f
Generated with a real local PostgreSQL 16 and applied cleanly (prisma migrate dev --name init). Covers the full current schema: Kc, Gemeinde, Role/MembershipStatus enums, User (authentikSub nullable, passwordHash, kcId, isLeitungsteam), Membership.status, GuestAccount, TeamerInvite, Wahl/Workshop/Teilnehmer/ForceZuteilung/Zuteilung, File, Chat*, Sync* . Backend boots against the real DB and the smoke-tested routes (/, guest login, onboarding invite lookup, protected /api/kc) behave correctly. Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
329 lines
11 KiB
SQL
329 lines
11 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "Role" AS ENUM ('LEITUNGSTEAM', 'GEMEINDE_VERANTWORTLICHER', 'GEMEINDE_TEAMER');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "MembershipStatus" AS ENUM ('ACTIVE', 'PENDING');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "FileVisibility" AS ENUM ('ALLE', 'ALLE_AUSSER_KONFIS', 'NUR_LT');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ChatChannelType" AS ENUM ('GEMEINDE_GRUPPE', 'DIREKT', 'LT_UEBERGREIFEND', 'BROADCAST');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "SyncOperation" AS ENUM ('CREATE', 'UPDATE', 'DELETE');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Kc" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"inviteCode" TEXT NOT NULL,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Kc_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Gemeinde" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"kcId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Gemeinde_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" TEXT NOT NULL,
|
|
"authentikSub" TEXT,
|
|
"email" TEXT NOT NULL,
|
|
"firstName" TEXT NOT NULL,
|
|
"lastName" TEXT NOT NULL,
|
|
"passwordHash" TEXT,
|
|
"kcId" TEXT,
|
|
"isLeitungsteam" BOOLEAN NOT NULL DEFAULT false,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "User_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Membership" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"kcId" TEXT NOT NULL,
|
|
"gemeindeId" TEXT,
|
|
"role" "Role" NOT NULL,
|
|
"status" "MembershipStatus" NOT NULL DEFAULT 'ACTIVE',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Membership_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "GuestAccount" (
|
|
"id" TEXT NOT NULL,
|
|
"kcId" TEXT NOT NULL,
|
|
"gemeindeId" TEXT,
|
|
"firstName" TEXT NOT NULL,
|
|
"lastName" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "GuestAccount_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "TeamerInvite" (
|
|
"id" TEXT NOT NULL,
|
|
"kcId" TEXT NOT NULL,
|
|
"gemeindeId" TEXT NOT NULL,
|
|
"token" TEXT NOT NULL,
|
|
"email" TEXT,
|
|
"maxUses" INTEGER,
|
|
"usedCount" INTEGER NOT NULL DEFAULT 0,
|
|
"expiresAt" TIMESTAMP(3),
|
|
"revokedAt" TIMESTAMP(3),
|
|
"createdByUserId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "TeamerInvite_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Wahl" (
|
|
"id" TEXT NOT NULL,
|
|
"kcId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"datumsSchluessel" TEXT NOT NULL,
|
|
"teil" TEXT NOT NULL,
|
|
"isOpen" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Wahl_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Workshop" (
|
|
"id" TEXT NOT NULL,
|
|
"wahlId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"kapazitaet" INTEGER NOT NULL,
|
|
"minTeilnehmer" INTEGER NOT NULL DEFAULT 0,
|
|
|
|
CONSTRAINT "Workshop_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Teilnehmer" (
|
|
"id" TEXT NOT NULL,
|
|
"wahlId" TEXT NOT NULL,
|
|
"guestAccountId" TEXT NOT NULL,
|
|
"prioritaeten" JSONB NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Teilnehmer_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ForceZuteilung" (
|
|
"id" TEXT NOT NULL,
|
|
"wahlId" TEXT NOT NULL,
|
|
"teilnehmerId" TEXT NOT NULL,
|
|
"workshopId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "ForceZuteilung_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Zuteilung" (
|
|
"id" TEXT NOT NULL,
|
|
"teilnehmerId" TEXT NOT NULL,
|
|
"workshopId" TEXT,
|
|
"wunschRang" INTEGER NOT NULL DEFAULT -1,
|
|
"isForced" BOOLEAN NOT NULL DEFAULT false,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Zuteilung_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "File" (
|
|
"id" TEXT NOT NULL,
|
|
"kcId" TEXT NOT NULL,
|
|
"storageKey" TEXT NOT NULL,
|
|
"filename" TEXT NOT NULL,
|
|
"visibility" "FileVisibility" NOT NULL,
|
|
"uploadedById" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "File_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ChatChannel" (
|
|
"id" TEXT NOT NULL,
|
|
"kcId" TEXT NOT NULL,
|
|
"type" "ChatChannelType" NOT NULL,
|
|
"gemeindeId" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "ChatChannel_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ChatParticipant" (
|
|
"id" TEXT NOT NULL,
|
|
"channelId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "ChatParticipant_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ChatMessage" (
|
|
"id" TEXT NOT NULL,
|
|
"channelId" TEXT NOT NULL,
|
|
"senderUserId" TEXT,
|
|
"senderGuestId" TEXT,
|
|
"body" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "ChatMessage_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "SyncLogEntry" (
|
|
"id" TEXT NOT NULL,
|
|
"sequence" SERIAL NOT NULL,
|
|
"model" TEXT NOT NULL,
|
|
"recordId" TEXT NOT NULL,
|
|
"operation" "SyncOperation" NOT NULL,
|
|
"payload" JSONB NOT NULL,
|
|
"originId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "SyncLogEntry_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "SyncCursor" (
|
|
"id" TEXT NOT NULL,
|
|
"peerId" TEXT NOT NULL,
|
|
"lastPushedSequence" INTEGER NOT NULL DEFAULT 0,
|
|
"lastPulledSequence" INTEGER NOT NULL DEFAULT 0,
|
|
|
|
CONSTRAINT "SyncCursor_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Kc_inviteCode_key" ON "Kc"("inviteCode");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Gemeinde_kcId_name_key" ON "Gemeinde"("kcId", "name");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_authentikSub_key" ON "User"("authentikSub");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Membership_userId_kcId_gemeindeId_key" ON "Membership"("userId", "kcId", "gemeindeId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "TeamerInvite_token_key" ON "TeamerInvite"("token");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Teilnehmer_wahlId_guestAccountId_key" ON "Teilnehmer"("wahlId", "guestAccountId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "ForceZuteilung_teilnehmerId_key" ON "ForceZuteilung"("teilnehmerId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Zuteilung_teilnehmerId_key" ON "Zuteilung"("teilnehmerId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "ChatParticipant_channelId_userId_key" ON "ChatParticipant"("channelId", "userId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "SyncCursor_peerId_key" ON "SyncCursor"("peerId");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Gemeinde" ADD CONSTRAINT "Gemeinde_kcId_fkey" FOREIGN KEY ("kcId") REFERENCES "Kc"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "User" ADD CONSTRAINT "User_kcId_fkey" FOREIGN KEY ("kcId") REFERENCES "Kc"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Membership" ADD CONSTRAINT "Membership_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Membership" ADD CONSTRAINT "Membership_kcId_fkey" FOREIGN KEY ("kcId") REFERENCES "Kc"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Membership" ADD CONSTRAINT "Membership_gemeindeId_fkey" FOREIGN KEY ("gemeindeId") REFERENCES "Gemeinde"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "GuestAccount" ADD CONSTRAINT "GuestAccount_kcId_fkey" FOREIGN KEY ("kcId") REFERENCES "Kc"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "GuestAccount" ADD CONSTRAINT "GuestAccount_gemeindeId_fkey" FOREIGN KEY ("gemeindeId") REFERENCES "Gemeinde"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "TeamerInvite" ADD CONSTRAINT "TeamerInvite_kcId_fkey" FOREIGN KEY ("kcId") REFERENCES "Kc"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "TeamerInvite" ADD CONSTRAINT "TeamerInvite_gemeindeId_fkey" FOREIGN KEY ("gemeindeId") REFERENCES "Gemeinde"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Wahl" ADD CONSTRAINT "Wahl_kcId_fkey" FOREIGN KEY ("kcId") REFERENCES "Kc"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Workshop" ADD CONSTRAINT "Workshop_wahlId_fkey" FOREIGN KEY ("wahlId") REFERENCES "Wahl"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Teilnehmer" ADD CONSTRAINT "Teilnehmer_wahlId_fkey" FOREIGN KEY ("wahlId") REFERENCES "Wahl"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Teilnehmer" ADD CONSTRAINT "Teilnehmer_guestAccountId_fkey" FOREIGN KEY ("guestAccountId") REFERENCES "GuestAccount"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ForceZuteilung" ADD CONSTRAINT "ForceZuteilung_wahlId_fkey" FOREIGN KEY ("wahlId") REFERENCES "Wahl"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ForceZuteilung" ADD CONSTRAINT "ForceZuteilung_teilnehmerId_fkey" FOREIGN KEY ("teilnehmerId") REFERENCES "Teilnehmer"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ForceZuteilung" ADD CONSTRAINT "ForceZuteilung_workshopId_fkey" FOREIGN KEY ("workshopId") REFERENCES "Workshop"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Zuteilung" ADD CONSTRAINT "Zuteilung_teilnehmerId_fkey" FOREIGN KEY ("teilnehmerId") REFERENCES "Teilnehmer"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Zuteilung" ADD CONSTRAINT "Zuteilung_workshopId_fkey" FOREIGN KEY ("workshopId") REFERENCES "Workshop"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "File" ADD CONSTRAINT "File_kcId_fkey" FOREIGN KEY ("kcId") REFERENCES "Kc"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ChatChannel" ADD CONSTRAINT "ChatChannel_kcId_fkey" FOREIGN KEY ("kcId") REFERENCES "Kc"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ChatParticipant" ADD CONSTRAINT "ChatParticipant_channelId_fkey" FOREIGN KEY ("channelId") REFERENCES "ChatChannel"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ChatParticipant" ADD CONSTRAINT "ChatParticipant_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ChatMessage" ADD CONSTRAINT "ChatMessage_channelId_fkey" FOREIGN KEY ("channelId") REFERENCES "ChatChannel"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ChatMessage" ADD CONSTRAINT "ChatMessage_senderUserId_fkey" FOREIGN KEY ("senderUserId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ChatMessage" ADD CONSTRAINT "ChatMessage_senderGuestId_fkey" FOREIGN KEY ("senderGuestId") REFERENCES "GuestAccount"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|