-- ジョブテーブル
CREATE TABLE IF NOT EXISTS jobs (
  id               TEXT PRIMARY KEY,
  repo             TEXT NOT NULL,
  issue_number     INTEGER NOT NULL,
  pr_number        INTEGER,
  status           TEXT NOT NULL DEFAULT 'queued'
                   CHECK (status IN ('queued', 'dispatching', 'running', 'succeeded', 'failed', 'retry', 'cancelled', 'waiting_human', 'waiting_subtasks')),
  piece_name       TEXT NOT NULL DEFAULT 'general',
  required_profile TEXT NOT NULL DEFAULT 'auto',
  task_class       TEXT NOT NULL DEFAULT 'auto',
  current_movement TEXT,
  current_activity TEXT,
  instruction      TEXT NOT NULL DEFAULT '',
  branch_name      TEXT,
  worktree_path    TEXT,
  attempt          INTEGER NOT NULL DEFAULT 1,
  max_attempts     INTEGER NOT NULL DEFAULT 3,
  next_retry_at    TEXT,
  error_summary    TEXT,
  abort_reason     TEXT,  -- structured abort code (e.g. 'max_iterations_exceeded', 'agent_self_abort'); see piece-runner / agent-loop
  resume_movement  TEXT,
  wait_reason      TEXT,              -- 'ask' | 'browser_login' | 'mcp_auth_required' | NULL
  ask_count        INTEGER NOT NULL DEFAULT 0,
  worker_id        TEXT,
  last_backend_id  TEXT,  -- physical backend id (LiteLLM deployment) for proxy workers; NULL for direct
  parent_job_id    TEXT,
  subtask_depth    INTEGER NOT NULL DEFAULT 0,
  context_prompt_tokens   INTEGER,
  context_limit_tokens    INTEGER,
  context_updated_at      TEXT,
  task_kind        TEXT NOT NULL DEFAULT 'agent',
  payload          TEXT,
  created_at       TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at       TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_jobs_status ON jobs (status);
CREATE INDEX IF NOT EXISTS idx_jobs_repo_issue ON jobs (repo, issue_number);

-- Local task テーブル
CREATE TABLE IF NOT EXISTS local_tasks (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  title         TEXT NOT NULL,
  body          TEXT NOT NULL DEFAULT '',
  piece_name    TEXT NOT NULL DEFAULT 'general',
  profile       TEXT NOT NULL DEFAULT 'auto',
  output_format TEXT NOT NULL DEFAULT 'markdown',
  ask_policy    TEXT NOT NULL DEFAULT 'low',
  priority      TEXT NOT NULL DEFAULT 'medium',
  state         TEXT NOT NULL DEFAULT 'open'
                CHECK (state IN ('open', 'closed')),
  workspace_path TEXT,
  created_at    TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at    TEXT NOT NULL DEFAULT (datetime('now')),
  feedback_rating  TEXT,
  feedback_comment TEXT,
  feedback_tags    TEXT,
  feedback_at      TEXT,
  share_token      TEXT,
  shared_at        TEXT,
  -- Mission Brief: per-task pinned memo carrying { goal, done, open, clarifications }
  -- as a single JSON blob. Always rendered at the top of every movement's system
  -- prompt. Editable by both the LLM (mission_update tool) and the user (UI).
  mission_brief    TEXT,
  -- Per-task options (JSON blob). Controls runtime behaviour toggles such as
  -- { mcpDisabled: true, skillsDisabled: true }. Default '{}' = all enabled.
  options          TEXT DEFAULT '{}'
);

CREATE INDEX IF NOT EXISTS idx_local_tasks_updated_at ON local_tasks (updated_at DESC);

-- Local task コメント / イベント
CREATE TABLE IF NOT EXISTS local_task_comments (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  task_id    INTEGER NOT NULL,
  author     TEXT NOT NULL,
  kind       TEXT NOT NULL DEFAULT 'comment',
  body       TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  injected_at TEXT,
  FOREIGN KEY (task_id) REFERENCES local_tasks(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_local_task_comments_task_id ON local_task_comments (task_id, created_at ASC);

-- Issue ロックテーブル (同一 Issue の並列実行を防ぐ)
CREATE TABLE IF NOT EXISTS issue_locks (
  repo         TEXT NOT NULL,
  issue_number INTEGER NOT NULL,
  job_id       TEXT NOT NULL,
  locked_at    TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (repo, issue_number)
);

-- 監査ログテーブル
CREATE TABLE IF NOT EXISTS audit_log (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  job_id     TEXT,
  action     TEXT NOT NULL,
  actor      TEXT,
  detail     TEXT,  -- JSON
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_audit_log_job_id ON audit_log (job_id);

-- Worker ノード状態
CREATE TABLE IF NOT EXISTS worker_nodes (
  worker_id        TEXT PRIMARY KEY,
  endpoint         TEXT NOT NULL,
  enabled          INTEGER NOT NULL DEFAULT 1,
  healthy          INTEGER NOT NULL DEFAULT 0,
  profile_tags     TEXT NOT NULL DEFAULT ',auto,',
  task_class_tags  TEXT NOT NULL DEFAULT ',auto,',
  max_concurrency  INTEGER NOT NULL DEFAULT 1,
  available_models TEXT,
  inflight_jobs    INTEGER NOT NULL DEFAULT 0,
  last_error       TEXT,
  last_seen_at     TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at       TEXT NOT NULL DEFAULT (datetime('now'))
);

-- スケジュール実行定義
CREATE TABLE IF NOT EXISTS scheduled_tasks (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  title           TEXT,
  body            TEXT NOT NULL,
  piece_name      TEXT NOT NULL DEFAULT 'auto',
  profile         TEXT NOT NULL DEFAULT 'auto',
  output_format   TEXT NOT NULL DEFAULT 'markdown',
  cron_expression TEXT NOT NULL,
  next_run_at     TEXT NOT NULL,
  last_run_at     TEXT,
  last_job_id     TEXT,
  is_active       INTEGER NOT NULL DEFAULT 1,
  task_kind       TEXT NOT NULL DEFAULT 'agent' CHECK (task_kind IN ('agent','script')),
  script_name     TEXT,
  script_params   TEXT, -- JSON-encoded object when task_kind = 'script'
  created_at      TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Auth: users
CREATE TABLE IF NOT EXISTS users (
  id         TEXT PRIMARY KEY,
  email      TEXT UNIQUE NOT NULL,
  name       TEXT,
  avatar_url TEXT,
  role       TEXT NOT NULL DEFAULT 'user',
  status     TEXT NOT NULL DEFAULT 'pending',
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Auth: OAuth account linkage
CREATE TABLE IF NOT EXISTS oauth_accounts (
  id          TEXT PRIMARY KEY,
  user_id     TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  provider    TEXT NOT NULL,
  provider_id TEXT NOT NULL,
  created_at  TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE(provider, provider_id)
);

-- Auth: sessions (express-session)
CREATE TABLE IF NOT EXISTS sessions (
  sid     TEXT PRIMARY KEY,
  sess    TEXT NOT NULL,
  expired TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sessions_expired ON sessions(expired);

-- ── Browser session persistence (2026-05) ─────────────────────────────
CREATE TABLE IF NOT EXISTS user_deks (
  user_id        TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  encrypted_dek  BLOB NOT NULL,
  created_at     TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS browser_session_profiles (
  id                   INTEGER PRIMARY KEY AUTOINCREMENT,
  owner_id             TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  label                TEXT NOT NULL,
  start_url            TEXT NOT NULL,
  match_patterns       TEXT NOT NULL DEFAULT '[]',
  storage_origins      TEXT NOT NULL DEFAULT '[]',
  logged_in_selector   TEXT,
  login_url_patterns   TEXT NOT NULL DEFAULT '[]',
  encrypted_state_blob BLOB,
  state_version        INTEGER NOT NULL DEFAULT 0,
  playwright_version   TEXT,
  status               TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','active','expired','revoked','error')),
  last_saved_at        TEXT,
  last_used_at         TEXT,
  last_validated_at    TEXT,
  last_error           TEXT,
  created_at           TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at           TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_bsp_owner ON browser_session_profiles(owner_id);

-- audit log: intentionally no FK — must survive deletion of referenced rows
CREATE TABLE IF NOT EXISTS browser_session_audit (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  ts              TEXT NOT NULL DEFAULT (datetime('now')),
  actor_user_id   TEXT,
  profile_id      INTEGER,
  owner_id        TEXT,
  action          TEXT NOT NULL CHECK (action IN ('create','save','decrypt','use','delete','expire','revoke','test','login_start','login_cancel')),
  task_id         INTEGER,
  job_id          TEXT,
  result          TEXT NOT NULL CHECK (result IN ('success','error')),
  reason          TEXT
);
CREATE INDEX IF NOT EXISTS idx_bsa_profile ON browser_session_audit(profile_id);
CREATE INDEX IF NOT EXISTS idx_bsa_actor ON browser_session_audit(actor_user_id);

-- ─── MCP (Model Context Protocol) ──────────────────────────────
-- Note: migrate.ts intentionally omits the FK clauses below for
-- in-flight DBs; see migrateMcpTables in src/db/migrate.ts.

-- Admin-registered MCP servers
CREATE TABLE IF NOT EXISTS mcp_servers (
  id                      TEXT PRIMARY KEY,
  name                    TEXT NOT NULL,
  url                     TEXT NOT NULL,
  oauth_client_id         TEXT NOT NULL,
  oauth_client_secret_enc BLOB NOT NULL,
  oauth_scopes            TEXT,
  issuer                  TEXT,
  authorization_endpoint  TEXT,
  token_endpoint          TEXT,
  discovery_fingerprint   TEXT,
  enabled                 INTEGER NOT NULL DEFAULT 1,
  created_by              TEXT,
  created_at              TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at              TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Per-user OAuth tokens
CREATE TABLE IF NOT EXISTS user_mcp_tokens (
  user_id            TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  server_id          TEXT NOT NULL REFERENCES mcp_servers(id) ON DELETE CASCADE,
  access_token_enc   BLOB NOT NULL,
  refresh_token_enc  BLOB,
  expires_at         TEXT,
  scope              TEXT,
  scope_type         TEXT NOT NULL DEFAULT 'user' CHECK(scope_type IN ('user', 'org')),
  scope_id           TEXT,
  connected_at       TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at         TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (user_id, server_id)
);

-- list_tools cache
CREATE TABLE IF NOT EXISTS mcp_server_tools (
  server_id    TEXT NOT NULL REFERENCES mcp_servers(id) ON DELETE CASCADE,
  tool_name    TEXT NOT NULL,
  description  TEXT,
  input_schema TEXT,
  refreshed_at TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (server_id, tool_name)
);

-- Pending OAuth state/verifier (single-use, TTL-cleaned)
CREATE TABLE IF NOT EXISTS mcp_oauth_pending (
  state         TEXT PRIMARY KEY,
  user_id       TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  server_id     TEXT NOT NULL REFERENCES mcp_servers(id) ON DELETE CASCADE,
  code_verifier TEXT NOT NULL,
  created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_mcp_oauth_pending_created ON mcp_oauth_pending(created_at);

-- ─── SSH (2026-05) ──────────────────────────────────────────
-- Plan: docs/superpowers/plans/2026-05-12-ssh-tool-integration.md
-- Feature gated by config.ssh.enabled; tables are inert when disabled.

-- System DEK for envelope-encrypting global connection keys.
-- Single-row table (CHECK id=1). Bootstrapped at boot when ssh.enabled.
-- Wrapped under MCP_ENCRYPTION_KEY env var (the SSH "master key").
CREATE TABLE IF NOT EXISTS system_deks (
  id            INTEGER PRIMARY KEY CHECK (id = 1),
  encrypted_dek BLOB NOT NULL,
  key_version   INTEGER NOT NULL DEFAULT 1,
  created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Per-user DEK for user-owned SSH connection keys.
-- Distinct from `user_deks` (used by browser-sessions under master.key file).
-- ssh_user_deks is wrapped under MCP_ENCRYPTION_KEY env var.
-- Created lazily on first SSH connection creation per user.
CREATE TABLE IF NOT EXISTS ssh_user_deks (
  user_id       TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  encrypted_dek BLOB NOT NULL,
  key_version   INTEGER NOT NULL DEFAULT 1,
  created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);

-- SSH connection records. Private key is envelope-encrypted (per-user DEK for
-- user-owned, system DEK for globals). Host key is TOFU with explicit UI verify.
-- Lock/abuse state lives in ssh_abuse_counters (single source of truth).
CREATE TABLE IF NOT EXISTS ssh_connections (
  id                            TEXT PRIMARY KEY,
  owner_id                      TEXT,
  label                         TEXT NOT NULL,
  host                          TEXT NOT NULL,
  port                          INTEGER NOT NULL DEFAULT 22,
  username                      TEXT NOT NULL,

  private_key_enc               BLOB NOT NULL,
  passphrase_enc                BLOB,
  key_version                   INTEGER NOT NULL DEFAULT 1,
  key_fingerprint               TEXT,

  host_key_type                 TEXT,
  host_key_b64                  TEXT,
  host_key_fingerprint          TEXT,
  host_key_recorded_at          TEXT,
  host_key_verified_at          TEXT,
  host_key_pending              INTEGER NOT NULL DEFAULT 0,
  host_key_pending_b64          TEXT,
  host_key_pending_fingerprint  TEXT,
  host_key_pending_token        TEXT,
  host_key_pending_source       TEXT,

  command_deny_patterns         TEXT,
  command_allow_patterns        TEXT,
  remote_path_prefix            TEXT NOT NULL CHECK (LENGTH(remote_path_prefix) > 0),
  allow_remote_unrestricted     INTEGER NOT NULL DEFAULT 0,
  allow_private_addresses       INTEGER NOT NULL DEFAULT 0,

  enabled                       INTEGER NOT NULL DEFAULT 1,
  disabled_by_admin             INTEGER NOT NULL DEFAULT 0,
  disabled_by_admin_reason      TEXT,
  disabled_by_admin_at          TEXT,
  disabled_by_admin_user_id     TEXT,

  created_at                    TEXT NOT NULL,
  updated_at                    TEXT NOT NULL,
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_ssh_connections_owner    ON ssh_connections(owner_id);
CREATE INDEX IF NOT EXISTS idx_ssh_connections_enabled  ON ssh_connections(enabled, disabled_by_admin);

-- Per-(user|org, piece) grants. Required for non-owner non-admin access to a
-- connection (typical case: granting access to a global connection).
-- applies_to_all_pieces=1 makes piece_name NULL and requires admin + reason.
CREATE TABLE IF NOT EXISTS ssh_connection_grants (
  id                       TEXT PRIMARY KEY,
  connection_id            TEXT NOT NULL REFERENCES ssh_connections(id) ON DELETE CASCADE,
  subject_type             TEXT NOT NULL CHECK (subject_type IN ('user','org')),
  subject_id               TEXT NOT NULL,
  piece_name               TEXT,
  applies_to_all_pieces    INTEGER NOT NULL DEFAULT 0,
  granted_by_user_id       TEXT NOT NULL,
  reason                   TEXT NOT NULL CHECK (LENGTH(reason) >= 8),
  expires_at               TEXT,
  created_at               TEXT NOT NULL,
  CHECK (
    (applies_to_all_pieces = 1 AND piece_name IS NULL) OR
    (applies_to_all_pieces = 0 AND piece_name IS NOT NULL)
  )
);

CREATE INDEX IF NOT EXISTS idx_ssh_grants_connection ON ssh_connection_grants(connection_id);
CREATE INDEX IF NOT EXISTS idx_ssh_grants_subject    ON ssh_connection_grants(subject_type, subject_id);

-- Dedicated audit log with pending→completed lifecycle.
-- beginAudit inserts outcome='pending' and commits before any remote call.
-- completeAudit updates outcome + completed_at + detail.
-- Startup recovery sweeps remaining 'pending' rows to 'aborted'.
CREATE TABLE IF NOT EXISTS ssh_audit_log (
  id                INTEGER PRIMARY KEY AUTOINCREMENT,
  action            TEXT NOT NULL,
  entity_type       TEXT,
  entity_id         TEXT,
  connection_id     TEXT,
  owner_id          TEXT,
  acting_user_id    TEXT,
  job_id            TEXT,
  piece_name        TEXT,
  outcome           TEXT NOT NULL CHECK (outcome IN ('pending','success','failed','denied','aborted')),
  reason            TEXT,
  detail            TEXT,
  started_at        TEXT NOT NULL,
  completed_at      TEXT,
  FOREIGN KEY (connection_id) REFERENCES ssh_connections(id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_ssh_audit_action     ON ssh_audit_log(action, started_at);
CREATE INDEX IF NOT EXISTS idx_ssh_audit_connection ON ssh_audit_log(connection_id, started_at);
CREATE INDEX IF NOT EXISTS idx_ssh_audit_owner      ON ssh_audit_log(owner_id, started_at);
CREATE INDEX IF NOT EXISTS idx_ssh_audit_outcome    ON ssh_audit_log(outcome, started_at);
CREATE INDEX IF NOT EXISTS idx_ssh_audit_pending    ON ssh_audit_log(outcome) WHERE outcome = 'pending';

-- Abuse counters: single source of truth for failure_count + lock_until.
-- Three scope kinds: 'conn' (per connection), 'userhost' (per user+host+username),
-- 'globalhost' (per host+username; enforce_lock=0 for user-owned connections, 1 for globals).
CREATE TABLE IF NOT EXISTS ssh_abuse_counters (
  scope_key            TEXT PRIMARY KEY,
  scope_kind           TEXT NOT NULL CHECK (scope_kind IN ('conn','userhost','globalhost')),
  enforce_lock         INTEGER NOT NULL DEFAULT 1,
  failure_count        INTEGER NOT NULL DEFAULT 0,
  failure_window_start TEXT,
  lock_until           TEXT,
  updated_at           TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_ssh_abuse_kind   ON ssh_abuse_counters(scope_kind);
CREATE INDEX IF NOT EXISTS idx_ssh_abuse_locked ON ssh_abuse_counters(lock_until) WHERE lock_until IS NOT NULL;

-- ---------------------------------------------------------------------------
-- Shared Knowledge Notes
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS note_index (
  owner_id           TEXT NOT NULL,
  folder             TEXT NOT NULL,
  file_name          TEXT NOT NULL,
  title              TEXT,
  visibility         TEXT NOT NULL CHECK (visibility IN ('private','org','public')),
  visibility_scope_org_id TEXT,
  mode_hint          TEXT CHECK (mode_hint IS NULL OR mode_hint IN ('search','inject')),
  tags_json          TEXT,
  content_size       INTEGER NOT NULL DEFAULT 0,
  content_hash       TEXT NOT NULL DEFAULT '',
  body               TEXT NOT NULL DEFAULT '',
  updated_at         INTEGER NOT NULL,
  PRIMARY KEY (owner_id, folder, file_name),
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_note_index_visibility ON note_index(visibility, visibility_scope_org_id);
CREATE INDEX IF NOT EXISTS idx_note_index_owner_folder ON note_index(owner_id, folder);

CREATE TABLE IF NOT EXISTS note_subscriptions (
  consumer_user_id   TEXT NOT NULL,
  publisher_user_id  TEXT NOT NULL,
  folder             TEXT NOT NULL,
  mode               TEXT NOT NULL CHECK (mode IN ('search','inject')),
  enabled            INTEGER NOT NULL DEFAULT 1,
  created_at         INTEGER NOT NULL,
  PRIMARY KEY (consumer_user_id, publisher_user_id, folder),
  FOREIGN KEY (consumer_user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (publisher_user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_note_subscriptions_consumer_mode ON note_subscriptions(consumer_user_id, mode, enabled);

CREATE TABLE IF NOT EXISTS pending_reindex (
  owner_id           TEXT NOT NULL,
  folder             TEXT NOT NULL,
  file_name          TEXT NOT NULL,
  reason             TEXT,
  created_at         INTEGER NOT NULL,
  PRIMARY KEY (owner_id, folder, file_name)
);

CREATE VIRTUAL TABLE IF NOT EXISTS note_index_fts USING fts5(
  owner_id UNINDEXED,
  folder UNINDEXED,
  file_name UNINDEXED,
  title,
  tags,
  body
);

-- Sync triggers
CREATE TRIGGER IF NOT EXISTS note_index_ai AFTER INSERT ON note_index BEGIN
  INSERT INTO note_index_fts(owner_id, folder, file_name, title, tags, body)
  VALUES (new.owner_id, new.folder, new.file_name, new.title, new.tags_json, new.body);
END;

CREATE TRIGGER IF NOT EXISTS note_index_ad AFTER DELETE ON note_index BEGIN
  DELETE FROM note_index_fts WHERE owner_id = old.owner_id AND folder = old.folder AND file_name = old.file_name;
END;

CREATE TRIGGER IF NOT EXISTS note_index_au AFTER UPDATE ON note_index BEGIN
  DELETE FROM note_index_fts WHERE owner_id = old.owner_id AND folder = old.folder AND file_name = old.file_name;
  INSERT INTO note_index_fts(owner_id, folder, file_name, title, tags, body)
  VALUES (new.owner_id, new.folder, new.file_name, new.title, new.tags_json, new.body);
END;

-- Per-user dashboard widgets (Side Info Panel feature, 2026-05).
-- Markdown content authored by the user or by an agent via UpdateDashboardWidget.
-- `slug` is user-scoped and used as an upsert key by the agent tool.
CREATE TABLE IF NOT EXISTS user_dashboard_widgets (
  id               INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id          TEXT NOT NULL,
  slug             TEXT NOT NULL,
  title            TEXT NOT NULL,
  -- Widget kind. 'markdown' (default) stores user/agent-authored Markdown in
  -- markdown_content; 'node-status' is a built-in view that ignores
  -- markdown_content and renders BackendStatusRegistry data live. See
  -- docs/superpowers/specs/2026-05-18-multi-team-gpu-pool-and-node-status-design.md (Phase B).
  kind             TEXT NOT NULL DEFAULT 'markdown',
  markdown_content TEXT NOT NULL DEFAULT '',
  sort_order       INTEGER NOT NULL DEFAULT 0,
  created_at       TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at       TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE(user_id, slug)
);

CREATE INDEX IF NOT EXISTS idx_dashboard_widgets_user
  ON user_dashboard_widgets (user_id, sort_order);

-- ── AAO Gateway: virtual keys (Phase 2a) ────────────────────────────────
-- Per-team bearer keys for gateway-mode auth. Only the SHA-256 hash is
-- stored (raw key is returned to the admin exactly once at issuance).
-- `source` distinguishes admin-API-issued keys from keys auto-imported
-- from config.yaml at gateway boot; the latter are protected from hard
-- delete via the admin API.
-- Plan: docs/superpowers/specs/2026-05-18-aao-gateway-mode-design.md (Phase 2a).
CREATE TABLE IF NOT EXISTS gateway_virtual_keys (
  id             TEXT PRIMARY KEY,
  key_hash       TEXT NOT NULL UNIQUE,
  key_prefix     TEXT NOT NULL,
  team           TEXT NOT NULL,
  allowed_models TEXT,                          -- JSON array, NULL = no allowlist
  source         TEXT NOT NULL DEFAULT 'admin' CHECK (source IN ('admin','config-import')),
  created_at     TEXT NOT NULL,
  created_by     TEXT,
  revoked_at     TEXT,
  revoked_by     TEXT,
  last_used_at   TEXT,
  -- Phase 2b additions: per-key monthly token budget + per-minute rate
  -- limit. NULL = unlimited (default). Enforced by the gateway middleware
  -- chain; the DB only persists the limit values.
  tokens_budget  INTEGER,
  rate_limit_rpm INTEGER
);

-- Partial index so hot-path auth lookup only walks active keys.
CREATE INDEX IF NOT EXISTS idx_gateway_keys_hash_active
  ON gateway_virtual_keys (key_hash)
  WHERE revoked_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_gateway_keys_team
  ON gateway_virtual_keys (team);

-- ── AAO Gateway: per-key usage tracking (Phase 2b) ──────────────────────
-- Monthly token + request counters. period_start is the UTC 'YYYY-MM'
-- bucket. PRIMARY KEY (key_id, period_start) lets the budget enforcement
-- read happen as a single point lookup.
-- Plan: docs/superpowers/specs/2026-05-18-aao-gateway-mode-design.md (Phase 2b).
CREATE TABLE IF NOT EXISTS gateway_key_usage (
  key_id          TEXT NOT NULL REFERENCES gateway_virtual_keys(id) ON DELETE CASCADE,
  period_start    TEXT NOT NULL,                  -- 'YYYY-MM' (UTC)
  tokens_in       INTEGER NOT NULL DEFAULT 0,
  tokens_out      INTEGER NOT NULL DEFAULT 0,
  requests        INTEGER NOT NULL DEFAULT 0,
  last_updated_at TEXT NOT NULL,
  PRIMARY KEY (key_id, period_start)
);

CREATE INDEX IF NOT EXISTS idx_gateway_usage_key
  ON gateway_key_usage (key_id);

-- ── Browser Notifications V2: Web Push subscriptions + per-user prefs ───
-- Spec: docs/superpowers/specs/2026-05-28-browser-notifications-v2-webpush.md
-- endpoint is globally UNIQUE so logging into a different user in the same
-- browser reassigns the subscription via ON CONFLICT(endpoint) DO UPDATE.
CREATE TABLE IF NOT EXISTS push_subscriptions (
  id              TEXT PRIMARY KEY,
  user_id         TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  endpoint        TEXT NOT NULL UNIQUE,
  p256dh          TEXT NOT NULL,
  auth            TEXT NOT NULL,
  user_agent      TEXT,
  vapid_key_id    TEXT NOT NULL,
  created_at      TEXT NOT NULL DEFAULT (datetime('now')),
  last_success_at TEXT,
  last_failure_at TEXT,
  failure_count   INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_push_subscriptions_user_id
  ON push_subscriptions(user_id);

-- Per-user notification preferences. v1_migrated tracks the one-time import
-- from V1 localStorage. include_details gates whether task title/piece name
-- are placed in the push payload (privacy-default off).
CREATE TABLE IF NOT EXISTS user_notification_prefs (
  user_id              TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  enabled              INTEGER NOT NULL DEFAULT 1,
  event_running        INTEGER NOT NULL DEFAULT 1,
  event_succeeded      INTEGER NOT NULL DEFAULT 1,
  event_failed         INTEGER NOT NULL DEFAULT 1,
  event_waiting_human  INTEGER NOT NULL DEFAULT 1,
  include_details      INTEGER NOT NULL DEFAULT 0,
  v1_migrated          INTEGER NOT NULL DEFAULT 0,
  updated_at           TEXT NOT NULL DEFAULT (datetime('now'))
);
