Drizzle

// ✅ Good: follows the existing user/workspace table family (names are// illustrative — swap `widget` for the real noun).export const userWidgetLogs = pgTable('user_widget_logs', { ... });export const workspaceWidgetLogs = pgTable('workspace_widget_logs', { ... });
// ❌ Bad: introduces a new suffix for the same concept.export const workspaceWidgetRecords = pgTable('workspace_widget_records', { ... });

// ✅ Good: app-generated text ID; explicit inserts can still override it.id: text('id')  .primaryKey()  .$defaultFn(() => idGenerator('agents'))  .notNull(),
// ❌ Bad: sequence state is fragile across DB migrations and restores.id: serial('id').primaryKey(),

// ✅ Good: surrogate PK; uniqueness scope can evolve without a PK rebuild.export const workspaceUserSettings = pgTable(  'workspace_user_settings',  {    id: uuid('id').defaultRandom().notNull().primaryKey(),    workspaceId: text('workspace_id').references(() => workspaces.id, { onDelete: 'cascade' }).notNull(),    userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),    ...timestamps,  },  (t) => [uniqueIndex('workspace_user_settings_workspace_id_user_id_unique').on(t.workspaceId, t.userId)],);
// ❌ Bad: locked to exactly these columns; adding a nullable scope column// (workspaceId, deviceId, …) later forces a full PK rebuild migration.(t) => [primaryKey({ columns: [t.workspaceId, t.userId] })],

userId: text('user_id')  .references(() => users.id, { onDelete: 'cascade' })  .notNull(),

...timestamps,  // Spread from _helpers.ts

// ✅ Good: absent until a custom-execution attempt actually runs// (packages/database/src/schemas/agentIntervention.ts).export const AGENT_INTERVENTION_CUSTOM_EXECUTION_STATES = ['pending', 'executing', 'completed'] as const;export type AgentInterventionCustomExecutionState =  (typeof AGENT_INTERVENTION_CUSTOM_EXECUTION_STATES)[number];
customExecutionState: text('custom_execution_state').$type<AgentInterventionCustomExecutionState>(),
// ❌ Bad: invents a new state that callers now need to handle everywhere.export type AgentInterventionCustomExecutionState = 'pending' | 'executing' | 'completed' | 'unknown';
customExecutionState: text('custom_execution_state')  .$type<AgentInterventionCustomExecutionState>()  .notNull()  .default('unknown');

// ✅ Good: explain the table's business object first, then only document// non-obvious lifecycle fields// (packages/database/src/schemas/agentIntervention.ts)./** * Private resolution/outbox records. Unlike `agent_interventions`, this table * may retain user-edited arguments so a claimed decision can be delivered * reliably after the app disconnects. It is never a notification payload. */export const agentInterventionResolutions = pgTable('agent_intervention_resolutions', {  /** Owner of the durable operation, distinct from the resolving actor. */  userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }).notNull(),
  /** Private executor result; never selected into Review or notification DTOs. */  customExecutionResult: jsonb('custom_execution_result').$type<AgentInterventionCustomExecutionResult>(),
  /** Monotonic lease attempt, starting at zero while pending. */  customExecutionAttempt: integer('custom_execution_attempt'),});
// ❌ Bad: comments restate obvious column names without adding domain meaning./** User email */email: text('email'),

// packages/types/src/agent/agentIntervention.ts +// packages/database/src/schemas/agentIntervention.tsinterface AgentInterventionCustomExecutionResult {  content: string;  pluginState: Record<string, unknown>;}
customExecutionResult: jsonb('custom_execution_result').$type<AgentInterventionCustomExecutionResult>(),

// ❌ Bad: hides the contract and makes downstream access untyped.customExecutionResult: jsonb('custom_execution_result').$type<Record<string, unknown>>(),

// Return array (object style deprecated)(t) => [uniqueIndex('client_id_user_id_unique').on(t.clientId, t.userId)],

export const insertAgentSchema = createInsertSchema(agents);export type NewAgent = typeof agents.$inferInsert;export type AgentItem = typeof agents.$inferSelect;

export const agents = pgTable(  'agents',  {    id: text('id')      .primaryKey()      .$defaultFn(() => idGenerator('agents'))      .notNull(),    slug: varchar('slug', { length: 100 })      .$defaultFn(() => randomSlug(4))      .unique(),    userId: text('user_id')      .references(() => users.id, { onDelete: 'cascade' })      .notNull(),    clientId: text('client_id'),    chatConfig: jsonb('chat_config').$type<LobeAgentChatConfig>(),    ...timestamps,  },  (t) => [uniqueIndex('client_id_user_id_unique').on(t.clientId, t.userId)],);

export const agentsKnowledgeBases = pgTable(  'agents_knowledge_bases',  {    id: uuid('id').defaultRandom().notNull().primaryKey(),    agentId: text('agent_id')      .references(() => agents.id, { onDelete: 'cascade' })      .notNull(),    knowledgeBaseId: text('knowledge_base_id')      .references(() => knowledgeBases.id, { onDelete: 'cascade' })      .notNull(),    userId: text('user_id')      .references(() => users.id, { onDelete: 'cascade' })      .notNull(),    enabled: boolean('enabled').default(true),    ...timestamps,  },  (t) => [    uniqueIndex('agents_knowledge_bases_agent_id_knowledge_base_id_unique').on(      t.agentId,      t.knowledgeBaseId,    ),  ],);

// ✅ Goodconst [result] = await this.db.select().from(agents).where(eq(agents.id, id)).limit(1);return result;
// ❌ Bad: relational APIreturn this.db.query.agents.findFirst({  where: eq(agents.id, id),});

// ✅ Good: explicit select + leftJoinconst rows = await this.db  .select({    runId: agentEvalRunTopics.runId,    score: agentEvalRunTopics.score,    testCase: agentEvalTestCases,    topic: topics,  })  .from(agentEvalRunTopics)  .leftJoin(agentEvalTestCases, eq(agentEvalRunTopics.testCaseId, agentEvalTestCases.id))  .leftJoin(topics, eq(agentEvalRunTopics.topicId, topics.id))  .where(eq(agentEvalRunTopics.runId, runId))  .orderBy(asc(agentEvalRunTopics.createdAt));
// ❌ Bad: relational API with `with:`return this.db.query.agentEvalRunTopics.findMany({  where: eq(agentEvalRunTopics.runId, runId),  with: { testCase: true, topic: true },});

// ✅ Good: select + leftJoin + groupByconst rows = await this.db  .select({    id: agentEvalDatasets.id,    name: agentEvalDatasets.name,    testCaseCount: count(agentEvalTestCases.id).as('testCaseCount'),  })  .from(agentEvalDatasets)  .leftJoin(agentEvalTestCases, eq(agentEvalDatasets.id, agentEvalTestCases.datasetId))  .groupBy(agentEvalDatasets.id);

// ✅ Scalars included only when present; SQL hidden behind a named helper.// (`userWidgetLogs` is illustrative — swap for the real table.)const updateValues = compactUndefined({  email: record.email ?? undefined,  ip: record.ip ?? undefined,});await db.insert(userWidgetLogs).values(values).onConflictDoUpdate({  set: { ...updateValues, events: appendJsonbArray(userWidgetLogs.events, event), updatedAt: now },  target: userWidgetLogs.id,});
// ❌ Every scalar becomes SQL plumbing.set: {  email: sql`COALESCE(excluded.email, ${userWidgetLogs.email})`,  ip: sql`COALESCE(excluded.ip, ${userWidgetLogs.ip})`,}

interface TaskTreeRow {  id: string;  parent_task_id: string | null;}
// execute<T> acceptable: no clean WITH RECURSIVE builder. Keep schema refs in the// interpolations and scope every leg to the user.const { rows } = await db.execute<TaskTreeRow>(sql`  WITH RECURSIVE task_tree AS (    SELECT ${tasks.id}, ${tasks.parentTaskId}    FROM ${tasks}    WHERE ${tasks.id} = ${rootTaskId} AND ${tasks.createdByUserId} = ${userId}    UNION ALL    SELECT ${tasks.id}, ${tasks.parentTaskId}    FROM ${tasks}    JOIN task_tree ON ${tasks.parentTaskId} = task_tree.id    WHERE ${tasks.createdByUserId} = ${userId}  )  SELECT * FROM task_tree`);

// ✅ Good: two simple queriesconst [dataset] = await this.db  .select()  .from(agentEvalDatasets)  .where(eq(agentEvalDatasets.id, id))  .limit(1);
if (!dataset) return undefined;
const testCases = await this.db  .select()  .from(agentEvalTestCases)  .where(eq(agentEvalTestCases.datasetId, id))  .orderBy(asc(agentEvalTestCases.sortOrder));
return { ...dataset, testCases };