Repository navigation
Design sketch: OpenCode local history as a CostUsageStore source adapter (#3273) #4752
Description
Design sketch: OpenCode local history as a CostUsageStore source adapter
Following the close notes on #4574, #4702, and #4744: before another implementation, here is the small sketch you offered to confirm. The feature request stays #3273 (opt-in, default-off, machine-local OpenCode history for OpenAI-provider assistant turns, merged into the ambient Codex ledger).
The proposal
1. Register the OpenCode database as a scanner source, not a new store.
The OpenCode SQLite database registers as one files row. Byte cursors don't exist for a database, but rowids do — scan_state carries SQLite cursors instead: per-table [low, high] rowid windows for message / part / session_message, plus raw table row counts. inode/mtime/size fingerprinting reuses the existing file identity machinery unchanged; the WAL sidecar folds into the fingerprint the way the OpenCode Go reader already does.
2. Incremental scans feed the existing write path.
A scan pass decodes only rows outside the window — new rows above high (the common append case), newest-first continuation below low for a first pass on a large database, and a part rowid cursor that re-evaluates just the message IDs of late-arriving step-finish parts. Rows are append-only event data upstream; a raw count that shrank triggers a coverage reset for this source. Everything writes through the existing write pipeline (usage_rows, file_day_aggregates, day_aggregates) with timestamp/day from OpenCode's time.created, so day keys, merges, and merges-into-reports stay native. Scans stay cancellable and budget-bounded the way CodexScanBudget already defines.
3. Publication and completion tracking change nothing.
Read views, windowed queries, retention, scan_complete, coverage ranges, and the catch-up status API all apply to OpenCode rows exactly as they do to Codex history — which is the point: the existing history performance gates cover this source by construction, and there is no second schema, connection, or persistence layer.
4. Provenance.
Recorded cost surfaces through authoritative_cost_nanos as source-recorded dollars (OpenCode records $0 for subscription usage). The visible "OpenCode local" label and the sourceRecorded/mixed provenance on reports need one small schema decision: a source/provenance column on the aggregate tables (my preference — explicit, queryable) versus a model-name marker. Which would you rather take?
5. The #3273 contract stays as already implemented and reviewed.
Opt-in default-off setting; SQLITE_OPEN_READONLY; exact providerID == "openai" assistant rows only, counting valid step-finish parts (one per part, like the Go reader) with message-level fallback; recorded cost as-is; ambient Codex scope only — never scoped CODEX_HOME homes, managed accounts, or the Spend Dashboard; no account attribution; OPENCODE_DB/XDG_DATA_HOME follow upstream's single-database selection.
What this drops from my three closed PRs
The separate ~1,300-line store, its own schema/connections/persistence, the cache-only publication types, and the self-managed completion state — all replaced by the store's own.
Open questions
- Provenance column on aggregates vs. model-name marker — your preference?
- Should the adapter hang off a generic "source driver" seam in the scanner, or a dedicated OpenCode branch mirroring how Claude session files plug in?
- Any size/shape ceiling for the SQLite-window scan code itself (e.g. must it live within the vendored scanner's budget abstractions, or a sibling module the scanner drives)?
If this shape is right, I'll implement on it. Happy to adjust any part of the contract first — better here than in a fourth PR.
Activity
clawsweeper commentedon Oct 23, 2026
Codex review: this still needs some work.
Summary
Keep this issue open: the owner approved the adapter design and is completing the linked draft PR, while current main still excludes ordinary OpenCode/OpenAI session history.
Reproducibility: This is an approved feature request; current documentation, reader filters, and scan-state models confirm the missing capability without a live reproduction.
Next step
Review the owner's completion of #4757 against the agreed correctness, scope, compatibility, and bounded-work regressions before resolving this issue.
Review details
Best possible solution:
Complete the approved shared-store adapter with bounded ingestion, preserved recorded costs, compatible checkpoints, and enforced ambient-only reporting.
Do we have a high-confidence way to reproduce the issue?
This is an approved feature request; current documentation, reader filters, and scan-state models confirm the missing capability without a live reproduction.
Is this the best way to solve the issue?
The owner-approved dedicated adapter reuses existing persistence and read-view owners, but its correctness and performance still require the regressions identified in the draft discussion.
AGENTS.md: found and applied where relevant.
Remaining risk / open question:
Codex review notes: model internal, reasoning medium; reviewed against aa029218d82e.
Label changes
Label changes:
No label changes.
Label justifications:
P3: This adds an optional history source without establishing a regression or blocking an existing supported workflow.Evidence reviewed
What I checked:
docs/opencode.md:63, aa029218d82e)Sources/CodexBarCore/Vendored/CostUsage/CostUsageStoreModels.swift:18, aa029218d82e)Sources/CodexBarCore/Vendored/CostUsage/CostUsageStoreModels.swift, aa029218d82e)How this review workflow works
@clawsweeper re-reviewcomments, exact-item GitHub events, scheduled/background review runs, or manual workflow dispatch.@clawsweeper re-reviewor@clawsweeper re-runon an open PR or issue to request a fresh review only.@clawsweeper reviewto request a fresh review only.@clawsweeper explainto ask for more context, or@clawsweeper stopto stop active automation.Reviewed October 26, 2026, 7:39 AM ET / 11:39 UTC.
steipete commentedon Oct 24, 2026
Thanks @Danielweng0902, this is exactly the sketch I was hoping for, and an adapter feeding the existing history store is the right direction. Keep the default-off, ambient-Codex-only scope, exact OpenAI assistant filtering, recorded costs, and account/dashboard exclusions. I would use a dedicated OpenCode adapter driven by the scanner, without introducing a general source-driver framework. Claude is not an existing adapter into this SQLite store: its transcript cache remains separate. The sketch needs these concrete adjustments before implementation:
One file record can hold the checkpoint without a SQL schema migration.
files.scan_stateis a JSON-encodedCostUsageStoreScanState, with optionalresumePayloadanddetailsPayload. Put versioned, typed SQLite progress there: table windows, an in-progress part cursor, pending/reconciliation progress, source identity and the DB/WAL fingerprint. Add a backward-compatible source discriminator to the JSON state, defaulting existing records to native Codex. Do not put rowids inparsed_bytesorscan_target_size, or feed SQLite payloads to the JSONL decoder. The current restore path explicitly decodesStoredFileDetailsandCostUsageJsonl.ResumeState, so it needs source-aware dispatch. Preserve native rows/checkpoints through the store's compatible parser-hash/version path and prove the upgrade; a SQL table rebuild is unnecessary. Row identity also needs an explicit answer:usage_rowsis keyed by(file_id, row_index), and rowids from message, part and session_message can collide. Define stable, idempotent identities for replacements without a whole-history ID map.Use neither aggregate provenance columns nor model-name markers initially.
file_day_aggregates.file_idalready joins to the registered source'sfilesrow; request rows retain that same file identity. Resolve the source from the registration/typed checkpoint, not a.dbfilename heuristic. Derive the label and window/day/model provenance with a bounded source-specific aggregate query in the same read snapshot. Use aggregate/record presence, including recorded zero, rather thancost > 0. Globalday_aggregatesmerges by(day, model)and has no file identity, so its current query alone cannot supply provenance; extend the read view to carry the bounded source summary. This avoids an aggregate-table migration/backfill and preserves real model IDs for grouping and pricing. Adding a single source string to a mixed global row would not represent both contributors anyway.sourceRecordedis also a proposed report-model case, not an existing persisted enum value on main.Recorded $0 needs an explicit regression.
authoritativeCostsandaggregateRowscurrently omitauthoritativeCostNanos == 0. The adapter must preserve “recorded zero” through cold/cached aggregate-only publication, using source-qualified aggregate presence, so an OpenCode subscription row is neither missing nor repriced at public API rates. Test OpenCode-only zero, mixed native/OpenCode, and windows containing only one source.The source is not append-only. Current upstream creates assistant
session_messagerows at step start, then UPDATEs those same rows with final cost/tokens at step end; it also deletes rows. A WAL fingerprint change tells you something changed, but unchanged rowids/counts do not identify those updates. A shrinking count misses deletion plus insertion too. Specify bounded pending-row rechecks and mutation/deletion reconciliation (using a verified indexed upstream change mechanism where available, otherwise resumable reconciliation), plus replacement/rowid-reuse invalidation. Do not clear progress on every WAL write and restart forever. Read boundaries and data in a short read-only source transaction; then commit rows, aggregate deltas and checkpoint atomically through the existing store, revalidating the durable baseline under its writer lock. A short-lived connection to the external OpenCode database is necessary; there should be no second writable history cache.Joining the store does not automatically satisfy its performance contract. Avoid full-table counts on each refresh, JSON filtering or sorting an entire table before LIMIT, all-parts joins before admission, whole-database row arrays, and rebuilding every historical day for one append. Bound raw rows, projected payload bytes and SQLite execution time/cancellation;
CodexScanBudgetbounds admitted bytes/time but cannot interrupt an expensive SQL statement by itself. Persist progress within a message with many parts. The current generic file persistence replaces that file's entire day map, and retention evicts whole files; one database registered as one file must not turn those operations into whole-history work or prevent old rows from being reclaimed. Keep cached publication windowed and source-I/O-free. Extend the existing read-work/write-amplification/coverage tests with adapter fixtures, including fixed-window/two-row-budget runs against 1k/10k/100k histories and an oversized row. Measure source SQL/page work as well as decoded row counts.The three Add opt-in OpenCode local history with incremental rowid-window ingestion #4744 cursor failures need separate regressions. An invalid row still consumes SQL LIMIT: advance/count every raw row examined and use raw lookahead to establish exhaustion, never successful decodes. For a message exceeding the part budget, checkpoint a strict
(message, part)continuation; do not restart at its first part or advance the late-part watermark until its reevaluation is durably represented. Replace fallback/part contributions without double counting or dropping an unfinished replacement. Finally, never advance to a laterMAX(rowid)from a different snapshot: pin the upper boundary with the SELECT, and checkpoint only consumed rows. An append after that snapshot must remain eligible next pass.Completion and scope need wiring, not new parallel state. Use the store's persisted completion/coverage, but make the read views and catch-up scheduler include this source only for the opted-in ambient ledger. The current read view scopes to Codex roots; merely adding an external database row does not do this. Bucket
time.createdwith the store's pinned calendar/time zone. Recent rowids do not prove recent timestamps, so a partial newest-first scan cannot claim a complete date window without proving the omitted range cannot overlap it. Disabling the option or changing the selected database must exclude its prior contribution from reports without disturbing native history. Preserve cancellation/restart progress and test scoped CODEX_HOME, managed accounts and Spend Dashboard exclusion.A sibling adapter module driven by the scanner is fine; it must share the existing budget and persistence owners. There is no arbitrary adapter line ceiling to target. Keep the change at this source boundary, reuse the store's transaction/read-view owners, and prove bounded progress rather than adding another persistence or publication layer. The direction is approved; the append-only and “unchanged/by construction” claims above are the parts that must change.
If you'd like to go ahead on that basis, please open the implementation as a draft PR early so we can catch structural issues before the full test matrix. Thank you for sticking with this.
29 remaining items
feat(cost): track OpenCode local history in the cost store (#4786)
steipete commentedon Oct 26, 2026
The approved design is implemented in #4786 (914204f), finishing #4757: OpenCode local history is an opt-in source in the existing cost store with bounded, resumable reconciliation and scope limited to the ambient Codex ledger. It will ship in the next release.