Files

443 lines
17 KiB
Go

// Package store holds the chat module's SQL: conversations, their turns, what
// each turn streamed, and the actions its agent proposed.
package store
import (
"context"
"database/sql"
"encoding/json/jsontext"
"errors"
"fmt"
"time"
"github.com/google/uuid"
"github.com/jmoiron/sqlx"
)
// ErrNotFound means there is no such session, turn or action.
var ErrNotFound = errors.New("not found")
// Session is one conversation.
type Session struct {
ID uuid.UUID `db:"id"`
Owner string `db:"owner_id"`
Title string `db:"title"`
Tainted bool `db:"tainted"`
CreatedAt time.Time `db:"created_at"`
UpdatedAt time.Time `db:"updated_at"`
}
// Turn is one operator message and the reply to it.
type Turn struct {
Seq int64 `db:"seq"`
ID uuid.UUID `db:"id"`
SessionID uuid.UUID `db:"session_id"`
Owner string `db:"owner_id"`
State string `db:"state"`
Text string `db:"text"`
Page string `db:"page"`
PageLabel string `db:"page_label"`
Reply string `db:"reply"`
Reasoning string `db:"reasoning"`
Error string `db:"error"`
Cancel bool `db:"cancel"`
CreatedAt time.Time `db:"created_at"`
StartedAt sql.NullTime `db:"started_at"`
FinishedAt sql.NullTime `db:"finished_at"`
}
// Event is one thing a turn streamed.
type Event struct {
Seq int64 `db:"seq"`
TurnID uuid.UUID `db:"turn_id"`
Kind string `db:"kind"`
Data jsontext.Value `db:"data"`
CreatedAt time.Time `db:"created_at"`
}
// Action is an action judgement held for approval.
type Action struct {
ID uuid.UUID `db:"id"`
TurnID uuid.UUID `db:"turn_id"`
Owner string `db:"owner_id"`
Key string `db:"key"`
Decision jsontext.Value `db:"decision"`
RequestID uuid.UUID `db:"request_id"`
Path jsontext.Value `db:"path"`
Step int `db:"step"`
StepDue time.Time `db:"step_due"`
State string `db:"state"`
Answer []byte `db:"answer"`
Result string `db:"result"`
// Binding is the record the approval is bound to, and BindingDigest its
// digest. An action stored without them can't be run.
Binding jsontext.Value `db:"binding"`
BindingDigest string `db:"binding_digest"`
}
// Store is the chat module's Postgres store.
type Store struct {
db *sqlx.DB
}
// New returns a store on db.
func New(db *sqlx.DB) *Store { return &Store{db: db} }
const turnColumns = `seq, id, session_id, state, text, page, page_label, reply, reasoning, error, cancel, created_at, started_at, finished_at,
(SELECT s.owner_id FROM chat.sessions s WHERE s.id = chat.turns.session_id) AS owner_id`
const sessionColumns = `id, owner_id, title, tainted, created_at, updated_at`
// owned limits a turn query to the turns of owner's conversations in org.
const owned = `session_id IN (SELECT s.id FROM chat.sessions s WHERE s.org_id = $1 AND s.owner_id = $2)`
// CreateSession stores a new conversation owned by owner.
func (s *Store) CreateSession(ctx context.Context, orgID, owner string, id uuid.UUID) (Session, error) {
var out Session
if err := s.db.GetContext(ctx, &out, `
INSERT INTO chat.sessions (id, org_id, owner_id) VALUES ($1, $2, $3)
RETURNING `+sessionColumns, id, orgID, owner); err != nil {
return Session{}, fmt.Errorf("create session: %w", err)
}
return out, nil
}
// Sessions lists owner's conversations, most recently active first.
func (s *Store) Sessions(ctx context.Context, orgID, owner string, limit int) ([]Session, error) {
var out []Session
if err := s.db.SelectContext(ctx, &out, `
SELECT `+sessionColumns+` FROM chat.sessions
WHERE org_id = $1 AND owner_id = $2 ORDER BY updated_at DESC LIMIT $3`, orgID, owner, limit); err != nil {
return nil, fmt.Errorf("list sessions: %w", err)
}
return out, nil
}
// Session returns one of owner's conversations.
func (s *Store) Session(ctx context.Context, orgID, owner string, id uuid.UUID) (Session, error) {
var out Session
err := s.db.GetContext(ctx, &out, `
SELECT `+sessionColumns+` FROM chat.sessions WHERE org_id = $1 AND owner_id = $2 AND id = $3`, orgID, owner, id)
if errors.Is(err, sql.ErrNoRows) {
return Session{}, ErrNotFound
}
if err != nil {
return Session{}, fmt.Errorf("read session: %w", err)
}
return out, nil
}
// Taint marks one of owner's conversations as having ingested untrusted
// content. It only ever sets the flag; nothing clears it.
func (s *Store) Taint(ctx context.Context, orgID, owner string, id uuid.UUID) error {
res, err := s.db.ExecContext(ctx, `
UPDATE chat.sessions SET tainted = true WHERE org_id = $1 AND owner_id = $2 AND id = $3`, orgID, owner, id)
return affected(res, err, "taint session")
}
// SessionTainted reports whether one of owner's conversations is tainted.
func (s *Store) SessionTainted(ctx context.Context, orgID, owner string, id uuid.UUID) (bool, error) {
var tainted bool
err := s.db.GetContext(ctx, &tainted, `
SELECT tainted FROM chat.sessions WHERE org_id = $1 AND owner_id = $2 AND id = $3`, orgID, owner, id)
if errors.Is(err, sql.ErrNoRows) {
return false, ErrNotFound
}
if err != nil {
return false, fmt.Errorf("read session taint: %w", err)
}
return tainted, nil
}
// Rename sets the title of one of owner's conversations.
func (s *Store) Rename(ctx context.Context, orgID, owner string, id uuid.UUID, title string) error {
res, err := s.db.ExecContext(ctx, `
UPDATE chat.sessions SET title = $4 WHERE org_id = $1 AND owner_id = $2 AND id = $3`, orgID, owner, id, title)
return affected(res, err, "rename session")
}
// TitleIfUntitled sets a title only on a conversation that has none, so a
// generated title never replaces one the operator chose.
func (s *Store) TitleIfUntitled(ctx context.Context, orgID string, id uuid.UUID, title string) error {
if _, err := s.db.ExecContext(ctx, `
UPDATE chat.sessions SET title = $3 WHERE org_id = $1 AND id = $2 AND title = ''`, orgID, id, title); err != nil {
return fmt.Errorf("title session: %w", err)
}
return nil
}
// DeleteSession removes one of owner's conversations with its turns.
func (s *Store) DeleteSession(ctx context.Context, orgID, owner string, id uuid.UUID) error {
res, err := s.db.ExecContext(ctx, `
DELETE FROM chat.sessions WHERE org_id = $1 AND owner_id = $2 AND id = $3`, orgID, owner, id)
return affected(res, err, "delete session")
}
// AddTurn stores a turn as queued in one of owner's conversations and marks
// the conversation active.
func (s *Store) AddTurn(ctx context.Context, orgID, owner string, t Turn) (Turn, error) {
tx, err := s.db.BeginTxx(ctx, nil)
if err != nil {
return Turn{}, fmt.Errorf("begin: %w", err)
}
defer func() { _ = tx.Rollback() }()
res, err := tx.ExecContext(ctx, `
UPDATE chat.sessions SET updated_at = now() WHERE org_id = $1 AND owner_id = $2 AND id = $3`, orgID, owner, t.SessionID)
if err := affected(res, err, "touch session"); err != nil {
return Turn{}, err
}
var out Turn
if err := tx.GetContext(ctx, &out, `
INSERT INTO chat.turns (id, org_id, session_id, state, text, page, page_label)
VALUES ($1, $2, $3, 'queued', $4, $5, $6)
RETURNING `+turnColumns, t.ID, orgID, t.SessionID, t.Text, t.Page, t.PageLabel); err != nil {
return Turn{}, fmt.Errorf("add turn: %w", err)
}
if err := tx.Commit(); err != nil {
return Turn{}, fmt.Errorf("commit turn: %w", err)
}
return out, nil
}
// Turns returns the turns of one of owner's conversations, oldest first.
func (s *Store) Turns(ctx context.Context, orgID, owner string, sessionID uuid.UUID) ([]Turn, error) {
var out []Turn
if err := s.db.SelectContext(ctx, &out, `
SELECT `+turnColumns+` FROM chat.turns WHERE `+owned+` AND session_id = $3 ORDER BY seq`, orgID, owner, sessionID); err != nil {
return nil, fmt.Errorf("list turns: %w", err)
}
return out, nil
}
// Turn returns one turn of owner's conversations.
func (s *Store) Turn(ctx context.Context, orgID, owner string, id uuid.UUID) (Turn, error) {
var out Turn
err := s.db.GetContext(ctx, &out, `SELECT `+turnColumns+` FROM chat.turns WHERE `+owned+` AND id = $3`, orgID, owner, id)
if errors.Is(err, sql.ErrNoRows) {
return Turn{}, ErrNotFound
}
if err != nil {
return Turn{}, fmt.Errorf("read turn: %w", err)
}
return out, nil
}
// TurnOwner returns the owner of the conversation turn id is in, and false
// when id is not a chat turn.
func (s *Store) TurnOwner(ctx context.Context, id uuid.UUID) (string, bool, error) {
var owner string
err := s.db.GetContext(ctx, &owner, `
SELECT s.owner_id FROM chat.turns t JOIN chat.sessions s ON s.id = t.session_id WHERE t.id = $1`, id)
if errors.Is(err, sql.ErrNoRows) {
return "", false, nil
}
if err != nil {
return "", false, fmt.Errorf("find turn: %w", err)
}
return owner, true, nil
}
// Claim starts the oldest queued turn of a conversation with no turn running,
// so a conversation's turns run one at a time in the order they were sent.
func (s *Store) Claim(ctx context.Context) (Turn, bool, error) {
var out Turn
err := s.db.GetContext(ctx, &out, `
UPDATE chat.turns SET state = 'running', started_at = now()
WHERE id = (
SELECT t.id FROM chat.turns t
WHERE t.state = 'queued' AND NOT t.cancel AND NOT EXISTS (
SELECT 1 FROM chat.turns r WHERE r.session_id = t.session_id AND r.state = 'running')
AND NOT EXISTS (
SELECT 1 FROM chat.turns q WHERE q.session_id = t.session_id AND q.state = 'queued' AND q.seq < t.seq)
ORDER BY t.seq LIMIT 1 FOR UPDATE SKIP LOCKED)
RETURNING `+turnColumns)
if errors.Is(err, sql.ErrNoRows) {
return Turn{}, false, nil
}
if err != nil {
return Turn{}, false, fmt.Errorf("claim turn: %w", err)
}
return out, true, nil
}
// Finish ends a running turn with its outcome.
func (s *Store) Finish(ctx context.Context, id uuid.UUID, state, reply, reasoning, failure string) error {
if _, err := s.db.ExecContext(ctx, `
UPDATE chat.turns SET state = $2, reply = $3, reasoning = $4, error = $5, finished_at = now()
WHERE id = $1 AND state IN ('queued', 'running')`, id, state, reply, reasoning, failure); err != nil {
return fmt.Errorf("finish turn: %w", err)
}
return nil
}
// RequestCancel asks one of owner's turns to stop. A queued turn is cancelled
// at once and reported true; a running one stops when its worker sees the
// request.
func (s *Store) RequestCancel(ctx context.Context, orgID, owner string, id uuid.UUID) (bool, error) {
var state string
err := s.db.GetContext(ctx, &state, `
UPDATE chat.turns SET cancel = true,
state = CASE WHEN state = 'queued' THEN 'cancelled' ELSE state END,
finished_at = CASE WHEN state = 'queued' THEN now() ELSE finished_at END
WHERE `+owned+` AND id = $3 RETURNING state`, orgID, owner, id)
if errors.Is(err, sql.ErrNoRows) {
return false, ErrNotFound
}
if err != nil {
return false, fmt.Errorf("cancel turn: %w", err)
}
return state == "cancelled", nil
}
// Interrupted fails every turn left running by a process that stopped, and
// returns them.
func (s *Store) Interrupted(ctx context.Context, reason string) ([]uuid.UUID, error) {
var out []uuid.UUID
if err := s.db.SelectContext(ctx, &out, `
UPDATE chat.turns SET state = 'failed', error = $1, finished_at = now()
WHERE state = 'running' RETURNING id`, reason); err != nil {
return nil, fmt.Errorf("fail interrupted turns: %w", err)
}
return out, nil
}
// History returns a conversation's finished turns before the turn seq, oldest
// first, at most limit of them.
func (s *Store) History(ctx context.Context, sessionID uuid.UUID, before int64, limit int) ([]Turn, error) {
var out []Turn
if err := s.db.SelectContext(ctx, &out, `
SELECT * FROM (
SELECT `+turnColumns+` FROM chat.turns
WHERE session_id = $1 AND seq < $2 AND state IN ('succeeded', 'failed', 'cancelled')
ORDER BY seq DESC LIMIT $3) recent
ORDER BY seq`, sessionID, before, limit); err != nil {
return nil, fmt.Errorf("read history: %w", err)
}
return out, nil
}
// AddEvent records what a turn streamed and returns its position.
func (s *Store) AddEvent(ctx context.Context, turnID uuid.UUID, kind string, data jsontext.Value) (int64, error) {
var seq int64
if err := s.db.GetContext(ctx, &seq, `
INSERT INTO chat.events (turn_id, kind, data) VALUES ($1, $2, $3) RETURNING seq`, turnID, kind, data); err != nil {
return 0, fmt.Errorf("record event: %w", err)
}
return seq, nil
}
// Events returns a turn's events after seq, oldest first.
func (s *Store) Events(ctx context.Context, turnID uuid.UUID, after int64) ([]Event, error) {
var out []Event
if err := s.db.SelectContext(ctx, &out, `
SELECT seq, turn_id, kind, data, created_at FROM chat.events
WHERE turn_id = $1 AND seq > $2 ORDER BY seq`, turnID, after); err != nil {
return nil, fmt.Errorf("list events: %w", err)
}
return out, nil
}
// EventsOf returns the events of the kinds given for a conversation's turns,
// oldest first.
func (s *Store) EventsOf(ctx context.Context, sessionID uuid.UUID, kinds []string) ([]Event, error) {
query, args, err := sqlx.In(`
SELECT e.seq, e.turn_id, e.kind, e.data, e.created_at FROM chat.events e
JOIN chat.turns t ON t.id = e.turn_id
WHERE t.session_id = ? AND e.kind IN (?) ORDER BY e.seq`, sessionID, kinds)
if err != nil {
return nil, err
}
var out []Event
if err := s.db.SelectContext(ctx, &out, s.db.Rebind(query), args...); err != nil {
return nil, fmt.Errorf("list session events: %w", err)
}
return out, nil
}
// PutAction stores an action held for approval unless the turn already has
// one under its key, and returns the stored one either way.
func (s *Store) PutAction(ctx context.Context, orgID string, a Action) (Action, error) {
if _, err := s.db.ExecContext(ctx, `
INSERT INTO chat.actions (id, org_id, turn_id, key, decision, request_id, path, step_due, state, binding, binding_digest)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 'waiting', $9, $10)
ON CONFLICT (turn_id, key) DO NOTHING`,
a.ID, orgID, a.TurnID, a.Key, a.Decision, a.RequestID, a.Path, a.StepDue, a.Binding, a.BindingDigest); err != nil {
return Action{}, fmt.Errorf("record held action: %w", err)
}
return s.actionBy(ctx, `turn_id = $1 AND key = $2`, a.TurnID, a.Key)
}
const actionColumns = `id, turn_id, key, decision, request_id, path, step, step_due, state, answer, result, binding, binding_digest,
(SELECT s.owner_id FROM chat.turns t JOIN chat.sessions s ON s.id = t.session_id WHERE t.id = chat.actions.turn_id) AS owner_id`
func (s *Store) actionBy(ctx context.Context, where string, args ...any) (Action, error) {
var out Action
err := s.db.GetContext(ctx, &out, `SELECT `+actionColumns+` FROM chat.actions WHERE `+where, args...)
if errors.Is(err, sql.ErrNoRows) {
return Action{}, ErrNotFound
}
if err != nil {
return Action{}, fmt.Errorf("read action: %w", err)
}
return out, nil
}
// Answer records the answer to a waiting action under its turn and key, and
// reports whether it was waiting.
func (s *Store) Answer(ctx context.Context, turnID uuid.UUID, key, state string, answer []byte) (bool, error) {
res, err := s.db.ExecContext(ctx, `
UPDATE chat.actions SET state = $3, answer = $4 WHERE turn_id = $1 AND key = $2 AND state = 'waiting'`,
turnID, key, state, answer)
if err != nil {
return false, fmt.Errorf("answer action: %w", err)
}
n, err := res.RowsAffected()
return n > 0, err
}
// Open returns the actions still to settle: waiting for an answer, approved
// and not yet started, or started.
func (s *Store) Open(ctx context.Context) ([]Action, error) {
var out []Action
if err := s.db.SelectContext(ctx, &out, `
SELECT `+actionColumns+` FROM chat.actions WHERE state IN ('waiting', 'approved', 'running') ORDER BY created_at`); err != nil {
return nil, fmt.Errorf("list open actions: %w", err)
}
return out, nil
}
// Move changes an action from one state to another and reports whether it was
// in the first.
func (s *Store) Move(ctx context.Context, id uuid.UUID, from, to, result string) (bool, error) {
res, err := s.db.ExecContext(ctx, `
UPDATE chat.actions SET state = $3, result = $4 WHERE id = $1 AND state = $2`, id, from, to, result)
if err != nil {
return false, fmt.Errorf("move action: %w", err)
}
n, err := res.RowsAffected()
return n > 0, err
}
// Escalated records that a waiting action moved to the next step of its path.
func (s *Store) Escalated(ctx context.Context, id uuid.UUID, step int, due time.Time) error {
if _, err := s.db.ExecContext(ctx, `
UPDATE chat.actions SET step = $2, step_due = $3 WHERE id = $1 AND state = 'waiting'`, id, step, due); err != nil {
return fmt.Errorf("escalate action: %w", err)
}
return nil
}
func affected(res sql.Result, err error, what string) error {
if err != nil {
return fmt.Errorf("%s: %w", what, err)
}
n, err := res.RowsAffected()
if err != nil {
return fmt.Errorf("%s: %w", what, err)
}
if n == 0 {
return ErrNotFound
}
return nil
}