wulf-pulse/migrations/038_create_sentinelone_tables.sql

-- SentinelOne Tables
-- Prefixed with s1_ to identify data source

-- Sync history
CREATE TABLE IF NOT EXISTS s1_sync_history (
  id              SERIAL PRIMARY KEY,
  sync_type       VARCHAR(50) NOT NULL DEFAULT 'full',
  status          VARCHAR(20) NOT NULL DEFAULT 'running',
  triggered_by    VARCHAR(100) NOT NULL DEFAULT 'system',
  started_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  completed_at    TIMESTAMP,
  duration_ms     INTEGER,
  total_upserted  INTEGER DEFAULT 0,
  error_message   TEXT,
  entity_results  JSONB DEFAULT '[]'
);

-- Sites (one per client/tenant in S1)
CREATE TABLE IF NOT EXISTS s1_sites (
  id                      VARCHAR(50) PRIMARY KEY,
  account_id              VARCHAR(50),
  account_name            VARCHAR(255),
  name                    VARCHAR(255),
  site_type               VARCHAR(50),
  state                   VARCHAR(50),
  sku                     VARCHAR(100),
  suite                   VARCHAR(100),
  health_status           BOOLEAN,
  active_licenses         INTEGER DEFAULT 0,
  total_licenses          INTEGER DEFAULT 0,
  unlimited_licenses      BOOLEAN DEFAULT false,
  unlimited_expiration    BOOLEAN DEFAULT false,
  expiration              TIMESTAMP,
  is_default              BOOLEAN DEFAULT false,
  usage_type              VARCHAR(50),
  external_id             VARCHAR(255),
  registration_token      TEXT,
  description             TEXT,
  created_at              TIMESTAMP,
  updated_at              TIMESTAMP,
  synced_at               TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Agents (endpoints)
CREATE TABLE IF NOT EXISTS s1_agents (
  id                        VARCHAR(50) PRIMARY KEY,
  site_id                   VARCHAR(50),
  site_name                 VARCHAR(255),
  account_id                VARCHAR(50),
  account_name              VARCHAR(255),
  group_id                  VARCHAR(50),
  group_name                VARCHAR(255),
  computer_name             VARCHAR(255),
  domain                    VARCHAR(255),
  os_type                   VARCHAR(50),
  os_name                   VARCHAR(255),
  os_revision               VARCHAR(100),
  agent_version             VARCHAR(50),
  machine_type              VARCHAR(50),
  is_active                 BOOLEAN DEFAULT false,
  is_decommissioned         BOOLEAN DEFAULT false,
  is_up_to_date             BOOLEAN DEFAULT false,
  is_pending_uninstall      BOOLEAN DEFAULT false,
  is_uninstalled            BOOLEAN DEFAULT false,
  infected                  BOOLEAN DEFAULT false,
  active_threats            INTEGER DEFAULT 0,
  network_status            VARCHAR(50),
  mitigation_mode           VARCHAR(50),
  detection_state           VARCHAR(50),
  apps_vulnerability_status VARCHAR(50),
  firewall_enabled          BOOLEAN,
  external_ip               VARCHAR(50),
  last_active_date          TIMESTAMP,
  last_logged_in_user_name  VARCHAR(255),
  cpu_id                    VARCHAR(255),
  core_count                INTEGER,
  cpu_count                 INTEGER,
  total_memory              INTEGER,
  uuid                      VARCHAR(100),
  external_id               VARCHAR(255),
  installer_type            VARCHAR(20),
  scan_status               VARCHAR(50),
  scan_started_at           TIMESTAMP,
  scan_finished_at          TIMESTAMP,
  created_at                TIMESTAMP,
  updated_at                TIMESTAMP,
  synced_at                 TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Threats
CREATE TABLE IF NOT EXISTS s1_threats (
  id                        VARCHAR(50) PRIMARY KEY,
  site_id                   VARCHAR(50),
  site_name                 VARCHAR(255),
  account_id                VARCHAR(50),
  agent_id                  VARCHAR(50),
  agent_computer_name       VARCHAR(255),
  agent_os_name             VARCHAR(255),
  agent_version             VARCHAR(50),
  agent_is_active           BOOLEAN,
  agent_is_decommissioned   BOOLEAN,
  threat_name               VARCHAR(500),
  threat_file_path          TEXT,
  threat_file_sha256        VARCHAR(100),
  classification            VARCHAR(100),
  classification_source     VARCHAR(100),
  confidence_level          VARCHAR(50),
  mitigation_status         VARCHAR(50),
  mitigation_report         JSONB,
  analyst_verdict           VARCHAR(50),
  incident_status           VARCHAR(50),
  detection_engines         JSONB,
  indicators                JSONB,
  created_at                TIMESTAMP,
  updated_at                TIMESTAMP,
  synced_at                 TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Company mapping (S1 site → Autotask company)
CREATE TABLE IF NOT EXISTS s1_company_mappings (
  id              SERIAL PRIMARY KEY,
  s1_site_id      VARCHAR(50) NOT NULL UNIQUE,
  s1_site_name    VARCHAR(255) NOT NULL,
  company_id      INTEGER NOT NULL,
  company_name    VARCHAR(255),
  notes           TEXT,
  created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_s1_agents_site_id        ON s1_agents(site_id);
CREATE INDEX IF NOT EXISTS idx_s1_agents_is_active       ON s1_agents(is_active);
CREATE INDEX IF NOT EXISTS idx_s1_agents_infected        ON s1_agents(infected);
CREATE INDEX IF NOT EXISTS idx_s1_threats_site_id        ON s1_threats(site_id);
CREATE INDEX IF NOT EXISTS idx_s1_threats_agent_id       ON s1_threats(agent_id);
CREATE INDEX IF NOT EXISTS idx_s1_threats_mitigation     ON s1_threats(mitigation_status);
CREATE INDEX IF NOT EXISTS idx_s1_company_mappings_company ON s1_company_mappings(company_id);
CREATE INDEX IF NOT EXISTS idx_s1_sync_history_started   ON s1_sync_history(started_at DESC);