go-sqlite-multi-db-wiring

Answer

Root cause. The cron handler owns the cron tables (jobs), but it also resolves a client-supplied workspace name to an id by querying the workspaces table — which lives in a different SQLite file (workspace.db). The pre-fix handler only held its own cron.db connection and ran SELECT id FROM workspaces ... against it, producing no such table: workspaces → every /cron request 500ed on a fresh data dir.

Fix. Wire the shared workspace DB connection into CronHandler (constructor now takes workspaceDB *sql.DB), extract a resolveWorkspaceID() helper returning (id, status, msg), and make it nil-safe so handlers constructed without a workspace DB (tests) get a clean 404 instead of a panic.

// cron.go
type CronHandler struct {
    cronDB      *sql.DB
    workspaceDB *sql.DB // shared workspace.db connection (was missing)
}

// NewCronHandler wires both databases. workspaceDB may be nil; see
// resolveWorkspaceID for the nil-safe behaviour.
func NewCronHandler(cronDB, workspaceDB *sql.DB) *CronHandler {
    return &CronHandler{cronDB: cronDB, workspaceDB: workspaceDB}
}

// resolveWorkspaceID resolves the workspace named in the request to its id.
// Returns (id, status, msg): status==0 and msg=="" on success; on failure
// status is the HTTP status to send and msg is the error body.
func (h *CronHandler) resolveWorkspaceID(r *http.Request) (id int64, status int, msg string) {
    name := r.URL.Query().Get("workspace")
    if name == "" {
        return 0, http.StatusBadRequest, "missing workspace query parameter"
    }
    if h.workspaceDB == nil { // nil-safe for tests wired without workspace.db
        return 0, http.StatusNotFound, "workspace store not available"
    }
    err := h.workspaceDB.QueryRowContext(r.Context(),
        `SELECT id FROM workspaces WHERE name = ?`, name).Scan(&id)
    switch {
    case errors.Is(err, sql.ErrNoRows):
        return 0, http.StatusNotFound, "workspace not found: " + name
    case err != nil:
        log.Printf("cron: resolve workspace %q: %v", name, err)
        return 0, http.StatusInternalServerError, "failed to resolve workspace"
    }
    return id, 0, ""
}

// ServeHTTP resolves the workspace against workspace.db, then reads the jobs
// from cron.db.
func (h *CronHandler) ServeHTTP(w http.ResponseWriter, r *http.Request) {
    id, status, msg := h.resolveWorkspaceID(r)
    if status != 0 {
        http.Error(w, msg, status)
        return
    }
    rows, err := h.cronDB.QueryContext(r.Context(),
        `SELECT schedule FROM jobs WHERE workspace_id = ? ORDER BY schedule`, id)
    // ... scan into jobs, write JSON {"workspace_id": id, "jobs": [...]}
}

Main wiring — each handler gets the connection that owns the table it touches:

workspaceDB, _ := openDB(filepath.Join(dataDir, "workspace.db")) // has workspaces
cronDB,       _ := openDB(filepath.Join(dataDir, "cron.db"))    // has jobs
createWorkspaceSchema(workspaceDB)
createCronSchema(cronDB)
mux.Handle("/cron", NewCronHandler(cronDB, workspaceDB))

Regression test (cron_test.go):

func TestCronHandler_SeparateWorkspaceDB(t *testing.T) {
    // Two real, separate SQLite files.
    wsDB  := mustOpen(t, filepath.Join(t.TempDir(), "workspace.db"))
    cronDB := mustOpen(t, filepath.Join(t.TempDir(), "cron.db"))
    createWorkspaceSchema(wsDB)
    createCronSchema(cronDB)

    // Guard: cron.db must NOT have a workspaces table — proves the two
    // tables genuinely live in two files.
    if err := cronDB.QueryRow(`SELECT COUNT(*) FROM workspaces`).Scan(&n); err == nil {
        t.Fatal("cron.db unexpectedly contains workspaces; not a two-file setup")
    }

    wsDB.Exec(`INSERT INTO workspaces (name) VALUES ('alpha')`)
    cronDB.Exec(`INSERT INTO jobs (workspace_id, schedule) VALUES (1, '0 3 * * *')`)

    h := NewCronHandler(cronDB, wsDB)
    rec := httptest.NewRecorder()
    h.ServeHTTP(rec, httptest.NewRequest("GET", "/cron?workspace=alpha", nil))
    // rec.Code == 200; body decodes to {workspace_id:1, jobs:["0 3 * * *"]}
}