6d3cb9d678
- Add Dockerfile and docker-compose.yml for VPS/LXC deployment. - Update README with setup, environment variables, and deployment notes. - Commit initial Prisma migration for SQLite schema. - Configure Next.js for standalone output and dev origin allowlist.
104 lines
3.3 KiB
SQL
104 lines
3.3 KiB
SQL
-- CreateTable
|
|
CREATE TABLE "AdminUser" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"username" TEXT NOT NULL,
|
|
"passwordHash" TEXT NOT NULL,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Source" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"name" TEXT NOT NULL,
|
|
"baseUrl" TEXT,
|
|
"apiCode" TEXT NOT NULL,
|
|
"defaultBias" TEXT,
|
|
"credibilityScore" REAL NOT NULL DEFAULT 5,
|
|
"active" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Article" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"title" TEXT NOT NULL,
|
|
"summary" TEXT,
|
|
"url" TEXT NOT NULL,
|
|
"sourceId" TEXT NOT NULL,
|
|
"imageUrl" TEXT,
|
|
"publishedAt" DATETIME,
|
|
"fetchedAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"primaryBias" TEXT NOT NULL,
|
|
"biasConfidence" REAL NOT NULL DEFAULT 0,
|
|
"politicalTags" TEXT NOT NULL DEFAULT '[]',
|
|
"rankScore" REAL NOT NULL DEFAULT 0,
|
|
"credibilityScore" REAL NOT NULL DEFAULT 5,
|
|
"status" TEXT NOT NULL DEFAULT 'DRAFT',
|
|
"topics" TEXT NOT NULL DEFAULT '[]',
|
|
"rawData" TEXT NOT NULL DEFAULT '{}',
|
|
"aiExplanation" TEXT,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL,
|
|
CONSTRAINT "Article_sourceId_fkey" FOREIGN KEY ("sourceId") REFERENCES "Source" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ApiKey" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"provider" TEXT NOT NULL,
|
|
"encryptedKey" TEXT NOT NULL,
|
|
"label" TEXT,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"lastUsedAt" DATETIME,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "SearchRule" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"name" TEXT NOT NULL,
|
|
"query" TEXT NOT NULL,
|
|
"sources" TEXT NOT NULL DEFAULT '[]',
|
|
"schedule" TEXT NOT NULL DEFAULT '0 */6 * * *',
|
|
"maxResults" INTEGER NOT NULL DEFAULT 20,
|
|
"autoPublish" BOOLEAN NOT NULL DEFAULT false,
|
|
"minRankScore" REAL NOT NULL DEFAULT 5,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"lastRunAt" DATETIME,
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "SearchRun" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"ruleId" TEXT NOT NULL,
|
|
"status" TEXT NOT NULL DEFAULT 'RUNNING',
|
|
"articlesFound" INTEGER NOT NULL DEFAULT 0,
|
|
"error" TEXT,
|
|
"startedAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"endedAt" DATETIME,
|
|
CONSTRAINT "SearchRun_ruleId_fkey" FOREIGN KEY ("ruleId") REFERENCES "SearchRule" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "AdminUser_username_key" ON "AdminUser"("username");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Source_apiCode_key" ON "Source"("apiCode");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Article_primaryBias_status_idx" ON "Article"("primaryBias", "status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Article_status_publishedAt_idx" ON "Article"("status", "publishedAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Article_rankScore_idx" ON "Article"("rankScore");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "ApiKey_provider_isActive_key" ON "ApiKey"("provider", "isActive");
|