Use runtime-selected JSONB with a JSON-text compatibility mode

[DandyLyons]

Goal

Use SQLite JSONB when supported while retaining JSON-text compatibility with older system SQLite runtimes. Part of Phase 2.5 in #93; follows #137 and #138 and coordinates with #139.

Approved design

• Probe JSONB creation and extraction through the actual linked SQLite runtime before creating/migrating an index; do not infer support from the OS version or a different sqlite3 executable.
• New caches use JSONB when the capability is available and JSON text otherwise. Record the chosen encoding in cache metadata.
• Keep exported metadata as ordinary JSON. Define how documented views and raw storage columns expose metadata so binary storage does not accidentally leak into normal JSON output.
• Update schema validation for the selected representation; the current one-argument json_valid(metadata) check is not JSONB validation.
• Preserve metadata extraction, expression-index query planning, type views, and compatibility with appropriately capable external SQLite tools. No GRDB-specific public SQL functions.
• Detect existing JSONB caches on older runtimes before mutation. Provide an explicit rebuild from authoritative files into JSON text; merely changing future writes is insufficient.
• Rebuild preserves saved scopes and declared field-index/view configuration. Define existing text-cache upgrade policy explicitly; do not silently mix policies.
• Preserve pending edits separately from disposable cache rebuilds (Add revision-checked index apply for pending file edits #142).
• Focus compatibility documentation on macOS for this work while retaining the existing system-runtime packaging boundary. Record measured/probed capability rather than promising support solely by OS version.

Research references

JSONB starts with SQLite 3.45.0: https://sqlite.org/json1.html#jsonb
The macOSdb executable and system-library histories place the first recorded JSONB-capable upgrade in macOS 26 betas (3.48.0, then 3.51.0); macOS 27 records 3.54.0. These are catalog observations, not a substitute for runtime probes:

• https://macosdb.com/macos/component/sqlite/
• https://macosdb.com/macos/component/libsqlite3/

Acceptance criteria

• Runtime capability tests select JSONB or JSON text correctly.
• Encoding is persisted and validated on open.
• Metadata queries, field indexes, and type views behave equivalently across encodings.
• Normal exported metadata remains JSON text.
• Incompatible cache detection leaves the cache unchanged and explains the explicit rebuild path.
• Rebuild preserves declarations and pending edits and has tested failure behavior.
• Documentation covers macOS observations, runtime probes, and external-tool compatibility.

Superseded requirements

This deliberately evolves the JSON-text-only requirements in completed #137/#138 and open #139. It does not replace system SQLite with a bundled runtime.