fix: update sql

docs/development/database-schema.dbml

@@ -1,3 +1,33 @@
+table agent_versions {
+  id uuid [pk, not null, default: `gen_random_uuid()`]
+  agent_id text [not null]
+  user_id text [not null]
+  version integer [not null]
+  title varchar(255)
+  description varchar(1000)
+  tags jsonb
+  editor_data jsonb
+  avatar text
+  background_color text
+  plugins jsonb
+  chat_config jsonb
+  few_shots jsonb
+  model text
+  params jsonb
+  provider text
+  system_role text
+  tts jsonb
+  opening_message text
+  opening_questions text[]
+  change_message text
+  created_at "timestamp with time zone" [not null, default: `now()`]
+
+  indexes {
+    agent_id [name: 'agent_versions_agent_id_idx']
+    (agent_id, version) [name: 'agent_versions_agent_version_unique', unique]
+  }
+}
+
 table agents {
   id text [pk, not null]
   slug varchar(100)
@@ -1202,6 +1232,8 @@ ref: auth_sessions.user_id > users.id
 
 ref: two_factor.user_id > users.id
 
+ref: agent_versions.agent_id > agents.id
+
 ref: agents_files.file_id > files.id
 
 ref: agents_files.agent_id > agents.id

packages/database/migrations/0060_add_agent_versions_table.sql

@@ -0,0 +1,46 @@
+CREATE TABLE IF NOT EXISTS "agent_versions" (
+	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
+	"agent_id" text NOT NULL,
+	"user_id" text NOT NULL,
+	"version" integer NOT NULL,
+	"title" varchar(255),
+	"description" varchar(1000),
+	"tags" jsonb,
+	"editor_data" jsonb,
+	"avatar" text,
+	"background_color" text,
+	"plugins" jsonb,
+	"chat_config" jsonb,
+	"few_shots" jsonb,
+	"model" text,
+	"params" jsonb,
+	"provider" text,
+	"system_role" text,
+	"tts" jsonb,
+	"opening_message" text,
+	"opening_questions" text[],
+	"change_message" text,
+	"created_at" timestamp with time zone DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+DO $$
+BEGIN
+    IF NOT EXISTS (
+        SELECT 1 FROM pg_constraint WHERE conname = 'agent_versions_agent_id_agents_id_fk'
+    ) THEN
+        ALTER TABLE "agent_versions" ADD CONSTRAINT "agent_versions_agent_id_agents_id_fk" FOREIGN KEY ("agent_id") REFERENCES "public"."agents"("id") ON DELETE cascade ON UPDATE no action;
+    END IF;
+END $$;
+--> statement-breakpoint
+DO $$
+BEGIN
+    IF NOT EXISTS (
+        SELECT 1 FROM pg_constraint WHERE conname = 'agent_versions_user_id_users_id_fk'
+    ) THEN
+        ALTER TABLE "agent_versions" ADD CONSTRAINT "agent_versions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
+    END IF;
+END $$;
+--> statement-breakpoint
+CREATE INDEX IF NOT EXISTS "agent_versions_agent_id_idx" ON "agent_versions" USING btree ("agent_id");
+--> statement-breakpoint
+CREATE UNIQUE INDEX IF NOT EXISTS "agent_versions_agent_version_unique" ON "agent_versions" USING btree ("agent_id","version");

packages/database/migrations/meta/_journal.json

@@ -420,6 +420,13 @@
       "when": 1764858574403,
       "tag": "0059_add_normalized_email_indexes",
       "breakpoints": true
+    },
+    {
+      "idx": 60,
+      "version": "7",
+      "when": 1765197497275,
+      "tag": "0060_add_agent_versions_table",
+      "breakpoints": true
     }
   ],
   "version": "6"

packages/database/src/core/migrations.json

@@ -223,7 +223,10 @@
     "hash": "9646161fa041354714f823d726af27247bcd6e60fa3be5698c0d69f337a5700b"
   },
   {
-    "sql": ["DROP TABLE \"user_budgets\";", "\nDROP TABLE \"user_subscriptions\";"],
+    "sql": [
+      "DROP TABLE \"user_budgets\";",
+      "\nDROP TABLE \"user_subscriptions\";"
+    ],
     "bps": true,
     "folderMillis": 1729699958471,
     "hash": "7dad43a2a25d1aec82124a4e53f8d82f8505c3073f23606c1dc5d2a4598eacf9"
@@ -295,7 +298,9 @@
     "hash": "845a692ceabbfc3caf252a97d3e19a213bc0c433df2689900135f9cfded2cf49"
   },
   {
-    "sql": ["ALTER TABLE \"messages\" ADD COLUMN \"reasoning\" jsonb;"],
+    "sql": [
+      "ALTER TABLE \"messages\" ADD COLUMN \"reasoning\" jsonb;"
+    ],
     "bps": true,
     "folderMillis": 1737609172353,
     "hash": "2cb36ae4fcdd7b7064767e04bfbb36ae34518ff4bb1b39006f2dd394d1893868"
@@ -510,7 +515,9 @@
     "hash": "a7ccf007fd185ff922823148d1eae6fafe652fc98d2fd2793f84a84f29e93cd1"
   },
   {
-    "sql": ["ALTER TABLE \"ai_providers\" ADD COLUMN \"config\" jsonb;"],
+    "sql": [
+      "ALTER TABLE \"ai_providers\" ADD COLUMN \"config\" jsonb;"
+    ],
     "bps": true,
     "folderMillis": 1749309388370,
     "hash": "39cea379f08ee4cb944875c0b67f7791387b508c2d47958bb4cd501ed1ef33eb"
@@ -628,7 +635,9 @@
     "hash": "1ba9b1f74ea13348da98d6fcdad7867ab4316ed565bf75d84d160c526cdac14b"
   },
   {
-    "sql": ["ALTER TABLE \"agents\" ADD COLUMN IF NOT EXISTS \"virtual\" boolean DEFAULT false;"],
+    "sql": [
+      "ALTER TABLE \"agents\" ADD COLUMN IF NOT EXISTS \"virtual\" boolean DEFAULT false;"
+    ],
     "bps": true,
     "folderMillis": 1759116400580,
     "hash": "433ddae88e785f2db734e49a4c115eee93e60afe389f7919d66e5ba9aa159a37"
@@ -678,13 +687,17 @@
     "hash": "4bdc6505797d7a33b622498c138cfd47f637239f6905e1c484cd01d9d5f21d6b"
   },
   {
-    "sql": ["ALTER TABLE \"user_settings\" ADD COLUMN IF NOT EXISTS \"image\" jsonb;"],
+    "sql": [
+      "ALTER TABLE \"user_settings\" ADD COLUMN IF NOT EXISTS \"image\" jsonb;"
+    ],
     "bps": true,
     "folderMillis": 1760108430562,
     "hash": "ce09b301abb80f6563abc2f526bdd20b4f69bae430f09ba2179b9e3bfec43067"
   },
   {
-    "sql": ["ALTER TABLE \"documents\" ADD COLUMN IF NOT EXISTS \"editor_data\" jsonb;"],
+    "sql": [
+      "ALTER TABLE \"documents\" ADD COLUMN IF NOT EXISTS \"editor_data\" jsonb;"
+    ],
     "bps": true,
     "folderMillis": 1761554153406,
     "hash": "bf2f21293e90e11cf60a784cf3ec219eafa95f7545d7d2f9d1449c0b0949599a"
@@ -764,13 +777,17 @@
     "hash": "923ccbdf46c32be9a981dabd348e6923b4a365444241e9b8cc174bf5b914cbc5"
   },
   {
-    "sql": ["ALTER TABLE \"agents\" ADD COLUMN IF NOT EXISTS \"market_identifier\" text;\n"],
+    "sql": [
+      "ALTER TABLE \"agents\" ADD COLUMN IF NOT EXISTS \"market_identifier\" text;\n"
+    ],
     "bps": true,
     "folderMillis": 1762870034882,
     "hash": "4178aacb4b8892b7fd15d29209bbf9b1d1f9d7c406ba796f27542c0bcd919680"
   },
   {
-    "sql": ["ALTER TABLE \"message_plugins\" ADD COLUMN IF NOT EXISTS \"intervention\" jsonb;\n"],
+    "sql": [
+      "ALTER TABLE \"message_plugins\" ADD COLUMN IF NOT EXISTS \"intervention\" jsonb;\n"
+    ],
     "bps": true,
     "folderMillis": 1762911968658,
     "hash": "552a032cc0e595277232e70b5f9338658585bafe9481ae8346a5f322b673a68b"
@@ -799,7 +816,9 @@
     "hash": "f823b521f4d25e5dc5ab238b372727d2d2d7f0aed27b5eabc8a9608ce4e50568"
   },
   {
-    "sql": ["ALTER TABLE \"agents\" ADD COLUMN IF NOT EXISTS \"editor_data\" jsonb;"],
+    "sql": [
+      "ALTER TABLE \"agents\" ADD COLUMN IF NOT EXISTS \"editor_data\" jsonb;"
+    ],
     "bps": true,
     "folderMillis": 1764215503726,
     "hash": "4188893a9083b3c7baebdbad0dd3f9d9400ede7584ca2394f5c64305dc9ec7b0"
@@ -840,7 +859,9 @@
     "hash": "2c103eee82bdf329944fb622dd9c2b9f20df80eb54f23eb9254d2285de413099"
   },
   {
-    "sql": ["ALTER TABLE \"user_settings\" ADD COLUMN IF NOT EXISTS \"market\" jsonb;"],
+    "sql": [
+      "ALTER TABLE \"user_settings\" ADD COLUMN IF NOT EXISTS \"market\" jsonb;"
+    ],
     "bps": true,
     "folderMillis": 1764335703306,
     "hash": "28c0d738c0b1fdf5fd871363be1a1477b4accbabdc140fe8dc6e9b339aae2c89"
@@ -938,5 +959,17 @@
     "bps": true,
     "folderMillis": 1764858574403,
     "hash": "7838f9938b370867470e5e11807855253d23b11c2ac6aa9e90687844a356c949"
+  },
+  {
+    "sql": [
+      "CREATE TABLE IF NOT EXISTS \"agent_versions\" (\n\t\"id\" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,\n\t\"agent_id\" text NOT NULL,\n\t\"user_id\" text NOT NULL,\n\t\"version\" integer NOT NULL,\n\t\"title\" varchar(255),\n\t\"description\" varchar(1000),\n\t\"tags\" jsonb,\n\t\"editor_data\" jsonb,\n\t\"avatar\" text,\n\t\"background_color\" text,\n\t\"plugins\" jsonb,\n\t\"chat_config\" jsonb,\n\t\"few_shots\" jsonb,\n\t\"model\" text,\n\t\"params\" jsonb,\n\t\"provider\" text,\n\t\"system_role\" text,\n\t\"tts\" jsonb,\n\t\"opening_message\" text,\n\t\"opening_questions\" text[],\n\t\"change_message\" text,\n\t\"created_at\" timestamp with time zone DEFAULT now() NOT NULL\n);\n",
+      "\nDO $$\nBEGIN\n    IF NOT EXISTS (\n        SELECT 1 FROM pg_constraint WHERE conname = 'agent_versions_agent_id_agents_id_fk'\n    ) THEN\n        ALTER TABLE \"agent_versions\" ADD CONSTRAINT \"agent_versions_agent_id_agents_id_fk\" FOREIGN KEY (\"agent_id\") REFERENCES \"public\".\"agents\"(\"id\") ON DELETE cascade ON UPDATE no action;\n    END IF;\nEND $$;\n",
+      "\nDO $$\nBEGIN\n    IF NOT EXISTS (\n        SELECT 1 FROM pg_constraint WHERE conname = 'agent_versions_user_id_users_id_fk'\n    ) THEN\n        ALTER TABLE \"agent_versions\" ADD CONSTRAINT \"agent_versions_user_id_users_id_fk\" FOREIGN KEY (\"user_id\") REFERENCES \"public\".\"users\"(\"id\") ON DELETE cascade ON UPDATE no action;\n    END IF;\nEND $$;\n",
+      "\nCREATE INDEX IF NOT EXISTS \"agent_versions_agent_id_idx\" ON \"agent_versions\" USING btree (\"agent_id\");\n",
+      "\nCREATE UNIQUE INDEX IF NOT EXISTS \"agent_versions_agent_version_unique\" ON \"agent_versions\" USING btree (\"agent_id\",\"version\");\n"
+    ],
+    "bps": true,
+    "folderMillis": 1765197497275,
+    "hash": "1541de87b912f226868ca93528205d40c5cf28121cf9faf3f20ce219b0167aa6"
   }
 ]

packages/database/src/repositories/tableViewer/index.test.ts

@@ -23,7 +23,7 @@ describe('TableViewerRepo', () => {
     it('should return all tables with counts', async () => {
       const result = await repo.getAllTables();
 
-      expect(result.length).toEqual(72);
+      expect(result.length).toEqual(73);
       expect(result[0]).toEqual({ name: 'accounts', count: 0, type: 'BASE TABLE' });
     });
 

packages/database/src/schemas/agent.ts

@@ -3,17 +3,19 @@ import type { LobeAgentChatConfig, LobeAgentTTSConfig } from '@lobechat/types';
 import {
   boolean,
   index,
+  integer,
   jsonb,
   pgTable,
   primaryKey,
   text,
   uniqueIndex,
+  uuid,
   varchar,
 } from 'drizzle-orm/pg-core';
 import { createInsertSchema } from 'drizzle-zod';
 
 import { idGenerator, randomSlug } from '../utils/idGenerator';
-import { timestamps } from './_helpers';
+import { createdAt, timestamps } from './_helpers';
 import { files, knowledgeBases } from './file';
 import { users } from './user';
 
@@ -117,3 +119,57 @@ export const agentsFiles = pgTable(
     index('agents_files_agent_id_idx').on(t.agentId),
   ],
 );
+
+// Agent versions table for tracking agent configuration history
+export const agentVersions = pgTable(
+  'agent_versions',
+  {
+    id: uuid('id').primaryKey().defaultRandom().notNull(),
+
+    agentId: text('agent_id')
+      .references(() => agents.id, { onDelete: 'cascade' })
+      .notNull(),
+
+    userId: text('user_id')
+      .references(() => users.id, { onDelete: 'cascade' })
+      .notNull(),
+
+    version: integer('version').notNull(),
+
+    // Snapshot of agent configuration at this version
+    title: varchar('title', { length: 255 }),
+    description: varchar('description', { length: 1000 }),
+    tags: jsonb('tags').$type<string[]>(),
+    editorData: jsonb('editor_data'),
+    avatar: text('avatar'),
+    backgroundColor: text('background_color'),
+
+    plugins: jsonb('plugins').$type<string[]>(),
+
+    chatConfig: jsonb('chat_config').$type<LobeAgentChatConfig>(),
+
+    fewShots: jsonb('few_shots'),
+    model: text('model'),
+    params: jsonb('params'),
+    provider: text('provider'),
+    systemRole: text('system_role'),
+    tts: jsonb('tts').$type<LobeAgentTTSConfig>(),
+
+    openingMessage: text('opening_message'),
+    openingQuestions: text('opening_questions').array(),
+
+    // Change information
+    changeMessage: text('change_message'),
+
+    createdAt: createdAt(),
+  },
+  (t) => [
+    index('agent_versions_agent_id_idx').on(t.agentId),
+    uniqueIndex('agent_versions_agent_version_unique').on(t.agentId, t.version),
+  ],
+);
+
+export const insertAgentVersionSchema = createInsertSchema(agentVersions);
+
+export type NewAgentVersion = typeof agentVersions.$inferInsert;
+export type AgentVersionItem = typeof agentVersions.$inferSelect;

packages/database/src/schemas/relations.ts

@@ -3,7 +3,7 @@ import { relations } from 'drizzle-orm';
 import { index, pgTable, primaryKey, text, uuid, varchar } from 'drizzle-orm/pg-core';
 
 import { createdAt } from './_helpers';
-import { agents, agentsFiles, agentsKnowledgeBases } from './agent';
+import { agents, agentsFiles, agentsKnowledgeBases, agentVersions } from './agent';
 import { asyncTasks } from './asyncTask';
 import { chatGroups, chatGroupsAgents } from './chatGroup';
 import { documents, files, knowledgeBases } from './file';
@@ -117,6 +117,7 @@ export const agentsRelations = relations(agents, ({ many }) => ({
   knowledgeBases: many(agentsKnowledgeBases),
   files: many(agentsFiles),
   chatGroups: many(chatGroupsAgents),
+  versions: many(agentVersions),
 }));
 
 export const agentsToSessionsRelations = relations(agentsToSessions, ({ one }) => ({
@@ -163,6 +164,13 @@ export const agentsFilesRelations = relations(agentsFiles, ({ one }) => ({
   }),
 }));
 
+export const agentVersionsRelations = relations(agentVersions, ({ one }) => ({
+  agent: one(agents, {
+    fields: [agentVersions.agentId],
+    references: [agents.id],
+  }),
+}));
+
 export const messagesFilesRelations = relations(messagesFiles, ({ one }) => ({
   file: one(files, {
     fields: [messagesFiles.fileId],