60 lines
2.5 KiB
SQL
60 lines
2.5 KiB
SQL
-- WatchLink V1 completion support: persistent realtime state, messages, audit,
|
|
-- invites, disabled users, and protected personal rooms.
|
|
|
|
CREATE TYPE "InviteStatus" AS ENUM ('ACTIVE', 'REVOKED', 'USED', 'EXPIRED');
|
|
|
|
ALTER TABLE "User" ADD COLUMN "disabledAt" TIMESTAMP(3);
|
|
ALTER TABLE "Room" ADD COLUMN "isPersonal" BOOLEAN NOT NULL DEFAULT false;
|
|
|
|
UPDATE "Room"
|
|
SET "isPersonal" = true
|
|
WHERE "slug" LIKE '@%';
|
|
|
|
CREATE TABLE "RoomMessage" (
|
|
"id" TEXT NOT NULL,
|
|
"roomId" TEXT NOT NULL,
|
|
"userId" TEXT,
|
|
"body" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "RoomMessage_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "AuditEvent" (
|
|
"id" TEXT NOT NULL,
|
|
"actorId" TEXT,
|
|
"roomId" TEXT,
|
|
"action" TEXT NOT NULL,
|
|
"metadata" JSONB,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "AuditEvent_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "Invite" (
|
|
"id" TEXT NOT NULL,
|
|
"code" TEXT NOT NULL,
|
|
"roomId" TEXT,
|
|
"creatorId" TEXT,
|
|
"status" "InviteStatus" NOT NULL DEFAULT 'ACTIVE',
|
|
"expiresAt" TIMESTAMP(3),
|
|
"usedById" TEXT,
|
|
"usedAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Invite_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX "Invite_code_key" ON "Invite"("code");
|
|
CREATE INDEX "RoomMessage_roomId_createdAt_idx" ON "RoomMessage"("roomId", "createdAt");
|
|
CREATE INDEX "AuditEvent_roomId_createdAt_idx" ON "AuditEvent"("roomId", "createdAt");
|
|
CREATE INDEX "AuditEvent_actorId_createdAt_idx" ON "AuditEvent"("actorId", "createdAt");
|
|
CREATE INDEX "Invite_roomId_status_idx" ON "Invite"("roomId", "status");
|
|
|
|
ALTER TABLE "RoomMessage" ADD CONSTRAINT "RoomMessage_roomId_fkey" FOREIGN KEY ("roomId") REFERENCES "Room"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "RoomMessage" ADD CONSTRAINT "RoomMessage_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
ALTER TABLE "AuditEvent" ADD CONSTRAINT "AuditEvent_actorId_fkey" FOREIGN KEY ("actorId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
ALTER TABLE "AuditEvent" ADD CONSTRAINT "AuditEvent_roomId_fkey" FOREIGN KEY ("roomId") REFERENCES "Room"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
ALTER TABLE "Invite" ADD CONSTRAINT "Invite_roomId_fkey" FOREIGN KEY ("roomId") REFERENCES "Room"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "Invite" ADD CONSTRAINT "Invite_creatorId_fkey" FOREIGN KEY ("creatorId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|