arsam
b3cc7a9380
Import the existing local project snapshot. This commit organizes existing files; it does not represent newly developed functionality.
386 lines
13 KiB
SQL
386 lines
13 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "Status" AS ENUM ('healthy', 'warning', 'critical', 'maintenance', 'disabled');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "Runtime" AS ENUM ('Static', 'PHP', 'Node.js', 'Python', 'Ruby', 'Docker', 'Reverse proxy', 'Custom service');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "Environment" AS ENUM ('production', 'staging');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "DomainType" AS ENUM ('primary', 'addon', 'alias', 'subdomain', 'parked');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "DnsRecordType" AS ENUM ('A', 'AAAA', 'CNAME', 'MX', 'TXT', 'SRV', 'CAA', 'NS', 'PTR');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "DatabaseEngine" AS ENUM ('MySQL', 'MariaDB', 'PostgreSQL');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "BackupScope" AS ENUM ('full', 'website', 'database', 'email', 'configuration', 'snapshot');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "SecuritySeverity" AS ENUM ('critical', 'high', 'medium', 'low');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "SecurityStatus" AS ENUM ('open', 'acknowledged', 'resolved');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "NotificationCategory" AS ENUM ('security', 'backup', 'resource', 'ssl', 'deployment', 'system');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "Permission" AS ENUM ('dashboard:read', 'tools:read', 'websites:manage', 'domains:manage', 'files:manage', 'databases:manage', 'email:manage', 'metrics:read', 'security:manage', 'software:manage', 'advanced:manage', 'servers:manage', 'users:manage', 'resellers:manage', 'backups:manage', 'automation:manage', 'notifications:read', 'audit:read', 'support:manage', 'settings:manage');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"role" TEXT NOT NULL,
|
|
"avatarInitials" TEXT NOT NULL,
|
|
"twoFactorEnabled" BOOLEAN NOT NULL DEFAULT false,
|
|
"lastLogin" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "User_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Session" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"expiresAt" TIMESTAMP(3) NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"userAgent" TEXT,
|
|
"ipAddress" TEXT,
|
|
"challenge" TEXT,
|
|
|
|
CONSTRAINT "Session_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "UserPermission" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"permission" "Permission" NOT NULL,
|
|
|
|
CONSTRAINT "UserPermission_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "HostingAccount" (
|
|
"id" TEXT NOT NULL,
|
|
"username" TEXT NOT NULL,
|
|
"primaryDomain" TEXT NOT NULL,
|
|
"homeDirectory" TEXT NOT NULL,
|
|
"sharedIp" TEXT NOT NULL,
|
|
"dedicatedIp" TEXT,
|
|
"theme" TEXT NOT NULL DEFAULT 'system',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "HostingAccount_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "AccountUser" (
|
|
"userId" TEXT NOT NULL,
|
|
"accountId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "AccountUser_pkey" PRIMARY KEY ("userId","accountId")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Website" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"domain" TEXT NOT NULL,
|
|
"runtime" "Runtime" NOT NULL,
|
|
"status" "Status" NOT NULL DEFAULT 'healthy',
|
|
"ssl" "Status" NOT NULL DEFAULT 'healthy',
|
|
"repository" TEXT,
|
|
"environment" "Environment" NOT NULL DEFAULT 'production',
|
|
"cpuLimit" TEXT NOT NULL,
|
|
"memoryLimit" TEXT NOT NULL,
|
|
"lastDeployment" TIMESTAMP(3),
|
|
"accountId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "Website_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Domain" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"type" "DomainType" NOT NULL,
|
|
"documentRoot" TEXT NOT NULL,
|
|
"sslStatus" "Status" NOT NULL DEFAULT 'healthy',
|
|
"dnssec" BOOLEAN NOT NULL DEFAULT false,
|
|
"propagation" INTEGER NOT NULL DEFAULT 100,
|
|
"accountId" TEXT NOT NULL,
|
|
"websiteId" TEXT,
|
|
|
|
CONSTRAINT "Domain_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "DnsRecord" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"ttl" INTEGER NOT NULL DEFAULT 14400,
|
|
"class" TEXT NOT NULL DEFAULT 'IN',
|
|
"type" "DnsRecordType" NOT NULL,
|
|
"record" TEXT NOT NULL,
|
|
"priority" INTEGER,
|
|
"warning" TEXT,
|
|
"domainId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "DnsRecord_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Server" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"hostname" TEXT NOT NULL,
|
|
"os" TEXT NOT NULL,
|
|
"panelVersion" TEXT NOT NULL,
|
|
"load1" DOUBLE PRECISION NOT NULL,
|
|
"load5" DOUBLE PRECISION NOT NULL,
|
|
"load15" DOUBLE PRECISION NOT NULL,
|
|
"ipAddress" TEXT NOT NULL,
|
|
"status" "Status" NOT NULL DEFAULT 'healthy',
|
|
"location" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Server_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Database" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"engine" "DatabaseEngine" NOT NULL,
|
|
"size" BIGINT NOT NULL,
|
|
"tables" INTEGER NOT NULL DEFAULT 0,
|
|
"connections" INTEGER NOT NULL DEFAULT 0,
|
|
"lastBackup" TIMESTAMP(3),
|
|
"accountId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "Database_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "DatabaseUser" (
|
|
"id" TEXT NOT NULL,
|
|
"username" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "DatabaseUser_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "DatabaseUserAccess" (
|
|
"databaseId" TEXT NOT NULL,
|
|
"username" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "DatabaseUserAccess_pkey" PRIMARY KEY ("databaseId","username")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Mailbox" (
|
|
"id" TEXT NOT NULL,
|
|
"address" TEXT NOT NULL,
|
|
"used" BIGINT NOT NULL DEFAULT 0,
|
|
"quota" BIGINT NOT NULL,
|
|
"lastLogin" TIMESTAMP(3),
|
|
"forwarding" BOOLEAN NOT NULL DEFAULT false,
|
|
"spamScore" DOUBLE PRECISION NOT NULL DEFAULT 0.0,
|
|
"authStatus" "Status" NOT NULL DEFAULT 'healthy',
|
|
"domainId" TEXT NOT NULL,
|
|
"accountId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "Mailbox_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ResourceUsage" (
|
|
"id" TEXT NOT NULL,
|
|
"key" TEXT NOT NULL,
|
|
"label" TEXT NOT NULL,
|
|
"used" DOUBLE PRECISION NOT NULL,
|
|
"limit" DOUBLE PRECISION NOT NULL,
|
|
"unit" TEXT NOT NULL,
|
|
"status" "Status" NOT NULL,
|
|
"accountId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "ResourceUsage_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "MetricPoint" (
|
|
"id" TEXT NOT NULL,
|
|
"timestamp" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"cpu" DOUBLE PRECISION NOT NULL,
|
|
"memory" DOUBLE PRECISION NOT NULL,
|
|
"disk" DOUBLE PRECISION NOT NULL,
|
|
"bandwidth" DOUBLE PRECISION NOT NULL,
|
|
"requests" INTEGER NOT NULL,
|
|
"accountId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "MetricPoint_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "SecurityFinding" (
|
|
"id" TEXT NOT NULL,
|
|
"severity" "SecuritySeverity" NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"target" TEXT NOT NULL,
|
|
"status" "SecurityStatus" NOT NULL DEFAULT 'open',
|
|
"recommendation" TEXT NOT NULL,
|
|
"accountId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "SecurityFinding_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Backup" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"scope" "BackupScope" NOT NULL,
|
|
"destination" TEXT NOT NULL,
|
|
"size" BIGINT NOT NULL,
|
|
"status" "Status" NOT NULL,
|
|
"encrypted" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"accountId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "Backup_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Automation" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"trigger" TEXT NOT NULL,
|
|
"status" "Status" NOT NULL,
|
|
"lastRun" TIMESTAMP(3),
|
|
"successRate" INTEGER NOT NULL DEFAULT 100,
|
|
"accountId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "Automation_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Notification" (
|
|
"id" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"category" "NotificationCategory" NOT NULL,
|
|
"read" BOOLEAN NOT NULL DEFAULT false,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"body" TEXT NOT NULL,
|
|
"accountId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "Notification_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "AuditEntry" (
|
|
"id" TEXT NOT NULL,
|
|
"actor" TEXT NOT NULL,
|
|
"action" TEXT NOT NULL,
|
|
"target" TEXT NOT NULL,
|
|
"ipAddress" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"requestId" TEXT NOT NULL,
|
|
"accountId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "AuditEntry_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "UserPermission_userId_permission_key" ON "UserPermission"("userId", "permission");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "HostingAccount_username_key" ON "HostingAccount"("username");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Domain_name_key" ON "Domain"("name");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Database_name_key" ON "Database"("name");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "DatabaseUser_username_key" ON "DatabaseUser"("username");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Mailbox_address_key" ON "Mailbox"("address");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "ResourceUsage_accountId_key_key" ON "ResourceUsage"("accountId", "key");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Session" ADD CONSTRAINT "Session_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "UserPermission" ADD CONSTRAINT "UserPermission_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "AccountUser" ADD CONSTRAINT "AccountUser_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "AccountUser" ADD CONSTRAINT "AccountUser_accountId_fkey" FOREIGN KEY ("accountId") REFERENCES "HostingAccount"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Website" ADD CONSTRAINT "Website_accountId_fkey" FOREIGN KEY ("accountId") REFERENCES "HostingAccount"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Domain" ADD CONSTRAINT "Domain_accountId_fkey" FOREIGN KEY ("accountId") REFERENCES "HostingAccount"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Domain" ADD CONSTRAINT "Domain_websiteId_fkey" FOREIGN KEY ("websiteId") REFERENCES "Website"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "DnsRecord" ADD CONSTRAINT "DnsRecord_domainId_fkey" FOREIGN KEY ("domainId") REFERENCES "Domain"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Database" ADD CONSTRAINT "Database_accountId_fkey" FOREIGN KEY ("accountId") REFERENCES "HostingAccount"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "DatabaseUserAccess" ADD CONSTRAINT "DatabaseUserAccess_databaseId_fkey" FOREIGN KEY ("databaseId") REFERENCES "Database"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "DatabaseUserAccess" ADD CONSTRAINT "DatabaseUserAccess_username_fkey" FOREIGN KEY ("username") REFERENCES "DatabaseUser"("username") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Mailbox" ADD CONSTRAINT "Mailbox_domainId_fkey" FOREIGN KEY ("domainId") REFERENCES "Domain"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Mailbox" ADD CONSTRAINT "Mailbox_accountId_fkey" FOREIGN KEY ("accountId") REFERENCES "HostingAccount"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ResourceUsage" ADD CONSTRAINT "ResourceUsage_accountId_fkey" FOREIGN KEY ("accountId") REFERENCES "HostingAccount"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "MetricPoint" ADD CONSTRAINT "MetricPoint_accountId_fkey" FOREIGN KEY ("accountId") REFERENCES "HostingAccount"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "SecurityFinding" ADD CONSTRAINT "SecurityFinding_accountId_fkey" FOREIGN KEY ("accountId") REFERENCES "HostingAccount"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Backup" ADD CONSTRAINT "Backup_accountId_fkey" FOREIGN KEY ("accountId") REFERENCES "HostingAccount"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Automation" ADD CONSTRAINT "Automation_accountId_fkey" FOREIGN KEY ("accountId") REFERENCES "HostingAccount"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Notification" ADD CONSTRAINT "Notification_accountId_fkey" FOREIGN KEY ("accountId") REFERENCES "HostingAccount"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "AuditEntry" ADD CONSTRAINT "AuditEntry_accountId_fkey" FOREIGN KEY ("accountId") REFERENCES "HostingAccount"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|