Convex Database

How CodeCourier uses Convex as its reactive real-time database for all application state, including schema design, queries, mutations, and real-time subscriptions.

8 min read
convexdatabasereactive

Convex is the reactive backend platform that serves as the central data store and coordination layer for CodeCourier. Every piece of application state -- users, projects, sandboxes, workflows, runs, messages, learnings, usage records, and more -- lives in Convex. What makes Convex unique compared to traditional databases is its built-in reactivity: when data changes, every subscribed client is automatically notified and re-renders with the latest state. This page explains how CodeCourier uses Convex, the schema design, and code patterns for working with the database.

What Convex Provides

  • Real-time subscriptions -- Queries automatically re-execute when their underlying data changes. The dashboard shows sandbox status, run progress, and messages in real time without polling.
  • Transactional mutations -- Every mutation runs in a serializable transaction. Reads and writes within a mutation are atomic, preventing race conditions.
  • Type-safe functions -- Queries, mutations, and actions are defined in TypeScript with full type inference. The Convex schema validates arguments and return types at runtime.
  • Actions for side effects -- Operations that call external services (E2B, Trigger.dev) are defined as actions, which can read data and schedule mutations but are not themselves transactional.
  • Built-in authentication -- Convex validates Clerk JWTs and makes the authenticated user identity available to every function.
  • Scheduled functions -- The ctx.scheduler.runAfter API enables deferred execution, used for async operations like learning extraction dispatch.
  • File storage -- Convex provides built-in file storage (the _storage table) used for reference images and project logos.

Schema Overview

The Convex schema is defined in convex/schema.ts and contains over twenty tables. Here are the major entity groups:

Identity and Access

  • users -- User accounts synced from Clerk. Indexed by clerkId and email.
  • projects -- Top-level organizational units. Indexed by ownerId and slug.
  • projectMembers -- Many-to-many relationship between users and projects with roles (owner, admin, member) and invitation status.
  • projectSettings -- Per-project configuration including system prompts, CLAUDE.md, environment variables, selected skills and commands, and deploy keys.
  • userSettings -- Per-user preferences like last active project.

API Keys

  • apiKeys -- User-level provider API keys (E2B, Anthropic, OpenRouter, OpenAI, GitHub). Encrypted storage with last-four display.
  • projectProviderKeys -- Project-level provider API keys that override user-level keys.
  • projectApiKeys -- CodeCourier REST API keys for programmatic access. SHA-256 hashed, revocable, with usage tracking.

Execution

  • sandboxes -- E2B sandbox instances with status, configuration, and lifecycle tracking.
  • sandboxMessages -- Conversation messages between users and AI agents within sandboxes.
  • workflows -- Workflow blueprints defining pipeline type, steps, and default configuration.
  • runs -- Workflow execution instances with status, progress, and PR tracking.
  • runSteps -- Individual execution steps within runs (designer, checker, optimizer, etc.).
  • workChains -- Sequential issue execution chains.

AI Knowledge

  • personas -- AI agent personalities with custom instructions, skills, and model preferences.
  • skills / skillFiles -- Skill definitions and their file contents.
  • commands -- Reusable command definitions.
  • scripts -- Script definitions for sandbox execution.
  • learnings -- Extracted knowledge from sandbox sessions.
  • learningVersions -- Compiled learning snapshots for injection into future sessions.

Issues

  • issueSessions -- Issue discovery session records.
  • issues -- Individual issues discovered during sessions or created manually.

Analytics and Usage

  • usageCostRates -- Cost rates for different services (Claude Code, E2B, Trigger.dev, Convex, etc.) with configurable rate types and model tiers.
  • usageRecords -- Detailed usage data per project, service, and date, with optional enterprise tracking fields for token counts, model IDs, and step attribution.
  • projectCounters -- Denormalized counters for quick access to sandbox, run, workflow, and member counts.
  • dailyStats -- Per-day aggregated statistics for project activity.
  • notifications -- User notification records for run completions, PR events, and team activity.

Real-Time Queries

CodeCourier's dashboard leverages Convex's real-time query subscriptions extensively. When you view a sandbox conversation, the sandboxMessages.listBySandbox query subscribes to all messages for that sandbox. When a new message arrives from the AI agent (written by a Trigger.dev task through the callback endpoint), the query automatically re-executes and the UI updates instantly -- no polling, no WebSocket setup, no manual invalidation.

Key queries used in the dashboard:

  • sandboxes.listPaginated -- Powers the sandbox list view with cursor-based pagination.
  • runs.listPaginated -- Powers the runs list view.
  • workflows.listForProject -- Loads all workflows for the current project.
  • sandboxMessages.listBySandbox -- Streams the conversation in real time.
  • runSteps.listByRun -- Shows step-by-step progress during workflow execution.
  • usage.getProjectUsageSummary -- Real-time usage dashboard data.

Mutations

Mutations in Convex are transactional writes. CodeCourier uses mutations for all user-initiated state changes:

  • Creating, updating, and deleting workflows
  • Renaming sandboxes and runs
  • Managing project members and invitations
  • Generating and revoking API keys
  • Updating project settings
  • Recording usage and upserting cost rates

Internal mutations (prefixed with internal) are used by Trigger.dev callbacks and scheduled functions. They are not accessible from the frontend.

Indexes

The schema defines extensive indexes for efficient querying. Every table has at least one index beyond the default _id index. Key index patterns include:

  • by_user / by_project -- Filter records by ownership.
  • by_deleted -- Filter out soft-deleted records efficiently.
  • by_status -- Filter by lifecycle state.
  • by_project_date -- Time-series queries for analytics.
  • by_key -- API key lookup by hash.

Working with Convex in CodeCourier

Frontend Queries

The dashboard uses the useQuery hook from the Convex React client:

const sandboxes = useQuery(api.sandboxes.listPaginated, {
  paginationOpts: { numItems: 20 }
});

Queries return undefined while loading and automatically update when the underlying data changes.

Frontend Mutations

Mutations are called via the useMutation hook:

const rename = useMutation(api.sandboxes.renameSandbox);
await rename({ id: sandboxId, name: "New Name" });

Frontend Actions

Actions that trigger external operations use useAction:

const launch = useAction(api.sandboxActions.launchSandboxes);
await launch({ config, prompt, projectId });