morluto/rea

Fork 0

mirror of https://github.com/morluto/rea.git synced 2026-11-05 06:45:22 +00:00

[PR #1356] [CLOSED] feat(sqlite): inspect database snapshots and selected table rows #2822

New issue

Closed

opened 2026-10-17 21:10:41 +00:00 by bsakaguchi · 0 comments

bsakaguchi commented

2026-10-17 21:10:41 +00:00

Owner

📋 Pull Request Information

Original PR: https://github.com/morluto/rea/pull/1356
Author: @Y0UW1SH
Created: 10/9/2026
Status: ❌ Closed

Base: main ← Head: feat/sqlite-inspection

📝 Commits (10+)

beb2d8b feat(sqlite): inspect database snapshots and selected table rows
443ecd2 fix: avoid SQLite schema collisions and stabilize cleanup fixture
76d7036 fix(sqlite): preserve schema bytes and refuse ambiguous shadow rows
f3212fb fix(ci): complete the packaged managed-native fixture
00243e4 chore: merge main and retain the shared package fixture
a62af04 fix(sqlite): accept inactive journals and preserve acquisition failures
878b873 fix(sqlite): preserve negative zero in JSON evidence
75930f0 fix(sqlite): retain cleanup owners and honor native runtime capabilities
a9ce588 fix(sqlite): retain worker and transport cleanup owners
544c7a1 fix(sqlite): retain descriptor cleanup owners and verify runtime compatibility

📊 Changes

53 files changed (+4936 additions, -17 deletions)

View changed files

📝 .agents/skills/reverse-engineer-anything/SKILL.md (+7 -0)
📝 .agents/skills/reverse-engineer-anything/references/native-and-artifacts.md (+12 -0)
📝 .github/workflows/ci.yml (+6 -0)
➕ .github/workflows/sqlite-runtime.yml (+43 -0)
📝 README.md (+1 -0)
📝 docs/.vitepress/config.ts (+1 -0)
📝 docs/cli.md (+8 -0)
➕ docs/sqlite-databases.md (+121 -0)
📝 package.json (+2 -0)
📝 scripts/ci/plan.mjs (+2 -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 (+8 -1)
➕ src/application/sqlite/SqliteDatabaseCapability.test.ts (+30 -0)
➕ src/application/sqlite/SqliteDatabaseCapability.ts (+34 -0)
➕ src/application/sqlite/SqliteDatabasePort.ts (+17 -0)
➕ src/application/sqlite/SqliteDatabaseService.test.ts (+146 -0)

...and 33 more files

📄 Description

Summary

Add standalone SQLite schema and selected-row inspection through inspect_sqlite_database and rea inspect-sqlite-database. Preserve original database/WAL identities and lossless integer, blob, and text values.

Requested enhancement; no linked issue.

Problem and expected behavior

Callers can inspect a quiescent SQLite database or application-created backup directly, without opening a native analysis target:

rea inspect-sqlite-database ./application.sqlite --table settings --row-limit 20 --json

Equivalent MCP arguments: {"path":"/artifacts/application.sqlite","table":"settings","row_limit":20}.

Review and CI corrections in this update:

Report SQLite runtime availability in rea capabilities before a database is selected. CLI and MCP share the runtime policy and upgrade note; Node 22 and Node 24.11–24.14 remain supported for other REA features.
Retain failed DB, WAL, journal, and worker-reply descriptor owners. Retry cleanup before another inspection and during shutdown, while preserving permission-denied and cancellation diagnostics.
Preserve the original CLI inspection failure alongside a later shutdown failure. Keep cancellation during reply reading typed as cancellation even when releasing its descriptor fails.
Set fixture statements to return BigInt row IDs. The previous default rounded a 64-bit row ID on Node 24 and rejected it on Node 26 before inspection ran.
Incorporate upstream cleanup fixes for browser, EVM, firmware, and shared-skill regressions. Keep selected path assertions exact on Windows by checking decoded CLI messages; normalize the SVG condition to repository formatting.
Share regression fixture setup and cleanup assertions without dropping failure-path cases, preserving the repository duplicate-code limit.
Add a reusable SQLite compatibility workflow covering the minimum supported runtime and rolling Node 22.x, 24.x and 26.x releases alongside the pinned Node 24 full suite.

Change and scope

Copy a checked DB/WAL file set into a private runtime root and inspect it in an owned, cancellable SQLite worker. Inventory tables, columns, indexes, views, triggers, and stored virtual declarations; optionally read bounded rows from one exact ordinary table. Preserve failed resource owners until release is confirmed.

Contract and boundary impact

Semantic owner: provider-neutral SQLite domain contracts, application service, and local SQLite adapter.
CLI/MCP/catalog: one additive, target-free artifact inspection contract with canonical effects and generated catalog registration.
Compatibility: inspection requires Node 24.x >=24.15 or 26+ and native DatabaseSync.limits support. rea capabilities exposes runtime availability and remediation before inspection; unsupported runtimes still run the inventory successfully.
Evidence: original paths, DB/WAL SHA-256 and byte counts, observed schema, exact selected table, explicit row limits and truncation.
Execution/cleanup: temporary filesystem writes and an owned worker with deadlines, native SQLite value/SQL limits, bounded JSON, and a JavaScript heap limit. Source database files remain unchanged.
Metadata/package: authored docs and skill guidance updated; generated outputs stay ignored. No new dependency.

Evidence and regression coverage

Executed real SQLite fixtures cover WAL commits, quoted identifiers, integer boundaries, blob and text encodings, nonfinite values, corrupt input, native resource limits, and journal handling. Filesystem cases cover replacement and changes across acquisition.

Fourteen descriptor cleanup regressions cover DB/WAL/journal/reply release, retry admission, sticky invalidated-handle failures, EACCES/EPERM, caller cancellation, and CLI shutdown diagnostics. The four latest regressions reproduced the diagnostic failures before their fixes; the permanent reply regression uses the production owned-worker launcher.

Representative selected values:

{"columns":["id","payload"],"values":[[{"type":"integer","value":"1"},{"type":"blob","hex":"01"}]],"returned_rows":1,"truncated":true}

Observed and unsupported outcomes remain distinct.
Original artifact identity and source provenance remain preserved.
Failed cleanup and truncation remain visible.

Update hardening

Move SQLite compatibility into a reusable workflow, leaving a small call in the shared CI file.
Cover Node 22.x unavailable-runtime diagnostics, the minimum supported 24.15.0 release, and rolling 24.x/26.x releases. The pinned runtime still runs the full suite.
Use one SQLite test command locally and in CI; directory filters automatically include new service and boundary tests.
Select the shared build when the SQLite workflow changes, and guard the required gate against omitted dependencies or failed, cancelled, skipped, or missing selected SQLite results.
Merge upstream through f998fcb1 and correct its two formatting failures without changing their behavior.

Validation of the tree published as 2d96bf77:

Build, all eight static-check tasks, architecture guards, documentation checks, and discovery of all 811 test files passed.
58 CI selector/gate and reference-source regression tests passed. The workflow-only selector case failed before the fix.
npm run test:sqlite: 81 passed, 2 skipped, 17 worker-dependent failures due to this host's existing procps/PID namespace mismatch. Real worker coverage remains with CI; the previous GitHub run passed the Node 22 and Node 26 SQLite lanes.
Independent review found no actionable defects in the scoped hardening.
Fresh GitHub CI passed all four SQLite runtime jobs (22.x, 24.15.0, 24.x, 26.x), build, static checks, documentation generation, and workflow validation on this head. Remaining repository-wide jobs are still running. The previous run failed the separate binary-layout memory-limit verifier on process-identity cleanup; its new result is not yet complete.

These changes reduce avoidable integration drift and detect runtime regressions. They do not guarantee compatibility with arbitrary future changes to main.

Earlier validation

On local Node 24.19.0:

npm run check:ci: all eight tasks and architecture guards passed.
npm run test:prepare and npm run docs:check: passed.
node scripts/verify-test-discovery.mjs: all 800 test files discovered exactly once.
Runtime policy, capability inventory, SQLite service, CLI output/status and one-shot lifecycle coverage: 54 tests passed.
The compiled CLI discovery regression failed for the missing SQLite row before the fix. After the fix, the compiled CLI and real MCP discovery regression passed, including output-schema validation.
Follow-up validation: 59 path-resolution tests passed against current main; 15 Electron composition cases and the three compiled CLI/MCP preload and catalog cases passed on the feature branch.
Independent review: no actionable findings in the runtime-discovery or four-file CI fixture follow-up.
git diff --check: passed.

Full CI on the discovery fix exposed Electron fixtures missing the explicit imports required by current main and a catalog test with a narrower timeout than the configured suite budget. The follow-up declares inert Electron imports, preserves source-range assertions, and restores that catalog case's configured budget.

Fresh GitHub Actions CI passed on corrected head d63d1b15ca475548f408333be0268146c5ee475b: all 33 jobs, including the final CI required gate.

Full coverage suite: 7,274 passed, 91 skipped, 0 failed across 802 test files.
SQLite runtime compatibility: Node 22.23.1, 67 passed / 33 expected skips; Node 26.11.1, 98 passed / 2 expected skips. Both exercised real CLI and MCP runtime discovery.
Real Apple CLI/MCP, Windows capability/package, Linux x64/arm64 and macOS x64/arm64 package lanes passed.

Compatibility, safety, and release

Additive feature; no migration. Inspection accepts clean offline snapshots and inactive journals. A stable file set does not establish an atomic live backup. Arbitrary caller SQL, extensions, repair, encryption, and deleted-record recovery remain unsupported. Native heap/RSS is not fully capped; the worker and transport output budgets are separate.

Local owned-worker acceptance runs are blocked by this host's PID/procfs mismatch (procps: fatal library error, lookup self). The earlier GitHub Actions run on d63d1b15 passed the real CLI/MCP, process, and platform checks; validation for the hardening update is reported separately above.

Review checklist

Concrete requested enhancement and conventional title.
Named contracts and source identities reviewed.
Meaningful producer, resource, and failure-path tests.
Owning docs and canonical catalog sources updated.
Representative CLI/MCP output included.
Final diff checked for unrelated changes and build outputs.

_{🔄 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/1356 **Author:** [@Y0UW1SH](https://github.com/Y0UW1SH) **Created:** 10/9/2026 **Status:** ❌ Closed **Base:** `main` ← **Head:** `feat/sqlite-inspection` --- ### 📝 Commits (10+) - [`beb2d8b`](https://github.com/morluto/rea/commit/beb2d8b1bfd8e20d98708608d0fceb639cd7107b) feat(sqlite): inspect database snapshots and selected table rows - [`443ecd2`](https://github.com/morluto/rea/commit/443ecd224c5e7f45698e553e3a9228360b07b05c) fix: avoid SQLite schema collisions and stabilize cleanup fixture - [`76d7036`](https://github.com/morluto/rea/commit/76d7036d51a78ed2b7a5d632b5b40a5cb634185b) fix(sqlite): preserve schema bytes and refuse ambiguous shadow rows - [`f3212fb`](https://github.com/morluto/rea/commit/f3212fbf23b242ec8681d31004fab8d8292cc8fb) fix(ci): complete the packaged managed-native fixture - [`00243e4`](https://github.com/morluto/rea/commit/00243e493184a0cc3665ac167f2c2233280b2081) chore: merge main and retain the shared package fixture - [`a62af04`](https://github.com/morluto/rea/commit/a62af04001cdeab4c70706e4cef03becef730a2e) fix(sqlite): accept inactive journals and preserve acquisition failures - [`878b873`](https://github.com/morluto/rea/commit/878b873799d0b0d4a541b77cc85488823d0e89dd) fix(sqlite): preserve negative zero in JSON evidence - [`75930f0`](https://github.com/morluto/rea/commit/75930f07054b7c138877ac03984ffccf4654577b) fix(sqlite): retain cleanup owners and honor native runtime capabilities - [`a9ce588`](https://github.com/morluto/rea/commit/a9ce5887dba17fbdee7701d3ac067dc916f6de4d) fix(sqlite): retain worker and transport cleanup owners - [`544c7a1`](https://github.com/morluto/rea/commit/544c7a11c80d1977476fec4db9c224ca282c0d6e) fix(sqlite): retain descriptor cleanup owners and verify runtime compatibility ### 📊 Changes **53 files changed** (+4936 additions, -17 deletions) <details> <summary>View changed files</summary> 📝 `.agents/skills/reverse-engineer-anything/SKILL.md` (+7 -0) 📝 `.agents/skills/reverse-engineer-anything/references/native-and-artifacts.md` (+12 -0) 📝 `.github/workflows/ci.yml` (+6 -0) ➕ `.github/workflows/sqlite-runtime.yml` (+43 -0) 📝 `README.md` (+1 -0) 📝 `docs/.vitepress/config.ts` (+1 -0) 📝 `docs/cli.md` (+8 -0) ➕ `docs/sqlite-databases.md` (+121 -0) 📝 `package.json` (+2 -0) 📝 `scripts/ci/plan.mjs` (+2 -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` (+8 -1) ➕ `src/application/sqlite/SqliteDatabaseCapability.test.ts` (+30 -0) ➕ `src/application/sqlite/SqliteDatabaseCapability.ts` (+34 -0) ➕ `src/application/sqlite/SqliteDatabasePort.ts` (+17 -0) ➕ `src/application/sqlite/SqliteDatabaseService.test.ts` (+146 -0) _...and 33 more files_ </details> ### 📄 Description ## Summary Add standalone SQLite schema and selected-row inspection through `inspect_sqlite_database` and `rea inspect-sqlite-database`. Preserve original database/WAL identities and lossless integer, blob, and text values. Requested enhancement; no linked issue. ## Problem and expected behavior Callers can inspect a quiescent SQLite database or application-created backup directly, without opening a native analysis target: ```sh rea inspect-sqlite-database ./application.sqlite --table settings --row-limit 20 --json ``` Equivalent MCP arguments: `{"path":"/artifacts/application.sqlite","table":"settings","row_limit":20}`. Review and CI corrections in this update: - Report SQLite runtime availability in `rea capabilities` before a database is selected. CLI and MCP share the runtime policy and upgrade note; Node 22 and Node 24.11–24.14 remain supported for other REA features. - Retain failed DB, WAL, journal, and worker-reply descriptor owners. Retry cleanup before another inspection and during shutdown, while preserving permission-denied and cancellation diagnostics. - Preserve the original CLI inspection failure alongside a later shutdown failure. Keep cancellation during reply reading typed as cancellation even when releasing its descriptor fails. - Set fixture statements to return BigInt row IDs. The previous default rounded a 64-bit row ID on Node 24 and rejected it on Node 26 before inspection ran. - Incorporate upstream cleanup fixes for browser, EVM, firmware, and shared-skill regressions. Keep selected path assertions exact on Windows by checking decoded CLI messages; normalize the SVG condition to repository formatting. - Share regression fixture setup and cleanup assertions without dropping failure-path cases, preserving the repository duplicate-code limit. - Add a reusable SQLite compatibility workflow covering the minimum supported runtime and rolling Node 22.x, 24.x and 26.x releases alongside the pinned Node 24 full suite. ## Change and scope Copy a checked DB/WAL file set into a private runtime root and inspect it in an owned, cancellable SQLite worker. Inventory tables, columns, indexes, views, triggers, and stored virtual declarations; optionally read bounded rows from one exact ordinary table. Preserve failed resource owners until release is confirmed. ## Contract and boundary impact - Semantic owner: provider-neutral SQLite domain contracts, application service, and local SQLite adapter. - CLI/MCP/catalog: one additive, target-free artifact inspection contract with canonical effects and generated catalog registration. - Compatibility: inspection requires Node 24.x >=24.15 or 26+ and native `DatabaseSync.limits` support. `rea capabilities` exposes runtime availability and remediation before inspection; unsupported runtimes still run the inventory successfully. - Evidence: original paths, DB/WAL SHA-256 and byte counts, observed schema, exact selected table, explicit row limits and truncation. - Execution/cleanup: temporary filesystem writes and an owned worker with deadlines, native SQLite value/SQL limits, bounded JSON, and a JavaScript heap limit. Source database files remain unchanged. - Metadata/package: authored docs and skill guidance updated; generated outputs stay ignored. No new dependency. ## Evidence and regression coverage Executed real SQLite fixtures cover WAL commits, quoted identifiers, integer boundaries, blob and text encodings, nonfinite values, corrupt input, native resource limits, and journal handling. Filesystem cases cover replacement and changes across acquisition. Fourteen descriptor cleanup regressions cover DB/WAL/journal/reply release, retry admission, sticky invalidated-handle failures, EACCES/EPERM, caller cancellation, and CLI shutdown diagnostics. The four latest regressions reproduced the diagnostic failures before their fixes; the permanent reply regression uses the production owned-worker launcher. Representative selected values: ```json {"columns":["id","payload"],"values":[[{"type":"integer","value":"1"},{"type":"blob","hex":"01"}]],"returned_rows":1,"truncated":true} ``` - [x] Observed and unsupported outcomes remain distinct. - [x] Original artifact identity and source provenance remain preserved. - [x] Failed cleanup and truncation remain visible. ## Update hardening - Move SQLite compatibility into a reusable workflow, leaving a small call in the shared CI file. - Cover Node 22.x unavailable-runtime diagnostics, the minimum supported 24.15.0 release, and rolling 24.x/26.x releases. The pinned runtime still runs the full suite. - Use one SQLite test command locally and in CI; directory filters automatically include new service and boundary tests. - Select the shared build when the SQLite workflow changes, and guard the required gate against omitted dependencies or failed, cancelled, skipped, or missing selected SQLite results. - Merge upstream through `f998fcb1` and correct its two formatting failures without changing their behavior. Validation of the tree published as `2d96bf77`: - Build, all eight static-check tasks, architecture guards, documentation checks, and discovery of all 811 test files passed. - 58 CI selector/gate and reference-source regression tests passed. The workflow-only selector case failed before the fix. - `npm run test:sqlite`: 81 passed, 2 skipped, 17 worker-dependent failures due to this host's existing procps/PID namespace mismatch. Real worker coverage remains with CI; the previous GitHub run passed the Node 22 and Node 26 SQLite lanes. - Independent review found no actionable defects in the scoped hardening. - [Fresh GitHub CI](https://github.com/morluto/rea/actions/runs/38105180567) passed all four SQLite runtime jobs (22.x, 24.15.0, 24.x, 26.x), build, static checks, documentation generation, and workflow validation on this head. Remaining repository-wide jobs are still running. The previous run failed the separate binary-layout memory-limit verifier on process-identity cleanup; its new result is not yet complete. These changes reduce avoidable integration drift and detect runtime regressions. They do not guarantee compatibility with arbitrary future changes to `main`. ## Earlier validation On local Node 24.19.0: - `npm run check:ci`: all eight tasks and architecture guards passed. - `npm run test:prepare` and `npm run docs:check`: passed. - `node scripts/verify-test-discovery.mjs`: all 800 test files discovered exactly once. - Runtime policy, capability inventory, SQLite service, CLI output/status and one-shot lifecycle coverage: 54 tests passed. - The compiled CLI discovery regression failed for the missing SQLite row before the fix. After the fix, the compiled CLI and real MCP discovery regression passed, including output-schema validation. - Follow-up validation: 59 path-resolution tests passed against current main; 15 Electron composition cases and the three compiled CLI/MCP preload and catalog cases passed on the feature branch. - Independent review: no actionable findings in the runtime-discovery or four-file CI fixture follow-up. - `git diff --check`: passed. Full CI on the discovery fix exposed Electron fixtures missing the explicit imports required by current main and a catalog test with a narrower timeout than the configured suite budget. The follow-up declares inert Electron imports, preserves source-range assertions, and restores that catalog case's configured budget. Fresh [GitHub Actions CI](https://github.com/morluto/rea/actions/runs/38100462698) passed on corrected head `d63d1b15ca475548f408333be0268146c5ee475b`: all 33 jobs, including the final CI required gate. - Full coverage suite: 7,274 passed, 91 skipped, 0 failed across 802 test files. - SQLite runtime compatibility: Node 22.23.1, 67 passed / 33 expected skips; Node 26.11.1, 98 passed / 2 expected skips. Both exercised real CLI and MCP runtime discovery. - Real Apple CLI/MCP, Windows capability/package, Linux x64/arm64 and macOS x64/arm64 package lanes passed. ## Compatibility, safety, and release Additive feature; no migration. Inspection accepts clean offline snapshots and inactive journals. A stable file set does not establish an atomic live backup. Arbitrary caller SQL, extensions, repair, encryption, and deleted-record recovery remain unsupported. Native heap/RSS is not fully capped; the worker and transport output budgets are separate. Local owned-worker acceptance runs are blocked by this host's PID/procfs mismatch (`procps: fatal library error, lookup self`). The earlier GitHub Actions run on `d63d1b15` passed the real CLI/MCP, process, and platform checks; validation for the hardening update is reported separately above. ## Review checklist - [x] Concrete requested enhancement and conventional title. - [x] Named contracts and source identities reviewed. - [x] Meaningful producer, resource, and failure-path tests. - [x] Owning docs and canonical catalog sources updated. - [x] Representative CLI/MCP output included. - [x] Final diff checked for unrelated changes and build outputs. --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>

bsakaguchi

2026-10-17 21:10:41 +00:00