Day 2 — Schema, documents, indexes

The one idea. In Convex you don't write a query and hope the planner finds an index. You declare the index, and the query names it. Access patterns are a design-time decision, not a runtime optimisation.

Time: ~90 min · Video: The Ultimate Convex Beginner Tutorial — Part 1 (8:16) · You ship: a typed schema, and a query you have proved uses an index.

#1. Documents, but typed

Convex stores documents. Each gets _id and _creationTime for free. Unlike a schemaless store, you declare the shape in convex/schema.ts and the types flow all the way to your React hooks with no codegen step you have to remember to run.

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

export default defineSchema({
  messages: defineTable({
    body: v.string(),
    user: v.id("users"),
  }),
  users: defineTable({
    name: v.string(),
    tokenIdentifier: v.string(),
  }).index("by_token", ["tokenIdentifier"]),
});

v.id("users") is the part to notice. It is not "a string that happens to hold an id" — it is typed as a reference to that table, and it will fail validation if you hand it an id from somewhere else. That is a foreign key that TypeScript can see.

Validators you have available:

v.id(table)  v.null()  v.int64()  v.number()  v.boolean()  v.string()  v.bytes()
v.commitTs() v.any()   v.array()  v.object()  v.record(k,v)
v.union(...) v.literal(x)  v.optional(x)  v.nullable(x)

v.number() is Float64. Reach for v.int64() only when you genuinely need BigInt semantics — and note that if you're modelling money, neither one is the right answer; store integer minor units in a v.number() and never let a float near a currency.

#2. Reading

const task = await ctx.db.get("tasks", args.taskId);

await ctx.db.query("users").take(5);
await ctx.db.query("counter").unique();            // throws unless exactly one
await ctx.db.query("messages").order("desc").collect();

Stop and read this twice. ctx.db.get("tasks", id) takes the table name first. Nearly every video tutorial — including the excellent ones in this repo — shows ctx.db.get(id). When Part 1's code doesn't typecheck for you, this is why. You are not confused; the ecosystem moved.

#3. Writing

const id = await ctx.db.insert("tasks", { text: args.text });
await ctx.db.patch("tasks", id, { tag: "bar" });      // shallow merge
await ctx.db.replace("tasks", id, { text: "new" });   // full overwrite
await ctx.db.delete("tasks", id);

patch merges shallowly. It is the one people get wrong: patching a nested object replaces that whole nested object, it does not deep-merge into it.

#4. Indexes — the day's real content

The story. A library with no catalogue. You want every book by one author. The only method available is to walk the shelves and read every spine. With 50 books that's a minute's work and you'd never bother building a catalogue. With 500,000 you are still walking at closing time. The catalogue isn't an optimisation you add when the library gets slow — it's the thing that decides whether a library is possible.

.filter() walks the shelves. .withIndex() uses the catalogue.

// ❌ scans the table, then throws most of it away
ctx.db.query("messages")
  .filter((q) => q.eq(q.field("author"), "Tom"))
  .collect();

// ✅ seeks directly, given .index("by_author", ["author"]) in the schema
await ctx.db.query("messages")
  .withIndex("by_author", (q) => q.eq("author", "Tom"))
  .collect();

Both return identical results. In a dev database of 50 seeded rows they take indistinguishable time. That is exactly why this bug reaches production: it is invisible at the scale where you write it and fatal at the scale where you need it.

Same warning for a bare .collect(). ctx.db.query("movies").collect() says "load the entire table into memory," and it will keep working right up until it doesn't. Bound every read: an index, .take(n), or .paginate().

#Composite indexes and prefix rules

.index("by_team_and_user", ["team", "user"])

Field order matters. This index serves:

.withIndex("by_team_and_user", (q) => q.eq("team", teamId))                    // ✅ prefix
.withIndex("by_team_and_user", (q) => q.eq("team", teamId).eq("user", userId)) // ✅ full

...but not a query on user alone. And because the composite already serves team-only queries, a separate by_team index is redundant — delete it. Redundant indexes cost you write throughput and storage and buy nothing.

#Quick check 1

You have .index("by_org_and_status_and_created", ["org", "status", "created"]). Which of these can use it? (a) org only (b) status only (c) org + status (d) org + created, skipping status

Answer

(a) ✅ and (c) ✅ — both are prefixes. (b) ❌ — status is not a prefix; the index is ordered by org first. (d) ❌ — you cannot skip a middle field and still seek; you'd get everything for that org and filter created afterwards, which is a scan wearing a disguise.

#Quick check 2

Your by_author query is instant in dev and times out in production. The code did not change. What happened?

Answer

Nothing happened to the code — the table got big. You almost certainly used .filter() or an unbounded .collect(), which are O(table). Dev had 50 rows. This is the single most common Convex production incident, and it is why indexes are a Day 2 topic rather than a Day 7 one.

#Build task

Build a link-saver schema — you will grow this app for the rest of the bootcamp.

1. Two tables: links (url, title, note optional, ownerId, archived boolean) and tags (name, linkId).
2. Use v.optional() for note and v.id("links") for tags.linkId.
3. Write listByOwner twice: once with .filter(), once with .withIndex().
4. Seed ~2,000 rows (a loop in an internalMutation is fine). Run both. Compare the function timings in the dashboard Logs tab.
5. Delete the .filter() version. Keep the number you measured.

Step 4 is the point of the day. Do not skip it — a measured number changes your instincts in a way that reading this page cannot.

#Checkpoint

1. Where does an index get declared, and where does it get named?
2. Why is .filter() dangerous specifically because it works fine in development?
3. Given by_team_and_user, why is a separate by_team index waste?
4. What's the difference between patch and replace on a document with a nested object?
5. Write, from memory, the current call to fetch one tasks document by id.

#Cheat strip

schema.ts : defineSchema({ table: defineTable({...}).index("by_x", ["x"]) })
read      : ctx.db.get("tasks", id)          ← TABLE NAME FIRST
             .query(t).withIndex(name, q => q.eq(f, v)).take(n) / .unique() / .first()
write     : insert(t, doc) · patch(t, id, partial) · replace(t, id, doc) · delete(t, id)
index     : prefix rule — [a,b,c] serves a, ab, abc. Never b alone.
            composite exists ⇒ delete the prefix-only index
never     : unbounded .collect() · .filter() as a substitute for an index

#Tomorrow

Day 3 wires this to React and you finally see the payoff: two browser windows, one mutation, zero lines of WebSocket code.