Authenticated data requests issue ~20 DB queries in ~17 sequential legs — per-request auth/session/localization/metadata resolution has no cross-request caching, costing ~1.5s/request on remote Postgres

[os-zhuang]

Found while root-causing objectstack-ai/cloud#1518 (control plane pays ~1.5s of server time on EVERY authenticated DB-touching request on prod, ~6s on staging, independent of query shape and response size). Traced at cloud's pin 0c24898c. The mechanism is entirely in framework packages, so per cloud's contract-first rule it is filed here rather than worked around in the consumer.

The measurement

Staging (OS_SERVER_TIMING=true) for GET /api/v1/data/sys_user_preference?$top=1:

wall=6674ms server=[parse=0.05ms, db=6240.57ms, serialize=0.06ms, handler=5812.91ms, total=5813.63ms]

handler ≈ total and db ≈ handler — the request is essentially all DB time. db > total is explained by the db mark being a per-query SUM (PerfTiming.count, packages/drivers/driver-sql/src/sql-driver.ts installQueryTiming) over queries that partly overlap (see the parallel settings reads below).

Where the queries come from — one authenticated GET /data/:object?$top=1 issues ~20 queries in ~17 sequential legs

Every leg is a Neon round trip. At the ~50–90ms/query this deployment measures, ~17 sequential legs are the ~1.5s.

A. better-auth getSession — no caching anywhere (5 queries):

1. sys_session + 2. sys_user (better-auth core; cookieCache is not configured — zero grep hits in plugin-auth);
2. sys_user_permission_set, 4. sys_permission_set (FULL list, limit 50), 5. sys_member — the customSession plugin re-derives platform-admin/org roles on EVERY getSession call (packages/plugins/plugin-auth/src/auth-manager.ts ~2898–2960).

B. resolveAuthzContext / resolveUserAuthzGrants (8 queries, sequential) (packages/core/src/security/resolve-authz-context.ts ~330–560):
6. sys_member {user_id}; 7. sys_user_position; 8. sys_member {organization_id} (fellow-org, limit 1000); 9. sys_user_permission_set (duplicate of A3); 10. sys_position {name $in}; 11. sys_position_permission_set; 12. sys_permission_set {id $in}; 13. sys_user {id} for ai_seat (duplicate of A2).

C. Localization (3 queries, parallel): resolveLocalizationContext → 3× settings.get('localization', …) → loadRows is uncached by design (#10221 caches only FAILED reads), so 3 sys_setting reads per request.

packages/rest/src/rest-server.ts ~800 documents this whole block as "~16 sequential queries" and memoizes it per request (execCtxMemo) — but nothing caches it ACROSS requests, so every request pays it once.

D. Route handler (5–6 more):

• enforceApiAccess → loadObjectItems → getMetaItems('object') ALWAYS queries sys_metadata (1–2: the empty result triggers the alt-type retry) — packages/metadata-protocol/src/protocol.ts ~5700;
• plugin-security's engine middleware runs resolvePermissionSetsForContext → dbLoader (sys_permission_set {name $in}) with no cache, and it runs TWICE — once for the find, once for the COUNT (security-plugin.ts ~1351);
• the data SELECT itself;
• findData runs engine.count() whenever a limit is present (protocol.ts ~8858) — a second data query even for $top=1.

E. Zero-hint observation: the SqlDriver's tenancy auto-scoping itself issues no extra queries — but its INPUTS (accessible_org_ids, org_user_ids) are what B pays for, including the limit-1000 fellow-org read.

Consequences on a real deployment (cloud prod/staging)

• ~1.5s fixed server time per authenticated data request at ZERO concurrency (prod; ~6s staging where per-query RTT is ~300ms).
• Requests being ~100% DB-bound makes the pg pool cap the request-concurrency cap: cloud measured a hard knee exactly past 10 concurrent (= its pool max), median doubling at 20. The Console home fires 27 API calls in one cold load.

Fix directions (each independently valuable, roughly by leverage)

1. Session caching: enable better-auth cookieCache (signed cookie, short TTL) or an in-process TTL cache keyed by session token — removes A entirely on warm requests.
2. customSession is redundant work on the data path: B re-derives everything A3–A5 derive, more completely. Consider gating the customSession enrichment to the endpoints that need it, or a short per-user TTL cache.
3. Cross-request TTL cache for resolveUserAuthzGrants keyed (userId, tenantId) with write-invalidation or a short TTL (grants change rarely; the framework already accepts 30s staleness for hostname routing).
4. Batch B's reads: steps 6–13 are ~8 sequential round trips that could be 2–3 batched/joined queries; the duplicates (A2/13, A3/9) are free wins.
5. Cache successful localization reads with write-invalidation (the 新环境日志被 sys_setting 'no such table' ERROR 刷屏：本地化读取先于建表，真错误被噪音淹没 #10221 cache deliberately holds failures only; the dogfood analytics-timezone test pins read-after-write — invalidation-on-write satisfies it).
6. Cache getMetaItems' sys_metadata read (short TTL / registry-epoch key), and skip the alt-type retry when the first read returned an empty-but-healthy result set.
7. Reuse the find's permission-set resolution for its count (same request, same context — the middleware resolves twice today).
8. $count=false fast path: honor the existing $count parameter to skip the COUNT for callers that don't need total (objectui's cold-load probes mostly don't).

Per-query evidence is directly obtainable on any deployment of current main by an admin: X-OS-Debug-Timing: json returns Server-Timing (db;dur=…;desc="N queries") plus X-OS-Debug-Timing-Detail (slowest parametrized statements) — rest-server.ts:1937 opens the disclosure gate for admin principals.

Generated by Claude Code

[os-zhuang]

Triage: routed to the decision inbox (needs-user-decision, type Task). This is a multi-week performance program, not one fix: 8 independent directions, and the highest-leverage ones (session cookie cache, grant-resolution TTL cache) trade permission-change latency against request latency — a staleness window on authz grants is a security-posture call, which sits on the manual floor. Landing spans plugin-auth, core, rest, metadata-protocol and driver-sql, so the post-decision split will be contract-first sub-issues per lane. Chinese four-facet analysis below.

os-decision-facets(四棱卡面块;标记按字面文本写入)

一句话问题:客户每点一次列表,平台先花 1.5 秒(staging 6 秒)把「你是谁、你能看什么、你说什么语言」从头到尾问一遍数据库 —— 二十来次往返,问完才开始干正事;并发一超过连接池就排队,控制台首屏一次要问 27 次。

选项与真实代价:

• A. 全量缓存计划(会话、权限、本地化、元数据四层都上短 TTL 缓存 + 批量合并重复读):业务上 = 请求从 1.5s 降到百毫秒级,像成熟 SaaS 的手感。真实代价:管理员改了权限后,最长一个 TTL 窗口内旧权限仍在生效 —— 撤某人权限后的几十秒他还能看到数据;每层缓存都是一个失效逻辑的永久维护义务。
• B. 只做无语义变化的批量与去重(合并 B 段 8 次串行为 2–3 次批查、复用 find 的权限解析给 count、跳过多余的 alt-type 重试、$count=false 快路径):业务上 = 大约砍一半时延,零语义风险 —— 任何时刻权限都是现读的。真实代价:到不了百毫秒级;剩下一半时延仍在。
• C. B 先行,A 分层逐个裁(先合并去重落地,再按「会话缓存 → 本地化缓存 → 权限缓存」逐层单独裁,每层带自己的失效实测):业务上 = 先拿下无风险的一半,带安全语义的每一层单独见你一次。真实代价:多轮裁决与多张卡的协调成本。
• D. 不动:业务上 = cloud 生产每请求继续付 1.5s,Console 冷启动继续 27 连击,客户感知「平台慢」。

四棱:

• ① 项目长远合理性:请求路径的固定开销是平台横向底盘,拖得越久,上面长出的功能越多、返工面越大;C 的分层裁决给每层缓存留下明确的失效契约,优于一次性大改。
• ② 实际业务拉动:cloud#1518 生产实测拉动,不是投机优化;objectui#5544(冷启动重复请求)是同一根因的前端面。
• ③ 防 AI 犯错:缓存失效是 AI 最容易写错且错了静默的地方(旧权限静默放行,没人看见)—— 所以带语义的每一层必须有「改权限后立即生效」的实测用例作响亮门,B 类纯去重则结构上无此风险,可放心机械化。
• ④ 创业阶段不扩散:不加新能力面,只还底盘债;C 把投入切成可叫停的片段,最合创业节奏。

推荐:C(回退:B —— 若近期不想碰任何带语义的层)。回一个字母即可;「C,但权限层缓存不做」= C 去掉第三层。

置信缺口(本分析看不见什么):staging 的 ~300ms/查询 RTT 是否代表目标部署形态(同 region 部署下 B 单独就可能够用);better-auth cookieCache 的签名 cookie 在现有多实例部署下的失效行为未实测。裁后执行:按裁定拆 contract-first 子单入各车道队列(engine/services/cli),每子单带各自门禁与实测判据,本卡转协调父单不派发。

---

Generated by Claude Code

[os-zhuang]

Maintainer decision — this card is released for development, and split in two

Authorization, 2026-08-21, verbatim (quoted, not translated):

「相关的任务你都安排开发。包括 https://github.com/objectstack-ai/objectstack/issues/10757」

That resolves the needs-user-decision state, which was holding on how far to go across the 8 fix directions. Label removed. Routed under the maintainer-direct channel from the cloud-side performance epic (objectstack-ai/cloud#1521), whose PM session is 30b1d4ba-aaec-4c03-a7b4-d0d9c746cb8a.

PM split, and the reasoning

The 8 directions are not one PR, and they do not carry the same risk. They divide cleanly:

Tranche 1 — deterministic de-duplication. No caching, no staleness, no security surface.

• Add Changesets and GitHub Actions automation #4 the duplicate reads: A2/13 (sys_user twice) and A3/9 (sys_user_permission_set twice)
• Remove explicit pnpm version from workflows to fix version conflict #7 reuse the find's permission-set resolution for its COUNT instead of resolving twice
• Convert to monorepo with scoped packages #8 honour $count=false to skip the COUNT for callers that do not need total

These remove queries by not asking the same question twice. Nothing observes stale data afterwards that could not observe it before. Dispatched now.

Tranche 2 — caching. Where most of the value is, and all of the risk.

• Add metamodel interfaces for ObjectQL/ObjectUI contract #1 session caching (cookieCache / TTL cache on session token) — removes leg A, ~5 queries
• ✨ Set up Copilot instructions #2 gate or cache customSession enrichment on the data path
• Implement ObjectStack protocol specification with Zod schemas and TypeScript interfaces #3 cross-request TTL cache for resolveUserAuthzGrants — removes leg B, ~8 queries
• [WIP] Fix error in step four of the action run #5 cache successful localization reads with write-invalidation
• Migrate from npm to pnpm #6 cache getMetaItems' sys_metadata read

Tranche 2 is the larger win by far — roughly 13 of ~20 queries against tranche 1's ~4. It is deliberately not dispatched with tranche 1, for two reasons:

1. File overlap. Both tranches touch resolve-authz-context.ts, and Add metamodel interfaces for ObjectQL/ObjectUI contract #1/✨ Set up Copilot instructions #2 touch auth-manager.ts alongside it. Same-file work must serialise; running them together produces a conflict, not parallelism.
2. Caching session and authorization state is a security change wearing a performance costume. A stale grants cache is a privilege-escalation window: a permission revoked at T is still honoured until the TTL expires. That needs an invalidation design agreed before code, not discovered during review. It gets its own card, with that as the primary requirement rather than an afterthought.

Honest scoping note: tranche 1 alone will not clear cloud#1521's ≤200 ms gate — it removes roughly a quarter of the queries. It is worth landing on its own merits (free wins, no risk), but the gate needs tranche 2, or placement, or both. Nobody should read a green tranche-1 PR as "the performance problem is fixed".

Fresh reference numbers since this card was filed

The cloud side has since measured per-query cost directly, which sharpens every projection here:

	per DB query
prod control plane	~34 ms
staging control plane	~178 ms
ideal intra-region us-east-1	~1–5 ms

Both Neon projects are confirmed in us-east-1, so neither container is co-located with its database — the deployed cost is N × RTT with both factors wrong. This card owns N. Placement is being pursued separately on cloud#1518.

A count of N measured directly (rather than the ~20-traced / ~35-implied range this card cites) is in flight as cloud#1539; when it lands it should be treated as authoritative over both estimates here.

[os-zhuang]

{
 "issue": 10757,
 "status": "done",
 "branch": "claude/issue-10757-dedupe-per-request-queries",
 "pr": "https://github.com/objectstack-ai/objectstack/pull/10824",
 "premise_still_valid": true,
 "duplicates_confirmed_on_main": "Confirmed as SQL, rejected as a tranche-1 fix. The card's whole trace reproduces on main byte-for-byte: I captured every statement of one authenticated GET /data/crm_account?$top=1 with DEBUG=knex:query on a live `pnpm dev:crm --fresh` rig, isolating the request by log byte-offset. 24 queries, matching the card's legs in order, including q3 sys_user (better-auth) / q15 sys_user (resolveUserAuthzGrants step 7) and q4 sys_user_permission_set (customSession) / q11 sys_user_permission_set (resolveUserAuthzGrants section 6). All four are `select *`, so at the SQL level each pair really is one question asked twice. EQUIVALENCE FAILS on the sys_user pair and OWNERSHIP fails on the other. (a) sys_user: better-auth's session payload does NOT carry `ai_access` — verified live against /api/v1/auth/get-session, which returns name/email/role/banned/positions/isPlatformAdmin and no extension columns — because auth-manager.ts:1046 deliberately refuses to declare it as a better-auth additionalField ('better-auth SELECTs explicit columns, so declaring it here would make getSession query a column that may not exist on every env yet -> broken auth'). So the framework's read cannot be seeded from the session without reversing that decision, and removing better-auth's own read is direction 1 (session caching, tranche 2). Note the email half of that read is ALREADY deduped on main via `seedEmail`, and resolve-authz-context.ts already carries an in-function getUserRow memo — main has moved since the card was traced, in the card's favour. (b) sys_user_permission_set: q11 is the authoritative resolution and cannot go; q4 is the customSession enrichment, which the data path never reads back (resolveAuthzContext consumes only user.id/email and session.activeOrganizationId, never positions/isPlatformAdmin) — removing it IS direction 2, in auth-manager.ts, the file the PM split tranche 2 out to serialise on. I did not attempt either: both are authorization-shaped changes wearing a de-duplication costume.",
 "queries_before_after": "24 before, 23 after, 22 after with ?$count=false. Measured, not reasoned: `X-OS-Debug-Timing: json` on an admin principal returns `Server-Timing: db;dur=...;desc=\"N queries\"` (rest-server.ts opens the gate via isPerfDisclosurePrincipal; the counter is driver-sql's installQueryTiming, ALS-attributed per request). Same rig, same seed, same warm-request protocol before and after; cross-checked against the full knex statement log, which shows the second `select * from sys_permission_set where name in (?, ?, ?)` gone at 23 and the `select count(*) from crm_account` gone at 22. Honest ceiling: that is ~1/12 of a request's queries. The remaining ~22 are session, grants, localization and metadata reads that repeat every request — tranche 2 and placement (cloud#1518), neither addressed here.",
 "count_param_existed": "It existed and needed NO contract change. `$count` is declared in packages/spec/src/api/odata.zod.ts (ODataQuerySchema.$count), aliased on the wire by WIRE_DOLLAR_ALIASES ($count -> count), reserved out of the implicit-field-filter bucket by RESERVED_LIST_QUERY_PARAMS, arity-checked (protocol.query-param-arity.test.ts already pins 'neither true nor false'), and boolean-coerced in findData — and then deleted unread by the protocol-key strip. Omitting `total` is equally contract-clean: FindDataResponseSchema already declares `total: z.number().optional()` with the description 'Total number of records matching the filter (if requested)'. packages/spec was not touched.",
 "summary": "Two of tranche 1's three items landed; the third is reported rather than implemented, with the measurement that rules it out. Shipped: (7) the security middleware now memoizes a permission-set resolution per execution-context object and retires it on ANY engine write — the epoch bump sits ahead of the isSystem bypass so seeds/publishes/auto-grants invalidate too — so a find and its COUNT resolve once instead of twice; (8) findData honours an explicit $count=false by skipping engine.count() and omitting `total` (absent and $count=true are unchanged, so no existing caller moves). Not shipped: (4), because each 'duplicate' pair is one better-auth read plus one framework read and the removable half is directions 1 and 2 — tranche 2, in auth-manager.ts. No cache survives a request, a write, or a context; packages/spec untouched; no pin touched.",
 "tests": "All exit codes captured before any pipe. HEAD at the union run: fe87edaac. NOTE ON THE VERIFY LOCK: `bash scripts/pm/os-verify-lock.sh --status` prints 'host: CANNOT OPERATE THIS LOCK ... a stock macOS does not ship flock' — the entry point disqualifies itself on this host, so heavy commands ran directly rather than hand-rolling a lock. (1) `pnpm --filter @objectstack/plugin-security --filter @objectstack/metadata-protocol test` EXIT=0 — 'Test Files 69 passed (69) / Tests 1348 passed (1348)' and 'Test Files 128 passed | 2 skipped (130) / Tests 1747 passed | 10 skipped (1757)'. (2) downstream consumers `--filter @objectstack/objectql --filter @objectstack/rest --filter @objectstack/client --filter @objectstack/runtime test` EXIT=0 — 226 + 133 + 23 + 179 test files. (3) `pnpm --filter @objectstack/plugin-security typecheck` EXIT=0 ('typecheck: Done'); metadata-protocol has NO typecheck script (DEBT-ledger package) so its type coverage comes from the tsup dts build, which DID red on my first attempt ('src/protocol.ts(8977,57): error TS18048: total is possibly undefined') and is green now — `pnpm build` EXIT=0, 'Tasks: 71 successful, 71 total'. (4) `pnpm lint` (repo-wide `eslint . --no-inline-config`, no narrowing) EXIT=0. (5) every gate `node scripts/pm/dispatch-gates.mjs` derived (14 path-matched + 6 convention-triggered) run individually, all EXIT=0: check:changeset-gate-self-tests, check:cross-package-test-inputs, check:durability-log-level, check:filter-alias-parity, check:objectui-changeset, check:slot-lookup, check:test-source-alias, check:type-source-resolution, check:nul-bytes, check-adr-0087-registration.mjs, check-changeset-no-major.mjs, check-cross-package-test-inputs.mjs, check-empty-changeset.mjs, check-plugin-teardown-shape.mjs, check:query-options-erasure, check:type-check-coverage, check:engine-double-contract, check:where-matcher, check:i18n, docs-audit/check-affected-docs.mjs, and check:type-check-debt --re-measure whose verdict line reads 'check-type-check-coverage --re-measure: OK -- 33 ledger entr(ies) re-measured in 134.2s, 1912 raw tsc error(s) total, none above its recorded number'. That ratchet CAUGHT my new test file (+1 raw tsc error in plugin-security's TEST_DEBT: 'Tuple type [object: string] of length 1 has no element at index 1'); fixed in the test, not by raising the ledger. EXISTING COVERAGE OF THE CHANGED AUTHORIZATION PATHS: for the memo, none — resolve-permission-sets-for-context.pin.test.ts pins reachability through the service locator, and read-scope-provenance-mark.test.ts / security-plugin.test.ts drive the middleware but never twice under one context, so nothing observed the duplicate or would have observed a stale reuse. For $count, none — protocol-data.test.ts pins total/hasMore across limit/offset/search and protocol.query-param-arity.test.ts pins $count's arity, but nothing asserted the flag's EFFECT (it had none). Added: packages/plugins/plugin-security/src/permission-set-resolution-memo.test.ts (6 tests: the dedupe plus four invalidation guards — system write, write by another context, a different context object, grants rewritten in place) and packages/metadata-protocol/src/protocol.count-opt-out.test.ts (9 tests: both wire spellings and the boolean form opt out, hasMore from a full/short page, `count` stripped off the engine bag, and absent/$count=true/no-limit unchanged). ABLATIONS — three legs, each via a script carrying `trap restore EXIT INT TERM` (restore = `git checkout HEAD -- <paths>` off the committed fix), each mutation confirmed on disk by grepping BOTH the injected marker and the deleted text, never by an editor exit code. No dist rebuild is involved or claimed: both test files import their subject relatively (./security-plugin.js, ./protocol.js), so vitest resolves source. (A) memo disabled -> 2 red / 4 green: the two dedupe assertions fell ('expected 2 to be 1'), the four invalidation guards held, which is the documented direction — they are guards, not evidence. (B) write-epoch bump removed -> 2 red / 4 green: exactly the two write-invalidation tests fell ('expected 1 to be 2'); the deleted-text count read 1 rather than 0 because `this.writeEpoch++` also occurs at the metadata-watch site, a different statement the mutation left alone. (C) countOptOut forced to false -> 4 red / 5 green: the opt-out block fell, the 'unchanged' block held. `git status --porcelain` clean after each leg. RIG TEARDOWN: dev servers on port 39147 were located by lsof, their cwd verified as this worktree, and killed by PID (never by process name).",
 "open_questions": [
 {
 "question": "Where does the card's direction 4 go, now that both of its 'free win' pairs are measured to be one better-auth read plus one framework read, with the removable half owned by directions 1 and 2?",
 "options": [
 "A — fold direction 4 into tranche 2 and drop it as a separate item: direction 1 removes the sys_user duplicate as a side effect of session caching, and direction 2 removes the sys_user_permission_set duplicate as a side effect of gating customSession. Nothing is left of 4 that 1 and 2 do not already carry.",
 "B — keep direction 4 alive as its own card scoped to BATCHING legs 6-13 (the half of the card's direction 4 the PM did not dispatch): 8 sequential framework round trips that could be 2-3, no caching, no staleness. That is genuinely tranche-1-shaped work and is untouched by tranche 2.",
 "C — reopen the ai_access decision: declare it as a better-auth additionalField so getSession's row serves both readers. Removes one query and reverses a documented refusal."
 ],
 "recommendation": "A for the de-duplication framing, and B as a separate card that is worth dispatching on its own. Real business need: cloud#1521 measures the fixed cost per request, and legs 6-13 are 8 of the ~22 that remain — the largest no-risk block left. Long-term soundness: batching keeps every read live, so it needs no invalidation contract and cannot drift from tranche 2's caching design. Hard to get wrong: a batched read returns the same rows or fails loudly; a cache returns stale grants silently. Startup scope: B adds no capability surface, only removes round trips. C is refused on all four axes — it trades a documented auth-breaking risk for one query, on the exact path where a wrong answer is a 500 on every request."
 },
 {
 "question": "resolveLocalizationContext issues THREE identical `select * from sys_setting where namespace = ?` per request (measured as queries 16-18 of 24). The function already batches its own direct fallback into one $in, but the settings-service path it prefers calls SettingsService.get() three times and each call runs loadRows(namespace, ...) over the whole namespace. This is three reads of ONE question inside one request — tranche-1 shaped, no cache needed — but the card files localization under direction 5 (caching, tranche 2). I did not touch it: the tranche boundary is the PM's, and the dispatch said tranche 1 is exactly three items.",
 "options": [
 "A — dispatch it as tranche 1b: replace the three settings.get() calls with one namespace read the three keys are picked out of. No caching, no staleness, ~2 queries saved.",
 "B — leave it inside direction 5 and let the localization cache subsume it."
 ],
 "recommendation": "A. It is the same class as the two fixes in this PR (one question, asked repeatedly, inside one request) and it is INDEPENDENT of the caching decision — batching the three reads is correct whether or not direction 5 ever ships, and it shrinks what direction 5 has to cache. Filing it as an issue myself would have pre-empted a split the PM owns, which is why it is reported here instead."
 }
 ],
 "out_of_scope_findings": []
}