package oltp
import (
"crypto/rand"
"database/sql"
"encoding/hex"
"errors"
"time"
)
// signOffSchema holds an agent's requests for its owner's sign-off on a
// deed act (RFC 0021, amended 2026-10-21): an agent never owns a
// repository, so destroying, un-serving or handing one on is its
// owner's to approve. The row is the exact request — the op and the
// input it will run with — so what the owner approves is what runs.
//
// code is the repository's project code when the agent asked: a
// repository deleted and re-created under the same name is not the one
// the owner was asked about, and a sign-off must not reach it.
const signOffSchema = `
CREATE TABLE IF NOT EXISTS signoff (
id TEXT PRIMARY KEY,
agent TEXT NOT NULL,
owner TEXT NOT NULL,
op TEXT NOT NULL,
repo TEXT NOT NULL,
code TEXT NOT NULL,
input TEXT NOT NULL,
summary TEXT NOT NULL,
state TEXT NOT NULL, -- pending | approved | denied | failed
requested TEXT NOT NULL,
decided TEXT NOT NULL DEFAULT '',
err TEXT NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS signoff_by_owner ON signoff (owner, state, requested DESC);
CREATE UNIQUE INDEX IF NOT EXISTS signoff_one_open ON signoff (agent, op, repo, input) WHERE state = 'pending';`
// SignOffWindow is how long a request waits for its owner. Past it the
// request is closed unanswered and nothing runs; the agent asks again.
const SignOffWindow = 24 * time.Hour
// SignOff is one request for an owner's sign-off.
type SignOff struct {
ID string `json:"id"`
Agent string `json:"agent"`
Owner string `json:"owner"`
Op string `json:"op"`
Repo string `json:"repo"`
Code string `json:"-"`
Input string `json:"-"`
Summary string `json:"summary"`
State string `json:"state"`
Requested string `json:"requested"`
Decided string `json:"decided,omitempty"`
Err string `json:"err,omitempty"`
}
// ErrSignOffClosed is a decision on a request that is not pending any
// more: already decided, or past SignOffWindow.
var ErrSignOffClosed = errors.New("oltp: that sign-off request is no longer waiting")
const signOffColumns = `id, agent, owner, op, repo, code, input, summary, state, requested, decided, err`
func scanSignOff(row interface{ Scan(...any) error }) (SignOff, error) {
var s SignOff
err := row.Scan(&s.ID, &s.Agent, &s.Owner, &s.Op, &s.Repo, &s.Code, &s.Input, &s.Summary, &s.State, &s.Requested, &s.Decided, &s.Err)
return s, err
}
func signOffCutoff() string { return time.Now().UTC().Add(-SignOffWindow).Format(time.RFC3339) }
// RequestSignOff records s as pending and returns it with its id. The
// same open request from the same agent returns the one already waiting
// rather than a second, so an agent that retries does not fill its
// owner's list.
func (d *DB) RequestSignOff(s SignOff) (SignOff, error) {
tx, err := d.w.Begin()
if err != nil {
return SignOff{}, err
}
defer tx.Rollback()
cutoff := signOffCutoff()
// A request past its window is closed here as well as on read, so
// the uniqueness of open requests never counts a stale one.
if _, err := tx.Exec(`UPDATE signoff SET state = 'denied', decided = ?, err = 'nobody answered in time'
WHERE state = 'pending' AND requested < ?`, time.Now().UTC().Format(time.RFC3339), cutoff); err != nil {
return SignOff{}, err
}
was, err := scanSignOff(tx.QueryRow(`SELECT `+signOffColumns+` FROM signoff
WHERE agent = ? AND op = ? AND repo = ? AND input = ? AND state = 'pending'`, s.Agent, s.Op, s.Repo, s.Input))
if err == nil {
return was, tx.Commit()
}
if !errors.Is(err, sql.ErrNoRows) {
return SignOff{}, err
}
raw := make([]byte, 12)
if _, err := rand.Read(raw); err != nil {
return SignOff{}, err
}
s.ID = hex.EncodeToString(raw)
s.State = "pending"
s.Requested = time.Now().UTC().Format(time.RFC3339)
s.Decided, s.Err = "", ""
if _, err := tx.Exec(`INSERT INTO signoff (`+signOffColumns+`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
s.ID, s.Agent, s.Owner, s.Op, s.Repo, s.Code, s.Input, s.Summary, s.State, s.Requested, s.Decided, s.Err); err != nil {
return SignOff{}, err
}
return s, tx.Commit()
}
// SignOffsFor is owner's requests, waiting ones first, then the most
// recently decided, at most limit in all. A request past its window
// reads as closed.
func (d *DB) SignOffsFor(owner string, limit int) ([]SignOff, error) {
if limit <= 0 || limit > 200 {
limit = 50
}
rows, err := d.db.Query(`SELECT `+signOffColumns+` FROM signoff WHERE owner = ?
ORDER BY (state = 'pending' AND requested >= ?) DESC, requested DESC LIMIT ?`, owner, signOffCutoff(), limit)
if err != nil {
return nil, err
}
defer rows.Close()
cutoff := signOffCutoff()
var out []SignOff
for rows.Next() {
s, err := scanSignOff(rows)
if err != nil {
return nil, err
}
if s.State == "pending" && s.Requested < cutoff {
s.State, s.Err = "denied", "nobody answered in time"
}
out = append(out, s)
}
return out, rows.Err()
}
// SignOffByID is one request, as stored.
func (d *DB) SignOffByID(id string) (SignOff, bool, error) {
s, err := scanSignOff(d.db.QueryRow(`SELECT `+signOffColumns+` FROM signoff WHERE id = ?`, id))
if errors.Is(err, sql.ErrNoRows) {
return SignOff{}, false, nil
}
return s, err == nil, err
}
// DecideSignOff closes a pending request owner holds: approved or
// denied. Exactly one decision wins: a second, or one past the window,
// or one by anybody but the owner, is ErrSignOffClosed and changes
// nothing, which is what stops a double submit running a request twice.
func (d *DB) DecideSignOff(id, owner string, approve bool) (SignOff, error) {
state := "denied"
if approve {
state = "approved"
}
res, err := d.w.Exec(`UPDATE signoff SET state = ?, decided = ? WHERE id = ? AND owner = ? AND state = 'pending' AND requested >= ?`,
state, time.Now().UTC().Format(time.RFC3339), id, owner, signOffCutoff())
if err != nil {
return SignOff{}, err
}
if n, _ := res.RowsAffected(); n != 1 {
return SignOff{}, ErrSignOffClosed
}
s, _, err := d.SignOffByID(id)
return s, err
}
// FailSignOff records that an approved request did not run, and why.
func (d *DB) FailSignOff(id, why string) error {
_, err := d.w.Exec(`UPDATE signoff SET state = 'failed', err = ? WHERE id = ? AND state = 'approved'`, why, id)
return err
}