fleetd #209 follow-up: keep roster() off the resolve path

roster() is the supplier for LeadHeartbeatLoop and FleetHealthMonitor
(both timer-driven) and for placement/exhaustion checks and the
metrics scrape — none of which read agentSessionId. Resolving there
meant every tick could open a lazy-resolving adapter's (opencode's)
on-disk session database once per member whose id was still unknown,
with no bound: a member whose id never appears would pay that cost for
the life of the process.

roster() goes back to its pre-#209 behavior (no resolve, no I/O). A
new rosterResolved() carries the resolve logic, and is used only by
the two surfaces that actually report agentSessionId to a caller:
fleet_list (FleetMcp.listFleet) and the REST roster
(FleetApp.listMembers). fleet_whoami's roster().stream() at
FleetMcp.java:768 does not surface the field, so it stays on the plain
roster(). get(paneId) (fleet_status) and the release() resolve are
caller-driven, not timers, and are unchanged.

Retargeted the roster-facing tests from #209 at rosterResolved(), and
added plainRosterDoesNotResolveAgentSessionId, which pins the split by
asserting the handle's agentSessionId() is not called again by
roster().

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/mcp/FleetMcp.java

@@ -951,7 +951,10 @@ public final class FleetMcp {
                     .sorted(Map.Entry.comparingByValue())
                     .map(e -> leadView(e.getKey(), e.getValue(), live.get(e.getKey()), selfTerm))
                     .toList();
-            List<MemberSession> roster = sessions.roster();
+            // fleetd #209: this is the caller-driven fleet_list read that actually reports
+            // agentSessionId (via memberCapacityView -> SessionManager.rosterView), so it uses the
+            // resolving roster; the heartbeat/health/metrics timers stay on the plain sessions.roster().
+            List<MemberSession> roster = sessions.rosterResolved();
             List<Map<String, Object>> out = roster.stream()
                     .map(s -> memberCapacityView(s, live.get(s.terminalId()), messages, capacity.clock().getAsLong()))
                     .toList();

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/main/java/dev/ltms/fleet/rest/FleetApp.java

@@ -315,7 +315,9 @@ public final class FleetApp {
                 .map(Agent.class::cast)
                 .filter(a -> a.terminalId() != null)
                 .collect(Collectors.toMap(Agent::terminalId, Function.identity(), (_, b) -> b));
-        List<Map<String, Object>> out = sessions.roster().stream()
+        // fleetd #209: this REST roster reports agentSessionId via SessionManager.rosterView, so it
+        // uses the resolving roster read (caller-driven, not a timer) rather than the plain one.
+        List<Map<String, Object>> out = sessions.rosterResolved().stream()
                 .map(s -> SessionManager.rosterView(s, live.get(s.terminalId())))
                 .toList();
         Map<String, Object> body = new LinkedHashMap<>();

fleetd/src/main/java/dev/ltms/fleet/session/MemberSession.java

@@ -89,4 +89,15 @@ public record MemberSession(
         return new MemberSession(paneId, terminalId, profile, role, cwd, ownerTerminal, spawnedAtNanos,
                 nowNanos, turnCount + 1, state, worktree, branch, charterReceipt, agentSessionId);
     }
+
+    /**
+     * Return a copy with {@code agentSessionId} resolved to a non-null value (fleetd #209). Some
+     * adapters (opencode) cannot answer {@link dev.ltms.fleet.peer.PeerHandle#agentSessionId()} at
+     * spawn time — the peer has not persisted its session record yet — so the id is discovered on
+     * a later poll and swapped into the otherwise-immutable session via this wither.
+     */
+    public MemberSession withAgentSessionId(String agentSessionId) {
+        return new MemberSession(paneId, terminalId, profile, role, cwd, ownerTerminal, spawnedAtNanos,
+                lastActivityAtNanos, turnCount, state, worktree, branch, charterReceipt, agentSessionId);
+    }
 }

fleetd/src/main/java/dev/ltms/fleet/session/SessionManager.java

@@ -46,6 +46,15 @@ public final class SessionManager implements TurnListener {
     private final PeerLauncher launcher;
     private final Worktrees worktrees;
     private final ConcurrentHashMap<String /*paneId*/, MemberSession> registry = new ConcurrentHashMap<>();
+    /**
+     * fleetd #209: the live {@link PeerHandle} for every registered pane, retained solely so
+     * {@link #resolveAgentSessionId} can re-poll {@link PeerHandle#agentSessionId()} after spawn.
+     * The handle used to go out of scope at the end of the spawn method, so a launcher that answers
+     * the id lazily (opencode — the on-disk session row is written after the pane is created) could
+     * never be re-asked, and {@code fleet_list}/{@code fleet_spawn resumeSessionId} never saw it.
+     * Populated on every spawn path, removed on {@link #release}.
+     */
+    private final ConcurrentHashMap<String /*paneId*/, PeerHandle> handles = new ConcurrentHashMap<>();
     private final MemberPresence presence;
     private final SecureRandom nonceRandom = new SecureRandom();
     private final AtomicLong nonceSeq = new AtomicLong();
@@ -205,6 +214,7 @@ public final class SessionManager implements TurnListener {
                     handle.charterReceipt(),
                     handle.agentSessionId());
             registry.put(handle.id(), session);
+            handles.put(handle.id(), handle);
             memberLifecycle.acquired(session.role(), session.profile(), session.terminalId());
             log.debug("acquired session id={} terminal={} profile={} owner={}",
                     handle.id(), handle.terminalId(), session.profile(), session.ownerTerminal());
@@ -257,6 +267,10 @@ public final class SessionManager implements TurnListener {
      */
     private void release(String paneId, ReleaseCause cause) {
         MemberSession removed = registry.remove(paneId);
+        // fleetd #209: remove right alongside the registry entry so a released session's handle is
+        // never leaked — but keep the local reference below, so the id can still be resolved for
+        // the ReleaseDetail this teardown notifies with.
+        PeerHandle removedHandle = handles.remove(paneId);
         boolean preserveWorktree = cause == ReleaseCause.SHUTDOWN;
         String snapshotRef = null;
         if (removed != null) {
@@ -303,8 +317,11 @@ public final class SessionManager implements TurnListener {
                 // too, so a failed ticket's detail can point a lead at the same tree to re-dispatch.
                 // CB-584 (issue #65 criterion 5): carry agentSessionId alongside them, so a lead can
                 // also resume the member's conversation, not just re-dispatch onto its files.
-                notifyReleased(new ReleaseDetail(removed.terminalId(), removed.worktree(),
-                        removed.branch(), snapshotRef, removed.agentSessionId()));
+                // fleetd #209: a late-resolving adapter (opencode) may only now have an id — resolve
+                // one last time so a released member's detail carries the id it now has.
+                MemberSession resolved = resolveAgentSessionId(removed, removedHandle);
+                notifyReleased(new ReleaseDetail(resolved.terminalId(), resolved.worktree(),
+                        resolved.branch(), snapshotRef, resolved.agentSessionId()));
             }
         }
         // CB-581: the pane must always stop, even if the dirty check above threw. A session removed
@@ -508,6 +525,7 @@ public final class SessionManager implements TurnListener {
                 handle.charterReceipt(),
                 handle.agentSessionId());
         registry.put(handle.id(), session);
+        handles.put(handle.id(), handle);
         memberLifecycle.acquired(session.role(), session.profile(), session.terminalId());
         log.debug("acquired worktree session id={} terminal={} profile={} branch={} path={}",
                 handle.id(), handle.terminalId(), session.profile(), session.branch(), session.worktree());
@@ -539,16 +557,73 @@ public final class SessionManager implements TurnListener {
         return launcher.defaultProfile();
     }
 
-    /** The session for {@code paneId}, if it is still registered and not released. */
+    /**
+     * The session for {@code paneId}, if it is still registered and not released. fleetd #209:
+     * resolves a still-unknown {@code agentSessionId} against the retained handle before returning,
+     * so {@code fleet_status} sees an id a lazy-resolving adapter has since written.
+     */
     public Optional<MemberSession> get(String paneId) {
-        return Optional.ofNullable(registry.get(paneId));
+        return Optional.ofNullable(registry.get(paneId)).map(this::resolveAgentSessionId);
     }
 
-    /** Fleet-owned roster: all registered sessions (acquired minus released). */
+    /**
+     * Fleet-owned roster: all registered sessions (acquired minus released). Deliberately does
+     * <strong>not</strong> resolve {@code agentSessionId} (fleetd #209 follow-up) — this is the
+     * roster supplier on the heartbeat and health-tick timers ({@code LeadHeartbeatLoop},
+     * {@code FleetHealthMonitor} in {@code Fleetd}), on the placement/exhaustion paths, and on the
+     * metrics scrape ({@code FleetMetrics}), all called far more often than any caller actually
+     * reads {@code agentSessionId}. Resolving here would mean every tick opens a lazy-resolving
+     * adapter's (opencode's) on-disk session store once per member whose id is still unknown — and
+     * for a member whose id never appears, that cost never stops, for the life of the process. Use
+     * {@link #rosterResolved()} instead wherever the id must be current.
+     */
     public List<MemberSession> roster() {
         return List.copyOf(registry.values());
     }
 
+    /**
+     * {@link #roster()}, with each session's still-unknown {@code agentSessionId} re-resolved
+     * against its retained handle (fleetd #209) — so a caller that actually reports the id (
+     * {@code fleet_list}, the REST roster) sees one a lazy-resolving adapter (opencode) has since
+     * written, rather than the null frozen in at spawn time. Reserved for caller-driven reads, not
+     * timers: see {@link #roster()}'s javadoc for why the plain roster must stay non-resolving.
+     */
+    public List<MemberSession> rosterResolved() {
+        return registry.values().stream().map(this::resolveAgentSessionId).toList();
+    }
+
+    /**
+     * Resolve {@code session}'s {@code agentSessionId} if still unknown, re-polling the retained
+     * {@link PeerHandle} for this pane (fleetd #209). A no-op — returning {@code session} unchanged
+     * — once the id is already known, once no handle is retained for this pane (never spawned, or
+     * already released), or if the handle throws while answering. A resolved id is best-effort
+     * CAS-swapped into the registry via {@link #replace}; a lost race just means another caller
+     * already applied the same update, so the resolved value is returned either way.
+     */
+    private MemberSession resolveAgentSessionId(MemberSession session) {
+        return resolveAgentSessionId(session, handles.get(session.paneId()));
+    }
+
+    private MemberSession resolveAgentSessionId(MemberSession session, PeerHandle handle) {
+        if (session.agentSessionId() != null || handle == null) {
+            return session;
+        }
+        String resolved;
+        try {
+            resolved = handle.agentSessionId();
+        } catch (RuntimeException e) {
+            log.debug("agentSessionId lookup failed for pane={} terminal={}: {}",
+                    session.paneId(), session.terminalId(), e.toString());
+            return session;
+        }
+        if (resolved == null) {
+            return session;
+        }
+        MemberSession updated = session.withAgentSessionId(resolved);
+        replace(session, updated); // best-effort; a lost CAS just means the resolved value stands anyway
+        return updated;
+    }
+
     /**
      * CB-304 merged roster+live view. The registry is authoritative for worktree, branch,
      * profile, owner, and state; the optional live agent supplies the herdr-reported status.

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");
     }
 }

fleetd/src/test/java/dev/ltms/fleet/session/SessionManagerTest.java

@@ -931,4 +931,235 @@ class SessionManagerTest {
         assertTrue(e.getMessage().contains("SESSION_RESUME"), e.getMessage());
         assertTrue(e.getMessage().contains("stub-profile"), e.getMessage());
     }
+
+    // ── fleetd #209: agentSessionId resolved lazily against the retained handle ─────────────────
+    //
+    // The opencode adapter cannot answer PeerHandle.agentSessionId() at spawn time — the on-disk
+    // session row is written only after the pane is live — so the id must be re-polled on a LATER
+    // call, against the SAME handle instance the launcher returned at spawn. SessionManager used
+    // to let that handle go out of scope at the end of the spawn method, so no caller ever re-asked
+    // it and fleet_list/fleet_status never saw the id. LazyIdHandle below reproduces exactly that
+    // shape: null on the first N calls (the spawn-time call included), a real id after.
+
+    /**
+     * A {@link PeerHandle} whose {@link #agentSessionId()} answers {@code null} for its first
+     * {@code nullCalls} invocations, then either a fixed id or a configured throw on every call
+     * after that — the shape of the opencode bug (fleetd #209): the session row is not written
+     * until after the pane is live, so early polls come back empty and a later one finds it.
+     */
+    private static final class LazyIdHandle implements PeerHandle {
+        private final String id;
+        private final String terminalId;
+        private final int nullCalls;
+        private final String resolvedId;
+        private final java.util.concurrent.atomic.AtomicInteger calls =
+                new java.util.concurrent.atomic.AtomicInteger();
+        private volatile RuntimeException throwAfter;
+
+        LazyIdHandle(String id, String terminalId, int nullCalls, String resolvedId) {
+            this.id = id;
+            this.terminalId = terminalId;
+            this.nullCalls = nullCalls;
+            this.resolvedId = resolvedId;
+        }
+
+        /** After the null calls are exhausted, throw instead of answering the resolved id. */
+        LazyIdHandle throwing(RuntimeException e) {
+            this.throwAfter = e;
+            return this;
+        }
+
+        @Override
+        public String id() {
+            return id;
+        }
+
+        @Override
+        public String terminalId() {
+            return terminalId;
+        }
+
+        @Override
+        public String agentSessionId() {
+            int n = calls.incrementAndGet();
+            if (n <= nullCalls) {
+                return null;
+            }
+            if (throwAfter != null) {
+                throw throwAfter;
+            }
+            return resolvedId;
+        }
+
+        @Override
+        public CharterReceipt charterReceipt() {
+            return null;
+        }
+
+        int callCount() {
+            return calls.get();
+        }
+    }
+
+    /** A minimal {@link PeerLauncher} that hands out pre-built {@link LazyIdHandle}s, one per spawn. */
+    private static final class LazyIdLauncher implements PeerLauncher {
+        private final java.util.Deque<LazyIdHandle> queued = new java.util.ArrayDeque<>();
+
+        LazyIdLauncher queue(LazyIdHandle handle) {
+            queued.add(handle);
+            return this;
+        }
+
+        @Override
+        public Set<Capability> capabilities() {
+            return Set.of(Capability.WORKTREE, Capability.SESSION_RESUME);
+        }
+
+        @Override
+        public Set<Capability> capabilitiesFor(String profileName) {
+            return capabilities();
+        }
+
+        @Override
+        public PeerHandle spawn(SpawnRequest req) {
+            LazyIdHandle handle = queued.poll();
+            if (handle == null) {
+                throw new IllegalStateException("no queued LazyIdHandle for this spawn");
+            }
+            return handle;
+        }
+
+        @Override
+        public Set<String> profiles() {
+            return Set.of("lazy");
+        }
+
+        @Override
+        public String defaultProfile() {
+            return "lazy";
+        }
+
+        @Override
+        public String effectiveCwd(SpawnRequest req) {
+            return "/cwd";
+        }
+
+        @Override
+        public List<String> parityOverlay(String profileName) {
+            return List.of();
+        }
+
+        @Override
+        public List<?> list() {
+            return List.of();
+        }
+
+        @Override
+        public int reapOrphanWorkers() {
+            return 0;
+        }
+
+        @Override
+        public void stop(String id) {
+        }
+
+        @Override
+        public boolean clearContext(String id) {
+            return false;
+        }
+    }
+
+    @Test
+    void plainRosterDoesNotResolveAgentSessionId() {
+        // fleetd #209 follow-up: roster() sits on the heartbeat/health-tick timers (and the metrics
+        // scrape), so it must never trigger the resolve lookup — for opencode that lookup opens an
+        // on-disk session database, and a member whose id never appears would pay that cost forever.
+        // rosterResolved() is the one to use when a caller actually reports the id.
+        LazyIdHandle handle = new LazyIdHandle("p0", "t0", 1, "oc-session-0");
+        SessionManager sessions = new SessionManager(new LazyIdLauncher().queue(handle));
+        MemberSession acquired = sessions.acquire("lazy", "/cwd", "/caller", null);
+        assertEquals(1, handle.callCount(), "sanity: only the spawn-time call happened so far");
+
+        List<MemberSession> roster = sessions.roster();
+
+        assertEquals(1, roster.size());
+        assertEquals(acquired.paneId(), roster.getFirst().paneId());
+        assertNull(roster.getFirst().agentSessionId(), "the plain roster must not resolve the id");
+        assertEquals(1, handle.callCount(),
+                "roster() must never call agentSessionId() again — it sits on the heartbeat/health timers");
+    }
+
+    @Test
+    void rosterResolvedResolvesALateAgentSessionIdFromTheRetainedHandle() {
+        LazyIdHandle handle = new LazyIdHandle("p1", "t1", 1, "oc-session-1");
+        SessionManager sessions = new SessionManager(new LazyIdLauncher().queue(handle));
+
+        MemberSession acquired = sessions.acquire("lazy", "/cwd", "/caller", null);
+        assertNull(acquired.agentSessionId(),
+                "opencode has not written its session row yet at spawn time");
+
+        List<MemberSession> roster = sessions.rosterResolved();
+        assertEquals(1, roster.size());
+        assertEquals("oc-session-1", roster.getFirst().agentSessionId(),
+                "fleet_list must see the id once the adapter can answer it");
+
+        Map<String, Object> view = SessionManager.rosterView(roster.getFirst(), null);
+        assertEquals("oc-session-1", view.get("agentSessionId"),
+                "rosterView renders whatever rosterResolved() resolved");
+    }
+
+    @Test
+    void getResolvesALateAgentSessionIdFromTheRetainedHandle() {
+        LazyIdHandle handle = new LazyIdHandle("p2", "t2", 1, "oc-session-2");
+        SessionManager sessions = new SessionManager(new LazyIdLauncher().queue(handle));
+        MemberSession acquired = sessions.acquire("lazy", "/cwd", "/caller", null);
+
+        MemberSession resolved = sessions.get(acquired.paneId()).orElseThrow();
+
+        assertEquals("oc-session-2", resolved.agentSessionId(),
+                "fleet_status (single-session lookup) must also see the late-resolved id");
+    }
+
+    @Test
+    void releaseCarriesALateResolvedAgentSessionIdIntoTheReleaseDetail() {
+        LazyIdHandle handle = new LazyIdHandle("p3", "t3", 1, "oc-session-3");
+        SessionManager sessions = new SessionManager(new LazyIdLauncher().queue(handle));
+        MemberSession acquired = sessions.acquire("lazy", "/cwd", "/caller", null);
+        java.util.List<String> released = new java.util.concurrent.CopyOnWriteArrayList<>();
+        sessions.onRelease(detail -> released.add(detail.agentSessionId()));
+
+        sessions.release(acquired.paneId());
+
+        assertEquals(java.util.List.of("oc-session-3"), released,
+                "a released member's detail carries the id it has since resolved, not the null "
+                        + "frozen in at spawn time");
+    }
+
+    @Test
+    void aThrowingHandleDoesNotBreakRosterResolved() {
+        LazyIdHandle handle = new LazyIdHandle("p4", "t4", 1, "oc-session-4")
+                .throwing(new RuntimeException("sqlite locked"));
+        SessionManager sessions = new SessionManager(new LazyIdLauncher().queue(handle));
+        sessions.acquire("lazy", "/cwd", "/caller", null);
+
+        List<MemberSession> roster = assertDoesNotThrow(sessions::rosterResolved,
+                "a handle that throws resolving its id must not break the roster read");
+
+        assertEquals(1, roster.size());
+        assertNull(roster.getFirst().agentSessionId(), "the id stays unresolved when the lookup throws");
+    }
+
+    @Test
+    void aResolvedAgentSessionIdIsNotLookedUpAgain() {
+        LazyIdHandle handle = new LazyIdHandle("p5", "t5", 1, "oc-session-5");
+        SessionManager sessions = new SessionManager(new LazyIdLauncher().queue(handle));
+        sessions.acquire("lazy", "/cwd", "/caller", null);
+        assertEquals(1, handle.callCount(), "sanity: only the spawn-time call happened so far");
+
+        sessions.rosterResolved();
+        assertEquals(2, handle.callCount(), "the first rosterResolved() read resolves the id");
+
+        sessions.rosterResolved();
+        assertEquals(2, handle.callCount(), "once resolved, the id must not be looked up again");
+    }
 }