Example: projects.list scans the whole table to authorize owner-or-member, and every scanned row joins the read set #443

Summary

projects.list authorizes owner-or-member in JS over an unindexed scan. It walks projects from the newest end and stops only once limit authorized rows are matched, so a user with no or only-old projects drains the whole table — and every scanned row joins the reactive read set, so any write to any project re-runs the subscription.

There is a correctness bug riding along: an includeArchived: true user with no archived projects gets an empty page after a full scan.

Root cause

example/convex/functions/projects.ts:163-179 builds .select().orderBy({ createdAt: 'desc' }).filter(...).paginate(...) with no index anchor and no maxScan, post-filtering owner-or-member in JS.

orderBy({ createdAt }) normalizes to _creationTime, which cannot be indexed, so no index attaches. The .filter() sets pipeline stages, so the strict guard on the non-pipeline branch never fires.

The file was not touched by any of the ten PRs merged since the audit.

The blocker survives, but the fix changed

Union sources are still same-table only. FindManyUnionSource (packages/kitcn/src/orm/types.ts:70-84) exposes only index and where — no table selector — and _buildUnionSourceStream hard-codes .query(this.tableConfig.name as any) (query.ts:3275). #394 added per-source index but stayed same-table; #425 reworked probe lowering without touching this.

However, the smallest real fix is a schema change, not an ORM API extension. That reframes this issue: it is no longer gated on ORM work.

Suggested fix

Preferred — denormalized access table. Add projectAccessTable with userId, projectId, projectCreatedAt, archived, indexed:

index('userId_archived_projectCreatedAt').on(t.userId, t.archived, t.projectCreatedAt)

maintained by schema triggers on projects and projectMembers. One index walk then serves both owned and member projects, in order, with no post-filter — reads become O(page), independent of table size, and the read set narrows to rows the user can actually see.

The alternative (extending FindManyUnionSource with a table selector so mergedStream can interleave projects and projectMembers) is a larger ORM change for the same outcome.

Related, already landed

projects.hasAny / hasAnyProject rescued the app-shell nav's existence check and is pinned by convex/orm/example-project-existence-reads.test.ts. The projects page list still runs the scan — that test deliberately covers hasAny, not list.

Existing tests

No test pins projects.list read bounds.

Environment

• kitcn 0.32.1
• convex 1.44.0
• verified against origin/main at 14bab503