66c6f7ee8f
- Create migration for BrainstormSession, BrainstormIdea, BrainstormNoteRef, BrainstormParticipant, BrainstormActivity, BrainstormShare, BrainstormSnapshot - Ghost cursor: only moves toward a target element, no random wandering, 120ms interval instead of 60fps, hidden when no target - Remove animate-ping that caused visual noise
160 lines
8.3 KiB
SQL
160 lines
8.3 KiB
SQL
CREATE TABLE "BrainstormSession" (
|
|
"id" TEXT NOT NULL,
|
|
"seedIdea" TEXT NOT NULL,
|
|
"sourceNoteId" TEXT,
|
|
"contextNoteIds" TEXT,
|
|
"exportedNoteId" TEXT,
|
|
"userId" TEXT NOT NULL,
|
|
"inviteToken" TEXT,
|
|
"inviteExpiry" TIMESTAMP(3),
|
|
"liveblocksRoomId" TEXT,
|
|
"isPublic" BOOLEAN NOT NULL DEFAULT false,
|
|
"guestCanEdit" BOOLEAN NOT NULL DEFAULT false,
|
|
"status" TEXT NOT NULL DEFAULT 'active',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "BrainstormSession_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "BrainstormIdea" (
|
|
"id" TEXT NOT NULL,
|
|
"sessionId" TEXT NOT NULL,
|
|
"waveNumber" INTEGER NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"description" TEXT NOT NULL,
|
|
"connectionToSeed" TEXT,
|
|
"noveltyScore" INTEGER,
|
|
"parentIdeaId" TEXT,
|
|
"convertedToNoteId" TEXT,
|
|
"relatedNoteIds" TEXT,
|
|
"status" TEXT NOT NULL DEFAULT 'active',
|
|
"positionX" DOUBLE PRECISION,
|
|
"positionY" DOUBLE PRECISION,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"createdBy" TEXT,
|
|
"createdByType" TEXT DEFAULT 'ai',
|
|
|
|
CONSTRAINT "BrainstormIdea_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "BrainstormNoteRef" (
|
|
"id" TEXT NOT NULL,
|
|
"ideaId" TEXT NOT NULL,
|
|
"noteId" TEXT,
|
|
"relation" TEXT NOT NULL,
|
|
"explanation" TEXT NOT NULL,
|
|
"verdict" TEXT NOT NULL DEFAULT 'unresolved',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"visibility" TEXT NOT NULL DEFAULT 'participants',
|
|
|
|
CONSTRAINT "BrainstormNoteRef_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "BrainstormParticipant" (
|
|
"id" TEXT NOT NULL,
|
|
"sessionId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"role" TEXT NOT NULL DEFAULT 'viewer',
|
|
"joinedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"lastSeenAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "BrainstormParticipant_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "BrainstormActivity" (
|
|
"id" TEXT NOT NULL,
|
|
"sessionId" TEXT NOT NULL,
|
|
"userId" TEXT,
|
|
"action" TEXT NOT NULL,
|
|
"details" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "BrainstormActivity_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "BrainstormShare" (
|
|
"id" TEXT NOT NULL,
|
|
"sessionId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"sharedBy" TEXT NOT NULL,
|
|
"status" TEXT NOT NULL DEFAULT 'pending',
|
|
"permission" TEXT NOT NULL DEFAULT 'editor',
|
|
"notifiedAt" TIMESTAMP(3),
|
|
"respondedAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "BrainstormShare_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "BrainstormSnapshot" (
|
|
"id" TEXT NOT NULL,
|
|
"sessionId" TEXT NOT NULL,
|
|
"activityId" TEXT,
|
|
"step" INTEGER NOT NULL,
|
|
"label" TEXT,
|
|
"ideaGraph" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "BrainstormSnapshot_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX "BrainstormSession_inviteToken_key" ON "BrainstormSession"("inviteToken");
|
|
CREATE INDEX "BrainstormSession_userId_idx" ON "BrainstormSession"("userId");
|
|
CREATE INDEX "BrainstormSession_userId_createdAt_idx" ON "BrainstormSession"("userId", "createdAt");
|
|
CREATE INDEX "BrainstormSession_inviteToken_idx" ON "BrainstormSession"("inviteToken");
|
|
CREATE INDEX "BrainstormSession_isPublic_idx" ON "BrainstormSession"("isPublic");
|
|
|
|
CREATE INDEX "BrainstormIdea_sessionId_idx" ON "BrainstormIdea"("sessionId");
|
|
CREATE INDEX "BrainstormIdea_waveNumber_idx" ON "BrainstormIdea"("waveNumber");
|
|
CREATE INDEX "BrainstormIdea_status_idx" ON "BrainstormIdea"("status");
|
|
CREATE INDEX "BrainstormIdea_parentIdeaId_idx" ON "BrainstormIdea"("parentIdeaId");
|
|
CREATE INDEX "BrainstormIdea_sessionId_status_idx" ON "BrainstormIdea"("sessionId", "status");
|
|
CREATE INDEX "BrainstormIdea_sessionId_waveNumber_createdAt_idx" ON "BrainstormIdea"("sessionId", "waveNumber", "createdAt");
|
|
|
|
CREATE INDEX "BrainstormNoteRef_ideaId_idx" ON "BrainstormNoteRef"("ideaId");
|
|
CREATE INDEX "BrainstormNoteRef_noteId_idx" ON "BrainstormNoteRef"("noteId");
|
|
CREATE INDEX "BrainstormNoteRef_noteId_relation_idx" ON "BrainstormNoteRef"("noteId", "relation");
|
|
CREATE INDEX "BrainstormNoteRef_visibility_idx" ON "BrainstormNoteRef"("visibility");
|
|
|
|
CREATE UNIQUE INDEX "BrainstormParticipant_sessionId_userId_key" ON "BrainstormParticipant"("sessionId", "userId");
|
|
CREATE INDEX "BrainstormParticipant_sessionId_idx" ON "BrainstormParticipant"("sessionId");
|
|
CREATE INDEX "BrainstormParticipant_userId_idx" ON "BrainstormParticipant"("userId");
|
|
CREATE INDEX "BrainstormParticipant_sessionId_userId_role_idx" ON "BrainstormParticipant"("sessionId", "userId", "role");
|
|
|
|
CREATE INDEX "BrainstormActivity_sessionId_createdAt_asc_idx" ON "BrainstormActivity"("sessionId", "createdAt");
|
|
CREATE INDEX "BrainstormActivity_sessionId_createdAt_desc_idx" ON "BrainstormActivity"("sessionId", "createdAt" DESC);
|
|
CREATE INDEX "BrainstormActivity_sessionId_createdAt_idx" ON "BrainstormActivity"("sessionId", "createdAt");
|
|
|
|
CREATE UNIQUE INDEX "BrainstormShare_sessionId_userId_key" ON "BrainstormShare"("sessionId", "userId");
|
|
CREATE INDEX "BrainstormShare_userId_idx" ON "BrainstormShare"("userId");
|
|
CREATE INDEX "BrainstormShare_status_idx" ON "BrainstormShare"("status");
|
|
|
|
CREATE INDEX "BrainstormSnapshot_sessionId_step_idx" ON "BrainstormSnapshot"("sessionId", "step");
|
|
CREATE INDEX "BrainstormSnapshot_sessionId_createdAt_idx" ON "BrainstormSnapshot"("sessionId", "createdAt");
|
|
CREATE INDEX "BrainstormSnapshot_activityId_idx" ON "BrainstormSnapshot"("activityId");
|
|
|
|
ALTER TABLE "BrainstormSession" ADD CONSTRAINT "BrainstormSession_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "BrainstormSession" ADD CONSTRAINT "BrainstormSession_sourceNoteId_fkey" FOREIGN KEY ("sourceNoteId") REFERENCES "Note"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
ALTER TABLE "BrainstormSession" ADD CONSTRAINT "BrainstormSession_exportedNoteId_fkey" FOREIGN KEY ("exportedNoteId") REFERENCES "Note"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "BrainstormIdea" ADD CONSTRAINT "BrainstormIdea_sessionId_fkey" FOREIGN KEY ("sessionId") REFERENCES "BrainstormSession"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "BrainstormIdea" ADD CONSTRAINT "BrainstormIdea_parentIdeaId_fkey" FOREIGN KEY ("parentIdeaId") REFERENCES "BrainstormIdea"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
ALTER TABLE "BrainstormIdea" ADD CONSTRAINT "BrainstormIdea_convertedToNoteId_fkey" FOREIGN KEY ("convertedToNoteId") REFERENCES "Note"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "BrainstormNoteRef" ADD CONSTRAINT "BrainstormNoteRef_ideaId_fkey" FOREIGN KEY ("ideaId") REFERENCES "BrainstormIdea"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "BrainstormNoteRef" ADD CONSTRAINT "BrainstormNoteRef_noteId_fkey" FOREIGN KEY ("noteId") REFERENCES "Note"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "BrainstormParticipant" ADD CONSTRAINT "BrainstormParticipant_sessionId_fkey" FOREIGN KEY ("sessionId") REFERENCES "BrainstormSession"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "BrainstormParticipant" ADD CONSTRAINT "BrainstormParticipant_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "BrainstormActivity" ADD CONSTRAINT "BrainstormActivity_sessionId_fkey" FOREIGN KEY ("sessionId") REFERENCES "BrainstormSession"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "BrainstormActivity" ADD CONSTRAINT "BrainstormActivity_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "BrainstormShare" ADD CONSTRAINT "BrainstormShare_sessionId_fkey" FOREIGN KEY ("sessionId") REFERENCES "BrainstormSession"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "BrainstormShare" ADD CONSTRAINT "BrainstormShare_sharedBy_fkey" FOREIGN KEY ("sharedBy") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "BrainstormShare" ADD CONSTRAINT "BrainstormShare_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "BrainstormSnapshot" ADD CONSTRAINT "BrainstormSnapshot_sessionId_fkey" FOREIGN KEY ("sessionId") REFERENCES "BrainstormSession"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|