sqlite: schema version, & fail run on writes fail

Spine now declares sqlite_db_schema_version and stamps it into the
database as PRAGMA user_version when the tables are created, in
both the shared and the discrete DDL blocks. On opening an
existing database it compares, and says once per run which version
it found and which it writes.

Failures are now tallied (shared, the output can run in parallel),
reported one line each on stderr naming the operation, and main
exits 1 without printing "run complete, ok".

Two tests under test/, both taking the spine binary as their first
argument and building their own database from data/pod unless
$SpinePOD says otherwise:
  test-search-db-schema.sh  names: every column the search form
    uses exists, and spine's declaration, the database's stamp
    and the search form's expectation all agree
  test-search-cgi.sh  behaviour: the real search binary answers
    real requests against a fresh database, no web server
    involved, the probe values read out of whichever database it
    is given

(assisted by Claude-Code)

7 files changed, 1118 insertions, 137 deletions

diff --git a/org/out_sqlite.org b/org/out_sqlite.org
index e4499ce..275931b 100644
--- a/org/out_sqlite.org
+++ b/org/out_sqlite.org
@@ -48,17 +48,18 @@ sudo chown ralph:ralph /var/www
 module sisudoc.outputs.io_out.sqlite;
 <<sqlite_imports>>
 <<sqlite_common>>
-<<sqlite_collection>>
-<<sqlite_discrete>>
-<<sqlite_run>>
-<<sqlite_munge>>
-<<sqlite_create>>
-<<sqlite_delete>>
-<<sqlite_insert_metadata>>
-<<sqlite_insert_metadata_topics>>
-<<sqlite_insert_doc_objects_loop>>
-<<sqlite_tables_create>>
-<<sqlite_tables_drop>>
+<<sqlite_schema>>
+<<template_sqlite_collection>>
+<<template_sqlite_discrete>>
+<<template_sqlite_run>>
+<<templates_sqlite_munge>>
+<<template_sqlite_create>>
+<<template_sqlite_delete>>
+<<template_sqlite_insert_metadata>>
+<<template_sqlite_insert_metadata_topics>>
+<<template_sqlite_insert_doc_objects_loop>>
+<<template_sqlite_tables_create>>
+<<template_sqlite_tables_drop>>
 #+END_SRC
 
 *** hub
@@ -75,9 +76,59 @@ static auto mkup = InlineMarkup();
 long _metadata_tid_lastrowid;
 #+END_SRC
 
+**** sqlite schema
+
+#+NAME: sqlite_schema
+#+HEADER: :noweb yes
+#+BEGIN_SRC d
+/+ ↓ sqlite db schema version, stamped into the db as PRAGMA user_version +/
+/+   bump whenever the table or index definitions in this module change, and +/
+/+   bump the matching expectation in sisudoc-spine-search-cgi +/
+enum sqlite_db_schema_version = 1;
+/+ ↓ tally of failed sqlite statements, shared: sqlite output may run in parallel +/
+private shared int _sqlite_failure_tally = 0;
+/+ ↓ schema version mismatch is reported once per run, not once per document +/
+private shared int _sqlite_schema_mismatch_reported = 0;
+/+ ↓ record a failed sqlite statement, reported on stderr, tallied for exit status +/
+void sqliteFailureRecord(string note, string msg) {
+  import core.atomic : atomicOp;
+  atomicOp!"+="(_sqlite_failure_tally, 1);
+  stderr.writeln("ERROR SQLite (", note, "): ", msg);
+}
+/+ ↓ count of failed sqlite statements this run, zero when all went well +/
+int sqliteFailureTally() {
+  import core.atomic : atomicLoad;
+  return atomicLoad(_sqlite_failure_tally);
+}
+/+ ↓ compare the schema version stamped in an existing db with this spine's +/
+void sqliteSchemaVersionCheck(Db)(Db db, string sqlite_file) {
+  import core.atomic : atomicOp;
+  int _found;
+  try {
+    _found = db.execute("PRAGMA user_version;").oneValue!int;
+  } catch (Exception) {
+    return;
+  }
+  if (_found == sqlite_db_schema_version
+  || _found == 0) { /+ ↓ zero: db predates the stamp, or has no tables yet +/
+    return;
+  }
+  if (atomicOp!"+="(_sqlite_schema_mismatch_reported, 1) == 1) {
+    stderr.writeln("ERROR SQLite : db schema version mismatch: ", sqlite_file);
+    stderr.writeln("       db holds schema version ", _found,
+      ", this spine writes schema version ", sqlite_db_schema_version);
+    stderr.writeln("       recreate the db, e.g.: spine --sqlite-db-recreate",
+      " --sqlite-db-path=... --sqlite-db-filename=...");
+    sqliteFailureRecord("schema version",
+      "db schema version " ~ _found.to!string
+      ~ " != spine schema version " ~ sqlite_db_schema_version.to!string);
+  }
+}
+#+END_SRC
+
 **** collection
 
-#+NAME: sqlite_collection
+#+NAME: template_sqlite_collection
 #+HEADER: :noweb yes
 #+BEGIN_SRC d
 template SQLiteHubBuildTablesAndPopulate() {
@@ -107,6 +158,7 @@ template SQLiteHubBuildTablesAndPopulate() {
     }
     try {
       auto db = Database(pth_sqlite.sqlite_file);
+      sqliteSchemaVersionCheck(db, pth_sqlite.sqlite_file);
       SQLiteDbStatementComposite!()(db, doc);
     }
     catch (FileException e) {
@@ -138,7 +190,7 @@ template SQLiteHubBuildTablesAndPopulate() {
 
 **** discrete
 
-#+NAME: sqlite_discrete
+#+NAME: template_sqlite_discrete
 #+HEADER: :noweb yes
 #+BEGIN_SRC d
 template SQLiteHubDiscreteBuildTablesAndPopulate() {
@@ -157,6 +209,7 @@ template SQLiteHubDiscreteBuildTablesAndPopulate() {
       }
     }
     auto db = Database(pth_sqlite.sqlite_file(doc.matters.src.filename));
+    sqliteSchemaVersionCheck(db, pth_sqlite.sqlite_file(doc.matters.src.filename));
     template SQLiteDiscreteDbStatementComposite() {
       void SQLiteDiscreteDbStatementComposite(Db,D)(
         Db   db,
@@ -199,7 +252,7 @@ template SQLiteHubDiscreteBuildTablesAndPopulate() {
 
 *** db run
 
-#+NAME: sqlite_run
+#+NAME: template_sqlite_run
 #+BEGIN_SRC d
 template SQLiteDbRun() {
   void SQLiteDbRun(Db,St,O)(
@@ -218,9 +271,9 @@ template SQLiteDbRun() {
         "\nCOMMIT TRANSACTION;\n"
       );
     } catch (ErrnoException ex) {
-      writeln("ERROR SQLite : ", ex);
+      sqliteFailureRecord(note, ex.msg);
     } catch (Exception ex) {
-      writeln("ERROR SQLite : ", ex);
+      sqliteFailureRecord(note, ex.msg);
     }
     { /+ debug +/
       if (opt_action.debug_do_sqlite) {
@@ -236,7 +289,7 @@ template SQLiteDbRun() {
 
 *** munge
 
-#+NAME: sqlite_munge
+#+NAME: templates_sqlite_munge
 #+HEADER: :noweb yes
 #+BEGIN_SRC d
 template SQLinsertDelimiter() {
@@ -288,7 +341,7 @@ template SQLiteFormatAndLoadObject() {
 *** sqlite instructions
 **** create
 
-#+NAME: sqlite_create
+#+NAME: template_sqlite_create
 #+HEADER: :noweb yes
 #+BEGIN_SRC d
 template SQLiteTablesReCreate() {
@@ -301,7 +354,10 @@ template SQLiteTablesReCreate() {
       <<sqlite_statement_create_table_topic_register>>
       <<sqlite_statement_create_table_objects>>
       <<sqlite_statement_create_index>>
-    ┃",);
+      PRAGMA user_version = %s;
+    ┃",
+      sqlite_db_schema_version,
+    );
     return _sql_instruct;
   }
 }
@@ -309,7 +365,7 @@ template SQLiteTablesReCreate() {
 
 **** delete
 
-#+NAME: sqlite_delete
+#+NAME: template_sqlite_delete
 #+HEADER: :noweb yes
 #+BEGIN_SRC d
 template SQLiteDeleteDocument() {
@@ -324,7 +380,7 @@ template SQLiteDeleteDocument() {
 
 **** insert metadata
 
-#+NAME: sqlite_insert_metadata
+#+NAME: template_sqlite_insert_metadata
 #+HEADER: :noweb yes
 #+BEGIN_SRC d
 template SQLiteInsertMetadata() {
@@ -340,7 +396,7 @@ template SQLiteInsertMetadata() {
 
 **** insert metadata topics
 
-#+NAME: sqlite_insert_metadata_topics
+#+NAME: template_sqlite_insert_metadata_topics
 #+HEADER: :noweb yes
 #+BEGIN_SRC d
 template SQLiteInsertMetadataTopics() {
@@ -358,7 +414,7 @@ template SQLiteInsertMetadataTopics() {
 
 **** insert doc objects loop
 
-#+NAME: sqlite_insert_doc_objects_loop
+#+NAME: template_sqlite_insert_doc_objects_loop
 #+HEADER: :noweb yes
 #+BEGIN_SRC d
 template SQLiteInsertDocObjectsLoop() {
@@ -379,26 +435,13 @@ template SQLiteInsertDocObjectsLoop() {
 
 **** tables create
 
-#+NAME: sqlite_tables_create
+#+NAME: template_sqlite_tables_create
 #+HEADER: :noweb yes
 #+BEGIN_SRC d
 template SQLiteTablesCreate() {
   void SQLiteTablesCreate(E,O,C)(E env, O opt_action, C config) {
     import d2sqlite3;
-    template SQLiteTablesReCreate() {
-      string SQLiteTablesReCreate()() {
-        string _sql_instruct;
-        _sql_instruct = format(q"┃
-          <<sqlite_statement_drop_existing_index>>
-          <<sqlite_statement_drop_existing_tables>>
-          <<sqlite_statement_create_table_metadata_and_src_txt>>
-          <<sqlite_statement_create_table_topic_register>>
-          <<sqlite_statement_create_table_objects>>
-          <<sqlite_statement_create_index>>
-        ┃",);
-        return _sql_instruct;
-      }
-    }
+    <<template_sqlite_create>>
     try {
       <<sqlite_db_create>>
     }
@@ -481,7 +524,7 @@ if (opt_action.sqlite_db_create) {
 
 **** tables drop
 
-#+NAME: sqlite_tables_drop
+#+NAME: template_sqlite_tables_drop
 #+BEGIN_SRC d
 template SQLiteDbDrop() {
   void SQLiteDbDrop(O,C)(O opt_action, C config) {
diff --git a/org/spine.org b/org/spine.org
index 51926d5..86003b9 100644
--- a/org/spine.org
+++ b/org/spine.org
@@ -124,6 +124,18 @@ string program_name = "spine";
   foreach (ref _dlr; _url_downloads) {
     cleanupDownload(_dlr);
   }
+  /+ ↓ sqlite statements that failed are fatal to the run, report and exit non-zero +/
+  {
+    import sisudoc.outputs.io_out.sqlite : sqliteFailureTally;
+    if (sqliteFailureTally() > 0) {
+      stderr.writefln(
+        "~ run FAILED ~ %s sqlite statement(s) failed, db not written as expected",
+        sqliteFailureTally(),
+      );
+      import core.stdc.stdlib : exit;
+      exit(1);
+    }
+  }
 }
 #+END_SRC
 
diff --git a/org/tests_for_document_abstraction_shell_scripts.org b/org/tests_for_document_abstraction_shell_scripts.org
index 1c93eed..30771be 100644
--- a/org/tests_for_document_abstraction_shell_scripts.org
+++ b/org/tests_for_document_abstraction_shell_scripts.org
@@ -15,7 +15,7 @@
 - magic single double-quote → " ← FIX changes hilighting behavior (occuring
   after it) in org document. INVESTIGATE (org-mode CONFIG?) FIND & FIX
 
-* test shell scripts document abstraction
+* document abstraction - ocda
 ** run tests
 
 #+HEADER: :tangle ../test/run-tests.sh
@@ -120,7 +120,8 @@ else
 fi
 #+END_SRC
 
-** ssp document abstraction test/test-abstraction-ssp.sh
+** ssp ocda abstraction
+*** document.ssp abstraction test/test-abstraction-ssp.sh
 
 #+HEADER: :tangle ../test/test-abstraction-ssp.sh
 #+HEADER: :tangle-mode (identity #o755)
@@ -306,7 +307,98 @@ else
 fi
 #+END_SRC
 
-** db document abstraction test/test-abstraction-db.sh
+*** document.ssp abstraction test/test-abstraction-ssp-roundtrip.sh
+
+#+HEADER: :tangle ../test/test-abstraction-ssp-roundtrip.sh
+#+HEADER: :tangle-mode (identity #o755)
+#+HEADER: :shebang "#!/usr/bin/env sh"
+#+BEGIN_SRC shell
+# test-abstraction-ssp-roundtrip.sh
+#
+# Reads each reference .ssp back into the abstraction and emits it again,
+# and requires the result to be byte identical to the file it read.
+#
+# What that proves: the reader (sisudoc.ocda.abstraction.ssp_in) restores
+# every field the writer emits. The check is made against the writer itself,
+# since both go through template sspObjectRecord, so there is no second
+# description of the format that could drift from the first.
+#
+# It needs no sample documents and no output directory: the committed
+# reference set is the input. Run test-abstraction-ssp.sh first if you want
+# to know that the reference set is itself current.
+#
+# Usage:
+#   ./test/test-abstraction-ssp-roundtrip.sh                  # uses result/bin/spine
+#   ./test/test-abstraction-ssp-roundtrip.sh ./bin/spine-ldc  # specify binary
+#
+# Exit codes:
+#   0  every reference .ssp round trips byte identically
+#   1  one or more do not (the first differences are printed)
+#   2  set-up problem (no binary, no reference set)
+
+set -e
+
+SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
+SPINE_DIR="$(cd "$SCRIPT_DIR/.." && pwd)"
+REF_DIR="$SCRIPT_DIR/reference/abstraction"
+
+SPINE_BIN="${1:-}"
+if [ -z "$SPINE_BIN" ]; then
+  if   [ -x "$SPINE_DIR/result/bin/spine" ]; then SPINE_BIN="$SPINE_DIR/result/bin/spine"
+  elif [ -x "$SPINE_DIR/bin/spine-ldc" ];    then SPINE_BIN="$SPINE_DIR/bin/spine-ldc"
+  elif [ -x "$SPINE_DIR/bin/spine" ];        then SPINE_BIN="$SPINE_DIR/bin/spine"
+  else
+    echo "ERROR: spine binary not found. Specify path as argument." >&2
+    exit 2
+  fi
+fi
+
+if [ ! -d "$REF_DIR" ] || [ -z "$(ls "$REF_DIR"/*.ssp 2>/dev/null)" ]; then
+  echo "ERROR: no reference files found at $REF_DIR" >&2
+  echo "Run ./test/test-abstraction-ssp.sh --generate first." >&2
+  exit 2
+fi
+
+TMP="$SCRIPT_DIR/current-roundtrip"
+rm -rf "$TMP"
+mkdir -p "$TMP"
+
+echo "spine binary: $SPINE_BIN"
+echo "reference: $REF_DIR"
+echo "Reading each .ssp back and emitting it again ..."
+
+COUNT=0
+FAILURES=0
+for ref in "$REF_DIR"/*.ssp; do
+  base=$(basename "$ref")
+  COUNT=$((COUNT + 1))
+  if ! $SPINE_BIN --ssp-round-trip="$ref" > "$TMP/$base" 2> "$TMP/$base.err"; then
+    echo "ERROR: $base (spine exited non-zero)"
+    head -3 "$TMP/$base.err"
+    FAILURES=$((FAILURES + 1))
+    continue
+  fi
+  if ! cmp -s "$ref" "$TMP/$base"; then
+    echo "DIFFERS: $base ($(diff "$ref" "$TMP/$base" | grep -c '^[<>]') lines)"
+    diff --unified=1 "$ref" "$TMP/$base" | head -12
+    echo "  ..."
+    FAILURES=$((FAILURES + 1))
+  fi
+done
+
+rm -rf "$TMP"
+
+if [ "$FAILURES" -eq 0 ]; then
+  echo "PASS: all $COUNT .ssp files read back and re-emit byte identically"
+  exit 0
+else
+  echo "FAIL: $FAILURES of $COUNT do not round trip"
+  exit 1
+fi
+#+END_SRC
+
+** db ocda abstraction
+*** document db ocda test/test-abstraction-db.sh
 
 #+HEADER: :tangle ../test/test-abstraction-db.sh
 #+HEADER: :tangle-mode (identity #o755)
@@ -545,97 +637,7 @@ else
 fi
 #+END_SRC
 *
-** ssp document abstraction test/test-abstraction-ssp-roundtrip.sh
-
-#+HEADER: :tangle ../test/test-abstraction-ssp-roundtrip.sh
-#+HEADER: :tangle-mode (identity #o755)
-#+HEADER: :shebang "#!/usr/bin/env sh"
-#+BEGIN_SRC shell
-# test-abstraction-ssp-roundtrip.sh
-#
-# Reads each reference .ssp back into the abstraction and emits it again,
-# and requires the result to be byte identical to the file it read.
-#
-# What that proves: the reader (sisudoc.ocda.abstraction.ssp_in) restores
-# every field the writer emits. The check is made against the writer itself,
-# since both go through template sspObjectRecord, so there is no second
-# description of the format that could drift from the first.
-#
-# It needs no sample documents and no output directory: the committed
-# reference set is the input. Run test-abstraction-ssp.sh first if you want
-# to know that the reference set is itself current.
-#
-# Usage:
-#   ./test/test-abstraction-ssp-roundtrip.sh                  # uses result/bin/spine
-#   ./test/test-abstraction-ssp-roundtrip.sh ./bin/spine-ldc  # specify binary
-#
-# Exit codes:
-#   0  every reference .ssp round trips byte identically
-#   1  one or more do not (the first differences are printed)
-#   2  set-up problem (no binary, no reference set)
-
-set -e
-
-SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
-SPINE_DIR="$(cd "$SCRIPT_DIR/.." && pwd)"
-REF_DIR="$SCRIPT_DIR/reference/abstraction"
-
-SPINE_BIN="${1:-}"
-if [ -z "$SPINE_BIN" ]; then
-  if   [ -x "$SPINE_DIR/result/bin/spine" ]; then SPINE_BIN="$SPINE_DIR/result/bin/spine"
-  elif [ -x "$SPINE_DIR/bin/spine-ldc" ];    then SPINE_BIN="$SPINE_DIR/bin/spine-ldc"
-  elif [ -x "$SPINE_DIR/bin/spine" ];        then SPINE_BIN="$SPINE_DIR/bin/spine"
-  else
-    echo "ERROR: spine binary not found. Specify path as argument." >&2
-    exit 2
-  fi
-fi
-
-if [ ! -d "$REF_DIR" ] || [ -z "$(ls "$REF_DIR"/*.ssp 2>/dev/null)" ]; then
-  echo "ERROR: no reference files found at $REF_DIR" >&2
-  echo "Run ./test/test-abstraction-ssp.sh --generate first." >&2
-  exit 2
-fi
-
-TMP="$SCRIPT_DIR/current-roundtrip"
-rm -rf "$TMP"
-mkdir -p "$TMP"
-
-echo "spine binary: $SPINE_BIN"
-echo "reference: $REF_DIR"
-echo "Reading each .ssp back and emitting it again ..."
-
-COUNT=0
-FAILURES=0
-for ref in "$REF_DIR"/*.ssp; do
-  base=$(basename "$ref")
-  COUNT=$((COUNT + 1))
-  if ! $SPINE_BIN --ssp-round-trip="$ref" > "$TMP/$base" 2> "$TMP/$base.err"; then
-    echo "ERROR: $base (spine exited non-zero)"
-    head -3 "$TMP/$base.err"
-    FAILURES=$((FAILURES + 1))
-    continue
-  fi
-  if ! cmp -s "$ref" "$TMP/$base"; then
-    echo "DIFFERS: $base ($(diff "$ref" "$TMP/$base" | grep -c '^[<>]') lines)"
-    diff --unified=1 "$ref" "$TMP/$base" | head -12
-    echo "  ..."
-    FAILURES=$((FAILURES + 1))
-  fi
-done
-
-rm -rf "$TMP"
-
-if [ "$FAILURES" -eq 0 ]; then
-  echo "PASS: all $COUNT .ssp files read back and re-emit byte identically"
-  exit 0
-else
-  echo "FAIL: $FAILURES of $COUNT do not round trip"
-  exit 1
-fi
-#+END_SRC
-
-** ssp document abstraction test-abstraction-db-roundtrip.sh
+*** document db ocda test-abstraction-db-roundtrip.sh
 
 #+HEADER: :tangle ../test/test-abstraction-db-roundtrip.sh
 #+HEADER: :tangle-mode (identity #o755)
@@ -740,3 +742,441 @@ else
   exit 1
 fi
 #+END_SRC
+
+* collection search db + cgi form
+** test search db schema
+
+#+HEADER: :tangle ../test/test-search-db-schema.sh
+#+HEADER: :tangle-mode (identity #o755)
+#+HEADER: :shebang "#!/usr/bin/env sh"
+#+BEGIN_SRC shell
+# test-search-db-schema.sh
+#
+# Schema contract between spine's --sqlite-update output and the search form
+# in sisudoc-spine-search-cgi.
+#
+# Spine holds the only copy of the DDL; the search form holds no DDL at all,
+# only a small set of column references. Nothing connects the two, so a
+# column renamed in spine is found by whoever runs a search next. This is the
+# check that finds it earlier: build a search database, then read the column
+# references out of the search form's source and require every one of them to
+# exist in what was built.
+#
+# It also requires the three-way agreement on schema version: the version
+# spine declares, the version stamped into the database it built, and the
+# version the search form expects.
+#
+# Usage:
+#   ./test/test-search-db-schema.sh [/path/to/spine]
+#
+# Environment:
+#   SpinePOD             pod directory to build the db from, relative to the
+#                        spine directory (default: data/pod, the bundled set)
+#   SpineSearchCGIsrc    path to the search cgi source file or its repository
+#                        (default: ../sisudoc-spine-search-cgi)
+#
+# sqlite3 is needed. If it is not on PATH the script re-runs itself inside a
+# nix shell that has it.
+#
+# Exit codes:
+#   0  spine's database satisfies every expectation the search form has
+#   1  a mismatch was found (printed)
+#   2  set-up problem (no binary, no cgi source, no sqlite3, no samples)
+
+set -e
+
+SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
+SPINE_DIR="$(cd "$SCRIPT_DIR/.." && pwd)"
+
+if ! command -v sqlite3 > /dev/null 2>&1; then
+  if command -v nix > /dev/null 2>&1; then
+    echo "sqlite3 not on PATH, re-running inside nix shell \"nixpkgs#sqlite\""
+    exec nix shell "nixpkgs#sqlite" -c "$0" "$@"
+  fi
+  echo "ERROR: sqlite3 not on PATH and nix not available." >&2
+  exit 2
+fi
+
+SPINE_BIN="${1:-}"
+if [ -z "$SPINE_BIN" ]; then
+  if   [ -x "$SPINE_DIR/result/bin/spine" ]; then SPINE_BIN="$SPINE_DIR/result/bin/spine"
+  elif [ -x "$SPINE_DIR/bin/spine-ldc" ];    then SPINE_BIN="$SPINE_DIR/bin/spine-ldc"
+  elif [ -x "$SPINE_DIR/bin/spine" ];        then SPINE_BIN="$SPINE_DIR/bin/spine"
+  else
+    echo "ERROR: spine binary not found. Specify path as argument." >&2
+    exit 2
+  fi
+fi
+
+# the search form's source, the other half of the contract
+CGI_SRC="${SpineSearchCGIsrc:-$SPINE_DIR/../sisudoc-spine-search-cgi}"
+if [ -d "$CGI_SRC" ]; then
+  CGI_SRC="$CGI_SRC/src/sisudoc/spine_search.d"
+fi
+if [ ! -f "$CGI_SRC" ]; then
+  echo "ERROR: search cgi source not found at $CGI_SRC" >&2
+  echo "  set \$SpineSearchCGIsrc to the sisudoc-spine-search-cgi checkout" >&2
+  exit 2
+fi
+
+SAMPLES_RAW="$SPINE_DIR/${SpinePOD:-data/pod}"
+if [ -d "$SAMPLES_RAW" ]; then
+  SAMPLES_DIR="$(cd "$SAMPLES_RAW" && pwd)"
+else
+  echo "ERROR: sample documents not found at $SAMPLES_RAW" >&2
+  exit 2
+fi
+
+OUT_DIR="$SCRIPT_DIR/current-search-db"
+DB_DIR="$OUT_DIR/sqlite-database"
+DB_NAME="spine.search.db"
+DB="$DB_DIR/$DB_NAME"
+
+echo "spine binary: $SPINE_BIN"
+echo "search cgi:   $CGI_SRC"
+echo "samples:      $SAMPLES_DIR"
+
+rm -rf "$OUT_DIR"
+mkdir -p "$DB_DIR"
+
+echo "Building search database ..."
+"$SPINE_BIN" --sqlite-db-recreate \
+  --sqlite-db-path="$DB_DIR" --sqlite-db-filename="$DB_NAME" > /dev/null
+# the update has to be able to fail the test: spine exits non-zero when any
+# sqlite statement failed, and that is exactly the case this test is here for
+if ! "$SPINE_BIN" --sqlite-update \
+  --sqlite-db-path="$DB_DIR" --sqlite-db-filename="$DB_NAME" \
+  --output="$OUT_DIR" "$SAMPLES_DIR"/* > "$OUT_DIR/build.log" 2>&1; then
+  echo "FAIL: spine --sqlite-update reported failure"
+  grep "ERROR SQLite\|run FAILED" "$OUT_DIR/build.log" | head -20
+  exit 1
+fi
+
+FAILURES=0
+fail() { echo "  MISMATCH $*"; FAILURES=$((FAILURES + 1)); }
+
+# ── the database was built at all
+if [ ! -f "$DB" ]; then
+  echo "FAIL: no database at $DB"
+  exit 1
+fi
+
+# ── the tables the search form joins
+echo "Checking tables ..."
+for t in metadata_and_text doc_objects topic_register; do
+  found=$(sqlite3 "$DB" \
+    "select count(*) from sqlite_master where type='table' and name='$t';")
+  [ "$found" = "1" ] || fail "table $t missing"
+done
+
+# ── the tables are populated, an empty db satisfies a column check trivially
+docs=$(sqlite3 "$DB" "select count(*) from metadata_and_text;" 2>/dev/null || echo 0)
+objs=$(sqlite3 "$DB" "select count(*) from doc_objects;" 2>/dev/null || echo 0)
+echo "  documents: $docs, objects: $objs"
+[ "$docs" -gt 0 ] || fail "metadata_and_text is empty"
+[ "$objs" -gt 0 ] || fail "doc_objects is empty"
+
+# ── schema version: spine's declaration, the stamp in the db, the search
+#    form's expectation; all three have to agree
+echo "Checking schema version ..."
+SPINE_SQL_SRC="$SPINE_DIR/src/sisudoc/outputs/io_out/sqlite.d"
+declared=$(sed -n 's/^enum sqlite_db_schema_version *= *\([0-9][0-9]*\);.*/\1/p' \
+  "$SPINE_SQL_SRC" | head -1)
+stamped=$(sqlite3 "$DB" "pragma user_version;")
+expected=$(sed -n 's/^enum sqlite_db_schema_version_expected *= *\([0-9][0-9]*\);.*/\1/p' \
+  "$CGI_SRC" | head -1)
+echo "  spine declares: ${declared:-unset}, db stamped: ${stamped:-unset}, cgi expects: ${expected:-unset}"
+if [ -z "$declared" ]; then
+  fail "spine declares no sqlite_db_schema_version in $SPINE_SQL_SRC"
+elif [ "$stamped" != "$declared" ]; then
+  fail "db stamped user_version $stamped, spine declares $declared"
+fi
+if [ -z "$expected" ]; then
+  fail "search form declares no sqlite_db_schema_version_expected"
+elif [ -n "$declared" ] && [ "$expected" != "$declared" ]; then
+  fail "search form expects schema version $expected, spine writes $declared"
+fi
+
+# ── every column the search form names has to exist
+#    the references are qualified, table.column, which is what makes them
+#    findable without parsing D
+echo "Checking columns named by the search form ..."
+COLS=$(grep -o \
+  'metadata_and_text\.[a-z_]*\|doc_objects\.[a-z_]*\|topic_register\.[a-z_]*' \
+  "$CGI_SRC" | sort -u)
+if [ -z "$COLS" ]; then
+  fail "no column references found in $CGI_SRC (has the source changed shape?)"
+fi
+count=0
+for ref in $COLS; do
+  table=${ref%%.*}
+  column=${ref#*.}
+  [ -n "$column" ] || continue
+  count=$((count + 1))
+  found=$(sqlite3 "$DB" \
+    "select count(*) from pragma_table_info('$table') where name='$column';")
+  [ "$found" = "1" ] || fail "$table.$column named by the search form, absent from the db"
+done
+echo "  column references checked: $count"
+
+echo ""
+if [ "$FAILURES" -eq 0 ]; then
+  echo "PASS: spine's search database satisfies the search form's expectations"
+  exit 0
+else
+  echo "FAIL: $FAILURES mismatch(es)"
+  exit 1
+fi
+#+END_SRC
+
+** test search cgi
+
+#+HEADER: :tangle ../test/test-search-cgi.sh
+#+HEADER: :tangle-mode (identity #o755)
+#+HEADER: :shebang "#!/usr/bin/env sh"
+#+BEGIN_SRC shell
+# test-search-cgi.sh
+#
+# End to end check that the search cgi works against a database spine has
+# just built.
+#
+# The schema check (test-search-db-schema.sh) compares names. This one runs
+# the real binary against the real database, which is the only thing that
+# tests the query spine's schema actually has to satisfy: the joins, the
+# ordering, the types, and the fields the form fills in from a request.
+#
+# No web server is involved. The cgi binary is invoked as CGI, with the
+# request in the environment, which is what a web server would do.
+#
+# Usage:
+#   ./test/test-search-cgi.sh [/path/to/spine] [/path/to/spine_search]
+#
+# Environment:
+#   SpinePOD           pod directory to build the db from, relative to the
+#                      spine directory (default: data/pod, the bundled set)
+#   SpineSearchCGIbin  the built search cgi
+#                      (default: ../sisudoc-spine-search-cgi/result/cgi-bin/spine_search,
+#                      then .../cgi-bin/spine-search-ldc.cgi)
+#   SpineSearchDB      an existing search database to test against; when set,
+#                      nothing is built and this database is used as is
+#
+# The cgi is pointed at the database with SPINE_SEARCH_DB_PATH, so the binary
+# under test needs no rebuild and its compiled-in path is left alone.
+#
+# Exit codes:
+#   0  every request behaved
+#   1  a request did not (printed)
+#   2  set-up problem (no binary, no cgi, no samples)
+
+set -e
+
+SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
+SPINE_DIR="$(cd "$SCRIPT_DIR/.." && pwd)"
+
+SPINE_BIN="${1:-}"
+if [ -z "$SPINE_BIN" ]; then
+  if   [ -x "$SPINE_DIR/result/bin/spine" ]; then SPINE_BIN="$SPINE_DIR/result/bin/spine"
+  elif [ -x "$SPINE_DIR/bin/spine-ldc" ];    then SPINE_BIN="$SPINE_DIR/bin/spine-ldc"
+  elif [ -x "$SPINE_DIR/bin/spine" ];        then SPINE_BIN="$SPINE_DIR/bin/spine"
+  else
+    echo "ERROR: spine binary not found. Specify path as first argument." >&2
+    exit 2
+  fi
+fi
+
+CGI_BIN="${2:-${SpineSearchCGIbin:-}}"
+if [ -z "$CGI_BIN" ]; then
+  CGI_REPO="$SPINE_DIR/../sisudoc-spine-search-cgi"
+  for c in \
+    "$CGI_REPO/result/cgi-bin/spine_search" \
+    "$CGI_REPO/cgi-bin/spine-search-ldc.cgi" \
+    "$CGI_REPO/cgi-bin/spine_search"
+  do
+    [ -x "$c" ] && CGI_BIN="$c" && break
+  done
+fi
+if [ -z "$CGI_BIN" ] || [ ! -x "$CGI_BIN" ]; then
+  echo "ERROR: search cgi binary not found." >&2
+  echo "  build it: cd ../sisudoc-spine-search-cgi && nix build \".#spine-search-overlay-ldc\"" >&2
+  echo "  or set \$SpineSearchCGIbin / pass it as the second argument" >&2
+  exit 2
+fi
+
+OUT_DIR="$SCRIPT_DIR/current-search-cgi"
+DB_NAME="spine.search.db"
+
+if [ -n "$SpineSearchDB" ]; then
+  DB_DIR="$(cd "$(dirname "$SpineSearchDB")" && pwd)"
+  DB_NAME="$(basename "$SpineSearchDB")"
+  echo "spine binary: (not used, \$SpineSearchDB set)"
+else
+  SAMPLES_RAW="$SPINE_DIR/${SpinePOD:-data/pod}"
+  if [ -d "$SAMPLES_RAW" ]; then
+    SAMPLES_DIR="$(cd "$SAMPLES_RAW" && pwd)"
+  else
+    echo "ERROR: sample documents not found at $SAMPLES_RAW" >&2
+    exit 2
+  fi
+  DB_DIR="$OUT_DIR/sqlite-database"
+  echo "spine binary: $SPINE_BIN"
+  echo "samples:      $SAMPLES_DIR"
+  rm -rf "$OUT_DIR"
+  mkdir -p "$DB_DIR"
+  echo "Building search database ..."
+  "$SPINE_BIN" --sqlite-db-recreate \
+    --sqlite-db-path="$DB_DIR" --sqlite-db-filename="$DB_NAME" > /dev/null
+  if ! "$SPINE_BIN" --sqlite-update \
+    --sqlite-db-path="$DB_DIR" --sqlite-db-filename="$DB_NAME" \
+    --output="$OUT_DIR" "$SAMPLES_DIR"/* > "$OUT_DIR/build.log" 2>&1; then
+    echo "FAIL: spine --sqlite-update reported failure"
+    grep "ERROR SQLite\|run FAILED" "$OUT_DIR/build.log" | head -20
+    exit 1
+  fi
+fi
+echo "search cgi:   $CGI_BIN"
+echo "database:     $DB_DIR/$DB_NAME"
+
+mkdir -p "$OUT_DIR"
+RESP="$OUT_DIR/response.html"
+FAILURES=0
+
+# one GET request, as a web server would present it
+request() {
+  SPINE_SEARCH_DB_PATH="$DB_DIR" \
+  REQUEST_METHOD=GET \
+  SCRIPT_NAME=/spine_search \
+  SERVER_NAME=localhost \
+  SERVER_PORT=80 \
+  REQUEST_SCHEME=http \
+  GATEWAY_INTERFACE=CGI/1.1 \
+  SERVER_PROTOCOL=HTTP/1.1 \
+  QUERY_STRING="$1" \
+  "$CGI_BIN" > "$RESP" 2>"$OUT_DIR/response.err" || true
+}
+
+# a request that has to answer 200, name no sql error, and find documents
+expect_hits() {
+  _query="$1"
+  _what="$2"
+  request "$_query"
+  if ! head -1 "$RESP" | grep -q "200 OK"; then
+    echo "  FAIL $_what: no 200 status ($(head -1 "$RESP"))"
+    FAILURES=$((FAILURES + 1))
+    return
+  fi
+  if grep -qi "SqliteException\|no such column\|no such table" "$RESP" "$OUT_DIR/response.err"; then
+    echo "  FAIL $_what: sql error"
+    grep -i "SqliteException\|no such column\|no such table" "$RESP" "$OUT_DIR/response.err" | head -3
+    FAILURES=$((FAILURES + 1))
+    return
+  fi
+  _hits=$(grep -o 'class="publication"' "$RESP" | wc -l)
+  _hits=$((_hits / 2))
+  if [ "$_hits" -lt 1 ]; then
+    echo "  FAIL $_what: no results"
+    FAILURES=$((FAILURES + 1))
+    return
+  fi
+  echo "  ok   $_what ($_hits document(s))"
+}
+
+# a request that has to answer 200 and say the named thing
+expect_says() {
+  _query="$1"
+  _text="$2"
+  _what="$3"
+  request "$_query"
+  if ! head -1 "$RESP" | grep -q "200 OK"; then
+    echo "  FAIL $_what: no 200 status ($(head -1 "$RESP"))"
+    FAILURES=$((FAILURES + 1))
+    return
+  fi
+  if ! grep -q "$_text" "$RESP"; then
+    echo "  FAIL $_what: response does not say \"$_text\""
+    FAILURES=$((FAILURES + 1))
+    return
+  fi
+  echo "  ok   $_what"
+}
+
+# the queries below name a different column of the schema each, so between
+# them they exercise the whole of what the search form selects on. The values
+# searched for are read out of the database itself where sqlite3 is present,
+# so that the test says something whichever corpus it is pointed at; the
+# fallbacks are words the bundled data/pod documents contain. Every field is
+# matched with LIKE '%value%', so a single safe word out of a longer value
+# still matches the row it came from.
+PROBE_TEXT="document"
+PROBE_AUTHOR="Amissah"
+PROBE_TITLE="markup"
+PROBE_FN="sisu-spine-markup"
+PROBE_UID="markup"
+PROBE_LNG="en"
+if command -v sqlite3 > /dev/null 2>&1; then
+  # first word of at least four letters or digits, nothing else: the values in
+  # the database carry punctuation, markup and newlines, none of which belong
+  # in a query string
+  _word() {
+    printf '%s' "$1" | tr -c 'A-Za-z0-9' ' ' \
+      | awk '{for (i = 1; i <= NF; i++) if (length($i) >= 4) { print $i; exit } }'
+  }
+  _ask() { sqlite3 "$DB_DIR/$DB_NAME" "$1" 2>/dev/null | head -1 || true; }
+  _v=$(_word "$(_ask "select clean from doc_objects where length(clean) > 60 limit 1;")")
+  [ -n "$_v" ] && PROBE_TEXT="$_v"
+  _v=$(_word "$(_ask "select creator_author from metadata_and_text where creator_author != '' limit 1;")")
+  [ -n "$_v" ] && PROBE_AUTHOR="$_v"
+  _v=$(_word "$(_ask "select title from metadata_and_text where title != '' limit 1;")")
+  [ -n "$_v" ] && PROBE_TITLE="$_v"
+  _v=$(_word "$(_ask "select src_filename_base from metadata_and_text limit 1;")")
+  [ -n "$_v" ] && PROBE_FN="$_v"
+  _v=$(_word "$(_ask "select uid from metadata_and_text limit 1;")")
+  [ -n "$_v" ] && PROBE_UID="$_v"
+  _v=$(_ask "select language_document_char from metadata_and_text limit 1;")
+  case "$_v" in [a-z][a-z]) PROBE_LNG="$_v" ;; esac
+fi
+echo "Requests (text \"$PROBE_TEXT\", author \"$PROBE_AUTHOR\", title \"$PROBE_TITLE\"):"
+expect_hits "sf=$PROBE_TEXT&rt=idx&sml=1000"          "text, index results"
+expect_hits "sf=$PROBE_TEXT&rt=txt&sml=1000"          "text, text results"
+expect_hits "sf=$PROBE_TEXT%20AND%20$PROBE_TEXT&rt=idx&sml=1000" "text, boolean AND"
+expect_hits "sf=$PROBE_TEXT%20OR%20zzzznotfound&rt=idx&sml=1000" "text, boolean OR"
+expect_hits "au=$PROBE_AUTHOR&sf=$PROBE_TEXT&rt=idx&sml=1000" "creator_author"
+expect_hits "ti=$PROBE_TITLE&sf=$PROBE_TEXT&rt=idx&sml=1000"  "title"
+expect_hits "lng=$PROBE_LNG&sf=$PROBE_TEXT&rt=idx&sml=1000"   "language_document_char"
+expect_hits "fn=$PROBE_FN&sf=$PROBE_TEXT&rt=idx&sml=1000"     "src_filename_base"
+expect_hits "uid=$PROBE_UID&sf=$PROBE_TEXT&rt=idx&sml=1000"   "uid"
+
+# the failure modes a database change would show up as, each of which used to
+# be a blank page, a 500, or a silently created empty file
+echo "Refusals:"
+expect_says 'sf=document&rt=idx&selected_db=no_such_file.db' \
+  "search database not found" "a database that is not there is named, not created"
+if [ -e "$DB_DIR/no_such_file.db" ]; then
+  echo "  FAIL a missing database was created by the request"
+  FAILURES=$((FAILURES + 1))
+fi
+expect_hits "sf=$PROBE_TEXT&rt=idx&sml=1000&selected_db=../../../../etc/passwd" \
+  "a path in selected_db falls back to the configured database"
+
+
+# the schema version gate, end to end: a database stamped with a version the
+# search form does not expect has to be named as such, not searched. Needs
+# sqlite3 to do the stamping, so it is skipped when sqlite3 is absent.
+if command -v sqlite3 > /dev/null 2>&1; then
+  echo "Schema version gate:"
+  cp "$DB_DIR/$DB_NAME" "$DB_DIR/wrong_version.db"
+  sqlite3 "$DB_DIR/wrong_version.db" "pragma user_version = 99;"
+  expect_says 'sf=document&rt=idx&selected_db=wrong_version.db' \
+    "schema version" "a database of another schema version is refused, with the version named"
+  rm -f "$DB_DIR/wrong_version.db"
+else
+  echo "Schema version gate: skipped, sqlite3 not on PATH"
+fi
+echo ""
+if [ "$FAILURES" -eq 0 ]; then
+  echo "PASS: the search cgi works against this database"
+  exit 0
+else
+  echo "FAIL: $FAILURES request(s) misbehaved"
+  exit 1
+fi
+#+END_SRC
diff --git a/src/sisudoc/outputs/io_out/sqlite.d b/src/sisudoc/outputs/io_out/sqlite.d
index f96846d..1e1970e 100644
--- a/src/sisudoc/outputs/io_out/sqlite.d
+++ b/src/sisudoc/outputs/io_out/sqlite.d
@@ -64,6 +64,49 @@ static auto rgx = RgxO();
 static auto rgx_xhtml = RgxXHTML();
 static auto mkup = InlineMarkup();
 long _metadata_tid_lastrowid;
+/+ ↓ sqlite db schema version, stamped into the db as PRAGMA user_version +/
+/+   bump whenever the table or index definitions in this module change, and +/
+/+   bump the matching expectation in sisudoc-spine-search-cgi +/
+enum sqlite_db_schema_version = 1;
+/+ ↓ tally of failed sqlite statements, shared: sqlite output may run in parallel +/
+private shared int _sqlite_failure_tally = 0;
+/+ ↓ schema version mismatch is reported once per run, not once per document +/
+private shared int _sqlite_schema_mismatch_reported = 0;
+/+ ↓ record a failed sqlite statement, reported on stderr, tallied for exit status +/
+void sqliteFailureRecord(string note, string msg) {
+  import core.atomic : atomicOp;
+  atomicOp!"+="(_sqlite_failure_tally, 1);
+  stderr.writeln("ERROR SQLite (", note, "): ", msg);
+}
+/+ ↓ count of failed sqlite statements this run, zero when all went well +/
+int sqliteFailureTally() {
+  import core.atomic : atomicLoad;
+  return atomicLoad(_sqlite_failure_tally);
+}
+/+ ↓ compare the schema version stamped in an existing db with this spine's +/
+void sqliteSchemaVersionCheck(Db)(Db db, string sqlite_file) {
+  import core.atomic : atomicOp;
+  int _found;
+  try {
+    _found = db.execute("PRAGMA user_version;").oneValue!int;
+  } catch (Exception) {
+    return;
+  }
+  if (_found == sqlite_db_schema_version
+  || _found == 0) { /+ ↓ zero: db predates the stamp, or has no tables yet +/
+    return;
+  }
+  if (atomicOp!"+="(_sqlite_schema_mismatch_reported, 1) == 1) {
+    stderr.writeln("ERROR SQLite : db schema version mismatch: ", sqlite_file);
+    stderr.writeln("       db holds schema version ", _found,
+      ", this spine writes schema version ", sqlite_db_schema_version);
+    stderr.writeln("       recreate the db, e.g.: spine --sqlite-db-recreate",
+      " --sqlite-db-path=... --sqlite-db-filename=...");
+    sqliteFailureRecord("schema version",
+      "db schema version " ~ _found.to!string
+      ~ " != spine schema version " ~ sqlite_db_schema_version.to!string);
+  }
+}
 template SQLiteHubBuildTablesAndPopulate() {
   void SQLiteHubBuildTablesAndPopulate(D)(D doc) {
     auto pth_sqlite = spinePathsSQLite!()(doc.matters.sqlite.filename, doc.matters.sqlite.path);
@@ -129,6 +172,7 @@ template SQLiteHubBuildTablesAndPopulate() {
     }
     try {
       auto db = Database(pth_sqlite.sqlite_file);
+      sqliteSchemaVersionCheck(db, pth_sqlite.sqlite_file);
       SQLiteDbStatementComposite!()(db, doc);
     }
     catch (FileException e) {
@@ -172,6 +216,7 @@ template SQLiteHubDiscreteBuildTablesAndPopulate() {
       }
     }
     auto db = Database(pth_sqlite.sqlite_file(doc.matters.src.filename));
+    sqliteSchemaVersionCheck(db, pth_sqlite.sqlite_file(doc.matters.src.filename));
     template SQLiteDiscreteDbStatementComposite() {
       void SQLiteDiscreteDbStatementComposite(Db,D)(
         Db   db,
@@ -235,9 +280,9 @@ template SQLiteDbRun() {
         "\nCOMMIT TRANSACTION;\n"
       );
     } catch (ErrnoException ex) {
-      writeln("ERROR SQLite : ", ex);
+      sqliteFailureRecord(note, ex.msg);
     } catch (Exception ex) {
-      writeln("ERROR SQLite : ", ex);
+      sqliteFailureRecord(note, ex.msg);
     }
     { /+ debug +/
       if (opt_action.debug_do_sqlite) {
@@ -1142,7 +1187,10 @@ template SQLiteTablesReCreate() {
       CREATE INDEX IF NOT EXISTS idx_language     ON metadata_and_text(language_document_char);
       CREATE INDEX IF NOT EXISTS idx_topics       ON metadata_and_text(classify_topic_register);
       CREATE INDEX IF NOT EXISTS idx_topic_list   ON topic_register(topic_register);
-    ┃",);
+      PRAGMA user_version = %s;
+    ┃",
+      sqlite_db_schema_version,
+    );
     return _sql_instruct;
   }
 }
@@ -1654,7 +1702,10 @@ template SQLiteTablesCreate() {
           CREATE INDEX IF NOT EXISTS idx_language     ON metadata_and_text(language_document_char);
           CREATE INDEX IF NOT EXISTS idx_topics       ON metadata_and_text(classify_topic_register);
           CREATE INDEX IF NOT EXISTS idx_topic_list   ON topic_register(topic_register);
-        ┃",);
+          PRAGMA user_version = %s;
+        ┃",
+          sqlite_db_schema_version,
+        );
         return _sql_instruct;
       }
     }
diff --git a/src/sisudoc/spine.d b/src/sisudoc/spine.d
index 568a102..2862303 100644
--- a/src/sisudoc/spine.d
+++ b/src/sisudoc/spine.d
@@ -1637,4 +1637,16 @@ string program_name = "spine";
   foreach (ref _dlr; _url_downloads) {
     cleanupDownload(_dlr);
   }
+  /+ ↓ sqlite statements that failed are fatal to the run, report and exit non-zero +/
+  {
+    import sisudoc.outputs.io_out.sqlite : sqliteFailureTally;
+    if (sqliteFailureTally() > 0) {
+      stderr.writefln(
+        "~ run FAILED ~ %s sqlite statement(s) failed, db not written as expected",
+        sqliteFailureTally(),
+      );
+      import core.stdc.stdlib : exit;
+      exit(1);
+    }
+  }
 }
diff --git a/test/test-search-cgi.sh b/test/test-search-cgi.sh
new file mode 100755
index 0000000..7541a92
--- /dev/null
+++ b/test/test-search-cgi.sh
@@ -0,0 +1,245 @@
+#!/usr/bin/env sh
+# test-search-cgi.sh
+#
+# End to end check that the search cgi works against a database spine has
+# just built.
+#
+# The schema check (test-search-db-schema.sh) compares names. This one runs
+# the real binary against the real database, which is the only thing that
+# tests the query spine's schema actually has to satisfy: the joins, the
+# ordering, the types, and the fields the form fills in from a request.
+#
+# No web server is involved. The cgi binary is invoked as CGI, with the
+# request in the environment, which is what a web server would do.
+#
+# Usage:
+#   ./test/test-search-cgi.sh [/path/to/spine] [/path/to/spine_search]
+#
+# Environment:
+#   SpinePOD           pod directory to build the db from, relative to the
+#                      spine directory (default: data/pod, the bundled set)
+#   SpineSearchCGIbin  the built search cgi
+#                      (default: ../sisudoc-spine-search-cgi/result/cgi-bin/spine_search,
+#                      then .../cgi-bin/spine-search-ldc.cgi)
+#   SpineSearchDB      an existing search database to test against; when set,
+#                      nothing is built and this database is used as is
+#
+# The cgi is pointed at the database with SPINE_SEARCH_DB_PATH, so the binary
+# under test needs no rebuild and its compiled-in path is left alone.
+#
+# Exit codes:
+#   0  every request behaved
+#   1  a request did not (printed)
+#   2  set-up problem (no binary, no cgi, no samples)
+
+set -e
+
+SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
+SPINE_DIR="$(cd "$SCRIPT_DIR/.." && pwd)"
+
+SPINE_BIN="${1:-}"
+if [ -z "$SPINE_BIN" ]; then
+  if   [ -x "$SPINE_DIR/result/bin/spine" ]; then SPINE_BIN="$SPINE_DIR/result/bin/spine"
+  elif [ -x "$SPINE_DIR/bin/spine-ldc" ];    then SPINE_BIN="$SPINE_DIR/bin/spine-ldc"
+  elif [ -x "$SPINE_DIR/bin/spine" ];        then SPINE_BIN="$SPINE_DIR/bin/spine"
+  else
+    echo "ERROR: spine binary not found. Specify path as first argument." >&2
+    exit 2
+  fi
+fi
+
+CGI_BIN="${2:-${SpineSearchCGIbin:-}}"
+if [ -z "$CGI_BIN" ]; then
+  CGI_REPO="$SPINE_DIR/../sisudoc-spine-search-cgi"
+  for c in \
+    "$CGI_REPO/result/cgi-bin/spine_search" \
+    "$CGI_REPO/cgi-bin/spine-search-ldc.cgi" \
+    "$CGI_REPO/cgi-bin/spine_search"
+  do
+    [ -x "$c" ] && CGI_BIN="$c" && break
+  done
+fi
+if [ -z "$CGI_BIN" ] || [ ! -x "$CGI_BIN" ]; then
+  echo "ERROR: search cgi binary not found." >&2
+  echo "  build it: cd ../sisudoc-spine-search-cgi && nix build \".#spine-search-overlay-ldc\"" >&2
+  echo "  or set \$SpineSearchCGIbin / pass it as the second argument" >&2
+  exit 2
+fi
+
+OUT_DIR="$SCRIPT_DIR/current-search-cgi"
+DB_NAME="spine.search.db"
+
+if [ -n "$SpineSearchDB" ]; then
+  DB_DIR="$(cd "$(dirname "$SpineSearchDB")" && pwd)"
+  DB_NAME="$(basename "$SpineSearchDB")"
+  echo "spine binary: (not used, \$SpineSearchDB set)"
+else
+  SAMPLES_RAW="$SPINE_DIR/${SpinePOD:-data/pod}"
+  if [ -d "$SAMPLES_RAW" ]; then
+    SAMPLES_DIR="$(cd "$SAMPLES_RAW" && pwd)"
+  else
+    echo "ERROR: sample documents not found at $SAMPLES_RAW" >&2
+    exit 2
+  fi
+  DB_DIR="$OUT_DIR/sqlite-database"
+  echo "spine binary: $SPINE_BIN"
+  echo "samples:      $SAMPLES_DIR"
+  rm -rf "$OUT_DIR"
+  mkdir -p "$DB_DIR"
+  echo "Building search database ..."
+  "$SPINE_BIN" --sqlite-db-recreate \
+    --sqlite-db-path="$DB_DIR" --sqlite-db-filename="$DB_NAME" > /dev/null
+  if ! "$SPINE_BIN" --sqlite-update \
+    --sqlite-db-path="$DB_DIR" --sqlite-db-filename="$DB_NAME" \
+    --output="$OUT_DIR" "$SAMPLES_DIR"/* > "$OUT_DIR/build.log" 2>&1; then
+    echo "FAIL: spine --sqlite-update reported failure"
+    grep "ERROR SQLite\|run FAILED" "$OUT_DIR/build.log" | head -20
+    exit 1
+  fi
+fi
+echo "search cgi:   $CGI_BIN"
+echo "database:     $DB_DIR/$DB_NAME"
+
+mkdir -p "$OUT_DIR"
+RESP="$OUT_DIR/response.html"
+FAILURES=0
+
+# one GET request, as a web server would present it
+request() {
+  SPINE_SEARCH_DB_PATH="$DB_DIR" \
+  REQUEST_METHOD=GET \
+  SCRIPT_NAME=/spine_search \
+  SERVER_NAME=localhost \
+  SERVER_PORT=80 \
+  REQUEST_SCHEME=http \
+  GATEWAY_INTERFACE=CGI/1.1 \
+  SERVER_PROTOCOL=HTTP/1.1 \
+  QUERY_STRING="$1" \
+  "$CGI_BIN" > "$RESP" 2>"$OUT_DIR/response.err" || true
+}
+
+# a request that has to answer 200, name no sql error, and find documents
+expect_hits() {
+  _query="$1"
+  _what="$2"
+  request "$_query"
+  if ! head -1 "$RESP" | grep -q "200 OK"; then
+    echo "  FAIL $_what: no 200 status ($(head -1 "$RESP"))"
+    FAILURES=$((FAILURES + 1))
+    return
+  fi
+  if grep -qi "SqliteException\|no such column\|no such table" "$RESP" "$OUT_DIR/response.err"; then
+    echo "  FAIL $_what: sql error"
+    grep -i "SqliteException\|no such column\|no such table" "$RESP" "$OUT_DIR/response.err" | head -3
+    FAILURES=$((FAILURES + 1))
+    return
+  fi
+  _hits=$(grep -o 'class="publication"' "$RESP" | wc -l)
+  _hits=$((_hits / 2))
+  if [ "$_hits" -lt 1 ]; then
+    echo "  FAIL $_what: no results"
+    FAILURES=$((FAILURES + 1))
+    return
+  fi
+  echo "  ok   $_what ($_hits document(s))"
+}
+
+# a request that has to answer 200 and say the named thing
+expect_says() {
+  _query="$1"
+  _text="$2"
+  _what="$3"
+  request "$_query"
+  if ! head -1 "$RESP" | grep -q "200 OK"; then
+    echo "  FAIL $_what: no 200 status ($(head -1 "$RESP"))"
+    FAILURES=$((FAILURES + 1))
+    return
+  fi
+  if ! grep -q "$_text" "$RESP"; then
+    echo "  FAIL $_what: response does not say \"$_text\""
+    FAILURES=$((FAILURES + 1))
+    return
+  fi
+  echo "  ok   $_what"
+}
+
+# the queries below name a different column of the schema each, so between
+# them they exercise the whole of what the search form selects on. The values
+# searched for are read out of the database itself where sqlite3 is present,
+# so that the test says something whichever corpus it is pointed at; the
+# fallbacks are words the bundled data/pod documents contain. Every field is
+# matched with LIKE '%value%', so a single safe word out of a longer value
+# still matches the row it came from.
+PROBE_TEXT="document"
+PROBE_AUTHOR="Amissah"
+PROBE_TITLE="markup"
+PROBE_FN="sisu-spine-markup"
+PROBE_UID="markup"
+PROBE_LNG="en"
+if command -v sqlite3 > /dev/null 2>&1; then
+  # first word of at least four letters or digits, nothing else: the values in
+  # the database carry punctuation, markup and newlines, none of which belong
+  # in a query string
+  _word() {
+    printf '%s' "$1" | tr -c 'A-Za-z0-9' ' ' \
+      | awk '{for (i = 1; i <= NF; i++) if (length($i) >= 4) { print $i; exit } }'
+  }
+  _ask() { sqlite3 "$DB_DIR/$DB_NAME" "$1" 2>/dev/null | head -1 || true; }
+  _v=$(_word "$(_ask "select clean from doc_objects where length(clean) > 60 limit 1;")")
+  [ -n "$_v" ] && PROBE_TEXT="$_v"
+  _v=$(_word "$(_ask "select creator_author from metadata_and_text where creator_author != '' limit 1;")")
+  [ -n "$_v" ] && PROBE_AUTHOR="$_v"
+  _v=$(_word "$(_ask "select title from metadata_and_text where title != '' limit 1;")")
+  [ -n "$_v" ] && PROBE_TITLE="$_v"
+  _v=$(_word "$(_ask "select src_filename_base from metadata_and_text limit 1;")")
+  [ -n "$_v" ] && PROBE_FN="$_v"
+  _v=$(_word "$(_ask "select uid from metadata_and_text limit 1;")")
+  [ -n "$_v" ] && PROBE_UID="$_v"
+  _v=$(_ask "select language_document_char from metadata_and_text limit 1;")
+  case "$_v" in [a-z][a-z]) PROBE_LNG="$_v" ;; esac
+fi
+echo "Requests (text \"$PROBE_TEXT\", author \"$PROBE_AUTHOR\", title \"$PROBE_TITLE\"):"
+expect_hits "sf=$PROBE_TEXT&rt=idx&sml=1000"          "text, index results"
+expect_hits "sf=$PROBE_TEXT&rt=txt&sml=1000"          "text, text results"
+expect_hits "sf=$PROBE_TEXT%20AND%20$PROBE_TEXT&rt=idx&sml=1000" "text, boolean AND"
+expect_hits "sf=$PROBE_TEXT%20OR%20zzzznotfound&rt=idx&sml=1000" "text, boolean OR"
+expect_hits "au=$PROBE_AUTHOR&sf=$PROBE_TEXT&rt=idx&sml=1000" "creator_author"
+expect_hits "ti=$PROBE_TITLE&sf=$PROBE_TEXT&rt=idx&sml=1000"  "title"
+expect_hits "lng=$PROBE_LNG&sf=$PROBE_TEXT&rt=idx&sml=1000"   "language_document_char"
+expect_hits "fn=$PROBE_FN&sf=$PROBE_TEXT&rt=idx&sml=1000"     "src_filename_base"
+expect_hits "uid=$PROBE_UID&sf=$PROBE_TEXT&rt=idx&sml=1000"   "uid"
+
+# the failure modes a database change would show up as, each of which used to
+# be a blank page, a 500, or a silently created empty file
+echo "Refusals:"
+expect_says 'sf=document&rt=idx&selected_db=no_such_file.db' \
+  "search database not found" "a database that is not there is named, not created"
+if [ -e "$DB_DIR/no_such_file.db" ]; then
+  echo "  FAIL a missing database was created by the request"
+  FAILURES=$((FAILURES + 1))
+fi
+expect_hits "sf=$PROBE_TEXT&rt=idx&sml=1000&selected_db=../../../../etc/passwd" \
+  "a path in selected_db falls back to the configured database"
+
+
+# the schema version gate, end to end: a database stamped with a version the
+# search form does not expect has to be named as such, not searched. Needs
+# sqlite3 to do the stamping, so it is skipped when sqlite3 is absent.
+if command -v sqlite3 > /dev/null 2>&1; then
+  echo "Schema version gate:"
+  cp "$DB_DIR/$DB_NAME" "$DB_DIR/wrong_version.db"
+  sqlite3 "$DB_DIR/wrong_version.db" "pragma user_version = 99;"
+  expect_says 'sf=document&rt=idx&selected_db=wrong_version.db' \
+    "schema version" "a database of another schema version is refused, with the version named"
+  rm -f "$DB_DIR/wrong_version.db"
+else
+  echo "Schema version gate: skipped, sqlite3 not on PATH"
+fi
+echo ""
+if [ "$FAILURES" -eq 0 ]; then
+  echo "PASS: the search cgi works against this database"
+  exit 0
+else
+  echo "FAIL: $FAILURES request(s) misbehaved"
+  exit 1
+fi
diff --git a/test/test-search-db-schema.sh b/test/test-search-db-schema.sh
new file mode 100755
index 0000000..8d28b76
--- /dev/null
+++ b/test/test-search-db-schema.sh
@@ -0,0 +1,178 @@
+#!/usr/bin/env sh
+# test-search-db-schema.sh
+#
+# Schema contract between spine's --sqlite-update output and the search form
+# in sisudoc-spine-search-cgi.
+#
+# Spine holds the only copy of the DDL; the search form holds no DDL at all,
+# only a small set of column references. Nothing connects the two, so a
+# column renamed in spine is found by whoever runs a search next. This is the
+# check that finds it earlier: build a search database, then read the column
+# references out of the search form's source and require every one of them to
+# exist in what was built.
+#
+# It also requires the three-way agreement on schema version: the version
+# spine declares, the version stamped into the database it built, and the
+# version the search form expects.
+#
+# Usage:
+#   ./test/test-search-db-schema.sh [/path/to/spine]
+#
+# Environment:
+#   SpinePOD             pod directory to build the db from, relative to the
+#                        spine directory (default: data/pod, the bundled set)
+#   SpineSearchCGIsrc    path to the search cgi source file or its repository
+#                        (default: ../sisudoc-spine-search-cgi)
+#
+# sqlite3 is needed. If it is not on PATH the script re-runs itself inside a
+# nix shell that has it.
+#
+# Exit codes:
+#   0  spine's database satisfies every expectation the search form has
+#   1  a mismatch was found (printed)
+#   2  set-up problem (no binary, no cgi source, no sqlite3, no samples)
+
+set -e
+
+SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
+SPINE_DIR="$(cd "$SCRIPT_DIR/.." && pwd)"
+
+if ! command -v sqlite3 > /dev/null 2>&1; then
+  if command -v nix > /dev/null 2>&1; then
+    echo "sqlite3 not on PATH, re-running inside nix shell \"nixpkgs#sqlite\""
+    exec nix shell "nixpkgs#sqlite" -c "$0" "$@"
+  fi
+  echo "ERROR: sqlite3 not on PATH and nix not available." >&2
+  exit 2
+fi
+
+SPINE_BIN="${1:-}"
+if [ -z "$SPINE_BIN" ]; then
+  if   [ -x "$SPINE_DIR/result/bin/spine" ]; then SPINE_BIN="$SPINE_DIR/result/bin/spine"
+  elif [ -x "$SPINE_DIR/bin/spine-ldc" ];    then SPINE_BIN="$SPINE_DIR/bin/spine-ldc"
+  elif [ -x "$SPINE_DIR/bin/spine" ];        then SPINE_BIN="$SPINE_DIR/bin/spine"
+  else
+    echo "ERROR: spine binary not found. Specify path as argument." >&2
+    exit 2
+  fi
+fi
+
+# the search form's source, the other half of the contract
+CGI_SRC="${SpineSearchCGIsrc:-$SPINE_DIR/../sisudoc-spine-search-cgi}"
+if [ -d "$CGI_SRC" ]; then
+  CGI_SRC="$CGI_SRC/src/sisudoc/spine_search.d"
+fi
+if [ ! -f "$CGI_SRC" ]; then
+  echo "ERROR: search cgi source not found at $CGI_SRC" >&2
+  echo "  set \$SpineSearchCGIsrc to the sisudoc-spine-search-cgi checkout" >&2
+  exit 2
+fi
+
+SAMPLES_RAW="$SPINE_DIR/${SpinePOD:-data/pod}"
+if [ -d "$SAMPLES_RAW" ]; then
+  SAMPLES_DIR="$(cd "$SAMPLES_RAW" && pwd)"
+else
+  echo "ERROR: sample documents not found at $SAMPLES_RAW" >&2
+  exit 2
+fi
+
+OUT_DIR="$SCRIPT_DIR/current-search-db"
+DB_DIR="$OUT_DIR/sqlite-database"
+DB_NAME="spine.search.db"
+DB="$DB_DIR/$DB_NAME"
+
+echo "spine binary: $SPINE_BIN"
+echo "search cgi:   $CGI_SRC"
+echo "samples:      $SAMPLES_DIR"
+
+rm -rf "$OUT_DIR"
+mkdir -p "$DB_DIR"
+
+echo "Building search database ..."
+"$SPINE_BIN" --sqlite-db-recreate \
+  --sqlite-db-path="$DB_DIR" --sqlite-db-filename="$DB_NAME" > /dev/null
+# the update has to be able to fail the test: spine exits non-zero when any
+# sqlite statement failed, and that is exactly the case this test is here for
+if ! "$SPINE_BIN" --sqlite-update \
+  --sqlite-db-path="$DB_DIR" --sqlite-db-filename="$DB_NAME" \
+  --output="$OUT_DIR" "$SAMPLES_DIR"/* > "$OUT_DIR/build.log" 2>&1; then
+  echo "FAIL: spine --sqlite-update reported failure"
+  grep "ERROR SQLite\|run FAILED" "$OUT_DIR/build.log" | head -20
+  exit 1
+fi
+
+FAILURES=0
+fail() { echo "  MISMATCH $*"; FAILURES=$((FAILURES + 1)); }
+
+# ── the database was built at all
+if [ ! -f "$DB" ]; then
+  echo "FAIL: no database at $DB"
+  exit 1
+fi
+
+# ── the tables the search form joins
+echo "Checking tables ..."
+for t in metadata_and_text doc_objects topic_register; do
+  found=$(sqlite3 "$DB" \
+    "select count(*) from sqlite_master where type='table' and name='$t';")
+  [ "$found" = "1" ] || fail "table $t missing"
+done
+
+# ── the tables are populated, an empty db satisfies a column check trivially
+docs=$(sqlite3 "$DB" "select count(*) from metadata_and_text;" 2>/dev/null || echo 0)
+objs=$(sqlite3 "$DB" "select count(*) from doc_objects;" 2>/dev/null || echo 0)
+echo "  documents: $docs, objects: $objs"
+[ "$docs" -gt 0 ] || fail "metadata_and_text is empty"
+[ "$objs" -gt 0 ] || fail "doc_objects is empty"
+
+# ── schema version: spine's declaration, the stamp in the db, the search
+#    form's expectation; all three have to agree
+echo "Checking schema version ..."
+SPINE_SQL_SRC="$SPINE_DIR/src/sisudoc/outputs/io_out/sqlite.d"
+declared=$(sed -n 's/^enum sqlite_db_schema_version *= *\([0-9][0-9]*\);.*/\1/p' \
+  "$SPINE_SQL_SRC" | head -1)
+stamped=$(sqlite3 "$DB" "pragma user_version;")
+expected=$(sed -n 's/^enum sqlite_db_schema_version_expected *= *\([0-9][0-9]*\);.*/\1/p' \
+  "$CGI_SRC" | head -1)
+echo "  spine declares: ${declared:-unset}, db stamped: ${stamped:-unset}, cgi expects: ${expected:-unset}"
+if [ -z "$declared" ]; then
+  fail "spine declares no sqlite_db_schema_version in $SPINE_SQL_SRC"
+elif [ "$stamped" != "$declared" ]; then
+  fail "db stamped user_version $stamped, spine declares $declared"
+fi
+if [ -z "$expected" ]; then
+  fail "search form declares no sqlite_db_schema_version_expected"
+elif [ -n "$declared" ] && [ "$expected" != "$declared" ]; then
+  fail "search form expects schema version $expected, spine writes $declared"
+fi
+
+# ── every column the search form names has to exist
+#    the references are qualified, table.column, which is what makes them
+#    findable without parsing D
+echo "Checking columns named by the search form ..."
+COLS=$(grep -o \
+  'metadata_and_text\.[a-z_]*\|doc_objects\.[a-z_]*\|topic_register\.[a-z_]*' \
+  "$CGI_SRC" | sort -u)
+if [ -z "$COLS" ]; then
+  fail "no column references found in $CGI_SRC (has the source changed shape?)"
+fi
+count=0
+for ref in $COLS; do
+  table=${ref%%.*}
+  column=${ref#*.}
+  [ -n "$column" ] || continue
+  count=$((count + 1))
+  found=$(sqlite3 "$DB" \
+    "select count(*) from pragma_table_info('$table') where name='$column';")
+  [ "$found" = "1" ] || fail "$table.$column named by the search form, absent from the db"
+done
+echo "  column references checked: $count"
+
+echo ""
+if [ "$FAILURES" -eq 0 ]; then
+  echo "PASS: spine's search database satisfies the search form's expectations"
+  exit 0
+else
+  echo "FAIL: $FAILURES mismatch(es)"
+  exit 1
+fi