core: add db command for database inspection and querying

3 files changed, 71 insertions, 0 deletions

diff --git a/packages/opencode/src/cli/cmd/db.ts b/packages/opencode/src/cli/cmd/db.ts
new file mode 100644
index 000000000..0ade4d3c4
--- /dev/null
+++ b/packages/opencode/src/cli/cmd/db.ts
@@ -0,0 +1,68 @@
+import type { Argv } from "yargs"
+import { spawn } from "child_process"
+import { Database } from "../../storage/db"
+import { Database as BunDatabase } from "bun:sqlite"
+import { UI } from "../ui"
+import { cmd } from "./cmd"
+
+const QueryCommand = cmd({
+  command: "$0 [query]",
+  describe: "open an interactive sqlite3 shell or run a query",
+  builder: (yargs: Argv) => {
+    return yargs
+      .positional("query", {
+        type: "string",
+        describe: "SQL query to execute",
+      })
+      .option("format", {
+        type: "string",
+        choices: ["json", "tsv"],
+        default: "tsv",
+        describe: "Output format",
+      })
+  },
+  handler: async (args: { query?: string; format: string }) => {
+    const query = args.query as string | undefined
+    if (query) {
+      const db = new BunDatabase(Database.Path, { readonly: true })
+      try {
+        const result = db.query(query).all() as Record<string, unknown>[]
+        if (args.format === "json") {
+          console.log(JSON.stringify(result, null, 2))
+        } else if (result.length > 0) {
+          const keys = Object.keys(result[0])
+          console.log(keys.join("\t"))
+          for (const row of result) {
+            console.log(keys.map((k) => row[k]).join("\t"))
+          }
+        }
+      } catch (err) {
+        UI.error(err instanceof Error ? err.message : String(err))
+        process.exit(1)
+      }
+      db.close()
+      return
+    }
+    const child = spawn("sqlite3", [Database.Path], {
+      stdio: "inherit",
+    })
+    await new Promise((resolve) => child.on("close", resolve))
+  },
+})
+
+const PathCommand = cmd({
+  command: "path",
+  describe: "print the database path",
+  handler: () => {
+    console.log(Database.Path)
+  },
+})
+
+export const DbCommand = cmd({
+  command: "db",
+  describe: "database tools",
+  builder: (yargs: Argv) => {
+    return yargs.command(QueryCommand).command(PathCommand).demandCommand()
+  },
+  handler: () => {},
+})
diff --git a/packages/opencode/src/index.ts b/packages/opencode/src/index.ts
index 420ead555..0c4fb5d19 100644
--- a/packages/opencode/src/index.ts
+++ b/packages/opencode/src/index.ts
@@ -26,6 +26,7 @@ import { EOL } from "os"
 import { WebCommand } from "./cli/cmd/web"
 import { PrCommand } from "./cli/cmd/pr"
 import { SessionCommand } from "./cli/cmd/session"
+import { DbCommand } from "./cli/cmd/db"
 import path from "path"
 import { Global } from "./global"
 import { JsonMigration } from "./storage/json-migration"
@@ -138,6 +139,7 @@ const cli = yargs(hideBin(process.argv))
   .command(GithubCommand)
   .command(PrCommand)
   .command(SessionCommand)
+  .command(DbCommand)
   .fail((msg, err) => {
     if (
       msg?.startsWith("Unknown argument") ||
diff --git a/packages/opencode/src/storage/db.ts b/packages/opencode/src/storage/db.ts
index 387e93b37..0974cbe7b 100644
--- a/packages/opencode/src/storage/db.ts
+++ b/packages/opencode/src/storage/db.ts
@@ -25,6 +25,7 @@ export const NotFoundError = NamedError.create(
 const log = Log.create({ service: "db" })
 
 export namespace Database {
+  export const Path = path.join(Global.Path.data, "opencode.db")
   type Schema = typeof schema
   export type Transaction = SQLiteTransaction<"sync", void, Schema>