Convex Schema Design, Indexes, and Query Patterns

Overview

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.

Mental Model

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

Schema Definition

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

export default defineSchema({
  users: defineTable({
    clerkId: v.string(),
    email: v.string(),
    name: v.string(),
    role: v.union(v.literal("admin"), v.literal("user")),
    createdAt: v.number(),       // Unix ms — v.number(), NOT Date
  })
    .index("by_clerk_id", ["clerkId"])
    .index("by_email", ["email"]),

  posts: defineTable({
    authorId: v.id("users"),
    title: v.string(),
    content: v.string(),
    published: v.boolean(),
    tags: v.array(v.string()),
    viewCount: v.number(),
  })
    .index("by_author", ["authorId"])
    .index("by_author_published", ["authorId", "published"]),
});

Key rules:

• Use v.number() for timestamps (Unix ms), not v.string() dates
• Use v.id("tableName") for foreign keys — type-safe document references
• Every field you filter or sort on needs an index

Query Patterns

// convex/posts.ts
import { query } from "./_generated/server";
import { v } from "convex/values";

// Paginated query with index
export const listPublished = query({
  args: {
    paginationOpts: paginationOptsValidator,
    authorId: v.optional(v.id("users")),
  },
  handler: async (ctx, args) => {
    let q = ctx.db.query("posts").withIndex("by_author_published", (q) =>
      args.authorId
        ? q.eq("authorId", args.authorId).eq("published", true)
        : q.eq("published", true)
    );
    return await q.order("desc").paginate(args.paginationOpts);
  },
});

// Single document lookup
export const getPost = query({
  args: { postId: v.id("posts") },
  handler: async (ctx, args) => {
    return await ctx.db.get(args.postId);  // Returns null if not found
  },
});

Mutation Patterns

import { mutation } from "./_generated/server";

export const createPost = mutation({
  args: {
    title: v.string(),
    content: v.string(),
    tags: v.array(v.string()),
  },
  handler: async (ctx, args) => {
    const identity = await ctx.auth.getUserIdentity();
    if (!identity) throw new Error("Not authenticated");

    const user = await ctx.db
      .query("users")
      .withIndex("by_clerk_id", (q) => q.eq("clerkId", identity.subject))
      .unique();

    if (!user) throw new Error("User not found");

    return await ctx.db.insert("posts", {
      authorId: user._id,
      title: args.title,
      content: args.content,
      published: false,
      tags: args.tags,
      viewCount: 0,
    });
  },
});

Action Pattern (External API Calls)

import { action, internalMutation } from "./_generated/server";
import { internal } from "./_generated/api";

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

    // Save via internal mutation
    await ctx.runMutation(internal.posts.saveEmbedding, {
      postId: args.postId,
      embedding,
    });
  },
});

Critical Anti-Patterns

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

React Hook Usage

// Reactive query — auto-updates
const posts = useQuery(api.posts.listPublished, { paginationOpts: { numItems: 20, cursor: null } });

// Mutation
const createPost = useMutation(api.posts.createPost);

async function handleSubmit(data: FormData) {
  await createPost({ title: data.title, content: data.content, tags: [] });
}

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