claudetools/projects/msp-tools/guru-rmm/server/migrations/004_agent_updates.sql

-- Agent update tracking
-- Tracks update commands sent to agents and their results

CREATE TABLE agent_updates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    agent_id UUID NOT NULL REFERENCES agents(id) ON DELETE CASCADE,
    update_id UUID NOT NULL UNIQUE,
    old_version VARCHAR(50) NOT NULL,
    target_version VARCHAR(50) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',  -- pending, downloading, installing, completed, failed, rolled_back
    download_url TEXT,
    checksum_sha256 VARCHAR(64),
    started_at TIMESTAMPTZ DEFAULT NOW(),
    completed_at TIMESTAMPTZ,
    error_message TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for finding updates by agent
CREATE INDEX idx_agent_updates_agent ON agent_updates(agent_id);

-- Index for finding updates by status (for monitoring)
CREATE INDEX idx_agent_updates_status ON agent_updates(status);

-- Index for finding pending/in-progress updates (for timeout detection)
CREATE INDEX idx_agent_updates_pending ON agent_updates(agent_id, status)
    WHERE status IN ('pending', 'downloading', 'installing');

-- Add architecture column to agents table for proper binary matching
ALTER TABLE agents ADD COLUMN IF NOT EXISTS architecture VARCHAR(20) DEFAULT 'amd64';