[PR #2271] [MERGED] feat(sqlite): inspect offline snapshots with selective evidence #5129

New issue

Closed

opened 2026-10-31 01:04:12 +00:00 by bsakaguchi · 0 comments

bsakaguchi commented

2026-10-31 01:04:12 +00:00

Owner

📋 Pull Request Information

Original PR: https://github.com/morluto/rea/pull/2271
Author: @N0zoM1z0
Created: 10/12/2026
Status: ✅ Merged
Merged: 10/12/2026
Merged by: @N0zoM1z0

Base: main ← Head: feat/sqlite-analysis-1356-20261012

📝 Commits (5)

7342192 feat(sqlite): add offline database snapshot inspection
7d8238f fix(sqlite): bound context and preserve completed evidence
9361cd9 fix(sqlite): recheck journal validity after snapshot acquisition
b16dda6 chore(sqlite): resolve current main lifecycle integration
dce269a fix(sqlite): consume prepared CI runtime and align effects audit

📊 Changes

59 files changed (+5757 additions, -24 deletions)

View changed files

📝 .agents/skills/reverse-engineer-anything/SKILL.md (+9 -0)
📝 .agents/skills/reverse-engineer-anything/references/native-and-artifacts.md (+15 -0)
📝 .github/workflows/ci.yml (+6 -0)
➕ .github/workflows/sqlite-runtime.yml (+37 -0)
📝 README.md (+1 -0)
📝 docs/.vitepress/config.ts (+1 -0)
📝 docs/cli.md (+8 -0)
➕ docs/sqlite-databases.md (+144 -0)
📝 package.json (+2 -0)
📝 scripts/ci/plan.mjs (+1 -0)
📝 scripts/ci/required.mjs (+1 -1)
📝 scripts/lib/catalog-builders.mjs (+5 -0)
📝 scripts/lib/catalog-core.mjs (+2 -0)
📝 scripts/lib/module-boundaries.mjs (+1 -0)
📝 src/application/CapabilityInventory.ts (+10 -0)
📝 src/application/DirectAnalysisStatus.ts (+11 -1)
📝 src/application/binary/BinarySessionStatus.ts (+7 -3)
➕ src/application/sqlite/SqliteDatabaseCapability.test.ts (+31 -0)
➕ src/application/sqlite/SqliteDatabaseCapability.ts (+33 -0)
➕ src/application/sqlite/SqliteDatabasePort.ts (+17 -0)

...and 39 more files

📄 Description

REA currently cannot inspect application SQLite storage through its public CLI/MCP. This adds inspect_sqlite_database / inspect-sqlite-database for explicit offline database/WAL snapshots, developed from @Y0UW1SH's implementation in #1356. This PR supersedes that proposal and includes its contributor credit.

Return compact inventory by default; selecting a table expands only its schema and samples 20 rows. Exact ordered columns / repeated --column avoids evaluating unrelated cells; schema_scope: database explicitly requests complete stored schema.
Preserve database/WAL identities, integer precision, raw text bytes, blobs, unknown virtual/shadow classification and sample truncation. Source files remain unchanged; this does not claim atomic acquisition from a live writer or forensic recovery.
Reuse canonical grouped discovery and admitted session handling. Preserve completed observations after late cancellation; private-root cleanup failures retain Evidence and resource owners. MCP records completed Evidence before returning a compact retained-evidence reference.
Address the original review's aggregate-resource finding: at most two inspections per provider through acquisition, worker and cleanup. Occupied capacity is retryable before new filesystem/process effects. Recheck rollback-journal validity after copying even when timestamps cannot distinguish an activation. Account for JSON escaping in bounded serializer parts before allocating full output.

Validation on Linux, with two CPU cores and an 8 GiB process-family guard:

npm run check, npm run docs:check, and npm run verify:test-discovery passed on the updated main base.
Final pinned Node 24.18.0 SQLite suite: 105 passed, 2 conditional skips. Node 22.23.3: 68 passed, 39 expected unavailable-runtime skips; public CLI/MCP discovery and diagnostics passed.
Minimum Node 24.15.0 full suite after journal fix: 104 passed, 2 skips; the subsequent bounded-JSON inspection cases also checked separately.
Actual packaged CLI and persistent MCP on official Chinook v1.4.5 and an owned real Firefox persistent profile (eight local history entries), with independent read-only SQLite row/digest oracles. Also inspected a 64,163,840-byte, 200,000-row SQLite fixture. No binaries, database files or profiles are committed.
Native regressions cover committed WAL, DELETE/PERSIST/TRUNCATE, exact names and column order, empty identifiers, malformed data/encoding, generated-value expansion, capacity, cancellation, descriptor cleanup, retained roots, MCP error retention and session draining. Journal activation with unchanged metadata failed before and passed after the producing-boundary fix.
SDK-converted advertised JSON Schemas validated against Draft 2020-12 and the supported client profile; related CI scope/required-lane and discovery tests passed.

Representative context measurements (MCP response text, same frozen inputs):

Request	Original	Scoped output
Chinook inventory	19,538 B	6,600 B
Chinook Track, 20 full rows	29,431 B	16,401 B
Firefox moz_places	43,868 B	20,201 B
Firefox selected id/url/title	43,868 B	12,918 B

The completed Chinook discovery + twenty full-row task, counting one complete SQLite tool definition plus both request JSON payloads and response text, estimates 17,687 → 10,300 o200k_base tokens (about 42% less). Other model tokenizers, catalog/prompt conversion, agent reasoning and billed model usage are unmeasured. Both workflows use two calls without retries and retain identical requested rows.

Isolated final 24-call MCP probe: 10.76 s wall time, 12.72 s process-family CPU time, peak 843.22 MiB RSS. Three repetitions per input; the 64 MB projected sample took 0.67–0.93 s. Each call starts a fresh worker; the MCP server stays warm and filesystem caches were not cleared. Earlier baseline CPU time was not measured. Each call checks source bytes; persistent MCP amortizes server startup rather than reusing potentially stale database contents.

Latest main lifecycle integration passed 35 affected SQLite/MCP/EVM/crash cases. The first CI run exposed two PR integration mistakes: an obsolete runtime artifact name and a missing trusted-worker wording-audit entry. Both are fixed, with a producer/consumer artifact-name-and-layout regression; 121 focused cases and the full local gates passed.

Node 26 and non-Linux behavior are not claimed from local verification; the new compatibility matrix and existing CI provide additional coverage. Native SQLite allocations remain distinct from the 256 MiB worker JavaScript heap; no overall native heap or OS sandbox claim is made.

_{🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.}

## 📋 Pull Request Information **Original PR:** https://github.com/morluto/rea/pull/2271 **Author:** [@N0zoM1z0](https://github.com/N0zoM1z0) **Created:** 10/12/2026 **Status:** ✅ Merged **Merged:** 10/12/2026 **Merged by:** [@N0zoM1z0](https://github.com/N0zoM1z0) **Base:** `main` ← **Head:** `feat/sqlite-analysis-1356-20261012` --- ### 📝 Commits (5) - [`7342192`](https://github.com/morluto/rea/commit/734219285903ad98879988f98a996a273da79baf) feat(sqlite): add offline database snapshot inspection - [`7d8238f`](https://github.com/morluto/rea/commit/7d8238fde188e8f9739cb9a2e9bfbbe43f5190ee) fix(sqlite): bound context and preserve completed evidence - [`9361cd9`](https://github.com/morluto/rea/commit/9361cd91c52130d084c9e64f5d7336cfb72ba8da) fix(sqlite): recheck journal validity after snapshot acquisition - [`b16dda6`](https://github.com/morluto/rea/commit/b16dda60b9baab0e29356f81c22dc48a9b0378a5) chore(sqlite): resolve current main lifecycle integration - [`dce269a`](https://github.com/morluto/rea/commit/dce269a4a7dede416490cb4fda985724a8ff8a0e) fix(sqlite): consume prepared CI runtime and align effects audit ### 📊 Changes **59 files changed** (+5757 additions, -24 deletions) <details> <summary>View changed files</summary> 📝 `.agents/skills/reverse-engineer-anything/SKILL.md` (+9 -0) 📝 `.agents/skills/reverse-engineer-anything/references/native-and-artifacts.md` (+15 -0) 📝 `.github/workflows/ci.yml` (+6 -0) ➕ `.github/workflows/sqlite-runtime.yml` (+37 -0) 📝 `README.md` (+1 -0) 📝 `docs/.vitepress/config.ts` (+1 -0) 📝 `docs/cli.md` (+8 -0) ➕ `docs/sqlite-databases.md` (+144 -0) 📝 `package.json` (+2 -0) 📝 `scripts/ci/plan.mjs` (+1 -0) 📝 `scripts/ci/required.mjs` (+1 -1) 📝 `scripts/lib/catalog-builders.mjs` (+5 -0) 📝 `scripts/lib/catalog-core.mjs` (+2 -0) 📝 `scripts/lib/module-boundaries.mjs` (+1 -0) 📝 `src/application/CapabilityInventory.ts` (+10 -0) 📝 `src/application/DirectAnalysisStatus.ts` (+11 -1) 📝 `src/application/binary/BinarySessionStatus.ts` (+7 -3) ➕ `src/application/sqlite/SqliteDatabaseCapability.test.ts` (+31 -0) ➕ `src/application/sqlite/SqliteDatabaseCapability.ts` (+33 -0) ➕ `src/application/sqlite/SqliteDatabasePort.ts` (+17 -0) _...and 39 more files_ </details> ### 📄 Description REA currently cannot inspect application SQLite storage through its public CLI/MCP. This adds `inspect_sqlite_database` / `inspect-sqlite-database` for explicit offline database/WAL snapshots, developed from @Y0UW1SH's implementation in #1356. This PR supersedes that proposal and includes its contributor credit. - Return compact inventory by default; selecting a table expands only its schema and samples 20 rows. Exact ordered `columns` / repeated `--column` avoids evaluating unrelated cells; `schema_scope: database` explicitly requests complete stored schema. - Preserve database/WAL identities, integer precision, raw text bytes, blobs, unknown virtual/shadow classification and sample truncation. Source files remain unchanged; this does not claim atomic acquisition from a live writer or forensic recovery. - Reuse canonical grouped discovery and admitted session handling. Preserve completed observations after late cancellation; private-root cleanup failures retain Evidence and resource owners. MCP records completed Evidence before returning a compact retained-evidence reference. - Address the original review's aggregate-resource finding: at most two inspections per provider through acquisition, worker and cleanup. Occupied capacity is retryable before new filesystem/process effects. Recheck rollback-journal validity after copying even when timestamps cannot distinguish an activation. Account for JSON escaping in bounded serializer parts before allocating full output. Validation on Linux, with two CPU cores and an 8 GiB process-family guard: - `npm run check`, `npm run docs:check`, and `npm run verify:test-discovery` passed on the updated main base. - Final pinned Node 24.18.0 SQLite suite: 105 passed, 2 conditional skips. Node 22.23.3: 68 passed, 39 expected unavailable-runtime skips; public CLI/MCP discovery and diagnostics passed. - Minimum Node 24.15.0 full suite after journal fix: 104 passed, 2 skips; the subsequent bounded-JSON inspection cases also checked separately. - Actual packaged CLI and persistent MCP on official Chinook v1.4.5 and an owned real Firefox persistent profile (eight local history entries), with independent read-only SQLite row/digest oracles. Also inspected a 64,163,840-byte, 200,000-row SQLite fixture. No binaries, database files or profiles are committed. - Native regressions cover committed WAL, DELETE/PERSIST/TRUNCATE, exact names and column order, empty identifiers, malformed data/encoding, generated-value expansion, capacity, cancellation, descriptor cleanup, retained roots, MCP error retention and session draining. Journal activation with unchanged metadata failed before and passed after the producing-boundary fix. - SDK-converted advertised JSON Schemas validated against Draft 2020-12 and the supported client profile; related CI scope/required-lane and discovery tests passed. Representative context measurements (MCP response text, same frozen inputs): | Request | Original | Scoped output | | --- | ---: | ---: | | Chinook inventory | 19,538 B | 6,600 B | | Chinook Track, 20 full rows | 29,431 B | 16,401 B | | Firefox moz_places | 43,868 B | 20,201 B | | Firefox selected id/url/title | 43,868 B | 12,918 B | The completed Chinook discovery + twenty full-row task, counting one complete SQLite tool definition plus both request JSON payloads and response text, estimates 17,687 → 10,300 `o200k_base` tokens (about 42% less). Other model tokenizers, catalog/prompt conversion, agent reasoning and billed model usage are unmeasured. Both workflows use two calls without retries and retain identical requested rows. Isolated final 24-call MCP probe: 10.76 s wall time, 12.72 s process-family CPU time, peak 843.22 MiB RSS. Three repetitions per input; the 64 MB projected sample took 0.67–0.93 s. Each call starts a fresh worker; the MCP server stays warm and filesystem caches were not cleared. Earlier baseline CPU time was not measured. Each call checks source bytes; persistent MCP amortizes server startup rather than reusing potentially stale database contents. Latest main lifecycle integration passed 35 affected SQLite/MCP/EVM/crash cases. The first CI run exposed two PR integration mistakes: an obsolete runtime artifact name and a missing trusted-worker wording-audit entry. Both are fixed, with a producer/consumer artifact-name-and-layout regression; 121 focused cases and the full local gates passed. Node 26 and non-Linux behavior are not claimed from local verification; the new compatibility matrix and existing CI provide additional coverage. Native SQLite allocations remain distinct from the 256 MiB worker JavaScript heap; no overall native heap or OS sandbox claim is made. --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>

bsakaguchi

2026-10-31 01:04:12 +00:00