From edeffd05dc62c4c3d747cc41067bf4e1814f300a Mon Sep 17 00:00:00 2001
From: Paul Buetow <paul@buetow.org>
Date: Thu, 16 Apr 2026 08:43:55 +0300
Subject: Add excluded_hosts feature: store in SQLite, expose CLI subcommands

Adds an excluded_host table to the SQLite schema and three new CLI
subcommands (exclude, unexclude, list-excluded) so operators can mark
hosts that are no longer expected to send updates. The IsExcludedHost
and LoadExcludedHosts storage helpers are ready for the Prometheus
alerting endpoint (task d4).

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
---
 internal/storage/db.go | 68 ++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 68 insertions(+)

(limited to 'internal/storage/db.go')

diff --git a/internal/storage/db.go b/internal/storage/db.go
index edd5a93..d500509 100644
--- a/internal/storage/db.go
+++ b/internal/storage/db.go
@@ -26,6 +26,11 @@ CREATE INDEX IF NOT EXISTS idx_record_host ON record(host);
 CREATE INDEX IF NOT EXISTS idx_record_os ON record(os);
 CREATE INDEX IF NOT EXISTS idx_record_os_kernel_name ON record(os_kernel_name);
 CREATE INDEX IF NOT EXISTS idx_record_os_kernel_major ON record(os_kernel_major);
+CREATE TABLE IF NOT EXISTS excluded_host (
+	host TEXT NOT NULL PRIMARY KEY,
+	reason TEXT NOT NULL DEFAULT '',
+	excluded_at INTEGER NOT NULL DEFAULT (strftime('%s','now'))
+);
 `
 
 // Record is one uptimed boot row stored in the record table.
@@ -136,6 +141,69 @@ func LoadRecords(ctx context.Context, db *sql.DB) ([]Record, error) {
 	return out, nil
 }
 
+// ExcludedHost holds an entry from the excluded_host table.
+type ExcludedHost struct {
+	Host       string
+	Reason     string
+	ExcludedAt int64
+}
+
+// AddExcludedHost inserts or replaces a host in the excluded_host table.
+func AddExcludedHost(ctx context.Context, db *sql.DB, host, reason string) error {
+	_, err := db.ExecContext(ctx,
+		"INSERT OR REPLACE INTO excluded_host (host, reason) VALUES (?, ?)",
+		host, reason)
+	if err != nil {
+		return fmt.Errorf("add excluded host: %w", err)
+	}
+	return nil
+}
+
+// RemoveExcludedHost removes a host from the excluded_host table.
+func RemoveExcludedHost(ctx context.Context, db *sql.DB, host string) error {
+	_, err := db.ExecContext(ctx, "DELETE FROM excluded_host WHERE host = ?", host)
+	if err != nil {
+		return fmt.Errorf("remove excluded host: %w", err)
+	}
+	return nil
+}
+
+// LoadExcludedHosts returns all rows from the excluded_host table.
+func LoadExcludedHosts(ctx context.Context, db *sql.DB) ([]ExcludedHost, error) {
+	rows, err := db.QueryContext(ctx, "SELECT host, reason, excluded_at FROM excluded_host ORDER BY host")
+	if err != nil {
+		return nil, fmt.Errorf("query excluded hosts: %w", err)
+	}
+	defer rows.Close()
+	var out []ExcludedHost
+	for rows.Next() {
+		select {
+		case <-ctx.Done():
+			return nil, ctx.Err()
+		default:
+		}
+		var e ExcludedHost
+		if err := rows.Scan(&e.Host, &e.Reason, &e.ExcludedAt); err != nil {
+			return nil, fmt.Errorf("scan excluded host: %w", err)
+		}
+		out = append(out, e)
+	}
+	if err := rows.Err(); err != nil {
+		return nil, fmt.Errorf("rows excluded hosts: %w", err)
+	}
+	return out, nil
+}
+
+// IsExcludedHost reports whether a host is in the excluded_host table.
+func IsExcludedHost(ctx context.Context, db *sql.DB, host string) (bool, error) {
+	var count int
+	err := db.QueryRowContext(ctx, "SELECT COUNT(*) FROM excluded_host WHERE host = ?", host).Scan(&count)
+	if err != nil {
+		return false, fmt.Errorf("check excluded host: %w", err)
+	}
+	return count > 0, nil
+}
+
 func importFile(ctx context.Context, insert *sql.Stmt, fsys fs.FS, relPath, host string) error {
 	f, err := fsys.Open(relPath)
 	if err != nil {
-- 
cgit v1.2.3