Database Schema
Relevant source files
The following files were used as context for generating this wiki page:
- packages/database/src/schemas/index.ts
- packages/database/src/schemas/relations.ts
- packages/database/src/schemas/agent.ts
- packages/database/src/schemas/message.ts
- packages/database/src/schemas/knowledgeBase.ts
- drizzle.config.ts
- packages/database/src/schemas/file.ts
- packages/database/src/schemas/chunk.ts
- packages/database/src/schemas/session.ts
- packages/database/src/schemas/user.ts
- scripts/db-verify-schema.ts
The LobeChat database schema is designed to manage all persistent data for the application, including user information, chat sessions, messages, agents, and knowledge bases. It is implemented using Drizzle ORM and targets a PostgreSQL database. The schema is organized into multiple files, each defining a specific table, with a central relations.ts file establishing the connections between them.
This structure supports core application features such as multi-session chat, customizable AI agents, file management for knowledge augmentation, and user-specific data isolation. The use of foreign keys ensures data integrity across related entities.
Entity Relationship Diagram
The following diagram provides a high-level overview of all the tables in the database and their relationships.
Core Table Schemas
The database is composed of several core tables that store the application's primary data.
users
Stores user account information, including authentication details and preferences.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | varchar | Primary Key | Unique identifier for the user. |
avatar | varchar(255) | - | URL to the user's avatar image. |
email | varchar(255) | Unique | User's email address. |
email_verified | boolean | Default: false | Flag indicating if the email is verified. |
nickname | varchar(255) | - | User's display name. |
password | varchar(255) | - | Hashed password for the user. |
settings | jsonb | - | JSON object for storing user-specific settings. |
created_at | timestamp | Not Null, Default: now() | Timestamp of user creation. |
updated_at | timestamp | Not Null, Default: now() | Timestamp of the last update. |
| Sources: packages/database/src/schemas/user.ts |
sessions
Represents a chat session. Each session is associated with a user and can belong to a session group.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | varchar | Primary Key | Unique identifier for the session. |
user_id | varchar | Foreign Key (users.id) | The user who owns the session. |
group_id | varchar | Foreign Key (session_groups.id) | The group this session belongs to. |
type | varchar | Not Null, Default: 'agent' | The type of session (e.g., 'agent'). |
meta | jsonb | - | Metadata for the session (e.g., title, description). |
pinned | boolean | Default: false | Whether the session is pinned. |
created_at | timestamp | Not Null, Default: now() | Timestamp of session creation. |
updated_at | timestamp | Not Null, Default: now() | Timestamp of the last update. |
| Sources: packages/database/src/schemas/session.ts |
agents
Stores the configuration for AI agents, which are customizable personas for the chat.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | varchar | Primary Key | Unique identifier for the agent. |
user_id | varchar | Foreign Key (users.id) | The user who created the agent. |
config | jsonb | - | Configuration for the agent (model, params, system role, etc.). |
meta | jsonb | - | Metadata for the agent (title, description, avatar, etc.). |
background_color | varchar(255) | - | Custom background color for the agent's UI. |
created_at | timestamp | Not Null, Default: now() | Timestamp of agent creation. |
updated_at | timestamp | Not Null, Default: now() | Timestamp of the last update. |
| Sources: packages/database/src/schemas/agent.ts |
messages
Contains the content of each message within a chat session. Messages are linked in a threaded structure.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | varchar | Primary Key | Unique identifier for the message. |
user_id | varchar | Foreign Key (users.id) | The user associated with the message. |
session_id | varchar | Foreign Key (sessions.id) | The session this message belongs to. |
topic_id | varchar | Foreign Key (topics.id) | The topic within the session. |
parent_id | varchar | Foreign Key (messages.id) | The parent message in a thread. |
role | varchar | Not Null | The role of the message author (e.g., 'user', 'assistant', 'tool'). |
content | text | - | The text content of the message. |
tools | jsonb | - | JSON object for tool calls associated with the message. |
created_at | timestamp | Not Null, Default: now() | Timestamp of message creation. |
updated_at | timestamp | Not Null, Default: now() | Timestamp of the last update. |
| Sources: packages/database/src/schemas/message.ts |
Knowledge Base Schemas
These tables manage files, their chunked content, and vector embeddings for retrieval-augmented generation (RAG).
Knowledge Base Data Flow
The diagram below illustrates how files are processed and stored for use in a knowledge base.
knowledge_bases
Defines a knowledge base, which is a collection of files.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | varchar | Primary Key | Unique identifier for the knowledge base. |
user_id | varchar | Foreign Key (users.id) | The user who owns the knowledge base. |
name | varchar | Not Null | The name of the knowledge base. |
description | text | - | A description of the knowledge base. |
avatar | varchar | - | An avatar or icon for the knowledge base. |
| Sources: packages/database/src/schemas/knowledgeBase.ts:10-23 |
files & knowledge_base_files
The files table stores metadata about each uploaded file. The knowledge_base_files table is a join table that links files to one or more knowledge bases.
| Table | Column | Type | Constraints | Description |
|---|---|---|---|---|
files | id | varchar | Primary Key | Unique identifier for the file. |
user_id | varchar | Foreign Key (users.id) | The user who uploaded the file. | |
name | varchar | Not Null | The name of the file. | |
size | integer | Not Null | File size in bytes. | |
file_type | varchar | Not Null | The MIME type of the file. | |
file_hash | varchar | - | A hash of the file content for deduplication. | |
knowledge_base_files | knowledge_base_id | varchar | PK, Foreign Key | Links to the knowledge_bases table. |
file_id | varchar | PK, Foreign Key | Links to the files table. | |
user_id | varchar | Foreign Key (users.id) | The user who made the association. | |
| Sources: packages/database/src/schemas/file.ts:13-33, packages/database/src/schemas/knowledgeBase.ts:25-36 |
chunks & embeddings
The chunks table stores smaller pieces of text extracted from files. The embeddings table stores the vector representations of these chunks, which are used for semantic search.
| Table | Column | Type | Constraints | Description |
|---|---|---|---|---|
chunks | id | varchar | Primary Key | Unique identifier for the chunk. |
user_id | varchar | Foreign Key (users.id) | The user who owns the chunk. | |
text | text | - | The text content of the chunk. | |
embeddings | chunk_id | varchar | PK, Foreign Key | Links to the chunks table. |
user_id | varchar | Foreign Key (users.id) | The user who owns the embedding. | |
embeddings | vector(1536) | - | The 1536-dimension vector embedding. | |
model | varchar | Not Null | The model used to generate the embedding. | |
| Sources: packages/database/src/schemas/chunk.ts:11-46 |
Drizzle ORM Configuration
The project uses Drizzle ORM for database interactions. The configuration is defined in drizzle.config.ts, which specifies the schema location and the output directory for migrations.
// drizzle.config.ts
export default {
dbCredentials: {
url: process.env.DATABASE_URL!,
},
driver: 'pg',
out: './packages/database/migrations',
schema: './packages/database/src/schemas/index.ts',
} satisfies Config;
This configuration tells Drizzle Kit to look for all schema definitions exported from packages/database/src/schemas/index.ts and to place generated migration files into the packages/database/migrations directory.
Sources: drizzle.config.ts:1-9