443 lines
17 KiB
Go
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
|
|
}
|