Sandbar/internal/sandbar/store.go

package sandbar

import (
	"crypto/rand"
	"crypto/sha256"
	"database/sql"
	"encoding/base64"
	"encoding/hex"
	"encoding/json"
	"errors"
	"os"
	"path/filepath"
	"time"

	"git.veillax.com/Veillax/reef/secretbox"
	_ "modernc.org/sqlite"
)

// Store is Sandbar's one SQLite file (pure Go, WAL mode). Configuration that is
// edited by hand and rarely queried (channels, monitors, schedules, policies,
// incidents) is kept as JSON documents in one table; things that grow or are
// searched (messages, checks, alerts, timeline events) have proper indexed tables.
type Store struct {
	db  *sql.DB
	box *secretbox.Box
}

var errNotFound = errors.New("not found")

const schema = `
CREATE TABLE IF NOT EXISTS users (
	id TEXT PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE COLLATE NOCASE,
	hash TEXT NOT NULL, role TEXT NOT NULL, phone TEXT NOT NULL DEFAULT '', created INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS sessions (
	hash TEXT PRIMARY KEY, user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE, expires INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS apikeys (
	id TEXT PRIMARY KEY, name TEXT NOT NULL, hash TEXT NOT NULL UNIQUE,
	user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE, created INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS docs (
	kind TEXT NOT NULL, id TEXT NOT NULL, body TEXT NOT NULL, PRIMARY KEY (kind, id));
CREATE TABLE IF NOT EXISTS messages (
	id TEXT PRIMARY KEY, topic TEXT NOT NULL, time INTEGER NOT NULL, title TEXT NOT NULL,
	body TEXT NOT NULL, priority INTEGER NOT NULL, tags TEXT NOT NULL);
CREATE INDEX IF NOT EXISTS messages_topic ON messages(topic, time);
CREATE TABLE IF NOT EXISTS checks (
	monitor_id TEXT NOT NULL, time INTEGER NOT NULL, ok INTEGER NOT NULL, ms INTEGER NOT NULL, detail TEXT NOT NULL);
CREATE INDEX IF NOT EXISTS checks_monitor ON checks(monitor_id, time);
-- One row per monitor per UTC day, kept far longer than raw checks: it feeds the status page's uptime bars.
CREATE TABLE IF NOT EXISTS daily (
	monitor_id TEXT NOT NULL, day TEXT NOT NULL, checks INTEGER NOT NULL, fails INTEGER NOT NULL, PRIMARY KEY (monitor_id, day));
-- Website analytics: one row per page view or event. No IP address or user agent is kept, only labels.
CREATE TABLE IF NOT EXISTS hits (
	website TEXT NOT NULL, time INTEGER NOT NULL, visitor TEXT NOT NULL, event TEXT NOT NULL, path TEXT NOT NULL,
	referrer TEXT NOT NULL, browser TEXT NOT NULL, os TEXT NOT NULL, device TEXT NOT NULL, country TEXT NOT NULL,
	lang TEXT NOT NULL, data TEXT NOT NULL);
CREATE INDEX IF NOT EXISTS hits_site ON hits(website, time);
CREATE TABLE IF NOT EXISTS mstate (
	monitor_id TEXT PRIMARY KEY, status TEXT NOT NULL, since INTEGER NOT NULL, last_check INTEGER NOT NULL,
	last_ms INTEGER NOT NULL, fails INTEGER NOT NULL, last_ping INTEGER NOT NULL, detail TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS alerts (
	id TEXT PRIMARY KEY, key TEXT NOT NULL, source TEXT NOT NULL, summary TEXT NOT NULL, detail TEXT NOT NULL,
	severity TEXT NOT NULL, status TEXT NOT NULL, policy_id TEXT NOT NULL, step INTEGER NOT NULL,
	next_at INTEGER NOT NULL, created INTEGER NOT NULL, updated INTEGER NOT NULL, count INTEGER NOT NULL,
	acked_by TEXT NOT NULL DEFAULT '', acked_at INTEGER NOT NULL DEFAULT 0, snooze_until INTEGER NOT NULL DEFAULT 0,
	resolved_at INTEGER NOT NULL DEFAULT 0, incident_id TEXT NOT NULL DEFAULT '');
CREATE INDEX IF NOT EXISTS alerts_key ON alerts(key, status);
CREATE INDEX IF NOT EXISTS alerts_due ON alerts(status, next_at);
-- Web Push registrations, one per installed companion app.
CREATE TABLE IF NOT EXISTS push_subs (
	id TEXT PRIMARY KEY, user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE, endpoint TEXT NOT NULL UNIQUE,
	p256dh TEXT NOT NULL, auth TEXT NOT NULL, topics TEXT NOT NULL, device TEXT NOT NULL, created INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS events (
	id INTEGER PRIMARY KEY AUTOINCREMENT, scope TEXT NOT NULL, ref TEXT NOT NULL, time INTEGER NOT NULL,
	kind TEXT NOT NULL, actor TEXT NOT NULL, text TEXT NOT NULL);
CREATE INDEX IF NOT EXISTS events_ref ON events(scope, ref, time);
-- People who follow the public status page by email or webhook. The address is sealed; key is a hash of it, for lookups.
CREATE TABLE IF NOT EXISTS subscribers (
	id TEXT PRIMARY KEY, kind TEXT NOT NULL, target TEXT NOT NULL, key TEXT NOT NULL UNIQUE,
	confirmed INTEGER NOT NULL, created INTEGER NOT NULL);
`

// OpenStore opens (creating if needed) the database in dir.
func OpenStore(dir string, box *secretbox.Box) (*Store, error) {
	if err := os.MkdirAll(dir, 0o700); err != nil {
		return nil, err
	}
	dsn := "file:" + filepath.ToSlash(filepath.Join(dir, "sandbar.db")) +
		"?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)&_pragma=foreign_keys(1)&_pragma=synchronous(NORMAL)"
	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(4)
	if _, err := db.Exec(schema); err != nil {
		db.Close()
		return nil, err
	}
	// Older databases have no status column on events: public incident posts carry the status they announce.
	db.Exec(`ALTER TABLE events ADD COLUMN status TEXT NOT NULL DEFAULT ''`)
	st := &Store{db: db, box: box}
	st.backfillDaily()
	return st, nil
}

// backfillDaily fills the daily table from the raw checks, but only when it is empty: a database
// from before the table existed. After that every check is counted as it is recorded.
func (s *Store) backfillDaily() {
	var have int
	if s.db.QueryRow(`SELECT count(*) FROM daily`).Scan(&have) == nil && have == 0 {
		s.db.Exec(`INSERT OR IGNORE INTO daily(monitor_id,day,checks,fails)
			SELECT monitor_id, date(time,'unixepoch'), count(*), count(*)-sum(ok) FROM checks GROUP BY monitor_id, date(time,'unixepoch')`)
	}
}

func (s *Store) Close() error { return s.db.Close() }

// ---------- ids and tokens ----------

func newID() string {
	b := make([]byte, 9)
	rand.Read(b)
	return hex.EncodeToString(b)
}

func newToken() string {
	b := make([]byte, 32)
	rand.Read(b)
	return base64.RawURLEncoding.EncodeToString(b)
}

func hashToken(t string) string {
	sum := sha256.Sum256([]byte(t))
	return hex.EncodeToString(sum[:])
}

// ---------- JSON documents ----------

func (s *Store) put(kind, id string, v any) error {
	b, err := json.Marshal(v)
	if err != nil {
		return err
	}
	_, err = s.db.Exec(`INSERT INTO docs(kind,id,body) VALUES(?,?,?) ON CONFLICT(kind,id) DO UPDATE SET body=excluded.body`, kind, id, string(b))
	return err
}

func (s *Store) get(kind, id string, v any) error {
	var body string
	err := s.db.QueryRow(`SELECT body FROM docs WHERE kind=? AND id=?`, kind, id).Scan(&body)
	if errors.Is(err, sql.ErrNoRows) {
		return errNotFound
	}
	if err != nil {
		return err
	}
	return json.Unmarshal([]byte(body), v)
}

// list decodes every document of a kind through add, which is called with a
// fresh pointer-to-value from newItem and then appended by the caller.
func (s *Store) list(kind string, each func(raw []byte) error) error {
	rows, err := s.db.Query(`SELECT body FROM docs WHERE kind=? ORDER BY id`, kind)
	if err != nil {
		return err
	}
	var bodies []string
	for rows.Next() {
		var b string
		if err := rows.Scan(&b); err != nil {
			rows.Close()
			return err
		}
		bodies = append(bodies, b)
	}
	rows.Close()
	for _, b := range bodies {
		if err := each([]byte(b)); err != nil {
			return err
		}
	}
	return nil
}

func (s *Store) del(kind, id string) error {
	res, err := s.db.Exec(`DELETE FROM docs WHERE kind=? AND id=?`, kind, id)
	if err != nil {
		return err
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return errNotFound
	}
	return nil
}

func listOf[T any](s *Store, kind string) ([]T, error) {
	out := []T{}
	err := s.list(kind, func(raw []byte) error {
		var v T
		if err := json.Unmarshal(raw, &v); err != nil {
			return err
		}
		out = append(out, v)
		return nil
	})
	return out, err
}

func getOf[T any](s *Store, kind, id string) (*T, error) {
	var v T
	if err := s.get(kind, id, &v); err != nil {
		return nil, err
	}
	return &v, nil
}

// seal and open wrap secretbox so callers don't repeat the nil check.
func (s *Store) seal(v string) string {
	if s.box == nil || v == "" {
		return v
	}
	return s.box.Seal(v)
}

func (s *Store) open(v string) string {
	if s.box == nil {
		return v
	}
	out, err := s.box.Open(v)
	if err != nil {
		return ""
	}
	return out
}

// ---------- timeline events ----------

func (s *Store) addEvent(scope, ref, kind, actor, text string, at time.Time) {
	s.db.Exec(`INSERT INTO events(scope,ref,time,kind,actor,text) VALUES(?,?,?,?,?,?)`, scope, ref, at.Unix(), kind, actor, text)
}

type Event struct {
	Time  int64  `json:"time"`
	Kind  string `json:"kind"`
	Actor string `json:"actor"`
	Text  string `json:"text"`
	// Status is set on posts only: the incident status the post announces.
	Status string `json:"status,omitempty"`
}

func (s *Store) events(scope, ref string) []Event {
	out := []Event{}
	rows, err := s.db.Query(`SELECT time,kind,actor,text,status FROM events WHERE scope=? AND ref=? ORDER BY time, id`, scope, ref)
	if err != nil {
		return out
	}
	defer rows.Close()
	for rows.Next() {
		var e Event
		if rows.Scan(&e.Time, &e.Kind, &e.Actor, &e.Text, &e.Status) == nil {
			out = append(out, e)
		}
	}
	return out
}

// addPost records a public incident update: the text and status the status page shows.
func (s *Store) addPost(incidentID, status, text string, at time.Time) {
	s.db.Exec(`INSERT INTO events(scope,ref,time,kind,actor,text,status) VALUES('incident',?,?,'post','',?,?)`, incidentID, at.Unix(), text, status)
}

// prune deletes data past its retention.
func (s *Store) prune(now time.Time, messageDays, checkDays, hitDays int) {
	s.db.Exec(`DELETE FROM hits WHERE time < ?`, now.AddDate(0, 0, -hitDays).Unix())
	s.db.Exec(`DELETE FROM messages WHERE time < ?`, now.AddDate(0, 0, -messageDays).Unix())
	s.db.Exec(`DELETE FROM checks WHERE time < ?`, now.AddDate(0, 0, -checkDays).Unix())
	s.db.Exec(`DELETE FROM daily WHERE day < ?`, now.UTC().AddDate(0, 0, -400).Format("2006-01-02"))
	s.db.Exec(`DELETE FROM sessions WHERE expires < ?`, now.Unix())
	s.db.Exec(`DELETE FROM subscribers WHERE confirmed=0 AND created < ?`, now.AddDate(0, 0, -7).Unix())
	s.db.Exec(`DELETE FROM alerts WHERE status='resolved' AND resolved_at < ? AND incident_id=''`, now.AddDate(0, 0, -90).Unix())
}