stack/docs/scratchpads/128-llm-provider-schema.md

Issue #128: Add LlmProviderInstance Prisma Schema

Objective

Create database schema for LLM provider instance configuration to support multi-provider LLM backend.

Approach

Schema Design

model LlmProviderInstance {
  id            String   @id @default(uuid())
  providerType  String   @map("provider_type")  // "ollama" | "claude" | "openai"
  displayName   String   @map("display_name")
  userId        String?  @map("user_id")        // NULL = system-level, UUID = user-level
  config        Json                             // Provider-specific configuration
  isDefault     Boolean  @default(false) @map("is_default")
  isEnabled     Boolean  @default(true) @map("is_enabled")
  createdAt     DateTime @default(now()) @map("created_at")
  updatedAt     DateTime @updatedAt @map("updated_at")

  user          User?    @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId])
  @@index([providerType])
  @@index([isDefault])
  @@map("llm_provider_instances")
}

Configuration Patterns

System-level instances (user_id = NULL):

• Available to all users
• Managed by admins only
• Example: Shared Ollama server

User-level instances (user_id = UUID):

• Private to specific user
• User manages their own API keys
• Example: Personal OpenAI API key

Config Field Structure

{
  "endpoint": "http://localhost:11434",
  "timeout": 30000,
  "apiKey": "sk-...", // encrypted in production
  "organization": "org-...",
  "customHeaders": {}
}

Progress

• Read current Prisma schema
• Add LlmProviderInstance model
• Add relation to User model
• Add indexes for performance
• Create migration (20260131115600_add_llm_provider_instance)
• Update Prisma client types (prisma generate)
• Run migration on dev database (manual step, requires DB connection)
• Verify migration succeeded (manual step, requires DB connection)

Testing

• Verify model is accessible via Prisma client
• Verify indexes exist in database
• Verify foreign key constraints work
• Test both system and user-level instances

Notes

• Using snake_case for database columns (project convention)
• JSON field for flexible provider configs
• Cascade delete when user is deleted
• isDefault allows marking one provider as default per user

Implementation Details

Model Added:

• LlmProviderInstance with all required fields
• Foreign key relation to User model (nullable for system-level instances)
• Added llmProviders relation array to User model

Indexes Created:

• user_id - Fast lookup by user
• provider_type - Fast filtering by provider
• is_default - Quick default provider lookup
• is_enabled - Filter enabled/disabled providers

Migration File: 20260131115600_add_llm_provider_instance

• Creates table with proper PostgreSQL types
• Sets up foreign key constraint with CASCADE delete
• Creates all performance indexes

Acceptance Criteria Status

• ✅ Migration created successfully
• ✅ Model accessible via Prisma client (prisma generate succeeded)
• ✅ Indexes defined correctly
• ⏸️ Migration not run (requires database connection)
• ⏸️ No data loss risk (new table, no existing data)

Next Steps

When database is available:

pnpm --filter @mosaic/api prisma migrate deploy
# OR for development:
pnpm --filter @mosaic/api prisma migrate dev