Database Chat

Copy code// convex/convex.config.ts
import { defineApp } from "convex/server";
import databaseChat from "./components/databaseChat/convex.config";

const app = defineApp();
app.use(databaseChat);

export default app;

Copy codenpx convex env set OPENROUTER_API_KEY your_key_here

Copy code// convex/chatTools.ts
import { query } from "./_generated/server";
import { v } from "convex/values";

export const countRecords = query({
  args: { table: v.string() },
  returns: v.object({ count: v.number() }),
  handler: async (ctx, args) => {
    if (args.table === "users") {
      const users = await ctx.db.query("users").collect();
      return { count: users.length };
    }
    if (args.table === "orders") {
      const orders = await ctx.db.query("orders").collect();
      return { count: orders.length };
    }
    return { count: 0 };
  },
});

Copy code// convex/chat.ts
import { v } from "convex/values";
import { action, mutation, query } from "./_generated/server";
import { components, api } from "./_generated/api";

const SYSTEM_PROMPT = `You are a helpful assistant. Use the available tools to answer questions about the database.`;

const TOOLS = [
  {
    name: "countRecords",
    description: "Count records in a table. Available tables: users, orders",
    parameters: {
      type: "object",
      properties: {
        table: { type: "string", enum: ["users", "orders"] },
      },
      required: ["table"],
    },
    handler: "countRecords",
  },
];

// Create conversation
export const createConversation = mutation({
  args: { title: v.optional(v.string()) },
  returns: v.string(),
  handler: async (ctx, args) => {
    return await ctx.runMutation(components.databaseChat.conversations.create, {
      externalId: "user-id", // Use your auth system
      title: args.title ?? "New Chat",
    });
  },
});

// Get messages
export const getMessages = query({
  args: { conversationId: v.string() },
  returns: v.array(v.any()),
  handler: async (ctx, args) => {
    return await ctx.runQuery(components.databaseChat.messages.list, {
      conversationId: args.conversationId as any,
    });
  },
});

// Get stream state (for real-time UI updates)
export const getStreamState = query({
  args: { conversationId: v.string() },
  handler: async (ctx, args) => {
    return await ctx.runQuery(components.databaseChat.stream.getStream, {
      conversationId: args.conversationId as any,
    });
  },
});

// Get stream deltas (efficient O(n) streaming)
export const getStreamDeltas = query({
  args: { streamId: v.string(), cursor: v.number() },
  handler: async (ctx, args) => {
    return await ctx.runQuery(components.databaseChat.stream.listDeltas, {
      streamId: args.streamId as any,
      cursor: args.cursor,
    });
  },
});

// Abort a stream (stop generation)
export const abortStream = mutation({
  args: { conversationId: v.string(), reason: v.optional(v.string()) },
  handler: async (ctx, args) => {
    return await ctx.runMutation(components.databaseChat.stream.abortByConversation, {
      conversationId: args.conversationId as any,
      reason: args.reason ?? "User cancelled",
    });
  },
});

// Send message (see full implementation in "Creating the Chat Integration")
export const sendMessage = action({
  args: { conversationId: v.string(), message: v.string() },
  handler: async (ctx, args) => {
    // Implementation shown below
  },
});

Copy codeinterface Tool {
  name: string;           // Unique identifier
  description: string;    // What the tool does (LLM reads this!)
  parameters: {           // JSON Schema for arguments
    type: "object";
    properties: Record<string, {
      type: "string" | "number" | "boolean" | "array" | "object";
      description?: string;
      enum?: string[];    // For constrained values
    }>;
    required?: string[];
  };
  handlerType?: "query" | "mutation" | "action"; // Default: "query"
  handler: string;        // Function handle string (createFunctionHandle)
}

Copy codeconst searchProductsTool = {
  name: "searchProducts",
  description: "Search products by name, category, or price range",
  parameters: {
    type: "object",
    properties: {
      query: {
        type: "string",
        description: "Search text to match against product name",
      },
      category: {
        type: "string",
        description: "Filter by category",
        enum: ["electronics", "clothing", "home", "sports"],
      },
      minPrice: {
        type: "number",
        description: "Minimum price",
      },
      maxPrice: {
        type: "number",
        description: "Maximum price",
      },
      limit: {
        type: "number",
        description: "Maximum results (default: 20)",
      },
    },
    required: [],
  },
  handler: "searchProducts",
};

Copy codeconst getTasksTool = {
  name: "getTasks",
  description: "Get tasks with optional filters for status, assignee, or project",
  parameters: {
    type: "object",
    properties: {
      status: {
        type: "string",
        description: "Filter by task status",
        enum: ["todo", "in_progress", "review", "done"],
      },
      assigneeId: {
        type: "string",
        description: "Filter by assigned user ID",
      },
      projectId: {
        type: "string",
        description: "Filter by project ID",
      },
      priority: {
        type: "string",
        enum: ["low", "medium", "high", "urgent"],
      },
    },
    required: [],
  },
  handler: "getTasks",
};

Copy codeconst searchArticlesTool = {
  name: "searchArticles",
  description: "Search published articles by title, author, or tags",
  parameters: {
    type: "object",
    properties: {
      query: {
        type: "string",
        description: "Search text for title or content",
      },
      authorId: {
        type: "string",
        description: "Filter by author",
      },
      tags: {
        type: "array",
        description: "Filter by tags",
        items: { type: "string" },
      },
      publishedAfter: {
        type: "string",
        description: "ISO date string - only articles after this date",
      },
    },
    required: [],
  },
  handler: "searchArticles",
};

Copy code// convex/chatTools.ts
import { query } from "./_generated/server";
import { v } from "convex/values";

export const searchProducts = query({
  args: {
    query: v.optional(v.string()),
    category: v.optional(v.string()),
    minPrice: v.optional(v.number()),
    maxPrice: v.optional(v.number()),
    limit: v.optional(v.number()),
  },
  returns: v.array(
    v.object({
      id: v.string(),
      name: v.string(),
      category: v.string(),
      price: v.number(),
      inStock: v.boolean(),
      viewUrl: v.string(),
    })
  ),
  handler: async (ctx, args) => {
    const limit = Math.min(args.limit ?? 20, 100);
    let products = await ctx.db.query("products").collect();

    // Apply filters
    if (args.category) {
      products = products.filter((p) => p.category === args.category);
    }
    if (args.minPrice !== undefined) {
      products = products.filter((p) => p.price >= args.minPrice!);
    }
    if (args.maxPrice !== undefined) {
      products = products.filter((p) => p.price <= args.maxPrice!);
    }
    if (args.query) {
      const q = args.query.toLowerCase();
      products = products.filter((p) => p.name.toLowerCase().includes(q));
    }

    return products.slice(0, limit).map((p) => ({
      id: p._id,
      name: p.name,
      category: p.category,
      price: p.price,
      inStock: p.stockCount > 0,
      viewUrl: `/products/${p._id}`,
    }));
  },
});

Copy codeexport const getOrderStats = query({
  args: { period: v.optional(v.string()) },
  returns: v.object({
    totalOrders: v.number(),
    totalRevenue: v.number(),
    averageOrderValue: v.number(),
    statusBreakdown: v.object({
      pending: v.number(),
      processing: v.number(),
      shipped: v.number(),
      delivered: v.number(),
    }),
  }),
  handler: async (ctx, args) => {
    let orders = await ctx.db.query("orders").collect();

    // Filter by period if provided
    if (args.period === "today") {
      const today = new Date().setHours(0, 0, 0, 0);
      orders = orders.filter((o) => o.createdAt >= today);
    } else if (args.period === "week") {
      const weekAgo = Date.now() - 7 * 24 * 60 * 60 * 1000;
      orders = orders.filter((o) => o.createdA

[README truncated for prompt length]