AgentServer/apps/server/src/db/schema.ts

import { sql } from 'drizzle-orm'
import { index, integer, primaryKey, real, sqliteTable, text } from 'drizzle-orm/sqlite-core'
import type { FileAccessPolicy, InteractionRequest, InteractionResponse, PermissionMode, ProfileSnapshot } from '@agent-server/shared'

const now = sql`(strftime('%Y-%m-%dT%H:%M:%fZ','now'))`
const createdAt = () => text('created_at').notNull().default(now)
const updatedAt = () => text('updated_at').notNull().default(now)

export const apiKeys = sqliteTable('api_keys', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  keyHash: text('key_hash').notNull().unique(),
  keyPrefix: text('key_prefix').notNull(),
  enabled: integer('enabled', { mode: 'boolean' }).notNull().default(true),
  /** 这个平台可以使用的 Profile（至少一个） */
  allowedProfileIds: text('allowed_profile_ids', { mode: 'json' }).$type<string[]>().notNull().default(sql`'[]'`),
  /** 平台创建会话时不指定 Profile 则使用它，必须在 allowedProfileIds 中 */
  defaultProfileId: text('default_profile_id'),
  createdAt: createdAt(),
  lastUsedAt: text('last_used_at'),
})

export const profiles = sqliteTable('profiles', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  description: text('description').notNull().default(''),
  provider: text('provider', { enum: ['claude', 'codex'] }).notNull(),
  model: text('model').notNull(),
  systemPrompt: text('system_prompt').notNull().default(''),
  permissionMode: text('permission_mode').$type<PermissionMode>().notNull().default('ask'),
  allowedTools: text('allowed_tools', { mode: 'json' }).$type<string[]>().notNull().default(sql`'[]'`),
  disallowedTools: text('disallowed_tools', { mode: 'json' }).$type<string[]>().notNull().default(sql`'[]'`),
  skills: text('skills', { mode: 'json' }).$type<string[] | 'all'>().notNull().default(sql`'"all"'`),
  fileAccess: text('file_access', { mode: 'json' }).$type<FileAccessPolicy>().notNull(),
  knowledgeRootIds: text('knowledge_root_ids', { mode: 'json' }).$type<string[]>().notNull().default(sql`'[]'`),
  isDefault: integer('is_default', { mode: 'boolean' }).notNull().default(false),
  /** 内置 Profile（通用助手）不可删除 */
  builtin: integer('builtin', { mode: 'boolean' }).notNull().default(false),
  version: integer('version').notNull().default(1),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
})

export const knowledgeRoots = sqliteTable('knowledge_roots', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  path: text('path').notNull(),
  description: text('description').notNull().default(''),
  access: text('access', { enum: ['read', 'write'] }).notNull().default('read'),
  enabled: integer('enabled', { mode: 'boolean' }).notNull().default(true),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
})

export const sessions = sqliteTable('sessions', {
  id: text('id').primaryKey(),
  /** 调用来源：API Key id，或后台的 'admin' */
  source: text('source').notNull(),
  profileId: text('profile_id'),
  provider: text('provider', { enum: ['claude', 'codex'] }).notNull(),
  model: text('model').notNull(),
  /** 为空表示跟随 Profile；只有后台管理员可以为单个会话覆盖 */
  permissionMode: text('permission_mode').$type<PermissionMode>(),
  systemPromptAppend: text('system_prompt_append').notNull().default(''),
  providerSessionId: text('provider_session_id'),
  workspaceDir: text('workspace_dir').notNull(),
  title: text('title').notNull().default(''),
  lastSeq: integer('last_seq').notNull().default(0),
  archived: integer('archived', { mode: 'boolean' }).notNull().default(false),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
}, t => [index('sessions_source_idx').on(t.source, t.updatedAt)])

export const runs = sqliteTable('runs', {
  id: text('id').primaryKey(),
  sessionId: text('session_id').notNull(),
  status: text('status', { enum: ['queued', 'running', 'waiting_input', 'completed', 'failed', 'interrupted'] }).notNull(),
  startedAt: text('started_at'),
  endedAt: text('ended_at'),
  inputTokens: integer('input_tokens'),
  outputTokens: integer('output_tokens'),
  cacheReadTokens: integer('cache_read_tokens'),
  cacheWriteTokens: integer('cache_write_tokens'),
  costUsd: real('cost_usd'),
  error: text('error'),
  createdAt: createdAt(),
}, t => [index('runs_session_idx').on(t.sessionId)])

export const events = sqliteTable('events', {
  sessionId: text('session_id').notNull(),
  seq: integer('seq').notNull(),
  runId: text('run_id'),
  type: text('type').notNull(),
  payload: text('payload', { mode: 'json' }).notNull(),
  ts: text('ts').notNull(),
}, t => [primaryKey({ columns: [t.sessionId, t.seq] })])

export const interactions = sqliteTable('interactions', {
  id: text('id').primaryKey(),
  sessionId: text('session_id').notNull(),
  runId: text('run_id').notNull(),
  kind: text('kind', { enum: ['approval', 'question'] }).notNull(),
  request: text('request', { mode: 'json' }).$type<InteractionRequest>().notNull(),
  response: text('response', { mode: 'json' }).$type<InteractionResponse>(),
  status: text('status', { enum: ['pending', 'allowed', 'denied', 'answered', 'timeout', 'cancelled'] }).notNull(),
  createdAt: createdAt(),
  resolvedAt: text('resolved_at'),
  expiresAt: text('expires_at').notNull(),
}, t => [index('interactions_session_idx').on(t.sessionId, t.status)])

export const files = sqliteTable('files', {
  id: text('id').primaryKey(),
  sessionId: text('session_id').notNull(),
  relPath: text('rel_path').notNull(),
  origin: text('origin', { enum: ['upload', 'agent'] }).notNull(),
  mime: text('mime').notNull(),
  size: integer('size').notNull(),
  mtimeMs: real('mtime_ms').notNull(),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
}, t => [index('files_session_path_idx').on(t.sessionId, t.relPath)])

export const settings = sqliteTable('settings', {
  key: text('key').primaryKey(),
  value: text('value', { mode: 'json' }).notNull(),
  updatedAt: updatedAt(),
})

export const auditLogs = sqliteTable('audit_logs', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  ts: text('ts').notNull().default(now),
  actor: text('actor').notNull(),
  sessionId: text('session_id'),
  kind: text('kind', { enum: ['command', 'file_access_denied', 'network_denied', 'approval', 'admin', 'gateway_call'] }).notNull(),
  summary: text('summary').notNull(),
  detail: text('detail', { mode: 'json' }),
}, t => [index('audit_ts_idx').on(t.ts)])

export const adminSessions = sqliteTable('admin_sessions', {
  id: text('id').primaryKey(),
  createdAt: createdAt(),
  expiresAt: text('expires_at').notNull(),
})

/** 按天汇总的用量（Run 结束时累加；删除会话不影响历史用量）。日期为服务器本地日期 */
export const usageDaily = sqliteTable('usage_daily', {
  date: text('date').notNull(),
  /** API Key id，或后台的 'admin' */
  source: text('source').notNull(),
  provider: text('provider').notNull(),
  model: text('model').notNull(),
  runs: integer('runs').notNull().default(0),
  failed: integer('failed').notNull().default(0),
  interrupted: integer('interrupted').notNull().default(0),
  inputTokens: integer('input_tokens').notNull().default(0),
  outputTokens: integer('output_tokens').notNull().default(0),
  cacheReadTokens: integer('cache_read_tokens').notNull().default(0),
  cacheWriteTokens: integer('cache_write_tokens').notNull().default(0),
  costUsd: real('cost_usd').notNull().default(0),
  durationMs: integer('duration_ms').notNull().default(0),
}, t => [primaryKey({ columns: [t.date, t.source, t.provider, t.model] })])

/** Profile 的历史版本：每次内容变化保存一份快照（不含"是否默认"），可以查看和恢复 */
export const profileVersions = sqliteTable('profile_versions', {
  profileId: text('profile_id').notNull(),
  version: integer('version').notNull(),
  snapshot: text('snapshot', { mode: 'json' }).$type<ProfileSnapshot>().notNull(),
  createdAt: createdAt(),
}, t => [primaryKey({ columns: [t.profileId, t.version] })])