Data modeling

Schema patterns for the most common app shapes — multi-tenant SaaS, hierarchies, soft deletes, audit trails, polymorphism.

Data modeling is where architectural decisions cement. Get the schema right early; it’s the most expensive layer to change later.

Common patterns

Multi-tenant scoping

Every domain table has a workspaceId. Every query filters by it.

projects: defineTable({
  workspaceId: v.id("workspaces"),
  name: v.string(),
  // ...
}).index("by_workspace", ["workspaceId"])

See Multi-tenancy.

Soft delete

items: defineTable({
  // ...
  deletedAt: v.optional(v.number()),  // null when active
}).index("by_deleted", ["deletedAt"])

Active queries filter deletedAt === undefined. Cleanup job deletes hard after 30 days.

Audit trail

audits: defineTable({
  actorId:    v.id("users"),
  action:     v.string(),
  entityType: v.string(),
  entityId:   v.string(),
  before:     v.optional(v.any()),
  after:      v.optional(v.any()),
  at:         v.number(),
}).index("by_entity", ["entityType", "entityId"])
  .index("by_actor", ["actorId"])

Insert from every write mutation.

Hierarchies (tree)

nodes: defineTable({
  parentId: v.optional(v.id("nodes")),
  name:     v.string(),
  // ...
}).index("by_parent", ["parentId"])

For deep hierarchies needing fast ancestry lookup, also store path (array of ancestor IDs) — slower writes, instant ancestry queries.

Polymorphism

When the same field can point to different entity types:

comments: defineTable({
  authorId:    v.id("users"),
  body:        v.string(),
  targetType:  v.union(v.literal("post"), v.literal("task"), v.literal("project")),
  targetId:    v.string(),  // not v.id() because it's polymorphic
}).index("by_target", ["targetType", "targetId"])

Lookups still need the type to resolve the right table.

Many-to-many

A join table:

postTags: defineTable({
  postId: v.id("posts"),
  tagId:  v.id("tags"),
}).index("by_post", ["postId"])
  .index("by_tag",  ["tagId"])

Time series

For analytics-style data:

events: defineTable({
  type:      v.string(),
  payload:   v.any(),
  timestamp: v.number(),
}).index("by_type_and_timestamp", ["type", "timestamp"])

Partition by user/team if cardinality requires:

.index("by_team_type_timestamp", ["teamId", "type", "timestamp"])

Tips

Name fields what they are. dueAt is clearer than due_date (and deadline is clearer still in business contexts). Future-you will thank you.

Index fields you filter or sort by. Without an index, queries do table scans.

For event sourcing, store the events. Aggregate views can be re-derived; events are the truth.

Related

Convex schema

The schema definition reference.

Indexes

Make queries fast.

Multi-tenancy

Multi-tenant patterns.