AetherForge/server/internal/db/seer.go

package db

import (
	"encoding/json"
	"time"
)

// SeerNoteRecord is one persisted Seer memory note.
type SeerNoteRecord struct {
	ID        int64  `json:"id"`
	AgentID   string `json:"agent_id,omitempty"`
	Note      string `json:"note"`
	Source    string `json:"source"`
	Timestamp string `json:"ts"`
}

// SeerEventRecord is one persisted Seer feed event.
type SeerEventRecord struct {
	ID        int64           `json:"id"`
	EventType string          `json:"event_type"`
	AgentID   string          `json:"agent_id,omitempty"`
	Payload   json.RawMessage `json:"payload"`
	Timestamp string          `json:"ts"`
}

func (d *Database) ensureSeerTables() error {
	_, err := d.Exec(`CREATE TABLE IF NOT EXISTS seer_notes (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		agent_id TEXT NOT NULL DEFAULT '',
		note TEXT NOT NULL,
		source TEXT NOT NULL DEFAULT '',
		ts DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
	)`)
	if err != nil {
		return err
	}
	_, _ = d.Exec(`CREATE INDEX IF NOT EXISTS idx_seer_notes_ts ON seer_notes(ts)`)

	_, err = d.Exec(`CREATE TABLE IF NOT EXISTS seer_events (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		event_type TEXT NOT NULL,
		agent_id TEXT NOT NULL DEFAULT '',
		payload TEXT NOT NULL DEFAULT '{}',
		ts DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
	)`)
	if err != nil {
		return err
	}
	_, _ = d.Exec(`CREATE INDEX IF NOT EXISTS idx_seer_events_ts ON seer_events(ts)`)
	_, _ = d.Exec(`CREATE INDEX IF NOT EXISTS idx_seer_events_type ON seer_events(event_type)`)
	return nil
}

// InsertSeerNote appends a Seer memory note.
func (d *Database) InsertSeerNote(agentID, note, source string) error {
	_, err := d.Exec(
		`INSERT INTO seer_notes (agent_id, note, source) VALUES (?, ?, ?)`,
		agentID, note, source,
	)
	return err
}

// ListSeerNotes returns recent notes ordered newest-first.
func (d *Database) ListSeerNotes(limit int) ([]SeerNoteRecord, error) {
	if limit <= 0 {
		limit = 100
	}
	rows, err := d.Query(
		`SELECT id, agent_id, note, source, ts FROM seer_notes ORDER BY id DESC LIMIT ?`,
		limit,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []SeerNoteRecord
	for rows.Next() {
		var rec SeerNoteRecord
		var ts time.Time
		if err := rows.Scan(&rec.ID, &rec.AgentID, &rec.Note, &rec.Source, &ts); err != nil {
			return nil, err
		}
		rec.Timestamp = ts.UTC().Format(time.RFC3339)
		out = append(out, rec)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	if out == nil {
		out = []SeerNoteRecord{}
	}
	return out, nil
}

// ListSeerNotesForPrompt returns notes oldest-first for LLM replay (fleet-wide + agent-specific).
func (d *Database) ListSeerNotesForPrompt(agentID string, limit int) ([]SeerNoteRecord, error) {
	if limit <= 0 {
		limit = 32
	}
	if limit > 64 {
		limit = 64
	}
	rows, err := d.Query(
		`SELECT id, agent_id, note, source, ts FROM seer_notes
		 WHERE agent_id = '' OR agent_id = ?
		 ORDER BY id ASC LIMIT ?`,
		agentID, limit,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []SeerNoteRecord
	for rows.Next() {
		var rec SeerNoteRecord
		var ts time.Time
		if err := rows.Scan(&rec.ID, &rec.AgentID, &rec.Note, &rec.Source, &ts); err != nil {
			return nil, err
		}
		rec.Timestamp = ts.UTC().Format(time.RFC3339)
		out = append(out, rec)
	}
	return out, rows.Err()
}

// InsertSeerEvent persists and returns the new row id.
func (d *Database) InsertSeerEvent(eventType, agentID string, payload []byte) (int64, error) {
	if len(payload) == 0 {
		payload = []byte("{}")
	}
	res, err := d.Exec(
		`INSERT INTO seer_events (event_type, agent_id, payload) VALUES (?, ?, ?)`,
		eventType, agentID, string(payload),
	)
	if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

// ListSeerEvents returns recent Seer feed events.
func (d *Database) ListSeerEvents(limit int) ([]SeerEventRecord, error) {
	if limit <= 0 {
		limit = 100
	}
	rows, err := d.Query(
		`SELECT id, event_type, agent_id, payload, ts FROM seer_events ORDER BY id DESC LIMIT ?`,
		limit,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []SeerEventRecord
	for rows.Next() {
		var rec SeerEventRecord
		var payload string
		var ts time.Time
		if err := rows.Scan(&rec.ID, &rec.EventType, &rec.AgentID, &payload, &ts); err != nil {
			return nil, err
		}
		rec.Payload = json.RawMessage(payload)
		rec.Timestamp = ts.UTC().Format(time.RFC3339)
		out = append(out, rec)
	}
	return out, rows.Err()
}