🗃️ feat(database): add workspace ai-infra unique constraints migration and schema

docs/development/database-schema.dbml

@@ -415,6 +415,7 @@ table agent_skills {
 }
 
 table ai_models {
+  _id uuid [pk, not null, default: `gen_random_uuid()`]
   id varchar(150) [not null]
   display_name varchar(200)
   description text
@@ -438,12 +439,14 @@ table ai_models {
   updated_at "timestamp with time zone" [not null, default: `now()`]
 
   indexes {
-    (id, provider_id, user_id) [pk]
+    (id, provider_id, user_id) [name: 'ai_models_id_provider_id_user_id_unique', unique]
+    (id, provider_id, user_id, workspace_id) [name: 'ai_models_id_provider_id_user_id_workspace_id_unique', unique]
     user_id [name: 'ai_models_user_id_idx']
   }
 }
 
 table ai_providers {
+  _id uuid [pk, not null, default: `gen_random_uuid()`]
   id varchar(64) [not null]
   name text
   user_id text [not null]
@@ -463,7 +466,8 @@ table ai_providers {
   updated_at "timestamp with time zone" [not null, default: `now()`]
 
   indexes {
-    (id, user_id) [pk]
+    (id, user_id) [name: 'ai_providers_id_user_id_unique', unique]
+    (id, user_id, workspace_id) [name: 'ai_providers_id_user_id_workspace_id_unique', unique]
     user_id [name: 'ai_providers_user_id_idx']
   }
 }

packages/database/migrations/0110_ai_infra_workspace_unique_constraints.sql

@@ -0,0 +1,12 @@
+-- Replace the composite primary keys on ai_providers / ai_models with a dedicated
+-- surrogate uuid "_id" primary key, and split the business-key uniqueness between
+-- personal and workspace scopes so workspace-scoped upserts can use matching
+-- ON CONFLICT targets.
+ALTER TABLE "ai_models" DROP CONSTRAINT IF EXISTS "ai_models_id_provider_id_user_id_pk";--> statement-breakpoint
+ALTER TABLE "ai_providers" DROP CONSTRAINT IF EXISTS "ai_providers_id_user_id_pk";--> statement-breakpoint
+ALTER TABLE "ai_models" ADD COLUMN IF NOT EXISTS "_id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL;--> statement-breakpoint
+ALTER TABLE "ai_providers" ADD COLUMN IF NOT EXISTS "_id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL;--> statement-breakpoint
+CREATE UNIQUE INDEX IF NOT EXISTS "ai_providers_id_user_id_unique" ON "ai_providers" USING btree ("id","user_id") WHERE "ai_providers"."workspace_id" is null;--> statement-breakpoint
+CREATE UNIQUE INDEX IF NOT EXISTS "ai_providers_id_user_id_workspace_id_unique" ON "ai_providers" USING btree ("id","user_id","workspace_id") WHERE "ai_providers"."workspace_id" is not null;--> statement-breakpoint
+CREATE UNIQUE INDEX IF NOT EXISTS "ai_models_id_provider_id_user_id_unique" ON "ai_models" USING btree ("id","provider_id","user_id") WHERE "ai_models"."workspace_id" is null;--> statement-breakpoint
+CREATE UNIQUE INDEX IF NOT EXISTS "ai_models_id_provider_id_user_id_workspace_id_unique" ON "ai_models" USING btree ("id","provider_id","user_id","workspace_id") WHERE "ai_models"."workspace_id" is not null;

packages/database/migrations/meta/_journal.json

@@ -770,6 +770,13 @@
       "when": 1780571940776,
       "tag": "0109_migrate_unique_constraints",
       "breakpoints": true
+    },
+    {
+      "idx": 110,
+      "version": "7",
+      "when": 1780575940776,
+      "tag": "0110_ai_infra_workspace_unique_constraints",
+      "breakpoints": true
     }
   ],
   "version": "6"

packages/database/src/schemas/aiInfra.ts

@@ -1,12 +1,14 @@
 import type { AiProviderConfig, AiProviderSettings } from '@lobechat/types';
+import { isNotNull, isNull } from 'drizzle-orm';
 import {
   boolean,
   index,
   integer,
   jsonb,
   pgTable,
-  primaryKey,
   text,
+  uniqueIndex,
+  uuid,
   varchar,
 } from 'drizzle-orm/pg-core';
 import type { AiModelSettings } from 'model-bank';
@@ -18,6 +20,7 @@ import { workspaces } from './workspace';
 export const aiProviders = pgTable(
   'ai_providers',
   {
+    _id: uuid('_id').defaultRandom().primaryKey(),
     id: varchar('id', { length: 64 }).notNull(),
     name: text('name'),
 
@@ -48,7 +51,12 @@ export const aiProviders = pgTable(
     ...timestamps,
   },
   (table) => [
-    primaryKey({ columns: [table.id, table.userId] }),
+    uniqueIndex('ai_providers_id_user_id_unique')
+      .on(table.id, table.userId)
+      .where(isNull(table.workspaceId)),
+    uniqueIndex('ai_providers_id_user_id_workspace_id_unique')
+      .on(table.id, table.userId, table.workspaceId)
+      .where(isNotNull(table.workspaceId)),
     index('ai_providers_user_id_idx').on(table.userId),
     index('ai_providers_workspace_id_idx').on(table.workspaceId),
   ],
@@ -60,6 +68,7 @@ export type AiProviderSelectItem = typeof aiProviders.$inferSelect;
 export const aiModels = pgTable(
   'ai_models',
   {
+    _id: uuid('_id').defaultRandom().primaryKey(),
     id: varchar('id', { length: 150 }).notNull(),
     displayName: varchar('display_name', { length: 200 }),
     description: text('description'),
@@ -86,7 +95,12 @@ export const aiModels = pgTable(
     ...timestamps,
   },
   (table) => [
-    primaryKey({ columns: [table.id, table.providerId, table.userId] }),
+    uniqueIndex('ai_models_id_provider_id_user_id_unique')
+      .on(table.id, table.providerId, table.userId)
+      .where(isNull(table.workspaceId)),
+    uniqueIndex('ai_models_id_provider_id_user_id_workspace_id_unique')
+      .on(table.id, table.providerId, table.userId, table.workspaceId)
+      .where(isNotNull(table.workspaceId)),
     index('ai_models_user_id_idx').on(table.userId),
     index('ai_models_workspace_id_idx').on(table.workspaceId),
   ],