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';`
// signOffMigrations widen a request to anything an agent may ask its
// person for (RFC 0027): the agent's reason, and what the approved call
// answered. result is the answer the agent and the person both read;
// secret holds the fields an operation marks secret, which only the
// asking agent reads, once (TakeSignOffSecret empties it).
var signOffMigrations = []string{
`ALTER TABLE signoff ADD COLUMN reason TEXT NOT NULL DEFAULT ''`,
`ALTER TABLE signoff ADD COLUMN result TEXT NOT NULL DEFAULT ''`,
`ALTER TABLE signoff ADD COLUMN secret TEXT NOT NULL DEFAULT ''`,
}
// SignOffWindow is how long a request waits for its person. Past it the
// request is closed unanswered and nothing runs; the agent asks again.
// RFC 0027: a week, since a request can now be anything the person may
// do, and a person away for a weekend should still find it.
const SignOffWindow = 7 * 24 * time.Hour
// MaxPendingSignOffs is how many requests one agent may have waiting:
// past it a new one is refused, so a looping agent cannot bury its
// person's list (RFC 0027, Limits).
const MaxPendingSignOffs = 20
// ErrTooManySignOffs is a request from an agent already holding
// MaxPendingSignOffs waiting ones.
var ErrTooManySignOffs = errors.New("oltp: this agent already has as many requests waiting as it may")
// 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"`
Reason string `json:"reason,omitempty"`
Result string `json:"-"`
Secret string `json:"-"`
}
// 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, reason, result, secret`
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, &s.Reason, &s.Result, &s.Secret)
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
}
var waiting int
if err := tx.QueryRow(`SELECT count(*) FROM signoff WHERE agent = ? AND state = 'pending'`, s.Agent).Scan(&waiting); err != nil {
return SignOff{}, err
}
if waiting >= MaxPendingSignOffs {
return SignOff{}, ErrTooManySignOffs
}
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, s.Result, s.Secret = "", "", "", ""
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,
s.Reason, s.Result, s.Secret); 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
}
// FinishSignOff records what an approved request's call answered:
// result for the agent and the person, secret for the agent alone.
func (d *DB) FinishSignOff(id, result, secret string) error {
_, err := d.w.Exec(`UPDATE signoff SET result = ?, secret = ? WHERE id = ? AND state = 'approved'`, result, secret, id)
return err
}
// TakeSignOffSecret hands the asking agent the secret part of its
// approved request's answer and forgets it, in one statement, so two
// reads cannot both have it. "" when there is none or it was taken.
func (d *DB) TakeSignOffSecret(id, agent string) (string, error) {
tx, err := d.w.Begin()
if err != nil {
return "", err
}
defer tx.Rollback()
var secret string
err = tx.QueryRow(`SELECT secret FROM signoff WHERE id = ? AND agent = ?`, id, agent).Scan(&secret)
if errors.Is(err, sql.ErrNoRows) || (err == nil && secret == "") {
return "", nil
}
if err != nil {
return "", err
}
if _, err := tx.Exec(`UPDATE signoff SET secret = '' WHERE id = ?`, id); err != nil {
return "", err
}
return secret, tx.Commit()
}