vent_agent_2.0/db/schema_sqlite.sql

-- ============================================================
-- vent_agent 2.0 — 默认 SQLite 业务库
-- ============================================================
-- 与 mysql_schema.sql 结构完全等价，仅类型与索引语法按 SQLite 调整。
-- 会话、业务任务、计费与推送记录均使用本库；checkpoint 使用独立 SQLite 文件。
-- ============================================================

CREATE TABLE IF NOT EXISTS sessions (
  id          TEXT PRIMARY KEY,
  title       TEXT NOT NULL DEFAULT '',
  user_name   TEXT NOT NULL DEFAULT 'default',
  mode        TEXT NOT NULL DEFAULT 'full',
  pinned      INTEGER NOT NULL DEFAULT 0,
  created_at  TEXT NOT NULL,
  updated_at  TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sessions_user_updated ON sessions (user_name, updated_at);
CREATE INDEX IF NOT EXISTS idx_sessions_title ON sessions (title);

CREATE TABLE IF NOT EXISTS messages (
  id          TEXT PRIMARY KEY,
  session_id  TEXT NOT NULL,
  msg_role    TEXT NOT NULL,
  content     TEXT,
  tool_calls  TEXT,
  citations   TEXT,
  created_at  TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_messages_session_created ON messages (session_id, created_at);

CREATE TABLE IF NOT EXISTS session_context_usage (
  id                TEXT PRIMARY KEY,
  session_id        TEXT NOT NULL,
  turn              INTEGER NOT NULL DEFAULT 0,
  input_tokens      INTEGER NOT NULL DEFAULT 0,
  output_tokens     INTEGER NOT NULL DEFAULT 0,
  cache_read_tokens INTEGER NOT NULL DEFAULT 0,
  total_tokens      INTEGER NOT NULL DEFAULT 0,
  usage_source      TEXT NOT NULL DEFAULT 'provider',
  compacted         INTEGER NOT NULL DEFAULT 0,
  compact_count     INTEGER NOT NULL DEFAULT 0,
  snapshot          TEXT,
  created_at        TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_ctx_session_created ON session_context_usage (session_id, created_at);

CREATE TABLE IF NOT EXISTS context_archives (
  id          TEXT PRIMARY KEY,
  session_id  TEXT NOT NULL,
  turn        INTEGER NOT NULL DEFAULT 0,
  file_path   TEXT NOT NULL DEFAULT '',
  tokens      INTEGER NOT NULL DEFAULT 0,
  created_at  TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_archive_session_created ON context_archives (session_id, created_at);

CREATE TABLE IF NOT EXISTS schedules (
  id          TEXT PRIMARY KEY,
  user_name   TEXT NOT NULL DEFAULT 'default',
  title       TEXT NOT NULL DEFAULT '',
  cron        TEXT NOT NULL,
  prompt      TEXT NOT NULL,
  mode        TEXT NOT NULL DEFAULT 'full',
  enabled     INTEGER NOT NULL DEFAULT 1,
  created_at  TEXT NOT NULL,
  updated_at  TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_schedules_enabled ON schedules (enabled);

CREATE TABLE IF NOT EXISTS schedule_runs (
  id          TEXT PRIMARY KEY,
  schedule_id TEXT NOT NULL,
  thread_id   TEXT NOT NULL,
  status      TEXT NOT NULL,
  started_at  TEXT NOT NULL,
  finished_at TEXT,
  error       TEXT,
  output      TEXT
);
CREATE INDEX IF NOT EXISTS idx_runs_schedule_started ON schedule_runs (schedule_id, started_at);

CREATE TABLE IF NOT EXISTS billing_usage (
  id            TEXT PRIMARY KEY,
  user_name     TEXT NOT NULL DEFAULT 'default',
  session_id    TEXT NOT NULL DEFAULT '',
  kind          TEXT NOT NULL DEFAULT 'chat',
  model         TEXT NOT NULL DEFAULT '',
  input_tokens  INTEGER NOT NULL DEFAULT 0,
  output_tokens INTEGER NOT NULL DEFAULT 0,
  total_tokens  INTEGER NOT NULL DEFAULT 0,
  cost          REAL NOT NULL DEFAULT 0,
  usage_day     TEXT NOT NULL,
  trace_id      TEXT NOT NULL DEFAULT '',
  created_at    TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_billing_user_day ON billing_usage (user_name, usage_day);
CREATE INDEX IF NOT EXISTS idx_billing_session ON billing_usage (session_id);

CREATE TABLE IF NOT EXISTS billing_pricing (
  model        TEXT PRIMARY KEY,
  input_price  REAL NOT NULL DEFAULT 0,
  output_price REAL NOT NULL DEFAULT 0,
  currency     TEXT NOT NULL DEFAULT 'CNY',
  updated_at   TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS daily_quota (
  user_name    TEXT NOT NULL,
  usage_day    TEXT NOT NULL,
  limit_tokens INTEGER NOT NULL DEFAULT 0,
  used_tokens  INTEGER NOT NULL DEFAULT 0,
  updated_at   TEXT NOT NULL,
  PRIMARY KEY (user_name, usage_day)
);

CREATE TABLE IF NOT EXISTS user_preferences (
  id          TEXT PRIMARY KEY,
  user_name   TEXT NOT NULL,
  pref_key    TEXT NOT NULL,
  pref_value  TEXT NOT NULL,
  updated_at  TEXT NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS uk_pref_user_key ON user_preferences (user_name, pref_key);

CREATE TABLE IF NOT EXISTS execution_runs (
  id TEXT PRIMARY KEY,
  session_id TEXT NOT NULL,
  user_name VARCHAR(128) NOT NULL,
  kind VARCHAR(24) NOT NULL,
  status VARCHAR(24) NOT NULL,
  error TEXT,
  started_at VARCHAR(32) NOT NULL,
  finished_at VARCHAR(32)
);
CREATE TABLE IF NOT EXISTS billing_pending (
  id TEXT PRIMARY KEY,
  payload TEXT NOT NULL,
  created_at VARCHAR(32) NOT NULL
);

CREATE TABLE IF NOT EXISTS request_keys (
  id TEXT PRIMARY KEY,
  session_id TEXT NOT NULL,
  run_id TEXT NOT NULL,
  created_at VARCHAR(32) NOT NULL
);

CREATE TABLE IF NOT EXISTS business_agent_state (
  name TEXT PRIMARY KEY,
  local_enabled INTEGER NOT NULL DEFAULT 1,
  gateway_online INTEGER NOT NULL DEFAULT 1,
  updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS business_session_agents (
  session_id TEXT PRIMARY KEY,
  agent_name TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS business_jobs (
  id TEXT PRIMARY KEY,
  agent_name TEXT NOT NULL,
  agent_code TEXT,
  user_name TEXT NOT NULL,
  source TEXT NOT NULL,
  payload TEXT NOT NULL,
  status TEXT NOT NULL,
  session_id TEXT,
  run_id TEXT,
  error TEXT,
  result_json TEXT,
  push_status TEXT NOT NULL DEFAULT 'none',
  push_attempts INTEGER NOT NULL DEFAULT 0,
  next_push_at TEXT,
  push_payload TEXT,
  receipt_json TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  finished_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_business_jobs_status ON business_jobs (agent_name, status, created_at);
CREATE TABLE IF NOT EXISTS business_push_targets (
  job_id TEXT PRIMARY KEY,
  endpoint TEXT NOT NULL,
  created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS business_files (
  id TEXT PRIMARY KEY,
  job_id TEXT NOT NULL,
  file_id TEXT NOT NULL,
  version TEXT NOT NULL,
  filename TEXT NOT NULL,
  local_path TEXT NOT NULL,
  sha256 TEXT NOT NULL,
  size INTEGER NOT NULL,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS main_fan_documents (
  id TEXT PRIMARY KEY,
  sha256 TEXT NOT NULL,
  facts_json TEXT NOT NULL,
  method TEXT NOT NULL,
  created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS main_fan_analyses (
  id TEXT PRIMARY KEY,
  user_name TEXT NOT NULL,
  session_id TEXT NOT NULL,
  result_json TEXT NOT NULL,
  created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS main_fan_workflows (
  id TEXT PRIMARY KEY,
  user_name TEXT NOT NULL,
  session_id TEXT NOT NULL,
  context_json TEXT NOT NULL,
  stages_json TEXT NOT NULL,
  result_json TEXT,
  status TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS test_lab_assets (
  id TEXT PRIMARY KEY,
  user_name TEXT NOT NULL,
  filename TEXT NOT NULL,
  sha256 TEXT NOT NULL,
  size INTEGER NOT NULL,
  created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS test_lab_runs (
  job_id TEXT PRIMARY KEY,
  user_name TEXT NOT NULL,
  agent_name TEXT NOT NULL,
  label TEXT NOT NULL,
  scenario_id TEXT NOT NULL,
  execution_mode TEXT NOT NULL,
  request_json TEXT NOT NULL,
  created_at TEXT NOT NULL
);