kitcn

Queries

Aggregates

Efficient counts, sums, and rankings with ORM aggregateIndex + rankIndex.

Scalar Metrics

Setup: Aggregate and rank indexes are an optional ORM subsystem. Register it once where you create the ORM:

convex/functions/generated/server.ts
import { createOrm } from 'kitcn/orm';
import { aggregateCapability } from 'kitcn/orm/aggregate-index';

export const orm = createOrm({ schema, capabilities: [aggregateCapability()] });

kitcn codegen writes this for schemas that declare an aggregateIndex or rankIndex, and leaves it out otherwise. Convex bundles everything a module statically imports and has no dynamic import(), and every procedure module imports generated/server.ts, so an unused registration would put the aggregate btree in every Convex function of the app. Declare the index first, then rerun kitcn codegen.

Without the capability, aggregateIndex/rankIndex schemas and filtered count() / aggregate() / groupBy() / rank() / relation _count throw a setup error. Unfiltered count() and aggregate({ _count: true }) are served by the native Convex count syscall and need nothing.

The easiest way to aggregate. Declare an aggregateIndex on your table, and the ORM handles everything — no triggers, no manual maintenance.

Important: Scalar metrics are strict no-scan. Filtered aggregate/count paths require a declared aggregateIndex matching the filter shape. Missing index → COUNT_NOT_INDEXED or AGGREGATE_NOT_INDEXED error. Unfiltered count() uses the native Convex count syscall.

Declaring Aggregate Indexes

Add aggregateIndex to your table definition:

convex/functions/schema.ts
const users = convexTable('users', { orgId: text().notNull(), status: text(), email: text() }, (t) => [
  aggregateIndex('by_org_status').on(t.orgId, t.status),
]);

.on(fields) defines the filter key — you'll query filtered counts/aggregates by these fields. For unfiltered aggregate blocks (like _sum/_avg/_min/_max), use .all(). Plain count() without where uses the native syscall. You can chain metric declarations:

convex/functions/schema.ts
const orders = convexTable(
  'orders',
  {
    orgId: text().notNull(),
    status: text(),
    customerEmail: text(),
    amount: integer(),
    score: integer(),
  },
  (t) => [
    aggregateIndex('all_metrics')
      .all()
      .count(t.customerEmail)
      .sum(t.amount)
      .avg(t.amount)
      .min(t.score)
      .max(t.score),
    aggregateIndex('by_org_status')
      .on(t.orgId, t.status)
      .count(t.customerEmail)
      .sum(t.amount)
      .avg(t.amount)
      .min(t.score)
      .max(t.score),
  ]
);

After deploying, the CLI runs aggregateBackfill automatically. Wait for aggregateBackfillStatus to report READY before querying.

count()

Count rows efficiently:

// Total users (native count syscall; no aggregateIndex required)
const totalUsers = await ctx.orm.query.users.count();

// Filtered count (requires aggregateIndex matching filter shape)
const activeUsers = await ctx.orm.query.users.count({
  where: { status: 'active' },
});

// Per-field non-null counts
const nonNulls = await ctx.orm.query.users.count({
  where: { status: 'active' },
  select: {
    _all: true,
    email: true,
  },
});

Filtered count() accepts index-compilable filters: eq, in, isNull, gt, gte, lt, lte, conjunction via AND, and bounded finite DNF OR when every branch is index-plannable on the same aggregateIndex. Field counts in select require .count(field) metric declarations on the matching aggregateIndex.

isNull: true matches a column that is explicitly null and a column that is absent from the document — a nullable column is optional, so a row inserted without it stores it absent. Matching null exactly ({ status: null } or { status: { eq: null } }) matches only the explicitly-null rows. count(), aggregate(), groupBy(), and relation _count use the same null semantics as findMany().

Missing or mismatched indexes throw deterministic errors — see Error Codes below.

For windowed counts with orderBy, skip, take, and cursor, see Windowed Count below.

aggregate()

For sum, avg, min, and max — use Prisma-style aggregate blocks:

const metrics = await ctx.orm.query.orders.aggregate({
  where: { orgId: 'org-1', status: 'paid' },
  _count: {
    _all: true,
    customerEmail: true,
  },
  _sum: {
    amount: true,
  },
  _avg: {
    amount: true,
  },
  _min: {
    score: true,
  },
  _max: {
    score: true,
  },
});

Same filter rules as count() — strict no-scan, index-backed. Supports bounded finite DNF OR when every branch is index-plannable and resolves to the same aggregateIndex. Unsupported args (callback where, relation filters) throw AGGREGATE_ARGS_UNSUPPORTED.

For windowed aggregate queries with orderBy, cursor, skip, and take, see Windowed Aggregate below.

groupBy()

For analytics-style queries, groupBy() lets you bucket rows and compute metrics per group. It follows the same strict no-scan guardrails:

  • by is required
  • every by field must have finite where constraints (eq/in/isNull)
  • an isNull-constrained by field emits one group keyed null, covering both explicitly-null and absent rows
  • orderBy supports by fields and selected metric fields
  • skip/take/cursor require explicit orderBy
  • having supports conjunction filters on by fields and selected metric outputs
  • OR/NOT in having are unsupported (AGGREGATE_FILTER_UNSUPPORTED)
const grouped = await ctx.orm.query.orders.groupBy({
  by: ['orgId'],
  where: {
    orgId: { in: ['org-1', 'org-2'] },
    status: 'paid',
  },
  _count: true,
  _sum: { amount: true },
  _avg: { amount: true },
  orderBy: [{ _count: 'desc' }, { _sum: { amount: 'desc' } }],
  having: { _count: { gt: 0 } },
  take: 10,
});

If any by field is unconstrained, groupBy() throws AGGREGATE_ARGS_UNSUPPORTED to avoid implicit bucket-wide scans.

Relation _count

You can load relation counts alongside query results — no N+1:

const projects = await ctx.orm.query.projects.findMany({
  with: { _count: { todos: true, members: true } },
});
// projects[0]._count?.todos => 5

You can filter the counted relations:

const users = await ctx.orm.query.users.findMany({
  with: {
    _count: {
      todos: { where: { completed: true } },
    },
  },
});
// users[0]._count?.todos => 3

Through-filtered _count also works for through() relations using indexed lookups:

const users = await ctx.orm.query.users.findMany({
  with: {
    _count: {
      memberTeams: { where: { name: 'Core' } },
    },
  },
});
// users[0]._count?.memberTeams => 1

Access counts via row._count?.relation ?? 0. Works on findMany, findFirst, and findFirstOrThrow.

Mutation returning({ _count })

You can also include _count in mutation returning selections — handy for returning fresh counts after a write:

const [user] = await ctx.orm
  .insert(usersTable)
  .values({ name: 'Alice' })
  .returning({
    id: usersTable.id,
    _count: { posts: true },
  });
// user._count?.posts => 0

This works with filters too:

const [updated] = await ctx.orm
  .update(usersTable)
  .set({ name: 'Bob' })
  .where(eq(usersTable.id, userId))
  .returning({
    id: usersTable.id,
    _count: { posts: { where: { status: 'published' } } },
  });
// updated._count?.posts => 2

Works on insert, update, and delete via split selection + relation count loading.

Ranked Access With rankIndex

For leaderboards, random picks, and ordered pagination, use rankIndex in schema and rank() in queries.

rank() is strict no-scan. You must query a declared rankIndex, and required partition fields must be constrained in where.

OperationDescription
rank().indexOf({ id })Position/rank of a document
rank().at(offset)Row at a specific position
rank().paginate({ cursor, limit })Ordered page traversal
rank().max() / rank().min()Extremes by rank order
rank().random()Random row from ranked set
rank().count() / rank().sum()Ranked-set count/sum

Declaring rankIndex

Declare a leaderboard index on a table:

convex/functions/schema.ts
const scores = convexTable(
  'scores',
  {
    gameId: text().notNull(),
    score: integer().notNull(),
    createdAt: timestamp().notNull(),
    userId: text().notNull(),
  },
  (t) => [
    rankIndex('leaderboard')
      .partitionBy(t.gameId)
      .orderBy({ column: t.score, direction: 'desc' })
      .orderBy({ column: t.createdAt, direction: 'asc' })
      .sum(t.score),

    rankIndex('global_leaderboard')
      .all()
      .orderBy({ column: t.score, direction: 'desc' }),
  ]
);

partitionBy(...) scopes each ranked set to a unique partition value — for example, one leaderboard per game.

Ranked Queries

Call rank() with the index name and a where clause that covers the partition fields:

const leaderboard = ctx.orm.query.scores.rank('leaderboard', {
  where: { gameId },
});

const top10 = await leaderboard.paginate({ cursor: null, limit: 10 });
const userRank = await leaderboard.indexOf({ id: userId });
const thirdPlace = await leaderboard.at(2);
const best = await leaderboard.max();
const worst = await leaderboard.min();
const randomPick = await leaderboard.random();
const total = await leaderboard.count();
const totalScore = await leaderboard.sum();

Leaderboard + User Stats

This is a common pattern: scalar metrics from ORM + ranking from rank():

const gameScores = await ctx.orm.query.scores.findMany({
  where: { userId: ctx.userId, gameId: input.gameId },
  limit: 1000,
});

if (gameScores.length === 0) {
  return { highScore: 0, averageScore: 0, gamesPlayed: 0, globalRank: null, percentile: 0 };
}

const highScore = Math.max(...gameScores.map((s) => s.score));
const averageScore =
  gameScores.reduce((sum, s) => sum + s.score, 0) / gameScores.length;

const lb = ctx.orm.query.scores.rank('leaderboard', {
  where: { gameId: input.gameId },
});
const globalRank = await lb.indexOf({ id: ctx.userId });
const totalPlayers = await lb.count();

Repair

If rank or aggregate state gets out of sync after a bad deploy or interrupted write path, you can trigger a full rebuild from the CLI:

kitcn aggregate rebuild

Rebuild clears stored state in scheduled batches, so it works on tables of any size. Indexes report CLEARING while draining and BUILDING while recomputing; wait for READY before querying them again.

Writes targeting a declared index in CLEARING fail before the document write. Retry them after the index advances to BUILDING or READY.

Automatic pruning follows aggregate lifecycle state, so kickoff reads stay bounded as backing tables grow. To recover state-less storage, invoke generated/aggregate:aggregateBackfill in prune mode with exact tableName and indexName arguments.

Best Practices

Use partitionBy for Isolation

// ✅ Partition per tenant to isolate write hot spots
rankIndex('tenant_scores')
  .partitionBy(t.tenantId)
  .orderBy({ column: t.score, direction: 'desc' });

// ❌ Global rank can create cross-tenant contention
rankIndex('global_scores')
  .all()
  .orderBy({ column: t.score, direction: 'desc' });

Design orderBy for Query Patterns

// ✅ Order matches product reads: highest score first, oldest tie first
rankIndex('game_scores')
  .partitionBy(t.gameId)
  .orderBy({ column: t.score, direction: 'desc' })
  .orderBy({ column: t.createdAt, direction: 'asc' });

// ❌ Order does not match "top score" queries
rankIndex('game_scores_by_time')
  .partitionBy(t.gameId)
  .orderBy({ column: t.createdAt, direction: 'asc' });

When to Use

NeedUse
Counts, sums, averagesScalar Metrics (aggregateIndex + ORM count()/aggregate())
Relation countsRelation _count (with: { _count: { ... } })
Rankings, leaderboardsRanked Access With rankIndex (indexOf, at, paginate)
Random document accessRanked Access With rankIndex (random, at)
Sorted paginationRanked Access With rankIndex (paginate({ cursor, limit }))
Non-table dataModel it as a table, then use Scalar Metrics or Ranked Access With rankIndex

API Reference

Prisma Parity Matrix (No-Scan)

Prisma featureStatusNotes
aggregate({ _count/_sum/_avg/_min/_max, where })SupportedBucket-backed, no base-table scan fallback
aggregate({ _sum }) nullabilitySupportedReturns null for empty/all-null sets
groupBy({ by, where, _count/_sum/_avg/_min/_max })Supportedby fields must be finite-constrained (eq/in/isNull) in where
groupBy({ having/orderBy/skip/take/cursor })PartialSupported for finite index-bounded groups with conjunction-only having
count()SupportedNative Convex count syscall
count({ where })SupportedIndexed scalar subset
count({ where, select: { _all, field } })SupportedField counts require aggregateIndex.count(field)
findMany({ with: { _count: { relation: true } } })SupportedIndexed relation counts
findMany({ with: { _count: { relation: { where } } } })SupportedDirect relation scalar filters
aggregate({ orderBy/take/skip/cursor })PartialorderBy/cursor supported; skip/take is _count-only in v1
Advanced aggregate/count filters (OR/NOT/string/relation)PartialBounded finite DNF OR rewrite is supported when branches resolve to one aggregateIndex; NOT/string/relation filters are blocked
Relation _count nested relation filterBlockedRELATION_COUNT_FILTER_UNSUPPORTED
findMany({ distinct })BlockedNot available under strict no-scan contract. Use select().distinct({ fields })
Relation _count filtered through relationSupportedIndexed through() relation filters
Mutation return _count paritySupportedreturning({ _count }) on insert/update/delete

Windowed Count

You can count rows within a window defined by ordering and bounds:

// Count with skip/take (pagination window)
const windowCount = await ctx.orm.query.todos.count({
  where: { projectId },
  orderBy: { createdAt: 'desc' },
  skip: 10,
  take: 50,
});

// Count with cursor (rows after a specific value)
const afterCursor = await ctx.orm.query.todos.count({
  where: { projectId },
  orderBy: { createdAt: 'desc' },
  cursor: { createdAt: lastSeen },
});
  • orderBy — sort direction for window bounds
  • skip — number of rows to skip before counting
  • take — max rows to count within the window
  • cursor — start counting after a specific field value (requires orderBy, single field in v1)

count({ select: { field: true } }) with skip/take/cursor is not supported in v1. Use count() or count({ select: { _all: true } }) for windowed counts.

Windowed Aggregate

aggregate() also supports window args with the same strict no-scan guardrails:

  • orderBy + cursor: supported for _count/_sum/_avg/_min/_max
  • skip/take: supported for _count only in v1
const metrics = await ctx.orm.query.orders.aggregate({
  where: { orgId: 'org-1' },
  orderBy: { amount: 'desc' },
  skip: 10,
  take: 100,
  _sum: { amount: true },
  _count: { _all: true },
});

skip/take with non-count metrics throws AGGREGATE_ARGS_UNSUPPORTED because metric window skip/take is not bucket-computable under strict no-scan in v1.

Error Codes

ErrorCause
COUNT_NOT_INDEXEDNo aggregateIndex matches the filter shape
COUNT_FILTER_UNSUPPORTEDFilter uses unsupported operators (NOT, string, relation, or non-finite OR), or a range count/aggregate exceeds aggregateWorkBudget work units. Bucket scans cost one unit; _min and _max also cost one extrema read per matching bucket.
COUNT_INDEX_BUILDINGIndex is still backfilling
COUNT_RLS_UNSUPPORTEDCalled in an RLS-restricted context

_sum Nullability

_sum returns null for empty sets or when all field values are null (Prisma-compatible):

// Empty table or all-null amounts → { _sum: { amount: null } }
// Non-empty with values → { _sum: { amount: 1500 } }

Limitations

ConsiderationGuideline
Update frequencyHigh-frequency updates to nearby keys cause contention
Key sizeKeep composite keys reasonable (3-4 components max)
Partition cardinalityHigh-cardinality partitionBy values increase rank index overhead
Query patternsDesign keys for actual needs, not hypothetical

findMany({ distinct }) (Unsupported)

findMany({ distinct }) is not available to keep strict index-backed no-scan guarantees.

If you need deduplication in query pipelines, use:

const page = await ctx.orm.query.todos
  .select()
  .distinct({ fields: ['status'] })
  .paginate({ cursor: null, limit: 100 });

Next Steps

Triggers

Rate Limiting

Pagination

Offset, cursor, composed, and key-based pagination in the ORM

Mutations

Insert, update, and delete operations with Drizzle-style builders

On this page

Scalar MetricsDeclaring Aggregate Indexescount()aggregate()groupBy()Relation _countMutation returning({ _count })Ranked Access With rankIndexDeclaring rankIndexRanked QueriesLeaderboard + User StatsRepairBest PracticesUse partitionBy for IsolationDesign orderBy for Query PatternsWhen to UseAPI ReferencePrisma Parity Matrix (No-Scan)Windowed CountWindowed AggregateError Codes_sum NullabilityLimitationsfindMany({ distinct }) (Unsupported)Next Steps