internet_use_control/internal/store/lists.go

// Per-device allow and block lists, and device lookup by MQTT topic
// (AGENTS.md §4 FR-3/FR-4, §7 blocklist/allowlist, §11 Phase 6).
//
// A list entry is one value tagged with a kind: "app" (an Android package name
// or a Windows process name) or "site" (a hostname). The editor on a device
// page edits the entries scoped to that device. A row with a NULL device_id
// applies to every device (a group entry, §7); the data model supports it and
// the effective state merges it, but the Phase 6 editor only manages
// device-scoped rows.
package store

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"strings"
	"time"
)

// List table names. They are package constants, never caller input, so they are
// safe to interpolate into a query.
const (
	TableBlocklist = "blocklist"
	TableAllowlist = "allowlist"
)

// List kind values allowed by the blocklist/allowlist CHECK constraint.
const (
	ListKindApp  = "app"
	ListKindSite = "site"
)

// ListEntry is one blocklist or allowlist row.
type ListEntry struct {
	ID        int64
	DeviceID  *int64 // nil = applies to every device
	Kind      string
	Value     string
	CreatedAt time.Time
}

// ValidListKind reports whether kind is "app" or "site".
func ValidListKind(kind string) bool {
	return kind == ListKindApp || kind == ListKindSite
}

// listTable maps a public constant to the real table name, rejecting anything
// else so a caller can never select an arbitrary table.
func listTable(table string) (string, error) {
	switch table {
	case TableBlocklist:
		return "blocklist", nil
	case TableAllowlist:
		return "allowlist", nil
	default:
		return "", fmt.Errorf("unknown list %q", table)
	}
}

// normalizeListValue trims a value and rejects blanks, over-long values and
// control characters. Hostnames are lower-cased because DNS is
// case-insensitive; app identifiers keep their case.
func normalizeListValue(kind, value string) (string, error) {
	if !ValidListKind(kind) {
		return "", errors.New(`kind must be "app" or "site"`)
	}
	value = strings.TrimSpace(value)
	if value == "" {
		return "", errors.New("value is required")
	}
	if len(value) > 200 {
		return "", errors.New("value must be 200 characters or fewer")
	}
	for _, r := range value {
		if r < 0x20 || r == 0x7f {
			return "", errors.New("value must not contain control characters")
		}
	}
	if kind == ListKindSite {
		value = strings.ToLower(value)
	}
	return value, nil
}

// DeviceLists returns the values that stay allowed and the values that are
// blocked for one device. A row with a NULL device_id applies to every device
// (§7). Rows are returned in insertion order, de-duplicated.
func (s *Store) DeviceLists(ctx context.Context, id int64) (allow, block []string, err error) {
	if allow, err = s.listValues(ctx, TableAllowlist, id); err != nil {
		return nil, nil, err
	}
	if block, err = s.listValues(ctx, TableBlocklist, id); err != nil {
		return nil, nil, err
	}
	return allow, block, nil
}

func (s *Store) listValues(ctx context.Context, table string, id int64) ([]string, error) {
	name, err := listTable(table)
	if err != nil {
		return nil, err
	}
	rows, err := s.db.QueryContext(ctx,
		`SELECT value FROM `+name+` WHERE device_id = ? OR device_id IS NULL ORDER BY id`, id)
	if err != nil {
		return nil, fmt.Errorf("list %s: %w", name, err)
	}
	defer rows.Close()

	out := make([]string, 0, 4)
	seen := make(map[string]struct{}, 4)
	for rows.Next() {
		var v string
		if err := rows.Scan(&v); err != nil {
			return nil, fmt.Errorf("scan %s: %w", name, err)
		}
		v = strings.TrimSpace(v)
		if v == "" {
			continue
		}
		key := v
		if _, dup := seen[key]; dup {
			continue
		}
		seen[key] = struct{}{}
		out = append(out, v)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("list %s: %w", name, err)
	}
	return out, nil
}

// ListEntries returns one device's blocklist or allowlist rows, oldest first.
func (s *Store) ListEntries(ctx context.Context, deviceID int64, table string) ([]ListEntry, error) {
	name, err := listTable(table)
	if err != nil {
		return nil, err
	}
	rows, err := s.db.QueryContext(ctx, `
		SELECT id, device_id, kind, value, created_at
		FROM `+name+` WHERE device_id = ?
		ORDER BY id`, deviceID)
	if err != nil {
		return nil, fmt.Errorf("list entries: %w", err)
	}
	defer rows.Close()

	out := make([]ListEntry, 0, 8)
	for rows.Next() {
		var (
			e       ListEntry
			devID   sql.NullInt64
			created sql.NullString
		)
		if err := rows.Scan(&e.ID, &devID, &e.Kind, &e.Value, &created); err != nil {
			return nil, fmt.Errorf("scan entry: %w", err)
		}
		if devID.Valid {
			id := devID.Int64
			e.DeviceID = &id
		}
		e.CreatedAt = parseTime(created.String)
		out = append(out, e)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("list entries: %w", err)
	}
	return out, nil
}

// AddListEntry appends one blocklist or allowlist value for a device and
// appends one audit entry in the same transaction. A value that is already
// present for the device (same kind and value, case-insensitively) is refused,
// so repeated submits do not pile up.
func (s *Store) AddListEntry(ctx context.Context, deviceID int64, table, kind, value, actor string) (*ListEntry, error) {
	name, err := listTable(table)
	if err != nil {
		return nil, err
	}
	value, err = normalizeListValue(kind, value)
	if err != nil {
		return nil, err
	}

	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return nil, fmt.Errorf("add entry: %w", err)
	}
	defer func() { _ = tx.Rollback() }()

	var exists int
	err = tx.QueryRowContext(ctx,
		`SELECT 1 FROM `+name+` WHERE device_id = ? AND kind = ? AND lower(value) = lower(?) LIMIT 1`,
		deviceID, kind, value).Scan(&exists)
	if err == nil {
		return nil, fmt.Errorf("%s %q is already on the list", kind, value)
	}
	if !errors.Is(err, sql.ErrNoRows) {
		return nil, fmt.Errorf("check entry: %w", err)
	}

	res, err := tx.ExecContext(ctx,
		`INSERT INTO `+name+` (device_id, kind, value) VALUES (?, ?, ?)`, deviceID, kind, value)
	if err != nil {
		return nil, fmt.Errorf("insert entry: %w", err)
	}
	id, err := res.LastInsertId()
	if err != nil {
		return nil, fmt.Errorf("entry id: %w", err)
	}
	listName := "allowlist"
	if table == TableBlocklist {
		listName = "blocklist"
	}
	if err := insertAudit(ctx, tx, actor, "list.added", &deviceID,
		fmt.Sprintf("%s %s %s", listName, kind, value)); err != nil {
		return nil, err
	}
	if err := tx.Commit(); err != nil {
		return nil, fmt.Errorf("commit entry: %w", err)
	}
	return &ListEntry{ID: id, DeviceID: &deviceID, Kind: kind, Value: value}, nil
}

// DeleteListEntry removes one device-scoped row and appends one audit entry.
// A row that belongs to another device is not touched: the id and device_id
// must both match, so a guessed id cannot edit someone else's list.
func (s *Store) DeleteListEntry(ctx context.Context, deviceID, entryID int64, table, actor string) error {
	name, err := listTable(table)
	if err != nil {
		return err
	}

	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("delete entry: %w", err)
	}
	defer func() { _ = tx.Rollback() }()

	var kind, value string
	err = tx.QueryRowContext(ctx,
		`SELECT kind, value FROM `+name+` WHERE id = ? AND device_id = ?`,
		entryID, deviceID).Scan(&kind, &value)
	if errors.Is(err, sql.ErrNoRows) {
		return ErrNotFound
	}
	if err != nil {
		return fmt.Errorf("read entry: %w", err)
	}

	if _, err := tx.ExecContext(ctx, `DELETE FROM `+name+` WHERE id = ?`, entryID); err != nil {
		return fmt.Errorf("delete entry: %w", err)
	}
	listName := "allowlist"
	if table == TableBlocklist {
		listName = "blocklist"
	}
	if err := insertAudit(ctx, tx, actor, "list.removed", &deviceID,
		fmt.Sprintf("%s %s %s", listName, kind, value)); err != nil {
		return err
	}
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit delete: %w", err)
	}
	return nil
}

// DeviceByConfigTopic finds a device from a topic the device wrote to, for
// example "restrict/harry_phone_2/status". Only the Control Service reads the
// topic table, so a topic the service does not know is ignored.
func (s *Store) DeviceByConfigTopic(ctx context.Context, topic string) (*Device, error) {
	dev, err := scanDevice(s.db.QueryRowContext(ctx, selectDevice+` WHERE d.topic = ?`, topic))
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("device by topic: %w", err)
	}
	return dev, nil
}