stackpulse/backend/db/dbs

-- Stackpulse database schema blueprint
-- This document captures the expected structure of all tables, indexes and core seed requirements.

CREATE TABLE event_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
  category TEXT NOT NULL,
  event_type TEXT,
  action TEXT,
  status TEXT,
  severity TEXT,
  entity_type TEXT,
  entity_id TEXT,
  entity_name TEXT,
  actor_type TEXT,
  actor_id TEXT,
  actor_name TEXT,
  source TEXT,
  context_type TEXT,
  context_id TEXT,
  context_label TEXT,
  message TEXT,
  metadata TEXT
);
CREATE INDEX idx_event_logs_timestamp ON event_logs (timestamp DESC);
CREATE INDEX idx_event_logs_category ON event_logs (category);
CREATE INDEX idx_event_logs_event_type ON event_logs (event_type);
CREATE INDEX idx_event_logs_entity ON event_logs (entity_type, entity_id);
CREATE INDEX idx_event_logs_context ON event_logs (context_type, context_id);

CREATE TABLE settings (
  key TEXT PRIMARY KEY,
  value TEXT,
  updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL UNIQUE,
  email TEXT UNIQUE,
  password_hash TEXT NOT NULL,
  password_salt TEXT,
  avatar_color TEXT,
  is_active INTEGER NOT NULL DEFAULT 1,
  last_login DATETIME,
  security_phrase_content TEXT,
  security_phrase_iv TEXT,
  security_phrase_tag TEXT,
  security_phrase_downloaded_at DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_groups (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  description TEXT,
  avatar_color TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_group_memberships (
  user_id INTEGER NOT NULL,
  group_id INTEGER NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, group_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (group_id) REFERENCES user_groups(id) ON DELETE CASCADE
);

CREATE TABLE permissions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  key TEXT NOT NULL UNIQUE,
  description TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_group_permissions (
  group_id INTEGER NOT NULL,
  permission_id INTEGER NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (group_id, permission_id),
  FOREIGN KEY (group_id) REFERENCES user_groups(id) ON DELETE CASCADE,
  FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

CREATE TABLE permission_sections (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  key TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL,
  sort_order INTEGER NOT NULL DEFAULT 0,
  has_navigation_flag INTEGER NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE permission_groups (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  section_id INTEGER NOT NULL,
  key TEXT NOT NULL,
  title TEXT NOT NULL,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(section_id, key),
  FOREIGN KEY (section_id) REFERENCES permission_sections(id) ON DELETE CASCADE
);

CREATE TABLE permission_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  section_id INTEGER NOT NULL,
  group_id INTEGER,
  key TEXT NOT NULL UNIQUE,
  label TEXT NOT NULL,
  sort_order INTEGER NOT NULL DEFAULT 0,
  default_level TEXT NOT NULL,
  available_levels TEXT NOT NULL,
  is_required INTEGER NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (section_id) REFERENCES permission_sections(id) ON DELETE CASCADE,
  FOREIGN KEY (group_id) REFERENCES permission_groups(id) ON DELETE SET NULL
);

CREATE TABLE permission_dependencies (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  permission_id INTEGER NOT NULL,
  depends_on_permission_id INTEGER NOT NULL,
  required_level TEXT DEFAULT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (permission_id) REFERENCES permission_items(id) ON DELETE CASCADE,
  FOREIGN KEY (depends_on_permission_id) REFERENCES permission_items(id) ON DELETE CASCADE,
  UNIQUE(permission_id, depends_on_permission_id)
);
CREATE INDEX idx_permission_dependencies_required
  ON permission_dependencies (permission_id, depends_on_permission_id);

CREATE TABLE group_permission_values (
  group_id INTEGER NOT NULL,
  permission_id INTEGER NOT NULL,
  level TEXT NOT NULL,
  effective_level TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (group_id, permission_id),
  FOREIGN KEY (group_id) REFERENCES user_groups(id) ON DELETE CASCADE,
  FOREIGN KEY (permission_id) REFERENCES permission_items(id) ON DELETE CASCADE
);

CREATE TABLE servers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  url TEXT NOT NULL UNIQUE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_server_permission_overrides (
  user_id INTEGER NOT NULL,
  server_id INTEGER NOT NULL,
  permission_id INTEGER NOT NULL,
  change_type TEXT NOT NULL CHECK (change_type IN ('ADD', 'REMOVE')),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (user_id, server_id, permission_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (server_id) REFERENCES servers(id) ON DELETE CASCADE,
  FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);
CREATE INDEX idx_user_server_permission_overrides_user_server
  ON user_server_permission_overrides (user_id, server_id);

CREATE TABLE server_api_keys (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  server_id INTEGER NOT NULL UNIQUE,
  key_cipher TEXT NOT NULL,
  key_iv TEXT NOT NULL,
  key_tag TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (server_id) REFERENCES servers(id) ON DELETE CASCADE
);

CREATE TABLE user_settings (
  user_id INTEGER NOT NULL,
  setting_key TEXT NOT NULL,
  setting_value TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, setting_key),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Core seed expectations (keys only – maintained via schemaEnsure)
-- permission_sections: stacks, logs, users, user-groups, maintenance
-- permission_items: includes users-security-phrase (available_levels ["full","none"])