# Production-Ready Convex Schema for AI Chat Applications

Building a Claude-like chat application requires a sophisticated schema that handles real-time sync, conversation branching, tool execution, and AI-specific data patterns. This schema integrates **Mastra AI's data models** (threads, agents, memory), **Vercel AI SDK's message structure** (UIMessage parts, tool invocations), and **Convex's reactive patterns** for seamless web and mobile synchronization.

## Core schema architecture spans seven interconnected tables

The design uses a **parent-reference model** for conversation branching, **parts-based message content** aligned with AI SDK 5's UIMessage format, and **separate artifact storage** following Claude's pattern for generated code and documents. Token tracking is embedded at the message level with aggregation support at conversation and user levels.

---

## Complete Convex schema implementation

```typescript
// convex/schema.ts
import { defineSchema, defineTable } from "convex/server";
import { v } from "convex/values";

// ============================================================================
// TYPE DEFINITIONS FOR EXTERNAL USE
// ============================================================================

/**
 * Message roles aligned with AI SDK and Mastra conventions
 */
const messageRoleValidator = v.union(
  v.literal("user"),
  v.literal("assistant"),
  v.literal("system"),
  v.literal("tool")
);

/**
 * Tool invocation states following AI SDK 5 patterns:
 * - input-streaming: Tool call arguments being generated
 * - input-available: Complete input ready for execution
 * - output-available: Tool executed successfully
 * - output-error: Tool execution failed
 */
const toolStateValidator = v.union(
  v.literal("input-streaming"),
  v.literal("input-available"),
  v.literal("output-available"),
  v.literal("output-error")
);

/**
 * Message part types aligned with AI SDK UIMessagePart
 * Each part represents a discrete content unit within a message
 */
const messagePartValidator = v.union(
  // Text content (streaming or complete)
  v.object({
    type: v.literal("text"),
    text: v.string(),
    state: v.optional(v.union(v.literal("streaming"), v.literal("done"))),
  }),
  // Tool invocation with full execution state
  v.object({
    type: v.literal("tool-invocation"),
    toolCallId: v.string(),
    toolName: v.string(),
    state: toolStateValidator,
    input: v.any(), // Tool-specific input (validated at runtime)
    output: v.optional(v.any()),
    errorText: v.optional(v.string()),
    providerExecuted: v.optional(v.boolean()),
  }),
  // File/image content
  v.object({
    type: v.literal("file"),
    mediaType: v.string(),
    filename: v.optional(v.string()),
    url: v.optional(v.string()),
    storageId: v.optional(v.id("_storage")),
  }),
  // Reasoning/chain-of-thought (for models like o1)
  v.object({
    type: v.literal("reasoning"),
    text: v.string(),
    state: v.optional(v.union(v.literal("streaming"), v.literal("done"))),
    providerMetadata: v.optional(v.any()),
  }),
  // Source citations (for RAG/web search)
  v.object({
    type: v.literal("source"),
    sourceType: v.union(v.literal("url"), v.literal("document")),
    id: v.optional(v.string()),
    url: v.optional(v.string()),
    title: v.optional(v.string()),
    documentId: v.optional(v.string()),
  }),
  // Step boundaries for multi-step agent loops
  v.object({
    type: v.literal("step-start"),
    stepNumber: v.optional(v.number()),
  }),
  // Custom data parts (for application-specific content)
  v.object({
    type: v.literal("data"),
    dataType: v.string(),
    id: v.optional(v.string()),
    data: v.any(),
  })
);

/**
 * Artifact types following Claude's artifact patterns
 */
const artifactTypeValidator = v.union(
  v.literal("code"),
  v.literal("document"),
  v.literal("svg"),
  v.literal("react_component"),
  v.literal("html"),
  v.literal("diagram"),
  v.literal("markdown"),
  v.literal("mermaid"),
  v.literal("csv"),
  v.literal("json")
);

// ============================================================================
// SCHEMA DEFINITION
// ============================================================================

export default defineSchema({
  // ==========================================================================
  // USERS AND AUTHENTICATION
  // ==========================================================================
  
  /**
   * Users table - Multi-tenant user management
   * 
   * Design decisions:
   * - tokenIdentifier stores the auth provider's unique ID (Clerk, Auth0, etc.)
   * - Separate profile data allows flexible user attributes
   * - organizationId enables team/enterprise multi-tenancy
   */
  users: defineTable({
    // Auth provider identifier (e.g., Clerk user ID)
    tokenIdentifier: v.string(),
    
    // Profile information
    name: v.optional(v.string()),
    email: v.optional(v.string()),
    avatarUrl: v.optional(v.string()),
    avatarStorageId: v.optional(v.id("_storage")),
    
    // Multi-tenant organization support
    organizationId: v.optional(v.id("organizations")),
    
    // User preferences (model defaults, UI settings)
    preferences: v.optional(v.object({
      defaultModel: v.optional(v.string()),
      theme: v.optional(v.union(v.literal("light"), v.literal("dark"), v.literal("system"))),
      notificationsEnabled: v.optional(v.boolean()),
      defaultSystemPrompt: v.optional(v.string()),
    })),
    
    // Status for presence features
    status: v.optional(v.union(
      v.literal("online"),
      v.literal("offline"),
      v.literal("away")
    )),
    lastSeenAt: v.optional(v.number()),
    
    // Timestamps
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index("by_token", ["tokenIdentifier"])
    .index("by_email", ["email"])
    .index("by_organization", ["organizationId"]),

  /**
   * Organizations - For team/enterprise multi-tenancy
   */
  organizations: defineTable({
    name: v.string(),
    slug: v.string(),
    ownerId: v.id("users"),
    
    // Billing and limits
    plan: v.union(v.literal("free"), v.literal("pro"), v.literal("enterprise")),
    monthlyTokenLimit: v.optional(v.number()),
    
    // Settings
    settings: v.optional(v.object({
      allowedModels: v.optional(v.array(v.string())),
      defaultModel: v.optional(v.string()),
      sharedSystemPrompts: v.optional(v.boolean()),
    })),
    
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index("by_slug", ["slug"])
    .index("by_owner", ["ownerId"]),

  /**
   * Organization memberships - Junction table for user-org relationships
   */
  organizationMemberships: defineTable({
    userId: v.id("users"),
    organizationId: v.id("organizations"),
    role: v.union(v.literal("owner"), v.literal("admin"), v.literal("member")),
    joinedAt: v.number(),
  })
    .index("by_user", ["userId"])
    .index("by_organization", ["organizationId"])
    .index("by_user_organization", ["userId", "organizationId"]),

  // ==========================================================================
  // CONVERSATIONS AND THREADS
  // ==========================================================================

  /**
   * Conversations (Threads) - Following Mastra's thread model
   * 
   * Design decisions:
   * - resourceId links to the owning user/entity (Mastra pattern)
   * - Separate from messages for efficient listing and metadata queries
   * - Supports shared conversations via organizationId
   */
  conversations: defineTable({
    // Ownership - resourceId follows Mastra convention for flexible ownership
    resourceId: v.string(), // User ID, org ID, or custom entity
    userId: v.id("users"),
    organizationId: v.optional(v.id("organizations")),
    
    // Thread metadata
    title: v.optional(v.string()),
    description: v.optional(v.string()),
    
    // AI configuration for this conversation
    model: v.optional(v.string()),
    systemPrompt: v.optional(v.string()),
    
    // Conversation state
    status: v.union(
      v.literal("active"),
      v.literal("archived"),
      v.literal("deleted")
    ),
    
    // Pin/star for user organization
    isPinned: v.optional(v.boolean()),
    
    // Sharing settings
    isShared: v.optional(v.boolean()),
    shareId: v.optional(v.string()), // Unique share link ID
    
    // Aggregated stats (denormalized for performance)
    messageCount: v.optional(v.number()),
    totalTokens: v.optional(v.number()),
    totalCostUsd: v.optional(v.float64()),
    
    // Custom metadata (Mastra pattern - flexible key-value)
    metadata: v.optional(v.any()),
    
    // Multi-device sync
    syncVersion: v.optional(v.number()),
    lastModifiedBy: v.optional(v.string()), // Device/client ID
    
    // Timestamps
    createdAt: v.number(),
    updatedAt: v.number(),
    lastMessageAt: v.optional(v.number()),
  })
    .index("by_user", ["userId"])
    .index("by_user_updated", ["userId", "updatedAt"])
    .index("by_user_status", ["userId", "status"])
    .index("by_resource", ["resourceId"])
    .index("by_organization", ["organizationId"])
    .index("by_share_id", ["shareId"]),

  // ==========================================================================
  // MESSAGES
  // ==========================================================================

  /**
   * Messages - Core message storage following AI SDK UIMessage format
   * 
   * Design decisions:
   * - Parts array enables multi-modal content (text, images, tool calls)
   * - parentId enables conversation branching (tree structure)
   * - Stores complete message state for restoration across devices
   * - Tool invocations embedded in parts (AI SDK pattern) + denormalized fields
   */
  messages: defineTable({
    // Relationships
    conversationId: v.id("conversations"),
    userId: v.id("users"),
    
    // Branching support (parent reference model)
    parentId: v.optional(v.id("messages")),
    branchIndex: v.optional(v.number()), // Position among siblings
    isActiveBranch: v.optional(v.boolean()), // Currently selected path
    
    // Message role (AI SDK/Mastra aligned)
    role: messageRoleValidator,
    
    // Content - Parts-based structure (AI SDK UIMessage format)
    parts: v.array(messagePartValidator),
    
    // Legacy/convenience text field (extracted from parts)
    content: v.optional(v.string()),
    
    // Message state
    status: v.union(
      v.literal("pending"),
      v.literal("streaming"),
      v.literal("completed"),
      v.literal("failed"),
      v.literal("cancelled")
    ),
    
    // AI-specific metadata
    model: v.optional(v.string()),
    modelProvider: v.optional(v.string()),
    finishReason: v.optional(v.union(
      v.literal("stop"),
      v.literal("length"),
      v.literal("tool-calls"),
      v.literal("content-filter"),
      v.literal("error"),
      v.literal("cancelled")
    )),
    
    // Token usage (per-message tracking)
    usage: v.optional(v.object({
      promptTokens: v.number(),
      completionTokens: v.number(),
      totalTokens: v.number(),
      reasoningTokens: v.optional(v.number()),
      cachedTokens: v.optional(v.number()),
    })),
    costUsd: v.optional(v.float64()),
    
    // Latency tracking
    latencyMs: v.optional(v.number()),
    
    // Tool call summary (denormalized for quick access)
    hasToolCalls: v.optional(v.boolean()),
    toolCallIds: v.optional(v.array(v.string())),
    
    // Attachments summary (IDs of related artifacts)
    artifactIds: v.optional(v.array(v.id("artifacts"))),
    
    // For tool role messages - link to the call being responded to
    toolCallId: v.optional(v.string()),
    
    // Custom metadata (AI SDK pattern)
    metadata: v.optional(v.any()),
    
    // Edit history
    editedAt: v.optional(v.number()),
    originalContent: v.optional(v.string()),
    
    // Soft delete
    deletedAt: v.optional(v.number()),
    
    // Timestamps
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index("by_conversation", ["conversationId"])
    .index("by_conversation_created", ["conversationId", "createdAt"])
    .index("by_parent", ["parentId"])
    .index("by_user", ["userId"])
    .index("by_tool_call_id", ["toolCallId"]),

  // ==========================================================================
  // TOOL CALLS AND EXECUTIONS
  // ==========================================================================

  /**
   * Tool Calls - Detailed tracking of tool/function executions
   * 
   * Design decisions:
   * - Separate table enables detailed analytics on tool usage
   * - Stores full execution context for debugging and audit
   * - Links to both request message and response message
   */
  toolCalls: defineTable({
    // Unique ID from AI provider
    toolCallId: v.string(),
    
    // Relationships
    conversationId: v.id("conversations"),
    requestMessageId: v.id("messages"), // Message containing the tool call
    responseMessageId: v.optional(v.id("messages")), // Tool response message
    
    // Tool identification
    toolName: v.string(),
    toolDescription: v.optional(v.string()),
    
    // Execution details
    input: v.any(), // Arguments passed to tool
    inputSchema: v.optional(v.any()), // JSON Schema for validation
    
    output: v.optional(v.any()), // Tool result
    outputSchema: v.optional(v.any()),
    
    // Execution state
    state: toolStateValidator,
    errorMessage: v.optional(v.string()),
    errorCode: v.optional(v.string()),
    
    // Execution metadata
    executionTimeMs: v.optional(v.number()),
    executedBy: v.optional(v.union(
      v.literal("server"),
      v.literal("client"),
      v.literal("provider")
    )),
    
    // For tools requiring user confirmation
    requiresConfirmation: v.optional(v.boolean()),
    confirmedAt: v.optional(v.number()),
    confirmedBy: v.optional(v.id("users")),
    
    // Timestamps
    createdAt: v.number(),
    completedAt: v.optional(v.number()),
  })
    .index("by_tool_call_id", ["toolCallId"])
    .index("by_conversation", ["conversationId"])
    .index("by_request_message", ["requestMessageId"])
    .index("by_tool_name", ["toolName"]),

  /**
   * Tool Definitions - Registry of available tools
   */
  toolDefinitions: defineTable({
    name: v.string(),
    description: v.string(),
    
    // JSON Schema for input validation
    inputSchema: v.any(),
    outputSchema: v.optional(v.any()),
    
    // Tool configuration
    isActive: v.boolean(),
    requiresConfirmation: v.optional(v.boolean()),
    
    // Scope - which users/orgs can use this tool
    scope: v.union(
      v.literal("global"),
      v.literal("organization"),
      v.literal("user")
    ),
    organizationId: v.optional(v.id("organizations")),
    userId: v.optional(v.id("users")),
    
    // Metadata
    category: v.optional(v.string()),
    iconUrl: v.optional(v.string()),
    
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index("by_name", ["name"])
    .index("by_organization", ["organizationId"])
    .index("by_user", ["userId"]),

  // ==========================================================================
  // ARTIFACTS (Claude-style)
  // ==========================================================================

  /**
   * Artifacts - Standalone generated content (code, documents, diagrams)
   * 
   * Design decisions:
   * - Separate from messages for independent versioning and sharing
   * - Supports iteration with parentArtifactId chain
   * - Content can be stored inline or in Convex file storage for large files
   */
  artifacts: defineTable({
    // Relationships
    conversationId: v.id("conversations"),
    messageId: v.id("messages"), // Message that created this artifact
    userId: v.id("users"),
    
    // Artifact identity
    artifactType: artifactTypeValidator,
    title: v.string(),
    
    // Content storage
    content: v.optional(v.string()), // Inline for small artifacts
    storageId: v.optional(v.id("_storage")), // File storage for large content
    
    // For code artifacts
    language: v.optional(v.string()),
    
    // Versioning
    version: v.number(),
    parentArtifactId: v.optional(v.id("artifacts")), // Previous version
    changeDescription: v.optional(v.string()),
    
    // Publishing/sharing
    isPublished: v.optional(v.boolean()),
    publishedUrl: v.optional(v.string()),
    shareId: v.optional(v.string()),
    
    // Execution state (for code/components)
    isExecutable: v.optional(v.boolean()),
    lastExecutedAt: v.optional(v.number()),
    executionResult: v.optional(v.any()),
    
    // Metadata
    metadata: v.optional(v.object({
      linesOfCode: v.optional(v.number()),
      dependencies: v.optional(v.array(v.string())),
      previewEnabled: v.optional(v.boolean()),
      fileSize: v.optional(v.number()),
    })),
    
    // Timestamps
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index("by_conversation", ["conversationId"])
    .index("by_message", ["messageId"])
    .index("by_user", ["userId"])
    .index("by_share_id", ["shareId"])
    .index("by_parent", ["parentArtifactId"]),

  // ==========================================================================
  // FILE ATTACHMENTS
  // ==========================================================================

  /**
   * Attachments - User-uploaded files (images, documents)
   * 
   * Design decisions:
   * - Separate from artifacts (user uploads vs AI-generated)
   * - Stores both file metadata and Convex storage reference
   * - Supports processing state for async operations (OCR, embedding)
   */
  attachments: defineTable({
    // Relationships
    conversationId: v.optional(v.id("conversations")),
    messageId: v.optional(v.id("messages")),
    userId: v.id("users"),
    
    // File storage
    storageId: v.id("_storage"),
    
    // File metadata
    filename: v.string(),
    mimeType: v.string(),
    fileSize: v.number(),
    
    // For images
    width: v.optional(v.number()),
    height: v.optional(v.number()),
    
    // Processing state
    processingStatus: v.optional(v.union(
      v.literal("pending"),
      v.literal("processing"),
      v.literal("completed"),
      v.literal("failed")
    )),
    
    // Extracted content (for documents)
    extractedText: v.optional(v.string()),
    
    // Vector embedding reference (for RAG)
    embeddingId: v.optional(v.id("embeddings")),
    
    // Metadata
    metadata: v.optional(v.any()),
    
    createdAt: v.number(),
  })
    .index("by_conversation", ["conversationId"])
    .index("by_message", ["messageId"])
    .index("by_user", ["userId"]),

  // ==========================================================================
  // MEMORY AND RAG
  // ==========================================================================

  /**
   * User Memories - Long-term memory persistence (Mastra pattern)
   * 
   * Design decisions:
   * - Separate from conversation context for cross-conversation recall
   * - Importance scoring for memory prioritization
   * - TTL support via expiresAt for temporary memories
   */
  userMemories: defineTable({
    userId: v.id("users"),
    
    // Memory categorization
    memoryType: v.union(
      v.literal("preference"),    // User preferences
      v.literal("fact"),          // Facts about user
      v.literal("instruction"),   // Custom instructions
      v.literal("context"),       // Contextual information
      v.literal("summary")        // Conversation summaries
    ),
    
    // Content
    content: v.string(),
    
    // Source tracking
    sourceConversationId: v.optional(v.id("conversations")),
    sourceMessageId: v.optional(v.id("messages")),
    
    // Prioritization
    importanceScore: v.optional(v.float64()), // 0.0 - 1.0
    accessCount: v.optional(v.number()),
    lastAccessedAt: v.optional(v.number()),
    
    // Scope (Mastra pattern)
    scope: v.union(
      v.literal("global"),        // Applies everywhere
      v.literal("conversation"),  // Specific conversation only
      v.literal("resource")       // Resource-scoped (Mastra)
    ),
    scopeId: v.optional(v.string()), // Thread ID or resource ID if scoped
    
    // Embedding for semantic retrieval
    embeddingId: v.optional(v.id("embeddings")),
    
    // TTL
    expiresAt: v.optional(v.number()),
    
    // Metadata
    metadata: v.optional(v.any()),
    
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index("by_user", ["userId"])
    .index("by_user_type", ["userId", "memoryType"])
    .index("by_user_scope", ["userId", "scope"])
    .index("by_source_conversation", ["sourceConversationId"]),

  /**
   * Working Memory - Session-scoped dynamic memory (Mastra pattern)
   * 
   * Design decisions:
   * - Stores structured working memory per thread or resource
   * - Can be Markdown text or structured JSON
   * - Updated throughout conversation based on AI observations
   */
  workingMemory: defineTable({
    // Scope reference
    resourceId: v.string(), // User ID, org ID, or thread ID
    scope: v.union(v.literal("thread"), v.literal("resource")),
    scopeId: v.string(), // Thread ID or resource ID
    
    // Content - Markdown or structured (Mastra pattern)
    content: v.string(),
    structuredData: v.optional(v.any()), // If using Zod schema
    
    // Version for optimistic updates
    version: v.number(),
    
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index("by_scope", ["scope", "scopeId"])
    .index("by_resource", ["resourceId"]),

  /**
   * Embeddings - Vector storage for RAG and semantic search
   * 
   * Design decisions:
   * - Stores embedding vectors as arrays (Convex doesn't have native vector type)
   * - For production RAG, consider external vector DB (Pinecone, Qdrant) with ID references
   * - Chunk-based for document retrieval
   */
  embeddings: defineTable({
    userId: v.id("users"),
    
    // Source reference
    sourceType: v.union(
      v.literal("message"),
      v.literal("memory"),
      v.literal("document"),
      v.literal("attachment")
    ),
    sourceId: v.string(),
    
    // Content
    chunkIndex: v.optional(v.number()),
    content: v.string(), // Original text (LLMs need text, not vectors)
    
    // Vector storage
    // For small-scale: store as array
    // For production: use external vector DB, store only reference ID
    embedding: v.optional(v.array(v.float64())), // e.g., 1536 dims for ada-002
    externalVectorId: v.optional(v.string()), // Reference to external vector DB
    
    // Embedding model info
    model: v.optional(v.string()),
    dimensions: v.optional(v.number()),
    
    // Metadata for filtering
    metadata: v.optional(v.any()),
    
    createdAt: v.number(),
  })
    .index("by_user", ["userId"])
    .index("by_source", ["sourceType", "sourceId"])
    .index("by_user_source_type", ["userId", "sourceType"]),

  /**
   * Documents - User-uploaded documents for RAG
   */
  documents: defineTable({
    userId: v.id("users"),
    organizationId: v.optional(v.id("organizations")),
    
    // File info
    filename: v.string(),
    storageId: v.id("_storage"),
    mimeType: v.string(),
    fileSize: v.number(),
    
    // Processing
    processingStatus: v.union(
      v.literal("pending"),
      v.literal("processing"),
      v.literal("completed"),
      v.literal("failed")
    ),
    chunkCount: v.optional(v.number()),
    
    // Content hash for deduplication
    contentHash: v.optional(v.string()),
    
    // Extracted content
    extractedText: v.optional(v.string()),
    
    // Metadata
    title: v.optional(v.string()),
    description: v.optional(v.string()),
    metadata: v.optional(v.any()),
    
    createdAt: v.number(),
    processedAt: v.optional(v.number()),
  })
    .index("by_user", ["userId"])
    .index("by_organization", ["organizationId"])
    .index("by_content_hash", ["contentHash"]),

  // ==========================================================================
  // TOKEN USAGE AND COST TRACKING
  // ==========================================================================

  /**
   * Usage Events - Detailed per-request tracking
   * 
   * Design decisions:
   * - Separate from messages for detailed analytics
   * - Enables cost allocation by user, org, project, tags
   * - Supports various token types (reasoning, cached, audio)
   */
  usageEvents: defineTable({
    // Relationships
    userId: v.id("users"),
    organizationId: v.optional(v.id("organizations")),
    conversationId: v.optional(v.id("conversations")),
    messageId: v.optional(v.id("messages")),
    
    // Model info
    model: v.string(),
    modelProvider: v.string(),
    
    // Token counts
    promptTokens: v.number(),
    completionTokens: v.number(),
    totalTokens: v.number(),
    
    // Special token types
    reasoningTokens: v.optional(v.number()),
    cachedTokens: v.optional(v.number()),
    audioTokens: v.optional(v.number()),
    imageTokens: v.optional(v.number()),
    
    // Cost
    costUsd: v.float64(),
    
    // Performance
    latencyMs: v.optional(v.number()),
    
    // Request type
    requestType: v.optional(v.union(
      v.literal("chat"),
      v.literal("completion"),
      v.literal("embedding"),
      v.literal("image"),
      v.literal("audio")
    )),
    
    // Tags for cost allocation
    tags: v.optional(v.array(v.string())),
    projectId: v.optional(v.string()),
    
    createdAt: v.number(),
  })
    .index("by_user", ["userId"])
    .index("by_user_date", ["userId", "createdAt"])
    .index("by_organization", ["organizationId"])
    .index("by_conversation", ["conversationId"]),

  /**
   * User Usage Aggregates - Daily rollups for efficient queries
   */
  userDailyUsage: defineTable({
    userId: v.id("users"),
    date: v.string(), // YYYY-MM-DD format
    model: v.string(),
    
    // Aggregates
    requestCount: v.number(),
    totalTokens: v.number(),
    promptTokens: v.number(),
    completionTokens: v.number(),
    totalCostUsd: v.float64(),
    
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index("by_user_date", ["userId", "date"])
    .index("by_user_date_model", ["userId", "date", "model"]),

  /**
   * User Budgets - Spending limits and alerts
   */
  userBudgets: defineTable({
    userId: v.id("users"),
    
    // Monthly limits
    monthlyLimitUsd: v.optional(v.float64()),
    currentMonthSpend: v.float64(),
    
    // Daily limits
    dailyLimitUsd: v.optional(v.float64()),
    currentDaySpend: v.float64(),
    
    // Alerts
    alertThresholdPercent: v.optional(v.number()), // e.g., 80 for 80%
    lastAlertSentAt: v.optional(v.number()),
    
    // Billing period
    billingCycleStart: v.number(),
    
    updatedAt: v.number(),
  })
    .index("by_user", ["userId"]),

  // ==========================================================================
  // REAL-TIME SYNC SUPPORT
  // ==========================================================================

  /**
   * Device Sessions - Track connected devices for sync
   */
  deviceSessions: defineTable({
    userId: v.id("users"),
    deviceId: v.string(),
    deviceType: v.union(
      v.literal("web"),
      v.literal("ios"),
      v.literal("android"),
      v.literal("desktop")
    ),
    deviceName: v.optional(v.string()),
    
    // Sync state
    lastSyncAt: v.number(),
    lastSyncVersion: v.optional(v.number()),
    
    // Connection state
    isActive: v.boolean(),
    connectedAt: v.optional(v.number()),
    disconnectedAt: v.optional(v.number()),
    
    // Push notification tokens
    pushToken: v.optional(v.string()),
    pushProvider: v.optional(v.union(
      v.literal("apns"),
      v.literal("fcm"),
      v.literal("web-push")
    )),
    
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index("by_user", ["userId"])
    .index("by_user_device", ["userId", "deviceId"]),

  // ==========================================================================
  // AGENT CONFIGURATIONS (Mastra pattern)
  // ==========================================================================

  /**
   * Agents - Custom AI agent configurations
   */
  agents: defineTable({
    // Ownership
    userId: v.optional(v.id("users")),
    organizationId: v.optional(v.id("organizations")),
    
    // Identity
    name: v.string(),
    description: v.optional(v.string()),
    avatarUrl: v.optional(v.string()),
    avatarStorageId: v.optional(v.id("_storage")),
    
    // AI configuration
    model: v.string(),
    instructions: v.string(), // System prompt
    
    // Tools
    enabledToolIds: v.optional(v.array(v.id("toolDefinitions"))),
    
    // Memory configuration (Mastra pattern)
    memoryConfig: v.optional(v.object({
      lastMessages: v.optional(v.number()),
      semanticRecall: v.optional(v.object({
        enabled: v.boolean(),
        topK: v.optional(v.number()),
        scope: v.optional(v.union(v.literal("thread"), v.literal("resource"))),
      })),
      workingMemory: v.optional(v.object({
        enabled: v.boolean(),
        scope: v.optional(v.union(v.literal("thread"), v.literal("resource"))),
      })),
    })),
    
    // Generation defaults
    generationConfig: v.optional(v.object({
      temperature: v.optional(v.float64()),
      maxTokens: v.optional(v.number()),
      topP: v.optional(v.float64()),
      frequencyPenalty: v.optional(v.float64()),
      presencePenalty: v.optional(v.float64()),
    })),
    
    // Visibility
    isPublic: v.optional(v.boolean()),
    
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index("by_user", ["userId"])
    .index("by_organization", ["organizationId"])
    .index("by_name", ["name"]),
});
```

---

## TypeScript types for application code

```typescript
// convex/types.ts
import { Doc, Id } from "./_generated/dataModel";

// Re-export document types
export type User = Doc<"users">;
export type Organization = Doc<"organizations">;
export type Conversation = Doc<"conversations">;
export type Message = Doc<"messages">;
export type ToolCall = Doc<"toolCalls">;
export type Artifact = Doc<"artifacts">;
export type Attachment = Doc<"attachments">;
export type UserMemory = Doc<"userMemories">;
export type UsageEvent = Doc<"usageEvents">;
export type Agent = Doc<"agents">;

// Branded ID types for type safety
export type UserId = Id<"users">;
export type ConversationId = Id<"conversations">;
export type MessageId = Id<"messages">;
export type ArtifactId = Id<"artifacts">;

// Message part types (aligned with AI SDK UIMessagePart)
export type TextPart = {
  type: "text";
  text: string;
  state?: "streaming" | "done";
};

export type ToolInvocationPart = {
  type: "tool-invocation";
  toolCallId: string;
  toolName: string;
  state: "input-streaming" | "input-available" | "output-available" | "output-error";
  input: unknown;
  output?: unknown;
  errorText?: string;
  providerExecuted?: boolean;
};

export type FilePart = {
  type: "file";
  mediaType: string;
  filename?: string;
  url?: string;
  storageId?: Id<"_storage">;
};

export type ReasoningPart = {
  type: "reasoning";
  text: string;
  state?: "streaming" | "done";
  providerMetadata?: Record<string, unknown>;
};

export type SourcePart = {
  type: "source";
  sourceType: "url" | "document";
  id?: string;
  url?: string;
  title?: string;
  documentId?: string;
};

export type StepStartPart = {
  type: "step-start";
  stepNumber?: number;
};

export type DataPart = {
  type: "data";
  dataType: string;
  id?: string;
  data: unknown;
};

export type MessagePart =
  | TextPart
  | ToolInvocationPart
  | FilePart
  | ReasoningPart
  | SourcePart
  | StepStartPart
  | DataPart;

// Roles
export type MessageRole = "user" | "assistant" | "system" | "tool";

// Tool states
export type ToolState =
  | "input-streaming"
  | "input-available"
  | "output-available"
  | "output-error";

// Artifact types
export type ArtifactType =
  | "code"
  | "document"
  | "svg"
  | "react_component"
  | "html"
  | "diagram"
  | "markdown"
  | "mermaid"
  | "csv"
  | "json";

// Usage tracking
export interface TokenUsage {
  promptTokens: number;
  completionTokens: number;
  totalTokens: number;
  reasoningTokens?: number;
  cachedTokens?: number;
}

// Memory configuration (Mastra pattern)
export interface MemoryConfig {
  lastMessages?: number;
  semanticRecall?: {
    enabled: boolean;
    topK?: number;
    scope?: "thread" | "resource";
  };
  workingMemory?: {
    enabled: boolean;
    scope?: "thread" | "resource";
  };
}

// For AI SDK integration - conversion helpers
export interface UIMessageCompatible {
  id: string;
  role: MessageRole;
  parts: MessagePart[];
  metadata?: Record<string, unknown>;
}
```

---

## Key design decisions explained

### Why parts-based message structure?
AI SDK 5 moved to a **parts array** architecture where each message contains discrete content units. This enables **multi-modal messages** (text + images + tool calls in one message), **streaming state per-part**, and **type-safe rendering**. The schema stores parts as a validated array rather than separate columns, preserving the exact structure the AI SDK expects.

### Branching with parentId versus tree tables
The **parent reference model** (`parentId` on messages) offers the best balance of simplicity and functionality. Each message points to its parent, enabling efficient queries for conversation paths while supporting unlimited branching depth. The `branchIndex` and `isActiveBranch` fields enable UI navigation between alternative responses—similar to Claude's regeneration feature.

### Separate artifacts table for generated content
Claude's artifacts (code blocks, documents, diagrams) benefit from **independent versioning, sharing, and storage**. Embedding them in messages would complicate queries and make sharing impossible. The artifacts table supports versioning via `parentArtifactId` chains and publishing via `shareId`.

### Memory architecture follows Mastra's three-tier model
**User memories** persist across all conversations (preferences, facts), **working memory** maintains session-specific state per thread, and **embeddings** enable semantic retrieval. This separation allows the AI to recall "you prefer dark mode" (user memory) while tracking "we're debugging the login issue" (working memory) and finding relevant past discussions (embeddings).

### Token tracking at multiple granularities
Per-message `usage` fields enable immediate cost display, while the `usageEvents` table captures detailed analytics. The `userDailyUsage` aggregation table prevents expensive rollup queries. This mirrors production patterns from **LiteLLM and Langfuse**.

---

## Real-time sync implementation pattern

```typescript
// convex/messages.ts
import { mutation, query } from "./_generated/server";
import { v } from "convex/values";

// Real-time message subscription - Convex handles reactivity automatically
export const listByConversation = query({
  args: { 
    conversationId: v.id("conversations"),
    limit: v.optional(v.number()),
  },
  handler: async (ctx, args) => {
    const messages = await ctx.db
      .query("messages")
      .withIndex("by_conversation_created", (q) => 
        q.eq("conversationId", args.conversationId)
      )
      .order("asc")
      .take(args.limit ?? 100);
    
    return messages;
  },
});

// Send message with optimistic update support
export const send = mutation({
  args: {
    conversationId: v.id("conversations"),
    role: v.union(v.literal("user"), v.literal("assistant")),
    parts: v.array(v.any()),
    parentId: v.optional(v.id("messages")),
  },
  handler: async (ctx, args) => {
    const identity = await ctx.auth.getUserIdentity();
    if (!identity) throw new Error("Unauthenticated");
    
    const user = await ctx.db
      .query("users")
      .withIndex("by_token", (q) => q.eq("tokenIdentifier", identity.tokenIdentifier))
      .first();
    if (!user) throw new Error("User not found");
    
    const now = Date.now();
    
    // Insert message
    const messageId = await ctx.db.insert("messages", {
      conversationId: args.conversationId,
      userId: user._id,
      role: args.role,
      parts: args.parts,
      status: "completed",
      parentId: args.parentId,
      isActiveBranch: true,
      createdAt: now,
      updatedAt: now,
    });
    
    // Update conversation metadata
    await ctx.db.patch(args.conversationId, {
      lastMessageAt: now,
      updatedAt: now,
      syncVersion: (await ctx.db.get(args.conversationId))?.syncVersion ?? 0 + 1,
    });
    
    return messageId;
  },
});
```

---

## Integration with Vercel AI SDK

```typescript
// app/api/chat/route.ts
import { streamText, convertToModelMessages, UIMessage } from "ai";
import { ConvexHttpClient } from "convex/browser";
import { api } from "@/convex/_generated/api";

const convex = new ConvexHttpClient(process.env.NEXT_PUBLIC_CONVEX_URL!);

export async function POST(req: Request) {
  const { messages, conversationId } = await req.json();
  
  const result = streamText({
    model: openai("gpt-4o"),
    messages: convertToModelMessages(messages),
    tools: myTools,
    
    onFinish: async ({ response, usage }) => {
      // Persist to Convex
      await convex.mutation(api.messages.send, {
        conversationId,
        role: "assistant",
        parts: response.messages[0].parts,
        usage: {
          promptTokens: usage.promptTokens,
          completionTokens: usage.completionTokens,
          totalTokens: usage.totalTokens,
        },
      });
    },
  });
  
  return result.toUIMessageStreamResponse({
    originalMessages: messages,
  });
}
```

This schema provides a **complete foundation** for building a production-grade AI chat application with real-time sync, conversation branching, tool execution tracking, Claude-style artifacts, comprehensive memory systems, and detailed usage analytics—all optimized for Convex's reactive architecture.