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 ''`,
// lapse names, not secretly, the credentials an approved request
// minted (a JSON list of repo and token id), so that one nobody
// collected can be revoked when its secret lapses.
`ALTER TABLE signoff ADD COLUMN lapse TEXT NOT NULL DEFAULT ''`,
}
// SignOffSecretLapsed is what the secret column holds once an
// uncollected secret has been cleared.
const SignOffSecretLapsed = "lapsed"
// SignOffSecretLife is how long an approved request's secret waits for
// its asking agent, from the decision. Past it the secret is cleared and
// what it minted is revoked.
const SignOffSecretLife = 24 * time.Hour
// SignOffLapse is one secret cleared uncollected: the request, and the
// credentials it minted.
type SignOffLapse struct {
ID string
Agent string
Repo string
Refs string
}
// 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
}
// signOffSecretCutoff is the one line between collectable and lapsed:
// a secret decided at or after it can be taken, one decided before it
// is due to lapse. Take and the sweep both use it, so no row is both.
func signOffSecretCutoff(now time.Time) string {
return now.UTC().Add(-SignOffSecretLife).Format(time.RFC3339)
}
// SignOffSecretCutoff is that line as a time, for the sweep's call.
func SignOffSecretCutoff(now time.Time) time.Time { return now.Add(-SignOffSecretLife) }
// collectable is the rows a take may hand over at a cutoff.
const collectable = `id = ? AND agent = ? AND secret != '' AND secret != ? AND decided != '' AND decided >= ?`
// TakeSignOffSecret hands the asking agent the secret part of its
// approved request's answer and forgets it, with the lapse references,
// in one statement, so two reads cannot both have it and a collected
// row keeps nothing that could revoke its token later. "" when there is
// none, it was taken, or it is past its limit at now.
func (d *DB) TakeSignOffSecret(id, agent string, now time.Time) (string, error) {
tx, err := d.w.Begin()
if err != nil {
return "", err
}
defer tx.Rollback()
cutoff := signOffSecretCutoff(now)
var secret string
err = tx.QueryRow(`SELECT secret FROM signoff WHERE `+collectable, id, agent, SignOffSecretLapsed, cutoff).Scan(&secret)
if errors.Is(err, sql.ErrNoRows) {
return "", nil
}
if err != nil {
return "", err
}
res, err := tx.Exec(`UPDATE signoff SET secret = '', lapse = '' WHERE `+collectable, id, agent, SignOffSecretLapsed, cutoff)
if err != nil {
return "", err
}
if n, _ := res.RowsAffected(); n != 1 {
return "", nil
}
return secret, tx.Commit()
}
// SetSignOffRefs records which credentials an approved request minted,
// for the sweep to revoke if its secret is never collected.
func (d *DB) SetSignOffRefs(id, refs string) error {
_, err := d.w.Exec(`UPDATE signoff SET lapse = ? WHERE id = ? AND state = 'approved'`, refs, id)
return err
}
// A request's secret is in one of four states by its columns: waiting
// (a secret, references maybe), lapsing (secret = the lapsed marker,
// references still present: its credentials are yet to be revoked),
// done (the marker, no references) and collected (no secret, no
// references). The take and the sweep's claim are each one write on the
// one writer, so whichever commits first wins the row outright and the
// other finds it: a collected row is never revoked, a claimed row is
// never collectable. No clock has to agree for that.
// DueSignOffLapses lists the requests a sweep has work on: waiting ones
// decided before the cutoff (SignOffSecretCutoff of the sweep's clock),
// and lapsing ones a failed or interrupted sweep left, whatever their
// age. It changes nothing.
func (d *DB) DueSignOffLapses(cutoff time.Time) ([]SignOffLapse, error) {
rows, err := d.db.Query(`SELECT id, agent, repo, lapse FROM signoff
WHERE (secret != '' AND secret != ?1 AND decided != '' AND decided < ?2)
OR (secret = ?1 AND lapse != '')`,
SignOffSecretLapsed, cutoff.UTC().Format(time.RFC3339))
if err != nil {
return nil, err
}
defer rows.Close()
var out []SignOffLapse
for rows.Next() {
var l SignOffLapse
if err := rows.Scan(&l.ID, &l.Agent, &l.Repo, &l.Refs); err != nil {
return nil, err
}
out = append(out, l)
}
return out, rows.Err()
}
// ClaimSignOffLapse takes a due request for lapsing: its secret is gone
// and no take can ever succeed on it. ok is false when there is nothing
// to revoke, because the secret was collected first (the references went
// with it), the row is not yet due at the cutoff, or it is done. refs
// are the credentials to revoke, still recorded until ClearSignOffLapse.
// A row already lapsing is returned again, so a failed revoke is retried.
func (d *DB) ClaimSignOffLapse(id string, cutoff time.Time) (refs string, ok bool, err error) {
tx, err := d.w.Begin()
if err != nil {
return "", false, err
}
defer tx.Rollback()
var secret, decided string
err = tx.QueryRow(`SELECT secret, lapse, decided FROM signoff WHERE id = ?`, id).Scan(&secret, &refs, &decided)
if errors.Is(err, sql.ErrNoRows) {
return "", false, nil
}
if err != nil {
return "", false, err
}
switch {
case secret == "":
return "", false, nil
case secret == SignOffSecretLapsed:
return refs, refs != "", nil
case decided == "" || decided >= cutoff.UTC().Format(time.RFC3339):
return "", false, nil
}
if _, err := tx.Exec(`UPDATE signoff SET secret = ? WHERE id = ? AND secret != '' AND secret != ?`,
SignOffSecretLapsed, id, SignOffSecretLapsed); err != nil {
return "", false, err
}
return refs, true, tx.Commit()
}
// ClearSignOffLapse finishes a lapsing request once what it minted is
// revoked, dropping the references.
func (d *DB) ClearSignOffLapse(id string) error {
_, err := d.w.Exec(`UPDATE signoff SET lapse = '' WHERE id = ? AND secret = ?`, id, SignOffSecretLapsed)
return err
}