wulf-pulse/migrations/077_rmm_overshell.sql

-- Phase 4.2 — Datto RMM Overshell evidence pipeline.
--
-- Two tables:
--   1. rmm_settings — singleton row caching the discovered Overshell
--      component_uid + variable_name. Avoids re-discovering on every dispatch.
--   2. rmm_executions — full lifecycle of every Overshell job Pulse triggers.
--      Forever-retained: stdout/stderr (redacted), parsed evidence, audit
--      linkage, target/asset linkage.

CREATE TABLE IF NOT EXISTS rmm_settings (
  id                          BOOLEAN PRIMARY KEY DEFAULT true CHECK (id),
  overshell_component_uid     TEXT,
  overshell_component_name    TEXT,
  -- The variable name the Overshell component expects the script body in.
  -- Default 'CommandLine' matches the Datto-provided "Run Command" component;
  -- Wulf's custom Overshell may use a different name (e.g. 'Script') —
  -- adjustable via /admin/rmm-overshell.
  overshell_variable_name     TEXT NOT NULL DEFAULT 'CommandLine',
  discovered_at               TIMESTAMPTZ,
  updated_at                  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
INSERT INTO rmm_settings (id) VALUES (true) ON CONFLICT DO NOTHING;

CREATE TABLE IF NOT EXISTS rmm_executions (
  id                       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  -- Script library identifiers (script bodies are version-controlled in code).
  script_id                TEXT NOT NULL,
  script_version           INT NOT NULL DEFAULT 1,
  -- Target resolution.
  target_type              TEXT NOT NULL
                             CHECK (target_type IN ('site_anchor','asset_self')),
  target_device_uid        TEXT NOT NULL,
  target_hostname          TEXT,
  target_company_id        BIGINT,
  -- Optional links back to the audit / asset the user clicked from.
  triggered_by_audit_id    UUID REFERENCES itglue_asset_audits(id) ON DELETE SET NULL,
  asset_type               TEXT
                             CHECK (asset_type IS NULL OR asset_type IN ('flexible_asset','configuration')),
  asset_id                 BIGINT,
  -- Datto job tracking.
  job_uid                  TEXT,
  job_name                 TEXT NOT NULL,
  variables                JSONB NOT NULL,
  status                   TEXT NOT NULL DEFAULT 'queued'
                             CHECK (status IN ('queued','running','complete','failed','timeout')),
  exit_code                INT,
  raw_stdout               TEXT,
  raw_stderr               TEXT,
  parsed_evidence          JSONB,
  parse_error              TEXT,
  error_message            TEXT,
  performed_by_user_id     TEXT REFERENCES "user"(id) ON DELETE SET NULL,
  queued_at                TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  started_at               TIMESTAMPTZ,
  completed_at             TIMESTAMPTZ,
  -- Hard timeout marker. The poller will mark a still-running execution as
  -- timeout once NOW() > timeout_at and stop polling.
  timeout_at               TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '5 minutes'
);

CREATE INDEX IF NOT EXISTS ix_rmm_executions_status
  ON rmm_executions (status, queued_at);
CREATE INDEX IF NOT EXISTS ix_rmm_executions_target_device
  ON rmm_executions (target_device_uid, queued_at DESC);
CREATE INDEX IF NOT EXISTS ix_rmm_executions_company
  ON rmm_executions (target_company_id, queued_at DESC);
CREATE INDEX IF NOT EXISTS ix_rmm_executions_audit
  ON rmm_executions (triggered_by_audit_id);
CREATE INDEX IF NOT EXISTS ix_rmm_executions_asset
  ON rmm_executions (asset_type, asset_id, queued_at DESC)
  WHERE asset_type IS NOT NULL;

-- The audit-context loader pulls the latest successful execution per
-- (company, script). Partial index keeps the lookup fast even as the table
-- grows.
CREATE INDEX IF NOT EXISTS ix_rmm_executions_latest_success
  ON rmm_executions (target_company_id, script_id, completed_at DESC)
  WHERE status = 'complete';

CREATE INDEX IF NOT EXISTS ix_rmm_executions_user_window
  ON rmm_executions (performed_by_user_id, queued_at)
  WHERE performed_by_user_id IS NOT NULL;

COMMENT ON TABLE rmm_executions IS
  'Every Overshell job Pulse dispatched to Datto RMM. status: queued (we '
  'inserted the row but have not yet called runQuickJob), running (job_uid '
  'returned by Datto), complete (poller saw stdout), failed (exit_code != 0 '
  'or HTTP failure), timeout (still running past timeout_at).';