go-sqlite-check-constraint-silent-noop

Answer

The daemon's ticks table carries a CHECK constraint that rejects any outcome outside ('ok','failed'), while status is restricted to ('pending','timeout','done'):

CREATE TABLE ticks (
    id         INTEGER PRIMARY KEY,
    status     TEXT NOT NULL CHECK (status IN ('pending','timeout','done')),
    outcome    TEXT CHECK (outcome IN ('ok','failed')),
    created_at INTEGER NOT NULL,
    deadline   INTEGER NOT NULL
);

Bug 1 (silent no-op): reapZombies wrote outcome='zombie_reaped' on every UPDATE. SQLite rejected each one (CHECK constraint failed: outcome IN ('ok','failed')), and because the daemon swallowed errors, reaping silently no-oped for its whole lifetime. Fix: drop the outcome assignment — status='timeout' only, matching the cleanDanglingOnStartup pattern.

Bug 2 (pool deadlock, exposed once Bug 1 was fixed): the UPDATE ran inside the rows.Next() loop while the SELECT rows were still open. With the daemon's single-connection SQLite pool (SetMaxOpenConns(1)), the only connection is held by the open rows, so Exec blocks forever waiting for a free connection. Fix: collect the zombie IDs first, rows.Close(), then update.

// cleanDanglingOnStartup — the reference pattern: status only, no outcome.
func cleanDanglingOnStartup(ctx context.Context, db *sql.DB, now time.Time) (int64, error) {
    res, err := db.ExecContext(ctx,
        `UPDATE ticks SET status = 'timeout'
         WHERE status = 'pending' AND deadline < ?`, now.Unix())
    if err != nil {
        return 0, err
    }
    return res.RowsAffected()
}

func reapZombies(ctx context.Context, db *sql.DB, now time.Time) (int64, error) {
    rows, err := db.QueryContext(ctx,
        `SELECT id FROM ticks WHERE status = 'pending' AND deadline < ?`, now.Unix())
    if err != nil {
        return 0, err
    }

    // Collect zombie IDs while the SELECT rows are open...
    var ids []int64
    for rows.Next() {
        var id int64
        if err := rows.Scan(&id); err != nil {
            rows.Close()
            return 0, err
        }
        ids = append(ids, id)
    }
    if err := rows.Err(); err != nil {
        rows.Close()
        return 0, err
    }
    rows.Close() // ...then release the only connection BEFORE writing, or Exec blocks forever

    // status='timeout' only. No outcome write: 'zombie_reaped' violates the
    // ticks CHECK constraint, which silently rejected every old UPDATE.
    for _, id := range ids {
        if _, err := db.ExecContext(ctx,
            `UPDATE ticks SET status = 'timeout'
             WHERE id = ? AND status = 'pending'`, id); err != nil {
            return 0, fmt.Errorf("reap tick %d: %w", id, err)
        }
    }
    return int64(len(ids)), nil
}

Key points: no outcome column write (bug 1), and the rows.Close() before any Exec on the single-connection pool (bug 2). The AND status = 'pending' guard keeps reaping idempotent.