GoClaw/drizzle/schema.ts

import { int, mysqlEnum, mysqlTable, text, timestamp, varchar, decimal, json, boolean, index } from "drizzle-orm/mysql-core";

/**
 * Core user table backing auth flow.
 * Extend this file with additional tables as your product grows.
 * Columns use camelCase to match both database fields and generated types.
 */
export const users = mysqlTable("users", {
  /**
   * Surrogate primary key. Auto-incremented numeric value managed by the database.
   * Use this for relations between tables.
   */
  id: int("id").autoincrement().primaryKey(),
  /** Manus OAuth identifier (openId) returned from the OAuth callback. Unique per user. */
  openId: varchar("openId", { length: 64 }).notNull().unique(),
  name: text("name"),
  email: varchar("email", { length: 320 }),
  loginMethod: varchar("loginMethod", { length: 64 }),
  role: mysqlEnum("role", ["user", "admin"]).default("user").notNull(),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
  lastSignedIn: timestamp("lastSignedIn").defaultNow().notNull(),
});

export type User = typeof users.$inferSelect;
export type InsertUser = typeof users.$inferInsert;

/**
 * Agents — конфигурация и управление AI-агентами
 */
export const agents = mysqlTable("agents", {
  id: int("id").autoincrement().primaryKey(),
  userId: int("userId").notNull(), // Владелец агента
  name: varchar("name", { length: 255 }).notNull(),
  description: text("description"),
  role: varchar("role", { length: 100 }).notNull(), // "developer", "researcher", "executor"
  
  // Модель LLM
  model: varchar("model", { length: 100 }).notNull(),
  provider: varchar("provider", { length: 50 }).notNull(),
  
  // Параметры LLM
  temperature: decimal("temperature", { precision: 3, scale: 2 }).default("0.7"),
  maxTokens: int("maxTokens").default(2048),
  topP: decimal("topP", { precision: 3, scale: 2 }).default("1.0"),
  frequencyPenalty: decimal("frequencyPenalty", { precision: 3, scale: 2 }).default("0.0"),
  presencePenalty: decimal("presencePenalty", { precision: 3, scale: 2 }).default("0.0"),
  
  // System Prompt
  systemPrompt: text("systemPrompt"),
  
  // Доступы и разрешения
  allowedTools: json("allowedTools").$type<string[]>().default([]),
  allowedDomains: json("allowedDomains").$type<string[]>().default([]),
  maxRequestsPerHour: int("maxRequestsPerHour").default(100),
  
  // Статус
  isActive: boolean("isActive").default(true),
  isPublic: boolean("isPublic").default(false),
  isSystem: boolean("isSystem").default(false),      // Системный агент (нельзя удалить)
  isOrchestrator: boolean("isOrchestrator").default(false), // Главный оркестратор чата

  // ── Container / Swarm fields ──────────────────────────────────────────────
  // Имя Docker Swarm service: "goclaw-agent-{id}"
  serviceName: varchar("serviceName", { length: 100 }),
  // Порт HTTP API агента внутри overlay сети (8001–8999)
  servicePort: int("servicePort"),
  // Docker image для запуска агента
  containerImage: varchar("containerImage", { length: 255 }).default("goclaw-agent-worker:latest"),
  // Статус контейнера (обновляется при деплое/остановке)
  containerStatus: mysqlEnum("containerStatus", ["stopped", "deploying", "running", "error"]).default("stopped"),

  // Метаданные
  tags: json("tags").$type<string[]>().default([]),
  metadata: json("metadata").$type<Record<string, any>>().default({}),
  
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
}, (table) => ({
  userIdIdx: index("agents_userId_idx").on(table.userId),
  modelIdx: index("agents_model_idx").on(table.model),
}));

export type Agent = typeof agents.$inferSelect;
export type InsertAgent = typeof agents.$inferInsert;

/**
 * Agent Metrics — метрики производительности агентов
 */
export const agentMetrics = mysqlTable("agentMetrics", {
  id: int("id").autoincrement().primaryKey(),
  agentId: int("agentId").notNull(),
  
  // Информация о запросе
  requestId: varchar("requestId", { length: 64 }).notNull().unique(),
  userMessage: text("userMessage"),
  agentResponse: text("agentResponse"),
  
  // Токены
  inputTokens: int("inputTokens").default(0),
  outputTokens: int("outputTokens").default(0),
  totalTokens: int("totalTokens").default(0),
  
  // Время обработки
  processingTimeMs: int("processingTimeMs").notNull(),
  
  // Статус
  status: mysqlEnum("status", ["success", "error", "timeout", "rate_limited"]).notNull(),
  errorMessage: text("errorMessage"),
  
  // Инструменты
  toolsCalled: json("toolsCalled").$type<string[]>().default([]),
  
  // Модель
  model: varchar("model", { length: 100 }),
  temperature: decimal("temperature", { precision: 3, scale: 2 }),
  
  createdAt: timestamp("createdAt").defaultNow().notNull(),
}, (table) => ({
  agentIdIdx: index("agentMetrics_agentId_idx").on(table.agentId),
  createdAtIdx: index("agentMetrics_createdAt_idx").on(table.createdAt),
}));

export type AgentMetric = typeof agentMetrics.$inferSelect;
export type InsertAgentMetric = typeof agentMetrics.$inferInsert;

/**
 * Agent History — полная история запросов
 */
export const agentHistory = mysqlTable("agentHistory", {
  id: int("id").autoincrement().primaryKey(),
  agentId: int("agentId").notNull(),
  
  userMessage: text("userMessage").notNull(),
  agentResponse: text("agentResponse"),
  
  conversationId: varchar("conversationId", { length: 64 }),
  messageIndex: int("messageIndex"),
  
  status: mysqlEnum("status", ["pending", "success", "error"]).default("pending"),
  
  createdAt: timestamp("createdAt").defaultNow().notNull(),
}, (table) => ({
  agentIdIdx: index("agentHistory_agentId_idx").on(table.agentId),
}));

export type AgentHistory = typeof agentHistory.$inferSelect;
export type InsertAgentHistory = typeof agentHistory.$inferInsert;

/**
 * Agent Access Control — управление доступами
 */
export const agentAccessControl = mysqlTable("agentAccessControl", {
  id: int("id").autoincrement().primaryKey(),
  agentId: int("agentId").notNull(),
  
  tool: varchar("tool", { length: 50 }).notNull(),
  
  isAllowed: boolean("isAllowed").default(true),
  
  maxExecutionsPerHour: int("maxExecutionsPerHour").default(100),
  timeoutSeconds: int("timeoutSeconds").default(30),
  
  allowedPatterns: json("allowedPatterns").$type<string[]>().default([]),
  blockedPatterns: json("blockedPatterns").$type<string[]>().default([]),
  
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
}, (table) => ({
  agentIdToolIdx: index("agentAccessControl_agentId_tool_idx").on(table.agentId, table.tool),
}));

export type AgentAccessControl = typeof agentAccessControl.$inferSelect;
export type InsertAgentAccessControl = typeof agentAccessControl.$inferInsert;
/**
 * Tool Definitions — пользовательские инструменты, созданные Tool Builder Agent
 */
export const toolDefinitions = mysqlTable("toolDefinitions", {
  id: int("id").autoincrement().primaryKey(),
  toolId: varchar("toolId", { length: 100 }).notNull().unique(),
  name: varchar("name", { length: 255 }).notNull(),
  description: text("description").notNull(),
  category: varchar("category", { length: 50 }).notNull().default("custom"),
  dangerous: boolean("dangerous").default(false),
  parameters: json("parameters").$type<Record<string, { type: string; description: string; required?: boolean }>>(),
  implementation: text("implementation").notNull(), // JS код функции
  isActive: boolean("isActive").default(true),
  createdBy: int("createdBy"), // agentId или null
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
});

export type ToolDefinition = typeof toolDefinitions.$inferSelect;
export type InsertToolDefinition = typeof toolDefinitions.$inferInsert;

/**
 * Browser Sessions — активные сессии браузера для Browser Agent
 */
export const browserSessions = mysqlTable("browserSessions", {
  id: int("id").autoincrement().primaryKey(),
  sessionId: varchar("sessionId", { length: 64 }).notNull().unique(),
  agentId: int("agentId").notNull(),
  currentUrl: text("currentUrl"),
  title: text("title"),
  status: mysqlEnum("status", ["active", "idle", "closed", "error"]).default("idle"),
  screenshotUrl: text("screenshotUrl"), // S3 URL последнего скриншота
  lastActionAt: timestamp("lastActionAt").defaultNow(),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  closedAt: timestamp("closedAt"),
});

export type BrowserSession = typeof browserSessions.$inferSelect;
export type InsertBrowserSession = typeof browserSessions.$inferInsert;

/**
 * LLM Providers — хранение конфигурации подключений к LLM API.
 * API-ключи хранятся в зашифрованном виде (AES-256-GCM через crypto.ts).
 * Активный провайдер читается gateway при каждом запросе.
 */
export const llmProviders = mysqlTable("llmProviders", {
  id: int("id").autoincrement().primaryKey(),
  name: varchar("name", { length: 128 }).notNull(),          // "Ollama Cloud", "OpenAI", etc.
  baseUrl: varchar("baseUrl", { length: 512 }).notNull(),    // https://ollama.com/v1
  apiKeyEncrypted: text("apiKeyEncrypted"),                  // AES-256-GCM encrypted key
  apiKeyHint: varchar("apiKeyHint", { length: 16 }),         // First 8 chars for display
  isActive: boolean("isActive").default(false).notNull(),    // Only one can be active
  isDefault: boolean("isDefault").default(false).notNull(),  // Default provider for new agents
  modelDefault: varchar("modelDefault", { length: 128 }),    // Default model for this provider
  notes: text("notes"),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
});

export type LlmProvider = typeof llmProviders.$inferSelect;
export type InsertLlmProvider = typeof llmProviders.$inferInsert;

/**
 * Chat Sessions — persistent server-side chat runs.
 * Each user message creates one session. The Go gateway processes it
 * and writes events to chatEvents. The frontend polls for events.
 */
export const chatSessions = mysqlTable("chatSessions", {
  id: int("id").autoincrement().primaryKey(),
  sessionId: varchar("sessionId", { length: 64 }).notNull().unique(),
  agentId: int("agentId").notNull().default(1),
  status: mysqlEnum("status", ["running", "done", "error"]).notNull().default("running"),
  userMessage: text("userMessage").notNull(),
  finalResponse: text("finalResponse"),
  model: varchar("model", { length: 128 }),
  totalTokens: int("totalTokens").default(0),
  processingTimeMs: int("processingTimeMs").default(0),
  errorMessage: text("errorMessage"),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
}, (table) => ({
  statusIdx: index("chatSessions_status_idx").on(table.status),
  createdAtIdx: index("chatSessions_createdAt_idx").on(table.createdAt),
}));

export type ChatSession = typeof chatSessions.$inferSelect;
export type InsertChatSession = typeof chatSessions.$inferInsert;

/**
 * Chat Events — individual SSE events written by Go gateway, read by frontend.
 */
export const chatEvents = mysqlTable("chatEvents", {
  id: int("id").autoincrement().primaryKey(),
  sessionId: varchar("sessionId", { length: 64 }).notNull(),
  seq: int("seq").notNull().default(0),
  eventType: mysqlEnum("eventType", ["thinking", "tool_call", "delta", "done", "error"]).notNull(),
  content: text("content"),
  toolName: varchar("toolName", { length: 128 }),
  toolArgs: json("toolArgs"),
  toolResult: text("toolResult"),
  toolSuccess: boolean("toolSuccess"),
  durationMs: int("durationMs"),
  model: varchar("model", { length: 128 }),
  usageJson: json("usageJson"),
  errorMsg: text("errorMsg"),
  createdAt: timestamp("createdAt", { fsp: 3 }).defaultNow().notNull(),
}, (table) => ({
  sessionSeqIdx: index("chatEvents_sessionId_seq_idx").on(table.sessionId, table.seq),
}));

export type ChatEvent = typeof chatEvents.$inferSelect;
export type InsertChatEvent = typeof chatEvents.$inferInsert;

// ─── Workflows ────────────────────────────────────────────────────────────────

/**
 * Workflows — visual pipeline definitions composed of agent/container nodes.
 * Each workflow is a directed graph stored as nodes + edges.
 */
export const workflows = mysqlTable("workflows", {
  id: int("id").autoincrement().primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  description: text("description"),
  /** Visual status used in the list/dashboard */
  status: mysqlEnum("status", ["draft", "active", "paused", "archived"]).default("draft").notNull(),
  /** JSON blob of canvas-level metadata: viewport position, zoom, layout hints */
  canvasMeta: json("canvasMeta").$type<{ viewportX?: number; viewportY?: number; zoom?: number }>().default({}),
  tags: json("tags").$type<string[]>().default([]),
  createdBy: int("createdBy"),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
});

export type Workflow = typeof workflows.$inferSelect;
export type InsertWorkflow = typeof workflows.$inferInsert;

/**
 * Workflow Nodes — individual blocks inside a workflow.
 * Each node references either an agent (agentId) or an arbitrary container config.
 */
export const workflowNodes = mysqlTable("workflowNodes", {
  id: int("id").autoincrement().primaryKey(),
  workflowId: int("workflowId").notNull(),
  /** Unique client-side ID used by the canvas (e.g. "node_abc123") */
  nodeKey: varchar("nodeKey", { length: 64 }).notNull(),
  label: varchar("label", { length: 255 }).notNull(),
  /** Node kind: agent = uses an existing agent; container = custom Docker image; trigger = entry point; output = terminal */
  kind: mysqlEnum("kind", ["agent", "container", "trigger", "condition", "output"]).notNull(),
  /** Link to agents table (nullable — only for kind=agent) */
  agentId: int("agentId"),
  /** For kind=container: Docker image, env vars, ports etc. */
  containerConfig: json("containerConfig").$type<{
    image?: string;
    env?: string[];
    ports?: string[];
    command?: string;
    volumes?: string[];
  }>().default({}),
  /** For kind=condition: JS expression evaluated at runtime */
  conditionExpr: text("conditionExpr"),
  /** Trigger config: cron, webhook, manual */
  triggerConfig: json("triggerConfig").$type<{ type?: string; cron?: string; webhookPath?: string }>().default({}),
  /** Canvas position */
  posX: int("posX").default(0),
  posY: int("posY").default(0),
  /** Extra metadata (colour, icon override, etc.) */
  meta: json("meta").$type<Record<string, any>>().default({}),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
}, (table) => ({
  workflowIdIdx: index("workflowNodes_workflowId_idx").on(table.workflowId),
}));

export type WorkflowNode = typeof workflowNodes.$inferSelect;
export type InsertWorkflowNode = typeof workflowNodes.$inferInsert;

/**
 * Workflow Edges — connections between nodes.
 */
export const workflowEdges = mysqlTable("workflowEdges", {
  id: int("id").autoincrement().primaryKey(),
  workflowId: int("workflowId").notNull(),
  /** Edge identifier on the canvas */
  edgeKey: varchar("edgeKey", { length: 64 }).notNull(),
  sourceNodeKey: varchar("sourceNodeKey", { length: 64 }).notNull(),
  targetNodeKey: varchar("targetNodeKey", { length: 64 }).notNull(),
  /** Optional: which output handle → which input handle */
  sourceHandle: varchar("sourceHandle", { length: 64 }),
  targetHandle: varchar("targetHandle", { length: 64 }),
  /** Edge label (e.g. "on success", "on fail") */
  label: varchar("label", { length: 128 }),
  /** Visual styling */
  meta: json("meta").$type<Record<string, any>>().default({}),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
}, (table) => ({
  workflowIdIdx: index("workflowEdges_workflowId_idx").on(table.workflowId),
}));

export type WorkflowEdge = typeof workflowEdges.$inferSelect;
export type InsertWorkflowEdge = typeof workflowEdges.$inferInsert;

/**
 * Workflow Runs — execution history.  Each run tracks overall status and
 * per-node results so the dashboard can show progress in real-time.
 */
export const workflowRuns = mysqlTable("workflowRuns", {
  id: int("id").autoincrement().primaryKey(),
  workflowId: int("workflowId").notNull(),
  runKey: varchar("runKey", { length: 64 }).notNull().unique(),
  status: mysqlEnum("status", ["pending", "running", "success", "failed", "cancelled"]).default("pending").notNull(),
  /** Per-node execution results: { [nodeKey]: { status, output, durationMs, error? } } */
  nodeResults: json("nodeResults").$type<Record<string, {
    status: "pending" | "running" | "success" | "failed" | "skipped";
    output?: string;
    durationMs?: number;
    error?: string;
    startedAt?: string;
    finishedAt?: string;
  }>>().default({}),
  /** The node currently being executed */
  currentNodeKey: varchar("currentNodeKey", { length: 64 }),
  /** Global input passed to the first node */
  input: text("input"),
  /** Final aggregated output */
  output: text("output"),
  totalDurationMs: int("totalDurationMs"),
  errorMessage: text("errorMessage"),
  startedAt: timestamp("startedAt"),
  finishedAt: timestamp("finishedAt"),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
}, (table) => ({
  workflowIdIdx: index("workflowRuns_workflowId_idx").on(table.workflowId),
  statusIdx: index("workflowRuns_status_idx").on(table.status),
}));

export type WorkflowRun = typeof workflowRuns.$inferSelect;
export type InsertWorkflowRun = typeof workflowRuns.$inferInsert;

// ─── TaskBoard ───────────────────────────────────────────────────────────────

/**
 * ChatTasks — persistent task board linked to chat sessions.
 * Both the orchestrator and agents can create/update tasks.
 * Subtasks are stored as JSON array inside the parent task row.
 */
export const chatTasks = mysqlTable("chatTasks", {
  id: int("id").autoincrement().primaryKey(),
  taskId: varchar("taskId", { length: 32 }).notNull().unique(),
  sessionId: varchar("sessionId", { length: 64 }),    // linked chat session
  content: text("content").notNull(),
  status: mysqlEnum("status", ["pending", "in_progress", "completed", "failed", "blocked"]).default("pending").notNull(),
  priority: mysqlEnum("priority", ["critical", "high", "medium", "low"]).default("medium").notNull(),
  createdBy: varchar("createdBy", { length: 128 }).default("user"),
  assignedTo: varchar("assignedTo", { length: 128 }),
  /** Subtasks stored as JSON array */
  subtasks: json("subtasks").$type<Array<{
    id: string;
    content: string;
    status: string;
    createdBy: string;
    createdAt: number;
    completedAt?: number;
  }>>().default([]),
  elapsedMs: int("elapsedMs").default(0),
  retryCount: int("retryCount").default(0),
  lastError: text("lastError"),
  testedAt: timestamp("testedAt"),
  startedAt: timestamp("startedAt"),
  completedAt: timestamp("completedAt"),
  createdAt: timestamp("createdAt").defaultNow().notNull(),
  updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
}, (table) => ({
  sessionIdx: index("chatTasks_sessionId_idx").on(table.sessionId),
  statusIdx: index("chatTasks_status_idx").on(table.status),
}));

export type ChatTask = typeof chatTasks.$inferSelect;
export type InsertChatTask = typeof chatTasks.$inferInsert;