fs::remove(TEMP_DB);

fs::remove(TEMP_DB + "-wal");

fs::remove(TEMP_DB + "-shm");

// BM25 index table lives in same DB, no separate file

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

CHECK(db.count() == 0);

db.store("Hello world, this is a test memory.");

CHECK(db.count() == 1);

db.store("Another memory about something else.");

CHECK(db.count() == 2);

db.close();

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

// PHASE8: notes are knowledge rows; a level other than "note" becomes

// a `level:<value>` tag (there is no level column any more).

ragger::json meta = {

{"level", "session"},

{"tags", {"test", "unit"}}

};

std::string id = db.store("Metadata round-trip test.", meta);

CHECK(!id.empty());

auto all = db.load_all();

CHECK(all.size() == 1);

// Tags stored as comma-separated string in the tags column, with the

// level folded in.

CHECK(all[0].metadata["tags"] == "test,unit,level:session");

CHECK(all[0].metadata["kind"] == "note");

db.close();

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

db.store("The capital of France is Paris.");

db.store("SQLite is a lightweight database engine.");

db.store("Machine learning uses neural networks for prediction.");

auto resp = db.search("What is the capital of France?", 3, 0.0f);

CHECK(!resp.results.empty());

// The France/Paris doc should be the top result

CHECK(resp.results[0].text.find("Paris") != std::string::npos);

db.close();

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

db.store("SQLite is a lightweight embedded database engine.");

db.store("The Eiffel Tower stands in Paris.");

// Queries laden with FTS5-significant characters — must not throw.

const char* nasty[] = {

"database \"engine\" AND (lightweight)",

"what is SQLite? -- punctuation: *,^,:",

"NEAR(\"paris", // unbalanced quote / bare operator

"***", // no usable tokens → keyword pass skipped

};

for (const char* q : nasty) {

auto resp = db.search(q, 5, 0.0f); // must not throw

(void)resp;

}

// A normal keyword still resolves through the hybrid path.

auto resp = db.search("lightweight database", 5, 0.0f);

CHECK(!resp.results.empty());

CHECK(resp.results[0].text.find("SQLite") != std::string::npos);

db.close();

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

db.store("The weather in Los Angeles is sunny.");

db.store("Quantum physics studies subatomic particles.");

// Very high min_score should return no results for a vague query

auto resp = db.search("random unrelated gibberish xyzzy", 5, 0.99f);

CHECK(resp.results.empty());

db.close();

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

// A cluster of clearly on-topic docs (high cosine to the query) plus some

// off-topic ones. With a moderate threshold the on-topic docs qualify.

db.store("Paris is the capital city of France.");

db.store("France's capital, Paris, sits on the Seine river.");

db.store("The capital of France is the city of Paris.");

db.store("Lyon and Marseille are large French cities, not the capital.");

db.store("Bananas are a tropical fruit rich in potassium.");

db.store("The mitochondria is the powerhouse of the cell.");

const float thr = 0.25f;

auto resp = db.search("What is the capital of France?", 5, thr);

// (1) Invariant: nothing below threshold is ever returned.

for (const auto& r : resp.results)

CHECK(r.score >= thr);

// (2) Count how many candidates actually clear the threshold by asking for

// a huge limit; with the fix, a smaller limit returns exactly

// min(limit, qualifying), never fewer due to top-k stealing.

auto all = db.search("What is the capital of France?", 100, thr);

size_t qualifying = all.results.size();

size_t expected = std::min<size_t>(5, qualifying);

CHECK(resp.results.size() == expected);

db.close();

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

for (int i = 0; i < 10; ++i) {

db.store("Test document number " + std::to_string(i) + " with some content.");

}

auto resp = db.search("test document", 3, 0.0f);

CHECK((int)resp.results.size() <= 3);

db.close();

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

db.store("First memory.");

db.store("Second memory.");

db.store("Third memory.");

auto all = db.load_all();

CHECK(all.size() == 3);

db.close();

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

// Store some test documents

db.store("Embedding rebuild test document one.");

db.store("Embedding rebuild test document two.");

db.store("Something completely different here.");

// Rebuild all embeddings

int count = db.rebuild_embeddings(emb);

CHECK(count == 3);

// Search should still work after rebuild

auto resp = db.search("embedding test", 5, 0.0f);

CHECK(!resp.results.empty());

db.close();

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

// Rebuild on empty DB should return 0

int count = db.rebuild_embeddings(emb);

CHECK(count == 0);

db.close();

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

// Store various numbers of documents

for (int i = 0; i < 7; ++i) {

db.store("Test document number " + std::to_string(i));

}

int total_count = db.count();

int rebuild_count = db.rebuild_embeddings(emb);

// Rebuild count should match total count

CHECK(rebuild_count == total_count);

CHECK(rebuild_count == 7);

db.close();

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

db.store("A summary about harpsichord tuning.");

ragger::DocumentChunk doc;

doc.text = "A document about harpsichord maintenance.";

doc.title = "Harpsichords";

doc.path = "harpsichord.md";

db.store_document(doc);

db.store_decision("Decided to buy a harpsichord.", "current", "music");

int turn_id = db.store_turn("What is a harpsichord?",

"A plucked keyboard instrument.", "test-model");

// An L2 turn summary is the corpus that carried the bug. It only matters

// if the query actually HITS it — the bad column sat in the per-row fetch,

// which never runs when a corpus has no matches, so an empty

// turn_summaries table would let the bug through.

db.finalize_turn_summary(turn_id, "User asked about the harpsichord.",

"test-model");

CHECK(db.turn_summary_exists(turn_id));

// Must not throw, and must actually find the keyword across corpora.

auto resp = db.search_text_only("harpsichord", 10);

CHECK(resp.timing.contains("text_only"));

CHECK(!resp.results.empty());

// Every hit carries a source label and no vector signal.

bool saw_turn_summary = false;

for (const auto& r : resp.results) {

CHECK(r.metadata.contains("source"));

CHECK(r.vec_score < 0.0f);

if (r.metadata["source"] == "turn_summary") {

saw_turn_summary = true;

CHECK(!r.timestamp.empty()); // resolved from turn_datetime

}

}

CHECK(saw_turn_summary);

// A query that matches nothing returns cleanly rather than throwing.

auto none = db.search_text_only("zzzzznotawordzzzzz", 5);

CHECK(none.results.empty());

db.close();

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

db.store("Timing test document.");

auto resp = db.search("timing", 5, 0.0f);

// Timing JSON should have keys

CHECK(resp.timing.contains("embedding_ms") || resp.timing.contains("total_ms") ||

!resp.timing.empty());

db.close();

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

// Store a memory

std::string id1 = db.store("Memory to delete.");

std::string id2 = db.store("Memory to keep.");

CHECK(db.count() == 2);

// Delete the first memory

bool deleted = db.delete_memory(std::stoi(id1));

CHECK(deleted);

CHECK(db.count() == 1);

// Verify the correct one was deleted

auto all = db.load_all();

CHECK(all.size() == 1);

CHECK(all[0].text == "Memory to keep.");

// Try deleting non-existent ID

deleted = db.delete_memory(99999);

CHECK(!deleted);

CHECK(db.count() == 1);

db.close();

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

// Store 3 memories

std::string id1 = db.store("First memory.");

std::string id2 = db.store("Second memory.");

std::string id3 = db.store("Third memory.");

CHECK(db.count() == 3);

// Delete 2 by ID

std::vector<int> to_delete = {std::stoi(id1), std::stoi(id3)};

int deleted_count = db.delete_batch(to_delete);

CHECK(deleted_count == 2);

CHECK(db.count() == 1);

// Verify the correct one remains

auto all = db.load_all();

CHECK(all.size() == 1);

CHECK(all[0].text == "Second memory.");

// Empty vector → returns 0

deleted_count = db.delete_batch({});

CHECK(deleted_count == 0);

// Delete with non-existent IDs

deleted_count = db.delete_batch({99999, 88888});

CHECK(deleted_count == 0);

db.close();

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

// Lean v2: filterable columns are level / status / tags (+ time window).

db.store("Apple note.", {{"level", "turn"}, {"tags", {"fruit"}}});

db.store("Banana note.", {{"level", "turn"}, {"tags", {"fruit", "tropical"}}});

db.store("Car note.", {{"level", "session"}, {"tags", {"vehicle"}}});

// Filter by level (PHASE8: level lives as a `level:<value>` tag)

auto results = db.search_by_metadata({{"level", "turn"}});

CHECK(results.size() == 2);

for (auto& r : results)

CHECK(r.metadata["tags"].get<std::string>().find("level:turn") != std::string::npos);

// Filter by tag (LIKE on the tags column)

results = db.search_by_metadata({{"tags", "vehicle"}});

CHECK(results.size() == 1);

CHECK(results[0].text == "Car note.");

// AND across columns

results = db.search_by_metadata({{"level", "turn"}, {"tags", "tropical"}});

CHECK(results.size() == 1);

CHECK(results[0].text == "Banana note.");

// Limit respected

results = db.search_by_metadata({{"level", "turn"}}, 1);

CHECK(results.size() == 1);

// No matches → empty

results = db.search_by_metadata({{"level", "project"}});

CHECK(results.empty());

// Unsupported key under lean schema → empty

results = db.search_by_metadata({{"color", "red"}});

CHECK(results.empty());

db.close();

cleanup();

cleanup();

// User management now uses sqlite::Backend (separate from storage backend)

ragger::UserStore umgr(TEMP_DB);

// create_user → returns valid ID

int user_id = umgr.create_user("testuser", "abc123hash");

CHECK(user_id > 0);

int admin_id = umgr.create_user("adminuser", "def456hash");

CHECK(admin_id > 0);

CHECK(admin_id != user_id);

// get_user_by_token_hash → finds created user

auto user_opt = umgr.get_user_by_token_hash("abc123hash");

CHECK(user_opt.has_value());

CHECK(user_opt->username == "testuser");

CHECK(user_opt->token_hash == "abc123hash");

// get_user_by_username → finds created user

user_opt = umgr.get_user_by_username("adminuser");

CHECK(user_opt.has_value());

CHECK(user_opt->username == "adminuser");

CHECK(user_opt->token_hash == "def456hash");

// get_user_by_token_hash with wrong hash → nullopt

user_opt = umgr.get_user_by_token_hash("wronghash");

CHECK(!user_opt.has_value());

// get_user_by_username with wrong name → nullopt

user_opt = umgr.get_user_by_username("nonexistent");

CHECK(!user_opt.has_value());

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

int initial_count = db.count();

// Store a memory with {"keep": true} in metadata

ragger::json meta = {{"keep", true}, {"collection", "memory"}};

auto id = db.store("protected memory", meta);

// Try to delete — should return false

CHECK(!db.delete_memory(std::stoi(id)));

// Memory should still exist

CHECK(db.count() == initial_count + 1);

// Store without keep

auto id2 = db.store("deletable memory", {{"collection", "memory"}});

// Delete should work

CHECK(db.delete_memory(std::stoi(id2)));

// Only the protected one should remain

CHECK(db.count() == initial_count + 1);

db.close();

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

// Store mix of keep and non-keep

ragger::json keep_meta = {{"keep", true}, {"collection", "memory"}};

ragger::json normal_meta = {{"collection", "memory"}};

auto id1 = db.store("keep me", keep_meta);

auto id2 = db.store("delete me", normal_meta);

auto id3 = db.store("delete me too", normal_meta);

int initial_count = db.count();

// Batch delete all three

int deleted = db.delete_batch({std::stoi(id1), std::stoi(id2), std::stoi(id3)});

// Only 2 should be deleted (not the keep one)

CHECK(deleted == 2);

CHECK(db.count() == initial_count - 2);

// Verify the protected one remains

auto all = db.load_all();

bool found_protected = false;

for (const auto& mem : all) {

if (mem.text == "keep me") {

found_protected = true;

break;

}

}

CHECK(found_protected);

db.close();

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

db.store("Timestamp format test.");

auto all = db.load_all();

CHECK(all.size() == 1);

// PHASE8: load_all renders knowledge.created_at as local

// "YYYY-MM-DD HH:MM:SS" (same rendering as every other read path).

auto& ts = all[0].timestamp;

CHECK(!ts.empty());

std::tm tm{};

CHECK(strptime(ts.c_str(), "%Y-%m-%d %H:%M:%S", &tm) != nullptr);

tm.tm_isdst = -1; // let mktime resolve DST (strptime leaves it 0)

int64_t epoch_val = static_cast<int64_t>(mktime(&tm));

int64_t now_epoch = static_cast<int64_t>(std::time(nullptr));

CHECK(epoch_val > 0);

CHECK(epoch_val <= now_epoch + 5); // not in the future

CHECK(now_epoch - epoch_val < 300); // stored "just now"

db.close();

cleanup();

cleanup();

{

ragger::sqlite::Backend db(emb, TEMP_DB);

db.store("v2 summaries backing check.",

{{"level", "session"}, {"tags", {"note"}}});

auto all = db.load_all();

CHECK(all.size() == 1);

CHECK(all[0].metadata["kind"] == "note");

CHECK(all[0].metadata["tags"] == "note,level:session");

db.close();

}

// Inspect the raw DB: the row must be in `knowledge`, and the legacy

// `memories` table must not exist in a fresh database.

sqlite3* raw = nullptr;

CHECK(sqlite3_open(TEMP_DB.c_str(), &raw) == SQLITE_OK);

sqlite3_stmt* st = nullptr;

sqlite3_prepare_v2(raw, "SELECT COUNT(*) FROM knowledge WHERE kind='note'", -1, &st, nullptr);

CHECK(sqlite3_step(st) == SQLITE_ROW);

CHECK(sqlite3_column_int(st, 0) == 1);

sqlite3_finalize(st);

sqlite3_prepare_v2(raw,

"SELECT COUNT(*) FROM sqlite_master "

"WHERE type='table' AND name='memories'",

-1, &st, nullptr);

CHECK(sqlite3_step(st) == SQLITE_ROW);

CHECK(sqlite3_column_int(st, 0) == 0); // no legacy table in fresh DB

sqlite3_finalize(st);

sqlite3_close(raw);

cleanup();

cleanup();

int doc_id = -1;

{

ragger::sqlite::Backend db(emb, TEMP_DB);

ragger::DocumentChunk doc;

doc.text = "The mitochondria is the powerhouse of the cell.";

doc.title = "Cell Biology Primer";

doc.tags = "biology,cells";

doc.year = 2021;

doc.path = "/tmp/primer.md";

doc.chunk_index = 1;

doc_id = db.store_document(doc);

CHECK(doc_id > 0);

db.close();

}

// Verify the dedicated columns round-trip. Per-document metadata

// (title/tags/year/path) lives in document_sources since the v0.15

// normalization migration; documents (chunks) hold only text/chunk_index

// + a document_source_id FK.

sqlite3* raw = nullptr;

CHECK(sqlite3_open(TEMP_DB.c_str(), &raw) == SQLITE_OK);

sqlite3_stmt* st = nullptr;

sqlite3_prepare_v2(raw,

"SELECT d.text, ds.title, ds.tags, ds.year, ds.path, d.chunk_index "

"FROM documents d JOIN document_sources ds "

" ON ds.document_source_id = d.document_source_id "

"WHERE d.document_id = ?",

-1, &st, nullptr);

sqlite3_bind_int(st, 1, doc_id);

CHECK(sqlite3_step(st) == SQLITE_ROW);

auto col = [&](int i) {

const char* s = reinterpret_cast<const char*>(sqlite3_column_text(st, i));

return std::string(s ? s : "");

};

std::string text = col(0);

CHECK(col(1) == "Cell Biology Primer");

CHECK(col(2) == "biology,cells");

CHECK(sqlite3_column_int(st, 3) == 2021);

CHECK(col(4) == "/tmp/primer.md");

CHECK(sqlite3_column_int(st, 5) == 1);

// Grouping metadata must NOT be embedded into the body text.

CHECK(text == "The mitochondria is the powerhouse of the cell.");

CHECK(text.find("Cell Biology Primer") == std::string::npos);

sqlite3_finalize(st);

sqlite3_close(raw);

cleanup();

cleanup();

{

ragger::sqlite::Backend db(emb, TEMP_DB);

// Complete turn in one call — embedded immediately.

int t1 = db.store_turn("What is the capital of France?",

"The capital of France is Paris.", "test-model");

CHECK(t1 > 0);

// Partial turn (no assistant yet) → embedding NULL → finalize fills it.

int t2 = db.store_turn("Tell me about photosynthesis.", "", "test-model",

/*defer_embedding=*/true);

CHECK(t2 > 0 && t2 != t1);

CHECK(db.finalize_turn(t2, "Plants convert sunlight into energy.",

"test-model"));

db.close();

}

sqlite3* raw = nullptr;

CHECK(sqlite3_open(TEMP_DB.c_str(), &raw) == SQLITE_OK);

// Both rows present, both have assistant_text and a non-NULL embedding,

// and a model_id resolved to the 'test-model' models row.

sqlite3_stmt* st = nullptr;

sqlite3_prepare_v2(raw,

"SELECT t.user_text, t.assistant_text, "

" length(t.embedding), m.name "

"FROM turns t JOIN models m ON m.model_id = t.model_id "

"ORDER BY t.turn_id", -1, &st, nullptr);

int rows = 0;

while (sqlite3_step(st) == SQLITE_ROW) {

const char* u = reinterpret_cast<const char*>(sqlite3_column_text(st, 0));

const char* a = reinterpret_cast<const char*>(sqlite3_column_text(st, 1));

int emb_bytes = sqlite3_column_int(st, 2);

const char* model = reinterpret_cast<const char*>(sqlite3_column_text(st, 3));

CHECK(u && a && a[0] != '\0'); // assistant filled in

// Embedded as f16, payload-only (db_version 0.15+; no version byte —

// that tag lives in the sibling embedding_version column now):

// 384 dims * 2 bytes/dim.

CHECK(emb_bytes == 384 * 2);

CHECK(model && std::string(model) == "test-model");

++rows;

}

sqlite3_finalize(st);

CHECK(rows == 2);

// The model row was created exactly once, not duplicated.

sqlite3_prepare_v2(raw, "SELECT COUNT(*) FROM models WHERE name='test-model'",

-1, &st, nullptr);

CHECK(sqlite3_step(st) == SQLITE_ROW);

CHECK(sqlite3_column_int(st, 0) == 1);

sqlite3_finalize(st);

// finalize_turn on a non-existent id returns false.

{

ragger::sqlite::Backend db2(emb, TEMP_DB);

CHECK(!db2.finalize_turn(99999, "x", "test-model"));

db2.close();

}

sqlite3_close(raw);

cleanup();

cleanup();

int t1 = 0;

{

ragger::sqlite::Backend db(emb, TEMP_DB);

// First answer to a prompt, in session A.

t1 = db.store_turn("Explain the general_search recipe.",

"It searches summaries.", "model-a",

/*defer_embedding=*/false, "sessionA");

CHECK(t1 > 0);

// Sleep across a second boundary: local_timestamp() has second-level

// resolution, so without a real gap here old_ts would trivially equal

// new_ts and this test would not exercise the drift this guards

// against (a dedup that lands in the same second as the original).

std::this_thread::sleep_for(std::chrono::milliseconds(1100));

// Same prompt re-answered — DIFFERENT session (sessionB), different

// assistant text. Must update t1 in place, not insert a new row.

int t2 = db.store_turn("Explain the general_search recipe.",

"It searches summaries, documents, and decisions.",

"model-b", /*defer_embedding=*/false, "sessionB");

CHECK(t2 == t1); // same row id returned — keep-latest

// Cross a second boundary before the next insert: local_timestamp()

// has second-level resolution, and turn3 landing in the same second

// as t1's dedup-updated created_at is itself a separate known

// same-second collision (harmless for the summarizer's matching, but

// it would otherwise make this assertion block conflate two issues).

std::this_thread::sleep_for(std::chrono::milliseconds(1100));

// A genuinely different prompt still inserts a fresh row.

int t3 = db.store_turn("What about decisions?", "They are first-class.",

"model-b", /*defer_embedding=*/false, "sessionB");

CHECK(t3 != t1);

db.close();

}

sqlite3* raw = nullptr;

CHECK(sqlite3_open(TEMP_DB.c_str(), &raw) == SQLITE_OK);

// Exactly two turn rows: the deduped pair collapsed to one, plus the

// distinct third prompt.

sqlite3_stmt* st = nullptr;

sqlite3_prepare_v2(raw, "SELECT COUNT(*) FROM turns", -1, &st, nullptr);

CHECK(sqlite3_step(st) == SQLITE_ROW);

CHECK(sqlite3_column_int(st, 0) == 2);

sqlite3_finalize(st);

// The surviving deduped row carries the LATEST assistant_text and is not

// duplicated in the FTS index (one hit for the prompt's distinctive term).

sqlite3_prepare_v2(raw,

"SELECT assistant_text FROM turns "

"WHERE user_text = 'Explain the general_search recipe.'",

-1, &st, nullptr);

CHECK(sqlite3_step(st) == SQLITE_ROW);

{

const char* a = reinterpret_cast<const char*>(sqlite3_column_text(st, 0));

CHECK(a && std::string(a).find("decisions") != std::string::npos);

}

// Only one such row (no duplicate).

CHECK(sqlite3_step(st) != SQLITE_ROW);

sqlite3_finalize(st);

// Custom terms index agrees: the prompt's distinctive stemmed bigram term

// resolves to exactly one turn_id (dedup didn't leave a stale/duplicate

// turns_terms row behind). Snowball's English algorithm leaves "general"

// unstemmed (unlike the old Porter pipeline, which reduced it to

// "genera"), so the stored literal bigram token is "general_search".

sqlite3_prepare_v2(raw,

"SELECT COUNT(*) FROM turns_terms tt "

"JOIN terms t ON t.term_id = tt.term_id "

"WHERE t.term = 'general_search'",

-1, &st, nullptr);

CHECK(sqlite3_step(st) == SQLITE_ROW);

CHECK(sqlite3_column_int(st, 0) == 1);

sqlite3_finalize(st);

// Regression: a dedup that moves the turn's created_at must not leave a

// stray turn_summaries row for this turn. Since placeholders no longer

// exist (per Reid's design decision — no placeholder mechanism), the

// deduped turn should have ZERO turn_summaries rows until explicitly

// finalized.

sqlite3_prepare_v2(raw,

"SELECT COUNT(*) FROM turn_summaries ts WHERE ts.turn_id = ?",

-1, &st, nullptr);

sqlite3_bind_int(st, 1, t1);

CHECK(sqlite3_step(st) == SQLITE_ROW);

CHECK(sqlite3_column_int(st, 0) == 0);

sqlite3_finalize(st);

sqlite3_close(raw);

cleanup();

cleanup();

const std::string S = "dedup-same-session-guid";

int t1 = 0;

{

ragger::sqlite::Backend db(emb, TEMP_DB);

t1 = db.store_turn("U1", "A1", "test-model",

/*defer_embedding=*/false, S);

CHECK(t1 > 0);

// Same prompt, same session, new answer, inside the 5-minute window

// (no sleep needed: same-second is well within it). Must keep-latest.

int t2 = db.store_turn("U1", "A2", "test-model",

/*defer_embedding=*/false, S);

CHECK(t2 == t1); // same row id returned — dedup branch reached

db.close();

}

{

// Exactly one row for that session after the deduped pair, and the

// surviving row carries the LATEST assistant_text.

sqlite3* raw = nullptr;

CHECK(sqlite3_open(TEMP_DB.c_str(), &raw) == SQLITE_OK);

sqlite3_stmt* st = nullptr;

sqlite3_prepare_v2(raw,

"SELECT COUNT(*) FROM turns t "

"JOIN sessions s ON s.session_id = t.session_id WHERE s.guid = ?",

-1, &st, nullptr);

sqlite3_bind_text(st, 1, S.c_str(), -1, SQLITE_STATIC);

CHECK(sqlite3_step(st) == SQLITE_ROW);

CHECK(sqlite3_column_int(st, 0) == 1);

sqlite3_finalize(st);

sqlite3_prepare_v2(raw,

"SELECT assistant_text FROM turns WHERE turn_id = ?",

-1, &st, nullptr);

sqlite3_bind_int(st, 1, t1);

CHECK(sqlite3_step(st) == SQLITE_ROW);

const char* a = reinterpret_cast<const char*>(sqlite3_column_text(st, 0));

CHECK(a && std::string(a) == "A2");

sqlite3_finalize(st);

sqlite3_close(raw);

}

{

// A genuinely different prompt in the same session inserts a fresh row.

ragger::sqlite::Backend db(emb, TEMP_DB);

int t3 = db.store_turn("U2", "A2b", "test-model",

/*defer_embedding=*/false, S);

CHECK(t3 > 0 && t3 != t1);

db.close();

}

{

sqlite3* raw = nullptr;

CHECK(sqlite3_open(TEMP_DB.c_str(), &raw) == SQLITE_OK);

sqlite3_stmt* st = nullptr;

sqlite3_prepare_v2(raw,

"SELECT COUNT(*) FROM turns t "

"JOIN sessions s ON s.session_id = t.session_id WHERE s.guid = ?",

-1, &st, nullptr);

sqlite3_bind_text(st, 1, S.c_str(), -1, SQLITE_STATIC);

CHECK(sqlite3_step(st) == SQLITE_ROW);

CHECK(sqlite3_column_int(st, 0) == 2);

sqlite3_finalize(st);

sqlite3_close(raw);

}

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

int t1 = db.store_turn("What is the capital of Spain?",

"The capital of Spain is Madrid.", "test-model");

CHECK(t1 > 0);

CHECK(!db.turn_summary_exists(t1));

sqlite3* raw = nullptr;

CHECK(sqlite3_open(TEMP_DB.c_str(), &raw) == SQLITE_OK);

sqlite3_stmt* st = nullptr;

sqlite3_prepare_v2(raw, "SELECT COUNT(*) FROM turn_summaries WHERE turn_id = ?",

-1, &st, nullptr);

sqlite3_bind_int(st, 1, t1);

CHECK(sqlite3_step(st) == SQLITE_ROW);

CHECK(sqlite3_column_int(st, 0) == 0);

sqlite3_finalize(st);

sqlite3_close(raw);

db.close();

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

int t1 = db.store_turn("Tell me about volcanoes.", "", "test-model",

/*defer_embedding=*/true);

CHECK(t1 > 0);

CHECK(!db.turn_summary_exists(t1));

db.close();

cleanup();

cleanup();

ragger::sqlite::Backend db(emb, TEMP_DB);

int t1 = db.store_turn("Summarize this exchange.",

"First finalize target.", "test-model");

CHECK(t1 > 0);

CHECK(db.finalize_turn_summary(t1, "First summary text.", "summarizer-model"));

CHECK(!db.finalize_turn_summary(t1, "Second summary text.", "summarizer-model"));

sqlite3* raw = nullptr;

CHECK(sqlite3_open(TEMP_DB.c_str(), &raw) == SQLITE_OK);

sqlite3_stmt* st = nullptr;

sqlite3_prepare_v2(raw, "SELECT COUNT(*) FROM turn_summaries WHERE turn_id = ?",

-1, &st, nullptr);

sqlite3_bind_int(st, 1, t1);

CHECK(sqlite3_step(st) == SQLITE_ROW);

CHECK(sqlite3_column_int(st, 0) == 1); // no duplicate row

sqlite3_finalize(st);

sqlite3_prepare_v2(raw, "SELECT text FROM turn_summaries WHERE turn_id = ?",

-1, &st, nullptr);

sqlite3_bind_int(st, 1, t1);

CHECK(sqlite3_step(st) == SQLITE_ROW);

{

const char* txt = reinterpret_cast<const char*>(sqlite3_column_text(st, 0));

CHECK(txt && std::string(txt) == "First summary text.");

}

sqlite3_finalize(st);