Adopt GRDB with system SQLite for native indexing

[DandyLyons]

Goal

Establish GRDB with system SQLite as the native database dependency for the indexing work in #93, with verified JSON, expression-index, and FTS5 support, without adding SQLite or GRDB to portable MarkdownUtilitiesCore.

Packaging decision

Selected: option 1 — upstream GRDB through Swift Package Manager, using the SQLite runtime supplied by the operating system or Linux distribution.

This supersedes the earlier preference for a controlled bundled SQLite build. md-utils will not vendor or bundle its own SQLite runtime in the selected approach. SQLite versions, compilation options, and SQLite security/correctness updates are supplied by the OS or distribution; md-utils maintains its GRDB dependency and documents supported environments. A particular SQLite version alone is not proof that all required capabilities are available.

Fallback: option 3 — a source-based SwiftPM adaptation of GRDB using a controlled bundled SQLite runtime. Revisit this only if the system-runtime approach cannot meet supported deployment requirements. The fallback would require a maintainable GRDB C-module/package adaptation, matching feature flags, and verified symbol isolation and macOS/Linux compatibility. It must preserve SwiftPM builds; it is not the current implementation plan.

Rejected: option 2 — GRDB's Xcode-based custom SQLite framework workflow. Do not introduce an Xcode-specific build or distribution requirement for this integration.

Scope

• Integrate upstream GRDB into the native indexing target through SwiftPM. Keep GRDB and SQLite out of Core and its WASM dependency graph.
• Replace the earlier bundled-runtime prototype as needed to implement this decision; do not retain a second bundled SQLite runtime for index operations.
• Demonstrate compilation and linkage on macOS and Linux, and document the support matrix for other package targets. Document any required Linux development/runtime packages.
• Measure release binary size and clean-build overhead for the selected GRDB/system-SQLite approach, and check linkage in library consumers. Measurements of the earlier amalgamation prototype do not establish the overhead of this approach.
• Before creating, opening for mutation, or migrating an index, run capability probes through an in-memory GRDB connection using the same linked SQLite runtime as index operations. Exercise JSON queries, JSON expression-index creation/use, and FTS5 creation/write/search; do not rely solely on version numbers or reported compile options.
• Missing required capabilities must produce actionable errors identifying the linked SQLite runtime and the failed capability, with guidance to use a supported OS/distribution/runtime package. Fail before touching the index; do not silently fall back to a different runtime or reduced functionality. Existing non-index commands remain usable.
• Keep the public index schema based on standard SQLite JSON text, expression indexes, and FTS5 facilities, without requiring GRDB-specific SQL functions or custom extensions.
• Document that external SQLite tools use their own runtime and need compatible capabilities; installing md-utils or GRDB does not upgrade those tools.
• Define GRDB dependency maintenance and OS/distribution SQLite support/update policy. Own macOS/Linux validation in this repository.

Acceptance criteria

• macOS and Linux GRDB/system-SQLite builds demonstrate JSON queries, expression indexes, and FTS5.
• Release size/build-time measurements and supported targets/runtime prerequisites are documented for the selected approach.
• GRDB dependency maintenance, system SQLite update responsibilities, and executable capability checks are defined.
• Failed capability checks leave existing index files unchanged, create no new index file, and report actionable errors while non-index commands remain usable.
• Portable Core and its WASM build remain independent of GRDB and SQLite.
• The selected system-runtime approach, SwiftPM bundled-runtime fallback, and rejection of Xcode-based integration are documented.

Out of scope

Implementing the option 3 fallback, Xcode-based database integration, StructuredQueries adoption, DuckDB, JMESPath/SQLite integration, and sqlite-jsonschema. DuckDB analytics and sqlite-jsonschema are potential future options only. Incremental indexing and query/server integration remain in the later child issues of #93.

[DandyLyons]

Completed by PR #144, merged as commit 701e03e.

All acceptance criteria are verified:

• macOS and Linux CI passed the GRDB/system-SQLite JSON, expression-index, FTS5, direct-C-consumer, and linkage checks.
• The seven focused tests passed on both platforms, including failed-probe file preservation, no file creation, and actionable errors. The full local suite passed 1,350 tests; existing CLI help and package builds passed.
• Core WASM CI and native Linux server CI passed; Core and existing CLI/server products remain independent of the new index target.
• docs/sqlite-index-packaging.md records release size/build-time measurements, supported targets and prerequisites, dependency/runtime maintenance responsibilities, and the selected option 1, fallback option 3, and rejected option 2.

Closing this database-foundation issue as complete. Incremental indexing and later query/server work remain in the subsequent issues of #93.

[DandyLyons]

Follow-up architecture decisions are tracked in #146 (optional FTS5 and single-copy body storage), #147 (bounded refresh), and #148 (runtime-selected JSONB with JSON-text compatibility).

These evolve the original unconditional FTS5 and JSON-text-only requirements; metadata-only mode will not require FTS5, and JSONB will be selected through runtime capability probes. The GRDB/system-SQLite packaging decision remains unchanged. This completed issue stays closed; the new requirements have unchecked acceptance criteria in their own issues. The roadmap is updated in #93.