worker/internal/webapp/store.go

package webapp

import (
	"context"
	"crypto/rand"
	"database/sql"
	"encoding/hex"
	"errors"
	"fmt"
	"strings"
	"time"

	// Pure-Go SQLite driver. Imported with the blank identifier so the
	// driver registers itself with database/sql under the name "sqlite".
	// modernc.org/sqlite is CGO-free which keeps the worker binary
	// portable (no glibc/musl split) and matches the constraint in
	// docs/distributed/worker-web-app.md section 13.1.
	_ "modernc.org/sqlite"
)

// schemaSQL defines the embedded store tables for the Phase 1 MVP.
// The store keeps:
//
//   - first-run webapp user with bcrypt hash and requires_change flag
//   - session cookies (server-side copy + idle/absolute expiry)
//   - audit log rows (actor, role, auth_mode, ip, ua, action, target,
//     before_hash, after_hash, ts)
//   - discovered-app inventory cache (json blob per app)
//
// All tables use INTEGER PRIMARY KEY so GORM-style IDs work; the
// store itself is hand-written SQL because the scope is tiny and we
// want zero coupling to GORM.
const schemaSQL = `
CREATE TABLE IF NOT EXISTS webapp_users (
    id INTEGER PRIMARY KEY,
    bcrypt_hash TEXT NOT NULL,
    requires_change INTEGER NOT NULL DEFAULT 0,
    created_at INTEGER NOT NULL,
    last_change_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS webapp_sessions (
    id TEXT PRIMARY KEY,
    user_id INTEGER NOT NULL,
    csrf_token TEXT NOT NULL,
    ip TEXT NOT NULL DEFAULT '',
    ua TEXT NOT NULL DEFAULT '',
    created_at INTEGER NOT NULL,
    last_seen_at INTEGER NOT NULL,
    expires_at INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_webapp_sessions_user
    ON webapp_sessions(user_id);

CREATE TABLE IF NOT EXISTS webapp_audit (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    actor TEXT NOT NULL DEFAULT '',
    role TEXT NOT NULL DEFAULT '',
    auth_mode TEXT NOT NULL DEFAULT '',
    ip TEXT NOT NULL DEFAULT '',
    ua TEXT NOT NULL DEFAULT '',
    action TEXT NOT NULL,
    target TEXT NOT NULL DEFAULT '',
    before_hash TEXT NOT NULL DEFAULT '',
    after_hash TEXT NOT NULL DEFAULT '',
    ts INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_webapp_audit_ts
    ON webapp_audit(ts);

CREATE TABLE IF NOT EXISTS webapp_apps (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    source TEXT NOT NULL,
    pid INTEGER NOT NULL DEFAULT 0,
    ports TEXT NOT NULL DEFAULT '',
    start_ts INTEGER NOT NULL DEFAULT 0,
    last_seen INTEGER NOT NULL,
    json_blob TEXT NOT NULL DEFAULT ''
);

CREATE INDEX IF NOT EXISTS idx_webapp_apps_name
    ON webapp_apps(name);
`

// Store is the embedded webapp database. It is safe for concurrent
// use because the underlying *sql.DB is.
//
// All exported methods take a context for cancellation; passing
// context.Background() is fine for the Phase 1 callers (background
// pruning timer + per-request handlers).
type Store struct {
	db *sql.DB
}

// OpenStore opens (or creates) the embedded SQLite database at the
// given path. For tests, pass "file::memory:?cache=shared" or just
// ":memory:". The returned Store must be closed via Close().
func OpenStore(path string) (*Store, error) {
	if path == "" {
		return nil, errors.New("webapp: empty store path")
	}
	// _pragma options: enforce foreign keys is irrelevant for this
	// single-table-per-feature design; we do however turn on WAL for
	// on-disk files because the audit write path is concurrent with
	// the login handler and we do not want readers to block writers.
	dsn := path
	if !strings.HasPrefix(path, "file:") && path != ":memory:" {
		dsn = path + "?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)"
	}
	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, fmt.Errorf("webapp: open sqlite: %w", err)
	}
	if err := db.Ping(); err != nil {
		_ = db.Close()
		return nil, fmt.Errorf("webapp: ping sqlite: %w", err)
	}
	if _, err := db.Exec(schemaSQL); err != nil {
		_ = db.Close()
		return nil, fmt.Errorf("webapp: apply schema: %w", err)
	}
	return &Store{db: db}, nil
}

// Close releases the underlying database handle. Safe to call once.
func (s *Store) Close() error {
	if s == nil || s.db == nil {
		return nil
	}
	return s.db.Close()
}

// User is the single-user model for the Phase 1 webapp. The worker
// webapp is single-tenant (one operator per worker) so a single
// row is the whole table.
type User struct {
	ID             int64
	BcryptHash     string
	RequiresChange bool
	CreatedAt      time.Time
	LastChangeAt   time.Time
}

// GetUser returns the single webapp user, or sql.ErrNoRows if no
// user has been provisioned yet (first run).
func (s *Store) GetUser(ctx context.Context) (*User, error) {
	row := s.db.QueryRowContext(ctx,
		`SELECT id, bcrypt_hash, requires_change, created_at, last_change_at
		 FROM webapp_users ORDER BY id ASC LIMIT 1`)
	u := &User{}
	var requiresChange int
	var created, changed int64
	if err := row.Scan(&u.ID, &u.BcryptHash, &requiresChange, &created, &changed); err != nil {
		return nil, err
	}
	u.RequiresChange = requiresChange != 0
	u.CreatedAt = time.Unix(created, 0).UTC()
	u.LastChangeAt = time.Unix(changed, 0).UTC()
	return u, nil
}

// CreateUser provisions the first (and only) webapp user with the
// requires_change flag set to the given value. Returns the new user
// row on success.
//
// Callers should pick the flag value:
//   - false: subsequent-start path; the operator knows the password
//     and does not need to rotate it again.
//   - true: first-run path; the operator must update the random
//     initial password before reaching any other page.
func (s *Store) CreateUser(ctx context.Context, bcryptHash string, requiresChange bool) (*User, error) {
	flagVal := 0
	if requiresChange {
		flagVal = 1
	}
	now := time.Now().UTC()
	res, err := s.db.ExecContext(ctx,
		`INSERT INTO webapp_users (bcrypt_hash, requires_change, created_at, last_change_at)
		 VALUES (?, ?, ?, ?)`,
		bcryptHash, flagVal, now.Unix(), now.Unix())
	if err != nil {
		return nil, fmt.Errorf("webapp: insert user: %w", err)
	}
	id, err := res.LastInsertId()
	if err != nil {
		return nil, fmt.Errorf("webapp: last insert id: %w", err)
	}
	return &User{
		ID:             id,
		BcryptHash:     bcryptHash,
		RequiresChange: requiresChange,
		CreatedAt:      now,
		LastChangeAt:   now,
	}, nil
}

// CreateFirstRunUser provisions the single webapp user with the
// requires_change flag set so the operator is forced to update the
// password before any other page is reachable. Used by the cmd
// binary on first start after minting the random initial password.
func (s *Store) CreateFirstRunUser(ctx context.Context, bcryptHash string) (*User, error) {
	return s.CreateUser(ctx, bcryptHash, true)
}

// EnsureAnonymousUser provisions a placeholder user row with an
// unusable bcrypt hash when no user exists yet. Used by the basic-auth
// login flow: the session row needs a user_id foreign key target, but
// the operator authenticates with WORKER_LOGIN/WORKER_PASSWORD, not a
// bcrypt password. Idempotent — returns nil if a row already exists.
//
// The hash is a fixed bcrypt of a random unguessable value so even an
// attacker who reads the SQLite file cannot derive a usable password.
func (s *Store) EnsureAnonymousUser(ctx context.Context) error {
	existing, err := s.GetUser(ctx)
	if err == nil && existing != nil {
		return nil
	}
	unusable, err := HashPassword("!" + newAnonymousSecret() + "!")
	if err != nil {
		return fmt.Errorf("webapp: hash anonymous password: %w", err)
	}
	_, err = s.CreateUser(ctx, unusable, false)
	if err != nil {
		return fmt.Errorf("webapp: create anonymous user: %w", err)
	}
	return nil
}

func newAnonymousSecret() string {
	var b [32]byte
	if _, err := rand.Read(b[:]); err != nil {
		// Fall back to a time-derived secret — still unguessable
		// from outside the worker process because we only need the
		// hash to be non-recoverable, not the plaintext.
		return fmt.Sprintf("anon-%d", time.Now().UnixNano())
	}
	return hex.EncodeToString(b[:])
}

// UpdatePassword rotates the user's bcrypt hash and clears the
// requires_change flag. beforeHash is recorded for the audit row.
func (s *Store) UpdatePassword(ctx context.Context, userID int64, newHash string) error {
	now := time.Now().UTC()
	res, err := s.db.ExecContext(ctx,
		`UPDATE webapp_users SET bcrypt_hash = ?, requires_change = 0, last_change_at = ? WHERE id = ?`,
		newHash, now.Unix(), userID)
	if err != nil {
		return fmt.Errorf("webapp: update password: %w", err)
	}
	n, err := res.RowsAffected()
	if err != nil {
		return fmt.Errorf("webapp: rows affected: %w", err)
	}
	if n == 0 {
		return fmt.Errorf("webapp: user %d not found", userID)
	}
	return nil
}

// MarkRequiresChange flips the requires_change flag back on. Used by
// the password-change handler when the new password fails the
// minimal-strength check (so the operator is forced to retry).
func (s *Store) MarkRequiresChange(ctx context.Context, userID int64) error {
	_, err := s.db.ExecContext(ctx,
		`UPDATE webapp_users SET requires_change = 1 WHERE id = ?`, userID)
	return err
}

// Session is a row from webapp_sessions.
type Session struct {
	ID         string
	UserID     int64
	CSRFToken  string
	IP         string
	UA         string
	CreatedAt  time.Time
	LastSeenAt time.Time
	ExpiresAt  time.Time
}

// CreateSession inserts a new session row and returns it.
func (s *Store) CreateSession(ctx context.Context, sess *Session) error {
	if sess.ID == "" || sess.CSRFToken == "" {
		return errors.New("webapp: session id and csrf token are required")
	}
	if sess.CreatedAt.IsZero() {
		sess.CreatedAt = time.Now().UTC()
	}
	if sess.LastSeenAt.IsZero() {
		sess.LastSeenAt = sess.CreatedAt
	}
	if sess.ExpiresAt.IsZero() {
		return errors.New("webapp: session expires_at is required")
	}
	_, err := s.db.ExecContext(ctx,
		`INSERT INTO webapp_sessions
		 (id, user_id, csrf_token, ip, ua, created_at, last_seen_at, expires_at)
		 VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
		sess.ID, sess.UserID, sess.CSRFToken, sess.IP, sess.UA,
		sess.CreatedAt.Unix(), sess.LastSeenAt.Unix(), sess.ExpiresAt.Unix())
	if err != nil {
		return fmt.Errorf("webapp: insert session: %w", err)
	}
	return nil
}

// GetSession fetches a session by id. Returns sql.ErrNoRows if the
// session is unknown or expired.
func (s *Store) GetSession(ctx context.Context, id string) (*Session, error) {
	row := s.db.QueryRowContext(ctx,
		`SELECT id, user_id, csrf_token, ip, ua, created_at, last_seen_at, expires_at
		 FROM webapp_sessions WHERE id = ?`, id)
	sess := &Session{}
	var created, lastSeen, expires int64
	if err := row.Scan(&sess.ID, &sess.UserID, &sess.CSRFToken, &sess.IP, &sess.UA,
		&created, &lastSeen, &expires); err != nil {
		return nil, err
	}
	sess.CreatedAt = time.Unix(created, 0).UTC()
	sess.LastSeenAt = time.Unix(lastSeen, 0).UTC()
	sess.ExpiresAt = time.Unix(expires, 0).UTC()
	if time.Now().UTC().After(sess.ExpiresAt) {
		return nil, sql.ErrNoRows
	}
	return sess, nil
}

// TouchSession updates last_seen_at and slides the absolute expiry
// forward by the idle window, if and only if the session is still
// inside its absolute cap.
func (s *Store) TouchSession(ctx context.Context, id string, idle, absolute time.Duration) error {
	now := time.Now().UTC()
	row := s.db.QueryRowContext(ctx,
		`SELECT last_seen_at FROM webapp_sessions WHERE id = ?`, id)
	var lastSeen int64
	if err := row.Scan(&lastSeen); err != nil {
		return err
	}
	created := time.Unix(lastSeen, 0).UTC()
	newLast := now
	newExp := now.Add(idle)
	absCap := created.Add(absolute)
	if newExp.After(absCap) {
		newExp = absCap
	}
	_, err := s.db.ExecContext(ctx,
		`UPDATE webapp_sessions SET last_seen_at = ?, expires_at = ? WHERE id = ?`,
		newLast.Unix(), newExp.Unix(), id)
	return err
}

// DeleteSession removes the session by id. Used on logout.
func (s *Store) DeleteSession(ctx context.Context, id string) error {
	_, err := s.db.ExecContext(ctx,
		`DELETE FROM webapp_sessions WHERE id = ?`, id)
	return err
}

// AuditEntry is a single audit-log row.
type AuditEntry struct {
	ID         int64
	Actor      string
	Role       string
	AuthMode   string
	IP         string
	UA         string
	Action     string
	Target     string
	BeforeHash string
	AfterHash  string
	TS         time.Time
}

// WriteAudit appends a row to webapp_audit.
func (s *Store) WriteAudit(ctx context.Context, e *AuditEntry) error {
	if e.Action == "" {
		return errors.New("webapp: audit action is required")
	}
	if e.TS.IsZero() {
		e.TS = time.Now().UTC()
	}
	_, err := s.db.ExecContext(ctx,
		`INSERT INTO webapp_audit
		 (actor, role, auth_mode, ip, ua, action, target, before_hash, after_hash, ts)
		 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		e.Actor, e.Role, e.AuthMode, e.IP, e.UA, e.Action, e.Target,
		e.BeforeHash, e.AfterHash, e.TS.Unix())
	if err != nil {
		return fmt.Errorf("webapp: insert audit: %w", err)
	}
	return nil
}

// RecentAudit returns the most recent n audit rows in reverse-chron
// order. Used by tests; the UI does not need this view in Phase 1.
func (s *Store) RecentAudit(ctx context.Context, limit int) ([]AuditEntry, error) {
	rows, err := s.db.QueryContext(ctx,
		`SELECT id, actor, role, auth_mode, ip, ua, action, target,
		        before_hash, after_hash, ts
		 FROM webapp_audit ORDER BY id DESC LIMIT ?`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close() //nolint:errcheck
	var out []AuditEntry
	for rows.Next() {
		e := AuditEntry{}
		var ts int64
		if err := rows.Scan(&e.ID, &e.Actor, &e.Role, &e.AuthMode, &e.IP, &e.UA,
			&e.Action, &e.Target, &e.BeforeHash, &e.AfterHash, &ts); err != nil {
			return nil, err
		}
		e.TS = time.Unix(ts, 0).UTC()
		out = append(out, e)
	}
	return out, rows.Err()
}

// PruneAudit deletes audit rows older than retention. Called from a
// daily timer in the server.
func (s *Store) PruneAudit(ctx context.Context, retention time.Duration) (int64, error) {
	cutoff := time.Now().UTC().Add(-retention).Unix()
	res, err := s.db.ExecContext(ctx,
		`DELETE FROM webapp_audit WHERE ts < ?`, cutoff)
	if err != nil {
		return 0, err
	}
	return res.RowsAffected()
}

// App is the inventory cache row.
type App struct {
	ID       int64
	Name     string
	Source   string
	PID      int
	Ports    string
	StartTS  int64
	LastSeen time.Time
	JSONBlob string
}

// ReplaceApps deletes the entire inventory and re-inserts the given
// snapshot. Called from the inventory refresh loop. Wrapped in a
// transaction so the page never reads a half-replaced list.
func (s *Store) ReplaceApps(ctx context.Context, apps []App) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx, `DELETE FROM webapp_apps`); err != nil {
		_ = tx.Rollback()
		return err
	}
	for _, a := range apps {
		if _, err := tx.ExecContext(ctx,
			`INSERT INTO webapp_apps (name, source, pid, ports, start_ts, last_seen, json_blob)
			 VALUES (?, ?, ?, ?, ?, ?, ?)`,
			a.Name, a.Source, a.PID, a.Ports, a.StartTS, a.LastSeen.Unix(), a.JSONBlob); err != nil {
			_ = tx.Rollback()
			return err
		}
	}
	return tx.Commit()
}

// ListApps returns the inventory rows in insertion order. Used by the
// discovered-apps handler.
func (s *Store) ListApps(ctx context.Context) ([]App, error) {
	rows, err := s.db.QueryContext(ctx,
		`SELECT id, name, source, pid, ports, start_ts, last_seen, json_blob
		 FROM webapp_apps ORDER BY id ASC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close() //nolint:errcheck
	var out []App
	for rows.Next() {
		a := App{}
		var lastSeen int64
		if err := rows.Scan(&a.ID, &a.Name, &a.Source, &a.PID, &a.Ports,
			&a.StartTS, &lastSeen, &a.JSONBlob); err != nil {
			return nil, err
		}
		a.LastSeen = time.Unix(lastSeen, 0).UTC()
		out = append(out, a)
	}
	return out, rows.Err()
}