aggregate design

a repeated owner-scoped question answered by scanning history grows in cost and latency with the data, and each route silently re-decides the same tradeoff. a declared aggregate makes the cost bounded and constant, the update atomic with the canonical write, and the value rebuildable from facts when it drifts. without that contract the summary is an invisible authority anyone can read but no one owns or repairs.

design aggregates from the query that must stay bounded. an aggregate answers one exact owner-scoped question from a durable row or projection — not loading every related row, reducing it in application memory, and calling the result "current state."

an aggregate row has a domain-prefixed semantic name, owner foreign key, named summary fields, source/update timestamp, and unique owner identity. index the exact owner read, not speculative report shapes. for derived aggregates: retain canonical source facts, make the projector replay-safe; the projection may be deleted/rebuilt without losing business truth.

Convex makes the tradeoff visible: an aggregate data structure answers count, sum, rank, range, or offset questions in logarithmic lookup work instead of loading every source row. the pattern matters beyond Convex — the aggregate is a separately maintained data structure with explicit key/range/namespace semantics, not a convenient collect followed by a reducer.

tradeoff: a single global counter or ever-increasing sort key can become a contention/hotspot boundary under frequent writes. scope the aggregate to the shortest exact owner, or choose a deliberate sharding/projector strategy, before optimizing read latency.

1. create enough history that a full scan visibly differs from one keyed aggregate or indexed page.
2. inspect the query plan: the request must be bounded by key/index/page.
3. repeat the same canonical write/delivery: the aggregate must not double-apply.
4. rebuild the aggregate from canonical facts and compare result/version.
5. force a lag/failure: the interface reports derived freshness honestly, never presents the projection as canonical fact.

bad

// route handler for workflow summary
async function getWorkflowSummary(workflowId: string) {
  const workflowRuns = await db
    .select()
    .from(workflowRun)
    .where(eq(workflowRun.workflow_id, workflowId));

  const completedCount = workflowRuns.filter((run) => {
    return run.status === WorkflowRunStatusEnum.Completed;
  }).length;

  return { completedCount };
}

loads every historical row — potentially thousands — into application memory, then filters synchronously for one count. three cascading failures under scale: (1) unbounded rows drain connection pool and memory, (2) response time grows linearly with history depth, (3) repeated calls for the same workflow repeat the full scan and hotspot under read concurrency. the interface delegates all cost and latency to callers; no cache, no boundary, no owner.

good

// schema: aggregate row maintains count with canonical writes
create table workflow_summary (
  workflow_id text primary key references workflow(id),
  completed_run_count integer not null default 0,
  updated_at text not null,
  unique(workflow_id)
);

// service owns the aggregate update with the write
async function completeWorkflowRun(runId: string) {
  await db.transaction(async (tx) => {
    await tx.update(workflowRun).set({ status: WorkflowRunStatusEnum.Completed }).where(eq(workflowRun.id, runId));
    await tx.update(workflowSummary).set({ completed_run_count: sql`completed_run_count + 1` }).where(eq(workflowSummary.workflow_id, workflowId));
  });
}

// route handler now reads bounded aggregate
async function getWorkflowSummary(workflowId: string) {
  const summary = await db.select().from(workflowSummary).where(eq(workflowSummary.workflow_id, workflowId));
  return { completedCount: summary.completed_run_count };
}

the owner-scoped aggregate is updated atomically with the canonical write — constant response time regardless of history depth. if stale, an owned rebuild job recomputes it idempotently from facts. reads never scan history; routes never own the cost tradeoff.

database design · table naming · durable handoffs · durable process-step record