#206: pin the read-only open with a test that actually fails without it

setReadOnly(true) is the whole thing keeping fleetd out of the operator's
live 841MB opencode.db, and no test failed when it was removed.

The obvious test does not work. Making the database file unwritable and
checking the read still succeeds passes either way, because SQLite silently
downgrades a read-write open of an unwritable file to read-only. I wrote that
test, watched it pass with the flag removed, and threw it away.

What works: extract a package-private openReadOnly(), then ask that connection
to INSERT and require the refusal. Watched red with the flag removed, green
with it restored.

Also switches the test's INSERT helper to a PreparedStatement -- hand-escaped
SQL in a test is a pattern that gets copied into main code.

fleetd/pom.xml

@@ -28,6 +28,7 @@
         <testcontainers.version>1.20.4</testcontainers.version>
         <commons-compress.version>1.27.1</commons-compress.version>
         <commons-lang3.version>3.18.0</commons-lang3.version>
+        <sqlite-jdbc.version>3.53.4.0</sqlite-jdbc.version>
     </properties>
 
     <!--
@@ -44,6 +45,12 @@
           3.0-rc5; bumping Jackson 3 to the patched 3.2.x breaks the SDK (annotation mismatch).
           Only the loopback /mcp endpoint parses this JSON, from trusted local Claude clients.
       The 11.0.23 -> 11.0.25 bump did clear jetty CVE-2024-8184 (5.9) and CVE-2024-6763.
+
+      fleetd #206: org.xerial:sqlite-jdbc 3.53.4.0 (added for OpenCodeSessionDiscovery) — the
+      only known advisory against this artifact is CVE-2023-32697 (RCE via an attacker-controlled
+      JDBC URL), fixed in 3.41.2.2; 3.53.4.0 is well past that fix and OSV.dev reports no open
+      advisory against it. Checked via the OSV.dev API (no Mend.io/JetBrains IDE MCP mount
+      available from this worktree) on 2026-08-31.
     -->
 
     <!-- Force the latest patched Jetty 11.x across all Javalin-pulled Jetty modules (no version
@@ -123,6 +130,17 @@
             <version>${amqp.version}</version>
         </dependency>
 
+        <!-- fleetd #206: opencode moved its session store from a JSON tree to SQLite
+             (opencode.db). This is the JDBC driver OpenCodeSessionDiscovery uses to read it
+             read-only. Ships bundled native libraries (linux/mac/windows, several archs), so it
+             is a heavier jar than most deps here — see the pom's dependency-security note below
+             for the size/CVE tradeoff actually measured. -->
+        <dependency>
+            <groupId>org.xerial</groupId>
+            <artifactId>sqlite-jdbc</artifactId>
+            <version>${sqlite-jdbc.version}</version>
+        </dependency>
+
         <!-- Logging -->
         <dependency>
             <groupId>org.slf4j</groupId>

fleetd/src/main/java/dev/ltms/fleet/member/OpenCodeSessionDiscovery.java

@@ -1,58 +1,87 @@
 package dev.ltms.fleet.member;
 
-import com.fasterxml.jackson.databind.JsonNode;
-import com.fasterxml.jackson.databind.ObjectMapper;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import org.sqlite.SQLiteConfig;
 
-import java.io.IOException;
 import java.nio.file.Files;
 import java.nio.file.Path;
-import java.util.stream.Stream;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.concurrent.atomic.AtomicBoolean;
 
 /**
  * Resolves the opencode session id for a fleetd worker from opencode's on-disk storage — the
  * only place this adapter touches opencode's private layout, and deliberately the <em>only</em>
  * class that does.
  *
- * <p><strong>Why this is isolated behind one seam.</strong> The layout is version-coupled and not a
- * stable contract: opencode writes one JSON file per session under
- * {@code <storageRoot>/session/<projectID>/<ses_*.json>}, and each record carries a
- * {@code "version"} field (e.g. {@code "1.1.31"}), so the exact directory shape, file naming, and
- * field names can move between opencode releases. opencode also ships a headless HTTP server that
- * may supersede file scanning entirely. Everything this adapter knows about that private storage —
- * its shape, naming, and field names — lives here, so a layout change, or a switch to the HTTP
- * server, changes exactly one class and nothing in {@link OpenCodeLauncher}.
+ * <p><strong>Why this is isolated behind one seam.</strong> The layout is version-coupled and not
+ * a stable contract: opencode persists its session state in a SQLite database at
+ * {@code <storageRoot>/opencode.db} (a {@code session} table, one row per session, keyed by id and
+ * carrying a {@code directory} column). That schema can move between opencode releases exactly
+ * like the JSON-file layout it replaced did (opencode migrated off a one-JSON-file-per-session
+ * tree under {@code <storageRoot>/storage/session/<projectID>/ses_*.json} in January 2026 — that
+ * tree is now a frozen migration artefact nothing writes, which is why this class no longer reads
+ * it). opencode also ships a headless HTTP server that may supersede both of these entirely.
+ * Everything this adapter knows about that private storage — its shape and column names — lives
+ * here, so a layout change, or a switch to the HTTP server, changes exactly one class and nothing
+ * in {@link OpenCodeLauncher}.
  *
  * <p>The determinism that makes this useful is structural, not a guess: every fleetd worker runs
- * in its own unique git worktree, so the record's {@code directory} (its project root) equals the
+ * in its own unique git worktree, so the row's {@code directory} (its project root) equals the
  * worker's cwd identifies <em>its</em> session unambiguously. We match on {@code directory} rather
  * than diffing {@code opencode session list} before/after — that races under concurrent spawns, and
  * the CLI listing does not even show the directory.
  *
- * <p>All reads are best-effort and never throw: a missing or unreadable storage root, a record that
- * fails to parse, or a directory with no record yet all yield {@code null}, and the caller (the
- * session handle) treats that as "identity not resolved yet" and retries later.
+ * <p>All reads are best-effort and never throw: a missing or unreadable database, a query that
+ * fails, or a directory with no row yet all yield {@code null}, and the caller (the session
+ * handle) treats that as "identity not resolved yet" and retries later. The database is opened
+ * read-only and never written to: opencode itself may be running and writing it concurrently (WAL
+ * mode), and this class must never disturb that.
  */
 final class OpenCodeSessionDiscovery {
 
+    private static final Logger log = LoggerFactory.getLogger(OpenCodeSessionDiscovery.class);
+
     private final Path storageRoot;   // e.g. ~/.local/share/opencode (injectable for tests)
-    private final ObjectMapper json;
+    private final Path databasePath;
+    private final AtomicBoolean warnedMissingDatabase = new AtomicBoolean(false);
 
     OpenCodeSessionDiscovery(Path storageRoot) {
         this.storageRoot = storageRoot;
-        this.json = new ObjectMapper();
+        this.databasePath = storageRoot.resolve("opencode.db");
     }
 
     /**
-     * The opencode session id whose record references {@code directory} (the worker's cwd), or
-     * {@code null} when no record matches yet. When several records share the directory — e.g.
-     * repeated spawns into the same worktree — the <em>most recently modified</em> one wins: it is
+     * A connection to {@link #databasePath} opened with SQLite's {@code SQLITE_OPEN_READONLY}
+     * flag: it never creates the file, never writes, and never touches WAL or journal mode.
+     * opencode may be running and writing this database concurrently, and this class must never
+     * disturb it.
+     *
+     * <p>Package-private so a test can hold the connection and prove it refuses a write. That is
+     * the only way to pin this property: making the file unwritable does <em>not</em> work,
+     * because SQLite silently downgrades a read-write open of an unwritable file to read-only, so
+     * such a test passes whether or not the flag is set.
+     */
+    Connection openReadOnly() throws SQLException {
+        SQLiteConfig config = new SQLiteConfig();
+        config.setReadOnly(true);
+        return config.createConnection("jdbc:sqlite:" + databasePath);
+    }
+
+    /**
+     * The opencode session id whose row references {@code directory} (the worker's cwd), or
+     * {@code null} when no row matches yet. When several rows share the directory — e.g. repeated
+     * spawns into the same worktree — the row with the highest {@code time_updated} wins: it is
      * the session the pane most likely corresponds to.
      *
-     * <p>Never throws: a missing {@code storageRoot}, an unreadable/malformed record, or a
-     * directory that has not been persisted yet all resolve to {@code null} rather than failing a
-     * spawn. A fleetd worker's session record is written lazily (when the session is first
-     * persisted), so {@code null} here is the normal answer right after the pane is ready, and the
-     * caller retries later.
+     * <p>Never throws: a missing {@code opencode.db}, a locked/unreadable database, a query
+     * failure, or a directory that has not been persisted yet all resolve to {@code null} rather
+     * than failing a spawn. A fleetd worker's session row is written lazily (when the session is
+     * first persisted), so {@code null} here is the normal answer right after the pane is ready,
+     * and the caller retries later.
      *
      * @param directory the worker's cwd, as resolved for this spawn
      * @return the matching session id, or {@code null} if none is known yet
@@ -61,62 +90,31 @@ final class OpenCodeSessionDiscovery {
         if (directory == null || directory.isBlank()) {
             return null;
         }
-        Path sessionRoot = storageRoot.resolve("session");
-        if (!Files.isDirectory(sessionRoot)) {
+        if (!Files.isRegularFile(databasePath)) {
+            if (warnedMissingDatabase.compareAndSet(false, true)) {
+                log.warn("opencode session database not found at {} — opencode's on-disk layout "
+                        + "may have moved again; session discovery will keep returning null",
+                        databasePath);
+            }
             return null;
         }
-        String best = null;
-        long bestMtime = Long.MIN_VALUE;
-        try (Stream<Path> projectDirs = Files.list(sessionRoot)) {
-            for (Path projectDir : projectDirs.filter(Files::isDirectory).toList()) {
-                try (Stream<Path> records = Files.list(projectDir)) {
-                    for (Path record : records.toList()) {
-                        String id = matchId(record, directory);
-                        if (id == null) {
-                            continue;
-                        }
-                        long mtime = lastModifiedEpochMillis(record);
-                        if (mtime > bestMtime) {
-                            bestMtime = mtime;
-                            best = id;
-                        }
-                    }
-                } catch (IOException ignored) {
-                    // one project dir unreadable — skip it; another may still match
+        String sql = "SELECT id FROM session WHERE directory = ? ORDER BY time_updated DESC LIMIT 1";
+        try (Connection connection = openReadOnly();
+             PreparedStatement statement = connection.prepareStatement(sql)) {
+            statement.setString(1, directory);
+            try (ResultSet rows = statement.executeQuery()) {
+                if (rows.next()) {
+                    return rows.getString("id");
                 }
             }
-        } catch (IOException ignored) {
-            // storage root vanished or became unreadable — "no session known yet"
+        } catch (SQLException e) {
+            // Locked, corrupt, or otherwise unreadable — never fatal to a spawn. Not the
+            // "database moved" signal (the file exists), so this stays below WARN.
+            log.debug("opencode session database unreadable at {}: {}", databasePath, e.toString());
             return null;
         }
-        return best;
-    }
-
-    /**
-     * The record's session id when it references {@code directory}, else {@code null}. A record
-     * that is not JSON, lacks {@code id}/{@code directory}, or points at a different directory is
-     * simply not our session; a malformed one is skipped, never fatal.
-     */
-    private String matchId(Path record, String directory) {
-        try {
-            JsonNode node = json.readTree(record.toFile());
-            JsonNode id = node == null ? null : node.get("id");
-            JsonNode dir = node == null ? null : node.get("directory");
-            if (id == null || dir == null || !directory.equals(dir.asText())) {
-                return null;
-            }
-            return id.asText();
-        } catch (IOException e) {
-            return null;
-        }
-    }
-
-    /** The record's last-modified epoch ms, or {@code Long.MIN_VALUE} if unreadable (never wins). */
-    private static long lastModifiedEpochMillis(Path record) {
-        try {
-            return Files.getLastModifiedTime(record).toMillis();
-        } catch (IOException e) {
-            return Long.MIN_VALUE;
-        }
+        log.debug("no opencode session row for directory (root={}, directory={})",
+                storageRoot, directory);
+        return null;
     }
 }

fleetd/src/test/java/dev/ltms/fleet/member/OpenCodeLauncherTest.java

@@ -334,8 +334,7 @@ class OpenCodeLauncherTest {
         assertNull(handle.agentSessionId(), "no record yet → null, not a spawn-time block");
         // Once the record appears (here: same cwd), lazy discovery resolves it — the handle's
         // session id matches its own worktree, not another's.
-        OpenCodeSessionDiscoveryTest.writeRecord(discRoot, "p1", "ses_a.json",
-                "ses_resolved", "/work/dir", 1000L);
+        OpenCodeSessionDiscoveryTest.writeRecord(discRoot, "ses_resolved", "/work/dir", 1000L);
         assertEquals("ses_resolved", handle.agentSessionId(),
                 "agentSessionId() re-scans and picks up a record that has since been written");
     }

fleetd/src/test/java/dev/ltms/fleet/member/OpenCodeSessionDiscoveryTest.java

@@ -5,68 +5,89 @@ import org.junit.jupiter.api.io.TempDir;
 
 import java.nio.file.Files;
 import java.nio.file.Path;
-import java.nio.file.attribute.FileTime;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
+import java.sql.Statement;
 
 import static org.junit.jupiter.api.Assertions.*;
 
 /**
- * {@link OpenCodeSessionDiscovery} matches an opencode session record by the worker's cwd (its
- * {@code directory}) against opencode's on-disk storage. These tests populate a TEMP storage root
- * themselves — never the operator's real {@code ~/.local/share/opencode}.
+ * {@link OpenCodeSessionDiscovery} matches an opencode session row by the worker's cwd (its
+ * {@code directory}) against opencode's {@code opencode.db} SQLite database. These tests build a
+ * SYNTHETIC database themselves, in a JUnit temp directory — never the operator's real
+ * {@code ~/.local/share/opencode/opencode.db}, which a live opencode process may be writing.
  */
 class OpenCodeSessionDiscoveryTest {
 
     /**
-     * Write a session record {@code {"id":..., "directory":...}} under
-     * {@code <root>/session/<projectID>/<fileName>} and stamp it with a known last-modified time,
-     * so "most recently modified wins" is deterministic. Static so the launcher test can reuse it.
+     * Create {@code <root>/opencode.db} with a minimal {@code session} table (just the columns
+     * {@link OpenCodeSessionDiscovery} reads: {@code id}, {@code directory}, {@code time_updated})
+     * and insert one row. Static so {@link OpenCodeLauncherTest} can reuse it.
      */
-    static void writeRecord(Path root, String projectId, String fileName, String id,
-                            String directory, long lastModifiedEpochMillis) throws Exception {
-        Path dir = root.resolve("session").resolve(projectId);
-        Files.createDirectories(dir);
-        Path file = dir.resolve(fileName);
-        Files.writeString(file, "{\"id\":\"" + id + "\",\"directory\":\"" + directory
-                + "\",\"projectID\":\"" + projectId + "\",\"version\":\"1.1.31\"}");
-        Files.setLastModifiedTime(file, FileTime.fromMillis(lastModifiedEpochMillis));
+    static void writeRecord(Path root, String id, String directory, long timeUpdated) throws Exception {
+        Path db = root.resolve("opencode.db");
+        try (Connection connection = DriverManager.getConnection("jdbc:sqlite:" + db)) {
+            try (Statement statement = connection.createStatement()) {
+                statement.execute("CREATE TABLE IF NOT EXISTS session ("
+                        + "id TEXT PRIMARY KEY, directory TEXT, time_updated INTEGER)");
+            }
+            // Bound parameters, not string interpolation: the class under test uses a
+            // PreparedStatement, and a hand-escaped INSERT here is a pattern someone copies out.
+            try (PreparedStatement insert = connection.prepareStatement(
+                    "INSERT INTO session (id, directory, time_updated) VALUES (?, ?, ?)")) {
+                insert.setString(1, id);
+                insert.setString(2, directory);
+                insert.setLong(3, timeUpdated);
+                insert.executeUpdate();
+            }
+        }
     }
 
     @Test
-    void findsTheRecordWhoseDirectoryEqualsTheCwd(@TempDir Path root) throws Exception {
-        writeRecord(root, "p1", "ses_a.json", "ses_aaa", "/w/a", 1000L);
-        writeRecord(root, "p2", "ses_b.json", "ses_bbb", "/w/b", 2000L);
+    void findsTheRowWhoseDirectoryEqualsTheCwd(@TempDir Path root) throws Exception {
+        writeRecord(root, "ses_aaa", "/w/a", 1000L);
+        writeRecord(root, "ses_bbb", "/w/b", 2000L);
 
         assertEquals("ses_bbb", new OpenCodeSessionDiscovery(root).sessionIdForDirectory("/w/b"),
-                "the record whose directory equals the cwd is the one found");
+                "the row whose directory equals the cwd is the one found");
         assertEquals("ses_aaa", new OpenCodeSessionDiscovery(root).sessionIdForDirectory("/w/a"));
     }
 
     @Test
     void aNonMatchingDirectoryYieldsNullRatherThanAMismatch(@TempDir Path root) throws Exception {
-        writeRecord(root, "p1", "ses_a.json", "ses_aaa", "/w/a", 1000L);
+        writeRecord(root, "ses_aaa", "/w/a", 1000L);
 
         assertNull(new OpenCodeSessionDiscovery(root).sessionIdForDirectory("/w/other"),
-                "no record for this cwd yet → null, not a wrong session");
+                "no row for this cwd yet → null, not a wrong session");
     }
 
     @Test
-    void prefersTheMostRecentlyModifiedRecordWhenSeveralMatch(@TempDir Path root) throws Exception {
-        writeRecord(root, "p1", "old.json", "ses_old", "/w/a", 1000L);
-        writeRecord(root, "p2", "new.json", "ses_new", "/w/a", 5000L);
+    void prefersTheMostRecentlyUpdatedRowWhenSeveralMatch(@TempDir Path root) throws Exception {
+        writeRecord(root, "ses_old", "/w/a", 1000L);
+        writeRecord(root, "ses_new", "/w/a", 5000L);
 
         assertEquals("ses_new", new OpenCodeSessionDiscovery(root).sessionIdForDirectory("/w/a"),
-                "the freshest record for the cwd wins");
+                "the row with the highest time_updated for the cwd wins");
     }
 
     @Test
-    void aMissingOrEmptyStorageRootYieldsNullWithoutThrowing(@TempDir Path root) throws Exception {
-        // Missing: no session dir at all under the root.
+    void aMissingDatabaseYieldsNullWithoutThrowing(@TempDir Path root) {
+        // No opencode.db at all under the root.
         assertNull(new OpenCodeSessionDiscovery(root).sessionIdForDirectory("/w/a"));
+    }
 
-        // Present but empty: a session dir with nothing in it produces no match, not a throw.
-        Path emptyRoot = root.resolve("empty");
-        Files.createDirectories(emptyRoot.resolve("session"));
-        assertNull(new OpenCodeSessionDiscovery(emptyRoot).sessionIdForDirectory("/w/a"));
+    @Test
+    void anEmptyDatabaseYieldsNullWithoutThrowing(@TempDir Path root) throws Exception {
+        Path db = root.resolve("opencode.db");
+        try (Connection connection = DriverManager.getConnection("jdbc:sqlite:" + db);
+             Statement statement = connection.createStatement()) {
+            statement.execute("CREATE TABLE session (id TEXT PRIMARY KEY, directory TEXT, "
+                    + "time_updated INTEGER)");
+        }
+
+        assertNull(new OpenCodeSessionDiscovery(root).sessionIdForDirectory("/w/a"));
     }
 
     @Test
@@ -76,15 +97,41 @@ class OpenCodeSessionDiscoveryTest {
         assertNull(discovery.sessionIdForDirectory("   "));
     }
 
+    /**
+     * The one line standing between fleetd and writing the operator's live {@code opencode.db} —
+     * 841MB, with a running opencode writing it — is {@code config.setReadOnly(true)} in
+     * {@link OpenCodeSessionDiscovery#openReadOnly()}. Delete it and every other test in this class
+     * still passes, so this is the test that guards it.
+     *
+     * <p>It asks the connection to write, and requires a refusal. The obvious alternative — make
+     * the database file unwritable and check the read still works — proves nothing: SQLite silently
+     * downgrades a read-write open of an unwritable file to read-only, so that test passes either
+     * way. It was tried and watched pass with the flag removed.
+     */
     @Test
-    void aMalformedRecordIsSkippedRatherThanFatal(@TempDir Path root) throws Exception {
-        // A record that fails to parse must not abort the scan of its siblings.
-        Path dir = root.resolve("session").resolve("p1");
-        Files.createDirectories(dir);
-        Files.writeString(dir.resolve("broken.json"), "{not valid json");
-        writeRecord(root, "p1", "good.json", "ses_good", "/w/a", 1000L);
+    void theDatabaseIsOpenedReadOnly(@TempDir Path root) throws Exception {
+        writeRecord(root, "ses_aaa", "/w/a", 1000L);
 
-        assertEquals("ses_good", new OpenCodeSessionDiscovery(root).sessionIdForDirectory("/w/a"),
-                "an unreadable record is skipped; a later valid one still matches");
+        try (Connection connection = new OpenCodeSessionDiscovery(root).openReadOnly();
+             Statement statement = connection.createStatement()) {
+            SQLException refused = assertThrows(SQLException.class,
+                    () -> statement.executeUpdate("INSERT INTO session (id, directory, time_updated) "
+                            + "VALUES ('ses_zzz', '/w/z', 1)"),
+                    "the connection must REFUSE a write — opencode is writing this database live");
+            assertTrue(refused.getMessage().toLowerCase().contains("readonly")
+                            || refused.getMessage().toLowerCase().contains("read-only"),
+                    "the refusal must be about read-only, not some other error: " + refused.getMessage());
+        }
+    }
+
+    @Test
+    void aCorruptDatabaseFileYieldsNullWithoutThrowing(@TempDir Path root) throws Exception {
+        // A file at opencode.db that is not a SQLite database at all — the open/query must fail
+        // safe, never fatal to a spawn.
+        Path db = root.resolve("opencode.db");
+        Files.writeString(db, "this is not a sqlite database");
+
+        assertNull(new OpenCodeSessionDiscovery(root).sessionIdForDirectory("/w/a"),
+                "an unreadable database resolves to null, not an exception");
     }
 }