RelationalDesign: ddl_sql.sql

PRAGMA foreign_keys = ON;

-- Drop child tables first
DROP TABLE IF EXISTS security_alerts;
DROP TABLE IF EXISTS network_connections;
DROP TABLE IF EXISTS sysmon_events;
DROP TABLE IF EXISTS computer_processes;
DROP TABLE IF EXISTS computer_history;
DROP TABLE IF EXISTS computers;

DROP TABLE IF EXISTS admin_sessions;
DROP TABLE IF EXISTS env_tokens;
DROP TABLE IF EXISTS environments;

DROP TABLE IF EXISTS memberships;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS tenants;


CREATE TABLE tenants (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  owner_email TEXT NOT NULL,
  created_at TEXT NOT NULL
);

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT NOT NULL UNIQUE,
  name TEXT,
  picture TEXT,
  created_at TEXT NOT NULL
);

CREATE TABLE memberships (
  user_id INTEGER NOT NULL,
  tenant_id INTEGER NOT NULL,
  role TEXT NOT NULL DEFAULT 'admin',
  created_at TEXT NOT NULL,
  PRIMARY KEY (user_id, tenant_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);


CREATE TABLE environments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  created_at TEXT NOT NULL,
  tenant_id INTEGER NOT NULL,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  UNIQUE (tenant_id, name)
);

CREATE TABLE env_tokens (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  env_name TEXT NOT NULL,
  token TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL,
  expires_at TEXT,
  tenant_id INTEGER NOT NULL,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
  -- (env_name is "soft FK" because SQLite cannot FK to a non-unique column across tenants)
);

CREATE TABLE admin_sessions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  token TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL,
  tenant_id INTEGER NOT NULL,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);


CREATE TABLE computers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  user TEXT,
  ip TEXT,
  os TEXT,
  first_seen TIMESTAMP,
  last_seen TIMESTAMP,
  sysmon_available BOOLEAN DEFAULT 0,
  env_name TEXT NOT NULL DEFAULT 'default',
  tenant_id INTEGER NOT NULL,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  UNIQUE (tenant_id, name)
);

CREATE TABLE computer_history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  computer_id INTEGER NOT NULL,
  cpu_usage REAL,
  ram_usage REAL,
  disk_usage REAL,
  network_sent_mb REAL,
  network_recv_mb REAL,
  timestamp TIMESTAMP,
  FOREIGN KEY (computer_id) REFERENCES computers(id) ON DELETE CASCADE
);

CREATE TABLE computer_processes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  computer_id INTEGER NOT NULL,
  pid INTEGER,
  name TEXT,
  cpu_percent REAL,
  memory_mb REAL,
  username TEXT,
  cmdline TEXT,
  timestamp TIMESTAMP,
  FOREIGN KEY (computer_id) REFERENCES computers(id) ON DELETE CASCADE
);

CREATE TABLE sysmon_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  computer_id INTEGER NOT NULL,
  event_id INTEGER,
  event_type TEXT,
  message TEXT,
  timestamp TIMESTAMP,
  details TEXT,
  FOREIGN KEY (computer_id) REFERENCES computers(id) ON DELETE CASCADE
);

CREATE TABLE network_connections (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  computer_id INTEGER NOT NULL,
  pid INTEGER,
  local_address TEXT,
  remote_address TEXT,
  status TEXT,
  process_name TEXT,
  timestamp TIMESTAMP,
  FOREIGN KEY (computer_id) REFERENCES computers(id) ON DELETE CASCADE
);

CREATE TABLE security_alerts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  computer_id INTEGER NOT NULL,
  alert_type TEXT,
  severity TEXT,
  description TEXT,
  timestamp TIMESTAMP,
  resolved BOOLEAN DEFAULT 0,
  FOREIGN KEY (computer_id) REFERENCES computers(id) ON DELETE CASCADE
);