txn reconcile: fails with "malformed JSON" when any transaction has non-JSON metadata (which -m/--metadata accepts)

[rrrodzilla]

Summary

bk txn reconcile fails with database error: malformed JSON whenever any transaction in the database has a metadata value that is not a JSON object. Because txn post -m/--metadata is documented as a free-text "Reference number or memo" and happily stores arbitrary strings, this is a state the CLI itself lets you create — but txn reconcile then cannot run at all.

Environment

• bk 0.8.0

Reproduction (minimal)

export BEANKEEPER_DB=/tmp/repro.db
bk init
bk company create acme "Acme"
bk --company acme account create 1000 "Cash"  --type asset
bk --company acme account create 3000 "Equity" --type equity

# -m takes a free-text string per `txn post --help` ("Reference number or memo")
bk --company acme txn post -d "Opening" --date 2026-01-01 \
   -m "open:1000" --debit 1000:100 --credit 3000:100

bk --company acme txn list      --json   # ok: true  (DB is fine)
bk --company acme txn reconcile --json   # ok: false, error below

Result:

{ "ok": false,
  "meta": { "command": "txn.reconcile", "company": "acme" },
  "error": { "code": "DATABASE", "message": "database error: malformed JSON" } }

(Exit code is 4, which is correct.) Remove the -m "open:1000" (or make it a JSON object like {"ref":"open:1000"}) and txn reconcile succeeds. txn list is unaffected in all cases, so the stored data is intact — only the reconcile query breaks.

Root cause (hypothesis)

txn reconcile appears to run a SQL query that JSON-parses the metadata column for every row (presumably to read the correlate field), without guarding against non-JSON values. SQLite's JSON functions raise malformed JSON on the first non-JSON string, aborting the whole command. The parse needs a json_valid(metadata) guard (or equivalent) so plain-string metadata is simply skipped.

Why it matters

• txn post -m/--metadata explicitly accepts free text, so non-JSON metadata is a fully supported state — yet it silently makes txn reconcile unusable for the entire company/database.
• It is all-or-nothing: a single plain-string metadata row anywhere blocks reconciliation of every correctly-correlated intercompany transaction in the DB. Correlations stored as proper JSON ({"correlate": N, ...}) are intact and readable via txn list; only the audit command is blocked.

Suggested fix

1. Guard the metadata JSON extraction in the txn reconcile query with json_valid(metadata) (treat non-JSON / NULL metadata as "no correlate").
2. Optionally, normalize what -m/--metadata stores (e.g. always wrap free text as {"memo": "..."}) so the column is consistently JSON — though the guard in (1) is the real fix and also protects pre-existing data.