driver-turso: remote mode never materializes object-level `indexes` — every declared secondary index is absent on production Turso tenant databases, so hot polling queries full-scan

[hotlong]

现象（生产实测；租户信息已匿名）

两个生产租户库（driver-turso remote 模式，libsql://）的 sqlite_master：

	表	索引总数	其中主键自动索引	其余
库 A	100	106	100	6 = 4 个字段级 unique 生成的 uniq_* + 2 个 metadata-protocol 原生 SQL 建的 idx_sys_metadata_overlay_*
库 B	100	105	100	5 个 uniq_*

• sys_notification_delivery 声明了 5 个索引（含 1 个对象级 unique）→ 两个库里一个都没有，只有 sqlite_autoindex_sys_notification_delivery_1
• sys_job_queue 声明了 3 个 → 两个库里一个都没有
• 同一批对象定义在本地 SqlDriver（better-sqlite3）落地后：sys_notification_delivery 6 个、sys_job_queue 4 个，齐全

同一条领取查询的执行计划：

EXPLAIN QUERY PLAN SELECT id FROM sys_notification_delivery
 WHERE status = 'pending' AND partition_key = ? AND next_attempt_at <= ?
 ORDER BY next_attempt_at LIMIT 50

远程租户库 → SCAN sys_notification_delivery
            USE TEMP B-TREE FOR ORDER BY
本地       → SEARCH sys_notification_delivery USING INDEX idx_sys_notification_delivery_b3081700
            (status=? AND partition_key=? AND next_attempt_at<?)

原因（读码；origin/main 3ef96b471 仍如此，无在途 PR）

remote 模式下 TursoDriver.initObjects 走 RemoteTransport.syncSchemasBatch。remote-transport.ts 里所有索引 DDL 都来自 buildUniqueIndexDDL（:2160，由 uniqueIndexesFromFields 生成——只看字段级 unique），发射点 :1841 / :1868 / :1941 / :1981，全文件唯一一条 DDL 模板是 :2196 的 CREATE UNIQUE INDEX IF NOT EXISTS。

对象级 indexes: [...]——无论是否 unique——在 remote 路径上没有任何消费者。本地的对应物是 SqlDriver.syncDeclaredIndexes（sql-driver.ts:9812）。

#16570 修的是 indexes 进 initObjects 参数的类型面；turso-driver.ts 那段注释自己也写了「the runtime carried both keys the whole time」——但带着键不等于把它渲染成 DDL。这是 ADR-0049「声明无执行点」在 DDL 层的一例：从外面看什么都没坏，代价只出现在账单上。

后果（生产实测）

一个租户库 9 天 1,011.6 亿次行读，占其所在 Turso 组织的 99.9%，已进入超额计费；该库数据总量 12.7 MB。

机制是三件事叠乘（另两件分卡）：

1. 本卡：热表没有索引，每条查找都是全表扫描
2. 通知调度器空闲时每跳仍发 32 条语句（service-messaging 另卡）
3. 投递表持续增长——未配置邮件传输的租户，每条通知写一条必死的 email 投递，保留 90 天（service-messaging 另卡）

实测稳态约每秒 28 次全表扫描 sys_notification_delivery；表每天 +316 行，日读量每天线性 +8 亿。

验收

1. remote 模式为每个对象级声明索引发射 DDL——非 unique 与 unique、含 nullSafe / 租户键部分——与 SqlDriver.syncDeclaredIndexes 同名同键（buildIndexName）。新表随建表批次一起落；已有表走 retrofit（IF NOT EXISTS，零改数据）
2. 失败不静默：建不出的索引按 AGENTS.md 降级规则以 error 级报出原因（与 drivers(turso): the remote face drops unique entirely — a declared-unique column takes duplicates, and an upsert on conflictKeys dies with a raw SqliteError #8413 的 unique retrofit 同等待遇）
3. 对等测试：同一组对象定义分别经 local SqlDriver 与 remote transport 落地，比较 sqlite_master 的索引名集合全等；至少覆盖 sys_notification_delivery、sys_job_queue
4. 开机成本受控：稳态（索引都已存在）不得每次 kernel 构建为每张表各发一轮语句——沿用 unique retrofit 的批量 / 一次性做法
5. 回归证据：remote 面上对上面那条领取查询 EXPLAIN QUERY PLAN 为 SEARCH … USING INDEX

影响面

所有 remote 模式租户库（托管云的每个环境）。修复发布并被消费方 pin 后，已有库靠 retrofit 在下一次 kernel 构建时补齐。

[hotlong]

关联卡（同一次生产读放大的三个乘数与一份燃料）：

• service-messaging: NotificationDispatcher issues 32 statements per tick on an EMPTY outbox (reap runs per partition, twice) — idle cost scales with partitions × table size and never backs off #17610 —— 通知调度器空闲时每跳仍发 32 条语句（本卡缺索引时每条都是全表扫）
• service-messaging: fan-out writes an email delivery for a tenant with no email transport — it dead-letters on its first attempt and is kept 90d, so the hot outbox grows ~1 dead row per notification #17611 —— 未配置邮件传输的租户，每条通知写一条必死的 email 投递、保留 90 天（热表持续增长）
• demo_bootstrap runs every 10 minutes forever in production tenants (1,776 runs in 13 days, longest 26 min) — a bootstrap should run once hotcrm#1892 —— demo_bootstrap 以常驻 10 分钟 cron 运行（燃料之一）

[hotlong]

Claim: maintainer direct dispatch (cloud ops session)
Session: c5c0ce54-bb9c-478c-9e5b-cf44b80d4569
Time: 2026-09-11T03:34Z
Branch: claude/issue-17609-remote-declared-indexes
Worktree: ~/Documents/GitHub/objectstack-issue-17609
Domain: domain:engine
File surface: packages/drivers/driver-turso/src/remote-transport.ts (+ its tests); parity reference packages/drivers/driver-sql/src/sql-driver.ts read-only
Clause-②: no — driver DDL materialization of already-declared indexes; no packages/spec/src/** surface expected (path limb re-checked on the actual diff at review)

[os-litant]

分诊:半状态治愈 —— 补 pm:queue;type 设为 Bug。⛔ priority:p0 与 domain:engine 是维护者所写,本席不动。

分诊席 · session_017VGfRocA8VjczSe84fgjY3 · R+178 · 2026-09-11T03:3xZ · 本评论来自分诊座位. 读数取 origin/main @ 3ef96b47120a989d9333aeeeb6181bc371bc9b52.

为什么这是治愈而不是首次定级

卡上已有 domain:engine + priority:p0,但没有 pm 状态标签 ⇒ 落在 ③ 析取里,对每一个队列视图都是不可见的。⇒ 「状态变更不过夜」,补上这一半。

⭐ priority:p0 是维护者(hotlong)亲手写的,⛔ 分诊不复核、不下调。 本评论只做两件事:让它进队列,以及把派发时会撞上的东西先测掉。

✅ 车道正确:packages/drivers/driver-turso 在 domain:engine 行(packages/drivers/driver-*)。

⭐ 最要紧的一条:本卡不被 sql-driver.ts 的串行链挡住

验收第 1 条要求与 SqlDriver.syncDeclaredIndexes「同名同键(buildIndexName)」,而 sql-driver.ts 上现在有一条硬串行链(#17175 pm:dispatched 占着文件,#17586 在等)。⇒ 「p0 可超 batch,⛔ 不越过同文件串行与深度等待」—— 所以「要不要改 sql-driver.ts」直接决定这张 p0 能不能今天派。实测:

读数	值
buildIndexName 定义处	packages/drivers/driver-sql/src/schema-drift.ts:1377 —— ⭐ 独立模块,不在 sql-driver.ts 里
是否公开导出	✅ packages/drivers/driver-sql/src/index.ts:87(与 expectedIndexes :1839、diffManagedIndexes、isManagedIndexName 同一个导出块)
sql-driver.ts 自己怎么拿到它	:83 import,与外部消费者同一条路
driver-turso 是否已依赖 driver-sql	✅ packages/drivers/driver-turso/package.json:34 —— "@objectstack/driver-sql": "workspace:*",已在

⇒ ⭐ remote 侧可以直接 import buildIndexName / expectedIndexes,零行 sql-driver.ts 改动。 ⇒ 不进串行链,可立即派。

⚠️ 而且这也定下了修法:⛔ 不要在 remote-transport.ts 里重写一份命名规则。仓里已经有过一次教训 —— packages/cli/src/commands/generate.ts:2007 自己复制了一份 buildIndexName,而 driver-memory 的注释写着「Reproducing half of buildIndexName would be a second [implementation]」。第三份副本就是「同名同键」这条验收自己要防的东西。

派发姿态(p0)

• 「priority:p0 插队:可超 batch、破轮次立即派发」⇒ ⛔ 不等本轮其余卡。
• ⛔ 但 p0 不豁免认领协议:先写标签、再 Claim:、再全线程重读,一样不省。
• ⛔ 也不豁免深度等待:派发前仍读 scripts/pm/os-verify-lock.sh --status。

⛔ 围栏

• ⛔ 不要动 SqlDriver.syncDeclaredIndexes。它是参照物,本地面已经是对的(卡上实测:本地 sys_notification_delivery 6 个、sys_job_queue 4 个,齐全)。本卡是让 remote 面追上它。
• ⚠️ 验收第 4 条是真的约束,⛔ 不是客套:「稳态(索引都已存在)不得每次 kernel 构建为每张表各发一轮语句」。⭐ 本卡的成因正是空转成本(9 天 1,011.6 亿次行读,12.7 MB 的库),⇒ 一个每次开机为 100 张表各发一轮 DDL 的修法,是用一种空转换掉另一种。沿用 unique retrofit 的批量/一次性做法。
• ⚠️ 失败必须响(验收第 2 条):建不出的索引按 AGENTS.md 降级规则以 error 级报出,与 drivers(turso): the remote face drops unique entirely — a declared-unique column takes duplicates, and an upsert on conflictKeys dies with a raw SqliteError #8413 的 unique retrofit 同等待遇。⛔ 静默跳过正是本卡的病根 —— ADR-0049「声明无执行点」。
• ⛔ 不要改索引声明词表。finding(metadata): 索引声明词表无法表达 partial/NULL-safe 形(knex table.unique() 上限)——overlay 索引的双形分歧由 carve-out 顶着,词表加宽零实测拉动,存档待第二个消费者 #13562(status:parked)记的是 partial / NULL-safe 形表达不出来,已归档待第二个消费者 ⇒ ⛔ 不在本卡范围。
• ⚠️ 验收第 3 条的对等测试(local 与 remote 落地后 sqlite_master 索引名集合全等)是这张卡真正的交付物 —— ⛔ 光发 DDL 不带这个测试,同样的分歧下次还会长出来。

⚠️ 本卡是三件事叠乘中的一件,另两件是别的卡

卡上写明:热表无索引(本卡)× 空闲每跳 32 条语句(#17610,domain:services,p1,本轮同批治愈)× 投递表持续增长(第三张)。

⇒ ⛔ 不要在本卡里修另外两件。⚠️ 但派发令应点名:本卡单独落地会让每秒 ~28 次全表扫描变成索引查找,⭐ 账单的量级下降主要来自这一张 —— 另两张降的是语句条数与行数增长,本张降的是每条语句的代价。

查重 —— 2026-09-11T03:3xZ 跑于 581 张 open 板

pattern	open hits
syncDeclaredIndexes	2 — #17609, #13562
remote-transport|RemoteTransport	3 — #17609, #6009, #5930
buildUniqueIndexDDL|uniqueIndexesFromFields	1 — #17609
driver-turso	9 — #17609, #16973, #16023, #15642, #15212, #15104, #8249, #6009, #5499
index(控制)	94

⛔ 无重复。 #13562 是词表卡(status:parked),#6009 / #5930 是 pm:on-hold 的观察项,#5499 是 driver-memory/mongodb 的冻结裁决(⛔ 不覆盖 driver-turso)。

Size/model suggestion: M —— DDL 发射面不大,但验收 3(对等测试)与验收 4(稳态成本)是这张卡的主体。

_{分诊席位 · session_017VGfRocA8VjczSe84fgjY3 · R+178 · 2026-09-11T03:3xZ · 本评论来自分诊座位}

---

Generated by Claude Code

[os-litant]

⛔ 更正:上一条评论里的 pm:queue 是一次写入竞态,已撤销 —— 本卡状态是 pm:dispatched,归 domain:engine 席

分诊席 · session_017VGfRocA8VjczSe84fgjY3 · R+178 · 2026-09-11T03:3xZ · 本评论来自分诊座位

发生了什么

本席在 03:33Z 取板时,本卡是 domain:engine + priority:p0 + 无 pm 状态(③ 半状态)。在我起草上一条评论的这几分钟里,domain:engine 席认领了它并挂上 pm:dispatched。我的标签写入按「读现集 → 加增删 → 写并集」执行,于是把 pm:queue 并到了一个已经有 pm:dispatched 的卡上 ⇒ 两个六态标签同时在卡上,约 40 秒。

回读 diff 当场看见,已在下一笔撤掉 pm:queue。现值:pm:dispatched,⛔ 没有第二个状态标签。

⇒ 对认领席的意思,说清楚以免误读

⭐ pm:dispatched 是对的,pm:queue 是我的竞态产物,⛔ 不是要你把卡放回队列的指示。 上一条评论里的技术内容(串行链、围栏、查重)全部照常有效 —— ⛔ 只有那一行「补 pm:queue」作废。

⚠️ 其中最要紧的一条不受影响,再说一次:buildIndexName 在 packages/drivers/driver-sql/src/schema-drift.ts:1377,已公开导出,driver-turso 已依赖 driver-sql ⇒ 本卡零行 sql-driver.ts 改动,⛔ 不被 #17175 的同文件串行挡住。

已机械化,⛔ 不靠下次记得

「六态 ONE-OF 的成因恒是半写的转换」。本例的成因更窄一点:并发写入下,并集语义会把一个刚出现的状态和我要加的状态一起留下。⇒ 写入层已改成:传入六态之一时,其余五态无条件进 remove 集,并在位移并发状态时打印一行;一次传两个六态直接抛错(双向已验证)。

⇒ 这一类 collision 现在写不出来了,⛔ 与我是否记得无关。

_{分诊席位 · session_017VGfRocA8VjczSe84fgjY3 · R+178 · 2026-09-11T03:3xZ · 本评论来自分诊座位}

---

Generated by Claude Code

[hotlong]

os-dev-report
{
"issue": 17609,
"status": "done",
"branch": "claude/issue-17609-remote-declared-indexes",
"pr": "#17615",
"premise_still_valid": true,
"summary": "RemoteTransport now materializes every declared index — field-level unique plus the object's own indexes, unique and non-unique, including the NULL-safe key of unique: 'organization' — by composing the same shared normalizers SqlDriver.syncDeclaredIndexes composes (uniqueIndexesFromFields + normalizeDeclaredIndex, names via buildIndexName); driver-sql is untouched. New tables get their indexes in the CREATE TABLE batch; existing tables are retrofitted with IF NOT EXISTS after ONE index-name read folded into the existing PRAGMA batch, with a batch then per-statement fallback that reports each unbuildable index at error and never rewrites a row. Premise verified at 3ef96b4 — every emit site and the sole template confirmed, one line drift only (syncDeclaredIndexes is at sql-driver.ts:12464, not :9812) — and reproduced locally before the fix (new parity suite 7 failed / 7 on base). Clause-2 stays no: packages/spec/src/** is untouched and no export, schema key or accept/refuse behaviour changes.",
"tests": "Base + test only (bd207df): parity suite 7 failed / 7. HEAD 6241e6e: pnpm --filter @objectstack/driver-turso test exit 0 (52 files, 1243 tests); typecheck exit 0 (tsc --listFiles includes the new suite); build exit 0 (check-dts-emitted 1/1). Narrowed eslint over the 4 touched .ts files: exit 0, JSON 4 files / 0 errors / 0 warnings, no ignore notice; eslint.config.mjs enables no type-aware linting, so no untouched file can move. Gate sweep on 6241e6e — the 62 commands dispatch-gates derives, the 4 roster gates it flags under this PR's directories, check:durability-log-level and the package trio, 70 in all: 68 exit 0; 2 NOT MEASURED, exit 3 PREREQUISITE NOT MET (check:dual-build-cjs-loads needs all 78 package dists; check:type-check-debt --re-measure needs 24 dependency closures — both need the full-repo build the dispatch ruled out). check:lean-entry-closure was exit 3 first, then exit 0 after a filtered objectql build (15/15 cached). dispatch-gates --ran over an exit-coded record: 62 derived, 60 run, 2 NOT-MEASURED derived from exit 3, 0 unrun. Ablations at 6241e6e, direction predicted before each run, marker counted on disk, restore proven by blob 90cf0ed7 == HEAD and an empty git diff HEAD: A (existing-name skip removed) 1 failed | 6 passed, as predicted; B (fallback's error report silenced) 2 failed | 5 passed, as predicted; C (index name spelled raw instead of escaped) 4 failed | 3 passed, as predicted. The subject resolves from src (the suite imports ./turso-driver.js), so no dist preflight applies. Every os-verify-lock call ran in declared UNLOCKED mode (macOS host, no flock).",
"acceptance": {
"1_every_declared_index_same_name_and_key": "Pins a fresh database carries the same index set — names, uniqueness, origin and key parts, the single-object syncSchema path lands the same set as the batch path and retrofits every declared index onto tables that already exist — and changes no row: PRAGMA index_list + index_xinfo + COALESCE text deep-equal between local TursoDriver(':memory:') (the SqlDriver path) and remote TursoDriver over the real @libsql/client on file::memory:, anchored to buildIndexName-computed sets. sys_notification_delivery 6 names (incl. idx_sys_notification_delivery_b3081700 and uniq_sys_notification_delivery_f5f71821), sys_job_queue 4, os17609_scoped covering a field-level NULL-safe unique, unique:'organization', unique:'global', two author-named indexes and a skipped formula index. Retrofit leaves rows byte-identical and total_changes() unchanged; every retrofit statement is IF NOT EXISTS.",
"2_failures_not_silent": "an object-level UNIQUE over existing duplicates: the dedup index is not created, the 4 plain indexes still land (a libsql write batch is transactional; the per-statement fallback lands them), exactly one logger.error names index and table with UNIQUE constraint failed, nothing on warn, both duplicate rows remain. a PLAIN index the server refuses: injected SQLITE_FULL gives one logger.error naming the index with its cause, and the other 4 land. Ablation B turns both red.",
"3_parity_test": "New packages/drivers/driver-turso/src/turso-local-remote-declared-index-parity.test.ts, 7 pins, both hot-table shapes; the remote face is the real @libsql/client on file::memory:, as turso-driver.test.ts's remote-mode suite already does.",
"4_steady_state_cost": "A second initObjects sends 0 index DDL. syncSchemasBatch alone: 2 round trips, 2N+1 statements (N=3 gives 7), independent of index count (14 declared); the single index-name read rides the existing PRAGMA batch. Base sent 2N+U statements over 3 round trips every boot (U = field-level uniques re-sent as IF NOT EXISTS). Ablation A turns the pin red.",
"5_explain_query_plan": "Remote face, one database: before retrofit the claim query plans as SCAN sys_notification_delivery plus USE TEMP B-TREE FOR ORDER BY; after retrofit it plans as SEARCH sys_notification_delivery USING INDEX idx_sys_notification_delivery_b3081700 over status, partition_key and a next_attempt_at range — identical to the local face. Pinned by serves the delivery claim query from the declared index — the same plan on both faces; verbatim plan output in the PR body."
},
"spec_touched": false,
"clause_2": "no",
"labels": "PR carries documentation, size/l, tests, tooling from the path labeler; no label added by hand (Clause-2 no, so no needs:contract-review)",
"mcp_calls": "0 — every GitHub read and write went through the gh CLI",
"open_questions": [],
"review_flags": [
"Acceptance 4 shape: rather than keep the unique retrofit's every-boot IF NOT EXISTS batch, the retrofit now diffs against existing index names, so steady state issues zero index DDL and costs less than base; the old per-boot re-send of field-level uniques is gone too.",
"Beyond the card: an author-declared index NAME is escaped (aliasIdentifierSql) instead of refused by SAFE_IDENTIFIER. Refusing would fail the remote face's whole schema sync — every object in the boot — over a spec-legal name the local face quotes. This moves the #14287 header's 'index names still refuse' for that one position; key columns stay refused. Corpus at 3ef96b4: 0 non-identifier declared index names across 85 declaring files. Pinned by fixture index os17609 scoped-by \"slug\" and ablation C.",
"'local SqlDriver' and 'remote face' are exercised as TursoDriver local mode (inherits SqlDriver.initObjects) and TursoDriver remote mode over the real @libsql/client on file::memory: — no sqld and no Turso endpoint were used."
],
"out_of_scope_findings": [
"noted, not filed: the two hot-table fixtures copy the shapes of sys_notification_delivery and sys_job_queue (driver-turso does not depend on service-messaging or platform-objects), so a later change to those objects' indexes is invisible to this suite — it pins driver parity, not those declarations. Successor: none.",
"noted, not filed: the better-sqlite3 libsql stub (libsql-sqlite-stub.testkit.ts) runs batch() as sequential non-transactional statements, while the real @libsql/client rolls a write batch back on any failure; remote suites that test failure paths on the stub do not see that rollback. Successor: none."
]
}

---

Generated by Claude Code

[hotlong]

状态（UTC 2026-09-11）：开发完成 → PR #17615（draft）。PM 复核已通过（见 PR 评论：spec 未触及、retrofit 仅 DDL、索引名转义已审过接受）。等 CI 全部结束且无失败后转 ready 入队。

[hotlong]

状态（UTC 2026-09-11 04:34）：PR #17615 CI 全绿（29 pass · 5 skipping · 0 fail）→ 已转 ready → 已进入合并队列（timeline added_to_merge_queue 04:34:31，队列第 2 位）。合并后本卡随 Closes 关闭；消费侧（托管云 pin bump + 全库索引普查）另有卡跟进。