plugin-pinyin-search: every boot scans all rows of transient sys_inbox_message, making startup O(table size)

Summary

PinyinSearchPlugin runs backfillSearchCompanion() synchronously from kernel:bootstrapped on every boot. The backfill enumerates every object carrying __search, then keyset-walks every row (1,000 rows/page) and only afterwards decides in JavaScript whether a row needs an update.

sys_inbox_message is automatically included because it declares nameField: "title", is DDL-managed, and does not declare searchable: false. It is also a transient, high-write pipeline table. As a result, application startup time becomes linear in retained inbox history even when every row has already been reconciled and no update is necessary.

This was measured on @objectstack/* 17.3.0 with PostgreSQL.

Production evidence

Database:

sys_inbox_message: 6,747,132 rows
total relation size: 3,273 MB (2,965 MB heap + 307 MB indexes)
average title + body payload: only 144 bytes; the size is row count, not large message bodies
pnpm dev startup: approximately 4m50s
config load: approximately 0.5s
metadata validation: approximately 2.2s
the long boot phase is the pinyin companion backfill walking sys_inbox_message

The unusually large row count was produced by the already-fixed notification redelivery defect #23122. That defect is not the subject of this card, but it exposes the scaling behavior clearly: after delivery is fixed, every subsequent restart still walks all historical rows.

Current code path

packages/plugins/plugin-pinyin-search/src/pinyin-search-plugin.ts registers:

ctx.hook('kernel:bootstrapped', async () => {
  await backfillSearchCompanion(engine, ctx.logger as any);
});

backfillSearchCompanion() then:

calls registry.getAllObjects();
selects every schema with __search;
runs keysetWalk() with pageSize: 1000;
fetches id, the display source, and __search for every row;
checks hasBlob / hasCjkSource in JavaScript.

The schema registry provisions __search for any searchable DDL-managed object with an eligible non-PK display field. sys_inbox_message qualifies because its display field is title.

Therefore a clean second boot does the same full read as the first boot. The algorithm is O(total rows across all companion-bearing objects), not O(rows needing backfill).

Why a simple `WHERE __search IS NULL` is incomplete

The current representation uses null both for:

a row never inspected/backfilled; and
a row whose display value contains no CJK and correctly needs no companion.

So pushing down only __search IS NULL would still rescan every legitimately non-CJK row on every boot. A durable migration/reconciliation marker, checkpoint, or distinct processed representation is needed if boot-time reconciliation remains automatic.

Expected behavior

Normal startup should not synchronously reread all historical rows of high-volume/transient objects. Once hooks are bound, ordinary writes already maintain __search; historical reconciliation should be bounded and observable.

Possible directions:

make the historical backfill an explicit migration/operator command instead of an every-boot action;
persist a per-object completion/checkpoint and scan only records beyond it;
allow object-level opt-out from the companion/backfill and mark pipeline objects such as sys_inbox_message non-searchable where appropriate;
run reconciliation after readiness in a bounded background worker rather than blocking boot.

Acceptance ideas

A second boot against an unchanged companion-bearing object does not read every row again.
A large sys_inbox_message history does not delay application readiness linearly with its row count.
Rows inserted through normal ObjectQL writes continue to receive __search through the existing hooks.
An explicit rebuild path remains available for hook-bypassing imports/direct writes.
A regression test records rows/pages read on first backfill versus an unchanged subsequent boot.

service-messaging on Postgres: sys_notification_delivery.claimed_at is a real (binary32) column, so every ack() fails the claim check and the inbox row is re-delivered every 131 s forever #23122 explains the production inbox amplification that made this visible. Its v18 delivery fix stops new duplicates but intentionally does not delete operator data and does not address pinyin boot scanning.

Triage: first grade — `bug` · `priority:p1` · `domain:server` · `area:records` · `pm:queue` · triage seat · `session_01AavokzJ5DndAwitDXvKy4U` · 2026-10-24T11:55Z

⛔ Not a claim, ⛔ not a dispatch.

p1: this is production evidence. Boot takes about 4 min 50 s against 6.7 M sys_inbox_message rows, and boot time grows with every table that carries __search, so every restart pays it.
Lane: domain:server (plugin-pinyin-search, plus sys_inbox_message in platform-objects).
No ruling is needed if the fix uses what exists. The object-level searchable: false key already exists, so marking the transient pipeline object non-searchable adds no key. Moving the backfill out of the blocking kernel:bootstrapped path, or bounding it with a checkpoint, is implementation. If the dev finds the chosen direction needs a new key or marker in the protocol, that half goes back to triage as a spec card first.
Acceptance, per the card: a second boot on unchanged data does not reread every row, and readiness does not grow with sys_inbox_message's size.

Generated by Claude Code

Claim: PM loop round 1
Session: session_01JVTd3PprStGa26aez5omLB
Account: sunhaolin (the seat's linked user as GET /user answers it; the card's assignee)
Branch: claude/issue-25634-pinyin-backfill-boot-bound
Worktree: objectstack-issue-25634
Domain: domain:server
Seat: domain:server#2
File surface: packages/plugins/plugin-pinyin-search/src/** (the kernel:bootstrapped backfill and backfillSearchCompanion), the sys_inbox_message object definition only if measurement shows searchable: false drops nothing a user reads, tests, one changeset. ⛔ No new spec key or marker: a direction that needs one goes back to triage as a spec card first (triage, 6383901837) (stop on breach; explain in the report)
Container & model: L, mode:cloud (maintainer: 「并发加到5，可以用云卡片」; cross-package, design-heavy, p1), model: TIER_DEFAULT (dispatch-gates --tier: "no path-derived mandate … the tier stays the PM's per-card judgment call"), effort: xhigh (p1)
Clause-②: no
Metadata-protocol: none
Responsibility: plugin-pinyin-search's boot backfill keyset-walks every row of every __search-bearing object on every boot and decides in JavaScript | the platform already maintains __search on every ObjectQL write through the plugin's hooks, has an explicit rebuild alias (force: true) and the object-level searchable key | every deployment with a large companion-bearing table; measured in production on 17.3.0 + PostgreSQL (6.7 M sys_inbox_message rows, boot about 4 min 50 s)
Thread-read: 6383901837
Serial constraints cleared: no open PR touches packages/plugins/plugin-pinyin-search/** or an inbox-message object file (every open PR's file list read this fire). Seat 1's #25582 (area:records) is in flight on packages/core/src/utils/import-coerce.ts; the file surfaces are disjoint.
Premise: re-read on origin/main 9dac9eb8d6: pinyin-search-plugin.ts hooks kernel:bootstrapped and awaits backfillSearchCompanion(engine, …); companion-projection.ts walks with { pageSize: batchSize }. searchable is z.boolean().default(true) on the object schema (object.zod.ts), and sys_inbox_message is a Console navigation object (account.app.ts), so marking it non-searchable is measured, not assumed.

Generated by Claude Code

os-dev-report

{
  "issue": 25634,
  "status": "done",
  "branch": "claude/issue-25634-pinyin-backfill-boot-bound",
  "pr": "https://github.com/objectstack-ai/objectstack/pull/25690",
  "session": "session_014yYprgEHbgKXeYT8h3XRJB",
  "premise_still_valid": true,
  "summary": "Measured on main 9dac9eb8d6: ObjectKernel.bootstrap() awaits kernel:bootstrapped before kernel:listening (the HTTP listen), so the awaited full-table walk held readiness. The plugin now starts the companion pass at kernel:bootstrapped without awaiting it (failures logged at warn; destroy() aborts between pages and awaits). It records each completed walk per object (source fields + since watermark) in one sys_migration receipt row, id 'pinyin-search-companion', spelled in this package, written against spec DataMigrationFlagSchema like the seed-tenancy receipt. A later boot walks only updated_at >= since AND __search IS NULL, so an unchanged second boot reads 0 rows. rebuildSearchCompanion (force) still walks every row. No new spec key or marker. sys_inbox_message searchable is untouched, so no served-set narrowing. Kernel hook dispatch is unchanged.",
  "tests": "head ca3ca05b0: pnpm --filter @objectstack/plugin-pinyin-search test => Test Files 4 passed (4), Tests 39 passed (39); typecheck (tsc --noEmit + check:test-typecheck OK, 0 errors). New src/boot-reconcile.integration.test.ts (real ObjectKernel + ObjectQL + SqlDriver on a SQLite FILE + real SysMigration): first boot 2500 rows / 3 queries vs unchanged second boot 0 rows / 1 query; bypassing row added after the walk => second boot reads 1 row and fills it; control: an ObjectQL insert stamps __search while the pass is parked; control: rebuild(force) walks 2501/2501; bootstrap() resolves while page 1 is held; shutdown mid-walk stops after page 2 of 3 and writes no receipt; no ledger => full walk each boot; a throwing pass logs warn. Ablation A (scripts/ablation-replace.mjs, anchor 1->0, blob 08208bf4cabb->d1469dcf9eac): readReceipt dropped => 2 red ('expected { queries: 3, rows: 2500 } to deeply equal { queries: 1, rows: 0 }', 'expected 2501 to be 1'); restored blob == HEAD 08208bf4cabb, git diff HEAD empty. Ablation B: await restored in kernel:bootstrapped => readiness pin red (timed out); first attempt refused by the tool as a no-op (replacement contained its anchor, anchor 1->1), re-run with a disjoint replacement (anchor 1->0, blob 28739311a522->d35dfcb8ad7e); restored == HEAD 28739311a522. Both ablations import ./*.js relatively (src, no dist), so no build leg. Scale (100k rows, SQLite file, one run each, shared box, same scratch harness, deleted): main first boot ready 11946 ms, unchanged second boot ready 1033 ms; branch first boot ready 293 ms (walk done 14352 ms, after readiness), second boot ready 676 ms (pass done 696 ms, 0 rows).",
  "gates": "Local at head ca3ca05b0: node scripts/pm/dispatch-gates.mjs --commands --repo objectstack-ai/objectstack derived 111 commands; all 111 run, every exit code captured before any pipe, all 111 exit 0; --ran verdict: '111 derived famil(ies) accounted for — 111 run, 0 NOT-MEASURED (a DERIVED zero — all 111 recorded an exit code and none of them is 3)', ran_exit=0. Red at earlier heads and fixed in this PR: check:tenant-audit-census (238->239 write call sites; --write regen + 8 hand-written prose figures), check:engine-double-contract (unused fake update dropped), check:test-source-alias (spec/system alias added), check:query-options-erasure (3 test as-any casts removed). Interim exit-3/1 readings of check:dual-build-cjs-loads, check:skill-examples, check:dts-closure at head c789ee89b were local build-state (dist being written mid-run); all exit 0 at the final head. pnpm lint scoped: node node_modules/eslint/bin/eslint.js packages/plugins/plugin-pinyin-search --no-inline-config exit 0. CI families (Lint & Repo Gates, TypeScript Type Check, Test Core, Build Core, Governed Surface Queue Guard): in_progress at report time, read from PR #25690 check-runs, not re-run locally.",
  "dogfood_battery": "n/a — no ! in the PR title and no (narrowing) arm",
  "line_budget": "903 insertions / 99 deletions over 11 files vs 9dac9eb8d6 (1002 changed lines; under the 3000 human-merge threshold)",
  "files_changed": [
    ".changeset/25634-pinyin-reconcile-off-readiness.md",
    "content/docs/permissions/tenant-audit-census.mdx",
    "docs/audits/2026-08-tenant-audit-write-call-sites.counts.md",
    "packages/plugins/plugin-pinyin-search/package.json",
    "packages/plugins/plugin-pinyin-search/src/boot-reconcile.integration.test.ts",
    "packages/plugins/plugin-pinyin-search/src/boot-reconcile.ts",
    "packages/plugins/plugin-pinyin-search/src/companion-projection.test.ts",
    "packages/plugins/plugin-pinyin-search/src/companion-projection.ts",
    "packages/plugins/plugin-pinyin-search/src/pinyin-search-plugin.ts",
    "packages/plugins/plugin-pinyin-search/vitest.config.ts",
    "pnpm-lock.yaml"
  ],
  "deviations": [
    "File surface: content/docs/permissions/tenant-audit-census.mdx and docs/audits/2026-08-tenant-audit-write-call-sites.counts.md are outside the claim's surface; check:tenant-audit-census required them because the receipt upsert is one new engine write call site (generated regen + hand-written prose counts only).",
    "package.json: @objectstack/spec added as a runtime dependency (ledger row contract) and @objectstack/driver-sql + @objectstack/platform-objects as devDependencies (real-driver test); pnpm-lock.yaml importer entries follow.",
    "Published surface additions (additive): CompanionBackfillResult.pages, CompanionBackfillOptions.signal, PinyinSearchPlugin.whenReconciled().",
    "git fetch origin 621a487607881c66b2899b7e3477115229a156b4 (a read) so check-plugin-teardown-shape --self-test could read its pinned fixture on this shallow clone.",
    "Ablation B's first attempt was a refused no-op (replacement contained the anchor); re-run, see tests.",
    "Scale note is one run per leg on a shared box, SQLite, not PostgreSQL."
  ],
  "mcp_calls": "2 — mcp__github__issue_read (get #25634), mcp__github__issue_read (get_comments #25634); no write tool",
  "api_writes": "2 — relay strokes via POST /repos/objectstack-ai/objectstack/dispatches: (1) pr_create => POST /repos/objectstack-ai/objectstack/pulls (draft) + POST /repos/objectstack-ai/objectstack/issues/25690/assignees; (2) this os-dev-report comment via scripts/pm/post-stamped.mjs => POST /repos/objectstack-ai/objectstack/issues/25634/comments. git push is not a REST write.",
  "open_questions": [],
  "out_of_scope_findings": [
    "carrier: none · noted, not filed — pre-existing: the companion fill writes through engine.update, so the non-bypassable audit stamp moves a filled record's updated_at/updated_by to the system at fill time (old boot backfill and rebuildSearchCompanion did the same); recorded in PR #25690 Acceptance notes."
  ]
}

Generated by Claude Code

Landed: PR #25690 → `f9a1e93f4` · `domain:server` seat 2 · `sunhaolin` · `session_01JVTd3PprStGa26aez5omLB` · 2026-10-25T04:49Z

reading	result
PR #25690 object	`merged: true`, merge commit `f9a1e93f437ac1d9c20042912acfa39bb434d938`, merged 2026-10-25T04:49:14Z through the merge queue
`origin/main`	`f9a1e93f4` is `origin/main`'s tip, 1 parent(s)
queue branch	no `gh-readonly-queue/main/*` head for PR 25690 remains

State: closed completed by Fixes #25634 on merge. pm:dispatched stripped in this act.
Delivered: the boot companion pass no longer holds readiness. It starts at kernel:bootstrapped and runs after the server is listening. An unchanged restart reads only the rows written since the last completed walk that still have no companion, through one completion receipt in the existing sys_migration ledger. rebuildSearchCompanion() (force: true) still walks every row. Declared Clause-②: yes (widening), patch: whenReconciled(), CompanionBackfillOptions.signal and CompanionBackfillResult.pages. The TIER_DEFAULT review was this seat's (record 6393427115, PASS).
Carried, not filed: CompanionBackfillResult.aborted? also ships, without a changeset line (record 6393427115, ③).
Dev: cloud session session_014yYprgEHbgKXeYT8h3XRJB, archived in this act.

Generated by Claude Code

Navigation Menu

Repository navigation

plugin-pinyin-search: every boot scans all rows of transient `sys_inbox_message`, making startup O(table size) #25634

Summary

Production evidence

Current code path

Why a simple `WHERE __search IS NULL` is incomplete

Expected behavior

Acceptance ideas

Related

Triage: first grade — `bug` · `priority:p1` · `domain:server` · `area:records` · `pm:queue` · triage seat · `session_01AavokzJ5DndAwitDXvKy4U` · 2026-10-24T11:55Z

Landed: PR #25690 → `f9a1e93f4` · `domain:server` seat 2 · `sunhaolin` · `session_01JVTd3PprStGa26aez5omLB` · 2026-10-25T04:49Z

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

plugin-pinyin-search: every boot scans all rows of transient sys_inbox_message, making startup O(table size) #25634

Description

Summary

Production evidence

Current code path

Why a simple WHERE __search IS NULL is incomplete

Expected behavior

Acceptance ideas

Related

Activity

objectstack-fleet commented on Oct 24, 2026

Triage: first grade — bug · priority:p1 · domain:server · area:records · pm:queue · triage seat · session_01AavokzJ5DndAwitDXvKy4U · 2026-10-24T11:55Z

objectstack-fleet commented on Oct 24, 2026

objectstack-fleet commented on Oct 24, 2026

objectstack-fleet commented on Oct 25, 2026

Landed: PR #25690 → f9a1e93f4 · domain:server seat 2 · sunhaolin · session_01JVTd3PprStGa26aez5omLB · 2026-10-25T04:49Z

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

plugin-pinyin-search: every boot scans all rows of transient `sys_inbox_message`, making startup O(table size) #25634

Why a simple `WHERE __search IS NULL` is incomplete

Triage: first grade — `bug` · `priority:p1` · `domain:server` · `area:records` · `pm:queue` · triage seat · `session_01AavokzJ5DndAwitDXvKy4U` · 2026-10-24T11:55Z

Landed: PR #25690 → `f9a1e93f4` · `domain:server` seat 2 · `sunhaolin` · `session_01JVTd3PprStGa26aez5omLB` · 2026-10-25T04:49Z