Database Setup

ZapDev uses Convex as its real-time database backend. This guide covers deploying the schema, understanding the data model, and performing database operations.

Overview

Convex is a serverless database that provides:

Real-time reactive queries
Type-safe database operations
Automatic indexing and optimization
Built-in authentication integration
Serverless functions for business logic

Prerequisites

Before deploying the database:

Convex Account: Sign up at dashboard.convex.dev
Bun Installed: ZapDev uses Bun as the package manager
Environment Variables: Configure NEXT_PUBLIC_CONVEX_URL (see Environment Variables)

Deployment

Initial Deployment

Deploy your Convex schema to production:

# Deploy schema and functions
bun run convex:deploy

Expected Output:

✔ Deployed Convex functions to production
✔ Schema updates applied
✔ 15 tables created
✔ 42 indexes created

Time: 1-2 minutes

Development Mode

For local development, run Convex in dev mode:

# Terminal 1: Start Convex dev server
bun run convex:dev

# Terminal 2: Start Next.js dev server
bun run dev

Convex Dev provides:

Live schema updates
Development dashboard at http://localhost:3000/_convex
Real-time function logs
Database explorer

Database Schema

ZapDev’s database consists of 15 tables defined in convex/schema.ts.

Core Tables

projects

Stores user projects and their configurations. Fields:

name (string) - Project name
userId (string) - Owner’s user ID
framework (enum) - Framework choice: NEXTJS, ANGULAR, REACT, VUE, SVELTE
modelPreference (string, optional) - Preferred AI model
createdAt (number, optional) - Unix timestamp
updatedAt (number, optional) - Unix timestamp

Indexes:

by_userId - Find projects by user
by_userId_createdAt - User projects ordered by creation date

messages

Stores conversation messages between users and AI agents. Fields:

content (string) - Message text
role (enum) - USER or ASSISTANT
type (enum) - RESULT, ERROR, or STREAMING
status (enum) - PENDING, STREAMING, or COMPLETE
projectId (id) - Reference to parent project
createdAt (number, optional) - Unix timestamp
updatedAt (number, optional) - Unix timestamp

Indexes:

by_projectId - Messages for a project
by_projectId_createdAt - Project messages ordered by time

fragments

Stores generated code fragments and sandbox metadata. Fields:

messageId (id) - Reference to parent message
sandboxUrl (string) - E2B sandbox URL
title (string) - Fragment title
files (any) - Generated file contents
metadata (any, optional) - Additional metadata
framework (enum) - Framework used
createdAt (number, optional) - Unix timestamp
updatedAt (number, optional) - Unix timestamp

Indexes:

by_messageId - Fragments for a message

Usage & Rate Limiting

usage

Tracks user credit consumption. Fields:

userId (string) - User ID
points (number) - Credits consumed
expire (number, optional) - Expiration timestamp
planType (enum, optional) - free, pro, or unlimited

Indexes:

by_userId - User’s usage records
by_expire - For cleanup of expired records

Limits:

Free tier: 5 generations/day
Pro tier: 100 generations/day
Unlimited tier: No limits

rateLimits

Generic rate limiting for API endpoints. Fields:

key (string) - Rate limit identifier
count (number) - Request count in window
windowStart (number) - Window start timestamp
limit (number) - Maximum requests allowed
windowMs (number) - Window duration in milliseconds

Indexes:

by_key - Find limit by key
by_windowStart - Cleanup old windows

Subscriptions & Billing

subscriptions

Stores Polar.sh subscription data. Fields:

userId (string) - User ID
polarSubscriptionId (string) - Polar.sh subscription ID
customerId (string) - Polar.sh customer ID
productId (string) - Product ID
priceId (string) - Price ID
status (enum) - active, past_due, canceled, unpaid, trialing
interval (enum) - monthly or yearly
currentPeriodStart (number) - Period start timestamp
currentPeriodEnd (number) - Period end timestamp
cancelAtPeriodEnd (boolean) - Auto-cancel flag
canceledAt (number, optional) - Cancellation timestamp
trialStart (number, optional) - Trial start
trialEnd (number, optional) - Trial end
metadata (any, optional) - Additional data
createdAt (number) - Creation timestamp
updatedAt (number) - Last update timestamp

Indexes:

by_userId - User’s subscriptions
by_polarSubscriptionId - Find by Polar ID
by_customerId - Find by customer
by_status - Filter by status

polarCustomers

Maps users to Polar.sh customers. Fields:

userId (string) - User ID
polarCustomerId (string) - Polar.sh customer ID
createdAt (number) - Creation timestamp
updatedAt (number) - Last update timestamp

Indexes:

by_userId - Find by user
by_polarCustomerId - Find by Polar customer ID

Imports & OAuth

imports

Tracks Figma and GitHub imports. Fields:

userId (string) - User ID
projectId (id) - Target project
messageId (id, optional) - Associated message
source (enum) - FIGMA or GITHUB
sourceId (string) - External resource ID
sourceName (string) - Resource name
sourceUrl (string) - Resource URL
status (enum) - PENDING, PROCESSING, COMPLETE, FAILED
metadata (any, optional) - Import metadata
error (string, optional) - Error message
createdAt (number) - Creation timestamp
updatedAt (number) - Last update timestamp

Indexes:

by_userId - User’s imports
by_projectId - Project imports
by_status - Filter by status

oauthConnections

Stores OAuth tokens for external services. Fields:

userId (string) - User ID
provider (enum) - figma or github
accessToken (string) - Encrypted access token
refreshToken (string, optional) - Encrypted refresh token
expiresAt (number, optional) - Token expiration
scope (string) - OAuth scopes
metadata (any, optional) - Provider-specific data
createdAt (number) - Creation timestamp
updatedAt (number) - Last update timestamp

Indexes:

by_userId - User’s connections
by_userId_provider - Specific provider connection

Security: OAuth tokens are encrypted before storage. Never log or expose them.

Webhooks & Events

webhookEvents

Tracks webhook events from external services (Polar.sh). Fields:

eventId (string) - External event ID
eventType (string) - Event type (e.g., subscription.created)
status (enum) - received, processed, failed, retrying
payload (any) - Event payload
error (string, optional) - Error message
processedAt (number, optional) - Processing timestamp
retryCount (number) - Retry attempts
createdAt (number) - Receipt timestamp

Indexes:

by_eventId - Find by event ID (deduplication)
by_status - Filter by status
by_eventType - Filter by type
by_createdAt - Ordered events

pendingSubscriptions

Temporary storage for subscriptions awaiting user creation. Fields:

polarSubscriptionId (string) - Polar subscription ID
customerId (string) - Polar customer ID
eventData (any) - Subscription data
status (enum) - pending, resolved, failed
resolvedUserId (string, optional) - Linked user ID
error (string, optional) - Error message
createdAt (number) - Creation timestamp
resolvedAt (number, optional) - Resolution timestamp

Indexes:

by_polarSubscriptionId - Find by subscription
by_customerId - Find by customer
by_status - Filter by status

Attachments & Metadata

attachments

Stores file attachments for messages. Fields:

type (enum) - IMAGE, FIGMA_FILE, GITHUB_REPO
url (string) - File URL
width (number, optional) - Image width
height (number, optional) - Image height
size (number) - File size in bytes
messageId (id) - Parent message
importId (id, optional) - Related import
sourceMetadata (any, optional) - Source-specific data
createdAt (number, optional) - Creation timestamp
updatedAt (number, optional) - Last update timestamp

Indexes:

by_messageId - Attachments for a message

fragmentDrafts

Temporary storage for in-progress code fragments. Fields:

projectId (id) - Parent project
files (any) - Draft file contents
framework (enum) - Framework type
createdAt (number, optional) - Creation timestamp
updatedAt (number, optional) - Last update timestamp

Indexes:

by_projectId - Drafts for a project

Agent Runs

agentRuns

Tracks AI agent execution status. Fields:

projectId (id) - Target project
value (string) - User input/prompt
model (string, optional) - AI model used
framework (string, optional) - Framework choice
status (enum) - PENDING, RUNNING, COMPLETED, FAILED
runSource (enum) - WEBCONTAINER or INNGEST
claimedBy (string, optional) - Execution worker ID
messageId (id, optional) - Result message
fragmentId (id, optional) - Result fragment
error (string, optional) - Error message
createdAt (number) - Creation timestamp
updatedAt (number) - Last update timestamp
completedAt (number, optional) - Completion timestamp

Indexes:

by_projectId - Runs for a project
by_projectId_status - Project runs by status
by_status - All runs by status
by_projectId_createdAt - Project runs ordered by time

Database Operations

Common Queries

Get User’s Projects

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

export const getUserProjects = query({
  args: { userId: v.string() },
  handler: async (ctx, args) => {
    return await ctx.db
      .query("projects")
      .withIndex("by_userId", (q) => q.eq("userId", args.userId))
      .order("desc")
      .collect();
  },
});

Get Project Messages

export const getProjectMessages = query({
  args: { projectId: v.id("projects") },
  handler: async (ctx, args) => {
    return await ctx.db
      .query("messages")
      .withIndex("by_projectId_createdAt", (q) => 
        q.eq("projectId", args.projectId)
      )
      .order("asc")
      .collect();
  },
});

Mutations

Create Project

import { mutation } from "./_generated/server";
import { v } from "convex/values";

export const createProject = mutation({
  args: {
    name: v.string(),
    userId: v.string(),
    framework: v.union(
      v.literal("NEXTJS"),
      v.literal("ANGULAR"),
      v.literal("REACT"),
      v.literal("VUE"),
      v.literal("SVELTE")
    ),
  },
  handler: async (ctx, args) => {
    const projectId = await ctx.db.insert("projects", {
      name: args.name,
      userId: args.userId,
      framework: args.framework,
      createdAt: Date.now(),
      updatedAt: Date.now(),
    });
    return projectId;
  },
});

Update Subscription Status

export const updateSubscriptionStatus = mutation({
  args: {
    subscriptionId: v.id("subscriptions"),
    status: v.union(
      v.literal("active"),
      v.literal("past_due"),
      v.literal("canceled"),
      v.literal("unpaid"),
      v.literal("trialing")
    ),
  },
  handler: async (ctx, args) => {
    await ctx.db.patch(args.subscriptionId, {
      status: args.status,
      updatedAt: Date.now(),
    });
  },
});

Schema Migrations

Convex automatically handles schema migrations.

Adding a New Field

Update convex/schema.ts:

projects: defineTable({
  name: v.string(),
  userId: v.string(),
  framework: frameworkEnum,
  description: v.optional(v.string()), // New field
  createdAt: v.optional(v.number()),
  updatedAt: v.optional(v.number()),
})

Deploy the change:

bun run convex:deploy

Convex will automatically:

Add the new field to the schema
Preserve existing data
Make the field available immediately

Adding an Index

Update schema with new index:

projects: defineTable({
  // ... fields
})
  .index("by_userId", ["userId"])
  .index("by_framework", ["framework"]) // New index

Deploy:

bun run convex:deploy

Convex will build the index in the background.

Performance Optimization

Best Practices

Never use .filter() in queries! Always use .withIndex() to avoid O(N) scans.

Bad:

// ❌ Scans entire table
const projects = await ctx.db
  .query("projects")
  .filter((q) => q.eq(q.field("userId"), userId))
  .collect();

Good:

// ✅ Uses index
const projects = await ctx.db
  .query("projects")
  .withIndex("by_userId", (q) => q.eq("userId", userId))
  .collect();

Pagination

For large result sets, use pagination:

export const paginatedProjects = query({
  args: {
    userId: v.string(),
    paginationOpts: paginationOptsValidator,
  },
  handler: async (ctx, args) => {
    return await ctx.db
      .query("projects")
      .withIndex("by_userId_createdAt", (q) => 
        q.eq("userId", args.userId)
      )
      .order("desc")
      .paginate(args.paginationOpts);
  },
});

Caching

Convex queries are reactive and automatically cached. To optimize:

Split queries - Separate frequently changing data from static data
Use specific indexes - More specific indexes = better performance
Limit result size - Use pagination for large datasets

Monitoring

Convex Dashboard

Monitor your database at dashboard.convex.dev:

Data Tab - View and edit table data
Functions Tab - Monitor query/mutation execution
Logs Tab - View function logs and errors
Usage Tab - Track database operations and bandwidth

Key Metrics

Database reads/writes - Track operation volume
Function duration - Identify slow queries
Error rate - Monitor failed operations
Bandwidth - Track data transfer

Troubleshooting

”Schema validation failed”

Cause: Data doesn’t match schema definition Solution: Check that all required fields are provided and types match:

// ✅ Correct
await ctx.db.insert("projects", {
  name: "My Project",
  userId: "user_123",
  framework: "NEXTJS", // Valid enum value
  createdAt: Date.now(),
});

// ❌ Wrong - invalid framework
await ctx.db.insert("projects", {
  name: "My Project",
  userId: "user_123",
  framework: "React", // Should be "REACT"
});

“Index not found”

Cause: Query references non-existent index Solution: Add the index to convex/schema.ts and deploy:

projects: defineTable({
  // ... fields
})
  .index("by_userId", ["userId"]) // Add this

Slow Queries

Cause: Missing indexes or inefficient queries Solution:

Check Convex Dashboard → Functions → Slow queries
Add indexes for frequently queried fields
Use pagination for large result sets
Avoid .filter() - use .withIndex() instead

Backup & Recovery

Convex automatically backs up your data:

Point-in-time recovery - Available for up to 30 days
Snapshot exports - Download data as JSON
Automatic replication - Multi-region redundancy

To export data:

Go to Convex Dashboard → Settings → Export
Select tables to export
Download JSON files

Next Steps

Deploy Application

Complete your deployment with Vercel

Environment Variables

Review all required configuration

Setup

Platforms

Configuration

​Overview

​Prerequisites

​Deployment

​Initial Deployment

​Development Mode

​Database Schema

​Core Tables

​projects

​messages

​fragments

​Usage & Rate Limiting

​usage

​rateLimits

​Subscriptions & Billing

​subscriptions

​polarCustomers

​Imports & OAuth

​imports

​oauthConnections

​Webhooks & Events

​webhookEvents

​pendingSubscriptions

​Attachments & Metadata

​attachments

​fragmentDrafts

​Agent Runs

​agentRuns

​Database Operations

​Common Queries

​Get User’s Projects

​Get Project Messages

​Mutations

​Create Project

​Update Subscription Status

​Schema Migrations

​Adding a New Field

​Adding an Index

​Performance Optimization

​Best Practices

​Pagination

​Caching

​Monitoring

​Convex Dashboard

​Key Metrics

​Troubleshooting

​”Schema validation failed”

​“Index not found”

​Slow Queries

​Backup & Recovery

​Next Steps

Deploy Application

Environment Variables

​Additional Resources

Build docs developers (and LLMs) love

Overview

Prerequisites

Deployment

Initial Deployment

Development Mode

Database Schema

Core Tables

projects

messages

fragments

Usage & Rate Limiting

usage

rateLimits

Subscriptions & Billing

subscriptions

polarCustomers

Imports & OAuth

imports

oauthConnections

Webhooks & Events

webhookEvents

pendingSubscriptions

Attachments & Metadata

attachments

fragmentDrafts

Agent Runs

agentRuns

Database Operations

Common Queries

Get User’s Projects

Get Project Messages

Mutations

Create Project

Update Subscription Status

Schema Migrations

Adding a New Field

Adding an Index

Performance Optimization

Best Practices

Pagination

Caching

Monitoring

Convex Dashboard

Key Metrics

Troubleshooting

”Schema validation failed”

“Index not found”

Slow Queries

Backup & Recovery

Next Steps

Additional Resources