268 lines
8.8 KiB
SQL
268 lines
8.8 KiB
SQL
-- Enable pgvector extension
|
|
CREATE EXTENSION IF NOT EXISTS vector;
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ImportSourceType" AS ENUM ('file', 'url', 'manual');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ImportStatus" AS ENUM ('pending', 'processing', 'completed', 'failed');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "MessageRole" AS ENUM ('user', 'assistant', 'system');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "UserRole" AS ENUM ('USER', 'ADMIN');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "MemoryType" AS ENUM ('conversation', 'character');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Character" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"avatarUrl" TEXT,
|
|
"personalityPrompt" TEXT NOT NULL,
|
|
"attributes" JSONB NOT NULL DEFAULT '{}',
|
|
"config" JSONB NOT NULL DEFAULT '{}',
|
|
"isPublic" BOOLEAN NOT NULL DEFAULT false,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "Character_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "CharacterKnowledge" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"sourceType" "ImportSourceType" NOT NULL,
|
|
"sourceName" TEXT NOT NULL,
|
|
"mimeType" TEXT,
|
|
"fileSize" BIGINT,
|
|
"rawContent" TEXT,
|
|
"status" "ImportStatus" NOT NULL DEFAULT 'pending',
|
|
"processingInfo" JSONB,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
"characterId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "CharacterKnowledge_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Conversation" (
|
|
"id" TEXT NOT NULL,
|
|
"title" TEXT,
|
|
"messageCount" INTEGER NOT NULL DEFAULT 0,
|
|
"totalTokens" INTEGER NOT NULL DEFAULT 0,
|
|
"settings" JSONB NOT NULL DEFAULT '{}',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"characterId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "Conversation_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ConversationParticipant" (
|
|
"id" TEXT NOT NULL,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"autoRespond" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"conversationId" TEXT NOT NULL,
|
|
"characterId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "ConversationParticipant_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ImportDocument" (
|
|
"id" TEXT NOT NULL,
|
|
"sourceType" "ImportSourceType" NOT NULL,
|
|
"sourceName" TEXT NOT NULL,
|
|
"mimeType" TEXT,
|
|
"fileSize" BIGINT,
|
|
"content" TEXT,
|
|
"status" "ImportStatus" NOT NULL DEFAULT 'pending',
|
|
"errorMessage" TEXT,
|
|
"metadata" JSONB,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "ImportDocument_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Message" (
|
|
"id" TEXT NOT NULL,
|
|
"role" "MessageRole" NOT NULL,
|
|
"content" TEXT NOT NULL,
|
|
"tokensUsed" INTEGER,
|
|
"model" TEXT,
|
|
"metadata" JSONB,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"conversationId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "Message_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "StoryBranch" (
|
|
"id" TEXT NOT NULL,
|
|
"title" TEXT,
|
|
"content" TEXT NOT NULL,
|
|
"userDirection" TEXT NOT NULL,
|
|
"generationParams" JSONB,
|
|
"depth" INTEGER NOT NULL DEFAULT 0,
|
|
"branchOrder" INTEGER NOT NULL DEFAULT 0,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"conversationId" TEXT NOT NULL,
|
|
"parentId" TEXT,
|
|
|
|
CONSTRAINT "StoryBranch_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"username" TEXT NOT NULL,
|
|
"passwordHash" TEXT,
|
|
"keycloakSub" TEXT,
|
|
"role" "UserRole" NOT NULL DEFAULT 'USER',
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "User_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "VectorMemory" (
|
|
"id" TEXT NOT NULL,
|
|
"content" TEXT NOT NULL,
|
|
"embedding" vector,
|
|
"memoryType" "MemoryType" NOT NULL DEFAULT 'conversation',
|
|
"metadata" JSONB,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"conversationId" TEXT,
|
|
"characterId" TEXT,
|
|
"knowledgeId" TEXT,
|
|
|
|
CONSTRAINT "VectorMemory_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Character_userId_idx" ON "Character"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Character_name_idx" ON "Character"("name");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "CharacterKnowledge_characterId_idx" ON "CharacterKnowledge"("characterId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "CharacterKnowledge_status_idx" ON "CharacterKnowledge"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Conversation_userId_idx" ON "Conversation"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Conversation_characterId_idx" ON "Conversation"("characterId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Conversation_createdAt_idx" ON "Conversation"("createdAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "ConversationParticipant_conversationId_idx" ON "ConversationParticipant"("conversationId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "ConversationParticipant_conversationId_characterId_key" ON "ConversationParticipant"("conversationId", "characterId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "ImportDocument_userId_idx" ON "ImportDocument"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "ImportDocument_status_idx" ON "ImportDocument"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Message_conversationId_idx" ON "Message"("conversationId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Message_createdAt_idx" ON "Message"("createdAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Message_conversationId_createdAt_idx" ON "Message"("conversationId", "createdAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "StoryBranch_conversationId_idx" ON "StoryBranch"("conversationId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "StoryBranch_parentId_idx" ON "StoryBranch"("parentId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_username_key" ON "User"("username");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_keycloakSub_key" ON "User"("keycloakSub");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "User_email_idx" ON "User"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "User_keycloakSub_idx" ON "User"("keycloakSub");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "VectorMemory_conversationId_idx" ON "VectorMemory"("conversationId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "VectorMemory_characterId_idx" ON "VectorMemory"("characterId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "VectorMemory_knowledgeId_idx" ON "VectorMemory"("knowledgeId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "VectorMemory_memoryType_idx" ON "VectorMemory"("memoryType");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Character" ADD CONSTRAINT "Character_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "CharacterKnowledge" ADD CONSTRAINT "CharacterKnowledge_characterId_fkey" FOREIGN KEY ("characterId") REFERENCES "Character"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Conversation" ADD CONSTRAINT "Conversation_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Conversation" ADD CONSTRAINT "Conversation_characterId_fkey" FOREIGN KEY ("characterId") REFERENCES "Character"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ConversationParticipant" ADD CONSTRAINT "ConversationParticipant_conversationId_fkey" FOREIGN KEY ("conversationId") REFERENCES "Conversation"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ImportDocument" ADD CONSTRAINT "ImportDocument_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Message" ADD CONSTRAINT "Message_conversationId_fkey" FOREIGN KEY ("conversationId") REFERENCES "Conversation"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "StoryBranch" ADD CONSTRAINT "StoryBranch_conversationId_fkey" FOREIGN KEY ("conversationId") REFERENCES "Conversation"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "StoryBranch" ADD CONSTRAINT "StoryBranch_parentId_fkey" FOREIGN KEY ("parentId") REFERENCES "StoryBranch"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "VectorMemory" ADD CONSTRAINT "VectorMemory_conversationId_fkey" FOREIGN KEY ("conversationId") REFERENCES "Conversation"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "VectorMemory" ADD CONSTRAINT "VectorMemory_characterId_fkey" FOREIGN KEY ("characterId") REFERENCES "Character"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "VectorMemory" ADD CONSTRAINT "VectorMemory_knowledgeId_fkey" FOREIGN KEY ("knowledgeId") REFERENCES "CharacterKnowledge"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|