go-sqlite-db-split-workspace-lookup

Answer

Root cause. The service keeps two separate sqlite files — cron.db (owns cron_jobs) and workspaces.db (owns workspaces). serve.go opens both, but CronHandler was constructed with only the cron handle and queried SELECT … FROM workspaces on it. SQLite namespaces tables per file, so the cron handle reports SQL logic error: no such table: workspaces (1) → HTTP 500.

Fix — three parts:

1. Handler gains a workspaceDB field (cron.go):

type CronHandler struct {
    db          *sql.DB // cron.db — owns cron_jobs
    workspaceDB *sql.DB // workspaces.db — shared, opened earlier in serve.go
}

func NewCronHandler(db, workspaceDB *sql.DB) *CronHandler {
    return &CronHandler{db: db, workspaceDB: workspaceDB}
}

2. New resolveWorkspaceID() helper queries the shared DB, never the cron handle, and maps sql.ErrNoRows to a sentinel:

var errWorkspaceNotFound = errors.New("workspace not found")

func (h *CronHandler) resolveWorkspaceID(ctx context.Context, slug string) (int64, error) {
    var id int64
    err := h.workspaceDB.QueryRowContext(ctx,
        `SELECT id FROM workspaces WHERE slug = ?`, slug).Scan(&id)
    if errors.Is(err, sql.ErrNoRows) {
        return 0, errWorkspaceNotFound
    }
    return id, err
}

RunDueJobs now calls it: wsID, err := h.resolveWorkspaceID(ctx, j.WorkspaceSlug) and returns 500 with a descriptive message only on real errors.

3. Wiring in serve.go — the shared workspaces handle is opened earlier and passed through (works in main() and tests alike):

func serve(cronDBPath, workspacesDBPath string) *CronHandler {
    cronDB := mustOpenDB(cronDBPath)
    workspacesDB := mustOpenDB(workspacesDBPath) // opened earlier, shared by all handlers
    return NewCronHandler(cronDB, workspacesDB)
}

Regression test (cron_test.go) — two real temp sqlite files via t.TempDir(): seeds cron_jobs into cron.db, workspaces into workspaces.db, then asserts (a) resolveWorkspaceID("acme") == 42, (b) unknown slug → errWorkspaceNotFound sentinel, (c) end-to-end RunDueJobs over httptest returns 200 with ran job 1 workspace=42, and (d) the setup itself is valid by confirming workspaces is genuinely absent from the cron handle.