[Bug]: QueueFS list_unacked builds a sort it does not need; an unusable temp dir makes the server refuse to start #6127

Issue Origin

Observed or reproduced in a real environment

Bug Description

SQLiteQueueBackend::list_unacked reads the whole unacknowledged list with

SELECT data FROM queue_messages
 WHERE queue_name = ?1 AND status IN ('pending', 'processing')
 ORDER BY id

(crates/ragfs/src/plugins/queuefs/backend.rs:782-784).

The same file creates idx_queue_status ON queue_messages(queue_name, status, id)
(backend.rs:463) — note the column order. Because the predicate matches two status
values, that index cannot deliver rows in id order, so SQLite adds a sort:

SEARCH queue_messages USING INDEX idx_queue_status (queue_name=? AND status=?)
USE TEMP B-TREE FOR ORDER BY

The sort is unnecessary: rows are already stored in id order, and id is the
INTEGER PRIMARY KEY. The sorter also spills to a temporary file once the queue passes
roughly a megabyte, which turns a cosmetic inefficiency into a hard failure.

Steps to Reproduce

1. Populate one queue with ~132 messages of 16 KB each (about 8.7 MB unacknowledged).
2. Make the process temporary directory unusable for SQLite (this is the environment, not
   the trigger — see Additional Context).
3. Call list_unacked, or start the server, which calls snapshot() for every queue during
   QueueManager.prepare_task_tracking.

Expected Behavior

The unacknowledged list is read without a sort, because the index can serve ORDER BY id
directly. Startup does not depend on a writable temporary directory.

Actual Behavior

internal error: sqlite list unacked row error: unable to open database file

The database is intact (PRAGMA quick_check returns ok) and small queues read fine — only
a queue large enough to force the sorter to disk fails. The message names the wrong cause.

Because task recovery reads every queue before any consumer starts, a large queue prevents
the server from starting at all (Application startup failed). A queue only drains while the
server runs, so the state never heals on its own; the only exit is deleting the queued
messages, which loses unprocessed work.

Minimal Reproducible Example

import json, os, sqlite3

# any directory SQLite cannot write to
os.environ["TEMP"] = r"C:\Temp"

con = sqlite3.connect("queue.db")
con.execute("""CREATE TABLE queue_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT, queue_name TEXT NOT NULL,
    message_id TEXT NOT NULL, data TEXT NOT NULL, timestamp INTEGER NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending', processing_started_at INTEGER,
    created_at INTEGER DEFAULT (strftime('%s','now')))""")
con.execute("CREATE INDEX idx_queue_status ON queue_messages(queue_name, status, id)")

blob = "x" * 16384
for i in range(132):
    con.execute("INSERT INTO queue_messages (queue_name, message_id, data, timestamp)"
                " VALUES ('Embedding', ?, ?, 0)",
                ("m%d" % i, json.dumps({"id": "m%d" % i, "data": blob})))
con.commit()

q = ("SELECT data FROM queue_messages WHERE queue_name = ?"
     " AND status IN ('pending','processing') ORDER BY id")

print([r[3] for r in con.execute("EXPLAIN QUERY PLAN " + q, ("Embedding",))])
print(len(con.execute(q, ("Embedding",)).fetchall()))   # sqlite3.OperationalError

# Recreating the index in a sort-free column order removes both the sort and the failure:
con.execute("DROP INDEX idx_queue_status")
con.execute("CREATE INDEX idx_queue_status ON queue_messages(queue_name, id, status)")
con.commit()
# plan becomes: SEARCH queue_messages USING INDEX idx_queue_status (queue_name=?)
# and the same query reads all 132 rows (8 674 710 bytes) with the same unusable TEMP

Error Logs

E openviking.service.task: Application startup failed
   internal error: sqlite list unacked row error: unable to open database file

OpenViking Version

0.5.0

Python Version

3.13.15

Operating System

Windows

Model Backend

Other

Additional Context

Model Backend
Other — local llama-server (llama.cpp) behind an OpenAI-compatible endpoint

Impact measured: 132 unacknowledged messages, 7 946 417 bytes; the server failed to
start, and search returned HTTP 500 for the duration.

Failure threshold: between 564 630 and 2 186 646 bytes of queued data, consistent with
SQLITE_SORTER_PMASZ (250 pages x 4096-byte pages).

Suggested fix — three parts.

1. Reorder the index so ORDER BY id is satisfied by it; status remains a residual filter:

-            "CREATE INDEX IF NOT EXISTS idx_queue_status ON queue_messages(queue_name, status, id)",
+            "CREATE INDEX IF NOT EXISTS idx_queue_status ON queue_messages(queue_name, id, status)",

2. Migrate existing databases. CREATE INDEX IF NOT EXISTS leaves the old definition in
   place, so the change above does not repair an installed instance. Something like this in
   SQLiteQueueBackend::open, after the schema batch:

let stale: i64 = conn.query_row(
    "SELECT COUNT(*) FROM sqlite_master WHERE type = 'index'"
      " AND name = 'idx_queue_status' AND sql NOT LIKE '%queue_name, id, status%'",
    [], |row| row.get(0),
)?;
if stale > 0 {
    conn.execute_batch(
        "DROP INDEX IF EXISTS idx_queue_status;
         CREATE INDEX idx_queue_status ON queue_messages(queue_name, id, status);",
    )?;
}

3. Do not let this path depend on a writable temporary directory: either set
   PRAGMA temp_store = MEMORY at connection setup, or keep the query sort-free as above.

Environment note worth knowing: SQLITE_TMPDIR is not honoured on this path. Setting it
while TEMP/TMP point at an unusable directory still fails, so an operator cannot work
around this from the environment.

Diagnostic note: Error::internal(format!("sqlite list unacked row error: {}", e))
(backend.rs:794) hides which file operation failed. Including the SQLite extended result
code — SQLITE_CANTOPEN here — would have turned a long diagnosis into a one-minute one.

Suggested area / owners (from CONTRIBUTING.md): Storage → RAGFS, PathLock, QueueFS,
crates/ragfs* → @baojun-zhang.