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>

4 files changed, 324 insertions, 0 deletions

diff --git a/internal/cli/cli.go b/internal/cli/cli.go
index e791512..8c22d6e 100644
--- a/internal/cli/cli.go
+++ b/internal/cli/cli.go
@@ -30,6 +30,12 @@ func Execute(args []string) error {
 		return runImport(args[1:])
 	case "query":
 		return runQuery(args[1:])
+	case "exclude":
+		return runExclude(args[1:])
+	case "unexclude":
+		return runUnexclude(args[1:])
+	case "list-excluded":
+		return runListExcluded(args[1:])
 	case "test":
 		return runTests()
 	default:
diff --git a/internal/cli/cmd_exclude.go b/internal/cli/cmd_exclude.go
new file mode 100644
index 0000000..92e1b84
--- /dev/null
+++ b/internal/cli/cmd_exclude.go
@@ -0,0 +1,102 @@
+package cli
+
+import (
+	"context"
+	"flag"
+	"fmt"
+	"os"
+	"time"
+
+	"codeberg.org/snonux/goprecords/internal/storage"
+)
+
+func runExclude(args []string) error {
+	fs := flag.NewFlagSet("exclude", flag.ExitOnError)
+	dbPath := fs.String("db", "goprecords.db", "SQLite database path")
+	reason := fs.String("reason", "", "Reason for exclusion")
+	if err := fs.Parse(args); err != nil {
+		return err
+	}
+	if fs.NArg() < 1 {
+		fmt.Fprintln(os.Stderr, "exclude: hostname required")
+		fs.Usage()
+		return fmt.Errorf("missing hostname")
+	}
+	host := fs.Arg(0)
+	ctx := context.Background()
+	db, err := storage.Open(ctx, *dbPath)
+	if err != nil {
+		return fmt.Errorf("open db: %w", err)
+	}
+	defer db.Close()
+	if err := storage.CreateSchema(ctx, db); err != nil {
+		return fmt.Errorf("schema: %w", err)
+	}
+	if err := storage.AddExcludedHost(ctx, db, host, *reason); err != nil {
+		return err
+	}
+	fmt.Fprintf(os.Stderr, "excluded host %q from alerts\n", host)
+	return nil
+}
+
+func runUnexclude(args []string) error {
+	fs := flag.NewFlagSet("unexclude", flag.ExitOnError)
+	dbPath := fs.String("db", "goprecords.db", "SQLite database path")
+	if err := fs.Parse(args); err != nil {
+		return err
+	}
+	if fs.NArg() < 1 {
+		fmt.Fprintln(os.Stderr, "unexclude: hostname required")
+		fs.Usage()
+		return fmt.Errorf("missing hostname")
+	}
+	host := fs.Arg(0)
+	ctx := context.Background()
+	db, err := storage.Open(ctx, *dbPath)
+	if err != nil {
+		return fmt.Errorf("open db: %w", err)
+	}
+	defer db.Close()
+	if err := storage.CreateSchema(ctx, db); err != nil {
+		return fmt.Errorf("schema: %w", err)
+	}
+	if err := storage.RemoveExcludedHost(ctx, db, host); err != nil {
+		return err
+	}
+	fmt.Fprintf(os.Stderr, "removed host %q from exclusion list\n", host)
+	return nil
+}
+
+func runListExcluded(args []string) error {
+	fs := flag.NewFlagSet("list-excluded", flag.ExitOnError)
+	dbPath := fs.String("db", "goprecords.db", "SQLite database path")
+	if err := fs.Parse(args); err != nil {
+		return err
+	}
+	ctx := context.Background()
+	db, err := storage.Open(ctx, *dbPath)
+	if err != nil {
+		return fmt.Errorf("open db: %w", err)
+	}
+	defer db.Close()
+	if err := storage.CreateSchema(ctx, db); err != nil {
+		return fmt.Errorf("schema: %w", err)
+	}
+	hosts, err := storage.LoadExcludedHosts(ctx, db)
+	if err != nil {
+		return err
+	}
+	if len(hosts) == 0 {
+		fmt.Println("no excluded hosts")
+		return nil
+	}
+	for _, h := range hosts {
+		t := time.Unix(h.ExcludedAt, 0).UTC().Format("2006-01-02")
+		if h.Reason != "" {
+			fmt.Printf("%-40s  excluded=%s  reason=%s\n", h.Host, t, h.Reason)
+		} else {
+			fmt.Printf("%-40s  excluded=%s\n", h.Host, t)
+		}
+	}
+	return nil
+}
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 {
diff --git a/internal/storage/excluded_host_test.go b/internal/storage/excluded_host_test.go
new file mode 100644
index 0000000..6af53a5
--- /dev/null
+++ b/internal/storage/excluded_host_test.go
@@ -0,0 +1,148 @@
+package storage
+
+import (
+	"context"
+	"path/filepath"
+	"testing"
+)
+
+func TestAddExcludedHost(t *testing.T) {
+	db, err := Open(context.Background(), filepath.Join(t.TempDir(), "test.db"))
+	if err != nil {
+		t.Fatal(err)
+	}
+	defer db.Close()
+	ctx := context.Background()
+	if err := CreateSchema(ctx, db); err != nil {
+		t.Fatal(err)
+	}
+	if err := AddExcludedHost(ctx, db, "host1", "decommissioned"); err != nil {
+		t.Fatal(err)
+	}
+	hosts, err := LoadExcludedHosts(ctx, db)
+	if err != nil {
+		t.Fatal(err)
+	}
+	if len(hosts) != 1 {
+		t.Fatalf("len=%d, want 1", len(hosts))
+	}
+	if hosts[0].Host != "host1" || hosts[0].Reason != "decommissioned" {
+		t.Fatalf("unexpected host entry: %+v", hosts[0])
+	}
+}
+
+func TestAddExcludedHost_idempotent(t *testing.T) {
+	db, err := Open(context.Background(), filepath.Join(t.TempDir(), "test.db"))
+	if err != nil {
+		t.Fatal(err)
+	}
+	defer db.Close()
+	ctx := context.Background()
+	if err := CreateSchema(ctx, db); err != nil {
+		t.Fatal(err)
+	}
+	if err := AddExcludedHost(ctx, db, "host1", "first"); err != nil {
+		t.Fatal(err)
+	}
+	if err := AddExcludedHost(ctx, db, "host1", "second"); err != nil {
+		t.Fatal(err)
+	}
+	hosts, err := LoadExcludedHosts(ctx, db)
+	if err != nil {
+		t.Fatal(err)
+	}
+	if len(hosts) != 1 {
+		t.Fatalf("len=%d, want 1", len(hosts))
+	}
+	if hosts[0].Reason != "second" {
+		t.Fatalf("reason=%q, want second", hosts[0].Reason)
+	}
+}
+
+func TestRemoveExcludedHost(t *testing.T) {
+	db, err := Open(context.Background(), filepath.Join(t.TempDir(), "test.db"))
+	if err != nil {
+		t.Fatal(err)
+	}
+	defer db.Close()
+	ctx := context.Background()
+	if err := CreateSchema(ctx, db); err != nil {
+		t.Fatal(err)
+	}
+	if err := AddExcludedHost(ctx, db, "host1", ""); err != nil {
+		t.Fatal(err)
+	}
+	if err := RemoveExcludedHost(ctx, db, "host1"); err != nil {
+		t.Fatal(err)
+	}
+	hosts, err := LoadExcludedHosts(ctx, db)
+	if err != nil {
+		t.Fatal(err)
+	}
+	if len(hosts) != 0 {
+		t.Fatalf("len=%d, want 0", len(hosts))
+	}
+}
+
+func TestRemoveExcludedHost_nonexistent(t *testing.T) {
+	db, err := Open(context.Background(), filepath.Join(t.TempDir(), "test.db"))
+	if err != nil {
+		t.Fatal(err)
+	}
+	defer db.Close()
+	ctx := context.Background()
+	if err := CreateSchema(ctx, db); err != nil {
+		t.Fatal(err)
+	}
+	if err := RemoveExcludedHost(ctx, db, "ghost"); err != nil {
+		t.Fatal(err)
+	}
+}
+
+func TestIsExcludedHost(t *testing.T) {
+	db, err := Open(context.Background(), filepath.Join(t.TempDir(), "test.db"))
+	if err != nil {
+		t.Fatal(err)
+	}
+	defer db.Close()
+	ctx := context.Background()
+	if err := CreateSchema(ctx, db); err != nil {
+		t.Fatal(err)
+	}
+	if err := AddExcludedHost(ctx, db, "host1", ""); err != nil {
+		t.Fatal(err)
+	}
+	yes, err := IsExcludedHost(ctx, db, "host1")
+	if err != nil {
+		t.Fatal(err)
+	}
+	if !yes {
+		t.Fatal("expected host1 to be excluded")
+	}
+	no, err := IsExcludedHost(ctx, db, "other")
+	if err != nil {
+		t.Fatal(err)
+	}
+	if no {
+		t.Fatal("expected other to not be excluded")
+	}
+}
+
+func TestLoadExcludedHosts_empty(t *testing.T) {
+	db, err := Open(context.Background(), filepath.Join(t.TempDir(), "test.db"))
+	if err != nil {
+		t.Fatal(err)
+	}
+	defer db.Close()
+	ctx := context.Background()
+	if err := CreateSchema(ctx, db); err != nil {
+		t.Fatal(err)
+	}
+	hosts, err := LoadExcludedHosts(ctx, db)
+	if err != nil {
+		t.Fatal(err)
+	}
+	if len(hosts) != 0 {
+		t.Fatalf("len=%d, want 0", len(hosts))
+	}
+}