769 lines
29 KiB
SQL
769 lines
29 KiB
SQL
-- Gap closure tables: organizations, catalog, schedules, notifications, compliance, billing, admin
|
|
|
|
-- CreateEnum
|
|
DO $$ BEGIN
|
|
CREATE TYPE "JobStatus" AS ENUM ('PENDING', 'RUNNING', 'COMPLETED', 'FAILED', 'CANCELLED');
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE IF NOT EXISTS "organizations" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"slug" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "organizations_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "organization_members" (
|
|
"id" TEXT NOT NULL,
|
|
"organizationId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"role" "OrganizationMemberRole" NOT NULL DEFAULT 'MEMBER',
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "organization_members_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "organization_invites" (
|
|
"id" TEXT NOT NULL,
|
|
"organizationId" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"role" "OrganizationMemberRole" NOT NULL DEFAULT 'MEMBER',
|
|
"tokenHash" TEXT NOT NULL,
|
|
"invitedById" TEXT NOT NULL,
|
|
"expiresAt" TIMESTAMPTZ(3) NOT NULL,
|
|
"acceptedAt" TIMESTAMPTZ(3),
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "organization_invites_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "game_server_members" (
|
|
"id" TEXT NOT NULL,
|
|
"serverId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"role" "GameServerMemberRole" NOT NULL DEFAULT 'VIEWER',
|
|
"permissions" JSONB,
|
|
"invitedAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"acceptedAt" TIMESTAMPTZ(3),
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "game_server_members_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "game_server_properties" (
|
|
"id" TEXT NOT NULL,
|
|
"serverId" TEXT NOT NULL,
|
|
"key" TEXT NOT NULL,
|
|
"value" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "game_server_properties_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "game_server_environment_variables" (
|
|
"id" TEXT NOT NULL,
|
|
"serverId" TEXT NOT NULL,
|
|
"key" TEXT NOT NULL,
|
|
"value" TEXT NOT NULL,
|
|
"isSecret" BOOLEAN NOT NULL DEFAULT false,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "game_server_environment_variables_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "game_server_secrets" (
|
|
"id" TEXT NOT NULL,
|
|
"serverId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"valueCiphertext" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "game_server_secrets_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "game_node_capabilities" (
|
|
"id" TEXT NOT NULL,
|
|
"nodeId" TEXT NOT NULL,
|
|
"key" TEXT NOT NULL,
|
|
"value" TEXT,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "game_node_capabilities_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "game_node_maintenance_windows" (
|
|
"id" TEXT NOT NULL,
|
|
"nodeId" TEXT NOT NULL,
|
|
"startsAt" TIMESTAMPTZ(3) NOT NULL,
|
|
"endsAt" TIMESTAMPTZ(3) NOT NULL,
|
|
"drainFirst" BOOLEAN NOT NULL DEFAULT true,
|
|
"message" TEXT,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "game_node_maintenance_windows_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "minecraft_versions" (
|
|
"id" TEXT NOT NULL,
|
|
"version" TEXT NOT NULL,
|
|
"edition" "ServerEdition" NOT NULL DEFAULT 'JAVA',
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "minecraft_versions_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "software_builds" (
|
|
"id" TEXT NOT NULL,
|
|
"family" "SoftwareFamily" NOT NULL,
|
|
"minecraftVersionId" TEXT NOT NULL,
|
|
"downloadUrl" TEXT,
|
|
"sha256" TEXT,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "software_builds_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "runtime_images" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"imageRef" TEXT NOT NULL,
|
|
"digest" TEXT,
|
|
"javaVersion" TEXT,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "runtime_images_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "catalog_providers" (
|
|
"id" TEXT NOT NULL,
|
|
"kind" "CatalogProviderKind" NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"isEnabled" BOOLEAN NOT NULL DEFAULT false,
|
|
"config" JSONB,
|
|
"lastSyncAt" TIMESTAMPTZ(3),
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "catalog_providers_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "catalog_projects" (
|
|
"id" TEXT NOT NULL,
|
|
"providerId" TEXT NOT NULL,
|
|
"externalId" TEXT NOT NULL,
|
|
"slug" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"projectType" TEXT NOT NULL,
|
|
"metadata" JSONB,
|
|
"syncedAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "catalog_projects_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "catalog_versions" (
|
|
"id" TEXT NOT NULL,
|
|
"projectId" TEXT NOT NULL,
|
|
"externalId" TEXT NOT NULL,
|
|
"versionNumber" TEXT NOT NULL,
|
|
"gameVersions" JSONB,
|
|
"loaders" JSONB,
|
|
"sha512" TEXT,
|
|
"downloadUrl" TEXT,
|
|
"syncedAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "catalog_versions_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "catalog_dependencies" (
|
|
"id" TEXT NOT NULL,
|
|
"versionId" TEXT NOT NULL,
|
|
"dependencyId" TEXT NOT NULL,
|
|
"dependencyType" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "catalog_dependencies_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "worlds" (
|
|
"id" TEXT NOT NULL,
|
|
"serverId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT false,
|
|
"sizeBytes" BIGINT,
|
|
"hasLevelDat" BOOLEAN NOT NULL DEFAULT false,
|
|
"seed" TEXT,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "worlds_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "world_archives" (
|
|
"id" TEXT NOT NULL,
|
|
"worldId" TEXT NOT NULL,
|
|
"s3Key" TEXT NOT NULL,
|
|
"sha256" TEXT,
|
|
"sizeBytes" BIGINT,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "world_archives_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "schedules" (
|
|
"id" TEXT NOT NULL,
|
|
"serverId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"timezone" TEXT NOT NULL DEFAULT 'UTC',
|
|
"cronExpr" TEXT NOT NULL,
|
|
"enabled" BOOLEAN NOT NULL DEFAULT true,
|
|
"nextRunAt" TIMESTAMPTZ(3),
|
|
"lastRunAt" TIMESTAMPTZ(3),
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "schedules_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "scheduled_tasks" (
|
|
"id" TEXT NOT NULL,
|
|
"scheduleId" TEXT NOT NULL,
|
|
"taskType" "ScheduleTaskType" NOT NULL,
|
|
"payload" JSONB,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "scheduled_tasks_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "notifications" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"type" "NotificationType" NOT NULL,
|
|
"channel" "NotificationChannel" NOT NULL DEFAULT 'IN_APP',
|
|
"title" TEXT NOT NULL,
|
|
"body" TEXT NOT NULL,
|
|
"metadata" JSONB,
|
|
"readAt" TIMESTAMPTZ(3),
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "notifications_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "notification_preferences" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"type" "NotificationType" NOT NULL,
|
|
"channel" "NotificationChannel" NOT NULL,
|
|
"enabled" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "notification_preferences_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "abuse_reports" (
|
|
"id" TEXT NOT NULL,
|
|
"reporterId" TEXT,
|
|
"targetUserId" TEXT,
|
|
"serverId" TEXT,
|
|
"status" "AbuseReportStatus" NOT NULL DEFAULT 'OPEN',
|
|
"reason" TEXT NOT NULL,
|
|
"details" TEXT,
|
|
"resolution" TEXT,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "abuse_reports_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "support_tickets" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"serverId" TEXT,
|
|
"subject" TEXT NOT NULL,
|
|
"status" "SupportTicketStatus" NOT NULL DEFAULT 'OPEN',
|
|
"priority" "SupportTicketPriority" NOT NULL DEFAULT 'NORMAL',
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "support_tickets_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "support_ticket_messages" (
|
|
"id" TEXT NOT NULL,
|
|
"ticketId" TEXT NOT NULL,
|
|
"authorId" TEXT,
|
|
"isInternal" BOOLEAN NOT NULL DEFAULT false,
|
|
"body" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "support_ticket_messages_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "subscriptions" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"planId" TEXT NOT NULL,
|
|
"status" TEXT NOT NULL,
|
|
"startsAt" TIMESTAMPTZ(3) NOT NULL,
|
|
"endsAt" TIMESTAMPTZ(3),
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "subscriptions_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "invoices" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"subscriptionId" TEXT,
|
|
"amountCents" INTEGER NOT NULL,
|
|
"currency" TEXT NOT NULL DEFAULT 'EUR',
|
|
"status" TEXT NOT NULL,
|
|
"externalRef" TEXT,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "invoices_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "payments" (
|
|
"id" TEXT NOT NULL,
|
|
"invoiceId" TEXT NOT NULL,
|
|
"amountCents" INTEGER NOT NULL,
|
|
"provider" TEXT NOT NULL,
|
|
"externalRef" TEXT,
|
|
"status" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "payments_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "resource_quotas" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT,
|
|
"planId" TEXT,
|
|
"key" TEXT NOT NULL,
|
|
"limit" INTEGER NOT NULL,
|
|
"used" INTEGER NOT NULL DEFAULT 0,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "resource_quotas_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "security_change_logs" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"action" TEXT NOT NULL,
|
|
"metadata" JSONB,
|
|
"ipAddress" TEXT,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "security_change_logs_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "data_export_requests" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"status" TEXT NOT NULL DEFAULT 'PENDING',
|
|
"downloadUrl" TEXT,
|
|
"expiresAt" TIMESTAMPTZ(3),
|
|
"completedAt" TIMESTAMPTZ(3),
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "data_export_requests_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "account_deletion_requests" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"status" TEXT NOT NULL DEFAULT 'PENDING',
|
|
"scheduledFor" TIMESTAMPTZ(3) NOT NULL,
|
|
"completedAt" TIMESTAMPTZ(3),
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "account_deletion_requests_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "whmcs_user_links" (
|
|
"id" TEXT NOT NULL,
|
|
"installationId" TEXT NOT NULL,
|
|
"externalUserId" TEXT NOT NULL,
|
|
"externalClientId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "whmcs_user_links_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "whmcs_product_mappings" (
|
|
"id" TEXT NOT NULL,
|
|
"installationId" TEXT NOT NULL,
|
|
"externalProductId" TEXT NOT NULL,
|
|
"planSlug" TEXT NOT NULL,
|
|
"defaultEdition" "ServerEdition" NOT NULL DEFAULT 'JAVA',
|
|
"defaultSoftware" "SoftwareFamily" NOT NULL DEFAULT 'VANILLA',
|
|
"defaultVersion" TEXT NOT NULL DEFAULT '1.21.1',
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "whmcs_product_mappings_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "whmcs_option_mappings" (
|
|
"id" TEXT NOT NULL,
|
|
"installationId" TEXT NOT NULL,
|
|
"externalOptionId" TEXT NOT NULL,
|
|
"externalValueId" TEXT NOT NULL,
|
|
"resourceKey" TEXT NOT NULL,
|
|
"normalizedValue" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "whmcs_option_mappings_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "whmcs_addon_mappings" (
|
|
"id" TEXT NOT NULL,
|
|
"installationId" TEXT NOT NULL,
|
|
"externalAddonId" TEXT NOT NULL,
|
|
"featureKey" TEXT NOT NULL,
|
|
"deltaValue" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "whmcs_addon_mappings_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "feature_flags" (
|
|
"id" TEXT NOT NULL,
|
|
"key" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"enabled" BOOLEAN NOT NULL DEFAULT false,
|
|
"metadata" JSONB,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "feature_flags_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "system_settings" (
|
|
"id" TEXT NOT NULL,
|
|
"key" TEXT NOT NULL,
|
|
"value" JSONB NOT NULL,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "system_settings_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "job_records" (
|
|
"id" TEXT NOT NULL,
|
|
"queue" TEXT NOT NULL,
|
|
"jobName" TEXT NOT NULL,
|
|
"status" "JobStatus" NOT NULL DEFAULT 'PENDING',
|
|
"payload" JSONB,
|
|
"result" JSONB,
|
|
"error" TEXT,
|
|
"attempts" INTEGER NOT NULL DEFAULT 0,
|
|
"startedAt" TIMESTAMPTZ(3),
|
|
"completedAt" TIMESTAMPTZ(3),
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "job_records_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "audit_events" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT,
|
|
"action" TEXT NOT NULL,
|
|
"entityType" TEXT,
|
|
"entityId" TEXT,
|
|
"metadata" JSONB,
|
|
"ipAddress" TEXT,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "audit_events_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "organizations_slug_key" ON "organizations"("slug");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "organization_members_organizationId_userId_key" ON "organization_members"("organizationId", "userId");
|
|
CREATE INDEX IF NOT EXISTS "organization_members_userId_idx" ON "organization_members"("userId");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "organization_invites_tokenHash_key" ON "organization_invites"("tokenHash");
|
|
CREATE INDEX IF NOT EXISTS "organization_invites_organizationId_idx" ON "organization_invites"("organizationId");
|
|
CREATE INDEX IF NOT EXISTS "organization_invites_email_idx" ON "organization_invites"("email");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "game_server_members_serverId_userId_key" ON "game_server_members"("serverId", "userId");
|
|
CREATE INDEX IF NOT EXISTS "game_server_members_userId_idx" ON "game_server_members"("userId");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "game_server_properties_serverId_key_key" ON "game_server_properties"("serverId", "key");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "game_server_environment_variables_serverId_key_key" ON "game_server_environment_variables"("serverId", "key");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "game_server_secrets_serverId_name_key" ON "game_server_secrets"("serverId", "name");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "game_node_capabilities_nodeId_key_key" ON "game_node_capabilities"("nodeId", "key");
|
|
|
|
CREATE INDEX IF NOT EXISTS "game_node_maintenance_windows_nodeId_startsAt_idx" ON "game_node_maintenance_windows"("nodeId", "startsAt");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "minecraft_versions_version_key" ON "minecraft_versions"("version");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "software_builds_family_minecraftVersionId_key" ON "software_builds"("family", "minecraftVersionId");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "runtime_images_name_key" ON "runtime_images"("name");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "catalog_providers_kind_key" ON "catalog_providers"("kind");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "catalog_projects_providerId_externalId_key" ON "catalog_projects"("providerId", "externalId");
|
|
CREATE INDEX IF NOT EXISTS "catalog_projects_slug_idx" ON "catalog_projects"("slug");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "catalog_versions_projectId_externalId_key" ON "catalog_versions"("projectId", "externalId");
|
|
|
|
CREATE INDEX IF NOT EXISTS "catalog_dependencies_versionId_idx" ON "catalog_dependencies"("versionId");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "worlds_serverId_name_key" ON "worlds"("serverId", "name");
|
|
|
|
CREATE INDEX IF NOT EXISTS "world_archives_worldId_idx" ON "world_archives"("worldId");
|
|
|
|
CREATE INDEX IF NOT EXISTS "schedules_serverId_idx" ON "schedules"("serverId");
|
|
|
|
CREATE INDEX IF NOT EXISTS "scheduled_tasks_scheduleId_idx" ON "scheduled_tasks"("scheduleId");
|
|
|
|
CREATE INDEX IF NOT EXISTS "notifications_userId_readAt_idx" ON "notifications"("userId", "readAt");
|
|
CREATE INDEX IF NOT EXISTS "notifications_createdAt_idx" ON "notifications"("createdAt");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "notification_preferences_userId_type_channel_key" ON "notification_preferences"("userId", "type", "channel");
|
|
|
|
CREATE INDEX IF NOT EXISTS "abuse_reports_status_idx" ON "abuse_reports"("status");
|
|
CREATE INDEX IF NOT EXISTS "abuse_reports_serverId_idx" ON "abuse_reports"("serverId");
|
|
|
|
CREATE INDEX IF NOT EXISTS "support_tickets_userId_idx" ON "support_tickets"("userId");
|
|
CREATE INDEX IF NOT EXISTS "support_tickets_status_idx" ON "support_tickets"("status");
|
|
|
|
CREATE INDEX IF NOT EXISTS "support_ticket_messages_ticketId_idx" ON "support_ticket_messages"("ticketId");
|
|
|
|
CREATE INDEX IF NOT EXISTS "subscriptions_userId_idx" ON "subscriptions"("userId");
|
|
|
|
CREATE INDEX IF NOT EXISTS "invoices_userId_idx" ON "invoices"("userId");
|
|
|
|
CREATE INDEX IF NOT EXISTS "payments_invoiceId_idx" ON "payments"("invoiceId");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "resource_quotas_userId_planId_key_key" ON "resource_quotas"("userId", "planId", "key");
|
|
|
|
CREATE INDEX IF NOT EXISTS "security_change_logs_userId_createdAt_idx" ON "security_change_logs"("userId", "createdAt");
|
|
|
|
CREATE INDEX IF NOT EXISTS "data_export_requests_userId_idx" ON "data_export_requests"("userId");
|
|
|
|
CREATE INDEX IF NOT EXISTS "account_deletion_requests_userId_idx" ON "account_deletion_requests"("userId");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "whmcs_user_links_installationId_externalUserId_key" ON "whmcs_user_links"("installationId", "externalUserId");
|
|
CREATE INDEX IF NOT EXISTS "whmcs_user_links_userId_idx" ON "whmcs_user_links"("userId");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "whmcs_product_mappings_installationId_externalProductId_key" ON "whmcs_product_mappings"("installationId", "externalProductId");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "whmcs_option_mappings_installationId_externalOptionId_externalValueId_key" ON "whmcs_option_mappings"("installationId", "externalOptionId", "externalValueId");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "whmcs_addon_mappings_installationId_externalAddonId_key" ON "whmcs_addon_mappings"("installationId", "externalAddonId");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "feature_flags_key_key" ON "feature_flags"("key");
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "system_settings_key_key" ON "system_settings"("key");
|
|
|
|
CREATE INDEX IF NOT EXISTS "job_records_queue_status_idx" ON "job_records"("queue", "status");
|
|
CREATE INDEX IF NOT EXISTS "job_records_createdAt_idx" ON "job_records"("createdAt");
|
|
|
|
CREATE INDEX IF NOT EXISTS "audit_events_userId_idx" ON "audit_events"("userId");
|
|
CREATE INDEX IF NOT EXISTS "audit_events_action_idx" ON "audit_events"("action");
|
|
CREATE INDEX IF NOT EXISTS "audit_events_entityType_entityId_idx" ON "audit_events"("entityType", "entityId");
|
|
CREATE INDEX IF NOT EXISTS "audit_events_createdAt_idx" ON "audit_events"("createdAt");
|
|
|
|
-- AddForeignKey
|
|
DO $$ BEGIN
|
|
ALTER TABLE "organization_members" ADD CONSTRAINT "organization_members_organizationId_fkey" FOREIGN KEY ("organizationId") REFERENCES "organizations"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "organization_members" ADD CONSTRAINT "organization_members_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "organization_invites" ADD CONSTRAINT "organization_invites_organizationId_fkey" FOREIGN KEY ("organizationId") REFERENCES "organizations"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "organization_invites" ADD CONSTRAINT "organization_invites_invitedById_fkey" FOREIGN KEY ("invitedById") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "game_server_members" ADD CONSTRAINT "game_server_members_serverId_fkey" FOREIGN KEY ("serverId") REFERENCES "game_servers"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "game_server_members" ADD CONSTRAINT "game_server_members_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "game_server_properties" ADD CONSTRAINT "game_server_properties_serverId_fkey" FOREIGN KEY ("serverId") REFERENCES "game_servers"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "game_server_environment_variables" ADD CONSTRAINT "game_server_environment_variables_serverId_fkey" FOREIGN KEY ("serverId") REFERENCES "game_servers"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "game_server_secrets" ADD CONSTRAINT "game_server_secrets_serverId_fkey" FOREIGN KEY ("serverId") REFERENCES "game_servers"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "game_node_capabilities" ADD CONSTRAINT "game_node_capabilities_nodeId_fkey" FOREIGN KEY ("nodeId") REFERENCES "game_nodes"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "game_node_maintenance_windows" ADD CONSTRAINT "game_node_maintenance_windows_nodeId_fkey" FOREIGN KEY ("nodeId") REFERENCES "game_nodes"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "software_builds" ADD CONSTRAINT "software_builds_minecraftVersionId_fkey" FOREIGN KEY ("minecraftVersionId") REFERENCES "minecraft_versions"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "catalog_projects" ADD CONSTRAINT "catalog_projects_providerId_fkey" FOREIGN KEY ("providerId") REFERENCES "catalog_providers"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "catalog_versions" ADD CONSTRAINT "catalog_versions_projectId_fkey" FOREIGN KEY ("projectId") REFERENCES "catalog_projects"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "catalog_dependencies" ADD CONSTRAINT "catalog_dependencies_versionId_fkey" FOREIGN KEY ("versionId") REFERENCES "catalog_versions"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "worlds" ADD CONSTRAINT "worlds_serverId_fkey" FOREIGN KEY ("serverId") REFERENCES "game_servers"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "world_archives" ADD CONSTRAINT "world_archives_worldId_fkey" FOREIGN KEY ("worldId") REFERENCES "worlds"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "schedules" ADD CONSTRAINT "schedules_serverId_fkey" FOREIGN KEY ("serverId") REFERENCES "game_servers"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "scheduled_tasks" ADD CONSTRAINT "scheduled_tasks_scheduleId_fkey" FOREIGN KEY ("scheduleId") REFERENCES "schedules"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "notifications" ADD CONSTRAINT "notifications_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "notification_preferences" ADD CONSTRAINT "notification_preferences_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "abuse_reports" ADD CONSTRAINT "abuse_reports_reporterId_fkey" FOREIGN KEY ("reporterId") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "support_tickets" ADD CONSTRAINT "support_tickets_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "support_ticket_messages" ADD CONSTRAINT "support_ticket_messages_ticketId_fkey" FOREIGN KEY ("ticketId") REFERENCES "support_tickets"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "payments" ADD CONSTRAINT "payments_invoiceId_fkey" FOREIGN KEY ("invoiceId") REFERENCES "invoices"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "security_change_logs" ADD CONSTRAINT "security_change_logs_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "data_export_requests" ADD CONSTRAINT "data_export_requests_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "account_deletion_requests" ADD CONSTRAINT "account_deletion_requests_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "whmcs_user_links" ADD CONSTRAINT "whmcs_user_links_installationId_fkey" FOREIGN KEY ("installationId") REFERENCES "whmcs_installations"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "whmcs_user_links" ADD CONSTRAINT "whmcs_user_links_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "whmcs_product_mappings" ADD CONSTRAINT "whmcs_product_mappings_installationId_fkey" FOREIGN KEY ("installationId") REFERENCES "whmcs_installations"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "whmcs_option_mappings" ADD CONSTRAINT "whmcs_option_mappings_installationId_fkey" FOREIGN KEY ("installationId") REFERENCES "whmcs_installations"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "whmcs_addon_mappings" ADD CONSTRAINT "whmcs_addon_mappings_installationId_fkey" FOREIGN KEY ("installationId") REFERENCES "whmcs_installations"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "audit_events" ADD CONSTRAINT "audit_events_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
EXCEPTION WHEN duplicate_object THEN NULL;
|
|
END $$;
|