Database Design

Demo Only

This is a dummy documentation page for demo purposes—there's no real product idea here. Content is placeholder and not based on an actual project.

Document your Convex database schema, tables, and relationships.

This is a template. Define your actual schema based on your product requirements!

Schema Overview

Convex uses a document-based NoSQL database with TypeScript-first schemas defined in convex/schema.ts.

Core Tables

Users Table

users: defineTable({
  betterAuthId: v.string(),      // BetterAuth user ID (unique)
  email: v.string(),             // User email
  name: v.optional(v.string()),  // Display name
  imageUrl: v.optional(v.string()), // Profile picture
  
  // Subscription data
  subscriptionStatus: v.optional(v.string()), // "active", "canceled", etc.
  subscriptionTier: v.optional(v.string()),   // "free", "pro", "enterprise"
  polarCustomerId: v.optional(v.string()),    // Polar customer ID
  
  // Metadata
  createdAt: v.number(),         // Timestamp
  lastActiveAt: v.optional(v.number()), // Last activity
})
.index("by_better_auth_id", ["betterAuthId"])
.index("by_email", ["email"])
.index("by_subscription_status", ["subscriptionStatus"])

Relationships:

One user has many [your resource]
User belongs to subscription via polarCustomerId

Subscriptions Table (Example)

subscriptions: defineTable({
  userId: v.id("users"),         // Reference to users table
  polarSubscriptionId: v.string(), // Polar subscription ID
  
  status: v.string(),            // "active", "canceled", "trialing"
  tier: v.string(),              // "starter", "pro", "enterprise"
  
  currentPeriodStart: v.number(), // Unix timestamp
  currentPeriodEnd: v.number(),   // Unix timestamp
  cancelAtPeriodEnd: v.boolean(),
  
  // Pricing
  amount: v.number(),            // Amount in cents
  currency: v.string(),          // "USD", etc.
  interval: v.string(),          // "month", "year"
  
  createdAt: v.number(),
  updatedAt: v.number(),
})
.index("by_user", ["userId"])
.index("by_polar_id", ["polarSubscriptionId"])
.index("by_status", ["status"])

Custom Tables

Define your application-specific tables here.

Example: Projects Table

projects: defineTable({
  name: v.string(),
  description: v.optional(v.string()),
  ownerId: v.id("users"),        // Reference to owner
  
  // Project data
  status: v.string(),            // "draft", "active", "archived"
  settings: v.object({
    // Define project-specific settings
    isPublic: v.boolean(),
    tags: v.array(v.string()),
  }),
  
  createdAt: v.number(),
  updatedAt: v.number(),
})
.index("by_owner", ["ownerId"])
.index("by_status", ["status"])

Example: Tasks Table

tasks: defineTable({
  projectId: v.id("projects"),   // Reference to project
  assigneeId: v.optional(v.id("users")), // Assigned user
  
  title: v.string(),
  description: v.optional(v.string()),
  status: v.string(),            // "todo", "in_progress", "done"
  priority: v.string(),          // "low", "medium", "high"
  
  dueDate: v.optional(v.number()),
  completedAt: v.optional(v.number()),
  
  createdAt: v.number(),
  updatedAt: v.number(),
})
.index("by_project", ["projectId"])
.index("by_assignee", ["assigneeId"])
.index("by_status", ["status"])
.index("by_due_date", ["dueDate"])

Indexes

Indexes are crucial for query performance. Define indexes for:

Single-Field Indexes

.index("by_field_name", ["fieldName"])

Use for queries like:

ctx.db.query("tableName")
  .withIndex("by_field_name", (q) => q.eq("fieldName", value))

Compound Indexes

.index("by_user_and_status", ["userId", "status"])

Use for queries like:

ctx.db.query("tableName")
  .withIndex("by_user_and_status", (q) => 
    q.eq("userId", userId).eq("status", "active")
  )

Relationships

Document how your tables relate to each other:

users (1) ──────< (many) projects
  │
  └────────< (many) subscriptions
  
projects (1) ────< (many) tasks
  │
  └───────< (many) project_members

tasks (1) ───────> (1) assignee (user)

Data Validation

Convex validators ensure data integrity:

// String with constraints
email: v.string(), // Must be valid email format

// Optional fields
middleName: v.optional(v.string()),

// Enums using union
status: v.union(
  v.literal("draft"),
  v.literal("active"),
  v.literal("archived")
),

// Arrays
tags: v.array(v.string()),

// Nested objects
settings: v.object({
  theme: v.string(),
  notifications: v.boolean(),
}),

// Numbers with constraints
age: v.number(), // Validated as number
rating: v.number(), // 1-5 range enforced in mutation