RMM/murkup.txt

users {
  id uuid pk
  username text unique
  password_hash text
  role user_role
  is_active boolean
  created_at timestamptz
  updated_at timestamptz

  @index idx_users_role (role)
  @index idx_users_active (is_active)
}

agents {
  id uuid pk
  hostname text unique
  token_hash text
  cert_fingerprint text
  os_info jsonb
  status enum
  is_active boolean
  last_seen timestamptz
  registered_at timestamptz
  updated_at timestamptz

  @index idx_agents_status (status)
  @index idx_agents_last_seen (last_seen)
  @index idx_agents_active (is_active)
  @index idx_agents_os_info_gin (os_info)
}

command_types {
  id smallserial pk
  name text unique
}

commands {
  id uuid pk
  agent_id uuid > agents.id
  issued_by uuid > users.id
  command_type_id smallint > command_types.id
  command text
  status enum
  output text
  exit_code integer
  created_at timestamptz
  completed_at timestamptz
  updated_at timestamptz

  @index idx_commands_agent (agent_id)
  @index idx_commands_issued_by (issued_by)
  @index idx_commands_status (status)
  @index idx_commands_created_at (created_at)
}

file_transfers {
  id uuid pk
  agent_id uuid > agents.id
  initiated_by uuid > users.id
  command_id uuid > commands.id
  direction transfer_direction
  filename text
  sha256 char(64)
  size_bytes bigint
  status enum
  created_at timestamptz
  updated_at timestamptz

  @index idx_file_transfers_agent (agent_id)
  @index idx_file_transfers_status (status)
  @index idx_file_transfers_sha256 (sha256)
  @index idx_file_transfers_created (created_at)
}

audit_logs {
  id uuid pk
  actor_id uuid
  actor_type enum
  action text
  target text
  details jsonb
  ip_address inet
  created_at timestamptz

  @index idx_audit_actor (actor_id)
  @index idx_audit_action (action)
  @index idx_audit_created (created_at)
  @index idx_audit_details_gin (details)
}

agent_heartbeats {
  id bigserial pk
  agent_id uuid > agents.id
  received_at timestamptz
  metadata jsonb

  @index idx_heartbeats_agent (agent_id)
  @index idx_heartbeats_received (received_at)
}

user_sessions {
  id uuid pk
  user_id uuid > users.id
  refresh_token_hash text
  user_agent text
  ip_address inet
  expires_at timestamptz
  created_at timestamptz

  @index idx_sessions_user (user_id)
  @index idx_sessions_expires (expires_at)
}

agent_groups {
  id uuid pk
  name text unique
  description text
  created_at timestamptz
}

agent_group_members {
  agent_id uuid pk > agents.id
  group_id uuid pk > agent_groups.id
}