Database Schema

Prisma 6 ORM with PostgreSQL 12+. Type-safe queries, migrations, model lifecycle hooks.

Core Models

User

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  password  String   // bcrypt hashed
  firstName String
  lastName  String
  role      UserRole @default(USER)
  deleted   Boolean  @default(false)
}

Machine (VM)

model Machine {
  id            String        @id @default(uuid())
  name          String
  status        MachineStatus
  departmentId  String
  userId        String        // Owner
  osType        String
  cpus          Int
  memoryMB      Int
  diskGB        Int
}

Department

model Department {
  id          String @id @default(uuid())
  name        String @unique
  description String?
  machines    Machine[]
}

Node

Libvirt node running VMs. Contains host info, capacity.

ISO

ISO images for OS installation. Stored at /var/lib/libvirt/images/.

VM Configuration Models

MachineConfiguration

Hardware config: CPU, RAM, disk, GPU passthrough, network interfaces.

MachineTemplate

VM templates for quick deployment. OS-specific with preconfigured resources.

MachineTemplateCategory

Template organization (Windows, Linux, Development, Production).

Application Models

Application

Application catalog with name, version, installer info.

MachineApplication

Links Machine ↔ Application. Tracks installation status.

ApplicationUsage

Tracks application usage: launch count, total duration, last used.

Monitoring & Health Models

SystemMetrics

CPU, memory, disk, network usage collected via VirtIO socket. 7-day retention.

ProcessSnapshot

Running processes on guest VMs. Updated via guest agent.

PortUsage

Network ports in use on VMs.

BlockedConnection

Firewall-blocked connections. Suggests rules for commonly blocked traffic.

WindowsService

Windows services status on guest VMs.

VMHealthSnapshot

Health check results: disk space, updates, defender status, resource usage.

VMHealthAlert

Alerts generated from health checks (LOW/MEDIUM/HIGH/CRITICAL severity).

Maintenance Models

MaintenanceTask

Scheduled maintenance: disk cleanup, defrag, Windows updates, Defender scans.

MaintenanceHistory

Execution history for maintenance tasks.

Firewall Models

FirewallRuleSet

Container for firewall rules (per-VM or per-department).

FirewallRule

Individual firewall rule: action (ACCEPT/DROP), direction, protocol, ports, IP ranges.

Recommendation Model

Recommendation

Automated recommendations based on health data, usage patterns, performance metrics.

System Models

ErrorLog

Server-side error logging with stack traces, severity.

BackgroundTaskLog

Background task execution logs (health checks, maintenance).

AppSettings

System-wide configuration (maintenance schedule, alert thresholds).

PendingCommand

Commands queued for VM execution (restart, config changes).

Notification

User notifications for alerts, task completion, system events.

Relationships

• User → Machine (1:N, owner)
• Department → Machine (1:N)
• Machine → FirewallRuleSet (1:1)
• Machine → SystemMetrics (1:N)
• Machine → MaintenanceTask (1:N)
• Machine → VMHealthSnapshot (1:N)
• Machine → Snapshot (1:N)
• Application → MachineApplication (1:N)
• FirewallRuleSet → FirewallRule (1:N)

Cascade deletes configured for dependent resources.

Indexing

Indexes on: id (primary keys), email (User), name (Machine, Department), status (Machine), timestamp (metrics, logs), foreign keys.

Enums

UserRole: USER, ADMIN, SUPER_ADMIN MachineStatus: RUNNING, STOPPED, PAUSED, SUSPENDED, ERROR MaintenanceType: DISK_CLEANUP, DEFRAG, WINDOWS_UPDATE, DEFENDER_SCAN AlertSeverity: LOW, MEDIUM, HIGH, CRITICAL FirewallAction: ACCEPT, DROP FirewallDirection: INBOUND, OUTBOUND FirewallProtocol: TCP, UDP, ICMP, ALL

Migrations

Run migrations: npm run db:migrate (backend directory) Generate Prisma client: npm run db:generate Seed database: npm run db:seed

Schema location: backend/prisma/schema.prisma

For API usage, see graphql-api.md. For architecture details, see architecture.md.