---
description: 
globs: src/database/schemas/*
alwaysApply: false
---
# Drizzle ORM Schema Style Guide for lobe-chat

This document outlines the conventions and best practices for defining PostgreSQL Drizzle ORM schemas within the lobe-chat project.

## Configuration

- Drizzle configuration is managed in [drizzle.config.ts](mdc:drizzle.config.ts)
- Schema files are located in the src/database/schemas/ directory
- Migration files are output to `src/database/migrations/`
- The project uses `postgresql` dialect with `strict: true`

## Helper Functions

Commonly used column definitions, especially for timestamps, are centralized in [src/database/schemas/_helpers.ts](mdc:src/database/schemas/_helpers.ts):
- `timestamptz(name: string)`: Creates a timestamp column with timezone
- `createdAt()`, `updatedAt()`, `accessedAt()`: Helper functions for standard timestamp columns
- `timestamps`: An object `{ createdAt, updatedAt, accessedAt }` for easy inclusion in table definitions

## Naming Conventions

- **Table Names**: Use plural snake_case (e.g., `users`, `agents`, `session_groups`)
- **Column Names**: Use snake_case (e.g., `user_id`, `created_at`, `background_color`)

## Column Definitions

### Primary Keys (PKs)
- Typically `text('id')` (or `varchar('id')` for some OIDC tables)
- Often use `.$defaultFn(() => idGenerator('table_name'))` for automatic ID generation with meaningful prefixes
- **ID Prefix Purpose**: Makes it easy for users and developers to distinguish different entity types at a glance
- For internal/system tables that users don't need to see, can use `uuid` or auto-increment keys
- Composite PKs are defined using `primaryKey({ columns: [t.colA, t.colB] })`

### Foreign Keys (FKs)
- Defined using `.references(() => otherTable.id, { onDelete: 'cascade' | 'set null' | 'no action' })`
- FK columns are usually named `related_table_singular_name_id` (e.g., `user_id` references `users.id`)
- Most tables include a `user_id` column referencing `users.id` with `onDelete: 'cascade'`

### Timestamps
- Consistently use the `...timestamps` spread from [_helpers.ts](mdc:src/database/schemas/_helpers.ts) for `created_at`, `updated_at`, and `accessed_at` columns

### Default Values
- `.$defaultFn(() => expression)` for dynamic defaults (e.g., `idGenerator()`, `randomSlug()`)
- `.default(staticValue)` for static defaults (e.g., `boolean('enabled').default(true)`)

### Indexes
- Defined in the table's second argument: `pgTable('name', {...columns}, (t) => ({ indexName: indexType().on(...) }))`
- Use `uniqueIndex()` for unique constraints and `index()` for non-unique indexes
- Naming pattern: `table_name_column(s)_idx` or `table_name_column(s)_unique`
- Many tables feature a `clientId: text('client_id')` column, often part of a composite unique index with `user_id`

### Data Types
- Common types: `text`, `varchar`, `jsonb`, `boolean`, `integer`, `uuid`, `pgTable`
- For `jsonb` fields, specify the TypeScript type using `.$type<MyType>()` for better type safety

## Zod Schemas & Type Inference

- Utilize `drizzle-zod` to generate Zod schemas for validation:
  - `createInsertSchema(tableName)`
  - `createSelectSchema(tableName)` (less common)
- Export inferred types: `export type NewEntity = typeof tableName.$inferInsert;` and `export type EntityItem = typeof tableName.$inferSelect;`

## Relations

- Table relationships are defined centrally in [src/database/schemas/relations.ts](mdc:src/database/schemas/relations.ts) using the `relations()` utility from `drizzle-orm`

## Code Style & Structure

- **File Organization**: Each main database entity typically has its own schema file (e.g., [user.ts](mdc:src/database/schemas/user.ts), [agent.ts](mdc:src/database/schemas/agent.ts))
- All schemas are re-exported from [src/database/schemas/index.ts](mdc:src/database/schemas/index.ts)
- **ESLint**: Files often start with `/* eslint-disable sort-keys-fix/sort-keys-fix */`
- **Comments**: Use JSDoc-style comments to explain the purpose of tables and complex columns, fields that are self-explanatory do not require jsdoc explanations, such as id, user_id, etc.

## Example Pattern

```typescript
// From src/database/schemas/agent.ts
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,
  },
  // return array instead of object, the object style is 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;
```

## Common Patterns

### 1. userId + clientId Pattern (Legacy)
Some existing tables include both fields for different purposes:

```typescript
// Example from agents table (legacy pattern)
userId: text('user_id')
  .references(() => users.id, { onDelete: 'cascade' })
  .notNull(),
clientId: text('client_id'),

// Usually with a composite unique index
clientIdUnique: uniqueIndex('agents_client_id_user_id_unique').on(t.clientId, t.userId),
```

- **`userId`**: Server-side user association, ensures data belongs to specific user
- **`clientId`**: Unique key for import/export operations, supports data migration between instances
- **Current Status**: New tables should NOT include `clientId` unless specifically needed for import/export functionality
- **Note**: This pattern is being phased out for new features to simplify the schema

### 2. Junction Tables (Many-to-Many Relationships)
Use composite primary keys for relationship tables:

```typescript
// Example: agents_knowledge_bases (from agent.ts)
export const agentsKnowledgeBases = pgTable(
  'agents_knowledge_bases',
  {
    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) => [
    primaryKey({ columns: [t.agentId, t.knowledgeBaseId] }),
  ],
);
```

**Pattern**: `{entity1}Id` + `{entity2}Id` as composite PK, plus `userId` for ownership

### 3. OIDC Tables Special Patterns
OIDC tables use `varchar` IDs instead of `text` with custom generators:

```typescript
// Example from oidc.ts
export const oidcAuthorizationCodes = pgTable('oidc_authorization_codes', {
  id: varchar('id', { length: 255 }).primaryKey(), // varchar not text
  data: jsonb('data').notNull(),
  expiresAt: timestamptz('expires_at').notNull(),
  // ... other fields
});
```

**Reason**: OIDC standards expect specific ID formats and lengths

### 4. File Processing with Async Tasks
File-related tables reference async task IDs for background processing:

```typescript
// Example from files table
export const files = pgTable('files', {
  // ... other fields
  chunkTaskId: uuid('chunk_task_id').references(() => asyncTasks.id, { onDelete: 'set null' }),
  embeddingTaskId: uuid('embedding_task_id').references(() => asyncTasks.id, { onDelete: 'set null' }),
  // ...
});
```

**Purpose**: 
- Track file chunking progress (breaking files into smaller pieces)
- Track embedding generation progress (converting text to vectors)
- Allow querying task status and handling failures

### 5. Slug Pattern (Legacy)
Some entities include auto-generated slugs - this is legacy code:

```typescript
slug: varchar('slug', { length: 100 })
  .$defaultFn(() => randomSlug(4))
  .unique(),

// Often with composite unique constraint
slugUserIdUnique: uniqueIndex('slug_user_id_unique').on(t.slug, t.userId),
```

**Current usage**: Only used to identify default agents/sessions (legacy pattern)
**Future refactor**: Will likely be replaced with `isDefault: boolean()` field
**Note**: Avoid using slugs for new features - prefer explicit boolean flags for status tracking

By following these guidelines, maintain consistency, type safety, and maintainability across database schema definitions.