phptits
0d6cd37579
- Monorepo: NestJS API (Fastify) + React web (antd/ProComponents) + shared packages - PostgreSQL/Prisma (unified resources, relations graph, audit, events, jobs) - Auth/RBAC/workspace isolation, credential vault (AES-256-GCM) - Servers/SSH, Docker discovery & lifecycle, Job Center - Websites & deployments (git provider credential clone, env, health verification) - Domains/DNS (Cloudflare adapter), certificates (TLS probe), monitoring - Automation (cron/event/webhook HMAC + notifications), AI Gateway, MCP (29 tools) - AI chat window (LLM function-calling + rules fallback, SSE streaming, approvals) - Notes: Vditor WYSIWYG markdown editor, non-blocking autosave - Tests: 35 unit/security tests; UI audit clean (desktop+mobile, deep links)
174 lines
6.0 KiB
SQL
174 lines
6.0 KiB
SQL
-- AlterTable
|
|
ALTER TABLE "deployments" ADD COLUMN "buildStrategy" TEXT,
|
|
ADD COLUMN "deployStrategy" TEXT,
|
|
ADD COLUMN "environment" TEXT,
|
|
ADD COLUMN "verification" JSONB;
|
|
|
|
-- AlterTable
|
|
ALTER TABLE "websites" ADD COLUMN "environment" TEXT NOT NULL DEFAULT 'production',
|
|
ADD COLUMN "repositoryId" TEXT;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "GitConnection" (
|
|
"id" TEXT NOT NULL,
|
|
"workspaceId" TEXT NOT NULL,
|
|
"resourceId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"provider" TEXT NOT NULL,
|
|
"apiBase" TEXT,
|
|
"credentialId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "GitConnection_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Repository" (
|
|
"id" TEXT NOT NULL,
|
|
"workspaceId" TEXT NOT NULL,
|
|
"resourceId" TEXT NOT NULL,
|
|
"connectionId" TEXT,
|
|
"provider" TEXT NOT NULL DEFAULT 'GENERIC',
|
|
"fullName" TEXT NOT NULL,
|
|
"cloneUrl" TEXT NOT NULL,
|
|
"defaultBranch" TEXT NOT NULL DEFAULT 'main',
|
|
"visibility" TEXT NOT NULL DEFAULT 'private',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Repository_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Task" (
|
|
"id" TEXT NOT NULL,
|
|
"workspaceId" TEXT NOT NULL,
|
|
"resourceId" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"description" TEXT NOT NULL DEFAULT '',
|
|
"status" TEXT NOT NULL DEFAULT 'TODO',
|
|
"priority" TEXT NOT NULL DEFAULT 'MEDIUM',
|
|
"labels" JSONB NOT NULL DEFAULT '[]',
|
|
"dueAt" TIMESTAMP(3),
|
|
"projectId" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Task_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Note" (
|
|
"id" TEXT NOT NULL,
|
|
"workspaceId" TEXT NOT NULL,
|
|
"resourceId" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"content" TEXT NOT NULL DEFAULT '',
|
|
"tags" JSONB NOT NULL DEFAULT '[]',
|
|
"projectId" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Note_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Notification" (
|
|
"id" TEXT NOT NULL,
|
|
"workspaceId" TEXT NOT NULL,
|
|
"resourceId" TEXT,
|
|
"title" TEXT NOT NULL,
|
|
"body" TEXT NOT NULL DEFAULT '',
|
|
"level" TEXT NOT NULL DEFAULT 'INFO',
|
|
"readAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Notification_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Certificate" (
|
|
"id" TEXT NOT NULL,
|
|
"workspaceId" TEXT NOT NULL,
|
|
"resourceId" TEXT NOT NULL,
|
|
"domain" TEXT NOT NULL,
|
|
"domainId" TEXT,
|
|
"status" TEXT NOT NULL DEFAULT 'PENDING',
|
|
"issuer" TEXT,
|
|
"notAfter" TIMESTAMP(3),
|
|
"lastCheckedAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Certificate_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "GitConnection_resourceId_key" ON "GitConnection"("resourceId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "GitConnection_workspaceId_idx" ON "GitConnection"("workspaceId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Repository_resourceId_key" ON "Repository"("resourceId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Repository_workspaceId_idx" ON "Repository"("workspaceId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Repository_workspaceId_cloneUrl_key" ON "Repository"("workspaceId", "cloneUrl");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Task_resourceId_key" ON "Task"("resourceId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Task_workspaceId_status_idx" ON "Task"("workspaceId", "status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Task_projectId_idx" ON "Task"("projectId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Note_resourceId_key" ON "Note"("resourceId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Note_workspaceId_idx" ON "Note"("workspaceId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Notification_workspaceId_createdAt_idx" ON "Notification"("workspaceId", "createdAt");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Certificate_resourceId_key" ON "Certificate"("resourceId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Certificate_workspaceId_idx" ON "Certificate"("workspaceId");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "websites" ADD CONSTRAINT "websites_repositoryId_fkey" FOREIGN KEY ("repositoryId") REFERENCES "Repository"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "GitConnection" ADD CONSTRAINT "GitConnection_resourceId_fkey" FOREIGN KEY ("resourceId") REFERENCES "resources"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Repository" ADD CONSTRAINT "Repository_resourceId_fkey" FOREIGN KEY ("resourceId") REFERENCES "resources"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Repository" ADD CONSTRAINT "Repository_connectionId_fkey" FOREIGN KEY ("connectionId") REFERENCES "GitConnection"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Task" ADD CONSTRAINT "Task_resourceId_fkey" FOREIGN KEY ("resourceId") REFERENCES "resources"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Note" ADD CONSTRAINT "Note_resourceId_fkey" FOREIGN KEY ("resourceId") REFERENCES "resources"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Certificate" ADD CONSTRAINT "Certificate_resourceId_fkey" FOREIGN KEY ("resourceId") REFERENCES "resources"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
|
|
-- Phase B: 关系类型标准化（旧 v1 小写名 → Phase2 大写规范名）
|
|
UPDATE "resource_relations" SET "relationType" = 'WEBSITE_DEPLOYED_TO_SERVER' WHERE "relationType" = 'deployed_on';
|
|
UPDATE "resource_relations" SET "relationType" = 'SERVER_HAS_CONTAINER' WHERE "relationType" = 'runs_container';
|
|
UPDATE "resource_relations" SET "relationType" = 'WEBSITE_USES_DOMAIN' WHERE "relationType" = 'resolves_to';
|
|
UPDATE "resource_relations" SET "relationType" = 'WEBSITE_HAS_DEPLOYMENT' WHERE "relationType" = 'has_deployment';
|
|
UPDATE "resource_relations" SET "relationType" = 'WEBSITE_HAS_MONITOR' WHERE "relationType" = 'monitors';
|