Skip to content

armgit / Pulse

Public Imported from GitHub
0

Real-time monitoring dashboard for Proxmox VE, PBS, Docker, Kubernetes, TrueNAS and vSphere. Self-hosted, with alerts for failed backups, full disks, unhealthy containers and offline nodes.

30.9 KB
Raw Blame
// Package eventlog is the append-only alert event log: every alert lifecycle
// transition and distinct notification-decision episode — including
// suppressions, with the mechanism that held them — is recorded as one
// immutable event. Re-evaluating an unchanged suppression or deferral does not
// create another episode. It exists so
// "why didn't I get notified?" is answerable from durable data instead of
// being reconstructed from logs (docs/ALERT_ENGINE_EVOLUTION.md, Phase 0).
//
// Delivery-diagnostic appends are additive to the live alert manager: they do
// not block evaluation and may be dropped under sustained pressure. Lifecycle
// events use AppendDurable because alert history is projected from this store.
package eventlog

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"net/url"
	"os"
	"strings"
	"sync"
	"sync/atomic"
	"time"

	pdb "github.com/rcourtman/pulse-go-rewrite/pkg/db"
	"github.com/rs/zerolog/log"
	_ "modernc.org/sqlite"
)

// Event types recorded by the alert manager.
const (
	// TypeFired marks a new occurrence activating; TypeRefired marks a
	// reactivation that resumed a recently resolved occurrence. Both come
	// from the reducer core's explicit activation events, so persisted
	// restores are never misreported as firings.
	TypeFired                  = "fired"
	TypeRefired                = "refired"
	TypeResolved               = "resolved"
	TypeAcknowledged           = "acknowledged"
	TypeUnacknowledged         = "unacknowledged"
	TypeSnoozed                = "snoozed"
	TypeUnsnoozed              = "unsnoozed"
	TypeEscalated              = "escalated"
	TypeFlappingDetected       = "flapping_detected"
	TypeNotificationDispatched = "notification_dispatched"
	TypeNotificationDeferred   = "notification_deferred"
	TypeNotificationSuppressed = "notification_suppressed"
	// TypeShadowDivergence records the shadow reducer disagreeing with the
	// live manager on an alert's state — the always-on parity signal
	// (docs/ALERT_ENGINE_EVOLUTION.md, Phase 1).
	TypeShadowDivergence = "shadow_divergence"
	// TypeHistoryImported carries one legacy JSON-history entry migrated
	// into the log when the log becomes the history authority. Its
	// snapshot is the entry's final state.
	TypeHistoryImported = "history_imported"
	// TypeHistoryCleared is the user's clear-history action. The log stays
	// append-only; the history projection ignores lifecycle events that
	// precede the newest tombstone.
	TypeHistoryCleared = "history_cleared"
)

// Event is one immutable alert event.
type Event struct {
	ID           int64             `json:"id"`
	OccurredAt   time.Time         `json:"occurredAt"`
	Type         string            `json:"type"`
	AlertID      string            `json:"alertId"`
	ResourceID   string            `json:"resourceId,omitempty"`
	ResourceName string            `json:"resourceName,omitempty"`
	AlertType    string            `json:"alertType,omitempty"`
	Level        string            `json:"level,omitempty"`
	Reason       string            `json:"reason,omitempty"`
	Message      string            `json:"message,omitempty"`
	Details      map[string]string `json:"details,omitempty"`
	// Snapshot is the full alert state at the moment of the event, recorded
	// for lifecycle transitions (fired, refired, resolved, acknowledged,
	// unacknowledged, snoozed, unsnoozed) so alert history can be projected
	// from the log alone.
	Snapshot json.RawMessage `json:"snapshot,omitempty"`
}

// Filter narrows a Query. Zero values mean "no constraint".
type Filter struct {
	AlertID string
	Types   []string
	Since   time.Time
	Until   time.Time
	Limit   int
	// AfterID restricts matches to events with a strictly greater durable id.
	// It is the projection-watermark cursor: replay consumers pass the highest
	// id they have fully applied so a walk visits only the un-projected tail
	// instead of the whole log.
	AfterID int64
	// ThroughID fixes an inclusive replay boundary while new events arrive.
	ThroughID int64
}

const (
	defaultQueryLimit = 200
	maxQueryLimit     = 1000
	appendBufferSize  = 1024
	// defaultRetention keeps events long enough to explain a past incident
	// without growing without bound.
	defaultRetention = 90 * 24 * time.Hour
	pruneInterval    = time.Hour
	eventLogFileName = "events.db"
	// maxRetainedSnapshotBytes bounds the total lifecycle-snapshot payload the
	// log keeps. Retention bounds how long an event lives; a single flapping
	// alert can still write hundreds of megabytes of full state snapshots
	// inside that window and fill the host disk. When the stored payload
	// exceeds this cap the oldest events are removed first, exactly as age
	// pruning removes them, so the history projection rebuilds from what
	// remains.
	maxRetainedSnapshotBytes = 256 << 20
)

// Store is the SQLite-backed event log. A nil *Store is valid: Append and
// Close are no-ops and Query returns no events.
type Store struct {
	db        *sql.DB
	readDB    *sql.DB
	dbPath    string
	events    chan Event
	stop      chan struct{}
	wg        sync.WaitGroup
	closeOnce sync.Once
	dropped   atomic.Int64
	appended  atomic.Int64
	written   atomic.Int64
	failed    atomic.Int64
	failureMu sync.RWMutex
	lastError error
	retention time.Duration
	episodeMu sync.Mutex
	episodes  map[string]string
}

func sqliteDSN(path string) string {
	separator := "?"
	if strings.Contains(path, "?") {
		separator = "&"
	}
	return path + separator + url.Values{
		"_pragma": []string{
			"busy_timeout(30000)",
			"journal_mode(WAL)",
			// Lifecycle transactions are restart authority, not disposable
			// telemetry. FULL keeps committed events and their active-state
			// projection durable across host power loss as well as process exit.
			"synchronous(FULL)",
			pdb.PageCachePragma,
		},
	}.Encode()
}

// Open creates or opens the event log inside dir (the manager's alerts data
// directory). The directory must already exist.
func Open(dir string) (*Store, error) {
	trimmed := strings.TrimSpace(dir)
	if trimmed == "" {
		return nil, fmt.Errorf("event log directory is required")
	}
	if info, err := os.Stat(trimmed); err != nil {
		return nil, fmt.Errorf("event log directory: %w", err)
	} else if !info.IsDir() {
		return nil, fmt.Errorf("event log directory %q is not a directory", trimmed)
	}
	dbPath := trimmed + string(os.PathSeparator) + eventLogFileName
	return openDSN(dbPath, sqliteDSN(dbPath))
}

// OpenInMemory creates a non-persistent store with identical behavior, for
// tests and as a fallback owner when on-disk bootstrap fails.
func OpenInMemory() (*Store, error) {
	return openDSN(":memory:", sqliteDSN("file::memory:?mode=memory"))
}

func openDSN(dbPath, dsn string) (*Store, error) {
	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, fmt.Errorf("open alert event log: %w", err)
	}
	// Serialize durable transactions on their own connection. Disk-backed WAL
	// readers use a separate, read-only pool so history cannot occupy the only
	// connection while an alert transition holds the manager's state lock.
	db.SetMaxOpenConns(1)
	db.SetMaxIdleConns(1)
	db.SetConnMaxLifetime(0)

	s := &Store{
		db:        db,
		readDB:    db,
		dbPath:    dbPath,
		events:    make(chan Event, appendBufferSize),
		stop:      make(chan struct{}),
		retention: defaultRetention,
		episodes:  make(map[string]string),
	}
	if err := s.initSchema(); err != nil {
		db.Close()
		return nil, fmt.Errorf("init alert event log schema: %w", err)
	}
	if dbPath != ":memory:" {
		reader, err := sql.Open("sqlite", sqliteDSN(dbPath)+"&mode=ro&_pragma=query_only(1)")
		if err != nil {
			db.Close()
			return nil, fmt.Errorf("open alert event reader: %w", err)
		}
		reader.SetMaxOpenConns(2)
		reader.SetMaxIdleConns(2)
		if err := reader.Ping(); err != nil {
			reader.Close()
			db.Close()
			return nil, fmt.Errorf("initialize alert event reader: %w", err)
		}
		s.readDB = reader
	}

	s.wg.Add(1)
	go s.writeLoop()

	log.Info().Str("dbPath", dbPath).Msg("alert event log initialized")
	return s, nil
}

func (s *Store) initSchema() error {
	_, err := s.db.Exec(`
		CREATE TABLE IF NOT EXISTS alert_events (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			occurred_at TEXT NOT NULL,
			event_type TEXT NOT NULL,
			alert_id TEXT NOT NULL,
			resource_id TEXT NOT NULL DEFAULT '',
			resource_name TEXT NOT NULL DEFAULT '',
			alert_type TEXT NOT NULL DEFAULT '',
			level TEXT NOT NULL DEFAULT '',
			reason TEXT NOT NULL DEFAULT '',
			message TEXT NOT NULL DEFAULT '',
			details TEXT NOT NULL DEFAULT '',
			snapshot TEXT NOT NULL DEFAULT ''
		);
		CREATE INDEX IF NOT EXISTS idx_alert_events_alert ON alert_events(alert_id, occurred_at);
		CREATE INDEX IF NOT EXISTS idx_alert_events_time ON alert_events(occurred_at);
		CREATE INDEX IF NOT EXISTS idx_alert_events_type ON alert_events(event_type, occurred_at);
		CREATE TABLE IF NOT EXISTS alert_active_state (
			alert_id TEXT PRIMARY KEY,
			occurrence_started_at TEXT NOT NULL,
			updated_at TEXT NOT NULL,
			snapshot TEXT NOT NULL
		);
		CREATE TABLE IF NOT EXISTS alert_store_meta (
			key TEXT PRIMARY KEY,
			value TEXT NOT NULL
		);
	`)
	if err != nil {
		return err
	}
	return s.ensureSnapshotColumn()
}

// ensureSnapshotColumn upgrades a pre-snapshot database in place. ALTER TABLE
// ADD COLUMN on SQLite is metadata-only, so the upgrade is cheap and existing
// rows read back with an empty snapshot.
func (s *Store) ensureSnapshotColumn() error {
	rows, err := s.db.Query(`PRAGMA table_info(alert_events)`)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var cid int
		var name, colType string
		var notNull, pk int
		var dflt any
		if err := rows.Scan(&cid, &name, &colType, &notNull, &dflt, &pk); err != nil {
			return err
		}
		if name == "snapshot" {
			return rows.Err()
		}
	}
	if err := rows.Err(); err != nil {
		return err
	}
	_, err = s.db.Exec(`ALTER TABLE alert_events ADD COLUMN snapshot TEXT NOT NULL DEFAULT ''`)
	return err
}

// Append records one event without blocking: when the buffer is full the
// event is dropped and counted, never stalling alert evaluation. Safe to
// call on a nil store and while holding manager locks.
func (s *Store) Append(event Event) {
	if s == nil {
		return
	}
	if event.OccurredAt.IsZero() {
		event.OccurredAt = time.Now()
	}
	if event.AlertID != "" && coalescesUnchangedDeliveryEpisode(event.Type) {
		signature := deliveryEpisodeSignature(event)
		s.episodeMu.Lock()
		if s.episodes[event.AlertID] == signature {
			s.episodeMu.Unlock()
			return
		}
		select {
		case s.events <- event:
			s.episodes[event.AlertID] = signature
			s.appended.Add(1)
		default:
			s.dropped.Add(1)
		}
		s.episodeMu.Unlock()
		return
	}
	s.clearDeliveryEpisode(event.AlertID)
	select {
	case s.events <- event:
		s.appended.Add(1)
	default:
		s.dropped.Add(1)
	}
}

// AppendDurable commits one lifecycle event before returning. Lifecycle
// volume is low, and this explicit durability boundary prevents alert history
// from silently losing state when the diagnostic append buffer is saturated.
func (s *Store) AppendDurable(event Event) error {
	if s == nil {
		return fmt.Errorf("event log is not enabled")
	}
	if event.OccurredAt.IsZero() {
		event.OccurredAt = time.Now()
	}
	s.clearDeliveryEpisode(event.AlertID)
	if err := s.insertBatch([]Event{event}); err != nil {
		s.recordWriteFailure(err)
		return err
	}
	return nil
}

// ImportEvents writes events synchronously, bypassing the droppable append
// buffer. It exists for the one-time legacy-history migration, where losing
// an entry to a full buffer would silently lose user data.
func (s *Store) ImportEvents(events []Event) error {
	if s == nil {
		return fmt.Errorf("event log is not enabled")
	}
	for i := range events {
		if events[i].OccurredAt.IsZero() {
			events[i].OccurredAt = time.Now()
		}
	}
	return s.insertBatch(events)
}

// Dropped reports how many events were discarded because the append buffer
// was full. A non-zero value means the log is incomplete for that window.
func (s *Store) Dropped() int64 {
	if s == nil {
		return 0
	}
	return s.dropped.Load()
}

func (s *Store) writeLoop() {
	defer s.wg.Done()
	pruneTicker := time.NewTicker(pruneInterval)
	defer pruneTicker.Stop()
	s.pruneOld()

	for {
		select {
		case event := <-s.events:
			batch := []Event{event}
		drain:
			for len(batch) < 128 {
				select {
				case next := <-s.events:
					batch = append(batch, next)
				default:
					break drain
				}
			}
			if err := s.insertBatch(batch); err != nil {
				s.forgetFailedDeliveryEpisodes(batch)
				s.recordWriteFailure(err)
				log.Error().Err(err).Int("events", len(batch)).Msg("alert event log write failed")
				continue
			}
			s.written.Add(int64(len(batch)))
		case <-pruneTicker.C:
			s.pruneOld()
		case <-s.stop:
			// Final drain so a clean shutdown does not lose buffered events.
			for {
				select {
				case event := <-s.events:
					if err := s.insertBatch([]Event{event}); err != nil {
						s.forgetFailedDeliveryEpisodes([]Event{event})
						s.recordWriteFailure(err)
						log.Error().Err(err).Msg("alert event log final drain write failed")
						continue
					}
					s.written.Add(1)
				default:
					return
				}
			}
		}
	}
}

func (s *Store) recordWriteFailure(err error) {
	if s == nil || err == nil {
		return
	}
	s.failureMu.Lock()
	s.lastError = err
	s.failureMu.Unlock()
	s.failed.Add(1)
}

func (s *Store) writeError() error {
	if s == nil || s.failed.Load() == 0 {
		return nil
	}
	s.failureMu.RLock()
	err := s.lastError
	s.failureMu.RUnlock()
	if err == nil {
		return fmt.Errorf("alert event log write failed")
	}
	return fmt.Errorf("alert event log write failed: %w", err)
}

func (s *Store) insertBatch(batch []Event) error {
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	stmt, err := tx.Prepare(`
		INSERT INTO alert_events
			(occurred_at, event_type, alert_id, resource_id, resource_name, alert_type, level, reason, message, details, snapshot)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	`)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	defer stmt.Close()
	decisionStmt, err := tx.Prepare(`
		INSERT INTO alert_events
			(occurred_at, event_type, alert_id, resource_id, resource_name, alert_type, level, reason, message, details, snapshot)
		SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
		WHERE NOT EXISTS (
			SELECT 1 FROM alert_events AS latest
			WHERE latest.id = (
				SELECT candidate.id FROM alert_events AS candidate
				WHERE candidate.alert_id = ?
				ORDER BY candidate.occurred_at DESC, candidate.id DESC
				LIMIT 1
			)
			AND latest.event_type = ?
			AND latest.resource_id = ?
			AND latest.resource_name = ?
			AND latest.alert_type = ?
			AND latest.level = ?
			AND latest.reason = ?
			AND latest.message = ?
			AND latest.details = ?
			AND latest.snapshot = ?
		)
	`)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	defer decisionStmt.Close()
	var importStmt *sql.Stmt
	for _, event := range batch {
		if event.Type != TypeHistoryImported {
			continue
		}
		importStmt, err = tx.Prepare(`
			INSERT INTO alert_events
				(occurred_at, event_type, alert_id, resource_id, resource_name, alert_type, level, reason, message, details, snapshot)
			SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
			WHERE NOT EXISTS (
				SELECT 1 FROM alert_events
				WHERE event_type = ? AND alert_id = ? AND occurred_at = ? AND snapshot = ?
			)
		`)
		if err != nil {
			_ = tx.Rollback()
			return err
		}
		defer importStmt.Close()
		break
	}
	for _, event := range batch {
		details := ""
		if len(event.Details) > 0 {
			encoded, err := json.Marshal(event.Details)
			if err == nil {
				details = string(encoded)
			}
		}
		occurredAt := event.OccurredAt.UTC().Format(time.RFC3339Nano)
		snapshot := string(event.Snapshot)
		args := []any{
			occurredAt,
			event.Type,
			event.AlertID,
			event.ResourceID,
			event.ResourceName,
			event.AlertType,
			event.Level,
			event.Reason,
			event.Message,
			details,
			snapshot,
		}
		var insertErr error
		if event.Type == TypeHistoryImported {
			_, insertErr = importStmt.Exec(append(args,
				TypeHistoryImported,
				event.AlertID,
				occurredAt,
				snapshot,
			)...)
		} else if event.AlertID != "" && coalescesUnchangedDeliveryEpisode(event.Type) {
			_, insertErr = decisionStmt.Exec(append(args,
				event.AlertID,
				event.Type,
				event.ResourceID,
				event.ResourceName,
				event.AlertType,
				event.Level,
				event.Reason,
				event.Message,
				details,
				snapshot,
			)...)
		} else {
			_, insertErr = stmt.Exec(args...)
		}
		if insertErr != nil {
			_ = tx.Rollback()
			return insertErr
		}
		if err := projectLifecycleActiveState(tx, event); err != nil {
			_ = tx.Rollback()
			return err
		}
	}
	return tx.Commit()
}

func coalescesUnchangedDeliveryEpisode(eventType string) bool {
	return eventType == TypeNotificationSuppressed || eventType == TypeNotificationDeferred
}

type deliveryEpisodeIdentity struct {
	Type         string            `json:"type"`
	ResourceID   string            `json:"resourceId"`
	ResourceName string            `json:"resourceName"`
	AlertType    string            `json:"alertType"`
	Level        string            `json:"level"`
	Reason       string            `json:"reason"`
	Message      string            `json:"message"`
	Details      map[string]string `json:"details"`
	Snapshot     string            `json:"snapshot"`
}

func deliveryEpisodeSignature(event Event) string {
	details := event.Details
	if len(details) == 0 {
		details = nil
	}
	encoded, _ := json.Marshal(deliveryEpisodeIdentity{
		Type:         event.Type,
		ResourceID:   event.ResourceID,
		ResourceName: event.ResourceName,
		AlertType:    event.AlertType,
		Level:        event.Level,
		Reason:       event.Reason,
		Message:      event.Message,
		Details:      details,
		Snapshot:     string(event.Snapshot),
	})
	return string(encoded)
}

func (s *Store) clearDeliveryEpisode(alertID string) {
	if s == nil || alertID == "" {
		return
	}
	s.episodeMu.Lock()
	delete(s.episodes, alertID)
	s.episodeMu.Unlock()
}

func (s *Store) forgetFailedDeliveryEpisodes(events []Event) {
	if s == nil {
		return
	}
	s.episodeMu.Lock()
	defer s.episodeMu.Unlock()
	for _, event := range events {
		if !coalescesUnchangedDeliveryEpisode(event.Type) {
			continue
		}
		signature := deliveryEpisodeSignature(event)
		if s.episodes[event.AlertID] == signature {
			delete(s.episodes, event.AlertID)
		}
	}
}

func (s *Store) pruneOld() {
	if s.retention > 0 {
		s.pruneEventsBefore(time.Now().Add(-s.retention))
	}
	s.pruneSnapshotVolume(maxRetainedSnapshotBytes)
}

// pruneSnapshotVolume removes the oldest events until the total stored snapshot
// payload is within maxBytes. Age retention alone cannot bound disk use: one
// flapping alert can write thousands of full state snapshots inside the
// retention window. Deleting the oldest rows raises the retention revision so
// the history projection rebuilds from the remaining log.
func (s *Store) pruneSnapshotVolume(maxBytes int64) {
	if s == nil || maxBytes <= 0 {
		return
	}
	var total int64
	if err := s.db.QueryRow(`SELECT COALESCE(SUM(length(snapshot)), 0) FROM alert_events`).Scan(&total); err != nil {
		log.Error().Err(err).Msg("alert event log volume check failed")
		return
	}
	if total <= maxBytes {
		return
	}
	// Read only ids and payload lengths, oldest first, until enough bytes are
	// scheduled for removal. The cursor must be closed before deleting because
	// the store serializes writes on a single connection.
	rows, err := s.db.Query(`SELECT id, length(snapshot) FROM alert_events ORDER BY occurred_at ASC, id ASC`)
	if err != nil {
		log.Error().Err(err).Msg("alert event log volume scan failed")
		return
	}
	ids := make([]int64, 0)
	for total > maxBytes && rows.Next() {
		var id, size int64
		if scanErr := rows.Scan(&id, &size); scanErr != nil {
			rows.Close()
			log.Error().Err(scanErr).Msg("alert event log volume scan failed")
			return
		}
		ids = append(ids, id)
		total -= size
	}
	scanErr := rows.Err()
	rows.Close()
	if scanErr != nil {
		log.Error().Err(scanErr).Msg("alert event log volume scan failed")
		return
	}
	if len(ids) == 0 {
		return
	}
	s.deleteEventsByID(ids)
}

func (s *Store) deleteEventsByID(ids []int64) {
	tx, err := s.db.Begin()
	if err != nil {
		log.Error().Err(err).Msg("alert event log volume prune failed")
		return
	}
	defer tx.Rollback()
	for start := 0; start < len(ids); start += 500 {
		end := start + 500
		if end > len(ids) {
			end = len(ids)
		}
		placeholders := make([]string, 0, end-start)
		args := make([]any, 0, end-start)
		for _, id := range ids[start:end] {
			placeholders = append(placeholders, "?")
			args = append(args, id)
		}
		if _, err = tx.Exec("DELETE FROM alert_events WHERE id IN ("+strings.Join(placeholders, ",")+")", args...); err != nil {
			log.Error().Err(err).Msg("alert event log volume prune failed")
			return
		}
	}
	if _, err = tx.Exec(`INSERT INTO alert_store_meta (key, value) VALUES ('retention_revision', '1')
		ON CONFLICT(key) DO UPDATE SET value = CAST(value AS INTEGER) + 1`); err != nil {
		log.Error().Err(err).Msg("alert event log volume prune failed")
		return
	}
	if err = tx.Commit(); err != nil {
		log.Error().Err(err).Msg("alert event log volume prune failed")
	}
}

func (s *Store) pruneEventsBefore(cutoff time.Time) {
	cutoffValue := cutoff.UTC().Format(time.RFC3339Nano)
	tx, err := s.db.Begin()
	if err != nil {
		log.Error().Err(err).Msg("alert event log prune failed")
		return
	}
	defer tx.Rollback()
	result, err := tx.Exec(`DELETE FROM alert_events WHERE occurred_at < ?`, cutoffValue)
	if err == nil {
		var deleted int64
		deleted, err = result.RowsAffected()
		if err == nil && deleted > 0 {
			_, err = tx.Exec(`INSERT INTO alert_store_meta (key, value) VALUES ('retention_revision', '1')
				ON CONFLICT(key) DO UPDATE SET value = CAST(value AS INTEGER) + 1`)
		}
	}
	if err == nil {
		err = tx.Commit()
	}
	if err != nil {
		log.Error().Err(err).Msg("alert event log prune failed")
	}
}

// ReplayBoundary identifies the durable append tail and any removal of retained
// events. Projections can reuse a fold and read only new IDs. Retention changes
// invalidate that fold, including deletion of older events below its cursor.
type ReplayBoundary struct {
	LastID            int64
	RetentionRevision int64
}

func (s *Store) ReplayBoundary() (ReplayBoundary, error) {
	var boundary ReplayBoundary
	if s == nil {
		return boundary, fmt.Errorf("event log is not enabled")
	}
	err := s.readDB.QueryRow(`SELECT
		COALESCE((SELECT MAX(id) FROM alert_events), 0),
		COALESCE((SELECT CAST(value AS INTEGER) FROM alert_store_meta WHERE key = 'retention_revision'), 0)
	`).Scan(&boundary.LastID, &boundary.RetentionRevision)
	return boundary, err
}

// Flush blocks until every diagnostic event appended before the call has been
// written. It reports failed writes and timeouts rather than claiming a lost
// batch was successfully flushed.
func (s *Store) Flush() error {
	if s == nil {
		return nil
	}
	target := s.appended.Load()
	deadline := time.Now().Add(5 * time.Second)
	for s.written.Load() < target && time.Now().Before(deadline) {
		if err := s.writeError(); err != nil {
			return err
		}
		time.Sleep(time.Millisecond)
	}
	if err := s.writeError(); err != nil {
		return err
	}
	if written := s.written.Load(); written < target {
		return fmt.Errorf("alert event log flush timed out: wrote %d of %d events", written, target)
	}
	return nil
}

// deliveryEpisodeQuerySource projects legacy poll-frequency duplicates as the
// single decision episode they represent. It compares each diagnostic hold to
// the immediately preceding event for that alert, including events outside the
// caller's filter, so a lifecycle or dispatch transition always opens a new
// episode. New writes are already coalesced in insertBatch; this read projection
// keeps upgraded stores immediately useful without rewriting immutable rows.
const deliveryEpisodeQuerySource = `(
	SELECT current.*
	FROM alert_events AS current
	WHERE NOT (
		current.alert_id <> ''
		AND current.event_type IN ('notification_suppressed', 'notification_deferred')
		AND EXISTS (
			SELECT 1
			FROM alert_events AS previous
			WHERE previous.id = (
				SELECT candidate.id
				FROM alert_events AS candidate
				WHERE candidate.alert_id = current.alert_id
					AND (
						candidate.occurred_at < current.occurred_at
						OR (candidate.occurred_at = current.occurred_at AND candidate.id < current.id)
					)
				ORDER BY candidate.occurred_at DESC, candidate.id DESC
				LIMIT 1
			)
			AND previous.event_type = current.event_type
			AND previous.resource_id = current.resource_id
			AND previous.resource_name = current.resource_name
			AND previous.alert_type = current.alert_type
			AND previous.level = current.level
			AND previous.reason = current.reason
			AND previous.message = current.message
			AND previous.details = current.details
			AND previous.snapshot = current.snapshot
		)
	)
) AS alert_events`

// Query returns matching events, newest first. Repeated unchanged suppression
// and deferral rows written by earlier versions are projected as one episode.
func (s *Store) Query(filter Filter) ([]Event, error) {
	if s == nil {
		return nil, nil
	}

	where, args := eventFilterWhere(filter)

	limit := filter.Limit
	if limit <= 0 {
		limit = defaultQueryLimit
	}
	if limit > maxQueryLimit {
		limit = maxQueryLimit
	}

	query := "SELECT id, occurred_at, event_type, alert_id, resource_id, resource_name, alert_type, level, reason, message, details, snapshot FROM " + deliveryEpisodeQuerySource
	if len(where) > 0 {
		query += " WHERE " + strings.Join(where, " AND ")
	}
	query += " ORDER BY occurred_at DESC, id DESC LIMIT ?"
	args = append(args, limit)

	rows, err := s.readDB.Query(query, args...)
	if err != nil {
		return nil, fmt.Errorf("query alert event log: %w", err)
	}
	defer rows.Close()

	// Do not use the request-derived limit as an allocation hint. The SQL
	// query is capped above, but keeping the result slice allocation independent
	// of caller input makes that memory-safety boundary explicit and prevents a
	// future query refactor from turning an oversized limit into an eager
	// allocation.
	return scanEventRows(rows, defaultQueryLimit)
}

// WalkOldest visits every matching event oldest first. It uses bounded keyset
// pages so a complete history projection is not truncated by Query's public
// safety cap or retain a reader connection for the full scan.
// A positive filter Limit caps the total visits; zero walks all
// matching rows.
func (s *Store) WalkOldest(filter Filter, visit func(Event) error) error {
	if s == nil {
		return nil
	}
	if visit == nil {
		return fmt.Errorf("event visitor is required")
	}

	visited := 0
	cursorOccurredAt := ""
	var cursorID int64

	for {
		pageLimit := maxQueryLimit
		if filter.Limit > 0 {
			remaining := filter.Limit - visited
			if remaining <= 0 {
				return nil
			}
			if remaining < pageLimit {
				pageLimit = remaining
			}
		}

		query, args := historyPageQuery(filter, cursorOccurredAt, cursorID, pageLimit)

		rows, err := s.readDB.Query(query, args...)
		if err != nil {
			return fmt.Errorf("walk alert event log: %w", err)
		}
		page, scanErr := scanEventRows(rows, pageLimit)
		closeErr := rows.Close()
		if scanErr != nil {
			return scanErr
		}
		if closeErr != nil {
			return fmt.Errorf("close alert event page: %w", closeErr)
		}

		for _, event := range page {
			if err := visit(event); err != nil {
				return err
			}
			visited++
		}
		if len(page) < pageLimit {
			return nil
		}

		last := page[len(page)-1]
		cursorOccurredAt = last.OccurredAt.UTC().Format(time.RFC3339Nano)
		cursorID = last.ID
	}
}

// historyPageQuery selects bounded IDs before reading wide event payloads.
// A replay watermark must seek by durable ID, not scan the time index from the
// beginning on every catch-up. Alert-specific reads use their chronological
// index. Full-history walks seek by event time between pages.
func historyPageQuery(filter Filter, cursorOccurredAt string, cursorID int64, pageLimit int) (string, []any) {
	where, args := eventFilterWhere(filter)
	if cursorOccurredAt != "" {
		where = append(where, "(occurred_at, id) > (?, ?)")
		args = append(args, cursorOccurredAt, cursorID)
	}
	index := " INDEXED BY idx_alert_events_time"
	if filter.AfterID > 0 {
		index = " NOT INDEXED" // SQLite seeks the INTEGER PRIMARY KEY for id > ?.
	} else if strings.TrimSpace(filter.AlertID) != "" {
		index = " INDEXED BY idx_alert_events_alert"
	}
	query := "WITH page AS MATERIALIZED (SELECT id, occurred_at FROM alert_events" + index
	if len(where) > 0 {
		query += " WHERE " + strings.Join(where, " AND ")
	}
	query += " ORDER BY occurred_at ASC, id ASC LIMIT ?) SELECT e.id, e.occurred_at, e.event_type, e.alert_id, e.resource_id, e.resource_name, e.alert_type, e.level, e.reason, e.message, e.details, e.snapshot FROM page JOIN alert_events AS e ON e.id = page.id ORDER BY page.occurred_at ASC, page.id ASC"
	return query, append(args, pageLimit)
}

func eventFilterWhere(filter Filter) ([]string, []any) {
	where := make([]string, 0, 4)
	args := make([]any, 0, 6)
	if trimmed := strings.TrimSpace(filter.AlertID); trimmed != "" {
		where = append(where, "alert_id = ?")
		args = append(args, trimmed)
	}
	if len(filter.Types) > 0 {
		placeholders := make([]string, 0, len(filter.Types))
		for _, eventType := range filter.Types {
			trimmed := strings.TrimSpace(eventType)
			if trimmed == "" {
				continue
			}
			placeholders = append(placeholders, "?")
			args = append(args, trimmed)
		}
		if len(placeholders) > 0 {
			where = append(where, "event_type IN ("+strings.Join(placeholders, ",")+")")
		}
	}
	if !filter.Since.IsZero() {
		where = append(where, "occurred_at >= ?")
		args = append(args, filter.Since.UTC().Format(time.RFC3339Nano))
	}
	if !filter.Until.IsZero() {
		where = append(where, "occurred_at <= ?")
		args = append(args, filter.Until.UTC().Format(time.RFC3339Nano))
	}
	if filter.AfterID > 0 {
		where = append(where, "id > ?")
		args = append(args, filter.AfterID)
	}
	if filter.ThroughID > 0 {
		where = append(where, "id <= ?")
		args = append(args, filter.ThroughID)
	}
	return where, args
}

func scanEventRows(rows *sql.Rows, capacity int) ([]Event, error) {
	if capacity < 0 || capacity > maxQueryLimit {
		capacity = maxQueryLimit
	}
	events := make([]Event, 0, capacity)
	for rows.Next() {
		var event Event
		var occurredAt, details, snapshot string
		if err := rows.Scan(
			&event.ID,
			&occurredAt,
			&event.Type,
			&event.AlertID,
			&event.ResourceID,
			&event.ResourceName,
			&event.AlertType,
			&event.Level,
			&event.Reason,
			&event.Message,
			&details,
			&snapshot,
		); err != nil {
			return nil, fmt.Errorf("scan alert event: %w", err)
		}
		if snapshot != "" {
			event.Snapshot = json.RawMessage(snapshot)
		}
		if parsed, err := time.Parse(time.RFC3339Nano, occurredAt); err == nil {
			event.OccurredAt = parsed
		}
		if details != "" {
			decoded := map[string]string{}
			if err := json.Unmarshal([]byte(details), &decoded); err == nil && len(decoded) > 0 {
				event.Details = decoded
			}
		}
		events = append(events, event)
	}
	return events, rows.Err()
}

// Close drains buffered events and closes the database.
func (s *Store) Close() {
	if s == nil {
		return
	}
	s.closeOnce.Do(func() {
		close(s.stop)
		s.wg.Wait()
		if s.readDB != s.db {
			if err := s.readDB.Close(); err != nil {
				log.Error().Err(err).Msg("alert event reader close failed")
			}
		}
		if err := s.db.Close(); err != nil {
			log.Error().Err(err).Msg("alert event log close failed")
		}
	})
}