Unified Update Management Platform

    "From each according to their updates, to each according to their needs"

Executive Summary

Aggregator is a self-hosted, cross-platform update management dashboard that provides centralized visibility and control over Windows Updates, Linux packages (apt/yum/dnf/aur), Winget applications, and Docker containers. Think ConnectWise Automate meets Grafana, but open-source and beautiful.
Core Value Proposition

    Single Pane of Glass: View all pending updates across your entire infrastructure
    Actionable Intelligence: Don't just see vulnerabilities—schedule and execute patches
    AI-Assisted (Future): Natural language queries, intelligent scheduling, failure analysis
    Selfhoster-First: Designed for homelabs, small IT teams, and SMBs (not enterprise bloat)

Project Structure

aggregator/
├── aggregator-server/          # Go - Central API & orchestration
├── aggregator-agent/           # Go - Lightweight cross-platform agent
├── aggregator-web/             # React/TypeScript - Web dashboard
├── aggregator-cli/             # Go - CLI tool for power users
├── docs/                       # Documentation
├── scripts/                    # Deployment helpers
└── docker-compose.yml          # Quick start deployment

Architecture Overview

┌─────────────────────────────────────────────────────────────┐
│                    Aggregator Web UI                        │
│  ┌──────────────────────────────────────────────────────┐   │
│  │  Main Dashboard        │  [AI Chat Sidebar - Hidden] │   │
│  │  ├─ Summary Cards      │  └─ Slides from right       │   │
│  │  ├─ Agent List         │     when opened              │   │
│  │  ├─ Updates Table      │                              │   │
│  │  ├─ Maintenance Windows│                              │   │
│  │  └─ Logs Viewer        │                              │   │
│  └──────────────────────────────────────────────────────┘   │
└───────────────────────┬─────────────────────────────────────┘
                        │ HTTPS/WSS
┌───────────────────────▼─────────────────────────────────────┐
│              Aggregator Server (Go)                         │
│  ┌──────────────┐  ┌──────────────┐  ┌─────────────────┐   │
│  │ REST API     │  │ WebSocket    │  │ AI Engine       │   │
│  │ /api/v1/*    │  │ (real-time)  │  │ (Ollama/OpenAI) │   │
│  └──────────────┘  └──────────────┘  └─────────────────┘   │
│  ┌──────────────────────────────────────────────────────┐   │
│  │              PostgreSQL Database                     │   │
│  │  • agents      • update_packages  • maintenance_windows│  │
│  │  • agent_specs • update_logs      • ai_decisions     │   │
│  └──────────────────────────────────────────────────────┘   │
└───────────────────────┬─────────────────────────────────────┘
                        │ Agent Pull (every 5 min)
          ┌─────────────┴─────────────┬──────────────┐
          │                           │              │
    ┌─────▼──────┐          ┌────────▼───────┐  ┌───▼────────┐
    │Agent (Win) │          │Agent (Linux)   │  │Agent (Mac) │
    │            │          │                │  │            │
    │• WU API    │          │• apt/yum/dnf   │  │• brew      │
    │• Winget    │          │• Docker        │  │• Docker    │
    │• Docker    │          │• Snap/Flatpak │  │            │
    └────────────┘          └────────────────┘  └────────────┘

Data Models
1. Agent

type Agent struct {
    ID              uuid.UUID         `json:"id" db:"id"`
    Hostname        string            `json:"hostname" db:"hostname"`
    OSType          string            `json:"os_type" db:"os_type"` // windows, linux, macos
    OSVersion       string            `json:"os_version" db:"os_version"`
    OSArchitecture  string            `json:"os_architecture" db:"os_architecture"` // x86_64, arm64
    AgentVersion    string            `json:"agent_version" db:"agent_version"`
    LastSeen        time.Time         `json:"last_seen" db:"last_seen"`
    Status          string            `json:"status" db:"status"` // online, offline, error
    Metadata        map[string]any    `json:"metadata" db:"metadata"` // JSONB
    CreatedAt       time.Time         `json:"created_at" db:"created_at"`
    UpdatedAt       time.Time         `json:"updated_at" db:"updated_at"`
}

2. AgentSpecs (System Information)

type AgentSpecs struct {
    ID                  uuid.UUID       `json:"id" db:"id"`
    AgentID             uuid.UUID       `json:"agent_id" db:"agent_id"`
    CPUModel            string          `json:"cpu_model" db:"cpu_model"`
    CPUCores            int             `json:"cpu_cores" db:"cpu_cores"`
    MemoryTotalMB       int             `json:"memory_total_mb" db:"memory_total_mb"`
    DiskTotalGB         int             `json:"disk_total_gb" db:"disk_total_gb"`
    DiskFreeGB          int             `json:"disk_free_gb" db:"disk_free_gb"`
    NetworkInterfaces   []NetworkIF     `json:"network_interfaces" db:"network_interfaces"`
    DockerInstalled     bool            `json:"docker_installed" db:"docker_installed"`
    DockerVersion       string          `json:"docker_version" db:"docker_version"`
    PackageManagers     []string        `json:"package_managers" db:"package_managers"` // ["apt", "snap"]
    CollectedAt         time.Time       `json:"collected_at" db:"collected_at"`
}

type NetworkIF struct {
    Name string `json:"name"`
    IPv4 string `json:"ipv4"`
    MAC  string `json:"mac"`
}

3. UpdatePackage (Core Model)

type UpdatePackage struct {
    ID                uuid.UUID   `json:"id" db:"id"`
    AgentID           uuid.UUID   `json:"agent_id" db:"agent_id"`
    PackageType       string      `json:"package_type" db:"package_type"`
    // ^ windows_update, winget, apt, yum, dnf, aur, docker_image, snap, flatpak
    PackageName       string      `json:"package_name" db:"package_name"`
    PackageDescription string     `json:"package_description" db:"package_description"`
    CurrentVersion    string      `json:"current_version" db:"current_version"`
    AvailableVersion  string      `json:"available_version" db:"available_version"`
    Severity          string      `json:"severity" db:"severity"` // critical, important, moderate, low
    CVEList           []string    `json:"cve_list" db:"cve_list"`
    KBID              string      `json:"kb_id" db:"kb_id"` // Windows KB number
    RepositorySource  string      `json:"repository_source" db:"repository_source"`
    SizeBytes         int64       `json:"size_bytes" db:"size_bytes"`
    Status            string      `json:"status" db:"status"`
    // ^ pending, approved, scheduled, installing, installed, failed, ignored
    DiscoveredAt      time.Time   `json:"discovered_at" db:"discovered_at"`
    ApprovedBy        string      `json:"approved_by,omitempty" db:"approved_by"`
    ApprovedAt        *time.Time  `json:"approved_at,omitempty" db:"approved_at"`
    ScheduledFor      *time.Time  `json:"scheduled_for,omitempty" db:"scheduled_for"`
    InstalledAt       *time.Time  `json:"installed_at,omitempty" db:"installed_at"`
    ErrorMessage      string      `json:"error_message,omitempty" db:"error_message"`
    Metadata          map[string]any `json:"metadata" db:"metadata"` // JSONB extensible
}

4. MaintenanceWindow

type MaintenanceWindow struct {
    ID                    uuid.UUID   `json:"id" db:"id"`
    Name                  string      `json:"name" db:"name"`
    Description           string      `json:"description" db:"description"`
    StartTime             time.Time   `json:"start_time" db:"start_time"`
    EndTime               time.Time   `json:"end_time" db:"end_time"`
    RecurrenceRule        string      `json:"recurrence_rule" db:"recurrence_rule"` // RRULE format
    AutoApproveSeverity   []string    `json:"auto_approve_severity" db:"auto_approve_severity"`
    TargetAgentIDs        []uuid.UUID `json:"target_agent_ids" db:"target_agent_ids"`
    TargetAgentTags       []string    `json:"target_agent_tags" db:"target_agent_tags"`
    CreatedBy             string      `json:"created_by" db:"created_by"`
    CreatedAt             time.Time   `json:"created_at" db:"created_at"`
    Enabled               bool        `json:"enabled" db:"enabled"`
}

5. UpdateLog

type UpdateLog struct {
    ID              uuid.UUID   `json:"id" db:"id"`
    AgentID         uuid.UUID   `json:"agent_id" db:"agent_id"`
    UpdatePackageID *uuid.UUID  `json:"update_package_id,omitempty" db:"update_package_id"`
    Action          string      `json:"action" db:"action"` // scan, download, install, rollback
    Result          string      `json:"result" db:"result"` // success, failed, partial
    Stdout          string      `json:"stdout" db:"stdout"`
    Stderr          string      `json:"stderr" db:"stderr"`
    ExitCode        int         `json:"exit_code" db:"exit_code"`
    DurationSeconds int         `json:"duration_seconds" db:"duration_seconds"`
    ExecutedAt      time.Time   `json:"executed_at" db:"executed_at"`
}

API Specification
Base URL: https://aggregator.yourdomain.com/api/v1
Authentication

Authorization: Bearer <jwt_token>

Endpoints
Agents

GET    /agents                          # List all agents
GET    /agents/{id}                     # Get agent details
POST   /agents/{id}/scan                # Trigger update scan
DELETE /agents/{id}                     # Decommission agent
GET    /agents/{id}/specs               # Get system specs
GET    /agents/{id}/updates             # Get updates for agent
GET    /agents/{id}/logs                # Get agent logs

Updates

GET    /updates                         # List all updates (filterable)
  ?agent_id=uuid
  &status=pending
  &severity=critical,important
  &package_type=windows_update,apt

GET    /updates/{id}                    # Get update details
POST   /updates/{id}/approve            # Approve single update
POST   /updates/{id}/schedule           # Schedule update
  Body: {"scheduled_for": "2025-01-20T02:00:00Z"}

POST   /updates/bulk/approve            # Bulk approve
  Body: {"update_ids": ["uuid1", "uuid2"]}

POST   /updates/bulk/schedule           # Bulk schedule
  Body: {
    "update_ids": ["uuid1"],
    "scheduled_for": "2025-01-20T02:00:00Z"
  }

PATCH  /updates/{id}                    # Update status (ignore, etc)

Maintenance Windows

GET    /maintenance-windows             # List windows
POST   /maintenance-windows             # Create window
  Body: {
    "name": "Weekend Patching",
    "start_time": "2025-01-20T02:00:00Z",
    "end_time": "2025-01-20T06:00:00Z",
    "recurrence_rule": "FREQ=WEEKLY;BYDAY=SA",
    "auto_approve_severity": ["critical", "important"],
    "target_agent_tags": ["production"]
  }

GET    /maintenance-windows/{id}        # Get window details
PATCH  /maintenance-windows/{id}        # Update window
DELETE /maintenance-windows/{id}        # Delete window

Logs

GET    /logs                            # Global logs (paginated)
  ?agent_id=uuid
  &action=install
  &result=failed
  &from=2025-01-01
  &to=2025-01-31

Statistics

GET    /stats/summary                   # Dashboard summary
  Response: {
    "total_agents": 48,
    "agents_online": 45,
    "total_updates_pending": 234,
    "updates_by_severity": {
      "critical": 12,
      "important": 45,
      "moderate": 89,
      "low": 88
    },
    "updates_by_type": {
      "windows_update": 56,
      "winget": 23,
      "apt": 89,
      "docker_image": 66
    }
  }

AI Endpoints (Future Phase)

POST   /ai/query                        # Natural language query
  Body: {
    "query": "Show critical Windows updates for web servers",
    "context": "user_viewing_dashboard"
  }
  Response: {
    "intent": "filter_updates",
    "entities": {...},
    "results": [...],
    "explanation": "Found 8 critical Windows updates..."
  }

POST   /ai/recommend                    # Get AI recommendations
POST   /ai/schedule                     # Let AI schedule updates
GET    /ai/decisions                    # Audit trail of AI actions

Agent Protocol
1. Registration (First Boot)

Agent → Server: POST /api/v1/agents/register
Body: {
  "hostname": "WEB-01",
  "os_type": "windows",
  "os_version": "Windows Server 2022",
  "os_architecture": "x86_64",
  "agent_version": "1.0.0"
}

Server → Agent: 200 OK
Body: {
  "agent_id": "550e8400-e29b-41d4-a716-446655440000",
  "token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...",
  "config": {
    "check_in_interval": 300,  // seconds
    "server_url": "https://aggregator.internal"
  }
}

Agent stores: agent_id and token in local config file.
2. Check-In Loop (Every 5 Minutes)

Agent → Server: GET /api/v1/agents/{id}/commands
Headers: Authorization: Bearer {token}

Server → Agent: 200 OK
Body: {
  "commands": [
    {
      "id": "cmd_123",
      "type": "scan_updates",
      "params": {}
    }
  ]
}

Command Types:

    scan_updates - Scan for available updates
    collect_specs - Collect system information
    install_updates - Install specified updates
    rollback_update - Rollback a failed update
    update_agent - Agent self-update

3. Report Updates

Agent → Server: POST /api/v1/agents/{id}/updates
Body: {
  "command_id": "cmd_123",
  "timestamp": "2025-01-15T14:30:00Z",
  "updates": [
    {
      "package_type": "windows_update",
      "package_name": "2024-01 Cumulative Update for Windows Server 2022",
      "kb_id": "KB5034441",
      "current_version": null,
      "available_version": "2024-01",
      "severity": "critical",
      "cve_list": ["CVE-2024-1234"],
      "size_bytes": 524288000,
      "requires_reboot": true
    }
  ]
}

Server → Agent: 200 OK

4. Execute Update

Agent → Server: GET /api/v1/agents/{id}/commands

Server → Agent: 200 OK
Body: {
  "commands": [
    {
      "id": "cmd_456",
      "type": "install_updates",
      "params": {
        "update_ids": ["upd_789"],
        "packages": ["KB5034441"]
      }
    }
  ]
}

Agent executes, then reports:

Agent → Server: POST /api/v1/agents/{id}/logs
Body: {
  "command_id": "cmd_456",
  "action": "install",
  "result": "success",
  "stdout": "...",
  "stderr": "",
  "exit_code": 0,
  "duration_seconds": 120
}

Agent Implementation Details
Windows Agent

Update Scanners:

    Windows Update API (COM)

// Using go-ole to interact with Windows Update COM interfaces
import "github.com/go-ole/go-ole"

func ScanWindowsUpdates() ([]Update, error) {
    updateSession := ole.CreateObject("Microsoft.Update.Session")
    updateSearcher := updateSession.CreateUpdateSearcher()
    searchResult := updateSearcher.Search("IsInstalled=0")

    // Parse updates, extract KB IDs, CVEs, severity
    return parseUpdates(searchResult)
}

    Winget

func ScanWingetUpdates() ([]Update, error) {
    cmd := exec.Command("winget", "list", "--upgrade-available", "--accept-source-agreements")
    output, _ := cmd.Output()

    // Parse output, extract package IDs, versions
    return parseWingetOutput(output)
}

    Docker

func ScanDockerUpdates() ([]Update, error) {
    cli, _ := client.NewClientWithOpts()
    containers, _ := cli.ContainerList(context.Background(), types.ContainerListOptions{All: true})

    for _, container := range containers {
        // Compare current image digest with registry latest
        // Use Docker Registry HTTP API v2
    }
}

Linux Agent

Update Scanners:

    APT (Debian/Ubuntu)

func ScanAPTUpdates() ([]Update, error) {
    // Update package cache
    exec.Command("apt-get", "update").Run()

    // Get upgradable packages
    cmd := exec.Command("apt", "list", "--upgradable")
    output, _ := cmd.Output()

    // Parse, extract package names, versions
    // Query Ubuntu Security Advisories for CVEs
    return parseAPTOutput(output)
}

    YUM/DNF (RHEL/CentOS/Fedora)

func ScanYUMUpdates() ([]Update, error) {
    cmd := exec.Command("yum", "check-update", "--quiet")
    output, _ := cmd.Output()

    // Parse output
    // Query Red Hat Security Data API for CVEs
    return parseYUMOutput(output)
}

    AUR (Arch Linux)

func ScanAURUpdates() ([]Update, error) {
    // Use yay or paru AUR helpers
    cmd := exec.Command("yay", "-Qu")
    output, _ := cmd.Output()

    return parseAUROutput(output)
}

Mac Agent

func ScanBrewUpdates() ([]Update, error) {
    cmd := exec.Command("brew", "outdated", "--json")
    output, _ := cmd.Output()

    var outdated []BrewPackage
    json.Unmarshal(output, &outdated)

    return convertToUpdates(outdated)
}

Web Dashboard Design
Technology Stack

    Framework: React 18 + TypeScript
    Styling: TailwindCSS
    State Management: Zustand (lightweight, simple)
    Data Fetching: TanStack Query (React Query)
    Charts: Recharts
    Tables: TanStack Table
    Real-time: WebSocket (native)

Layout

┌─────────────────────────────────────────────────────────┐
│  Aggregator  │  Agents (48)  Updates (234)  Settings    │ ← Header
├─────────────────────────────────────────────────────────┤
│                                                          │
│  ┌───────────┐  ┌───────────┐  ┌───────────┐           │
│  │  48 Total │  │ 234 Pending│ │ 12 Critical│  ← Cards │
│  │  Agents   │  │  Updates   │ │  Updates   │           │
│  └───────────┘  └───────────┘  └───────────┘           │
│                                                          │
│  Updates by Type         │  Updates by Severity         │
│  [Bar Chart]             │  [Donut Chart]               │
│                                                          │
├─────────────────────────────────────────────────────────┤
│  Recent Activity                                         │
│  ┌─────────────────────────────────────────────────┐    │
│  │ ✅ WEB-01: Installed KB5034441 (5 min ago)      │    │
│  │ ⏳ DB-01: Installing nginx update...            │    │
│  │ ❌ APP-03: Failed to install docker image       │    │
│  └─────────────────────────────────────────────────┘    │
│                                                          │
│  [View All Updates →]  [Schedule Maintenance →]         │
└─────────────────────────────────────────────────────────┘

                                       ┌──────────────────┐
                                       │                  │
                                       │  AI Chat         │
                                       │  ──────────      │
                                       │  💬 "Show me..." │
                                       │                  │
                                       │  [Slides from    │
                                       │   right side]    │
                                       │                  │
                                       └──────────────────┘

Key Views

    Dashboard - Summary, charts, recent activity
    Agents - List all agents, filter by OS/status
    Updates - Filterable table of all pending updates
    Maintenance Windows - Calendar view, create/edit windows
    Logs - Searchable update execution logs
    Settings - Configuration, users, API keys

Updates Table (Primary View)

┌──────────────────────────────────────────────────────────────┐
│ Updates (234)                                  [Filters ▼]   │
├──────────────────────────────────────────────────────────────┤
│ [✓] Select All  │  Approve (12)  Schedule  Ignore            │
├──────────────────────────────────────────────────────────────┤
│ [✓] │ 🔴 CRITICAL │ WEB-01 │ Windows Update │ KB5034441    │
│     │ CVE-2024-1234 │ 2024-01 Cumulative Update             │
├──────────────────────────────────────────────────────────────┤
│ [ ] │ 🟠 IMPORTANT │ WEB-01 │ Winget │ PowerShell          │
│     │ 7.3.0 → 7.4.1                                         │
├──────────────────────────────────────────────────────────────┤
│ [✓] │ 🔴 CRITICAL │ DB-01 │ APT │ linux-image-generic    │
│     │ CVE-2024-5555 │ Kernel update - requires reboot       │
├──────────────────────────────────────────────────────────────┤
│ [ ] │ 🟡 MODERATE │ PROXY-01 │ Docker │ nginx              │
│     │ 1.25.3 → 1.25.4                                       │
└──────────────────────────────────────────────────────────────┘

Filters:
- Agent: [All] [WEB-*] [DB-*] [Custom...]
- Type: [All] [Windows Update] [Winget] [APT] [Docker]
- Severity: [All] [Critical] [Important] [Moderate] [Low]
- Status: [Pending] [Approved] [Scheduled] [Installing] [Failed]

AI Chat Sidebar (Hidden by Default)

                                    ┌─────────────────────┐
                                    │ AI Assistant   [✕]  │
                                    ├─────────────────────┤
                                    │                     │
                                    │ 🤖 How can I help?  │
                                    │                     │
                                    │ Quick actions:      │
                                    │ • Critical updates  │
                                    │ • Schedule weekend  │
                                    │ • Check failures    │
                                    │                     │
                                    ├─────────────────────┤
                                    │ You: Show critical  │
                                    │      Windows updates│
                                    │                     │
                                    │ AI: Found 3 critical│
                                    │     Windows updates │
                                    │     affecting 2     │
                                    │     servers...      │
                                    │     [View Results]  │
                                    │                     │
                                    ├─────────────────────┤
                                    │ [Type message...]   │
                                    └─────────────────────┘

Database Schema (PostgreSQL)

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Agents table
CREATE TABLE agents (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    hostname VARCHAR(255) NOT NULL,
    os_type VARCHAR(50) NOT NULL CHECK (os_type IN ('windows', 'linux', 'macos')),
    os_version VARCHAR(100),
    os_architecture VARCHAR(20),
    agent_version VARCHAR(20) NOT NULL,
    last_seen TIMESTAMP NOT NULL DEFAULT NOW(),
    status VARCHAR(20) DEFAULT 'online' CHECK (status IN ('online', 'offline', 'error')),
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_agents_status ON agents(status);
CREATE INDEX idx_agents_os_type ON agents(os_type);

-- Agent specs
CREATE TABLE agent_specs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    agent_id UUID REFERENCES agents(id) ON DELETE CASCADE,
    cpu_model VARCHAR(255),
    cpu_cores INTEGER,
    memory_total_mb INTEGER,
    disk_total_gb INTEGER,
    disk_free_gb INTEGER,
    network_interfaces JSONB,
    docker_installed BOOLEAN DEFAULT false,
    docker_version VARCHAR(50),
    package_managers TEXT[],
    collected_at TIMESTAMP DEFAULT NOW()
);

-- Update packages
CREATE TABLE update_packages (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    agent_id UUID REFERENCES agents(id) ON DELETE CASCADE,
    package_type VARCHAR(50) NOT NULL,
    package_name VARCHAR(500) NOT NULL,
    package_description TEXT,
    current_version VARCHAR(100),
    available_version VARCHAR(100) NOT NULL,
    severity VARCHAR(20) CHECK (severity IN ('critical', 'important', 'moderate', 'low', 'none')),
    cve_list TEXT[],
    kb_id VARCHAR(50),
    repository_source VARCHAR(255),
    size_bytes BIGINT,
    status VARCHAR(30) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'scheduled', 'installing', 'installed', 'failed', 'ignored')),
    discovered_at TIMESTAMP DEFAULT NOW(),
    approved_by VARCHAR(255),
    approved_at TIMESTAMP,
    scheduled_for TIMESTAMP,
    installed_at TIMESTAMP,
    error_message TEXT,
    metadata JSONB
);

CREATE INDEX idx_updates_status ON update_packages(status);
CREATE INDEX idx_updates_agent ON update_packages(agent_id);
CREATE INDEX idx_updates_severity ON update_packages(severity);
CREATE INDEX idx_updates_package_type ON update_packages(package_type);

-- Maintenance windows
CREATE TABLE maintenance_windows (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    recurrence_rule VARCHAR(255),
    auto_approve_severity TEXT[],
    target_agent_ids UUID[],
    target_agent_tags TEXT[],
    created_by VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW(),
    enabled BOOLEAN DEFAULT true
);

-- Update logs
CREATE TABLE update_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    agent_id UUID REFERENCES agents(id) ON DELETE CASCADE,
    update_package_id UUID REFERENCES update_packages(id) ON DELETE SET NULL,
    action VARCHAR(50) NOT NULL,
    result VARCHAR(20) NOT NULL CHECK (result IN ('success', 'failed', 'partial')),
    stdout TEXT,
    stderr TEXT,
    exit_code INTEGER,
    duration_seconds INTEGER,
    executed_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_logs_agent ON update_logs(agent_id);
CREATE INDEX idx_logs_result ON update_logs(result);

-- AI decisions (future)
CREATE TABLE ai_decisions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    decision_type VARCHAR(50) NOT NULL,
    context JSONB NOT NULL,
    reasoning TEXT,
    action_taken JSONB,
    confidence_score FLOAT,
    overridden_by VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Agent tags
CREATE TABLE agent_tags (
    agent_id UUID REFERENCES agents(id) ON DELETE CASCADE,
    tag VARCHAR(100) NOT NULL,
    PRIMARY KEY (agent_id, tag)
);

-- Users (for authentication)
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    username VARCHAR(255) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(50) DEFAULT 'user' CHECK (role IN ('admin', 'user', 'readonly')),
    created_at TIMESTAMP DEFAULT NOW(),
    last_login TIMESTAMP
);

Deployment Options
Option 1: Docker Compose (Recommended for Testing)

# docker-compose.yml
version: '3.8'

services:
  aggregator-db:
    image: postgres:16-alpine
    environment:
      POSTGRES_DB: aggregator
      POSTGRES_USER: aggregator
      POSTGRES_PASSWORD: ${DB_PASSWORD}
    volumes:
      - aggregator-db-data:/var/lib/postgresql/data
    ports:
      - "5432:5432"
    restart: unless-stopped

  aggregator-server:
    image: ghcr.io/yourorg/aggregator-server:latest
    environment:
      DATABASE_URL: postgres://aggregator:${DB_PASSWORD}@aggregator-db:5432/aggregator
      JWT_SECRET: ${JWT_SECRET}
      SERVER_PORT: 8080
      OLLAMA_URL: http://ollama:11434  # Optional AI
    depends_on:
      - aggregator-db
    ports:
      - "8080:8080"
    restart: unless-stopped

  aggregator-web:
    image: ghcr.io/yourorg/aggregator-web:latest
    environment:
      VITE_API_URL: http://localhost:8080
    ports:
      - "3000:80"
    depends_on:
      - aggregator-server
    restart: unless-stopped

  # Optional: Local AI with Ollama
  ollama:
    image: ollama/ollama:latest
    volumes:
      - ollama-data:/root/.ollama
    ports:
      - "11434:11434"
    restart: unless-stopped

volumes:
  aggregator-db-data:
  ollama-data:

Option 2: Kubernetes (Production)

# aggregator-namespace.yaml
apiVersion: v1
kind: Namespace
metadata:
  name: aggregator

---
# aggregator-db-statefulset.yaml
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: aggregator-db
  namespace: aggregator
spec:
  serviceName: aggregator-db
  replicas: 1
  selector:
    matchLabels:
      app: aggregator-db
  template:
    metadata:
      labels:
        app: aggregator-db
    spec:
      containers:
      - name: postgres
        image: postgres:16-alpine
        env:
        - name: POSTGRES_DB
          value: aggregator
        - name: POSTGRES_USER
          valueFrom:
            secretKeyRef:
              name: aggregator-db-secret
              key: username
        - name: POSTGRES_PASSWORD
          valueFrom:
            secretKeyRef:
              name: aggregator-db-secret
              key: password
        ports:
        - containerPort: 5432
        volumeMounts:
        - name: aggregator-db-storage
          mountPath: /var/lib/postgresql/data
  volumeClaimTemplates:
  - metadata:
      name: aggregator-db-storage
    spec:
      accessModes: ["ReadWriteOnce"]
      resources:
        requests:
          storage: 50Gi

---
# aggregator-server-deployment.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  name: aggregator-server
  namespace: aggregator
spec:
  replicas: 3
  selector:
    matchLabels:
      app: aggregator-server
  template:
    metadata:
      labels:
        app: aggregator-server
    spec:
      containers:
      - name: server
        image: ghcr.io/yourorg/aggregator-server:latest
        env:
        - name: DATABASE_URL
          valueFrom:
            secretKeyRef:
              name: aggregator-db-secret
              key: url
        - name: JWT_SECRET
          valueFrom:
            secretKeyRef:
              name: aggregator-jwt-secret
              key: secret
        ports:
        - containerPort: 8080
        livenessProbe:
          httpGet:
            path: /health
            port: 8080
          initialDelaySeconds: 30
          periodSeconds: 10
        readinessProbe:
          httpGet:
            path: /ready
            port: 8080
          initialDelaySeconds: 5
          periodSeconds: 5

Option 3: Bare Metal / VM

# Install script
#!/bin/bash

# 1. Install PostgreSQL
sudo apt update
sudo apt install postgresql postgresql-contrib

# 2. Create database
sudo -u postgres psql -c "CREATE DATABASE aggregator;"
sudo -u postgres psql -c "CREATE USER aggregator WITH PASSWORD 'changeme';"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE aggregator TO aggregator;"

# 3. Download and install server
wget https://github.com/yourorg/aggregator/releases/download/v1.0.0/aggregator-server-linux-amd64
chmod +x aggregator-server-linux-amd64
sudo mv aggregator-server-linux-amd64 /usr/local/bin/aggregator-server

# 4. Create systemd service
sudo tee /etc/systemd/system/aggregator-server.service > /dev/null <<EOF
[Unit]
Description=Aggregator Server
After=network.target postgresql.service

[Service]
Type=simple
User=aggregator
Environment="DATABASE_URL=postgres://aggregator:changeme@localhost:5432/aggregator"
Environment="JWT_SECRET=generate-secure-secret-here"
ExecStart=/usr/local/bin/aggregator-server
Restart=on-failure

[Install]
WantedBy=multi-user.target
EOF

# 5. Start service
sudo systemctl daemon-reload
sudo systemctl enable aggregator-server
sudo systemctl start aggregator-server

# 6. Install Nginx reverse proxy
sudo apt install nginx
sudo tee /etc/nginx/sites-available/aggregator > /dev/null <<EOF
server {
    listen 80;
    server_name aggregator.yourdomain.com;

    location / {
        proxy_pass http://localhost:8080;
        proxy_http_version 1.1;
        proxy_set_header Upgrade \$http_upgrade;
        proxy_set_header Connection 'upgrade';
        proxy_set_header Host \$host;
        proxy_cache_bypass \$http_upgrade;
    }
}
EOF

sudo ln -s /etc/nginx/sites-available/aggregator /etc/nginx/sites-enabled/
sudo nginx -t
sudo systemctl restart nginx

echo "Aggregator server installed! Visit http://aggregator.yourdomain.com"

Agent Installation
Windows Agent

PowerShell Install Script:

# Install-AggregatorAgent.ps1

# Check admin privileges
if (-NOT ([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")) {
    Write-Error "This script must be run as Administrator"
    exit 1
}

# Configuration
$SERVER_URL = Read-Host "Enter Aggregator server URL (e.g., https://aggregator.yourdomain.com)"
$INSTALL_PATH = "C:\Program Files\Aggregator"

# Download agent
Write-Host "Downloading Aggregator agent..."
$DOWNLOAD_URL = "$SERVER_URL/downloads/aggregator-agent-windows-amd64.exe"
New-Item -ItemType Directory -Force -Path $INSTALL_PATH | Out-Null
Invoke-WebRequest -Uri $DOWNLOAD_URL -OutFile "$INSTALL_PATH\aggregator-agent.exe"

# Create config file
@{
    server_url = $SERVER_URL
    agent_id = ""
    token = ""
    check_in_interval = 300
} | ConvertTo-Json | Out-File "$INSTALL_PATH\config.json"

# Register agent
Write-Host "Registering agent with server..."
& "$INSTALL_PATH\aggregator-agent.exe" register

# Create Windows service
New-Service -Name "AggregatorAgent" `
    -BinaryPathName "$INSTALL_PATH\aggregator-agent.exe service" `
    -DisplayName "Aggregator Agent" `
    -Description "Update management agent for Aggregator" `
    -StartupType Automatic

# Start service
Start-Service -Name "AggregatorAgent"

# Configure firewall (if needed)
# New-NetFirewallRule -DisplayName "Aggregator Agent" -Direction Outbound -Action Allow

Write-Host "Aggregator agent installed successfully!"
Write-Host "Agent ID: $(Get-Content "$INSTALL_PATH\config.json" | ConvertFrom-Json | Select-Object -ExpandProperty agent_id)"

Linux Agent

Bash Install Script:

#!/bin/bash
# install-aggregator-agent.sh

set -e

# Check root
if [ "$EUID" -ne 0 ]; then
    echo "Please run as root"
    exit 1
fi

# Configuration
read -p "Enter Aggregator server URL: " SERVER_URL
INSTALL_PATH="/opt/aggregator"
CONFIG_PATH="/etc/aggregator"

# Detect architecture
ARCH=$(uname -m)
case $ARCH in
    x86_64)  ARCH="amd64" ;;
    aarch64) ARCH="arm64" ;;
    armv7l)  ARCH="armv7" ;;
    *)       echo "Unsupported architecture: $ARCH"; exit 1 ;;
esac

# Download agent
echo "Downloading Aggregator agent for $ARCH..."
mkdir -p $INSTALL_PATH
curl -L "$SERVER_URL/downloads/aggregator-agent-linux-$ARCH" -o "$INSTALL_PATH/aggregator-agent"
chmod +x "$INSTALL_PATH/aggregator-agent"

# Create config directory
mkdir -p $CONFIG_PATH

# Create config file
cat > "$CONFIG_PATH/config.json" <<EOF
{
  "server_url": "$SERVER_URL",
  "agent_id": "",
  "token": "",
  "check_in_interval": 300
}
EOF

# Register agent
echo "Registering agent with server..."
$INSTALL_PATH/aggregator-agent register

# Create systemd service
cat > /etc/systemd/system/aggregator-agent.service <<EOF
[Unit]
Description=Aggregator Agent
After=network.target

[Service]
Type=simple
User=root
ExecStart=$INSTALL_PATH/aggregator-agent service
Restart=on-failure
RestartSec=10

[Install]
WantedBy=multi-user.target
EOF

# Enable and start service
systemctl daemon-reload
systemctl enable aggregator-agent
systemctl start aggregator-agent

echo "Aggregator agent installed successfully!"
echo "Agent ID: $(jq -r .agent_id $CONFIG_PATH/config.json)"
echo "Status: $(systemctl status aggregator-agent --no-pager)"

Configuration Files
Server Configuration

# config.yaml
server:
  port: 8080
  host: 0.0.0.0
  cors_origins:
    - http://localhost:3000
    - https://aggregator.yourdomain.com

database:
  url: postgres://aggregator:password@localhost:5432/aggregator
  max_connections: 100
  log_queries: false

auth:
  jwt_secret: "${JWT_SECRET}"
  jwt_expiry: 24h
  session_timeout: 30m

agent:
  check_in_interval: 300  # seconds
  offline_threshold: 600   # seconds before marking offline
  command_timeout: 300     # seconds for command execution

ai:
  enabled: true
  provider: ollama  # ollama, openai, anthropic
  ollama_url: http://localhost:11434
  model: llama3
  # openai_api_key: "${OPENAI_API_KEY}"
  # anthropic_api_key: "${ANTHROPIC_API_KEY}"

logging:
  level: info
  format: json
  output: stdout

features:
  auto_approve_critical: false
  maintenance_windows: true
  ai_recommendations: true

Agent Configuration

{
  "server_url": "https://aggregator.yourdomain.com",
  "agent_id": "550e8400-e29b-41d4-a716-446655440000",
  "token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...",
  "check_in_interval": 300,
  "scanners": {
    "windows_update": {
      "enabled": true,
      "categories": ["SecurityUpdates", "CriticalUpdates", "UpdateRollups"]
    },
    "winget": {
      "enabled": true,
      "sources": ["winget", "msstore"]
    },
    "apt": {
      "enabled": true,
      "auto_update_cache": true
    },
    "docker": {
      "enabled": true,
      "check_running_only": false,
      "registries": [
        "docker.io",
        "ghcr.io"
      ]
    }
  },
  "logging": {
    "level": "info",
    "file": "/var/log/aggregator-agent.log"
  }
}

Development Roadmap
Phase 1: MVP (Months 1-3)

Server:

    [x] Project setup, database schema
    [x] Agent registration API
    [x] Update ingestion API
    [x] Basic REST API (agents, updates)
    [x] JWT authentication
    [x] WebSocket for real-time updates

Agent:

    [x] Windows Update scanner
    [x] Winget scanner
    [x] APT scanner (Ubuntu/Debian)
    [x] Docker scanner
    [x] Check-in loop
    [x] Update execution

Web:

    [x] Dashboard layout
    [x] Agents list view
    [x] Updates table with filters
    [x] Approve/Schedule actions
    [x] Logs viewer

Deliverable: Working system for Windows + Ubuntu with Docker support
Phase 2: Feature Complete (Months 4-6)

Server:

    [x] Maintenance windows
    [x] Bulk operations
    [x] Advanced filtering
    [x] User management
    [x] API rate limiting

Agent:

    [x] YUM/DNF scanner (RHEL/CentOS/Fedora)
    [x] AUR scanner (Arch Linux)
    [x] Snap scanner
    [x] Flatpak scanner
    [x] Mac/Homebrew support
    [x] Rollback capability

Web:

    [x] Calendar view for maintenance windows
    [x] Advanced charts/visualizations
    [x] Detailed update info pages
    [x] User settings/preferences
    [x] Dark mode

Deliverable: Production-ready for all major platforms
Phase 3: AI Integration (Months 7-9)

Server:

    [x] AI engine abstraction layer
    [x] Ollama integration
    [x] OpenAI/Anthropic integration
    [x] Natural language query parser
    [x] Intelligent scheduling
    [x] Failure analysis

Web:

    [x] AI chat sidebar
    [x] Quick actions from AI suggestions
    [x] AI decision audit log
    [x] Confidence indicators

Agent:

    [x] Pre-update health checks
    [x] Post-update validation
    [x] Rich error reporting for AI

Deliverable: AI-assisted update management
Phase 4: Enterprise Features (Months 10-12)

Server:

    [x] Multi-tenancy support
    [x] RBAC (role-based access control)
    [x] SSO integration (SAML, OAuth)
    [x] Compliance reporting
    [x] Webhook notifications
    [x] Prometheus metrics

Web:

    [x] Multi-user collaboration
    [x] Custom dashboards
    [x] Report builder
    [x] Mobile-responsive

Agent:

    [x] Agent groups/tags
    [x] Custom scripts execution
    [x] Offline mode support

Deliverable: Enterprise-ready platform
Testing Strategy
Unit Tests

// Example: Agent scanner test
func TestWindowsUpdateScanner(t *testing.T) {
    scanner := NewWindowsUpdateScanner()
    updates, err := scanner.Scan()

    assert.NoError(t, err)
    assert.NotEmpty(t, updates)

    for _, update := range updates {
        assert.NotEmpty(t, update.PackageName)
        assert.NotEmpty(t, update.AvailableVersion)
        assert.Contains(t, []string{"critical", "important", "moderate", "low"}, update.Severity)
    }
}

Integration Tests

// Example: API integration test
func TestAgentRegistration(t *testing.T) {
    testServer := setupTestServer(t)
    defer testServer.Close()

    payload := AgentRegistrationRequest{
        Hostname: "test-agent",
        OSType: "windows",
        OSVersion: "Windows Server 2022",
        AgentVersion: "1.0.0",
    }

    resp := testServer.Post("/api/v1/agents/register", payload)
    assert.Equal(t, 200, resp.StatusCode)

    var result AgentRegistrationResponse
    json.Unmarshal(resp.Body, &result)

    assert.NotEmpty(t, result.AgentID)
    assert.NotEmpty(t, result.Token)
}

E2E Tests (Playwright)

// Example: Web dashboard E2E test
test('approve critical updates', async ({ page }) => {
  await page.goto('http://localhost:3000');
  await page.fill('[data-testid="username"]', 'admin');
  await page.fill('[data-testid="password"]', 'password');
  await page.click('[data-testid="login-button"]');

  // Navigate to updates
  await page.click('[data-testid="nav-updates"]');

  // Filter critical
  await page.selectOption('[data-testid="severity-filter"]', 'critical');

  // Select all
  await page.check('[data-testid="select-all"]');

  // Approve
  await page.click('[data-testid="approve-selected"]');

  // Verify approval
  await expect(page.locator('[data-testid="toast-success"]')).toBeVisible();
});

Security Considerations
1. Agent Authentication

// JWT token with agent claims
type AgentClaims struct {
    AgentID string `json:"agent_id"`
    jwt.StandardClaims
}

// Token rotation every 24h
func (s *Server) RefreshAgentToken(agentID string) (string, error) {
    claims := AgentClaims{
        AgentID: agentID,
        StandardClaims: jwt.StandardClaims{
            ExpiresAt: time.Now().Add(24 * time.Hour).Unix(),
        },
    }

    token := jwt.NewWithClaims(jwt.SigningMethodHS256, claims)
    return token.SignedString(s.jwtSecret)
}

2. Command Validation

// Server validates commands before sending to agent
func (s *Server) ValidateCommand(cmd Command) error {
    // Whitelist allowed commands
    allowedCommands := []string{"scan_updates", "install_updates", "collect_specs"}
    if !contains(allowedCommands, cmd.Type) {
        return ErrInvalidCommand
    }

    // Validate parameters
    if cmd.Type == "install_updates" {
        if len(cmd.Params.UpdateIDs) == 0 {
            return ErrMissingUpdateIDs
        }
        // Verify update IDs exist and are approved
        for _, id := range cmd.Params.UpdateIDs {
            update, err := s.db.GetUpdate(id)
            if err != nil || update.Status != "approved" {
                return ErrUnauthorizedUpdate
            }
        }
    }

    return nil
}

3. Rate Limiting

// Rate limit API endpoints
func RateLimitMiddleware(limit int, window time.Duration) gin.HandlerFunc {
    limiter := rate.NewLimiter(rate.Every(window), limit)

    return func(c *gin.Context) {
        if !limiter.Allow() {
            c.JSON(429, gin.H{"error": "rate limit exceeded"})
            c.Abort()
            return
        }
        c.Next()
    }
}

// Apply to sensitive endpoints
router.POST("/api/v1/updates/bulk/approve",
    RateLimitMiddleware(10, time.Minute),
    BulkApproveHandler)

4. Input Sanitization

// Sanitize all user inputs
func SanitizeAgentHostname(hostname string) string {
    // Remove special characters, limit length
    sanitized := regexp.MustCompile(`[^a-zA-Z0-9-_]`).ReplaceAllString(hostname, "")
    if len(sanitized) > 255 {
        sanitized = sanitized[:255]
    }
    return sanitized
}

5. TLS/HTTPS Only

# Force HTTPS in production
server:
  tls:
    enabled: true
    cert_file: /path/to/cert.pem
    key_file: /path/to/key.pem
    min_version: "TLS1.2"

AI Integration Deep Dive
Natural Language Query Flow

User: "Show me critical Windows updates for production servers"
  │
  ▼
┌─────────────────────────────────┐
│  Intent Parser (AI)             │
│  └─ Extract entities:           │
│     - severity: critical        │
│     - os_type: windows          │
│     - tags: production          │
└────────────┬────────────────────┘
             │
             ▼
┌─────────────────────────────────┐
│  Query Builder                  │
│  └─ Generate SQL:               │
│     SELECT * FROM update_packages│
│     WHERE severity='critical'   │
│     AND agent_id IN (...)       │
└────────────┬────────────────────┘
             │
             ▼
┌─────────────────────────────────┐
│  Execute & Format               │
│  └─ Return results with        │
│     explanation                 │
└─────────────────────────────────┘

AI Prompt Templates

const SchedulingPrompt = `
You are an IT infrastructure update scheduler. Given the following context, create an optimal update schedule.

Context:
- Pending Updates: {{.PendingUpdates}}
- Agent Info: {{.AgentInfo}}
- Historical Failures: {{.HistoricalFailures}}
- Business Hours: {{.BusinessHours}}
- Maintenance Windows: {{.MaintenanceWindows}}

Task: Schedule updates to minimize risk and downtime.

Consider:
1. Severity (critical > important > moderate > low)
2. Dependencies (OS updates before app updates)
3. Reboot requirements (group reboots together)
4. Historical success rates
5. Agent workload patterns

Output JSON format:
{
  "schedule": [
    {
      "update_id": "uuid",
      "agent_id": "uuid",
      "scheduled_for": "2025-01-20T02:00:00Z",
      "reasoning": "Critical security update, low-traffic window"
    }
  ],
  "confidence": 0.85,
  "risks": ["WEB-03 has history of nginx update failures"],
  "recommendations": ["Test on staging first", "Have rollback plan ready"]
}
`

AI-Powered Failure Analysis

func (ai *AIEngine) AnalyzeFailure(log UpdateLog, context Context) (*FailureAnalysis, error) {
    prompt := fmt.Sprintf(`
    Update failed. Analyze the error and suggest remediation.

    Update: %s %s → %s on %s
    Exit Code: %d
    Stderr: %s

    Context:
    - Similar Updates: %s
    - System Specs: %s

    Provide:
    1. Root cause analysis
    2. Recommended fix
    3. Prevention strategy
    `, log.PackageName, log.CurrentVersion, log.AvailableVersion,
       log.AgentHostname, log.ExitCode, log.Stderr,
       context.SimilarUpdates, context.SystemSpecs)

    response := ai.Query(prompt)
    return parseFailureAnalysis(response)
}

Performance Optimization
Database Indexing Strategy

-- Critical indexes for query performance
CREATE INDEX CONCURRENTLY idx_updates_composite
ON update_packages(status, severity, agent_id);

CREATE INDEX CONCURRENTLY idx_agents_last_seen
ON agents(last_seen) WHERE status = 'online';

CREATE INDEX CONCURRENTLY idx_logs_executed_at_desc
ON update_logs(executed_at DESC);

-- Partial index for pending updates (most common query)
CREATE INDEX CONCURRENTLY idx_updates_pending
ON update_packages(agent_id, severity)
WHERE status = 'pending';

Caching Strategy

// Redis cache for frequently accessed data
type Cache struct {
    redis *redis.Client
}

func (c *Cache) GetAgentSummary(agentID string) (*AgentSummary, error) {
    key := fmt.Sprintf("agent:summary:%s", agentID)

    // Try cache first
    cached, err := c.redis.Get(context.Background(), key).Result()
    if err == nil {
        var summary AgentSummary
        json.Unmarshal([]byte(cached), &summary)
        return &summary, nil
    }

    // Cache miss - query DB
    summary := c.db.GetAgentSummary(agentID)

    // Cache for 5 minutes
    data, _ := json.Marshal(summary)
    c.redis.Set(context.Background(), key, data, 5*time.Minute)

    return summary, nil
}

WebSocket Connection Pooling

// Efficient WebSocket management
type WSManager struct {
    clients map[string]*websocket.Conn
    mu      sync.RWMutex
}

func (m *WSManager) Broadcast(event Event) {
    m.mu.RLock()
    defer m.mu.RUnlock()

    for _, conn := range m.clients {
        go func(c *websocket.Conn) {
            c.WriteJSON(event)
        }(conn)
    }
}

Monitoring & Observability
Prometheus Metrics

// Define metrics
var (
    agentsOnline = promauto.NewGauge(prometheus.GaugeOpts{
        Name: "aggregator_agents_online_total",
        Help: "Number of agents currently online",
    })

    updatesPending = promauto.NewGaugeVec(prometheus.GaugeOpts{
        Name: "aggregator_updates_pending_total",
        Help: "Number of pending updates by severity",
    }, []string{"severity"})

    updateDuration = promauto.NewHistogramVec(prometheus.HistogramOpts{
        Name: "aggregator_update_duration_seconds",
        Help: "Update installation duration",
    }, []string{"package_type", "result"})
)

// Update metrics
func (s *Server) UpdateMetrics() {
    agentsOnline.Set(float64(s.db.CountOnlineAgents()))

    for _, severity := range []string{"critical", "important", "moderate", "low"} {
        count := s.db.CountPendingUpdates(severity)
        updatesPending.WithLabelValues(severity).Set(float64(count))
    }
}

Health Checks

// /health endpoint
func (s *Server) HealthHandler(c *gin.Context) {
    health := Health{
        Status: "healthy",
        Checks: map[string]CheckResult{},
    }

    // Database check
    if err := s.db.Ping(); err != nil {
        health.Checks["database"] = CheckResult{Status: "unhealthy", Error: err.Error()}
        health.Status = "unhealthy"
    } else {
        health.Checks["database"] = CheckResult{Status: "healthy"}
    }

    // AI engine check (if enabled)
    if s.config.AI.Enabled {
        if err := s.ai.Ping(); err != nil {
            health.Checks["ai"] = CheckResult{Status: "degraded", Error: err.Error()}
            health.Status = "degraded"
        } else {
            health.Checks["ai"] = CheckResult{Status: "healthy"}
        }
    }

    statusCode := 200
    if health.Status == "unhealthy" {
        statusCode = 503
    }

    c.JSON(statusCode, health)
}

Documentation
API Documentation (OpenAPI 3.0)

Generated automatically from code annotations:

// @Summary Get all agents
// @Description Returns a list of all registered agents
// @Tags agents
// @Accept json
// @Produce json
// @Param status query string false "Filter by status" Enums(online, offline, error)
// @Param os_type query string false "Filter by OS type" Enums(windows, linux, macos)
// @Success 200 {array} Agent
// @Failure 401 {object} ErrorResponse
// @Router /agents [get]
// @Security BearerAuth
func (s *Server) ListAgents(c *gin.Context) {
    // Implementation
}

User Documentation

    Quick Start Guide - Get running in 10 minutes
    Installation Guides - Per-platform instructions
    Configuration Reference - All config options explained
    API Reference - Auto-generated from OpenAPI spec
    Troubleshooting - Common issues and solutions
    Best Practices - Security, performance, workflows

Developer Documentation

    Architecture Overview - System design
    Contributing Guide - How to contribute
    Database Schema - Entity relationships
    API Design Patterns - Conventions used
    Testing Guide - How to write tests

License & Community
License: AGPLv3

Why AGPLv3?

    Ensures modifications stay open source
    Prevents proprietary SaaS forks without contribution
    Aligns with "seize the means of production" ethos
    Allows commercial use with attribution
    Forces cloud providers to contribute back

Commercial Licensing Available: For organizations that cannot comply with AGPL (want proprietary modifications), commercial licenses available.
Community

GitHub Organization: github.com/aggregator-project

Repositories:

    aggregator-server - Backend API
    aggregator-agent - Cross-platform agent
    aggregator-web - React dashboard
    aggregator-cli - CLI tool
    aggregator-docs - Documentation site
    community-scripts - User-contributed scripts

Communication:

    Discord: Primary community chat
    GitHub Discussions: Feature requests, Q&A
    Reddit: r/aggregator (announcements, showcases)
    Twitter: @aggregator_dev

Contributing: We welcome contributions! See CONTRIBUTING.md for:

    Code of conduct
    Development setup
    Pull request process
    Style guides
    Testing requirements

FAQ for Fresh Claude Instance
Q: What problem does Aggregator solve?

A: Selfhosters and small IT teams have no good way to see ALL pending updates (Windows, Linux, Docker) in one place and actually install them on a schedule. Existing tools are either:

    Commercial SaaS only (ConnectWise, NinjaOne)
    Detection-only (Wazuh)
    Too complex (Foreman/Katello)
    Single-platform (Watchtower = Docker only)

Aggregator is the missing open-source, self-hosted, beautiful update management dashboard.
Q: What makes this "AI-native"?

A: Three things:

    Architecture: Every data model is designed for AI consumption (structured JSON, self-documenting names, rich context)
    API-first: AI can do anything a human can via REST API
    Natural language interface: Users can ask "Show me critical updates" instead of clicking filters

BUT - the AI is supplementary. The primary app is a traditional, information-dense dashboard. AI slides in from the right when needed.
Q: What's the tech stack?

A:

    Server: Go (fast, single binary, excellent for agents)
    Agent: Go (cross-platform, easy distribution)
    Database: PostgreSQL (proven, JSON support, great for this use case)
    Web: React + TypeScript + TailwindCSS
    AI: Pluggable (Ollama for local, OpenAI/Anthropic for cloud)
    Deployment: Docker Compose (dev), Kubernetes (prod)

Q: How does agent-server communication work?

A: Pull-based model:

    Agent registers with server (gets ID + JWT token)
    Agent polls server every 5 minutes: "Any commands for me?"
    Server responds with commands: "Scan for updates"
    Agent executes, reports results back
    Server stores updates in database
    Web dashboard shows updates in real-time (WebSocket)

Q: What's the MVP scope?

A: Phase 1 (Months 1-3):

    Windows agent (Windows Update + Winget + Docker)
    Linux agent (apt + Docker)
    Server API (agents, updates, logs)
    Web dashboard (view, approve, schedule)
    Basic execution (install approved updates)

No AI, no maintenance windows, no Mac support - just the core loop working.
Q: How do updates get installed?

A:

    User sees pending update in dashboard
    Clicks "Approve" (or bulk approves critical updates)
    Optionally schedules for specific time
    Server stores approval in database
    Agent polls, sees approved update in next check-in
    Agent downloads and installs update
    Agent reports success/failure back to server
    Dashboard shows real-time status

Q: What about rollbacks?

A: Phase 2 feature:

    Before update: Agent creates system restore point (Windows) or snapshot (Linux/Proxmox)
    If update fails: Agent can rollback to snapshot
    If user manually triggers: API endpoint /updates/{id}/rollback

Q: How does AI scheduling work?

A: AI considers:

    Update severity (critical first)
    Agent workload patterns (learned from history)
    Dependency chains (OS before apps)
    Historical failure rates per agent/package
    Business hours vs. maintenance windows
    Reboot requirements (group reboots together)

Output: Optimal schedule with confidence score and risks identified.
Q: What's the data flow for a Windows Update?

A:

1. Agent calls Windows Update COM API
2. Gets list of available updates with KB IDs, CVEs
3. Serializes to JSON, sends to server
4. Server stores in update_packages table
5. Web dashboard queries database, shows in UI
6. User approves update
7. Server marks status='approved' in database
8. Agent polls, sees approved update
9. Agent calls Windows Update API to install
10. Agent reports logs back to server
11. Dashboard shows success ✅

Q: How do we prevent agents from being compromised?

A:

    Agents only pull commands (never accept unsolicited commands)
    JWT tokens with 24h expiry, auto-rotation
    TLS-only communication (no plaintext)
    Command whitelist (only predefined commands allowed)
    Update validation (server verifies update is approved before sending install command)
    Audit logging (every action logged with timestamp + user)

Q: What if the server is down?

A:

    Agents cache last known state locally
    Continue checking in (with exponential backoff)
    When server returns, sync state
    Agents can operate in "offline mode" (execute pre-approved schedules)

Q: How does Docker update detection work?

A:

1. Agent lists all containers via Docker API
2. For each container:
   - Get current image (e.g., nginx:1.25.3)
   - Query registry API for latest tag
   - Compare digests (sha256 hashes)
3. If digest differs → update available
4. Report to server with: current_tag, latest_tag, registry

Q: What's the database size like?

A: Estimates for 100 agents:

    Agents table: ~100 rows × 1KB = 100KB
    Update packages (7 days retention): ~100 agents × 50 updates × 1KB = 5MB
    Logs (30 days retention): ~1000 updates/day × 5KB = 150MB
    Total: ~155MB for 100 agents

Scales linearly. At 1000 agents: ~1.5GB.
Q: Can I use this in production?

A:

    Phase 1 (MVP): Homelab, testing environments only
    Phase 2 (Feature complete): Yes, production-ready
    Phase 3 (AI): Production + intelligent automation
    Phase 4 (Enterprise): Large-scale production deployments

Q: How do I contribute?

A:

    Check GitHub Issues for "good first issue" label
    Fork repo, create feature branch
    Write code + tests (maintain 80%+ coverage)
    Open PR with description of changes
    Respond to review feedback
    Merge! 🎉

Areas needing help:

    Package manager scanners (snap, flatpak, chocolatey)
    UI/UX improvements
    Documentation
    Testing on various OSes
    Translations

Q: What's the "seize the means of production" joke about?

A: The project's tongue-in-cheek communist theming:

    Updates are "means of production" (they produce secure systems)
    Commercial RMMs are "capitalist tools" (expensive, SaaS-only)
    Aggregator "seizes" control back to the user (self-hosted, free)
    Project name options played on this (UpdateSoviet, RedFlag, etc.)

But ultimately: It's a serious tool with a playful brand. The name "Aggregator" works standalone without the context.
Quick Start for Development
1. Clone Repositories

git clone https://github.com/aggregator-project/aggregator-server.git
git clone https://github.com/aggregator-project/aggregator-agent.git
git clone https://github.com/aggregator-project/aggregator-web.git

2. Start Dependencies

# PostgreSQL + Ollama (optional)
docker-compose up -d

3. Run Server

cd aggregator-server
cp .env.example .env
# Edit .env with database URL
go run cmd/server/main.go

4. Run Web Dashboard

cd aggregator-web
npm install
npm run dev
# Open http://localhost:3000

5. Install Agent (Local Testing)

cd aggregator-agent
go build -o aggregator-agent cmd/agent/main.go

# Create config
cat > config.json <<EOF
{
  "server_url": "http://localhost:8080",
  "check_in_interval": 30
}
EOF

# Register and run
./aggregator-agent register
./aggregator-agent service

6. Verify It Works

    Visit http://localhost:3000
    Login (default: admin/admin)
    See your agent appear in Agents list
    Trigger scan: curl -X POST http://localhost:8080/api/v1/agents/{id}/scan
    See updates appear in dashboard

Code Examples
Example: Windows Update Scanner (Agent)

package scanner

import (
    "github.com/go-ole/go-ole"
    "github.com/go-ole/go-ole/oleutil"
)

type WindowsUpdateScanner struct{}

type WindowsUpdate struct {
    Title            string
    KBID             string
    Description      string
    Severity         string
    RequiresReboot   bool
    SizeBytes        int64
    CVEs             []string
}

func (s *WindowsUpdateScanner) Scan() ([]WindowsUpdate, error) {
    ole.CoInitialize(0)
    defer ole.CoUninitialize()

    // Create update session
    unknown, _ := oleutil.CreateObject("Microsoft.Update.Session")
    session, _ := unknown.QueryInterface(ole.IID_IDispatch)
    defer session.Release()

    // Create update searcher
    searcherRaw, _ := oleutil.CallMethod(session, "CreateUpdateSearcher")
    searcher := searcherRaw.ToIDispatch()
    defer searcher.Release()

    // Search for updates
    resultRaw, _ := oleutil.CallMethod(searcher, "Search", "IsInstalled=0")
    result := resultRaw.ToIDispatch()
    defer result.Release()

    // Get update collection
    updatesRaw, _ := oleutil.GetProperty(result, "Updates")
    updates := updatesRaw.ToIDispatch()
    defer updates.Release()

    // Get count
    countRaw, _ := oleutil.GetProperty(updates, "Count")
    count := int(countRaw.Val)

    var windowsUpdates []WindowsUpdate

    for i := 0; i < count; i++ {
        itemRaw, _ := oleutil.GetProperty(updates, "Item", i)
        item := itemRaw.ToIDispatch()

        title, _ := oleutil.GetProperty(item, "Title")
        description, _ := oleutil.GetProperty(item, "Description")

        // Extract KB ID from title
        kbID := extractKBID(title.ToString())

        // Get severity
        severityRaw, _ := oleutil.GetProperty(item, "MsrcSeverity")
        severity := mapSeverity(severityRaw.ToString())

        // Check if reboot required
        rebootRaw, _ := oleutil.GetProperty(item, "RebootRequired")
        rebootRequired := rebootRaw.Value().(bool)

        // Get size
        sizeRaw, _ := oleutil.GetProperty(item, "MaxDownloadSize")
        size := sizeRaw.Val

        // Get CVEs from security bulletin
        cves := extractCVEs(description.ToString())

        windowsUpdates = append(windowsUpdates, WindowsUpdate{
            Title:          title.ToString(),
            KBID:           kbID,
            Description:    description.ToString(),
            Severity:       severity,
            RequiresReboot: rebootRequired,
            SizeBytes:      int64(size),
            CVEs:           cves,
        })

        item.Release()
    }

    return windowsUpdates, nil
}

func mapSeverity(msrcSeverity string) string {
    switch msrcSeverity {
    case "Critical":
        return "critical"
    case "Important":
        return "important"
    case "Moderate":
        return "moderate"
    case "Low":
        return "low"
    default:
        return "none"
    }
}

func extractKBID(title string) string {
    // Extract KB number from title like "2024-01 Cumulative Update (KB5034441)"
    re := regexp.MustCompile(`KB\d+`)
    match := re.FindString(title)
    return match
}

func extractCVEs(description string) []string {
    // Extract CVE IDs from description
    re := regexp.MustCompile(`CVE-\d{4}-\d+`)
    return re.FindAllString(description, -1)
}

Example: API Handler (Server)

package api

import (
    "net/http"
    "github.com/gin-gonic/gin"
    "github.com/google/uuid"
)

type UpdatesHandler struct {
    db *database.DB
    ws *websocket.Manager
}

// GET /api/v1/updates
func (h *UpdatesHandler) ListUpdates(c *gin.Context) {
    // Parse query params
    filters := ParseUpdateFilters(c)

    // Query database
    updates, total, err := h.db.ListUpdates(filters)
    if err != nil {
        c.JSON(500, gin.H{"error": err.Error()})
        return
    }

    c.JSON(200, gin.H{
        "updates": updates,
        "total": total,
        "page": filters.Page,
        "page_size": filters.PageSize,
    })
}

// POST /api/v1/updates/:id/approve
func (h *UpdatesHandler) ApproveUpdate(c *gin.Context) {
    // Parse ID
    updateID, err := uuid.Parse(c.Param("id"))
    if err != nil {
        c.JSON(400, gin.H{"error": "invalid update ID"})
        return
    }

    // Get current user from JWT
    userID := c.GetString("user_id")

    // Approve update
    err = h.db.ApproveUpdate(updateID, userID)
    if err != nil {
        c.JSON(500, gin.H{"error": err.Error()})
        return
    }

    // Get updated record
    update, _ := h.db.GetUpdate(updateID)

    // Broadcast to WebSocket clients
    h.ws.Broadcast(websocket.Event{
        Type: "update_approved",
        Data: update,
    })

    c.JSON(200, gin.H{
        "message": "Update approved",
        "update": update,
    })
}

// POST /api/v1/updates/bulk/approve
func (h *UpdatesHandler) BulkApproveUpdates(c *gin.Context) {
    var req struct {
        UpdateIDs []uuid.UUID `json:"update_ids" binding:"required"`
    }

    if err := c.ShouldBindJSON(&req); err != nil {
        c.JSON(400, gin.H{"error": err.Error()})
        return
    }

    userID := c.GetString("user_id")

    // Approve all updates in transaction
    err := h.db.BulkApproveUpdates(req.UpdateIDs, userID)
    if err != nil {
        c.JSON(500, gin.H{"error": err.Error()})
        return
    }

    c.JSON(200, gin.H{
        "message": "Updates approved",
        "count": len(req.UpdateIDs),
    })
}

Example: React Component (Web)

// UpdatesTable.tsx
import { useQuery, useMutation, useQueryClient } from '@tanstack/react-query';
import { useState } from 'react';
import { api } from '@/api/client';

interface Update {
  id: string;
  agent_id: string;
  package_name: string;
  package_type: string;
  current_version: string;
  available_version: string;
  severity: 'critical' | 'important' | 'moderate' | 'low';
  status: string;
  cve_list: string[];
}

export function UpdatesTable() {
  const [filters, setFilters] = useState({
    severity: 'all',
    status: 'pending',
    package_type: 'all',
  });

  const [selectedUpdates, setSelectedUpdates] = useState<Set<string>>(new Set());
  const queryClient = useQueryClient();

  // Fetch updates
  const { data, isLoading } = useQuery({
    queryKey: ['updates', filters],
    queryFn: () => api.listUpdates(filters),
  });

  // Approve mutation
  const approveMutation = useMutation({
    mutationFn: (updateIds: string[]) => api.bulkApproveUpdates(updateIds),
    onSuccess: () => {
      queryClient.invalidateQueries({ queryKey: ['updates'] });
      setSelectedUpdates(new Set());
      toast.success('Updates approved successfully');
    },
  });

  const handleSelectAll = (checked: boolean) => {
    if (checked) {
      const allIds = new Set(data?.updates.map(u => u.id) || []);
      setSelectedUpdates(allIds);
    } else {
      setSelectedUpdates(new Set());
    }
  };

  const handleApproveSelected = () => {
    if (selectedUpdates.size === 0) return;
    approveMutation.mutate(Array.from(selectedUpdates));
  };

  if (isLoading) return <LoadingSpinner />;

  return (
    <div className="space-y-4">
      {/* Filters */}
      <div className="flex gap-4">
        <select
          value={filters.severity}
          onChange={(e) => setFilters({...filters, severity: e.target.value})}
          className="rounded border px-3 py-2"
        >
          <option value="all">All Severities</option>
          <option value="critical">Critical</option>
          <option value="important">Important</option>
          <option value="moderate">Moderate</option>
          <option value="low">Low</option>
        </select>

        <select
          value={filters.package_type}
          onChange={(e) => setFilters({...filters, package_type: e.target.value})}
          className="rounded border px-3 py-2"
        >
          <option value="all">All Types</option>
          <option value="windows_update">Windows Update</option>
          <option value="winget">Winget</option>
          <option value="apt">APT</option>
          <option value="docker_image">Docker</option>
        </select>
      </div>

      {/* Actions */}
      <div className="flex gap-2">
        <button
          onClick={handleApproveSelected}
          disabled={selectedUpdates.size === 0}
          className="rounded bg-blue-600 px-4 py-2 text-white disabled:opacity-50"
        >
          Approve ({selectedUpdates.size})
        </button>
      </div>

      {/* Table */}
      <table className="w-full">
        <thead>
          <tr className="border-b bg-gray-50">
            <th className="p-2">
              <input
                type="checkbox"
                checked={selectedUpdates.size === data?.updates.length}
                onChange={(e) => handleSelectAll(e.target.checked)}
              />
            </th>
            <th className="p-2 text-left">Severity</th>
            <th className="p-2 text-left">Agent</th>
            <th className="p-2 text-left">Package</th>
            <th className="p-2 text-left">Version</th>
            <th className="p-2 text-left">CVEs</th>
          </tr>
        </thead>
        <tbody>
          {data?.updates.map((update) => (
            <tr key={update.id} className="border-b hover:bg-gray-50">
              <td className="p-2">
                <input
                  type="checkbox"
                  checked={selectedUpdates.has(update.id)}
                  onChange={(e) => {
                    const newSelected = new Set(selectedUpdates);
                    if (e.target.checked) {
                      newSelected.add(update.id);
                    } else {
                      newSelected.delete(update.id);
                    }
                    setSelectedUpdates(newSelected);
                  }}
                />
              </td>
              <td className="p-2">
                <span className={`rounded px-2 py-1 text-xs ${getSeverityColor(update.severity)}`}>
                  {update.severity.toUpperCase()}
                </span>
              </td>
              <td className="p-2">{getAgentHostname(update.agent_id)}</td>
              <td className="p-2">
                <div className="font-medium">{update.package_name}</div>
                <div className="text-sm text-gray-500">{update.package_type}</div>
              </td>
              <td className="p-2">
                <div>{update.current_version} → {update.available_version}</div>
              </td>
              <td className="p-2">
                {update.cve_list.length > 0 ? (
                  <div className="text-sm">{update.cve_list.join(', ')}</div>
                ) : (
                  <span className="text-gray-400">None</span>
                )}
              </td>
            </tr>
          ))}
        </tbody>
      </table>
    </div>
  );
}

function getSeverityColor(severity: string) {
  switch (severity) {
    case 'critical': return 'bg-red-100 text-red-800';
    case 'important': return 'bg-orange-100 text-orange-800';
    case 'moderate': return 'bg-yellow-100 text-yellow-800';
    case 'low': return 'bg-green-100 text-green-800';
    default: return 'bg-gray-100 text-gray-800';
  }
}

Summary

Aggregator is a self-hosted, cross-platform update management platform that provides:

✅ Single pane of glass for all updates (Windows, Linux, Mac, Docker)
✅ Actionable intelligence (don't just see vulnerabilities—fix them)
✅ Beautiful, information-dense UI (inspired by Grafana)
✅ AI-assisted (natural language queries, intelligent scheduling)
✅ Open source (AGPLv3, community-driven)
✅ Self-hosted (your data, your infrastructure)

Target users: Selfhosters, homelabbers, small IT teams, MSPs

Not a competitor to: Enterprise RMMs (ConnectWise, NinjaOne), Security platforms (Wazuh)

Fills the gap between: Manual server-by-server updates and expensive commercial RMMs
Start Building!

Everything a fresh Claude instance needs is now documented. The architecture is sound, the data models are defined, the API is specified, and the development roadmap is clear.

Next concrete steps:

    Initialize GitHub repos
    Set up CI/CD (GitHub Actions)
    Create database migrations
    Build Windows Update scanner (agent)
    Build API endpoints (server)
    Create React dashboard skeleton
    Wire it all together
    Deploy to test environment
    Gather community feedback
    Iterate!

Go forth and aggregate! 🚩