perf: CLI commands hang on startup — full AppLayer init on 430MB database blocks every command #38837

Summary

Every opencode CLI command (including lightweight ones like themes, session list, stats) blocks for 10+ seconds to minutes on startup. The root cause is a combination of:

1. All commands share a heavy initialization path — even commands that don't need a database open the full 430 MB SQLite file, run WAL checkpoint, import 40+ migration files, and initialize 60+ services
2. session commands additionally trigger full InstanceBootstrap — starting LSP, VCS, file watchers, snapshot system for a simple SELECT query (opencode session list hangs for ~2 minutes due to unnecessary full InstanceBootstrap #37435)
3. No index on time_updated — session list queries force full table scans on an already I/O-contended database
4. Unbounded queries — multiple code paths issue SELECT * with no LIMIT

Environment

• macOS, opencode dev branch
• Database: 94 sessions, 3,769 messages, 16,237 parts, 19,739 events
• Database file: 430 MB on disk
• SQLite cache: 64 MB (PRAGMA cache_size = -64000)

Root Cause Analysis

1. All CLI commands share the full AppLayer init

packages/opencode/src/cli/effect-cmd.ts:76-81 — every command handler imports the full AppRuntime:

const { AppRuntime } = await import("@/effect/app-runtime")
await AppRuntime.runPromise(...)

packages/opencode/src/effect/app-runtime.ts:58-109 — AppLayer eagerly initializes 60+ services including Database.node, Auth.node, Account.node, Config.node, Session.node, SessionProjector.node, EventV2Bridge.node, Workspace.node, etc.

2. Database init is the heaviest step

packages/core/src/database/database.ts:22-37 — on every startup:

yield* makeDatabase                                    // ↑ opens 430MB SQLite file
yield* db.run("PRAGMA journal_mode = WAL")             // ↑ set twice (also in sqlite.bun.ts:164)
yield* db.run("PRAGMA synchronous = NORMAL")
yield* db.run("PRAGMA busy_timeout = 5000")
yield* db.run("PRAGMA cache_size = -64000")
yield* db.run("PRAGMA foreign_keys = ON")
yield* db.run("PRAGMA wal_checkpoint(PASSIVE)")        // ♨️ I/O on 430MB DB — EVERY command
yield* DatabaseMigration.apply(db)                     // ♨️ loads 40+ migration files

wal_checkpoint(PASSIVE) is the #1 suspect — it forces SQLite to merge WAL pages into the 430 MB main database file on every process start. This is unnecessary; WAL mode is already enabled and SQLite auto-checkpoints.

3. 40+ migration files imported at module evaluation time

packages/core/src/database/migration.gen.ts:3-43:

export const migrations = (
  await Promise.all([
    import("./migration/20260127222353_familiar_lady_ursula"),
    import("./migration/20260211171708_add_project_commands"),
    // ... 40+ more dynamic imports, all loaded and parsed before ANY query
  ])
).map((module) => module.default)

This await blocks module evaluation for every new process. The migration.apply() function later only checks a completed set in JS — it doesn't need all migration modules loaded upfront.

4. time_updated column has no index

packages/core/src/session/sql.ts:61-65:

// Only these indexes exist:
index("session_project_idx").on(table.project_id),
index("session_workspace_idx").on(table.workspace_id),
index("session_parent_idx").on(table.parent_id),

But listByProject() and listGlobal() both sort by time_updated DESC (session.ts:1003, session.ts:574). Without an index, SQLite does a full table scan.

5. Unbounded queries in multiple code paths

6. session list additionally triggers InstanceBootstrap (see #37435)

packages/opencode/src/cli/cmd/session.ts:70 — SessionListCommand defaults to instance: true, which boots LSP, VCS, file watchers, snapshot system.

Proposed Fixes

P0 — Remove unnecessary WAL checkpoint at startup

File: packages/core/src/database/database.ts:32

- yield* db.run("PRAGMA wal_checkpoint(PASSIVE)")
+ // WAL checkpoint is unnecessary on every startup; SQLite auto-checkpoints.
+ // Only force a checkpoint during explicit cleanup (e.g. before VACUUM).

P1 — Lazy-load migration files

File: packages/core/src/database/migration.gen.ts:3-43

Instead of top-level await Promise.all([import(...), ...]), delay the imports until apply() actually needs to iterate them. Since applyOnly() only uses migration.id for comparison (line 69-79), the import graph can be deferred.

P1 — Add instance: false to session list / stats / lightweight commands

File: packages/opencode/src/cli/cmd/session.ts:70
File: packages/opencode/src/cli/cmd/stats.ts

Follow the pattern already used by db, account, providers, serve, web commands.

P2 — Split AppLayer into lightweight and full variants

Create a LightAppLayer that only includes Database.node + minimal services, for commands that don't need the full runtime. Use instance: false commands to indicate this.

P2 — Add time_updated index

File: packages/core/src/session/sql.ts:61-65

  (table) => [
    index("session_project_idx").on(table.project_id),
    index("session_workspace_idx").on(table.workspace_id),
    index("session_parent_idx").on(table.parent_id),
+   index("session_time_updated_idx").on(table.time_updated),
  ],

P3 — Add default limits to unbounded queries

• stats.ts:83 — add sensible WHERE/LIMIT
• session.ts:830 — add max limit to messages() pagination loop
• core/session.ts:299 — add default limit when input.limit === undefined
• tui/sync.tsx:164 — add explicit limit parameter

Related Issues

• opencode session list hangs for ~2 minutes due to unnecessary full InstanceBootstrap #37435 — same author, session list specific diagnosis (InstanceBootstrap)
• Slow startup #14965 — Slow startup (theme-related blocking)
• Last couple of releases have serious hanging #37241 — Last couple releases have serious hanging
• It starts too slowly #37213 — It starts too slowly
• Desktop app extremely slow/hangs at startup due to massive opencode.global.dat (179MB+) caused by base64-encoded PDF attachments in prompt history #34215 — Desktop app hangs due to massive state file
• Opencode TUI - Hangs on Mac from v1.2.7 #38110 — TUI Hangs on Mac
• Headless opencode run intermittently hangs at startup (log frozen at exactly 1,472 bytes, before any session/API activity) #36763 — Headless opencode run hangs at startup