History: enforce modes on existing paths, index turns, strip NUL

From review of the storage commit. makedirs and connect leave an existing
directory or file at whatever mode it had, so a history dir made earlier
with the default umask stayed 0755; both are now chmodded. A NUL in a
search ended the FTS string and raised instead of returning nothing. The
index on turns(conv, seq) serves the seq lookup per save, load_conv and the
cascade, which otherwise scan every turn. The self-check now also asserts
that an OR query matches nothing, which a non-raising but unquoted query
would not.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>

1 files changed, 12 insertions, 4 deletions

diff --git a/assistant.py b/assistant.py
index 3bd8b0c..f455d29 100644
--- a/assistant.py
+++ b/assistant.py
@@ -119,6 +119,7 @@ CREATE TABLE IF NOT EXISTS conversations(id INTEGER PRIMARY KEY, title TEXT,
 CREATE TABLE IF NOT EXISTS turns(id INTEGER PRIMARY KEY,
     conv INTEGER REFERENCES conversations ON DELETE CASCADE,
     seq INTEGER, user TEXT, reply TEXT, tools TEXT, messages TEXT);
+CREATE INDEX IF NOT EXISTS turns_conv ON turns(conv, seq);
 CREATE VIRTUAL TABLE IF NOT EXISTS turns_fts USING fts5(user, reply, content='turns',
     content_rowid='id', tokenize='unicode61 remove_diacritics 2');
 CREATE TRIGGER IF NOT EXISTS turns_ai AFTER INSERT ON turns BEGIN
@@ -133,12 +134,15 @@ END;
 
 def db_open(path):
     if path != ":memory:":
-        os.makedirs(os.path.dirname(path), mode=0o700, exist_ok=True)
-    old = os.umask(0o177)                 # 0600, and the WAL files inherit it
+        os.makedirs(os.path.dirname(path), exist_ok=True)
+        os.chmod(os.path.dirname(path), 0o700)   # also when something else made it
+    old = os.umask(0o177)                 # 0600; SQLite gives the WAL files the db's mode
     try:
         conn = sqlite3.connect(path, check_same_thread=False)
     finally:
         os.umask(old)
+    if path != ":memory:":
+        os.chmod(path, 0o600)             # an existing file keeps its old mode otherwise
     conn.execute("PRAGMA journal_mode=WAL")
     conn.execute("PRAGMA foreign_keys=ON")
     conn.executescript(SCHEMA)
@@ -183,7 +187,7 @@ def search(conn, q):
     # The query is one quoted phrase with a prefix star on its last word, so
     # FTS syntax typed by the user is matched as text and cannot widen it.
     # ponytail: rank per turn, first hit per conversation; 30 results.
-    q = q.strip()
+    q = q.replace("\0", "").strip()         # NUL ends the FTS string early
     if not q:
         return []
     seen, out = set(), []
@@ -445,8 +449,9 @@ def test():
     assert [h["id"] for h in hits] == [a] and "**città**" in hits[0]["snippet"]
     assert [h["id"] for h in search(db, "cit")] == [a]                 # prefix, as you type
     assert [h["id"] for h in search(db, "piove")] == [a]               # replies are indexed
-    for q in ('"', "*", 'a"b', "NOT", "x OR y", "ls)", ""):            # FTS syntax is text
+    for q in ('"', "*", 'a"b', "NOT", "x OR y", "ls)", "", "a\0b"):    # FTS syntax is text
         search(db, q)
+    assert search(db, "piove OR ls") == []          # unquoted, this would match both
     delete_conv(db, a)
     assert load_conv(db, a) is None and search(db, "citta") == []
     # Raises if the index still holds the deleted turns: the cascade must
@@ -454,6 +459,9 @@ def test():
     db.execute("INSERT INTO turns_fts(turns_fts, rank) VALUES ('integrity-check', 1)")
     with tempfile.TemporaryDirectory() as d:
         path = os.path.join(d, "sub", "h.db")
+        os.makedirs(os.path.dirname(path), mode=0o755)
+        open(path, "w").close()
+        os.chmod(path, 0o644)                       # made earlier by something else
         db_open(path).close()
         assert os.stat(os.path.dirname(path)).st_mode & 0o777 == 0o700
         assert os.stat(path).st_mode & 0o777 == 0o600