What spike.py established, and what it changes about
the plan.
Reproduced on macOS arm64 (Metal), SQLite CLI 3.53.3, sqlite-ai 1.0.7,
sqlite-vector 1.0.0, sqlite-sync 1.1.0, Qwen3-1.7B-Q4_K_M +
all-MiniLM-L6-v2-q8_0. Re-run it with make spike.
Verdict: every risk in the plan's §7 table is cleared except the live sync round trip, which needs a SQLite Cloud project. Phase 1 can start.
Five things in the plan are wrong as written: the combined trigger (#2), the single connection (#3), the model and the three-call enrichment (#4), the normalizer (#4), and the schema (#7). Two risks the plan did not list turned out to matter most: the teardown crash in #1, and the quit-time abort in #8.
This is the one that matters, and it is a sqlite-ai bug, not a demo problem. EdgeTable must use several connections (finding 3), so it walks straight into it.
a, b = connect(), connect() # both load ai.dylib, both load a model
b.execute("SELECT llm_context_free()")
b.execute("SELECT llm_model_free()")
b.close() # b is gone
a.execute("SELECT llm_context_free()") # <-- SIGSEGVStack:
sqlite3LockAndPrepare
sqlite_db_write
ai_logger
llama_log_internal
llama_context::~llama_context()
llama_free
Root cause. sqlite3_ai_init() calls llama_log_set(ai_logger, ctx)
(src/sqlite-ai.c:3806), where ctx is that connection's ai_context.
llama_log_set is process-global, so whichever connection loaded the
extension last owns the log callback for every other connection. Closing it runs
ai_destroy → ai_free, and the global callback is left pointing at freed
memory. The next log line llama.cpp emits — and llama_context's destructor
emits one — dereferences it.
The if (ai->db == NULL) return; guard at src/sqlite-ai.c:896 cannot help:
ai itself is the dangling pointer, so the guard reads freed memory to decide.
This is also the root cause of DEMO-FINDINGS.md
finding 7 (ai_log ... API misuse printed five times at teardown). Same
mechanism; that report caught the mild symptom, this is the sharp edge. Finding 7
can be reclassified from cosmetic to memory-unsafe.
Workaround (used by shutdown()): close model-holding connections oldest
first, so the youngest — the one owning the callback — is closed last. Verified
both ways: oldest-first is clean, youngest-first segfaults every time.
That is a fragile contract to hand an application. Worth fixing upstream, e.g.
by giving ai_logger a registry keyed by sqlite3*, re-pointing the callback on
connection close, or calling llama_log_set(NULL, NULL) from ai_destroy when
the closing connection is the registered owner.
The plan's §4 trigger calls llm_text_generate() and llm_embed_generate() in
one UPDATE. It cannot: one model per connection, and llm_model_load() frees
whatever that connection already had.
The failure has no error attached to it. After loading the embedding model over
the generation model, llm_text_generate() returns an empty string:
textgen ok: 13 -- chars returned
embed ok: 1536 -- bytes, 384 x FLOAT32
textgen after embed load: 0 -- no error, no NULL, just ''
An empty string flows into json_extract() as NULL and lands in the grid as
three blank AI columns. In a demo that reads as "the AI didn't work". Returning
an error, or at least NULL, would be far kinder.
vector_init being per-connection is documented. cloudsync_init being
per-connection is not, and it is the sharper trap, because the failure looks
like a corrupt database rather than a missing setup call:
sqlite3.OperationalError: Unable to retrieve table name feedback in cloudsync_update.
That is what a second connection gets on every write if it did not call
cloudsync_init itself — even though the table is fully initialised in the
database file and the API describes the configuration as "stored in the database
and automatically loaded with the extension". It is loaded per connection, at
extension-load time, so a connection opened before cloudsync_init ran never
sees the table.
Both calls are idempotent, so the rule for Phase 1 is simply: every connection
runs cloudsync_init and vector_init for every table it touches, always.
The resulting split:
| Connection | Model | Owns |
|---|---|---|
GEN |
Qwen3-1.7B (see #4) | inserts, the enrichment trigger, sync |
EMB |
all-MiniLM-L6-v2 | embedding backfill, vector_full_scan |
Embedding therefore cannot be a trigger. An AFTER INSERT trigger runs on
whichever connection did the insert — GEN, which holds the generation model.
The plan's single combined trigger becomes: a trigger for the text fields, and a
worker pass on EMB for the vectors. The "AI fields are a trigger" pitch survives
intact; the embedding is an index, and nobody claims indexes are triggers.
The plan called for three llm_text_generate() calls per row. One
grammar-constrained call returning {summary, sentiment, category} is ~3× faster
and strictly better: the label and the summary come from the same forward pass, so
they cannot disagree.
The plan's model choice was wrong, though. Qwen3-0.6B produces valid, in-enum
labels that are frequently the wrong label — and the pills are on screen for the
whole demo. Scored against hand labels for the 24 seed rows
(EXPECTED_CATEGORY in spike.py, which is now the regression test for any prompt
or model change):
| Model | Prompt | Category accuracy | Latency |
|---|---|---|---|
| Qwen3-0.6B | plain enum list | 14/24 — 58% | 1.02 s/row |
| Qwen3-0.6B | per-label definitions | 15/24 — 62% | 1.08 s/row |
| Qwen3-0.6B | label-first JSON order | 12/24 — 50% | 0.97 s/row |
| Qwen3-0.6B | ordered precedence rules | 14/24 — 58% | 0.82 s/row |
| Qwen3-1.7B | plain enum list | 15/24 — 62% | 0.70 s/row |
| Qwen3-1.7B | per-label definitions | 19/24 — 79% | 0.80 s/row |
| Qwen3-1.7B | ordered precedence rules | 22/24 — 92% | 0.92 s/row |
Three things worth taking from that table:
- At 0.6B the prompt is not the bottleneck — the model is. Four prompt
variants all land between 50% and 62%. The precedence-rule prompt that takes
1.7B to 92% actively hurts 0.6B (58%), which collapses to labelling almost
everything
feature_request. It cannot follow ordered rules. - The bigger model is not slower. 1.7B measures 0.92 s/row against 0.6B's 1.02 s/row, because it writes shorter, better-disciplined summaries and token count dominates. The plan's warning that 1.7B "roughly triples latency" is wrong. The real cost is bundle size: 1.1 GB vs 300 MB.
- Model and prompt only pay off together — 1.7B on the plain prompt is 62%, the same as 0.6B. Both changes are needed for 92%.
The two remaining misses are genuinely arguable ("held back by the mobile app" →
billing; "changing a filter reloads the page" → bugs), not embarrassing.
Full run at the chosen configuration:
24 rows in 22.1s -- mean 0.92s/row, median 0.93s, max 1.32s
valid JSON 24/24 sentiment in enum 24/24 category in enum 24/24
category CORRECT (vs hand labels) 22/24 (92%)
Comfortably under the plan's 2 s/row budget, so the trigger stays on the insert path for single rows. Bulk paste still wants the Rust worker — not for correctness but because progressive column fill looks better.
The Rust-side normalizer in the plan is unnecessary, but not for the reason it assumed. GBNF makes off-enum output unrepresentable (48/48 in vocabulary), so there is nothing to normalise. It does nothing about off-target labels, which is a model-and-prompt problem and is what the accuracy table above is for. Do not let "the grammar guarantees valid output" become "the grammar guarantees correct output" in the talk track.
Two notes on the model:
- Qwen3 is a reasoning model and would normally open with a
<think>block. The grammar forbids it — the first token must be{. No/no_thinkneeded. - Summaries improved with the same change. At 0.6B the model invented detail ("29% of their billing balance" for a €29 charge). At 1.7B, with the instruction to restate only what the message says, summaries are short and faithful ("Request for dark theme", "Setup took ten minutes.").
Proved offline with cloudsync_payload_encode / cloudsync_payload_apply between
two database files — no server, no credentials, and it is a better test than a
cloud round trip because it isolates the semantics from the network.
Sync-applied rows do fire ordinary AFTER INSERT triggers. The plan hedged
("guarded either way"); the answer is that the guard is load-bearing. Both
branches verified:
| Row arriving on device B | summary |
Trigger | Result |
|---|---|---|---|
| Enriched on A | not null | does not fire | 24 rows applied in 0.01 s, zero inference |
| Raw, from a device with no model | null | fires | enriched locally in 0.85 s |
The second row is a bonus the plan did not claim: a device that cannot run a model — the Phase 4 phone — can send plain text and have a laptop enrich it on arrival, with the values syncing back. Worth a sentence in the talk track.
Query "complaints about billing" against the 24-row seed set:
0.487 * We cancelled the plan in March and the charge is still hitting our card
0.694 * Our accountant needs a proper VAT invoice for Q2
0.695 * My card was debited for the annual plan four days before the trial ended
0.712 * You have taken 29 EUR off my card twice this month
All six top hits are category = billing. None contains the word "billing".
WHERE body LIKE '%billing%' returns 0 rows. The contrast the demo is built
on is real, and it holds because the seed data was written to make it hold —
protect that property when the set grows to 200.
vector_full_scan over 24 rows is sub-millisecond. Embedding is ~5 ms/row.
cloudsync_init rejects the plan's §3 table outright:
All non-primary key columns declared as NOT NULL must have a DEFAULT value. (table feedback)
author, channel, body and created_at are all NOT NULL with no default.
CRDT merges arrive column by column, so a NOT NULL column with no default has
nothing to hold the row together mid-merge. Give each one a DEFAULT — do not
reach for init_flags = 2 to skip the check.
Also worth carrying into Phase 1: sqlite-sync's own docs warn that "triggers may
be called multiple times due to column-by-column processing". Our trigger is
idempotent under the IS NULL guard, so it is safe — but a second AI column added
later needs the same guard, not just the first one.
Found in Phase 2, from a crash report — not by the spike, which frees explicitly and so never saw it.
NSApplication terminate:
exit()
__cxa_finalize_ranges
ggml_metal_device_free
ggml_metal_rsets_free
ggml_abort -> abort() SIGABRT
ggml-metal-device.m:612: GGML_ASSERT([rsets->data count] == 0) failed
GGML tears its Metal device down from a process-exit handler and asserts that its resource sets are empty. With a model still loaded they are not, so the process aborts — after the user has clicked Quit, which makes a clean shutdown look like a crash.
Why Drop does not save you. Quit on macOS goes through
NSApplication terminate:, which calls exit(). That runs atexit handlers
without unwinding the stack, so no Rust destructor runs, so nothing calls
llm_context_free / llm_model_free. Any GUI framework that exits this way has
the same problem; a CLI that returns from main does not, which is why the spike
and the selftest are both clean.
Fix (EdgeTable): free both models from Tauri's RunEvent::Exit rather than
relying on Drop, and make the shutdown idempotent so reaching it twice is safe.
Verified by A/B on the real binary:
| exit code | |
|---|---|
| freeing on exit | 0 |
| not freeing (control) | 134 — SIGABRT, the stack above |
Worth fixing upstream. An embedder should not have to know that a Metal assertion will fire at exit. Freeing the model when its connection closes, or registering sqlite-ai's own handler ahead of GGML's, would make the default behaviour safe. This is a third teardown sharp edge alongside #1 and DEMO-FINDINGS #7, and the three share a theme: sqlite-ai holds process-global resources whose lifetime is not tied to the connection that created them.
Every change the database knows about lives in cloudsync_changes, including the
ones pulled from the server, each tagged with the site that authored it. Only
WHERE site_id = cloudsync_siteid() AND db_version > send_dbversionis ours to send — the pair cloudsync_payload_get itself uses. Counting the whole
table made a device that had just pulled 188 rows report 188 rows waiting to go
up, and no amount of syncing could clear it: measured on the demo database,
389 changes across 189 rows, of which exactly 1 row was local.
cloudsync_network_has_unsent_changes() gets this right, and is worth preferring
where a boolean will do — note it makes a network call, which is not obvious from
the name.
cloudsync_cleanup(tbl) on the last enabled table does far more than its name
suggests: it drops cloudsync_settings outright and calls cloudsync_reset_siteid
(cloudsync.c). cloudsync_init then mints a new site id and a db_version counter
starting from zero. Measured across a clear:
site id 01A0422EFDDF7FF18C7F8649510B96BF -> 01A0424159007330B3FD5C519B2A41B3
settings check/send watermarks -> gone, recreated empty
db_version -> 0
The device rejoins as a stranger and pushes again from version 1, which the
service accepts: version ranges are tracked per site, verified live — a
freshly-cleared device pushed localVersion 1 and pulled 225 rows back without
complaint. Re-claiming a range the server already holds for your own site is
what earns a 409 already_exists, so the one thing never to do is reset
send_dbversion by hand. cloudsync_network_reset_sync_version() does exactly
that (it resets all four watermarks, not just the receive pair its name suggests).
cloudsync_network_sync() uploads by asking the service for a URL and then PUTting
the payload to it. That second hop failed twice in about ten pushes against the
live service —
cloudsync_network_send_changes unable to upload BLOB changes to remote host.
— each time succeeding immediately afterwards from an unchanged database. It surfaces as a SQL error, so an app that maps errors to "offline" will say the network is down when it is not. Retry once before believing it.
The receive half is a poll: the client asks the check endpoint, the service builds
the download asynchronously, and until it is ready the endpoint answers "nothing
ready yet" — CLOUDSYNC_NETWORK_OK, explicitly not an error. cloudsync_network_sync
has a loop for exactly this, and it breaks as soon as anything arrives:
while (ntries < max_retries) {
if (ntries > 0) sqlite3_sleep(wait_ms);
rc = cloudsync_network_check_internal(context, &nrows, &sr, &receive_err);
if (rc != SQLITE_OK) break;
if (nrows > 0) break;
ntries++;
}The no-argument SQL form passes DEFAULT_SYNC_WAIT_MS 100, DEFAULT_SYNC_MAX_RETRIES 1
— one attempt, and the sleep never even runs. Three fresh devices needed 2, 3 and
2 presses before a single row arrived. There is a 2-argument overload,
cloudsync_network_sync(wait_ms, max_retries), and it is the one to call.
Cost when there is nothing to fetch is about 0.9s per extra check, so the retry count is a real trade-off rather than a free win:
| call | fresh device | already up to date |
|---|---|---|
cloudsync_network_sync() |
2–3 presses | 0.58s |
(400, 12) |
1 press, 3.0–7.4s | 11.9s |
(250, 6) |
— | 5.0s |
(400, 4) |
— | 3.4–3.8s |
Two related notes from the same measurements:
cloudsync_network_has_unsent_changes()makes a network call. The name does not suggest it, but it hits the status endpoint before answering. Asking it after every sync adds a round-trip to learn something the local change log knows.- No timeouts are set anywhere in the network layer — no curl or NSURLSession timeout is configured, so requests fall back to the platform default. One first sync took 71s.
The single most expensive thing in the app, and it was invisible.
sqlite-sync applies an incoming row column by column, and each write re-enters an
ordinary AFTER INSERT trigger. A guard on a column that has not arrived yet cannot
stop it — on the write carrying body, summary is still NULL, so
CREATE TRIGGER feedback_ai AFTER INSERT ON feedback
WHEN NEW.summary IS NULL AND NEW.body <> ''passes, and the row is inferred locally even though its labels were already in flight. At ~10 changes per row, that is several inferences per row, all discarded.
Measured on a first sync of 24 rows, same moment, same call:
trigger live 101.3s
trigger dropped 4.6s
with identical results: 24 rows, every one carrying the summary, sentiment and category the origin device computed. 22× on the demo's most visible operation.
The fix is the pattern bulk-insert already uses — drop the trigger, apply, put it back — not a cleverer guard: no guard written against a single column can survive column-at-a-time application. Rows that genuinely arrive unlabelled keep NULL AI columns, which is what the backfill worker already looks for.
Note this also invalidates the neater-sounding claim the spike made (§5): the
NEW.summary IS NULL guard does keep locally re-labelling idempotent, but it is
not what protects incoming sync traffic. Nothing was protecting incoming sync
traffic.
- Live SQLite Cloud round trip. The only untested risk. Needs a project, a
managed database id and an API key; then
make spike SPIKE_ARGS=--cloudwithEDGETABLE_DB_IDandEDGETABLE_API_KEYset. Offline semantics are already proven, so this is expected to be a formality — but it is the one step that happens live on stage, so it should be rehearsed early. - Extension loading from a bundled Tauri
.app(macOS signing/quarantine). Untouched by this spike; the plan's §7 mitigation still stands, and it should be tested on the second Mac before Phase 2 is finished, not after. sqlite3_close() returns 5— thesqlite3CLI prints this whenever cloudsync is loaded and the session ends, i.e. unfinalized statements at close. Cosmetic, but it is on screen during the CLI closer in demo beat 5. Worth a look before stage.