MrSphay
f29339cf12
Some checks failed
Build and publish Docker image / docker (push) Failing after 2m33s
245 lines
8.3 KiB
SQL
245 lines
8.3 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "Role" AS ENUM ('VISITOR', 'MEMBER', 'ADMIN');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "Language" AS ENUM ('DE', 'EN');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "TrustLevel" AS ENUM ('UNKNOWN', 'LOW', 'MEDIUM', 'HIGH', 'PRIMARY');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "EntryStatus" AS ENUM ('DRAFT', 'PUBLISHED', 'BLOCKED', 'ARCHIVED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ContributionStatus" AS ENUM ('PENDING', 'APPROVED', 'REJECTED', 'NEEDS_ADMIN_REVIEW');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ModerationOutcome" AS ENUM ('APPROVE', 'REJECT', 'ESCALATE');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ContributionOrigin" AS ENUM ('COMMUNITY', 'AI_RESEARCH', 'ADMIN');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT,
|
|
"email" TEXT,
|
|
"emailVerified" TIMESTAMP(3),
|
|
"image" TEXT,
|
|
"passwordHash" TEXT,
|
|
"role" "Role" NOT NULL DEFAULT 'MEMBER',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "User_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Account" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"type" TEXT NOT NULL,
|
|
"provider" TEXT NOT NULL,
|
|
"providerAccountId" TEXT NOT NULL,
|
|
"refresh_token" TEXT,
|
|
"access_token" TEXT,
|
|
"expires_at" INTEGER,
|
|
"token_type" TEXT,
|
|
"scope" TEXT,
|
|
"id_token" TEXT,
|
|
"session_state" TEXT,
|
|
CONSTRAINT "Account_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Session" (
|
|
"id" TEXT NOT NULL,
|
|
"sessionToken" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"expires" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "Session_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "VerificationToken" (
|
|
"identifier" TEXT NOT NULL,
|
|
"token" TEXT NOT NULL,
|
|
"expires" TIMESTAMP(3) NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Source" (
|
|
"id" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"url" TEXT NOT NULL,
|
|
"author" TEXT,
|
|
"institution" TEXT,
|
|
"license" TEXT,
|
|
"language" "Language" NOT NULL,
|
|
"retrievedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"trustLevel" "TrustLevel" NOT NULL DEFAULT 'UNKNOWN',
|
|
"notes" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "Source_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "KnowledgeEntry" (
|
|
"id" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"slug" TEXT NOT NULL,
|
|
"language" "Language" NOT NULL,
|
|
"summary" TEXT NOT NULL,
|
|
"content" JSONB NOT NULL,
|
|
"timelineStart" TIMESTAMP(3),
|
|
"timelineEnd" TIMESTAMP(3),
|
|
"tags" TEXT[],
|
|
"status" "EntryStatus" NOT NULL DEFAULT 'DRAFT',
|
|
"version" INTEGER NOT NULL DEFAULT 1,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "KnowledgeEntry_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "KnowledgeEntrySource" (
|
|
"entryId" TEXT NOT NULL,
|
|
"sourceId" TEXT NOT NULL,
|
|
"note" TEXT,
|
|
CONSTRAINT "KnowledgeEntrySource_pkey" PRIMARY KEY ("entryId","sourceId")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Contribution" (
|
|
"id" TEXT NOT NULL,
|
|
"origin" "ContributionOrigin" NOT NULL,
|
|
"status" "ContributionStatus" NOT NULL DEFAULT 'PENDING',
|
|
"title" TEXT NOT NULL,
|
|
"language" "Language" NOT NULL,
|
|
"proposedContent" JSONB NOT NULL,
|
|
"rationale" TEXT,
|
|
"authorId" TEXT,
|
|
"entryId" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "Contribution_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ContributionSource" (
|
|
"contributionId" TEXT NOT NULL,
|
|
"sourceId" TEXT NOT NULL,
|
|
CONSTRAINT "ContributionSource_pkey" PRIMARY KEY ("contributionId","sourceId")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ModerationDecision" (
|
|
"id" TEXT NOT NULL,
|
|
"contributionId" TEXT NOT NULL,
|
|
"outcome" "ModerationOutcome" NOT NULL,
|
|
"riskCategories" TEXT[],
|
|
"confidence" DOUBLE PRECISION NOT NULL,
|
|
"reasoning" TEXT NOT NULL,
|
|
"model" TEXT NOT NULL,
|
|
"promptVersion" TEXT NOT NULL,
|
|
"rawResponse" JSONB,
|
|
"overriddenById" TEXT,
|
|
"overrideReason" TEXT,
|
|
"overriddenAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "ModerationDecision_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "QuestionAnswer" (
|
|
"id" TEXT NOT NULL,
|
|
"question" TEXT NOT NULL,
|
|
"language" "Language" NOT NULL,
|
|
"answer" TEXT NOT NULL,
|
|
"model" TEXT NOT NULL,
|
|
"tokenCount" INTEGER,
|
|
"costCents" INTEGER,
|
|
"entryId" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "QuestionAnswer_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "AnswerSource" (
|
|
"answerId" TEXT NOT NULL,
|
|
"sourceId" TEXT NOT NULL,
|
|
CONSTRAINT "AnswerSource_pkey" PRIMARY KEY ("answerId","sourceId")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Account_provider_providerAccountId_key" ON "Account"("provider", "providerAccountId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Session_sessionToken_key" ON "Session"("sessionToken");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "VerificationToken_token_key" ON "VerificationToken"("token");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "VerificationToken_identifier_token_key" ON "VerificationToken"("identifier", "token");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Source_language_trustLevel_idx" ON "Source"("language", "trustLevel");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Source_url_language_key" ON "Source"("url", "language");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "KnowledgeEntry_status_language_idx" ON "KnowledgeEntry"("status", "language");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "KnowledgeEntry_slug_language_key" ON "KnowledgeEntry"("slug", "language");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Contribution_status_origin_idx" ON "Contribution"("status", "origin");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "ModerationDecision_outcome_createdAt_idx" ON "ModerationDecision"("outcome", "createdAt");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Account" ADD CONSTRAINT "Account_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Session" ADD CONSTRAINT "Session_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "KnowledgeEntrySource" ADD CONSTRAINT "KnowledgeEntrySource_entryId_fkey" FOREIGN KEY ("entryId") REFERENCES "KnowledgeEntry"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "KnowledgeEntrySource" ADD CONSTRAINT "KnowledgeEntrySource_sourceId_fkey" FOREIGN KEY ("sourceId") REFERENCES "Source"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Contribution" ADD CONSTRAINT "Contribution_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Contribution" ADD CONSTRAINT "Contribution_entryId_fkey" FOREIGN KEY ("entryId") REFERENCES "KnowledgeEntry"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ContributionSource" ADD CONSTRAINT "ContributionSource_contributionId_fkey" FOREIGN KEY ("contributionId") REFERENCES "Contribution"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ContributionSource" ADD CONSTRAINT "ContributionSource_sourceId_fkey" FOREIGN KEY ("sourceId") REFERENCES "Source"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ModerationDecision" ADD CONSTRAINT "ModerationDecision_contributionId_fkey" FOREIGN KEY ("contributionId") REFERENCES "Contribution"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ModerationDecision" ADD CONSTRAINT "ModerationDecision_overriddenById_fkey" FOREIGN KEY ("overriddenById") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "QuestionAnswer" ADD CONSTRAINT "QuestionAnswer_entryId_fkey" FOREIGN KEY ("entryId") REFERENCES "KnowledgeEntry"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "AnswerSource" ADD CONSTRAINT "AnswerSource_answerId_fkey" FOREIGN KEY ("answerId") REFERENCES "QuestionAnswer"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "AnswerSource" ADD CONSTRAINT "AnswerSource_sourceId_fkey" FOREIGN KEY ("sourceId") REFERENCES "Source"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|