Skip to content

Agent Mesh

80.7 KB
Raw Blame
// Copyright 2026 Google LLC
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

package storage

import (
	"context"
	"crypto/ed25519"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"os"
	"strings"
	"time"

	"github.com/google/agentmesh/api"
	log "github.com/ipfs/go-log/v2"
	"google.golang.org/protobuf/encoding/protojson"

	// Register PG and SQLite drivers
	_ "github.com/jackc/pgx/v5/stdlib"
	_ "modernc.org/sqlite"
)

var logger = log.Logger("agentmesh-storage")

// SQLStore implements Store interface using database/sql.
type SQLStore struct {
	db         *sql.DB
	driverName string
}

// NewSQLStore creates a new SQLStore, connects to the database, and initializes tables.
func NewSQLStore(driverName, dataSourceName string) (*SQLStore, error) {
	actualDriver := driverName
	if driverName == "postgres" || driverName == "postgresql" {
		actualDriver = "pgx"
	}
	if driverName == "sqlite" && !strings.Contains(dataSourceName, "?") {
		// SQLite driver options are configured via DSN query parameters so they apply
		// to all connections in the connection pool. We default to WAL mode for write
		// concurrency and busy_timeout to prevent SQLITE_BUSY locking errors.
		// Callers (e.g. integration tests that copy DB files) can override this by
		// passing a DSN containing custom query parameter parameters (e.g. "?_pragma=journal_mode(DELETE)&_pragma=busy_timeout(5000)").
		dataSourceName = dataSourceName + "?_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)"
	}
	if driverName == "sqlite" {
		if err := restrictSQLiteFileMode(dataSourceName); err != nil {
			return nil, err
		}
	}
	db, err := sql.Open(actualDriver, dataSourceName)
	if err != nil {
		return nil, fmt.Errorf("failed to open database: %w", err)
	}

	// For postgres, retry Ping() to allow DB container to finish booting
	if actualDriver == "pgx" {
		var pingErr error
		for i := 0; i < 30; i++ {
			pingErr = db.Ping()
			if pingErr == nil {
				break
			}
			time.Sleep(1 * time.Second)
		}
		if pingErr != nil {
			_ = db.Close()
			return nil, fmt.Errorf("failed to ping database: %w", pingErr)
		}

		// Bound the pool so a busy control-plane can't exhaust the postgres
		// server's max_connections; recycle connections periodically so
		// long-lived ones don't accumulate stale server-side state.
		db.SetMaxOpenConns(25)
		db.SetMaxIdleConns(5)
		db.SetConnMaxLifetime(5 * time.Minute)
	} else {
		if strings.Contains(dataSourceName, ":memory:") {
			// A lone connection keeps ":memory:" DSNs (used by tests)
			// consistent, since each additional sql.DB connection to
			// ":memory:" is otherwise its own independent, empty database.
			db.SetMaxOpenConns(1)
			db.SetMaxIdleConns(1)
		} else {
			// File-backed SQLite in WAL mode supports concurrent readers
			// with a single writer, so allow a small pool instead of
			// serializing every access through one connection.
			db.SetMaxOpenConns(10)
			db.SetMaxIdleConns(2)
		}
	}

	store := &SQLStore{
		db:         db,
		driverName: driverName,
	}

	if err := store.initSchema(); err != nil {
		_ = db.Close()
		return nil, fmt.Errorf("failed to initialize schema: %w", err)
	}

	return store, nil
}

func (s *SQLStore) isPostgres() bool {
	return strings.Contains(s.driverName, "postgres") || strings.Contains(s.driverName, "pgx")
}

// sqliteFilePath extracts the on-disk path from a SQLite DSN, or "" for
// in-memory databases.
func sqliteFilePath(dsn string) string {
	path := strings.TrimPrefix(dsn, "file:")
	if i := strings.IndexByte(path, '?'); i >= 0 {
		path = path[:i]
	}
	if path == "" || strings.Contains(path, ":memory:") {
		return ""
	}
	return path
}

// restrictSQLiteFileMode makes the database owner-only. The keyring holds the
// mesh signing private keys, and the SQLite driver otherwise creates the file
// 0644 (minus umask). The file is created here, before the driver opens it,
// because SQLite gives the -wal and -shm side files the main file's mode.
func restrictSQLiteFileMode(dsn string) error {
	path := sqliteFilePath(dsn)
	if path == "" {
		return nil
	}
	f, err := os.OpenFile(path, os.O_RDWR|os.O_CREATE, 0o600)
	if err != nil {
		return fmt.Errorf("failed to create database file %s: %w", path, err)
	}
	_ = f.Close()
	for _, p := range []string{path, path + "-wal", path + "-shm"} {
		if err := os.Chmod(p, 0o600); err != nil && !errors.Is(err, os.ErrNotExist) {
			return fmt.Errorf("failed to restrict permissions on %s: %w", p, err)
		}
	}
	return nil
}

type migration struct {
	version  int
	sqlite   []string
	postgres []string
}

var migrations = []migration{
	{
		version: 1,
		postgres: []string{
			`CREATE TABLE IF NOT EXISTS keyring (
				id SERIAL PRIMARY KEY,
				private_key BYTEA NOT NULL,
				public_key BYTEA NOT NULL UNIQUE,
				expiration BIGINT,
				created_at BIGINT NOT NULL
			)`,
			`CREATE TABLE IF NOT EXISTS nodes (
				peer_id VARCHAR(255) PRIMARY KEY,
				public_key BYTEA NOT NULL,
				biscuit_token BYTEA NOT NULL,
				role VARCHAR(64) NOT NULL,
				enrollment_type VARCHAR(64) NOT NULL,
				claims_json TEXT,
				enrolled_at BIGINT NOT NULL,
				expires_at BIGINT NOT NULL,
				banned BOOLEAN DEFAULT FALSE NOT NULL
			)`,
			`CREATE TABLE IF NOT EXISTS routers (
				peer_id VARCHAR(255) PRIMARY KEY,
				multiaddresses TEXT NOT NULL,
				last_lease_renewal BIGINT NOT NULL,
				expires_at BIGINT NOT NULL
			)`,
			`CREATE TABLE IF NOT EXISTS policies (
				id VARCHAR(255) PRIMARY KEY,
				content TEXT NOT NULL,
				updated_at BIGINT NOT NULL
			)`,
			`CREATE TABLE IF NOT EXISTS bootstrap_tokens (
				id VARCHAR(64) PRIMARY KEY,
				token_hash VARCHAR(64) UNIQUE NOT NULL,
				role VARCHAR(64) NOT NULL,
				max_usages INT NOT NULL,
				usages_count INT NOT NULL,
				description TEXT,
				created_at BIGINT NOT NULL,
				expires_at BIGINT NOT NULL
			)`,
			`CREATE TABLE IF NOT EXISTS enrollment_requests (
				id VARCHAR(64) PRIMARY KEY,
				peer_id VARCHAR(255) UNIQUE NOT NULL,
				public_key BYTEA NOT NULL,
				token_id VARCHAR(64) REFERENCES bootstrap_tokens(id),
				status INT NOT NULL,
				biscuit_token BYTEA,
				created_at BIGINT NOT NULL,
				resolved_at BIGINT,
				resolved_by VARCHAR(255)
			)`,
		},
		sqlite: []string{
			`CREATE TABLE IF NOT EXISTS keyring (
				id INTEGER PRIMARY KEY AUTOINCREMENT,
				private_key BLOB NOT NULL,
				public_key BLOB NOT NULL UNIQUE,
				expiration BIGINT,
				created_at BIGINT NOT NULL
			)`,
			`CREATE TABLE IF NOT EXISTS nodes (
				peer_id TEXT PRIMARY KEY,
				public_key BLOB NOT NULL,
				biscuit_token BLOB NOT NULL,
				role TEXT NOT NULL,
				enrollment_type TEXT NOT NULL,
				claims_json TEXT,
				enrolled_at BIGINT NOT NULL,
				expires_at BIGINT NOT NULL,
				banned BOOLEAN DEFAULT FALSE NOT NULL
			)`,
			`CREATE TABLE IF NOT EXISTS routers (
				peer_id TEXT PRIMARY KEY,
				multiaddresses TEXT NOT NULL,
				last_lease_renewal BIGINT NOT NULL,
				expires_at BIGINT NOT NULL
			)`,
			`CREATE TABLE IF NOT EXISTS policies (
				id TEXT PRIMARY KEY,
				content TEXT NOT NULL,
				updated_at BIGINT NOT NULL
			)`,
			`CREATE TABLE IF NOT EXISTS bootstrap_tokens (
				id TEXT PRIMARY KEY,
				token_hash TEXT UNIQUE NOT NULL,
				role TEXT NOT NULL,
				max_usages INTEGER NOT NULL,
				usages_count INTEGER NOT NULL,
				description TEXT,
				created_at BIGINT NOT NULL,
				expires_at BIGINT NOT NULL
			)`,
			`CREATE TABLE IF NOT EXISTS enrollment_requests (
				id TEXT PRIMARY KEY,
				peer_id TEXT UNIQUE NOT NULL,
				public_key BLOB NOT NULL,
				token_id TEXT REFERENCES bootstrap_tokens(id),
				status INTEGER NOT NULL,
				biscuit_token BLOB,
				created_at BIGINT NOT NULL,
				resolved_at BIGINT,
				resolved_by TEXT
			)`,
		},
	},
	{
		version: 2,
		postgres: []string{
			`ALTER TABLE routers ADD COLUMN IF NOT EXISTS connected_peers TEXT`,
			`ALTER TABLE routers ADD COLUMN IF NOT EXISTS dht_size INT`,
		},
		sqlite: []string{
			`ALTER TABLE routers ADD COLUMN connected_peers TEXT`,
			`ALTER TABLE routers ADD COLUMN dht_size INTEGER`,
		},
	},
	{
		version: 3,
		postgres: []string{
			`CREATE TABLE IF NOT EXISTS users (
				id VARCHAR(255) PRIMARY KEY,
				email VARCHAR(255) NOT NULL,
				role VARCHAR(64) NOT NULL,
				created_at BIGINT NOT NULL
			)`,
			`ALTER TABLE nodes ADD COLUMN owner_id VARCHAR(255) REFERENCES users(id)`,
			`ALTER TABLE bootstrap_tokens ADD COLUMN owner_id VARCHAR(255) REFERENCES users(id)`,
		},
		sqlite: []string{
			`CREATE TABLE IF NOT EXISTS users (
				id TEXT PRIMARY KEY,
				email TEXT NOT NULL,
				role TEXT NOT NULL,
				created_at BIGINT NOT NULL
			)`,
			`ALTER TABLE nodes ADD COLUMN owner_id TEXT REFERENCES users(id)`,
			`ALTER TABLE bootstrap_tokens ADD COLUMN owner_id TEXT REFERENCES users(id)`,
		},
	},
	{
		version: 4,
		postgres: []string{
			`DROP TABLE IF EXISTS policies`,
			`CREATE TABLE IF NOT EXISTS roles (
				name VARCHAR(64) PRIMARY KEY,
				description TEXT,
				created_at BIGINT NOT NULL
			)`,
			`CREATE TABLE IF NOT EXISTS role_permissions (
				id SERIAL PRIMARY KEY,
				role_name VARCHAR(64) REFERENCES roles(name) ON DELETE CASCADE,
				resource_type VARCHAR(64) NOT NULL,
				resource_value TEXT NOT NULL
			)`,
			`CREATE TABLE IF NOT EXISTS role_bindings (
				id SERIAL PRIMARY KEY,
				role_name VARCHAR(64) REFERENCES roles(name) ON DELETE CASCADE,
				member VARCHAR(255) NOT NULL
			)`,
		},
		sqlite: []string{
			`DROP TABLE IF EXISTS policies`,
			`CREATE TABLE IF NOT EXISTS roles (
				name TEXT PRIMARY KEY,
				description TEXT,
				created_at BIGINT NOT NULL
			)`,
			`CREATE TABLE IF NOT EXISTS role_permissions (
				id INTEGER PRIMARY KEY AUTOINCREMENT,
				role_name TEXT REFERENCES roles(name) ON DELETE CASCADE,
				resource_type TEXT NOT NULL,
				resource_value TEXT NOT NULL
			)`,
			`CREATE TABLE IF NOT EXISTS role_bindings (
				id INTEGER PRIMARY KEY AUTOINCREMENT,
				role_name TEXT REFERENCES roles(name) ON DELETE CASCADE,
				member TEXT NOT NULL
			)`,
		},
	},
	{
		version: 5,
		postgres: []string{
			`ALTER TABLE nodes ADD COLUMN region VARCHAR(16) DEFAULT '' NOT NULL`,
			`ALTER TABLE enrollment_requests ADD COLUMN region VARCHAR(16) DEFAULT '' NOT NULL`,
		},
		sqlite: []string{
			`ALTER TABLE nodes ADD COLUMN region TEXT DEFAULT '' NOT NULL`,
			`ALTER TABLE enrollment_requests ADD COLUMN region TEXT DEFAULT '' NOT NULL`,
		},
	},
	{
		// rotation_lock backs ClaimKeyRotation: a singleton row whose
		// next_rotation_at only advances via a conditional UPDATE, so it
		// doubles as a driver-agnostic mutex across control-plane replicas.
		version: 6,
		postgres: []string{
			`CREATE TABLE IF NOT EXISTS rotation_lock (
				id SMALLINT PRIMARY KEY,
				next_rotation_at BIGINT NOT NULL
			)`,
			`INSERT INTO rotation_lock (id, next_rotation_at) VALUES (1, 0) ON CONFLICT (id) DO NOTHING`,
		},
		sqlite: []string{
			`CREATE TABLE IF NOT EXISTS rotation_lock (
				id INTEGER PRIMARY KEY,
				next_rotation_at BIGINT NOT NULL
			)`,
			`INSERT OR IGNORE INTO rotation_lock (id, next_rotation_at) VALUES (1, 0)`,
		},
	},
	{
		// Replaces the region-specific claim with a generic key=value label
		// set (see api/labels.go): no built-in taxonomy or hierarchy, just a
		// JSON-encoded map an operator can populate with as many labels as
		// their composition needs.
		version: 7,
		postgres: []string{
			`ALTER TABLE nodes DROP COLUMN IF EXISTS region`,
			`ALTER TABLE nodes ADD COLUMN IF NOT EXISTS labels_json TEXT DEFAULT '' NOT NULL`,
			`ALTER TABLE enrollment_requests DROP COLUMN IF EXISTS region`,
			`ALTER TABLE enrollment_requests ADD COLUMN IF NOT EXISTS labels_json TEXT DEFAULT '' NOT NULL`,
		},
		sqlite: []string{
			`ALTER TABLE nodes DROP COLUMN region`,
			`ALTER TABLE nodes ADD COLUMN labels_json TEXT DEFAULT '' NOT NULL`,
			`ALTER TABLE enrollment_requests DROP COLUMN region`,
			`ALTER TABLE enrollment_requests ADD COLUMN labels_json TEXT DEFAULT '' NOT NULL`,
		},
	},
	{
		// A ban must outlive the keypair that carried it: banning a node also
		// bans the enrolled OIDC identity (issuer|subject), which a fresh
		// peer id cannot shed.
		version: 8,
		postgres: []string{
			`CREATE TABLE IF NOT EXISTS banned_identities (
				identity VARCHAR(512) PRIMARY KEY,
				banned_at BIGINT NOT NULL
			)`,
		},
		sqlite: []string{
			`CREATE TABLE IF NOT EXISTS banned_identities (
				identity TEXT PRIMARY KEY,
				banned_at BIGINT NOT NULL
			)`,
		},
	},
	{
		// Soft revoke, not delete: enrollment_requests.token_id has an FK to
		// this table, and a revoked_at column keeps the audit trail distinct
		// from the token's own natural expiry.
		version: 9,
		postgres: []string{
			`ALTER TABLE bootstrap_tokens ADD COLUMN IF NOT EXISTS revoked_at BIGINT`,
		},
		sqlite: []string{
			`ALTER TABLE bootstrap_tokens ADD COLUMN revoked_at BIGINT`,
		},
	},
	{
		// Opt-in autonomous recovery (#367): a per-node flag, seeded from the
		// enrolling bootstrap token, that lets /refresh re-issue a biscuit
		// whose signing key has been retired. Deny by default.
		version: 10,
		postgres: []string{
			`ALTER TABLE nodes ADD COLUMN IF NOT EXISTS autonomous_recovery BOOLEAN DEFAULT FALSE NOT NULL`,
			`ALTER TABLE bootstrap_tokens ADD COLUMN IF NOT EXISTS autonomous_recovery BOOLEAN DEFAULT FALSE NOT NULL`,
		},
		sqlite: []string{
			`ALTER TABLE nodes ADD COLUMN autonomous_recovery BOOLEAN DEFAULT FALSE NOT NULL`,
			`ALTER TABLE bootstrap_tokens ADD COLUMN autonomous_recovery BOOLEAN DEFAULT FALSE NOT NULL`,
		},
	},
	{
		// Identity bans are keyed on issuer|subject; a user row keyed on the
		// bare subject could not be matched against them, so the surfaces a
		// user drives with an ID token (bootstrap tokens, revocation) did not
		// see the ban. Empty for rows from before this migration until the
		// user next logs in.
		version: 11,
		postgres: []string{
			`ALTER TABLE users ADD COLUMN IF NOT EXISTS issuer VARCHAR(512) DEFAULT '' NOT NULL`,
		},
		sqlite: []string{
			`ALTER TABLE users ADD COLUMN issuer TEXT DEFAULT '' NOT NULL`,
		},
	},
	{
		// Egress destinations are part of the policy document (PolicyConfig.egress)
		// but are not role-scoped, so they get their own table. served_by is a
		// JSON array of role names and key=value labels.
		version: 12,
		postgres: []string{
			`CREATE TABLE IF NOT EXISTS egress_destinations (
				name VARCHAR(253) PRIMARY KEY,
				target_url TEXT NOT NULL,
				credential VARCHAR(64) NOT NULL,
				served_by TEXT NOT NULL
			)`,
		},
		sqlite: []string{
			`CREATE TABLE IF NOT EXISTS egress_destinations (
				name TEXT PRIMARY KEY,
				target_url TEXT NOT NULL,
				credential TEXT NOT NULL,
				served_by TEXT NOT NULL
			)`,
		},
	},
	{
		// Persist the full EgressDestination protojson (broker, inspection,
		// mode, ports, preserve_host, forward_context) while keeping the
		// existing columns for backward compatibility.
		version: 13,
		postgres: []string{
			`ALTER TABLE egress_destinations ADD COLUMN IF NOT EXISTS config_json TEXT DEFAULT '' NOT NULL`,
		},
		sqlite: []string{
			`ALTER TABLE egress_destinations ADD COLUMN config_json TEXT DEFAULT '' NOT NULL`,
		},
	},
	{
		// Persist OIDC ES256 signing keys and root Biscuit revocation IDs so
		// multi-replica control planes and restarts preserve JWKS and revocations.
		version: 14,
		postgres: []string{
			`CREATE TABLE IF NOT EXISTS oidc_keyring (
				id SERIAL PRIMARY KEY,
				kid VARCHAR(64) NOT NULL UNIQUE,
				private_key BYTEA NOT NULL,
				public_key BYTEA NOT NULL UNIQUE,
				expiration BIGINT,
				created_at BIGINT NOT NULL
			)`,
			`CREATE TABLE IF NOT EXISTS revoked_biscuits (
				revocation_id VARCHAR(255) PRIMARY KEY,
				expires_at BIGINT NOT NULL
			)`,
		},
		sqlite: []string{
			`CREATE TABLE IF NOT EXISTS oidc_keyring (
				id INTEGER PRIMARY KEY AUTOINCREMENT,
				kid TEXT NOT NULL UNIQUE,
				private_key BLOB NOT NULL,
				public_key BLOB NOT NULL UNIQUE,
				expiration BIGINT,
				created_at BIGINT NOT NULL
			)`,
			`CREATE TABLE IF NOT EXISTS revoked_biscuits (
				revocation_id TEXT PRIMARY KEY,
				expires_at BIGINT NOT NULL
			)`,
		},
	},
	{
		// Every BIGINT instant is unix milliseconds. These four tables held
		// seconds; the guard tells the two apart (10^11 seconds is the year
		// 5138, 10^11 milliseconds is 1973), so a row is converted once.
		version:  15,
		postgres: secondsToMillisMigration,
		sqlite:   secondsToMillisMigration,
	},
	{
		// The scans that run on every bootstrap (/info's ban set), every
		// lease lookup, every revocation pull and the node GC; nodes is the
		// one table that grows with the mesh.
		version:  16,
		postgres: indexMigration,
		sqlite:   indexMigration,
	},
	{
		// Migrate persisted roles, bindings, catalog service grants, enrolled
		// nodes, bootstrap tokens, and egress selectors from the legacy "sam:"
		// wire prefix to "mesh:".
		version:  17,
		postgres: meshPrefixMigration,
		sqlite:   meshPrefixMigration,
	},
	{
		// A router's load as of its last lease, for /info to list.
		version: 18,
		postgres: []string{
			`ALTER TABLE routers ADD COLUMN IF NOT EXISTS connections INT`,
			`ALTER TABLE routers ADD COLUMN IF NOT EXISTS connection_limit INT`,
		},
		sqlite: []string{
			`ALTER TABLE routers ADD COLUMN connections INTEGER`,
			`ALTER TABLE routers ADD COLUMN connection_limit INTEGER`,
		},
	},
	{
		// A user's node list searches owner_id, and a GET /user/nodes page
		// walks enrolled_at instead of sorting every matching row.
		version: 19,
		postgres: []string{
			`CREATE INDEX IF NOT EXISTS idx_nodes_owner_id ON nodes (owner_id)`,
			`CREATE INDEX IF NOT EXISTS idx_nodes_enrolled_at ON nodes (enrolled_at, peer_id)`,
		},
		sqlite: []string{
			`CREATE INDEX IF NOT EXISTS idx_nodes_owner_id ON nodes (owner_id)`,
			`CREATE INDEX IF NOT EXISTS idx_nodes_enrolled_at ON nodes (enrolled_at, peer_id)`,
		},
	},
	{
		// An ephemeral token enrolls members that are forgotten soon after
		// they fall silent; the flag is copied onto each node it enrolls.
		version: 20,
		postgres: []string{
			`ALTER TABLE bootstrap_tokens ADD COLUMN IF NOT EXISTS ephemeral BOOLEAN DEFAULT FALSE NOT NULL`,
			`ALTER TABLE nodes ADD COLUMN IF NOT EXISTS ephemeral BOOLEAN DEFAULT FALSE NOT NULL`,
		},
		sqlite: []string{
			`ALTER TABLE bootstrap_tokens ADD COLUMN ephemeral BOOLEAN DEFAULT FALSE NOT NULL`,
			`ALTER TABLE nodes ADD COLUMN ephemeral BOOLEAN DEFAULT FALSE NOT NULL`,
		},
	},
	{
		// Routers may advertise a public HTTP/HTTPS ingress base URL on /info.
		version: 21,
		postgres: []string{
			`ALTER TABLE routers ADD COLUMN IF NOT EXISTS ingress_url TEXT`,
		},
		sqlite: []string{
			`ALTER TABLE routers ADD COLUMN ingress_url TEXT`,
		},
	},
}

var meshPrefixMigration = []string{
	`INSERT INTO roles (name, description, created_at)
		SELECT 'mesh:role:' || SUBSTR(name, 10), description, created_at
		FROM roles WHERE name LIKE 'sam:role:%'
		ON CONFLICT (name) DO NOTHING`,
	`UPDATE role_permissions SET role_name = 'mesh:role:' || SUBSTR(role_name, 10) WHERE role_name LIKE 'sam:role:%'`,
	`UPDATE role_bindings SET role_name = 'mesh:role:' || SUBSTR(role_name, 10) WHERE role_name LIKE 'sam:role:%'`,
	`DELETE FROM roles WHERE name LIKE 'sam:role:%'`,
	`UPDATE role_bindings SET member = 'mesh:system:authenticated' WHERE member = 'sam:system:authenticated'`,
	`UPDATE role_permissions SET resource_value = 'system://mesh.catalog' WHERE resource_type = 'service' AND resource_value = 'system://sam.catalog'`,
	`UPDATE nodes SET role = 'mesh:role:' || SUBSTR(role, 10) WHERE role LIKE 'sam:role:%'`,
	`UPDATE bootstrap_tokens SET role = 'mesh:role:' || SUBSTR(role, 10) WHERE role LIKE 'sam:role:%'`,
	`UPDATE egress_destinations SET served_by = REPLACE(served_by, '"sam:role:', '"mesh:role:'), config_json = REPLACE(config_json, '"sam:role:', '"mesh:role:') WHERE served_by LIKE '%"sam:role:%' OR config_json LIKE '%"sam:role:%'`,
}

var indexMigration = []string{
	`CREATE INDEX IF NOT EXISTS idx_nodes_banned_expires_at ON nodes (banned, expires_at)`,
	`CREATE INDEX IF NOT EXISTS idx_routers_expires_at ON routers (expires_at)`,
	`CREATE INDEX IF NOT EXISTS idx_revoked_biscuits_expires_at ON revoked_biscuits (expires_at)`,
	`CREATE INDEX IF NOT EXISTS idx_enrollment_requests_created_at ON enrollment_requests (created_at)`,
}

var secondsToMillisMigration = []string{
	`UPDATE bootstrap_tokens SET created_at = created_at * 1000 WHERE created_at < 100000000000`,
	`UPDATE bootstrap_tokens SET expires_at = expires_at * 1000 WHERE expires_at < 100000000000`,
	`UPDATE bootstrap_tokens SET revoked_at = revoked_at * 1000 WHERE revoked_at IS NOT NULL AND revoked_at < 100000000000`,
	`UPDATE enrollment_requests SET created_at = created_at * 1000 WHERE created_at < 100000000000`,
	`UPDATE enrollment_requests SET resolved_at = resolved_at * 1000 WHERE resolved_at IS NOT NULL AND resolved_at < 100000000000`,
	`UPDATE users SET created_at = created_at * 1000 WHERE created_at < 100000000000`,
	`UPDATE banned_identities SET banned_at = banned_at * 1000 WHERE banned_at < 100000000000`,
}

func (s *SQLStore) initSchema() error {
	if s.isPostgres() {
		return s.initSchemaPostgres()
	}

	return s.initSchemaDefault()
}

// logSchemaMigrated reports a migration run in one line; an up-to-date
// database says nothing.
func logSchemaMigrated(from, to int) {
	if from == to {
		return
	}
	if from == 0 {
		logger.Infof("Initialized database schema at version %d", to)
		return
	}
	logger.Infof("Migrated database schema from version %d to %d", from, to)
}

func (s *SQLStore) initSchemaDefault() error {
	// Create schema_migrations table
	createMigrationsTable := `CREATE TABLE IF NOT EXISTS schema_migrations (version INTEGER PRIMARY KEY)`
	if _, err := s.db.Exec(createMigrationsTable); err != nil {
		return fmt.Errorf("failed to create schema_migrations table: %w", err)
	}

	var currentVersion int
	err := s.db.QueryRow("SELECT COALESCE(MAX(version), 0) FROM schema_migrations").Scan(&currentVersion)
	if err != nil {
		return fmt.Errorf("failed to check current schema version: %w", err)
	}
	startVersion := currentVersion

	for _, m := range migrations {
		if m.version <= currentVersion {
			continue
		}

		err := func() error {
			tx, err := s.db.Begin()
			if err != nil {
				return err
			}
			defer func() { _ = tx.Rollback() }()

			queries := m.sqlite
			for _, query := range queries {
				if _, err := tx.Exec(query); err != nil {
					errStr := strings.ToLower(err.Error())
					if strings.Contains(errStr, "duplicate column") || strings.Contains(errStr, "already exists") {
						continue
					}
					return fmt.Errorf("migration version %d failed: query %q failed: %w", m.version, query, err)
				}
			}

			insertQuery := s.rebind("INSERT INTO schema_migrations (version) VALUES (?)")
			if _, err := tx.Exec(insertQuery, m.version); err != nil {
				return fmt.Errorf("failed to update schema_migrations version: %w", err)
			}

			if err := tx.Commit(); err != nil {
				return err
			}
			logger.Debugf("Applied schema migration version %d", m.version)
			currentVersion = m.version
			return nil
		}()
		if err != nil {
			return err
		}
	}
	logSchemaMigrated(startVersion, currentVersion)
	return nil
}

func (s *SQLStore) initSchemaPostgres() error {
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	if _, err := tx.Exec("SELECT pg_advisory_xact_lock(7345892)"); err != nil {
		return fmt.Errorf("failed to acquire migration advisory lock: %w", err)
	}

	createMigrationsTable := `CREATE TABLE IF NOT EXISTS schema_migrations (version INTEGER PRIMARY KEY)`
	if _, err := tx.Exec(createMigrationsTable); err != nil {
		return fmt.Errorf("failed to create schema_migrations table: %w", err)
	}

	var currentVersion int
	err = tx.QueryRow("SELECT COALESCE(MAX(version), 0) FROM schema_migrations").Scan(&currentVersion)
	if err != nil {
		return fmt.Errorf("failed to check current schema version: %w", err)
	}
	startVersion := currentVersion

	for _, m := range migrations {
		if m.version <= currentVersion {
			continue
		}

		for _, query := range m.postgres {
			if _, err := tx.Exec(query); err != nil {
				errStr := strings.ToLower(err.Error())
				if strings.Contains(errStr, "duplicate column") || strings.Contains(errStr, "already exists") {
					continue
				}
				return fmt.Errorf("migration version %d failed: query %q failed: %w", m.version, query, err)
			}
		}

		insertQuery := s.rebind("INSERT INTO schema_migrations (version) VALUES (?)")
		if _, err := tx.Exec(insertQuery, m.version); err != nil {
			return fmt.Errorf("failed to update schema_migrations version: %w", err)
		}

		logger.Debugf("Applied schema migration version %d", m.version)
		currentVersion = m.version
	}

	if err := tx.Commit(); err != nil {
		return err
	}
	logSchemaMigrated(startVersion, currentVersion)

	return nil
}

func (s *SQLStore) rebind(query string) string {
	if !s.isPostgres() {
		return query
	}
	var result strings.Builder
	paramIndex := 1
	for _, char := range query {
		if char == '?' {
			fmt.Fprintf(&result, "$%d", paramIndex)
			paramIndex++
		} else {
			result.WriteRune(char)
		}
	}
	return result.String()
}

// GetCurrentKey implements Store.
func (s *SQLStore) GetCurrentKey(ctx context.Context) (ed25519.PrivateKey, ed25519.PublicKey, error) {
	query := s.rebind(`SELECT private_key, public_key FROM keyring WHERE expiration IS NULL ORDER BY id DESC LIMIT 1`)
	var privBytes, pubBytes []byte
	err := s.db.QueryRowContext(ctx, query).Scan(&privBytes, &pubBytes)
	if err == sql.ErrNoRows {
		return nil, nil, ErrNotFound
	}
	if err != nil {
		return nil, nil, err
	}
	return ed25519.PrivateKey(privBytes), ed25519.PublicKey(pubBytes), nil
}

// GetAllValidKeys implements Store.
func (s *SQLStore) GetAllValidKeys(ctx context.Context) ([]KeyPair, error) {
	query := s.rebind(`SELECT private_key, public_key, expiration FROM keyring WHERE expiration IS NULL OR expiration > ?`)
	rows, err := s.db.QueryContext(ctx, query, time.Now().UnixMilli())
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var keys []KeyPair
	for rows.Next() {
		var priv, pub []byte
		var exp sql.NullInt64
		if err := rows.Scan(&priv, &pub, &exp); err != nil {
			return nil, err
		}
		var expiration time.Time
		if exp.Valid {
			expiration = time.UnixMilli(exp.Int64)
		}
		privCopy := make([]byte, len(priv))
		copy(privCopy, priv)
		pubCopy := make([]byte, len(pub))
		copy(pubCopy, pub)
		keys = append(keys, KeyPair{
			Private:    ed25519.PrivateKey(privCopy),
			Public:     ed25519.PublicKey(pubCopy),
			Expiration: expiration,
		})
	}
	return keys, rows.Err()
}

// GetAllValidPublicKeys implements Store.
func (s *SQLStore) GetAllValidPublicKeys(ctx context.Context) ([]ed25519.PublicKey, error) {
	query := s.rebind(`SELECT public_key FROM keyring WHERE expiration IS NULL OR expiration > ?`)
	rows, err := s.db.QueryContext(ctx, query, time.Now().UnixMilli())
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var keys []ed25519.PublicKey
	for rows.Next() {
		var pub []byte
		if err := rows.Scan(&pub); err != nil {
			return nil, err
		}
		pubCopy := make([]byte, len(pub))
		copy(pubCopy, pub)
		keys = append(keys, ed25519.PublicKey(pubCopy))
	}
	return keys, rows.Err()
}

// ClaimKeyRotation implements Store.
func (s *SQLStore) ClaimKeyRotation(ctx context.Context, now time.Time, interval time.Duration) (bool, error) {
	query := s.rebind(`UPDATE rotation_lock SET next_rotation_at = ? WHERE id = 1 AND next_rotation_at <= ?`)
	res, err := s.db.ExecContext(ctx, query, now.Add(interval).UnixMilli(), now.UnixMilli())
	if err != nil {
		return false, err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return false, err
	}
	return n == 1, nil
}

// ReleaseKeyRotationClaim implements Store. It only resets the deadline if
// it still holds the exact value this claim set, so it can't clobber a
// newer claim.
func (s *SQLStore) ReleaseKeyRotationClaim(ctx context.Context, now time.Time, interval time.Duration) error {
	query := s.rebind(`UPDATE rotation_lock SET next_rotation_at = ? WHERE id = 1 AND next_rotation_at = ?`)
	_, err := s.db.ExecContext(ctx, query, now.UnixMilli(), now.Add(interval).UnixMilli())
	return err
}

// ScheduleKeyRotationIfUnset implements Store.
func (s *SQLStore) ScheduleKeyRotationIfUnset(ctx context.Context, at time.Time) (bool, error) {
	res, err := s.db.ExecContext(ctx, s.rebind(`UPDATE rotation_lock SET next_rotation_at = ? WHERE id = 1 AND next_rotation_at = 0`), at.UnixMilli())
	if err != nil {
		return false, err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return false, err
	}
	return n == 1, nil
}

// NextKeyRotation implements Store.
func (s *SQLStore) NextKeyRotation(ctx context.Context) (time.Time, error) {
	var ms int64
	if err := s.db.QueryRowContext(ctx, `SELECT next_rotation_at FROM rotation_lock WHERE id = 1`).Scan(&ms); err != nil {
		return time.Time{}, err
	}
	if ms == 0 {
		return time.Time{}, nil
	}
	return time.UnixMilli(ms), nil
}

// RotateKeys implements Store.
func (s *SQLStore) RotateKeys(ctx context.Context, newPriv ed25519.PrivateKey, newPub ed25519.PublicKey, gracePeriod time.Duration) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	now := time.Now()
	expireTime := now.Add(gracePeriod)

	// Set expiration on the current active key
	updateQuery := s.rebind(`UPDATE keyring SET expiration = ? WHERE expiration IS NULL`)
	if _, err := tx.ExecContext(ctx, updateQuery, expireTime.UnixMilli()); err != nil {
		return err
	}

	// Insert the new key
	insertQuery := s.rebind(`INSERT INTO keyring (private_key, public_key, created_at) VALUES (?, ?, ?)`)
	if _, err := tx.ExecContext(ctx, insertQuery, []byte(newPriv), []byte(newPub), now.UnixMilli()); err != nil {
		return err
	}

	// Clean up expired keys
	deleteQuery := s.rebind(`DELETE FROM keyring WHERE expiration <= ?`)
	if _, err := tx.ExecContext(ctx, deleteQuery, now.UnixMilli()); err != nil {
		return err
	}

	return tx.Commit()
}

// operatorKeyNoExpiry marks a key the operator lists for verification: valid
// until the operator drops it, at which point SetKeyring gives it a grace
// period. Far enough that no clock reaches it, finite so that the column
// stays "not current".
var operatorKeyNoExpiry = time.Date(9999, 12, 31, 0, 0, 0, 0, time.UTC)

// SetKeyring implements Store.
func (s *SQLStore) SetKeyring(ctx context.Context, current KeyPair, verifying []KeyPair, gracePeriod time.Duration) error {
	if len(current.Public) == 0 {
		return errors.New("SetKeyring: no current key")
	}
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()
	now := time.Now()

	if _, err := tx.ExecContext(ctx, s.rebind(`DELETE FROM keyring WHERE expiration IS NOT NULL AND expiration <= ?`), now.UnixMilli()); err != nil {
		return err
	}
	upsert := s.rebind(`INSERT INTO keyring (private_key, public_key, expiration, created_at) VALUES (?, ?, ?, ?)
		ON CONFLICT (public_key) DO UPDATE SET private_key = excluded.private_key, expiration = excluded.expiration`)
	for _, k := range verifying {
		if _, err := tx.ExecContext(ctx, upsert, []byte(k.Private), []byte(k.Public), operatorKeyNoExpiry.UnixMilli(), now.UnixMilli()); err != nil {
			return err
		}
	}
	if _, err := tx.ExecContext(ctx, upsert, []byte(current.Private), []byte(current.Public), nil, now.UnixMilli()); err != nil {
		return err
	}

	// Every other key that is still valid: the one that was current before,
	// or one the operator dropped from the file; both get the grace period
	// unless they are already expiring sooner.
	listed := make([]any, 0, len(verifying)+1)
	placeholders := make([]string, 0, len(verifying)+1)
	for _, k := range verifying {
		listed = append(listed, []byte(k.Public))
		placeholders = append(placeholders, "?")
	}
	listed = append(listed, []byte(current.Public))
	placeholders = append(placeholders, "?")
	grace := now.Add(gracePeriod).UnixMilli()
	args := append([]any{grace}, listed...)
	args = append(args, grace)
	retire := s.rebind(`UPDATE keyring SET expiration = ? WHERE public_key NOT IN (` + strings.Join(placeholders, ", ") + `) AND (expiration IS NULL OR expiration > ?)`)
	if _, err := tx.ExecContext(ctx, retire, args...); err != nil {
		return err
	}
	return tx.Commit()
}

// SaveInitialKey implements Store. It serializes on rotation_lock and only
// inserts when no active (unexpired) key exists, so concurrent control-plane
// replicas starting against an empty store converge on one initial key.
func (s *SQLStore) SaveInitialKey(ctx context.Context, priv ed25519.PrivateKey, pub ed25519.PublicKey) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	if _, err := tx.ExecContext(ctx, `UPDATE rotation_lock SET next_rotation_at = next_rotation_at WHERE id = 1`); err != nil {
		return err
	}
	query := s.rebind(`INSERT INTO keyring (private_key, public_key, created_at) SELECT ?, ?, ? WHERE NOT EXISTS (SELECT 1 FROM keyring WHERE expiration IS NULL)`)
	if _, err := tx.ExecContext(ctx, query, []byte(priv), []byte(pub), time.Now().UnixMilli()); err != nil {
		return err
	}
	return tx.Commit()
}

// EnrollNode implements Store.
func (s *SQLStore) EnrollNode(ctx context.Context, node *EnrolledNode) error {
	labelsJSON, err := json.Marshal(node.Labels)
	if err != nil {
		return fmt.Errorf("failed to marshal labels: %w", err)
	}
	if node.EnrolledAt.IsZero() {
		node.EnrolledAt = time.Now()
	}

	var query string
	if s.isPostgres() {
		query = s.rebind(`
			INSERT INTO nodes (peer_id, public_key, biscuit_token, role, enrollment_type, claims_json, owner_id, labels_json, enrolled_at, expires_at, autonomous_recovery, ephemeral, banned) 
			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, FALSE)
			ON CONFLICT (peer_id) 
			DO UPDATE SET public_key = EXCLUDED.public_key, biscuit_token = EXCLUDED.biscuit_token, role = EXCLUDED.role, enrollment_type = EXCLUDED.enrollment_type, claims_json = EXCLUDED.claims_json, owner_id = EXCLUDED.owner_id, labels_json = EXCLUDED.labels_json, enrolled_at = EXCLUDED.enrolled_at, expires_at = EXCLUDED.expires_at, autonomous_recovery = EXCLUDED.autonomous_recovery, ephemeral = EXCLUDED.ephemeral`)
	} else {
		query = s.rebind(`
			INSERT INTO nodes (peer_id, public_key, biscuit_token, role, enrollment_type, claims_json, owner_id, labels_json, enrolled_at, expires_at, autonomous_recovery, ephemeral, banned) 
			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0)
			ON CONFLICT (peer_id) 
			DO UPDATE SET public_key = excluded.public_key, biscuit_token = excluded.biscuit_token, role = excluded.role, enrollment_type = excluded.enrollment_type, claims_json = excluded.claims_json, owner_id = excluded.owner_id, labels_json = excluded.labels_json, enrolled_at = excluded.enrolled_at, expires_at = excluded.expires_at, autonomous_recovery = excluded.autonomous_recovery, ephemeral = excluded.ephemeral`)
	}

	ownerIDNull := sql.NullString{String: node.OwnerID, Valid: node.OwnerID != ""}
	_, err = s.db.ExecContext(ctx, query,
		node.PeerID,
		node.PublicKey,
		node.Biscuit,
		node.Role,
		node.EnrollmentType,
		node.ClaimsJSON,
		ownerIDNull,
		string(labelsJSON),
		node.EnrolledAt.UnixMilli(),
		node.ExpiresAt.UnixMilli(),
		node.AutonomousRecovery,
		node.Ephemeral,
	)
	return err
}

// GetNode implements Store.
func (s *SQLStore) GetNode(ctx context.Context, peerID string) (*EnrolledNode, error) {
	query := s.rebind(`SELECT peer_id, public_key, biscuit_token, role, enrollment_type, claims_json, owner_id, labels_json, enrolled_at, expires_at, banned, autonomous_recovery, ephemeral FROM nodes WHERE peer_id = ?`)
	var node EnrolledNode
	var claimsJSON, ownerID, labelsJSON sql.NullString
	var enrolledAtUnix, expiresAtUnix int64
	err := s.db.QueryRowContext(ctx, query, peerID).Scan(
		&node.PeerID,
		&node.PublicKey,
		&node.Biscuit,
		&node.Role,
		&node.EnrollmentType,
		&claimsJSON,
		&ownerID,
		&labelsJSON,
		&enrolledAtUnix,
		&expiresAtUnix,
		&node.Banned,
		&node.AutonomousRecovery,
		&node.Ephemeral,
	)
	if claimsJSON.Valid {
		node.ClaimsJSON = claimsJSON.String
	}
	if ownerID.Valid {
		node.OwnerID = ownerID.String
	}
	if err == sql.ErrNoRows {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, err
	}
	if labelsJSON.Valid && labelsJSON.String != "" {
		if err := json.Unmarshal([]byte(labelsJSON.String), &node.Labels); err != nil {
			return nil, fmt.Errorf("failed to unmarshal labels: %w", err)
		}
	}
	node.EnrolledAt = time.UnixMilli(enrolledAtUnix)
	node.ExpiresAt = time.UnixMilli(expiresAtUnix)
	return &node, nil
}

// SetNodeBanned implements Store.
func (s *SQLStore) SetNodeBanned(ctx context.Context, peerID string, banned bool) error {
	query := s.rebind(`UPDATE nodes SET banned = ? WHERE peer_id = ?`)
	res, err := s.db.ExecContext(ctx, query, banned, peerID)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

// SetNodeAutonomousRecovery implements Store.
func (s *SQLStore) SetNodeAutonomousRecovery(ctx context.Context, peerID string, enabled bool) error {
	query := s.rebind(`UPDATE nodes SET autonomous_recovery = ? WHERE peer_id = ?`)
	res, err := s.db.ExecContext(ctx, query, enabled, peerID)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}

// IsNodeBanned implements Store.
func (s *SQLStore) IsNodeBanned(ctx context.Context, peerID string) (bool, error) {
	query := s.rebind(`SELECT banned FROM nodes WHERE peer_id = ?`)
	var banned bool
	err := s.db.QueryRowContext(ctx, query, peerID).Scan(&banned)
	if err == sql.ErrNoRows {
		return false, nil // Not found nodes are not banned by default
	}
	if err != nil {
		return false, err
	}
	return banned, nil
}

// SetIdentityBanned implements Store.
func (s *SQLStore) SetIdentityBanned(ctx context.Context, identity string, banned bool) error {
	if identity == "" {
		return fmt.Errorf("identity cannot be empty")
	}
	if banned {
		query := s.rebind(`INSERT INTO banned_identities (identity, banned_at) VALUES (?, ?) ON CONFLICT (identity) DO NOTHING`)
		_, err := s.db.ExecContext(ctx, query, identity, time.Now().UnixMilli())
		return err
	}
	query := s.rebind(`DELETE FROM banned_identities WHERE identity = ?`)
	_, err := s.db.ExecContext(ctx, query, identity)
	return err
}

// SetNodeAndIdentityBanned implements Store.
func (s *SQLStore) SetNodeAndIdentityBanned(ctx context.Context, peerID, identity string, banned bool) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	res, err := tx.ExecContext(ctx, s.rebind(`UPDATE nodes SET banned = ? WHERE peer_id = ?`), banned, peerID)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return ErrNotFound
	}
	if identity != "" {
		if banned {
			_, err = tx.ExecContext(ctx, s.rebind(`INSERT INTO banned_identities (identity, banned_at) VALUES (?, ?) ON CONFLICT (identity) DO NOTHING`), identity, time.Now().UnixMilli())
		} else {
			_, err = tx.ExecContext(ctx, s.rebind(`DELETE FROM banned_identities WHERE identity = ?`), identity)
		}
		if err != nil {
			return err
		}
	}
	return tx.Commit()
}

// IsIdentityBanned implements Store.
func (s *SQLStore) IsIdentityBanned(ctx context.Context, identity string) (bool, error) {
	query := s.rebind(`SELECT 1 FROM banned_identities WHERE identity = ?`)
	var one int
	err := s.db.QueryRowContext(ctx, query, identity).Scan(&one)
	if err == sql.ErrNoRows {
		return false, nil
	}
	if err != nil {
		return false, err
	}
	return true, nil
}

// DeleteExpiredNodes implements Store.
func (s *SQLStore) DeleteExpiredNodes(ctx context.Context, before time.Time) (int64, error) {
	expired, args, err := nodeStateWhere(NodeExpired, before)
	if err != nil {
		return 0, err
	}
	res, err := s.db.ExecContext(ctx, s.rebind(`DELETE FROM nodes WHERE `+expired), args...)
	if err != nil {
		return 0, err
	}
	return res.RowsAffected()
}

// DeleteSilentBootstrapNodes implements Store. Two statements, one per
// retention, each leading with banned and expires_at so the
// (banned, expires_at) index narrows them to the unbounded rows.
func (s *SQLStore) DeleteSilentBootstrapNodes(ctx context.Context, before, ephemeralBefore time.Time) (int64, int64, error) {
	query := s.rebind(`DELETE FROM nodes WHERE banned = ? AND expires_at <= 0 AND ephemeral = ? AND enrolled_at < ?`)
	res, err := s.db.ExecContext(ctx, query, false, false, before.UnixMilli())
	if err != nil {
		return 0, 0, err
	}
	regular, err := res.RowsAffected()
	if err != nil {
		return 0, 0, err
	}
	res, err = s.db.ExecContext(ctx, query, false, true, ephemeralBefore.UnixMilli())
	if err != nil {
		return regular, 0, err
	}
	ephemeral, err := res.RowsAffected()
	return regular, ephemeral, err
}

// DeleteStaleEnrollmentRequests implements Store.
func (s *SQLStore) DeleteStaleEnrollmentRequests(ctx context.Context, before, ephemeralBefore time.Time) (int64, error) {
	// The OR form, rather than COALESCE over the two columns, lets an index
	// on either column serve the predicate.
	res, err := s.db.ExecContext(ctx, s.rebind(`DELETE FROM enrollment_requests WHERE resolved_at < ? OR (resolved_at IS NULL AND created_at < ?)`), before.UnixMilli(), before.UnixMilli())
	if err != nil {
		return 0, err
	}
	regular, err := res.RowsAffected()
	if err != nil {
		return 0, err
	}
	res, err = s.db.ExecContext(ctx, s.rebind(`DELETE FROM enrollment_requests WHERE (resolved_at < ? OR (resolved_at IS NULL AND created_at < ?)) AND token_id IN (SELECT id FROM bootstrap_tokens WHERE ephemeral = ?)`), ephemeralBefore.UnixMilli(), ephemeralBefore.UnixMilli(), true)
	if err != nil {
		return regular, err
	}
	ephemeral, err := res.RowsAffected()
	return regular + ephemeral, err
}

// DeleteExpiredBootstrapTokens implements Store. The NOT EXISTS keeps the
// foreign key from enrollment_requests.token_id satisfied: a token goes once
// its requests have.
func (s *SQLStore) DeleteExpiredBootstrapTokens(ctx context.Context, before time.Time) (int64, error) {
	res, err := s.db.ExecContext(ctx, s.rebind(`DELETE FROM bootstrap_tokens WHERE expires_at < ? AND NOT EXISTS (SELECT 1 FROM enrollment_requests r WHERE r.token_id = bootstrap_tokens.id)`), before.UnixMilli())
	if err != nil {
		return 0, err
	}
	return res.RowsAffected()
}

// DeleteExpiredRouterLeases implements Store.
func (s *SQLStore) DeleteExpiredRouterLeases(ctx context.Context, before time.Time) (int64, error) {
	res, err := s.db.ExecContext(ctx, s.rebind(`DELETE FROM routers WHERE expires_at < ?`), before.UnixMilli())
	if err != nil {
		return 0, err
	}
	return res.RowsAffected()
}

// countedTables are the tables whose row counts the control plane reports;
// every one of them grows with mesh activity and has a reaper or a bound.
var countedTables = []string{"nodes", "enrollment_requests", "bootstrap_tokens", "routers", "revoked_biscuits", "banned_identities", "users", "keyring"}

// CountRows implements Store.
func (s *SQLStore) CountRows(ctx context.Context) (map[string]int64, error) {
	counts := make(map[string]int64, len(countedTables))
	for _, table := range countedTables {
		var n int64
		if err := s.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM `+table).Scan(&n); err != nil {
			return nil, fmt.Errorf("count %s: %w", table, err)
		}
		counts[table] = n
	}
	return counts, nil
}

// ListBannedPeerIDs implements Store.
func (s *SQLStore) ListBannedPeerIDs(ctx context.Context) ([]string, error) {
	query := s.rebind(`SELECT peer_id FROM nodes WHERE banned = ?`)
	rows, err := s.db.QueryContext(ctx, query, true)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var peerIDs []string
	for rows.Next() {
		var peerID string
		if err := rows.Scan(&peerID); err != nil {
			return nil, err
		}
		peerIDs = append(peerIDs, peerID)
	}
	return peerIDs, rows.Err()
}

// UpsertRouterLease implements Store.
func (s *SQLStore) UpsertRouterLease(ctx context.Context, lease *RouterLease) error {
	addrsBytes, err := json.Marshal(lease.Addresses)
	if err != nil {
		return err
	}
	peersBytes, err := json.Marshal(lease.ConnectedPeers)
	if err != nil {
		return err
	}

	const columns = `peer_id, multiaddresses, last_lease_renewal, expires_at, connected_peers, dht_size, connections, connection_limit, ingress_url`
	var query string
	if s.isPostgres() {
		query = s.rebind(`
			INSERT INTO routers (` + columns + `)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
			ON CONFLICT (peer_id)
			DO UPDATE SET multiaddresses = EXCLUDED.multiaddresses, last_lease_renewal = EXCLUDED.last_lease_renewal, expires_at = EXCLUDED.expires_at, connected_peers = EXCLUDED.connected_peers, dht_size = EXCLUDED.dht_size, connections = EXCLUDED.connections, connection_limit = EXCLUDED.connection_limit, ingress_url = EXCLUDED.ingress_url`)
	} else {
		query = s.rebind(`
			INSERT INTO routers (` + columns + `)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
			ON CONFLICT (peer_id)
			DO UPDATE SET multiaddresses = excluded.multiaddresses, last_lease_renewal = excluded.last_lease_renewal, expires_at = excluded.expires_at, connected_peers = excluded.connected_peers, dht_size = excluded.dht_size, connections = excluded.connections, connection_limit = excluded.connection_limit, ingress_url = excluded.ingress_url`)
	}

	_, err = s.db.ExecContext(ctx, query, lease.PeerID, string(addrsBytes), lease.LastRenewal.UnixMilli(), lease.ExpiresAt.UnixMilli(), string(peersBytes), lease.DHTSize, lease.Connections, lease.ConnectionLimit, lease.IngressURL)
	return err
}

// GetActiveRouters implements Store. The order is by peer ID, so the store
// reads the same to every replica and caller; the control plane shuffles the
// list it hands members (routerAddresses) so they spread across the routers.
func (s *SQLStore) GetActiveRouters(ctx context.Context) ([]RouterLease, error) {
	query := s.rebind(`SELECT peer_id, multiaddresses, last_lease_renewal, expires_at, connected_peers, dht_size, connections, connection_limit, ingress_url FROM routers WHERE expires_at > ? ORDER BY peer_id`)
	rows, err := s.db.QueryContext(ctx, query, time.Now().UnixMilli())
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var leases []RouterLease
	for rows.Next() {
		var l RouterLease
		var addrsStr string
		var peersStr, ingressURL sql.NullString
		var dhtSize, connections, connectionLimit sql.NullInt64
		var lastRenewalUnix, expiresAtUnix int64
		if err := rows.Scan(&l.PeerID, &addrsStr, &lastRenewalUnix, &expiresAtUnix, &peersStr, &dhtSize, &connections, &connectionLimit, &ingressURL); err != nil {
			return nil, err
		}
		if err := json.Unmarshal([]byte(addrsStr), &l.Addresses); err != nil {
			return nil, err
		}
		if peersStr.Valid && peersStr.String != "" {
			if err := json.Unmarshal([]byte(peersStr.String), &l.ConnectedPeers); err != nil {
				return nil, err
			}
		}
		if dhtSize.Valid {
			l.DHTSize = int(dhtSize.Int64)
		}
		if connections.Valid {
			l.Connections = int(connections.Int64)
		}
		if connectionLimit.Valid {
			l.ConnectionLimit = int(connectionLimit.Int64)
		}
		if ingressURL.Valid {
			l.IngressURL = ingressURL.String
		}
		l.LastRenewal = time.UnixMilli(lastRenewalUnix)
		l.ExpiresAt = time.UnixMilli(expiresAtUnix)
		leases = append(leases, l)
	}
	return leases, rows.Err()
}

// SaveMeshPolicy replaces the entire mesh policy with the provided roles and bindings.
func (s *SQLStore) SaveMeshPolicy(ctx context.Context, roles []*api.PolicyRole, bindings []*api.PolicyBinding) error {
	return s.inTx(ctx, func(tx *sql.Tx) error {
		return s.saveMeshPolicyTx(ctx, tx, roles, bindings)
	})
}

// SavePolicyDocument replaces roles, bindings and egress destinations in one
// transaction, so a POST /policies is applied whole or not at all.
func (s *SQLStore) SavePolicyDocument(ctx context.Context, roles []*api.PolicyRole, bindings []*api.PolicyBinding, egress []*api.EgressDestination) error {
	return s.inTx(ctx, func(tx *sql.Tx) error {
		if err := s.saveMeshPolicyTx(ctx, tx, roles, bindings); err != nil {
			return err
		}
		return s.saveEgressDestinationsTx(ctx, tx, egress)
	})
}

// inTx runs fn in a transaction and commits when it returns nil.
func (s *SQLStore) inTx(ctx context.Context, fn func(tx *sql.Tx) error) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()
	if err := fn(tx); err != nil {
		return err
	}
	return tx.Commit()
}

func (s *SQLStore) saveMeshPolicyTx(ctx context.Context, tx *sql.Tx, roles []*api.PolicyRole, bindings []*api.PolicyBinding) error {
	// For simplicity in replacing policy, we clear all and insert new.
	if _, err := tx.ExecContext(ctx, "DELETE FROM role_permissions"); err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx, "DELETE FROM role_bindings"); err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx, "DELETE FROM roles"); err != nil {
		return err
	}

	for _, r := range roles {
		if r == nil {
			continue
		}
		if _, err := tx.ExecContext(ctx, s.rebind("INSERT INTO roles (name, description, created_at) VALUES (?, '', ?)"), r.Name, time.Now().UnixMilli()); err != nil {
			return err
		}
		for _, target := range r.AllowedTargets {
			if _, err := tx.ExecContext(ctx, s.rebind("INSERT INTO role_permissions (role_name, resource_type, resource_value) VALUES (?, 'target', ?)"), r.Name, target); err != nil {
				return err
			}
		}
		for _, svc := range r.AllowedServices {
			if _, err := tx.ExecContext(ctx, s.rebind("INSERT INTO role_permissions (role_name, resource_type, resource_value) VALUES (?, 'service', ?)"), r.Name, svc); err != nil {
				return err
			}
		}
		for _, dl := range r.CustomDatalog {
			if _, err := tx.ExecContext(ctx, s.rebind("INSERT INTO role_permissions (role_name, resource_type, resource_value) VALUES (?, 'custom_datalog', ?)"), r.Name, dl); err != nil {
				return err
			}
		}
		for _, label := range r.AllowedLabels {
			if _, err := tx.ExecContext(ctx, s.rebind("INSERT INTO role_permissions (role_name, resource_type, resource_value) VALUES (?, 'label', ?)"), r.Name, label); err != nil {
				return err
			}
		}
		for _, svc := range r.AllowedPublish {
			if _, err := tx.ExecContext(ctx, s.rebind("INSERT INTO role_permissions (role_name, resource_type, resource_value) VALUES (?, 'publish', ?)"), r.Name, svc); err != nil {
				return err
			}
		}
		// One row per narrowed entry, as protojson: the shape is the proto's
		// and a new HTTPGrant field needs no schema change here.
		for _, g := range r.Http {
			if g == nil {
				continue
			}
			encoded, err := protojson.MarshalOptions{UseProtoNames: true}.Marshal(g)
			if err != nil {
				return err
			}
			if _, err := tx.ExecContext(ctx, s.rebind("INSERT INTO role_permissions (role_name, resource_type, resource_value) VALUES (?, 'http', ?)"), r.Name, string(encoded)); err != nil {
				return err
			}
		}
		for _, g := range r.AllowedIngress {
			if g == nil {
				continue
			}
			encoded, err := protojson.MarshalOptions{UseProtoNames: true}.Marshal(g)
			if err != nil {
				return err
			}
			if _, err := tx.ExecContext(ctx, s.rebind("INSERT INTO role_permissions (role_name, resource_type, resource_value) VALUES (?, 'ingress', ?)"), r.Name, string(encoded)); err != nil {
				return err
			}
		}
	}

	for _, b := range bindings {
		if b == nil {
			continue
		}
		for _, member := range b.Members {
			if _, err := tx.ExecContext(ctx, s.rebind("INSERT INTO role_bindings (role_name, member) VALUES (?, ?)"), b.Role, member); err != nil {
				return err
			}
		}
	}

	return nil
}

// GetMeshPolicy retrieves the entire mesh policy as structured data, in an
// order that does not change between reads: the policy is rendered to text
// a member compares with the last text it fetched to tell a change.
func (s *SQLStore) GetMeshPolicy(ctx context.Context) ([]*api.PolicyRole, []*api.PolicyBinding, error) {
	rolesRows, err := s.db.QueryContext(ctx, s.rebind("SELECT name FROM roles ORDER BY name"))
	if err != nil {
		return nil, nil, err
	}
	defer func() { _ = rolesRows.Close() }()

	rolesMap := make(map[string]*api.PolicyRole)
	var roles []*api.PolicyRole

	for rolesRows.Next() {
		var name string
		if err := rolesRows.Scan(&name); err != nil {
			return nil, nil, err
		}
		role := &api.PolicyRole{Name: name}
		rolesMap[name] = role
		roles = append(roles, role)
	}
	if err := rolesRows.Err(); err != nil {
		return nil, nil, err
	}

	permsRows, err := s.db.QueryContext(ctx, s.rebind("SELECT role_name, resource_type, resource_value FROM role_permissions ORDER BY role_name, resource_type, resource_value"))
	if err != nil {
		return nil, nil, err
	}
	defer func() { _ = permsRows.Close() }()

	for permsRows.Next() {
		var roleName, resType, resValue string
		if err := permsRows.Scan(&roleName, &resType, &resValue); err != nil {
			return nil, nil, err
		}
		if r, ok := rolesMap[roleName]; ok {
			switch resType {
			case "target":
				r.AllowedTargets = append(r.AllowedTargets, resValue)
			case "service":
				r.AllowedServices = append(r.AllowedServices, resValue)
			case "custom_datalog":
				r.CustomDatalog = append(r.CustomDatalog, resValue)
			case "label":
				r.AllowedLabels = append(r.AllowedLabels, resValue)
			case "publish":
				r.AllowedPublish = append(r.AllowedPublish, resValue)
			case "http":
				g := &api.HTTPGrant{}
				if err := protojson.Unmarshal([]byte(resValue), g); err != nil {
					return nil, nil, fmt.Errorf("role %s: stored http grant does not parse: %w", roleName, err)
				}
				r.Http = append(r.Http, g)
			case "ingress":
				g := &api.IngressGrant{}
				if err := protojson.Unmarshal([]byte(resValue), g); err != nil {
					return nil, nil, fmt.Errorf("role %s: stored ingress grant does not parse: %w", roleName, err)
				}
				r.AllowedIngress = append(r.AllowedIngress, g)
			}
		}
	}
	if err := permsRows.Err(); err != nil {
		return nil, nil, err
	}

	bindingsRows, err := s.db.QueryContext(ctx, s.rebind("SELECT role_name, member FROM role_bindings ORDER BY role_name, member"))
	if err != nil {
		return nil, nil, err
	}
	defer func() { _ = bindingsRows.Close() }()

	var bindings []*api.PolicyBinding
	for bindingsRows.Next() {
		var roleName, member string
		if err := bindingsRows.Scan(&roleName, &member); err != nil {
			return nil, nil, err
		}
		if n := len(bindings); n > 0 && bindings[n-1].Role == roleName {
			bindings[n-1].Members = append(bindings[n-1].Members, member)
			continue
		}
		bindings = append(bindings, &api.PolicyBinding{Role: roleName, Members: []string{member}})
	}
	if err := bindingsRows.Err(); err != nil {
		return nil, nil, err
	}

	return roles, bindings, nil
}

// SaveEgressDestinations replaces the egress section of the mesh policy.
func (s *SQLStore) SaveEgressDestinations(ctx context.Context, egress []*api.EgressDestination) error {
	return s.inTx(ctx, func(tx *sql.Tx) error {
		return s.saveEgressDestinationsTx(ctx, tx, egress)
	})
}

func (s *SQLStore) saveEgressDestinationsTx(ctx context.Context, tx *sql.Tx, egress []*api.EgressDestination) error {
	if _, err := tx.ExecContext(ctx, "DELETE FROM egress_destinations"); err != nil {
		return err
	}
	marshaler := protojson.MarshalOptions{UseProtoNames: true}
	for _, d := range egress {
		if d == nil {
			continue
		}
		servedBy, err := json.Marshal(d.GetServedBy())
		if err != nil {
			return err
		}
		cfgBytes, err := marshaler.Marshal(d)
		if err != nil {
			return err
		}
		if _, err := tx.ExecContext(ctx, s.rebind("INSERT INTO egress_destinations (name, target_url, credential, served_by, config_json) VALUES (?, ?, ?, ?, ?)"),
			d.GetName(), d.GetTargetUrl(), d.GetCredential(), string(servedBy), string(cfgBytes)); err != nil {
			return err
		}
	}
	return nil
}

// GetEgressDestinations loads the egress section of the mesh policy, in name
// order so the rendered document and rules are stable.
func (s *SQLStore) GetEgressDestinations(ctx context.Context) ([]*api.EgressDestination, error) {
	rows, err := s.db.QueryContext(ctx, s.rebind("SELECT name, target_url, credential, served_by, config_json FROM egress_destinations ORDER BY name"))
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	unmarshaler := protojson.UnmarshalOptions{DiscardUnknown: true}
	var egress []*api.EgressDestination
	for rows.Next() {
		var name, targetURL, credential, servedByJSON, configJSON string
		if err := rows.Scan(&name, &targetURL, &credential, &servedByJSON, &configJSON); err != nil {
			return nil, err
		}
		if strings.TrimSpace(configJSON) != "" {
			var dest api.EgressDestination
			if err := unmarshaler.Unmarshal([]byte(configJSON), &dest); err != nil {
				return nil, fmt.Errorf("egress %s: stored config_json does not parse: %w", name, err)
			}
			egress = append(egress, &dest)
			continue
		}
		var servedBy []string
		if err := json.Unmarshal([]byte(servedByJSON), &servedBy); err != nil {
			return nil, fmt.Errorf("egress %s: stored served_by does not parse: %w", name, err)
		}
		egress = append(egress, &api.EgressDestination{Name: name, TargetUrl: targetURL, Credential: credential, ServedBy: servedBy})
	}
	return egress, rows.Err()
}

// SaveBootstrapToken persists a new bootstrap token.
func (s *SQLStore) SaveBootstrapToken(ctx context.Context, token *BootstrapToken) error {
	var query string
	if s.isPostgres() {
		query = s.rebind(`
			INSERT INTO bootstrap_tokens (id, token_hash, role, owner_id, max_usages, usages_count, description, created_at, expires_at, autonomous_recovery, ephemeral)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
			ON CONFLICT (id) DO NOTHING`)
	} else {
		query = s.rebind(`
			INSERT INTO bootstrap_tokens (id, token_hash, role, owner_id, max_usages, usages_count, description, created_at, expires_at, autonomous_recovery, ephemeral)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
			ON CONFLICT (id) DO NOTHING`)
	}
	ownerIDNull := sql.NullString{String: token.OwnerID, Valid: token.OwnerID != ""}
	_, err := s.db.ExecContext(ctx, query,
		token.ID,
		token.TokenHash,
		token.Role,
		ownerIDNull,
		token.MaxUsages,
		token.UsagesCount,
		token.Description,
		token.CreatedAt.UnixMilli(),
		token.ExpiresAt.UnixMilli(),
		token.AutonomousRecovery,
		token.Ephemeral,
	)
	return err
}

// GetBootstrapToken retrieves a bootstrap token by its ID (sha256 hash).
func (s *SQLStore) GetBootstrapToken(ctx context.Context, id string) (*BootstrapToken, error) {
	query := s.rebind(`SELECT id, token_hash, role, owner_id, max_usages, usages_count, description, created_at, expires_at, revoked_at, autonomous_recovery, ephemeral FROM bootstrap_tokens WHERE id = ?`)
	var t BootstrapToken
	var created, expires int64
	var ownerID sql.NullString
	var revokedAt sql.NullInt64
	err := s.db.QueryRowContext(ctx, query, id).Scan(
		&t.ID,
		&t.TokenHash,
		&t.Role,
		&ownerID,
		&t.MaxUsages,
		&t.UsagesCount,
		&t.Description,
		&created,
		&expires,
		&revokedAt,
		&t.AutonomousRecovery,
		&t.Ephemeral,
	)
	if err == sql.ErrNoRows {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, err
	}
	if ownerID.Valid {
		t.OwnerID = ownerID.String
	}
	t.CreatedAt = time.UnixMilli(created)
	t.ExpiresAt = time.UnixMilli(expires)
	if revokedAt.Valid {
		rt := time.UnixMilli(revokedAt.Int64)
		t.RevokedAt = &rt
	}
	return &t, nil
}

// ConsumeBootstrapTokenUsage implements Store. The validity conditions live
// in the WHERE clause so the check and the increment are one statement.
func (s *SQLStore) ConsumeBootstrapTokenUsage(ctx context.Context, id string, now time.Time) error {
	query := `UPDATE bootstrap_tokens SET usages_count = usages_count + 1
		WHERE id = ? AND usages_count < max_usages AND revoked_at IS NULL AND expires_at > ?`
	res, err := s.db.ExecContext(ctx, s.rebind(query), id, now.UnixMilli())
	if err != nil {
		return fmt.Errorf("failed to consume token usage: %w", err)
	}
	n, err := res.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to consume token usage: %w", err)
	}
	if n == 0 {
		return ErrBootstrapTokenUnusable
	}
	return nil
}

// RevokeBootstrapToken soft-revokes a token: see the Store interface comment
// for why this doesn't delete the row. The WHERE clause makes a repeat call
// a no-op rather than clobbering the original revocation time.
func (s *SQLStore) RevokeBootstrapToken(ctx context.Context, id string) error {
	query := `UPDATE bootstrap_tokens SET revoked_at = ? WHERE id = ? AND revoked_at IS NULL`
	_, err := s.db.ExecContext(ctx, s.rebind(query), time.Now().UnixMilli(), id)
	if err != nil {
		return fmt.Errorf("failed to revoke bootstrap token: %w", err)
	}
	return nil
}

// CreateEnrollmentRequest saves a new pending request.
func (s *SQLStore) CreateEnrollmentRequest(ctx context.Context, req *EnrollmentRequest) error {
	labelsJSON, err := json.Marshal(req.Labels)
	if err != nil {
		return fmt.Errorf("failed to marshal labels: %w", err)
	}
	query := `INSERT INTO enrollment_requests (id, peer_id, public_key, token_id, status, labels_json, biscuit_token, created_at, resolved_at, resolved_by)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
	var resAt sql.NullInt64
	if req.ResolvedAt != nil {
		resAt = sql.NullInt64{Int64: req.ResolvedAt.UnixMilli(), Valid: true}
	}
	_, err = s.db.ExecContext(ctx, s.rebind(query),
		req.ID,
		req.PeerID,
		req.PublicKey,
		req.TokenID,
		int(req.Status),
		string(labelsJSON),
		req.BiscuitToken,
		req.CreatedAt.UnixMilli(),
		resAt,
		req.ResolvedBy,
	)
	if err != nil {
		return fmt.Errorf("failed to create enrollment request: %w", err)
	}
	return nil
}

// GetEnrollmentRequest retrieves request by PeerID.
func (s *SQLStore) GetEnrollmentRequest(ctx context.Context, peerID string) (*EnrollmentRequest, error) {
	query := `SELECT id, peer_id, public_key, token_id, status, labels_json, biscuit_token, created_at, resolved_at, resolved_by 
		FROM enrollment_requests WHERE peer_id = ?`
	return s.scanEnrollmentRequest(s.db.QueryRowContext(ctx, s.rebind(query), peerID))
}

// GetEnrollmentRequestByID retrieves request by UUID.
func (s *SQLStore) GetEnrollmentRequestByID(ctx context.Context, id string) (*EnrollmentRequest, error) {
	query := `SELECT id, peer_id, public_key, token_id, status, labels_json, biscuit_token, created_at, resolved_at, resolved_by 
		FROM enrollment_requests WHERE id = ?`
	return s.scanEnrollmentRequest(s.db.QueryRowContext(ctx, s.rebind(query), id))
}

type scannable interface {
	Scan(dest ...any) error
}

func (s *SQLStore) scanEnrollmentRequest(row scannable) (*EnrollmentRequest, error) {
	var created int64
	var resAt sql.NullInt64
	var statusVal int
	var labelsJSON sql.NullString
	var req EnrollmentRequest

	err := row.Scan(
		&req.ID,
		&req.PeerID,
		&req.PublicKey,
		&req.TokenID,
		&statusVal,
		&labelsJSON,
		&req.BiscuitToken,
		&created,
		&resAt,
		&req.ResolvedBy,
	)
	if err == sql.ErrNoRows {
		return nil, ErrNotFound
	} else if err != nil {
		return nil, fmt.Errorf("failed to scan enrollment request: %w", err)
	}
	if labelsJSON.Valid && labelsJSON.String != "" {
		if err := json.Unmarshal([]byte(labelsJSON.String), &req.Labels); err != nil {
			return nil, fmt.Errorf("failed to unmarshal labels: %w", err)
		}
	}

	req.CreatedAt = time.UnixMilli(created)
	req.Status = api.EnrollmentStatus(statusVal)
	if resAt.Valid {
		t := time.UnixMilli(resAt.Int64)
		req.ResolvedAt = &t
	}

	pubCopy := make([]byte, len(req.PublicKey))
	copy(pubCopy, req.PublicKey)
	req.PublicKey = pubCopy

	biscuitCopy := make([]byte, len(req.BiscuitToken))
	copy(biscuitCopy, req.BiscuitToken)
	req.BiscuitToken = biscuitCopy

	return &req, nil
}

// ListEnrollmentRequests retrieves all requests.
func (s *SQLStore) ListEnrollmentRequests(ctx context.Context) ([]EnrollmentRequest, error) {
	query := `SELECT id, peer_id, public_key, token_id, status, labels_json, biscuit_token, created_at, resolved_at, resolved_by 
		FROM enrollment_requests ORDER BY created_at DESC`
	rows, err := s.db.QueryContext(ctx, s.rebind(query))
	if err != nil {
		return nil, fmt.Errorf("failed to query enrollment requests: %w", err)
	}
	defer func() { _ = rows.Close() }()

	var reqs []EnrollmentRequest
	for rows.Next() {
		req, err := s.scanEnrollmentRequest(rows)
		if err != nil {
			return nil, err
		}
		reqs = append(reqs, *req)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return reqs, nil
}

// UpdateEnrollmentRequest updates status, timestamp and biscuit token.
func (s *SQLStore) UpdateEnrollmentRequest(ctx context.Context, id string, status api.EnrollmentStatus, biscuit []byte, resolvedBy string) error {
	query := `UPDATE enrollment_requests SET status = ?, biscuit_token = ?, resolved_at = ?, resolved_by = ? WHERE id = ?`
	_, err := s.db.ExecContext(ctx, s.rebind(query),
		int(status),
		biscuit,
		time.Now().UnixMilli(),
		resolvedBy,
		id,
	)
	if err != nil {
		return fmt.Errorf("failed to update enrollment request: %w", err)
	}
	return nil
}

// ResolveEnrollmentRequest implements Store.
func (s *SQLStore) ResolveEnrollmentRequest(ctx context.Context, id string, status api.EnrollmentStatus, biscuit []byte, resolvedBy string) error {
	query := `UPDATE enrollment_requests SET status = ?, biscuit_token = ?, resolved_at = ?, resolved_by = ?
		WHERE id = ? AND status = ?`
	res, err := s.db.ExecContext(ctx, s.rebind(query),
		int(status),
		biscuit,
		time.Now().UnixMilli(),
		resolvedBy,
		id,
		int(api.EnrollmentStatus_ENROLLMENT_STATUS_PENDING),
	)
	if err != nil {
		return fmt.Errorf("failed to resolve enrollment request: %w", err)
	}
	n, err := res.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to resolve enrollment request: %w", err)
	}
	if n == 0 {
		return ErrEnrollmentAlreadyResolved
	}
	return nil
}

// ListNodes implements Store.
func (s *SQLStore) ListNodes(ctx context.Context, filter NodeFilter) ([]EnrolledNode, error) {
	where, args, err := nodeFilterWhere(filter)
	if err != nil {
		return nil, err
	}
	// Neither blob is read: a list is a view of the rows, and the biscuit is
	// a 2 KB column per row that only GetNode's single-row callers need.
	query := `SELECT peer_id, role, enrollment_type, claims_json, owner_id, labels_json, enrolled_at, expires_at, banned, autonomous_recovery, ephemeral FROM nodes` + where + ` ORDER BY enrolled_at DESC, peer_id DESC`
	if filter.Limit > 0 {
		query += ` LIMIT ?`
		args = append(args, filter.Limit)
	}
	rows, err := s.db.QueryContext(ctx, s.rebind(query), args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query nodes: %w", err)
	}
	defer func() { _ = rows.Close() }()

	var nodes []EnrolledNode
	for rows.Next() {
		var node EnrolledNode
		var claimsJSON, ownerID, labelsJSON sql.NullString
		var enrolledAtUnix, expiresAtUnix int64
		err := rows.Scan(
			&node.PeerID,
			&node.Role,
			&node.EnrollmentType,
			&claimsJSON,
			&ownerID,
			&labelsJSON,
			&enrolledAtUnix,
			&expiresAtUnix,
			&node.Banned,
			&node.AutonomousRecovery,
			&node.Ephemeral,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan enrolled node: %w", err)
		}
		if claimsJSON.Valid {
			node.ClaimsJSON = claimsJSON.String
		}
		if ownerID.Valid {
			node.OwnerID = ownerID.String
		}
		if labelsJSON.Valid && labelsJSON.String != "" {
			if err := json.Unmarshal([]byte(labelsJSON.String), &node.Labels); err != nil {
				return nil, fmt.Errorf("failed to unmarshal labels: %w", err)
			}
		}
		node.EnrolledAt = time.UnixMilli(enrolledAtUnix)
		node.ExpiresAt = time.UnixMilli(expiresAtUnix)
		nodes = append(nodes, node)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return nodes, nil
}

// nodeFilterWhere renders filter as a WHERE clause, empty for the zero filter.
func nodeFilterWhere(filter NodeFilter) (string, []any, error) {
	var conds []string
	var args []any
	if len(filter.States) > 0 {
		now := filter.Now
		if now.IsZero() {
			now = time.Now()
		}
		arms := make([]string, 0, len(filter.States))
		for _, state := range filter.States {
			arm, armArgs, err := nodeStateWhere(state, now)
			if err != nil {
				return "", nil, err
			}
			arms = append(arms, arm)
			args = append(args, armArgs...)
		}
		conds = append(conds, "("+strings.Join(arms, " OR ")+")")
	}
	if filter.OwnerID != nil {
		conds = append(conds, "owner_id = ?")
		args = append(args, *filter.OwnerID)
	}
	if filter.Role != "" {
		conds = append(conds, "role = ?")
		args = append(args, filter.Role)
	}
	if filter.After != nil {
		at := filter.After.EnrolledAt.UnixMilli()
		conds = append(conds, "enrolled_at <= ? AND (enrolled_at < ? OR peer_id < ?)")
		args = append(args, at, at, filter.After.PeerID)
	}
	if len(conds) == 0 {
		return "", nil, nil
	}
	return " WHERE " + strings.Join(conds, " AND "), args, nil
}

// nodeStateWhere is CheckAdmission in SQL. A zero ExpiresAt round-trips as a
// negative UnixMilli, so it never expires. Each arm leads with banned so it
// can search the (banned, expires_at) index.
func nodeStateWhere(state NodeState, now time.Time) (string, []any, error) {
	ms := now.UnixMilli()
	switch state {
	case NodeBanned:
		return "banned = ?", []any{true}, nil
	case NodeExpired:
		return "(banned = ? AND expires_at > 0 AND expires_at < ?)", []any{false, ms}, nil
	case NodeAdmitted:
		// Parenthesised like the other arms, so a caller may AND it with more.
		return "((banned = ? AND expires_at <= 0) OR (banned = ? AND expires_at >= ?))", []any{false, false, ms}, nil
	}
	return "", nil, fmt.Errorf("unknown node state %q", state)
}

// CountNodes implements Store. The CASE tests a ban first, as CheckAdmission does.
func (s *SQLStore) CountNodes(ctx context.Context, now time.Time) ([]NodeCount, error) {
	banned, args, err := nodeStateWhere(NodeBanned, now)
	if err != nil {
		return nil, err
	}
	expired, expiredArgs, err := nodeStateWhere(NodeExpired, now)
	if err != nil {
		return nil, err
	}
	query := fmt.Sprintf(`SELECT role, CASE WHEN %s THEN '%s' WHEN %s THEN '%s' ELSE '%s' END, COUNT(*) FROM nodes GROUP BY 1, 2`,
		banned, NodeBanned, expired, NodeExpired, NodeAdmitted)
	rows, err := s.db.QueryContext(ctx, s.rebind(query), append(args, expiredArgs...)...)
	if err != nil {
		return nil, fmt.Errorf("failed to count nodes: %w", err)
	}
	defer func() { _ = rows.Close() }()

	var counts []NodeCount
	for rows.Next() {
		var c NodeCount
		if err := rows.Scan(&c.Role, &c.State, &c.Count); err != nil {
			return nil, fmt.Errorf("failed to scan node count: %w", err)
		}
		counts = append(counts, c)
	}
	return counts, rows.Err()
}

// CountEnrollmentRequests implements Store.
func (s *SQLStore) CountEnrollmentRequests(ctx context.Context) (map[api.EnrollmentStatus]int64, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT status, COUNT(*) FROM enrollment_requests GROUP BY status`)
	if err != nil {
		return nil, fmt.Errorf("failed to count enrollment requests: %w", err)
	}
	defer func() { _ = rows.Close() }()

	counts := make(map[api.EnrollmentStatus]int64)
	for rows.Next() {
		var status int32
		var n int64
		if err := rows.Scan(&status, &n); err != nil {
			return nil, fmt.Errorf("failed to scan enrollment request count: %w", err)
		}
		counts[api.EnrollmentStatus(status)] = n
	}
	return counts, rows.Err()
}

// ListBootstrapTokens retrieves all bootstrap tokens.
func (s *SQLStore) ListBootstrapTokens(ctx context.Context) ([]BootstrapToken, error) {
	query := s.rebind(`SELECT id, token_hash, role, owner_id, max_usages, usages_count, description, created_at, expires_at, revoked_at, autonomous_recovery, ephemeral FROM bootstrap_tokens ORDER BY created_at DESC`)
	rows, err := s.db.QueryContext(ctx, query)
	if err != nil {
		return nil, fmt.Errorf("failed to query bootstrap tokens: %w", err)
	}
	defer func() { _ = rows.Close() }()

	var tokens []BootstrapToken
	for rows.Next() {
		var t BootstrapToken
		var desc sql.NullString
		var ownerID sql.NullString
		var created, expires int64
		var revokedAt sql.NullInt64
		err := rows.Scan(
			&t.ID,
			&t.TokenHash,
			&t.Role,
			&ownerID,
			&t.MaxUsages,
			&t.UsagesCount,
			&desc,
			&created,
			&expires,
			&revokedAt,
			&t.AutonomousRecovery,
			&t.Ephemeral,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan bootstrap token: %w", err)
		}
		if desc.Valid {
			t.Description = desc.String
		}
		if ownerID.Valid {
			t.OwnerID = ownerID.String
		}
		t.CreatedAt = time.UnixMilli(created)
		t.ExpiresAt = time.UnixMilli(expires)
		if revokedAt.Valid {
			rt := time.UnixMilli(revokedAt.Int64)
			t.RevokedAt = &rt
		}
		tokens = append(tokens, t)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return tokens, nil
}

// SaveUser creates or updates a user.
func (s *SQLStore) SaveUser(ctx context.Context, user *User) error {
	var query string
	if s.isPostgres() {
		query = s.rebind(`
			INSERT INTO users (id, issuer, email, role, created_at)
			VALUES (?, ?, ?, ?, ?)
			ON CONFLICT (id) DO UPDATE SET issuer = EXCLUDED.issuer, email = EXCLUDED.email, role = EXCLUDED.role`)
	} else {
		query = s.rebind(`
			INSERT INTO users (id, issuer, email, role, created_at)
			VALUES (?, ?, ?, ?, ?)
			ON CONFLICT (id) DO UPDATE SET issuer = excluded.issuer, email = excluded.email, role = excluded.role`)
	}

	_, err := s.db.ExecContext(ctx, query,
		user.ID,
		user.Issuer,
		user.Email,
		user.Role,
		user.CreatedAt.UnixMilli(),
	)
	return err
}

// GetUser retrieves a user by ID.
func (s *SQLStore) GetUser(ctx context.Context, id string) (*User, error) {
	query := s.rebind(`SELECT id, issuer, email, role, created_at FROM users WHERE id = ?`)
	var user User
	var created int64
	err := s.db.QueryRowContext(ctx, query, id).Scan(
		&user.ID,
		&user.Issuer,
		&user.Email,
		&user.Role,
		&created,
	)
	if err == sql.ErrNoRows {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, err
	}
	user.CreatedAt = time.UnixMilli(created)
	return &user, nil
}

// ListUsers retrieves all registered users.
func (s *SQLStore) ListUsers(ctx context.Context) ([]User, error) {
	query := `SELECT id, issuer, email, role, created_at FROM users`
	rows, err := s.db.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var users []User
	for rows.Next() {
		var user User
		var created int64
		if err := rows.Scan(
			&user.ID,
			&user.Issuer,
			&user.Email,
			&user.Role,
			&created,
		); err != nil {
			return nil, err
		}
		user.CreatedAt = time.UnixMilli(created)
		users = append(users, user)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return users, nil
}

// GetCurrentOIDCKey implements Store.
func (s *SQLStore) GetCurrentOIDCKey(ctx context.Context) (*OIDCKeyPair, error) {
	query := s.rebind(`SELECT kid, private_key, public_key FROM oidc_keyring WHERE expiration IS NULL ORDER BY id DESC LIMIT 1`)
	var kid string
	var privBytes, pubBytes []byte
	err := s.db.QueryRowContext(ctx, query).Scan(&kid, &privBytes, &pubBytes)
	if err == sql.ErrNoRows {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, err
	}
	return &OIDCKeyPair{
		Kid:        kid,
		PrivateKey: privBytes,
		PublicKey:  pubBytes,
	}, nil
}

// GetAllValidOIDCKeys implements Store.
func (s *SQLStore) GetAllValidOIDCKeys(ctx context.Context) ([]OIDCKeyPair, error) {
	query := s.rebind(`SELECT kid, private_key, public_key, expiration FROM oidc_keyring WHERE expiration IS NULL OR expiration > ? ORDER BY id DESC`)
	rows, err := s.db.QueryContext(ctx, query, time.Now().UnixMilli())
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var keys []OIDCKeyPair
	for rows.Next() {
		var kid string
		var priv, pub []byte
		var exp sql.NullInt64
		if err := rows.Scan(&kid, &priv, &pub, &exp); err != nil {
			return nil, err
		}
		var expiration time.Time
		if exp.Valid {
			expiration = time.UnixMilli(exp.Int64)
		}
		privCopy := make([]byte, len(priv))
		copy(privCopy, priv)
		pubCopy := make([]byte, len(pub))
		copy(pubCopy, pub)
		keys = append(keys, OIDCKeyPair{
			Kid:        kid,
			PrivateKey: privCopy,
			PublicKey:  pubCopy,
			Expiration: expiration,
		})
	}
	return keys, rows.Err()
}

// SaveInitialOIDCKey implements Store. It serializes on rotation_lock and only
// inserts when no active OIDC key exists, so concurrent control-plane replicas
// starting against an empty store converge on one initial OIDC key.
func (s *SQLStore) SaveInitialOIDCKey(ctx context.Context, kid string, priv, pub []byte) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	if _, err := tx.ExecContext(ctx, `UPDATE rotation_lock SET next_rotation_at = next_rotation_at WHERE id = 1`); err != nil {
		return err
	}
	query := s.rebind(`INSERT INTO oidc_keyring (kid, private_key, public_key, created_at) SELECT ?, ?, ?, ? WHERE NOT EXISTS (SELECT 1 FROM oidc_keyring WHERE expiration IS NULL)`)
	if _, err := tx.ExecContext(ctx, query, kid, priv, pub, time.Now().UnixMilli()); err != nil {
		return err
	}
	return tx.Commit()
}

// SetOIDCKeyring implements Store.
func (s *SQLStore) SetOIDCKeyring(ctx context.Context, current OIDCKeyPair, verifying []OIDCKeyPair, gracePeriod time.Duration) error {
	if current.Kid == "" {
		return errors.New("SetOIDCKeyring: no current key")
	}
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()
	now := time.Now()

	if _, err := tx.ExecContext(ctx, s.rebind(`DELETE FROM oidc_keyring WHERE expiration IS NOT NULL AND expiration <= ?`), now.UnixMilli()); err != nil {
		return err
	}
	upsert := s.rebind(`INSERT INTO oidc_keyring (kid, private_key, public_key, expiration, created_at) VALUES (?, ?, ?, ?, ?)
		ON CONFLICT (kid) DO UPDATE SET private_key = excluded.private_key, public_key = excluded.public_key, expiration = excluded.expiration`)
	for _, k := range verifying {
		if _, err := tx.ExecContext(ctx, upsert, k.Kid, k.PrivateKey, k.PublicKey, operatorKeyNoExpiry.UnixMilli(), now.UnixMilli()); err != nil {
			return err
		}
	}
	if _, err := tx.ExecContext(ctx, upsert, current.Kid, current.PrivateKey, current.PublicKey, nil, now.UnixMilli()); err != nil {
		return err
	}
	listed := make([]any, 0, len(verifying)+1)
	placeholders := make([]string, 0, len(verifying)+1)
	for _, k := range verifying {
		listed = append(listed, k.Kid)
		placeholders = append(placeholders, "?")
	}
	listed = append(listed, current.Kid)
	placeholders = append(placeholders, "?")
	grace := now.Add(gracePeriod).UnixMilli()
	args := append([]any{grace}, listed...)
	args = append(args, grace)
	retire := s.rebind(`UPDATE oidc_keyring SET expiration = ? WHERE kid NOT IN (` + strings.Join(placeholders, ", ") + `) AND (expiration IS NULL OR expiration > ?)`)
	if _, err := tx.ExecContext(ctx, retire, args...); err != nil {
		return err
	}
	return tx.Commit()
}

// RotateOIDCKeys implements Store.
func (s *SQLStore) RotateOIDCKeys(ctx context.Context, kid string, newPriv, newPub []byte, gracePeriod time.Duration) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	now := time.Now()
	if gracePeriod > 0 {
		expireTime := now.Add(gracePeriod)
		updateQuery := s.rebind(`UPDATE oidc_keyring SET expiration = ? WHERE expiration IS NULL`)
		if _, err := tx.ExecContext(ctx, updateQuery, expireTime.UnixMilli()); err != nil {
			return err
		}
	} else {
		deleteActiveQuery := s.rebind(`DELETE FROM oidc_keyring WHERE expiration IS NULL`)
		if _, err := tx.ExecContext(ctx, deleteActiveQuery); err != nil {
			return err
		}
	}

	insertQuery := s.rebind(`INSERT INTO oidc_keyring (kid, private_key, public_key, created_at) VALUES (?, ?, ?, ?)`)
	if _, err := tx.ExecContext(ctx, insertQuery, kid, newPriv, newPub, now.UnixMilli()); err != nil {
		return err
	}

	deleteQuery := s.rebind(`DELETE FROM oidc_keyring WHERE expiration IS NOT NULL AND expiration <= ?`)
	if _, err := tx.ExecContext(ctx, deleteQuery, now.UnixMilli()); err != nil {
		return err
	}

	return tx.Commit()
}

// SaveRevokedBiscuit implements Store.
func (s *SQLStore) SaveRevokedBiscuit(ctx context.Context, revocationID string, expiresAt time.Time) error {
	if revocationID == "" {
		return nil
	}
	var query string
	if s.isPostgres() {
		query = s.rebind(`INSERT INTO revoked_biscuits (revocation_id, expires_at) VALUES (?, ?) ON CONFLICT (revocation_id) DO UPDATE SET expires_at = EXCLUDED.expires_at`)
	} else {
		query = s.rebind(`INSERT INTO revoked_biscuits (revocation_id, expires_at) VALUES (?, ?) ON CONFLICT (revocation_id) DO UPDATE SET expires_at = excluded.expires_at`)
	}
	_, err := s.db.ExecContext(ctx, query, revocationID, expiresAt.UnixMilli())
	return err
}

// ListRevokedBiscuits implements Store.
func (s *SQLStore) ListRevokedBiscuits(ctx context.Context, now time.Time) (map[string]time.Time, error) {
	nowMs := now.UnixMilli()
	pruneQuery := s.rebind(`DELETE FROM revoked_biscuits WHERE expires_at <= ?`)
	_, _ = s.db.ExecContext(ctx, pruneQuery, nowMs)

	query := s.rebind(`SELECT revocation_id, expires_at FROM revoked_biscuits WHERE expires_at > ?`)
	rows, err := s.db.QueryContext(ctx, query, nowMs)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	res := make(map[string]time.Time)
	for rows.Next() {
		var revID string
		var expMs int64
		if err := rows.Scan(&revID, &expMs); err != nil {
			return nil, err
		}
		res[revID] = time.UnixMilli(expMs)
	}
	return res, rows.Err()
}

// IsBiscuitRevoked implements Store.
func (s *SQLStore) IsBiscuitRevoked(ctx context.Context, revocationID string, now time.Time) (bool, error) {
	if revocationID == "" {
		return false, nil
	}
	query := s.rebind(`SELECT 1 FROM revoked_biscuits WHERE revocation_id = ? AND expires_at > ? LIMIT 1`)
	var one int
	err := s.db.QueryRowContext(ctx, query, revocationID, now.UnixMilli()).Scan(&one)
	if err == sql.ErrNoRows {
		return false, nil
	}
	if err != nil {
		return false, err
	}
	return true, nil
}

// Ping implements Store.
func (s *SQLStore) Ping(ctx context.Context) error {
	if s.db == nil {
		return fmt.Errorf("database connection is nil")
	}
	return s.db.PingContext(ctx)
}

// Close implements Store.
func (s *SQLStore) Close() error {
	return s.db.Close()
}