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 ''`,
// pickup is the public code (hex SHA-256) of the pickup key the
// asker bound to the request, "" when none: set at ask time and
// never changed (RFC 0030, Credential pickup). collected is when the
// secret was taken, by whichever door took it.
`ALTER TABLE signoff ADD COLUMN pickup TEXT NOT NULL DEFAULT ''`,
`ALTER TABLE signoff ADD COLUMN collected TEXT NOT NULL DEFAULT ''`,
`CREATE UNIQUE INDEX IF NOT EXISTS signoff_by_pickup ON signoff (pickup) WHERE pickup != ''`,
}
// 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
}
// SignOffUnansweredWhy is what a request closed for want of an answer
// reads.
const SignOffUnansweredWhy = "nobody answered in time"
// 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:"-"`
// Pickup is the public code of the pickup key the asker bound to
// this request; Collected is when the secret was taken.
Pickup string `json:"pickup,omitempty"`
Collected string `json:"collected,omitempty"`
}
// ErrPickupBound is a request asked with a pickup code another request
// already holds: one code binds one request.
var ErrPickupBound = errors.New("oltp: that pickup code is already bound to a request")
// ErrPickupMismatch is a request asked again while pending with another
// pickup code than it holds, or with one where it holds none, or none
// where it holds one.
var ErrPickupMismatch = errors.New("oltp: that request is already waiting with a different pickup code")
// 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, pickup, collected`
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, &s.Pickup, &s.Collected)
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. Its pickup code, if any, is the one it was asked with:
// asking again with another changes nothing, so the code the owner
// compared is the code that collects.
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 = ?
WHERE state = 'pending' AND requested < ?`, time.Now().UTC().Format(time.RFC3339), SignOffUnansweredWhy, 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 {
// The same ask again is the same request, with the code it was
// asked with: a retry that disagrees about the code is refused,
// not answered with a request that will not collect as it
// expects (RFC 0030: one code binds one request, and it never
// changes).
if was.Pickup != s.Pickup {
return SignOff{}, ErrPickupMismatch
}
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, s.Collected = "", "", "", "", ""
if s.Pickup != "" {
var held int
if err := tx.QueryRow(`SELECT count(*) FROM signoff WHERE pickup = ?`, s.Pickup).Scan(&held); err != nil {
return SignOff{}, err
}
if held > 0 {
return SignOff{}, ErrPickupBound
}
}
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, s.Pickup, s.Collected); err != nil {
return SignOff{}, err
}
return s, tx.Commit()
}
// SignOffRan is the state of a parked row: a call its caller ran on its
// own authority, whose credential is held for a pickup code. It is not an
// approval, and nobody was asked; it is never waiting on its owner.
const SignOffRan = "ran"
// ParkSignOff records a call that already ran, in state SignOffRan, in
// one transaction: s (agent, owner, op, repo, code, input, summary, the
// pickup code), the answer without its secret, refs (what a lapse
// revokes), and the secret as seal makes it for the new id. The row is
// what the pickup route and the lapse read of an approved ask, though it
// is not one. ErrPickupBound when the code is held.
func (d *DB) ParkSignOff(s SignOff, result, refs string, seal func(id string) (string, error)) (SignOff, error) {
tx, err := d.w.Begin()
if err != nil {
return SignOff{}, err
}
defer tx.Rollback()
var held int
if err := tx.QueryRow(`SELECT count(*) FROM signoff WHERE pickup = ?`, s.Pickup).Scan(&held); err != nil {
return SignOff{}, err
}
if s.Pickup == "" || held > 0 {
return SignOff{}, ErrPickupBound
}
raw := make([]byte, 12)
if _, err := rand.Read(raw); err != nil {
return SignOff{}, err
}
s.ID = hex.EncodeToString(raw)
sealed, err := seal(s.ID)
if err != nil {
return SignOff{}, err
}
now := time.Now().UTC().Format(time.RFC3339)
s.State, s.Requested, s.Decided, s.Err, s.Result, s.Secret, s.Collected = SignOffRan, now, now, "", result, sealed, ""
if _, err := tx.Exec(`INSERT INTO signoff (`+signOffColumns+`, lapse) 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, s.Pickup, s.Collected, refs); err != nil {
return SignOff{}, err
}
s.Secret = ""
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", SignOffUnansweredWhy
}
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, once who
// (the caller's own condition on the row) holds: a secret, not the
// lapsed marker, decided at or after the cutoff.
const collectable = `secret != '' AND secret != ? AND decided != '' AND decided >= ?`
// takeSecret is the one way the secret column is read for handing out.
// It reads the row who selects and, only when the row is collectable at
// now, clears the secret and the lapse references and stamps collected
// in a write whose condition is the read's again, inside one
// transaction on the one writer. So two takes cannot both have the
// value, a collected row keeps nothing that could revoke its token
// later, and a take and the lapse's claim (ClaimSignOffLapse, the same
// writer, the same cutoff) cannot both win. ok is false, with nothing
// changed, when the row is absent, taken, lapsed or not yet decided.
func takeSecret(tx *sql.Tx, now time.Time, who string, args ...any) (secret string, ok bool, err error) {
cond := who + ` AND ` + collectable
args = append(args, SignOffSecretLapsed, signOffSecretCutoff(now))
err = tx.QueryRow(`SELECT secret FROM signoff WHERE `+cond, args...).Scan(&secret)
if errors.Is(err, sql.ErrNoRows) {
return "", false, nil
}
if err != nil {
return "", false, err
}
res, err := tx.Exec(`UPDATE signoff SET secret = '', lapse = '', collected = ? WHERE `+cond,
append([]any{now.UTC().Format(time.RFC3339)}, args...)...)
if err != nil {
return "", false, err
}
if n, _ := res.RowsAffected(); n != 1 {
return "", false, nil
}
return secret, true, nil
}
// TakeSignOffSecret hands the asking agent the secret part of its
// approved request's answer and forgets it, with the lapse references,
// in one take (takeSecret), so two reads cannot both have it. "" when
// there is none, it was taken, or it is past its limit at now. A request
// bound to a pickup code is never handed out here: its one way out is
// TakePickupSecret (RFC 0030, decision 13).
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()
secret, ok, err := takeSecret(tx, now, `id = ? AND agent = ? AND pickup = ''`, id, agent)
if err != nil || !ok {
return "", err
}
return secret, tx.Commit()
}
// SignOffByPickup is the request bound to a pickup code, as stored.
// code is the public hash, never a key.
func (d *DB) SignOffByPickup(code string) (SignOff, bool, error) {
if code == "" {
return SignOff{}, false, nil
}
s, err := scanSignOff(d.db.QueryRow(`SELECT `+signOffColumns+` FROM signoff WHERE pickup = ?`, code))
if errors.Is(err, sql.ErrNoRows) {
return SignOff{}, false, nil
}
return s, err == nil, err
}
// PickupTake is what one attempt to collect by pickup code found.
type PickupTake struct {
// Found is whether any request holds the code. Request is the row
// as read, its Secret set only when Taken.
Found bool
Taken bool
Request SignOff
// Lapsed is whether an uncollected secret is past its limit at now,
// swept yet or not.
Lapsed bool
}
// TakePickupSecret collects by pickup code: the same take as the asking
// agent's (takeSecret), selecting the row by its code. When nothing can
// be handed out, the row is returned for the caller to say why, secret
// blanked.
func (d *DB) TakePickupSecret(code string, now time.Time) (PickupTake, error) {
if code == "" {
return PickupTake{}, nil
}
tx, err := d.w.Begin()
if err != nil {
return PickupTake{}, err
}
defer tx.Rollback()
secret, ok, err := takeSecret(tx, now, `pickup = ?`, code)
if err != nil {
return PickupTake{}, err
}
s, err := scanSignOff(tx.QueryRow(`SELECT `+signOffColumns+` FROM signoff WHERE pickup = ?`, code))
if errors.Is(err, sql.ErrNoRows) {
return PickupTake{}, nil
}
if err != nil {
return PickupTake{}, err
}
out := PickupTake{Found: true, Taken: ok, Request: s}
if ok {
out.Request.Secret = secret
return out, tx.Commit()
}
out.Lapsed = s.Secret == SignOffSecretLapsed ||
(s.Secret != "" && s.Decided != "" && s.Decided < signOffSecretCutoff(now))
out.Request.Secret = ""
return out, nil
}
// 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
}