morluto/rea

Fork 0

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

[GH-ISSUE #6732] [Bug] SQLite rejects the exact sqlite_schema table as nonexistent #4095

New issue

Closed

opened 2026-10-31 00:46:46 +00:00 by bsakaguchi · 0 comments

bsakaguchi commented

2026-10-31 00:46:46 +00:00

Owner

Originally created by @coygeek on GitHub (Oct 23, 2026).
Original GitHub issue: https://github.com/morluto/rea/issues/6732

Before submitting

I searched the open and closed issues for a related report.
I removed credentials, tokens, private paths, and sensitive target data.

Area

Evidence, artifacts, or reconstruction

Observed behavior

Selecting table: "sqlite_schema" and columns: ["name", "type"] on a healthy, quiescent database fails in all three schema scopes. The shared service returns invalid_request, an issue on table with reason invalid_format, and Selected ordinary table does not exist with that exact name: sqlite_schema.

On the same file, native SQLite lists main.sqlite_schema as a five-column table and successfully selects its rows. An ordinary-table selection and full database-schema inspection both succeed. This was observed through the production SQLite provider, source worker, and shared service. The worker launcher supplied a TypeScript loader instead of using built JavaScript; CLI and MCP transport were not executed.

Reproduction

From a dependency-installed checkout at the revision below, run the following reduced source-boundary reproduction with Node's native SQLite support. The script below was executed before this report was drafted; its fixture is created through ordinary DDL and removed afterward.

node --import ./node_modules/tsx/dist/loader.mjs --input-type=module <<'JS'
import { DatabaseSync } from 'node:sqlite';
import { mkdtemp, rm } from 'node:fs/promises';
import { join } from 'node:path';
import { tmpdir } from 'node:os';
import { pathToFileURL } from 'node:url';
const { inspectSqliteDatabaseSnapshot } = await import(pathToFileURL(
  join(process.cwd(), 'src/sqlite/SqliteDatabaseInspection.ts')).href);
const root = await mkdtemp(join(process.env.REA_AUDIT_SCRATCH ?? tmpdir(), 'rea-schema-table-'));
try {
  const path = join(root, 'sample.sqlite');
  const writer = new DatabaseSync(path);
  writer.exec('CREATE TABLE records(id INTEGER); INSERT INTO records VALUES(1)');
  writer.close();
  const reader = new DatabaseSync(path, { readOnly: true });
  console.log('native rows:', reader.prepare('SELECT name FROM main.sqlite_schema').all());
  console.log('integrity:', reader.prepare('PRAGMA integrity_check').get());
  reader.close();
  for (const schema_scope of ['inventory', 'selected_table', 'database']) {
    try {
      console.log(inspectSqliteDatabaseSnapshot(path, {
        path, table: 'sqlite_schema', columns: ['name'], schema_scope,
      }));
    } catch (error) {
      console.log(schema_scope, error.reason, error.message);
    }
  }
} finally {
  await rm(root, { recursive: true, force: true });
}
JS

The reduced probe produced the failure for each of inventory, selected_table, and database. A separate provider/service probe made the same three selections with columns: ["name", "type"], then checked ordinary-table, full-schema, and genuinely absent-table controls.

Expected behavior

Do not classify the exact existing sqlite_schema table as nonexistent. Allow its supported read-only row selection, or explicitly describe an intentional profile restriction if system schema tables are meant to be excluded.

REA's selection contract permits exact ordinary-table names and lists views, virtual tables, and shadow tables as unsupported row sources; it does not state a system-table exclusion. SQLite's schema-table documentation establishes that sqlite_schema exists in every database and has no row describing itself. The successful native query and PRAGMA table_list result establish this fixture's existence independently of that documentation. Whether maintainers intend to support direct system-table selection remains a product-scope question; the observed false absence is the concrete defect.

Impact and scope

A valid read-only selection of the standard schema table cannot return rows, and recovery incorrectly asks the caller to correct its exact table name. All three schema scopes reproduced this behavior in the provider/service probe. Ordinary application rows and full database-schema inspection continued to work. No corruption, source mutation, or broad inspection failure was observed.

A safe workaround is a read-only node:sqlite query of main.sqlite_schema, as demonstrated by the control. Omitting table and requesting schema_scope: "database" also returned the stored application declarations in REA. Natural usage frequency and other operating systems are unknown.

REA version

Source commit 3c5c887a2cc7371a94fe0fd8b6c478dbf2af60db on main; package manifest version 6.7.0. The published npm artifact was not separately executed.

Runtime and platform

Node.js v26.12.1; Linux x64, Omarchy 4.0.6; built-in SQLite 3.54.0. No external analysis provider.

Target and artifact context

A local, quiescent SQLite database created with ordinary CREATE TABLE, INSERT, and CREATE INDEX statements. The provider/service fixture had one application table, one index, and no WAL or rollback journal. Native PRAGMA integrity_check returned ok.

Interface

Other

Earliest failure stage

Capture or observation

Relevant output or evidence

Reduced source-boundary output:

native rows: [ [Object: null prototype] { name: 'records' } ]
integrity: [Object: null prototype] { integrity_check: 'ok' }
inventory selection Selected ordinary table does not exist with that exact name: sqlite_schema
selected_table selection Selected ordinary table does not exist with that exact name: sqlite_schema
database selection Selected ordinary table does not exist with that exact name: sqlite_schema

The separate provider/service probe observed this projected error for each scope:

{
  "code": "invalid_request",
  "category": "invalid_input",
  "retryable": false,
  "details": {
    "operation": "inspect_sqlite_database",
    "issues": [{
      "path": ["table"],
      "reason": "invalid_format",
      "message": "Selected ordinary table does not exist with that exact name: sqlite_schema"
    }]
  }
}

Its native metadata control returned {"schema":"main","name":"sqlite_schema","type":"table","ncol":5,"wr":0,"strict":0}. Its source SHA-256 stayed unchanged, all six private runtime roots were removed, and ordinary-table selection returned the expected retained text. These are runtime observations. Public CLI/MCP transport, release-package reproduction, other hosts, and a known-good older version were not verified.

Typed outcome or recovery expected

Preserve the distinction between an absent selected table and an existing table whose inspection is unavailable. Do not advise correcting an exact existing identifier. No database mutation or arbitrary caller SQL is needed for the read-only operation.

Analysis or suspected cause

Source-derived: readSchema first collects PRAGMA table_list metadata, which includes sqlite_schema. It then iterates main.sqlite_schema and assigns selectedTable only while processing a table's stored row. SQLite intentionally has no self-entry in that table, so selectedTable stays undefined. readRows then turns the missing retained descriptor into a nonexistent-table error, despite the successful initial existence lookup.

Additional context

This fixture uses no application collation, generated expression, wide result, worker resource failure, or cleanup fault. It is distinct from #6604, #6292, #6607, and #5294. Open and closed issue searches for sqlite_schema, sqlite_master, and schema-table absence found no matching report.

Triage assessment

Impact level: P2
Assessment status: supported
Impact basis: Three valid exact schema-table selections fail and receive false table-absence/input-correction metadata, while ordinary-table and full-schema controls succeed. No broader failure or source mutation was observed; intended system-table support is not explicitly documented.
Workaround status: available
Workaround basis: The same file's schema rows were read successfully through a native read-only node:sqlite connection. REA's full database-schema path also returned the stored application declarations without selecting sqlite_schema.

Originally created by @coygeek on GitHub (Oct 23, 2026). Original GitHub issue: https://github.com/morluto/rea/issues/6732 ## Before submitting - [x] I searched the open and closed issues for a related report. - [x] I removed credentials, tokens, private paths, and sensitive target data. ## Area Evidence, artifacts, or reconstruction ## Observed behavior Selecting `table: "sqlite_schema"` and `columns: ["name", "type"]` on a healthy, quiescent database fails in all three schema scopes. The shared service returns `invalid_request`, an issue on `table` with reason `invalid_format`, and `Selected ordinary table does not exist with that exact name: sqlite_schema`. On the same file, native SQLite lists `main.sqlite_schema` as a five-column table and successfully selects its rows. An ordinary-table selection and full database-schema inspection both succeed. This was observed through the production SQLite provider, source worker, and shared service. The worker launcher supplied a TypeScript loader instead of using built JavaScript; CLI and MCP transport were not executed. ## Reproduction From a dependency-installed checkout at the revision below, run the following reduced source-boundary reproduction with Node's native SQLite support. The script below was executed before this report was drafted; its fixture is created through ordinary DDL and removed afterward. ```sh node --import ./node_modules/tsx/dist/loader.mjs --input-type=module <<'JS' import { DatabaseSync } from 'node:sqlite'; import { mkdtemp, rm } from 'node:fs/promises'; import { join } from 'node:path'; import { tmpdir } from 'node:os'; import { pathToFileURL } from 'node:url'; const { inspectSqliteDatabaseSnapshot } = await import(pathToFileURL( join(process.cwd(), 'src/sqlite/SqliteDatabaseInspection.ts')).href); const root = await mkdtemp(join(process.env.REA_AUDIT_SCRATCH ?? tmpdir(), 'rea-schema-table-')); try { const path = join(root, 'sample.sqlite'); const writer = new DatabaseSync(path); writer.exec('CREATE TABLE records(id INTEGER); INSERT INTO records VALUES(1)'); writer.close(); const reader = new DatabaseSync(path, { readOnly: true }); console.log('native rows:', reader.prepare('SELECT name FROM main.sqlite_schema').all()); console.log('integrity:', reader.prepare('PRAGMA integrity_check').get()); reader.close(); for (const schema_scope of ['inventory', 'selected_table', 'database']) { try { console.log(inspectSqliteDatabaseSnapshot(path, { path, table: 'sqlite_schema', columns: ['name'], schema_scope, })); } catch (error) { console.log(schema_scope, error.reason, error.message); } } } finally { await rm(root, { recursive: true, force: true }); } JS ``` The reduced probe produced the failure for each of `inventory`, `selected_table`, and `database`. A separate provider/service probe made the same three selections with `columns: ["name", "type"]`, then checked ordinary-table, full-schema, and genuinely absent-table controls. ## Expected behavior Do not classify the exact existing `sqlite_schema` table as nonexistent. Allow its supported read-only row selection, or explicitly describe an intentional profile restriction if system schema tables are meant to be excluded. [REA's selection contract](https://github.com/morluto/rea/blob/3c5c887a2cc7371a94fe0fd8b6c478dbf2af60db/docs/sqlite-databases.md#L57) permits exact ordinary-table names and lists views, virtual tables, and shadow tables as unsupported row sources; it does not state a system-table exclusion. SQLite's [schema-table documentation](https://sqlite.org/schematab.html) establishes that `sqlite_schema` exists in every database and has no row describing itself. The successful native query and `PRAGMA table_list` result establish this fixture's existence independently of that documentation. Whether maintainers intend to support direct system-table selection remains a product-scope question; the observed false absence is the concrete defect. ## Impact and scope A valid read-only selection of the standard schema table cannot return rows, and recovery incorrectly asks the caller to correct its exact table name. All three schema scopes reproduced this behavior in the provider/service probe. Ordinary application rows and full database-schema inspection continued to work. No corruption, source mutation, or broad inspection failure was observed. A safe workaround is a read-only `node:sqlite` query of `main.sqlite_schema`, as demonstrated by the control. Omitting `table` and requesting `schema_scope: "database"` also returned the stored application declarations in REA. Natural usage frequency and other operating systems are unknown. ## REA version Source commit `3c5c887a2cc7371a94fe0fd8b6c478dbf2af60db` on main; package manifest version `6.7.0`. The published npm artifact was not separately executed. ## Runtime and platform Node.js `v26.12.1`; Linux x64, Omarchy `4.0.6`; built-in SQLite `3.54.0`. No external analysis provider. ## Target and artifact context A local, quiescent SQLite database created with ordinary `CREATE TABLE`, `INSERT`, and `CREATE INDEX` statements. The provider/service fixture had one application table, one index, and no WAL or rollback journal. Native `PRAGMA integrity_check` returned `ok`. ## Interface Other ## Earliest failure stage Capture or observation ## Relevant output or evidence Reduced source-boundary output: ```text native rows: [ [Object: null prototype] { name: 'records' } ] integrity: [Object: null prototype] { integrity_check: 'ok' } inventory selection Selected ordinary table does not exist with that exact name: sqlite_schema selected_table selection Selected ordinary table does not exist with that exact name: sqlite_schema database selection Selected ordinary table does not exist with that exact name: sqlite_schema ``` The separate provider/service probe observed this projected error for each scope: ```json { "code": "invalid_request", "category": "invalid_input", "retryable": false, "details": { "operation": "inspect_sqlite_database", "issues": [{ "path": ["table"], "reason": "invalid_format", "message": "Selected ordinary table does not exist with that exact name: sqlite_schema" }] } } ``` Its native metadata control returned `{"schema":"main","name":"sqlite_schema","type":"table","ncol":5,"wr":0,"strict":0}`. Its source SHA-256 stayed unchanged, all six private runtime roots were removed, and ordinary-table selection returned the expected `retained` text. These are runtime observations. Public CLI/MCP transport, release-package reproduction, other hosts, and a known-good older version were not verified. ## Typed outcome or recovery expected Preserve the distinction between an absent selected table and an existing table whose inspection is unavailable. Do not advise correcting an exact existing identifier. No database mutation or arbitrary caller SQL is needed for the read-only operation. ## Analysis or suspected cause Source-derived: [readSchema first collects PRAGMA table_list metadata](https://github.com/morluto/rea/blob/3c5c887a2cc7371a94fe0fd8b6c478dbf2af60db/src/sqlite/SqliteDatabaseInspection.ts#L265), which includes `sqlite_schema`. It then [iterates main.sqlite_schema](https://github.com/morluto/rea/blob/3c5c887a2cc7371a94fe0fd8b6c478dbf2af60db/src/sqlite/SqliteDatabaseInspection.ts#L335) and [assigns selectedTable only while processing a table's stored row](https://github.com/morluto/rea/blob/3c5c887a2cc7371a94fe0fd8b6c478dbf2af60db/src/sqlite/SqliteDatabaseInspection.ts#L390). SQLite intentionally has no self-entry in that table, so `selectedTable` stays undefined. [readRows then turns the missing retained descriptor into a nonexistent-table error](https://github.com/morluto/rea/blob/3c5c887a2cc7371a94fe0fd8b6c478dbf2af60db/src/sqlite/SqliteDatabaseInspection.ts#L547), despite the successful initial existence lookup. ## Additional context This fixture uses no application collation, generated expression, wide result, worker resource failure, or cleanup fault. It is distinct from #6604, #6292, #6607, and #5294. Open and closed issue searches for `sqlite_schema`, `sqlite_master`, and schema-table absence found no matching report. ## Triage assessment - Impact level: P2 - Assessment status: supported - Impact basis: Three valid exact schema-table selections fail and receive false table-absence/input-correction metadata, while ordinary-table and full-schema controls succeed. No broader failure or source mutation was observed; intended system-table support is not explicitly documented. - Workaround status: available - Workaround basis: The same file's schema rows were read successfully through a native read-only node:sqlite connection. REA's full database-schema path also returned the stored application declarations without selecting sqlite_schema.

bsakaguchi closed this issue

2026-10-31 00:46:46 +00:00

bsakaguchi referenced this issue

2026-10-31 01:11:29 +00:00

[PR #4095] [MERGED] feat(kuna): add cached string inventory and search #6053

bsakaguchi referenced this issue

2026-10-31 01:11:34 +00:00

[PR #4113] [MERGED] fix(kuna): say why Kuna cannot answer instead of a generic message #6064

bsakaguchi referenced this issue

2026-10-31 01:11:36 +00:00

[PR #4116] [MERGED] fix(kuna): keep the real lane's wide-string marker independent of layout #6066

bsakaguchi referenced this issue