package db
import (
"cmp"
"database/sql"
"errors"
"fmt"
"log/slog"
"os"
"slices"
"time"
"gopkg.awl.red/cicada/config"
"gopkg.awl.red/cicada/speak"
)
type Db struct {
read *sql.DB
write *sql.DB
}
const (
severityImmediate = "IMMEDIATE"
severityInvestigate = "INVESTIGATE"
)
const (
statusTriggered = "TRIGGERED"
statusAcknowledged = "ACKNOWLEDGED"
statusResolved = "RESOLVED"
)
const (
migrationText = `
CREATE TABLE alert(
id INTEGER PRIMARY KEY,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
service TEXT NOT NULL,
component TEXT NOT NULL DEFAULT '',
error TEXT NOT NULL DEFAULT '',
message TEXT NOT NULL,
alerter TEXT NOT NULL,
updater TEXT NOT NULL DEFAULT '',
status TEXT NOT NULL DEFAULT 'TRIGGERED',
status_changes INT NOT NULL DEFAULT 0,
severity TEXT NOT NULL DEFAULT 'IMMEDIATE',
status_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_voiced TIMESTAMP,
metadata JSONB
);
`
devSeedAlert = `INSERT INTO alert (service, message, alerter) VALUES
('my_web_service', 'can not get index page', 'service_checker');
`
)
func migrate(db *sql.DB, cfg config.Config) error {
rows, err := db.Query(`SELECT service, message FROM alert LIMIT 1;`)
if err != nil {
_, err := db.Exec(migrationText)
if err != nil {
return err
}
} else {
defer rows.Close()
}
if !cfg.SeedAlert {
return nil
}
if rows == nil {
rows, err = db.Query(`SELECT service, message FROM alert LIMIT 1;`)
if err != nil {
return err
}
defer rows.Close()
}
var service, message string
for rows.Next() {
err = rows.Scan(&service, &message)
if err != nil {
return err
}
}
if service == "my_web_service" && message == "can not get index page" {
return nil
}
_, err = db.Exec(devSeedAlert)
if err != nil {
return err
}
return nil
}
func New(cfg config.Config) (*Db, error) {
dbWrite, err := sql.Open("sqlite", cfg.Conn)
if err != nil {
return nil, err
}
dbWrite.SetMaxOpenConns(1)
if err := migrate(dbWrite, cfg); err != nil {
return nil, err
}
dbRead, err := sql.Open("sqlite", cfg.Conn)
if err != nil {
return nil, err
}
return &Db{write: dbWrite, read: dbRead}, nil
}
func (d *Db) Close() error {
return errors.Join(d.read.Close(), d.write.Close())
}
func timeAsTerse(t time.Time) string {
return t.Format("Jan _2 06 15:04 MST")
}
func (d *Db) GetAllActiveAndResolvedAlerts() ([]map[string]any, []map[string]any, error) {
rows, err := d.read.Query(`SELECT id, service, component, severity, status, created, status_time, error, message, updater, alerter, status_changes
FROM alert;`)
if err != nil {
return nil, nil, err
}
alerts := []map[string]any{}
resolved := []map[string]any{}
for rows.Next() {
msa := map[string]any{}
var id int
var service, component, severity, status, error, message, updater, alerter string
var createdTime, statusTime time.Time
var statusChanges int
rows.Scan(&id, &service, &component, &severity, &status, &createdTime, &statusTime, &error, &message, &updater, &alerter, &statusChanges)
msa["Id"] = id
msa["Service"] = service
msa["Component"] = component
msa["Severity"] = severity
msa["Status"] = status
msa["CreatedTime"] = timeAsTerse(createdTime)
msa["StatusTime"] = timeAsTerse(statusTime)
msa["_StatusTime"] = statusTime
msa["Error"] = error
msa["Message"] = message
msa["Updater"] = updater
msa["Alerter"] = alerter
msa["StatusChanges"] = statusChanges
if status == statusResolved {
resolved = append(resolved, msa)
} else {
alerts = append(alerts, msa)
}
}
if err := rows.Err(); err != nil {
return nil, nil, err
}
slices.SortFunc(resolved, func(x map[string]any, y map[string]any) int {
xTime, ok := x["_StatusTime"].(time.Time)
if !ok {
return 0
}
yTime, ok := y["_StatusTime"].(time.Time)
if !ok {
return 0
}
return cmp.Compare(xTime.Unix(), yTime.Unix()) * -1
})
slices.SortFunc(alerts, func(x map[string]any, y map[string]any) int {
xComponent, ok := x["Component"].(string)
if !ok {
return 0
}
yComponent, ok := y["Component"].(string)
if !ok {
return 0
}
return cmp.Compare(xComponent, yComponent)
})
return alerts, resolved, nil
}
func (d *Db) FetchSingleForSpeak(args ...any) error {
getVoiceableSql := `SELECT id,
service, component, error, message, severity,
last_voiced, status_time
FROM alert
WHERE id = $1;`
row := d.read.QueryRow(getVoiceableSql, args[0])
err := row.Scan(args[1:]...)
return err
}
var log = slog.New(slog.NewJSONHandler(os.Stdout, nil))
type IdTime struct {
id int
time time.Time
}
func (d *Db) MarkAlertsForSpeaking(cfg config.Config) ([]speak.Alert, error) {
getVoiceableSql := `SELECT id,
service, component, error, message, severity,
last_voiced, status_time
FROM alert
WHERE status = 'TRIGGERED';`
rows, err := d.read.Query(getVoiceableSql)
if err != nil {
return nil, err
}
now := time.Now().UTC()
updateSqls := []IdTime{}
sas := []speak.Alert{}
for rows.Next() {
var sa speak.Alert
var lastVoiced *time.Time
rows.Scan(&sa.Id,
&sa.Service, &sa.Component, &sa.Error, &sa.Message, &sa.Severity,
&lastVoiced, &sa.StatusTime,
)
if lastVoiced != nil {
sa.LastVoiced = *lastVoiced
}
sa.LastVoiced = sa.LastVoiced.UTC()
sa.StatusTime = sa.StatusTime.UTC()
if sa.Severity == severityImmediate && now.Sub(sa.LastVoiced) <= time.Second*time.Duration(cfg.IntervalImmediate) {
continue
}
if sa.Severity == severityInvestigate && now.Sub(sa.LastVoiced) <= time.Second*time.Duration(cfg.IntervalInvestigate) {
continue
}
sas = append(sas, sa)
updateSqls = append(updateSqls, IdTime{
id: sa.Id,
time: time.Now().UTC(),
})
}
if err := rows.Err(); err != nil {
return nil, err
}
for _, updateSqlArgs := range updateSqls {
updateSql := `UPDATE alert SET last_voiced = $1 WHERE id = $2;`
_, err = d.write.Exec(updateSql, updateSqlArgs.time, updateSqlArgs.id)
if err != nil {
log.Error("could not update last voiced", "id", updateSqlArgs.id)
}
}
return sas, nil
}
func (d *Db) UpdateAlertStatus(status string, statusTime time.Time, updater string, id int) error {
_, err := d.write.Exec(`UPDATE alert SET status=$1, status_time = $2, status_changes = status_changes + 1, updater = $3 WHERE id = $4;`,
status, statusTime, updater, id)
return err
}
func (d *Db) DeleteAlert(id int) error {
_, err := d.write.Exec(`DELETE FROM alert WHERE id = $1`, id)
return err
}
func (d *Db) SetStatusOnDupeAlert(cfg config.Config, service, component, error, message, status, updater string) (bool, error) {
preexistingAlertsSql := `SELECT id,status,status_time FROM alert
WHERE status_time >= datetime('now', $1)
AND service = $2
AND component = $3
AND error = $4
AND message = $5
ORDER BY status_time DESC;`
lookback := fmt.Sprintf("-%d day", cfg.ActiveDupeLookbackDays)
rows, err := d.read.Query(preexistingAlertsSql, lookback, service, component, error, message)
if err != nil {
return false, err
}
var recentId int
// var recentStatus string
var recentStatusTime time.Time
var resolvedRecentId int
// var resolvedRecentStatus string
var resolvedRecentStatusTime time.Time
for rows.Next() {
var id int
var rowStatus string
var statusTime time.Time
err = rows.Scan(&id, &rowStatus, &statusTime)
if err != nil {
return false, err
}
if rowStatus == statusAcknowledged && status == statusTriggered {
// alert is already acknowldeged, do not create another alert or set alert to triggered.
// But do increment the status change count on a best effort basis
updateSql := `UPDATE alert SET status_changes = status_changes + 1, status_time = CURRENT_TIMESTAMP WHERE id = $1;`
_, err := d.write.Exec(updateSql, id)
if err != nil {
log.Error("error doing best effort update for a trigger of an acknowledged", "err", err)
}
return true, nil
}
if rowStatus == statusResolved && resolvedRecentId == 0 {
resolvedRecentId = id
// resolvedRecentStatus = rowStatus
resolvedRecentStatusTime = statusTime
} else if (rowStatus == statusTriggered || rowStatus == statusAcknowledged) && recentId == 0 {
recentId = id
// recentStatus = rowStatus
recentStatusTime = statusTime
}
if recentId > 0 && resolvedRecentId > 0 {
break
}
}
if err := rows.Err(); err != nil {
return false, err
}
mostRecentUnresolvedIsStale := recentId > 0 && resolvedRecentId > 0 && resolvedRecentStatusTime.After(recentStatusTime)
if !mostRecentUnresolvedIsStale && recentId > 0 {
updateSql := `UPDATE alert SET status = $1, status_changes = status_changes + 1, status_time = CURRENT_TIMESTAMP, updater = $2 WHERE id = $3;`
_, err := d.write.Exec(updateSql, status, updater, recentId)
if err != nil {
return false, err
}
return true, nil
}
hasResolvedAndNotActive := recentId == 0 && resolvedRecentId > 0
if hasResolvedAndNotActive {
if status == statusResolved {
return true, nil
}
// retrigger a resolved
updateSql := `UPDATE alert SET status = $1, status_changes = status_changes + 1, status_time = CURRENT_TIMESTAMP, updater = $2 WHERE id = $3;`
_, err := d.write.Exec(updateSql, status, updater, resolvedRecentId)
if err != nil {
return false, err
}
// TODO: factor out the fact that it should speak the alert
if !cfg.SkipVoicing {
sa := speak.Alert{}
err = sa.FetchId(d.FetchSingleForSpeak, resolvedRecentId, cfg)
if err != nil {
log.Error("could not fetch retriggered alert for speaking", "err", err)
} else {
go sa.Speak(cfg)
}
}
return true, nil
}
return false, nil
}
func (d *Db) InsertAlert(service, component, error, message, status, severity string, statusTime time.Time, username string) (int, error) {
insertSql := `INSERT INTO alert
(service, component, error, message, status, severity, status_time, alerter) VALUES
($1, $2, $3, $4, $5, $6, $7, $8) RETURNING id;`
row := d.write.QueryRow(insertSql,
service, component, error, message,
status, severity, statusTime, username)
var id int
err := row.Scan(&id)
if err != nil {
return 0, err
}
return id, nil
}