321 lines
10 KiB
SQL
321 lines
10 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "AdminRole" AS ENUM ('OWNER', 'ADMIN', 'OPERATOR', 'VIEWER');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "NoticeStatus" AS ENUM ('DRAFT', 'SCHEDULED', 'PUBLISHED', 'ARCHIVED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "LicenseCode" AS ENUM ('FREE', 'EDUCATION', 'PROFESSIONAL');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "RuntimeAction" AS ENUM ('RESTART', 'SCALE', 'LOGS', 'EVENTS', 'ROLLBACK', 'STOP');
|
|
|
|
-- DropIndex
|
|
DROP INDEX "notices_scope_is_active_idx";
|
|
|
|
-- AlterTable
|
|
ALTER TABLE "notices" ADD COLUMN "priority" INTEGER NOT NULL DEFAULT 0,
|
|
ADD COLUMN "status" "NoticeStatus" NOT NULL DEFAULT 'DRAFT';
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "admins" (
|
|
"id" UUID NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"password_hash" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"role" "AdminRole" NOT NULL DEFAULT 'ADMIN',
|
|
"is_active" BOOLEAN NOT NULL DEFAULT true,
|
|
"last_login_at" TIMESTAMPTZ(3),
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "admins_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "admin_sessions" (
|
|
"id" UUID NOT NULL,
|
|
"admin_id" UUID NOT NULL,
|
|
"token_hash" TEXT NOT NULL,
|
|
"ip_address" TEXT,
|
|
"user_agent" TEXT,
|
|
"expires_at" TIMESTAMPTZ(3) NOT NULL,
|
|
"revoked_at" TIMESTAMPTZ(3),
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "admin_sessions_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "admin_audit_logs" (
|
|
"id" UUID NOT NULL,
|
|
"admin_id" UUID,
|
|
"action" TEXT NOT NULL,
|
|
"resource" TEXT NOT NULL,
|
|
"resource_id" TEXT,
|
|
"metadata" JSONB,
|
|
"ip_address" TEXT,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "admin_audit_logs_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "launcher_versions" (
|
|
"id" UUID NOT NULL,
|
|
"version" TEXT NOT NULL,
|
|
"minimum_version" TEXT,
|
|
"artifact" TEXT NOT NULL,
|
|
"download_url" TEXT NOT NULL,
|
|
"rollout_percent" INTEGER NOT NULL DEFAULT 0,
|
|
"release_notes" TEXT,
|
|
"is_active" BOOLEAN NOT NULL DEFAULT false,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "launcher_versions_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "launcher_common_settings" (
|
|
"id" UUID NOT NULL,
|
|
"key" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"type" TEXT NOT NULL,
|
|
"value" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"is_active" BOOLEAN NOT NULL DEFAULT true,
|
|
"is_secret" BOOLEAN NOT NULL DEFAULT false,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "launcher_common_settings_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "launcher_app_links" (
|
|
"id" UUID NOT NULL,
|
|
"app_id" UUID NOT NULL,
|
|
"release_id" UUID,
|
|
"display_order" INTEGER NOT NULL DEFAULT 0,
|
|
"is_enabled" BOOLEAN NOT NULL DEFAULT true,
|
|
"launch_url" TEXT,
|
|
"artifact" TEXT,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "launcher_app_links_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "license_plan_snapshots" (
|
|
"id" UUID NOT NULL,
|
|
"code" "LicenseCode" NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"default_duration_days" INTEGER,
|
|
"is_active" BOOLEAN NOT NULL DEFAULT true,
|
|
"assigned_count" INTEGER NOT NULL DEFAULT 0,
|
|
"active_user_count" INTEGER NOT NULL DEFAULT 0,
|
|
"expiring_soon_count" INTEGER NOT NULL DEFAULT 0,
|
|
"synced_at" TIMESTAMPTZ(3),
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "license_plan_snapshots_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "user_license_snapshots" (
|
|
"id" UUID NOT NULL,
|
|
"license_id" UUID NOT NULL,
|
|
"auth_user_id" TEXT,
|
|
"email" TEXT NOT NULL,
|
|
"name" TEXT,
|
|
"starts_at" TIMESTAMPTZ(3),
|
|
"expires_at" TIMESTAMPTZ(3),
|
|
"assigned_by" TEXT,
|
|
"is_active" BOOLEAN NOT NULL DEFAULT true,
|
|
"synced_at" TIMESTAMPTZ(3),
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "user_license_snapshots_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "runtime_control_jobs" (
|
|
"id" UUID NOT NULL,
|
|
"namespace" TEXT NOT NULL,
|
|
"kind" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"action" "RuntimeAction" NOT NULL,
|
|
"status" "JobStatus" NOT NULL DEFAULT 'PENDING',
|
|
"requested_by" TEXT,
|
|
"request" JSONB,
|
|
"result" JSONB,
|
|
"logs" TEXT,
|
|
"started_at" TIMESTAMPTZ(3),
|
|
"finished_at" TIMESTAMPTZ(3),
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "runtime_control_jobs_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "platform_connections" (
|
|
"id" UUID NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"endpoint" TEXT NOT NULL,
|
|
"role" TEXT NOT NULL,
|
|
"status" TEXT NOT NULL,
|
|
"last_sync" TIMESTAMPTZ(3),
|
|
"metadata" JSONB,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "platform_connections_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "agent_devices" (
|
|
"id" UUID NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"account" TEXT,
|
|
"status" TEXT NOT NULL,
|
|
"workspace" TEXT,
|
|
"ssh_key" TEXT,
|
|
"last_seen" TIMESTAMPTZ(3),
|
|
"metadata" JSONB,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "agent_devices_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "tailscale_nodes" (
|
|
"id" UUID NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"ip" TEXT NOT NULL,
|
|
"role" TEXT,
|
|
"user" TEXT,
|
|
"routes" TEXT,
|
|
"status" TEXT NOT NULL,
|
|
"last_seen" TIMESTAMPTZ(3),
|
|
"metadata" JSONB,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "tailscale_nodes_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "tailscale_settings" (
|
|
"id" UUID NOT NULL,
|
|
"key" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"value" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"status" TEXT NOT NULL,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "tailscale_settings_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "admins_email_key" ON "admins"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "admins_is_active_role_idx" ON "admins"("is_active", "role");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "admin_sessions_token_hash_key" ON "admin_sessions"("token_hash");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "admin_sessions_admin_id_expires_at_idx" ON "admin_sessions"("admin_id", "expires_at");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "admin_audit_logs_resource_created_at_idx" ON "admin_audit_logs"("resource", "created_at");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "admin_audit_logs_admin_id_created_at_idx" ON "admin_audit_logs"("admin_id", "created_at");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "launcher_versions_version_key" ON "launcher_versions"("version");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "launcher_versions_is_active_created_at_idx" ON "launcher_versions"("is_active", "created_at");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "launcher_common_settings_key_key" ON "launcher_common_settings"("key");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "launcher_common_settings_type_is_active_idx" ON "launcher_common_settings"("type", "is_active");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "launcher_app_links_app_id_key" ON "launcher_app_links"("app_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "launcher_app_links_display_order_is_enabled_idx" ON "launcher_app_links"("display_order", "is_enabled");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "license_plan_snapshots_code_key" ON "license_plan_snapshots"("code");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "user_license_snapshots_email_idx" ON "user_license_snapshots"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "user_license_snapshots_license_id_is_active_idx" ON "user_license_snapshots"("license_id", "is_active");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "runtime_control_jobs_namespace_kind_name_idx" ON "runtime_control_jobs"("namespace", "kind", "name");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "runtime_control_jobs_created_at_idx" ON "runtime_control_jobs"("created_at");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "platform_connections_name_key" ON "platform_connections"("name");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "platform_connections_status_idx" ON "platform_connections"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "agent_devices_name_key" ON "agent_devices"("name");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "agent_devices_status_last_seen_idx" ON "agent_devices"("status", "last_seen");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "tailscale_nodes_name_key" ON "tailscale_nodes"("name");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "tailscale_nodes_status_last_seen_idx" ON "tailscale_nodes"("status", "last_seen");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "tailscale_settings_key_key" ON "tailscale_settings"("key");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "tailscale_settings_status_idx" ON "tailscale_settings"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "notices_scope_status_idx" ON "notices"("scope", "status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "notices_is_active_priority_idx" ON "notices"("is_active", "priority");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "admin_sessions" ADD CONSTRAINT "admin_sessions_admin_id_fkey" FOREIGN KEY ("admin_id") REFERENCES "admins"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "admin_audit_logs" ADD CONSTRAINT "admin_audit_logs_admin_id_fkey" FOREIGN KEY ("admin_id") REFERENCES "admins"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "launcher_app_links" ADD CONSTRAINT "launcher_app_links_app_id_fkey" FOREIGN KEY ("app_id") REFERENCES "apps"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "launcher_app_links" ADD CONSTRAINT "launcher_app_links_release_id_fkey" FOREIGN KEY ("release_id") REFERENCES "app_releases"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "user_license_snapshots" ADD CONSTRAINT "user_license_snapshots_license_id_fkey" FOREIGN KEY ("license_id") REFERENCES "license_plan_snapshots"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|