TASK-0135 — Performance Report (Phase 15)

Scope: profile + fix the top-5 hot paths (Redis caching, EXPLAIN-proven indexes, Prisma query shape, connection pooling + read-replica routing), and define the k6 load-test matrix with dev-report-only / staging-hard enforcement. Method: EXPLAIN (ANALYZE, BUFFERS) on the representative queries (dev PG16, the 100k-record seed DB + 10k-block seed page), query-count tests (vitest, counting the Prisma client calls), and k6 (the p95 numbers are staging-gated — the dev run is report-only, see §7). Date: 2026-10-04. Companion to docs/development/deployment.md (sizing / pooling) and the tests/performance suites.

Conflict note (AGENTS rule 29). The brief references "the NFR doc's §62 metric list". PRODUCT-SPEC has 28 sections and the NFR doc (docs/product/non-functional-requirements.md) has no "§62" — the metric set is defined by the NFR doc + the Observability requirement in phase-15-production.md, not by a §62. The observability metric list is implemented from those two sources. Also: the profiled N+1 in the "block batch save" path is the realtime flush (POST /internal/blocks/flush), not the REST batch() endpoint — the flush is the writer that actually issues one round-trip per block, so it is the path that is fixed here.

1. Top-5 hot paths — before / after

The five paths per the spec (tree load, block batch save, database query, search, record open). Each row: the fix, and the measured evidence.

#	Path	Fix	Before	After	Evidence
1	Page tree load (depth=all, the "<2 s / 100k blocks" path)	version-key Redis cache tree:{ws}:{page}:{version} + blocks (pageId, position) index + replica read	Bitmap Heap Scan + top-N heapsort, 12.7 ms (10k-block page)	Index Scan, 0.67 ms; cache hit = 0 PG	§2.1, §3
2	Block batch save (the realtime flush)	single raw INSERT … ON CONFLICT DO UPDATE multi-row upsert (N+1 → 1)	N block round-trips (10/100/1000 → 10/100/1000 queries)	constant count (≤5) for 10/100/1000; exactly 1 upsert	§4.1
3	Database query (record list / table view)	database_records (databaseId, position) index + 60 s column-metadata cache + replica read	parallel Bitmap Heap Scan + Gather Merge + Sort, 170 ms (100k DB)	Index Scan, 0.16 ms; metadata cached	§2.2, §3
4	Search (OpenSearch round-trip + shaping)	5 s TTL cache keyed by {ws, query-hash, filters, principalId} (permission-keyed)	every request = full OpenSearch round-trip + permission re-filter	5 s hit rate absorbs re-queries; bust on permission change	§5
5	Record open (record → page → blocks → props)	one narrow findUnique up front + shared metadata cache + ≤4 queries	profiled baseline 7 PG queries (double row fetch + full props re-fetch)	≤4 PG (perm-cache hit), 1 row fetch	§4.2

2.1 — Tree load EXPLAIN (the 10k-block seed page)

The query (depth=all, the loader in block.service.ts):

SELECT * FROM blocks WHERE "pageId" = $1 AND "deletedAt" IS NULL
ORDER BY "position" ASC LIMIT 1000

Before (no (pageId, position) index — the existing (pageId, parentId, position) cannot serve a parentId-less range in position order, and (workspaceId, pageId) has no position key):

Limit (actual time=12.572..12.777 rows=1000)
  ->  Sort  (Sort Key: "position"  Sort Method: top-N heapsort  Memory: 306kB)
        ->  Bitmap Heap Scan on blocks
              Recheck Cond: ("pageId" = 'pg_t3…0002')
              Filter: ("deletedAt" IS NULL)
Execution Time: 12.777 ms   (10k blocks on this page)

After (blocks_pageId_position_idx added — migration 20261004004500_perf_indexes_hot_paths_2):

Limit (actual time=0.039..0.567 rows=1000)
  ->  Index Scan using "blocks_pageId_position_idx" on blocks
        Index Cond: ("pageId" = 'pg_t3…0002')
        Filter: ("deletedAt" IS NULL)   (low-selectivity recheck)
Execution Time: 0.666 ms

The Sort node is eliminated (the index delivers position order directly) and the 10k-row bitmap scan is gone — ~19× faster on the seed page. The version-key cache makes the steady-state hit a 0-PG read; the index is what keeps the cache miss (a first load, or post-write bust) cheap.

2.2 — 100k-record database table list EXPLAIN

The query (query.service.ts list() / query(), take: 5000, position order):

SELECT * FROM database_records WHERE "databaseId" = $1
AND "archivedAt" IS NULL ORDER BY "position" ASC LIMIT 50

Before (the existing (databaseId, archivedAt) index prunes archived rows but offers no position ordering, so the planner still sorts):

Limit (actual time=169.955..175.920 rows=50)
  ->  Gather Merge (Workers Launched: 2)
        ->  Sort (top-N heapsort)
              ->  (parallel bitmap scan over 83k rows)
Execution Time: 175.920 ms   (100k-record seed DB)

After (database_records_databaseId_position_idx added):

Limit (actual time=0.072..0.112 rows=50)
  ->  Index Scan using "database_records_databaseId_position_idx" on database_records
        Index Cond: ("databaseId" = 'db_t3qi…001')
        Filter: ("archivedAt" IS NULL)   (low-selectivity recheck)
Execution Time: 0.156 ms

~1100× faster on the seed DB; the Sort + 2-worker Gather Merge are gone. This is the index the database-load k6 suite (table-first / table-scroll) depends on for the p95 < 500 ms target.

2.3 — Audit + file listing (round 1)

Already added in migration 20261003210000_perf_indexes_hot_paths:

• audit_logs (workspaceId, createdAt DESC) — the workspace-wide audit listing. Before: Index Scan (action_createdAt) + Sort (quicksort 40kB); After: backward Index Scan (workspaceId, createdAt DESC), Sort node gone (0.975 → 0.135 ms on the sampled workspace; the win is the eliminated sort, which grows with row count). The existing (workspaceId, action, createdAt) cannot serve a workspace-prefixed range without an action predicate in createdAt order.
• files (workspaceId, createdAt DESC) — file listing. At the 21-row dev scale the planner still prefers a Seq Scan + Sort (cheaper at that size); the index is present and becomes the plan as the table grows. Documented, not a measured win at dev scale.

3. Indexes added (all EXPLAIN-proven — no speculative indexes)

Index	Table	Query served	Before plan	After plan
audit_logs_workspaceId_createdAt_idx	audit_logs	workspace audit listing	Index(action) + Sort	backward Index, no Sort
files_workspaceId_createdAt_idx	files	file listing	Seq + Sort (21 rows)	index present; planner seq at dev scale
blocks_pageId_position_idx	blocks	tree depth=all load	Bitmap + heapsort, 12.7 ms	Index Scan, 0.67 ms, no Sort
database_records_databaseId_position_idx	database_records	100k table list	Bitmap + Gather Merge + Sort, 170 ms	Index Scan, 0.16 ms, no Sort

Migrations: 20261003210000_perf_indexes_hot_paths (audit + file) and 20261004004500_perf_indexes_hot_paths_2 (blocks + database_records). Both applied; schema @@index entries added.

4. Query-shape fixes

4.1 — Block batch save (realtime flush): N+1 → constant

The profiled N+1 was in the realtime flush (POST /internal/blocks/flush, internal.service.ts internalFlush): it issued one create/upsert per block. Fixed to a single raw multi-row INSERT … ON CONFLICT ("id") DO UPDATE (the 9 writable columns, updatedAt = now()), so the query count is flat across batch size.

Query-shape test: apps/api/src/modules/internal/__tests__/flush-query-shape.test.ts (counting mock, 10 / 100 / 1000 blocks):

• the total Prisma call count is identical for 10, 100, and 1000 blocks (c10 === c100 === c1000), and ≤ 5;
• at 1000 blocks there is exactly 1 $executeRaw (the multi-row upsert);
• the soft-delete branch (blocks removed) adds exactly 1 updateMany, ≤ 6.

4.2 — Record open: 7 → ≤ 4 queries

The profiled record-open (query.service.ts getRecord) double-fetched the row (a pre-check findUnique + a post-permission include fetch) and re-fetched the full property set. Rewritten:

1. ONE narrow databaseRecord.findUnique up front (exact RowT select + the page title/icon + property values — side-effect-free, so the 403 from the permission check is still the only early-exit);
2. getDbChecked(EDIT) → the permission decision (cached 60 s + bust);
3. the column metadata via the shared 60 s cache (identical key + shape to listProperties — the shared key can never be corrupted by a divergent shape);
4. relations → DTO.

Query-shape test: apps/api/src/modules/databases/__tests__/record-open-query-shape.test.ts:

• exact call order [databaseRecord.findUnique, databaseProperty.findMany, relationLink.findMany], total ≤ 4 (50-block record page), exactly 1 findUnique;
• with links: ≤ 5 (adds the relation/database/record findManys, one each);
• control (permission cache off): 6 — i.e. the steady-state bound is 4 because the permission decision is a cache hit; the uncached baseline drops 7 → 6.

5. Redis caching semantics + PERF_CACHE contract

apps/api/src/common/cache.ts — four caches, all structured (zod-validated on read: a corrupt entry is a cache miss, never a 500):

Cache	Key	Invalidation	TTL (safety net)
Page tree	tree:{ws}:{page}:{version}	version-key — reads key by the workspace content version; every page/block write INCRs the version, so a read can never observe a stale entry (the version in the key is the freshness proof). No stale reads across concurrent writes.	none (version is the token)
Permission decision	perm:{ws}:{user}:{resource-ancestors}	event bust on PERMISSION_CHANGED (granter/grantee + resource-ancestor chain)	60 s — "no event → stale ≤ 60 s"
Search results	search:{ws}:{query-hash}:{filters-hash}:{principalId}	permission-keyed (only filtered results cached; the principal is part of the key) + bust on permission change	5 s
DB column metadata	meta:{dbId}:properties	bust on database.updated (property/view change)	60 s

PERF_CACHE gate (config/env.ts, z.enum(["auto","on","off"]), default auto): auto → the caches are off unless NODE_ENV=production (so dev and the unit/integration suites are unaffected unless a spec opts in). on forces them on (the cache-invalidation integration suite sets it at module top). off forces them off (the replica-routing suite sets it so the tree read is always a live reader read, not a cache hit). The gate is the contract that keeps the caches out of the way of correctness tests while letting the production path use them.

6. Connection pooling + read-replica routing

Pooling — PgBouncer (transaction mode). The k8s manifests (infrastructure/kubernetes/) point the API/worker DATABASE_URL at a transaction-mode PgBouncer pool per app. Per-app sizing is DATABASE_POOL_SIZE (a PgBouncer pool_size share, default 20, max 200) — see deployment.md for the per-app pool table and the "connection counts stay under the documented pool limit" staging check.

Conflict note (rule 29). The brief says "pool sizing: DATABASE_TIMEOUT, connectionLimit documented per app". Prisma 5.22 has no client-side pool option — PrismaClientOptions is {datasources, datasourceUrl, errorFormat, log, transactionOptions} only (connectionLimit/poolTimeout are Prisma 6). Connection pooling is therefore owned by PgBouncer, not the Prisma client; DATABASE_POOL_SIZE is the per-app PgBouncer share, and there is no Prisma connectionLimit to set in 5.22. Recorded rather than guessed.

Read-replica routing (packages/database-client — the spec's prisma/clients.ts, held in a source-first package because the API tsconfig has rootDir: "src" and cannot import a repo-root file). createDatabaseClients returns {primary, reader, hasReplica, read(), write(), disconnect()}: reader is a second PrismaClient on DATABASE_REPLICA_URL when set, else primary; write() is always primary. PrismaService is primary, owns reader, and exposes .read() / .write().

Wired replica reads (spec-named "search-indexing, tree loads, listing"): the block tree loader (block.service.ts depth=all, inside the version-key cache), the record list() and table query() (query.service.ts). Deliberately NOT routed (stay on the primary): the realtime flush's If-Match / baseVersion CAS + the internal read (returns the version the CAS bumps), permission decisions, and anything in a transaction.

Worker (search-indexing) — recorded, not yet wired. The spec names "search-indexing" as a replica read, and that work runs in the worker (apps/worker/src/processors/search-index). In this pass the worker keeps its single primary PrismaClient: its source scan (collectWorkspaceDocs) is shared by the idempotent full reindex (a replica lag would only delay a re-index — safe) and the reconciliation sweep's direction-A delete decision (a lagging replica could delete a doc whose source row is not yet visible — not safe), so routing only part of it to the replica is a correctness trap, not a clean win. The db.read() routing helper + the live routing test are proven on the API; adopting the replica in the worker's reindex-only path is a one-line follow-up (pass a reader client into the search-index ops) to land once the OTel/traceparent worker wiring is in — recorded per AGENTS rule 29 rather than done speculatively into a shared file being edited concurrently.

Staleness contract. The replica lags. Replica reads are acceptable for tree/listing/search (a tiny lag window is the documented trade); they are never used for permission decisions or optimistic-concurrency version checks — a write-then-read-your-write on the same request always hits primary.

Live test (tests/integration/replica-routing.integration.test.ts, green against Testcontainers PG with a real second, schema-migrated-but-EMPTY database as the replica — zero copy = zero lag, so any mis-route is observable):

1. the block tree read (db.read()) is served from the replica → stale (empty) while the primary has the blocks;
2. the flush's baseVersion CAS hits the primary (a replica read would 404 the absent page) — it succeeds and bumps the primary version;
3. the replica stays diverged from the primary (the flush write never reached it).

7. k6 load-test matrix

Four suites in tests/performance/ (k6 + a local Playwright/Chromium editor driver), seeded by seed-100k.mjs (one 100k-record DB, one 10k-block page, 200 pages — idempotent by a marker table). Thresholds are staging-hard, dev-report-only:

Suite	Scenario shape	Hard threshold (staging)
api-load.js	tree-load / record-list / search / 5% record-create, ramp 0→1k rps	p95 < 300 ms (overall + per-scenario); http_req_failed < 0.1%
realtime-load.js	10k ws conns / 100 rooms / 200 msg-s + presence churn, 15 min	update (ack) p95 < 200 ms; connect p95 < 1 s; zero lost Yjs ops
database-load.js	table-first/scroll on the 100k DB + record-open + 100 rps creates	table + record-open p95 < 500 ms; create rate ≥ 100 rps, p95 < 500 ms
editor-local.mjs	headless Chromium: type / create-block / move-block (the real DnD path) / frame sampling	interaction p95 < 50 ms; block move < 16 ms; no frame > 100 ms during a move

Enforcement: the CI matrix runs these report-only in dev/main (results posted, thresholds as soft warnings) and hard in staging (a breach blocks the release pipeline — the §26 enforcement). A fixture run with a lowered threshold demonstrates the hard gate failing.

Staging-gated. The p95 numbers are measured against a real staging cluster under real load (1k rps / 10k conns / 100k rows); the dev run of the same suites is report-only and is not cited as a pass here. The dev/smoke tests/performance/reports/*.json are smoke artifacts, not the staging result.

8. Gaps / staging-only items (AGENTS rule 29)

• k6 p95s — measured only against staging; the dev run is report-only. The 10k-conn / 1k-rps / 100k-row shapes need the staging cluster (and the seed-100k precondition) to be meaningful.
• PgBouncer connection counts under load — the "stay under the documented pool limit" check is a staging measurement (PgBouncer stats during api-load), not a dev assertion.
• files index — present but the planner prefers Seq Scan at the 21-row dev scale; the plan flips to the index as the table grows (not measurable here).
• Prisma 5.22 client pooling — see §6; no client-side connectionLimit, pooling is PgBouncer-owned.
• §62 metric list — see header; the metric set is defined by the NFR doc + the observability requirement, not a "§62".
• Worker search-indexing replica read — the db.read() routing + live test are proven on the API; the worker keeps its single primary client in this pass (the source scan feeds a delete decision on the reconcile path, so a blind replica route is a correctness trap — see §6). Follow-up, recorded not done.
• Pre-existing integration-suite drift (recorded per AGENTS rule 29 — NOT introduced by TASK-0135). A full integration run on this tree shows 43 failing tests across 10 files. To separate regression from pre-existing drift, the exact same 10 files were run against a clean HEAD (1de5dfdc, zero TASK-0135 changes) baseline (a git archive checkout with a shared store): the failing test names are identical sets (43 = 43, empty diff in both directions). Root cause: the suites lag the API surface. The comments suite calls POST /api/v1/pages/:pageId/comments (the controller is now top-level POST /comments — 404); the blocks suite calls POST /api/v1/pages/:pageId/blocks:batch (the route is POST /blocks/batch — 404); the databases suite hits POST /api/v1/workspaces/:id/databases and the permissions suite POST /api/v1/workspaces/:id/permissions (no such routes exist — the permission model was reworked and the module removed); openapi.contract fails because docs/api/openapi.yaml was last regenerated 2026-10-02 19:31, before TASK-0133's org-admin routes (/orgs/{orgId}/admin/*) and the 2FA routes were added, so "LIVE ⊆ SPEC" legitimately lists the new routes as missing. These are cross-cutting rewrites of prior phases (Phase 8 comments, Phase 5 blocks, the permission rework, TASK-0133's spec regen) — fixing them "blind" in a release-phase pass risks masking real bugs (the automations 202 vs 200, api-keys 403 vs 201, and workspace-member-list failures are business-logic drift, not obvious route drift), so they are recorded here for a dedicated test-suite-repair task rather than silently rewritten. TASK-0135's own integration suites (the new replica-routing.integration.test.ts 3/3, the cache/flush/record-open query-shape suites) are green.
• Pre-existing BUILD-gate breaks (recorded per AGENTS rule 29; two fixed, one class recorded). The CI/release contract includes a build stage (tsc -p … --emit for api/worker/realtime, next build for web) that is stricter than the typecheck the dev loop uses. Running it surfaced:
  1. FIXED apps/api/src/modules/docs/docs.controller.ts (TASK-0127): const require = createRequire(process.cwd()) at module scope is an illegal identifier under module: "commonjs" (TS2441 — the compiler reserves require in CJS output). It failed the API build on a clean HEAD; renamed to req (1 declaration + 1 use). API build now green.
  2. FIXED apps/web/src/app/(app)/w/[workspace]/page/[id]/layout.tsx: Next.js rejects non-reserved named exports from a layout (PageSpinner is not a valid Layout export field); the component is used only locally, so it is now a local function.
  3. RECORDED (not fixed — out of scope, too broad to do blind in a release-hardening phase): apps/web has a pre-existing type-error baseline of ~122 errors across 27 files (e.g. property-editor.tsx cfg used before declaration, filter-builder.tsx op possibly undefined, use-database.ts unknown generics, the share page's "public"-as-PermLevel — the page's own comment already flags it as a pre-existing type gap). The dev server runs fine; only strict next build / tsc --noEmit catch them. next build for web will therefore remain RED until a dedicated web type-clean task lands. tsc for api/worker/realtime is 0 and their builds are green.
• FIXED (verified live) — realtime doc-room join 500. apps/realtime/src/ prisma-store.ts (TASK-0096-era) passed the Prisma enum role ("MEMBER") straight into the wire-case-keyed permissions engine via membership.role as WorkspaceRole; WorkspaceRole is wire-case ("member") so the engine hit levelRank(undefined) → Error: unknown permission level: undefined → 500 on every ?room= doc-room join (seen live in the dev log). This blocked the k6 realtime hard tier (zero-op-loss verify) — a TASK-0135 acceptance item. Fix: roleFromPrisma(membership.role) (1 line, @factory/permissions). Verified: realtime tsc=0, 131/131 unit, and a live WS join now returns 101 + a hello frame (was a 500). Pre-existing, not introduced by TASK-0135, but fixed here because it gates the realtime load gate.
• RECORDED (cross-task dev-only, not fixed) — committed CSP unsafe-eval. packages/shared/src/security-headers.ts sets script-src 'self' 'unsafe-inline' with no 'unsafe-eval', and the file's own header states "No unsafe-eval: nothing in the app uses eval." Next.js 14 dev uses eval-source-map, so a client that evaluates inline scripts can hit an EvalError in dev only (prod builds use hashed sources). The k6 agent's editor-local harness reported "dev web unbootable" from this, but (a) that harness bypasses CSP internally and its run PASSED, and (b) the standard Playwright e2e flow (no bypassCSP) boots the workspace and renders the sidebar, so the defect is not in the normal path. It is a pre-existing, already-documented, out-of-scope dev-tooling observation — proper fix is a dev-mode devtool/unsafe-eval carve-out in a dedicated task; recorded, not changed here.