{"slug":"convex-schema-and-queries","title":"Convex Schema Design, Indexes, and Query Patterns","tags":["convex","database","real-time","typescript","full-stack"],"agent_summary":"Convex schema definition, index design, query/mutation/action patterns, and critical anti-patterns for real-time reactive database development.","trigger_phrases":["Convex schema","Convex indexes","Convex query","Convex mutation","Convex real-time","defineTable","withIndex"],"runnable":false,"markdown":"\n## Overview\n\nConvex is a reactive database where queries are live subscriptions, not one-time fetches. Understanding this changes how you design schemas and data access patterns.\n\n## Mental Model\n\n| Concept | What it means |\n|---------|---------------|\n| **Query** | Live subscription — auto-updates when dependencies change |\n| **Mutation** | Atomic transaction — all or nothing, no external I/O |\n| **Action** | One-shot function — can call external APIs, not reactive |\n| **Indexes** | Explicit — no query planner, you control exactly which index runs |\n\n## Schema Definition\n\n```typescript\n// convex/schema.ts\nimport { defineSchema, defineTable } from \"convex/server\";\nimport { v } from \"convex/values\";\n\nexport default defineSchema({\n users: defineTable({\n clerkId: v.string(),\n email: v.string(),\n name: v.string(),\n role: v.union(v.literal(\"admin\"), v.literal(\"user\")),\n createdAt: v.number(), // Unix ms — v.number(), NOT Date\n })\n .index(\"by_clerk_id\", [\"clerkId\"])\n .index(\"by_email\", [\"email\"]),\n\n posts: defineTable({\n authorId: v.id(\"users\"),\n title: v.string(),\n content: v.string(),\n published: v.boolean(),\n tags: v.array(v.string()),\n viewCount: v.number(),\n })\n .index(\"by_author\", [\"authorId\"])\n .index(\"by_author_published\", [\"authorId\", \"published\"]),\n});\n```\n\n**Key rules:**\n- Use `v.number()` for timestamps (Unix ms), not `v.string()` dates\n- Use `v.id(\"tableName\")` for foreign keys — type-safe document references\n- Every field you filter or sort on needs an index\n\n## Query Patterns\n\n```typescript\n// convex/posts.ts\nimport { query } from \"./_generated/server\";\nimport { v } from \"convex/values\";\n\n// Paginated query with index\nexport const listPublished = query({\n args: {\n paginationOpts: paginationOptsValidator,\n authorId: v.optional(v.id(\"users\")),\n },\n handler: async (ctx, args) => {\n let q = ctx.db.query(\"posts\").withIndex(\"by_author_published\", (q) =>\n args.authorId\n ? q.eq(\"authorId\", args.authorId).eq(\"published\", true)\n : q.eq(\"published\", true)\n );\n return await q.order(\"desc\").paginate(args.paginationOpts);\n },\n});\n\n// Single document lookup\nexport const getPost = query({\n args: { postId: v.id(\"posts\") },\n handler: async (ctx, args) => {\n return await ctx.db.get(args.postId); // Returns null if not found\n },\n});\n```\n\n## Mutation Patterns\n\n```typescript\nimport { mutation } from \"./_generated/server\";\n\nexport const createPost = mutation({\n args: {\n title: v.string(),\n content: v.string(),\n tags: v.array(v.string()),\n },\n handler: async (ctx, args) => {\n const identity = await ctx.auth.getUserIdentity();\n if (!identity) throw new Error(\"Not authenticated\");\n\n const user = await ctx.db\n .query(\"users\")\n .withIndex(\"by_clerk_id\", (q) => q.eq(\"clerkId\", identity.subject))\n .unique();\n\n if (!user) throw new Error(\"User not found\");\n\n return await ctx.db.insert(\"posts\", {\n authorId: user._id,\n title: args.title,\n content: args.content,\n published: false,\n tags: args.tags,\n viewCount: 0,\n });\n },\n});\n```\n\n## Action Pattern (External API Calls)\n\n```typescript\nimport { action, internalMutation } from \"./_generated/server\";\nimport { internal } from \"./_generated/api\";\n\n// Action calls external API, then calls mutation to save result\nexport const generateEmbedding = action({\n args: { postId: v.id(\"posts\"), text: v.string() },\n handler: async (ctx, args) => {\n // External API call — allowed in actions\n const res = await fetch(\"https://api.openai.com/v1/embeddings\", {\n method: \"POST\",\n headers: { Authorization: `Bearer ${process.env.OPENAI_API_KEY}` },\n body: JSON.stringify({ model: \"text-embedding-3-small\", input: args.text }),\n });\n const data = await res.json();\n const embedding = data.data[0].embedding;\n\n // Save via internal mutation\n await ctx.runMutation(internal.posts.saveEmbedding, {\n postId: args.postId,\n embedding,\n });\n },\n});\n```\n\n## Critical Anti-Patterns\n\n| Anti-Pattern | Problem | Fix |\n|--------------|---------|-----|\n| `.collect()` without limit | Loads entire table into memory | Use `.paginate()` or `.take(N)` |\n| `.filter()` without index | Full table scan on every query | Add index, use `.withIndex()` |\n| `v.map()` / `v.set()` | Not supported by Convex validator | Use `v.array()` and filter in code |\n| `undefined` in values | Invalid — Convex rejects it | Use `null` or omit the field |\n| External API in mutation | Mutations cannot call external APIs | Move to an action |\n| Unbounded queries in hot paths | O(N) reads on every page load | Always paginate or take a bounded set |\n\n## React Hook Usage\n\n```tsx\n// Reactive query — auto-updates\nconst posts = useQuery(api.posts.listPublished, { paginationOpts: { numItems: 20, cursor: null } });\n\n// Mutation\nconst createPost = useMutation(api.posts.createPost);\n\nasync function handleSubmit(data: FormData) {\n await createPost({ title: data.title, content: data.content, tags: [] });\n}\n```\n\n`posts` is `undefined` while loading, then the paginated result — always handle the loading state.\n","html":"

Overview

\n

Convex is a reactive database where queries are live subscriptions, not one-time fetches. Understanding this changes how you design schemas and data access patterns.

\n

Mental Model

\n

| Concept | What it means |\n|---------|---------------|\n| Query | Live subscription — auto-updates when dependencies change |\n| Mutation | Atomic transaction — all or nothing, no external I/O |\n| Action | One-shot function — can call external APIs, not reactive |\n| Indexes | Explicit — no query planner, you control exactly which index runs |

\n

Schema Definition

\n
// convex/schema.ts\nimport { defineSchema, defineTable } from \"convex/server\";\nimport { v } from \"convex/values\";\n\nexport default defineSchema({\n  users: defineTable({\n    clerkId: v.string(),\n    email: v.string(),\n    name: v.string(),\n    role: v.union(v.literal(\"admin\"), v.literal(\"user\")),\n    createdAt: v.number(),       // Unix ms — v.number(), NOT Date\n  })\n    .index(\"by_clerk_id\", [\"clerkId\"])\n    .index(\"by_email\", [\"email\"]),\n\n  posts: defineTable({\n    authorId: v.id(\"users\"),\n    title: v.string(),\n    content: v.string(),\n    published: v.boolean(),\n    tags: v.array(v.string()),\n    viewCount: v.number(),\n  })\n    .index(\"by_author\", [\"authorId\"])\n    .index(\"by_author_published\", [\"authorId\", \"published\"]),\n});\n
\n

Key rules:

\n\n

Query Patterns

\n
// convex/posts.ts\nimport { query } from \"./_generated/server\";\nimport { v } from \"convex/values\";\n\n// Paginated query with index\nexport const listPublished = query({\n  args: {\n    paginationOpts: paginationOptsValidator,\n    authorId: v.optional(v.id(\"users\")),\n  },\n  handler: async (ctx, args) => {\n    let q = ctx.db.query(\"posts\").withIndex(\"by_author_published\", (q) =>\n      args.authorId\n        ? q.eq(\"authorId\", args.authorId).eq(\"published\", true)\n        : q.eq(\"published\", true)\n    );\n    return await q.order(\"desc\").paginate(args.paginationOpts);\n  },\n});\n\n// Single document lookup\nexport const getPost = query({\n  args: { postId: v.id(\"posts\") },\n  handler: async (ctx, args) => {\n    return await ctx.db.get(args.postId);  // Returns null if not found\n  },\n});\n
\n

Mutation Patterns

\n
import { mutation } from \"./_generated/server\";\n\nexport const createPost = mutation({\n  args: {\n    title: v.string(),\n    content: v.string(),\n    tags: v.array(v.string()),\n  },\n  handler: async (ctx, args) => {\n    const identity = await ctx.auth.getUserIdentity();\n    if (!identity) throw new Error(\"Not authenticated\");\n\n    const user = await ctx.db\n      .query(\"users\")\n      .withIndex(\"by_clerk_id\", (q) => q.eq(\"clerkId\", identity.subject))\n      .unique();\n\n    if (!user) throw new Error(\"User not found\");\n\n    return await ctx.db.insert(\"posts\", {\n      authorId: user._id,\n      title: args.title,\n      content: args.content,\n      published: false,\n      tags: args.tags,\n      viewCount: 0,\n    });\n  },\n});\n
\n

Action Pattern (External API Calls)

\n
import { action, internalMutation } from \"./_generated/server\";\nimport { internal } from \"./_generated/api\";\n\n// Action calls external API, then calls mutation to save result\nexport const generateEmbedding = action({\n  args: { postId: v.id(\"posts\"), text: v.string() },\n  handler: async (ctx, args) => {\n    // External API call — allowed in actions\n    const res = await fetch(\"https://api.openai.com/v1/embeddings\", {\n      method: \"POST\",\n      headers: { Authorization: `Bearer ${process.env.OPENAI_API_KEY}` },\n      body: JSON.stringify({ model: \"text-embedding-3-small\", input: args.text }),\n    });\n    const data = await res.json();\n    const embedding = data.data[0].embedding;\n\n    // Save via internal mutation\n    await ctx.runMutation(internal.posts.saveEmbedding, {\n      postId: args.postId,\n      embedding,\n    });\n  },\n});\n
\n

Critical Anti-Patterns

\n

| Anti-Pattern | Problem | Fix |\n|--------------|---------|-----|\n| .collect() without limit | Loads entire table into memory | Use .paginate() or .take(N) |\n| .filter() without index | Full table scan on every query | Add index, use .withIndex() |\n| v.map() / v.set() | Not supported by Convex validator | Use v.array() and filter in code |\n| undefined in values | Invalid — Convex rejects it | Use null or omit the field |\n| External API in mutation | Mutations cannot call external APIs | Move to an action |\n| Unbounded queries in hot paths | O(N) reads on every page load | Always paginate or take a bounded set |

\n

React Hook Usage

\n
// Reactive query — auto-updates\nconst posts = useQuery(api.posts.listPublished, { paginationOpts: { numItems: 20, cursor: null } });\n\n// Mutation\nconst createPost = useMutation(api.posts.createPost);\n\nasync function handleSubmit(data: FormData) {\n  await createPost({ title: data.title, content: data.content, tags: [] });\n}\n
\n

posts is undefined while loading, then the paginated result — always handle the loading state.

\n"}