// Package registry is the host's control DB — the set of routes a deployment
// host serves and how to reach each. Deliberately separate from any app's own
// database: it holds routing + lifecycle state, never app data.
//
// Ported from titogo internal/carlos/registry.go (which carries three fixes
// over Eleven's registry.go: busy_timeout before WAL, List on a fresh
// connection, and — in the edge — first-fingerprint reload), with Eleven's
// activator columns (hibernate, canary, replication, next_due) and the
// platform's placement columns (app, pool, region, channel, addr) restored.
package registry

import (
	"context"
	"crypto/sha256"
	"database/sql"
	"database/sql/driver"
	"errors"
	"fmt"
	"os"
	"regexp"
	"strings"
	"time"

	"modernc.org/sqlite"

	"github.com/carlosframework/platform/internal/controlname"
)

type Registry struct {
	remote *remoteClient
	sql    *sql.DB
	path   string
}

// Route is one host a deployment serves: an app instance the platform
// lifecycle-manages (kind "instance"), a sibling service it only proxies
// (kind "service"), a served file tree (kind "static"), or a process that
// serves no requests and that nothing can dial (kind "sidecar" — its
// next_due wake, via DueInstances, is its only timer wake source).
type Route struct {
	ClaimOwnership

	// WakeIncarnation is transient request state and is never persisted.
	WakeIncarnation string `json:"-"`
	Host            string
	// Slug is the app-account short name usable on a root domain. Empty =
	// subdomain-only.
	Slug   string
	Kind   string // instance | service | static | sidecar
	Socket string // unix socket upstream; wins over Addr when both set
	Addr   string // TCP upstream host:port — whole-app tenants (Kass v1)
	// Addrs is a route's multi-upstream set (HA design, issue #152): a
	// comma-joined list of TCP upstream host:port entries, "" = none. The
	// comma-join is safe because every element is validated by
	// provision.validAddr, whose shape (host:port) cannot itself contain a
	// comma. Addr above remains the legacy single-upstream field — it is
	// untouched by multi-upstream tenants and AddrList folds it into a
	// one-element list when Addrs is empty, so a single-upstream caller sees
	// no difference.
	Addrs string
	// Health is the health-probe config JSON, verbatim, from the record that
	// minted this route — the canonical json.Marshal of provision.Health
	// (wire key "probe" on the record; the rename there was JSON-only, so
	// this column keeps the internal name). "" means unprobed, not a zero or
	// failing probe.
	Health string
	Status string // pending|creating|starting|issuing|ready|failed
	// HoldOp is the operation holding Status (moves spec §5.2); "" when not
	// held. A held row (Status in HoldStatuses) belongs to that operation: the
	// edge never serves it, ticks refuse it and no ordinary wake reaches it.
	// Only SetHold, ReleaseHold and DeleteIfHold change a hold; Upsert's
	// conflict path never touches it. IN Fingerprint() and SnapshotFingerprint,
	// folded in only when non-empty so an unheld registry hashes exactly as it
	// did before the column existed.
	HoldOp string
	// SeedSource is the record's seed as this row needs it
	// (seeds.RowSeed's JSON, "" for none), written by the provision pass
	// (seeded-instances spec §3.4): the activator's restore fallback reads
	// it, never the record. Upsert keeps a non-empty value over an empty
	// one; SetSeedSource converges it. Not in Fingerprint.
	SeedSource string
	// ImportGate is an uncommitted import's gate (seeded-instances spec
	// §5.4): the sha256 of its verify token, or ImportGateClosed during a
	// rollback. Set: the edge answers 503 to every request without the
	// matching token, and ticks skip the row. Only the import transactions
	// (importjournal.go) write it; Upsert's conflict path never changes it.
	// In Fingerprint (the edge must reload on a change), folded only when
	// non-empty.
	ImportGate string

	LastError   string
	DBPath      string
	Version     string
	PrevVersion string
	// GoodVersion, GoodDigest and GoodDemand are the good build (zero-downtime
	// spec §8.5): the last build that served for this instance after passing
	// its health check, the sha256 of its staged executable, and the
	// release's own `swap` demand as verified then. Separate from
	// PrevVersion, which records install history whether or not the build
	// ever ran. Written only by SetGoodBuild, with an expected-current
	// condition; Upsert inserts them on a new row and never changes them on
	// an existing one.
	GoodVersion string
	GoodDigest  string
	GoodDemand  string
	// ServingVersion is the build of the generation that last took new
	// connections (spec §3), written by the agent for status and the CLI.
	// It is not cleared when the instance sleeps.
	ServingVersion string

	App    string // which app's releases this route runs
	Pool   string // placement pool ("" = shared)
	Region string // residency region code ("" = unconstrained); ALSO doubles
	// as a route's fleet placement target ("fleet/<name>" — see
	// FleetPlacement/PlacementTarget, fleets.go) written by SetPlacement, not
	// this comment's original meaning alone.
	Channel string // release channel this route follows ("" = stable)
	// Environment is the named config bundle this route is wired to ("" =
	// the app's default bundle, config.json, alone — see
	// internal/appconfig.LoadMerged). Independent of Channel: channel is
	// how mature a build is, environment is what glue it's wired to
	// (2026-08-02-pipelines-design.md §2/§4a).
	//
	// Written INTENTIONALLY only by `carlos route -environment` (cli.go's
	// runRoute), through the narrow setters below (SetEnvironment,
	// SetEnvironments, SetChannelsAndEnvironments). An earlier revision of
	// this comment claimed that was the only writer at all; it was wrong —
	// every full-row Upsert (`carlos add`, reconcile.go's two mint paths)
	// also writes this column, and did so with the zero value, silently
	// unbinding a route on re-add or re-mint (2026-08-02 review, IMPORTANT
	// 3). Upsert now preserves a non-empty existing value instead; see its
	// doc comment. Clearing therefore has exactly one door: `-environment -`.
	//
	// Platform+Canary routes are refused by `carlos route -environment`
	// (platformCanaryOwnedRow, cli.go) for the same structural reason
	// Channel is: the reconciler owns those rows and re-Upserts them
	// whenever its `followed` check goes false. Upsert's preservation makes
	// that survivable rather than silent, but a column the reconciler
	// rebuilds is not one this verb should be handing an operator.
	Environment string
	// Tunnel is the name of the reverse channel backing this route at the
	// edge ("" = not tunnel-backed — routed to a Socket/Addr upstream as
	// today). Written only by SetTunnel / carlos route -tunnel; Upsert
	// preserves non-empty values — see Environment's comment for why.
	Tunnel string
	// FleetBox is the label of the fleet box CURRENTLY bound to serve this
	// route ("" = no binding) — distinct from Region, which carries the
	// placement TARGET (see FleetPlacement/PlacementTarget, fleets.go); a
	// route can want "fleet/pi" and not yet have a box assigned. Written only
	// by SetFleetBox; Upsert preserves non-empty values — see Environment's
	// comment for why.
	FleetBox string

	// Caps is the OPERATOR-granted capability tokens for this route ON THIS
	// BOX: comma-separated and canonically sorted, so "port:25,host-role"
	// normalises to "host-role,port:25" (the vocabulary and the formatter are
	// internal/caps' Grants — this column's only reader and writer). Default
	// '' = nothing granted = today's behaviour for every row already on every
	// box.
	//
	// Written ONLY by SetCaps, behind `carlos route -grant/-revoke`. A
	// manifest DECLARES `ports`/`host_role`; nothing materializes until an
	// operator has granted it here, on this box — which is the whole point of
	// the column living in the registry, where no tenant can write (see
	// docs/superpowers/specs/2026-08-03-app-capabilities-design.md §2, §7).
	// Upsert preserves a non-empty existing value for the same reason it
	// preserves Environment; see its doc comment.
	//
	// Deliberately NOT in Fingerprint(): grants change unit shape, not
	// routing, and the edge has no reason to reload for one.
	Caps string

	Hibernate bool
	// Gate is the bcrypt hash of the host's shared password ("" = open),
	// converged from provision.Record.Gate by SetGate and read by the edge
	// on every request to the host (internal/gate). Validated at the
	// console door, never here — SetCaps's rule. IN Fingerprint(): a gate
	// set or cleared changes what the edge serves, and must reload it.
	// Upsert preserves a non-empty value against an empty one (the
	// caps/tunnel/unit idiom), so a full-row writer that predates the
	// column can never silently open a gated host.
	Gate string

	Canary bool
	// Replication is the route's litestream sync class — the rung of the
	// replication ladder its database is replicated on. The vocabulary is
	// the four Replication* constants below, and internal/activator maps it
	// to a real sync interval on every wake. "" is not a fifth rung: it is
	// the pre-ladder default, and it deliberately buys what stream buys, so
	// a registry written before the column existed keeps its behaviour.
	//
	// Upsert PRESERVES a non-empty value (see its doc comment), which is
	// what stops the provisioning reconciler — which mints routes from
	// instance records, and records carry no replication class — from
	// silently returning an operator's `daily` route to the default within
	// seconds of the next serial bump.
	Replication string
	NextDue     int64
	StatusNamed bool // publish this route's app by name on the status page — the app's own opt-in

	// Backing is who owns the instance's process: "" = an exec child of the
	// agent (the activator spawns live/<host> itself — the Eleven shape), or
	// BackingUnit = a carlos-app@<host> systemd unit (whole-app tenants:
	// DynamicUser, StateDirectory) that the activator drives via systemctl.
	Backing string

	// Unit is the systemd unit that owns this route's process when it is
	// neither an exec child nor a carlos-app@ tenant — an operator-run
	// bespoke service (console.service is the case that forced the column:
	// an addr-backed route, `-addr 127.0.0.1:8090`, whose process no
	// backing-dispatched restart could reach). Empty for everything else,
	// which is every route on every box today.
	//
	// The adopt pass's restart dispatch tries this unit FIRST when it is set
	// (internal/host's makeRestartRoute): before the change, adoption
	// repointed the symlink, stamped Version — and therefore the edge's
	// X-Carlos-Version header — and cycled nothing, so the old process went
	// on serving a build the header already claimed was live (issue #110).
	//
	// Written by SetUnit, which validates the name, and by Upsert, which does
	// not — Upsert validates no column at all (SetCaps's rule: the rails live
	// at the door), so the convention is that a full-row caller has already
	// run ValidUnitName. `carlos add -unit` is the only such caller today and
	// does exactly that, at the flag, before the registry is opened. Upsert
	// preserves a non-empty existing value — see Environment's comment for
	// why.
	//
	// Deliberately NOT in Fingerprint(): unit changes who restarts the
	// process, not what the edge serves.
	Unit string

	// Platform marks a route the domains reconcile pass minted
	// (<app>.<sqid>.<apps-domain>). Only platform routes are ever
	// auto-removed by reconciliation; custom domains are operator/owner
	// property and never touched.
	Platform bool
	// Provisioned marks a route the instance-provisioning reconcile pass
	// (internal/provision) minted from a bucket instance record. Only
	// provisioned routes are ever auto-removed by that pass — the same
	// authority rule Platform carries for the domains pass, kept as a
	// SEPARATE flag because Platform means "alias to another route's
	// upstream" to three consumers (config sync, upstream picking, apex
	// candidacy) and a provisioned instance is the opposite: it owns a
	// process.
	//
	// Because it carries AUTHORITY, false is not a value a full-row Upsert
	// may impose: `carlos add` on a provisioned host would otherwise revoke
	// the pass's removal right permanently (the environment precedent, with
	// worse stakes — see Upsert). Upsert preserves an existing true; the
	// only clear is the row's own deletion, which is what the removal phase
	// does anyway.
	Provisioned bool
	// Seed is the app's first-boot init argv as a JSON array (e.g.
	// ["seed"]), materialized from the app's instances.json by the
	// provision pass; "" = no seed. Run by the activator only on a wake
	// where restore-on-miss found no replica (backend_exec.go).
	//
	// Same preservation rule as Provisioned and for the same reason: a
	// full-row Upsert from a caller that knows nothing about seeding must
	// not erase one, so Upsert keeps a non-empty existing value and SetSeed
	// (the provision pass's narrow converge) is the only door that clears
	// it — which it must be able to do, for a manifest whose seed was
	// removed.
	Seed string
	// Args is a sidecar's argv as a JSON array, materialized from the app's
	// sidecars manifest on every reconcile pass (truth stays in the bucket).
	// Empty for every other kind of route.
	Args string
	// IdleSeconds and MaxRunSeconds are a sidecar worker's ceilings, in
	// seconds, materialized from the app's manifest exactly as Args and Seed
	// are — 0 means "use the platform default". The sweeper reads them
	// locally on every tick to decide whether a worker has outrun its
	// ceiling; a bucket read per wake would put the control plane in the hot
	// path of a timer.
	IdleSeconds   int
	MaxRunSeconds int
	// DNSStatus is the last DNS health verdict for this host: "pointing"
	// (resolves to a deployment edge IP), "elsewhere", or "" — unknown, the
	// honest answer for a resolver failure, never a guess.
	DNSStatus    string
	DNSCheckedAt int64 // unix seconds of the last DNS check; 0 = never checked
	// CertExpiresAt is the NotAfter of the host's cached ACME cert, unix
	// seconds; 0 = no cert known (absent or unparseable — unknown, not "bad").
	CertExpiresAt int64
	// Account is the app→account linkage carried on the route rows
	// (consistent with the registry's route-centric shape); "" displays as
	// the built-in "ops" account.
	Account string

	// Steering is this route's DNS steering opt-in (multi-region ingress
	// design Part 4): "" (default — today's static per-host answer) or
	// "latency" (a Route53 latency record per armed edge, converged by the
	// steering housekeeper duty). A route's own value, set from
	// provision.Record.Steering via routeFor's mint and SetSteering's
	// converge — provision.ValidSteering is the rail; this column validates
	// nothing, the same discipline every other provision-owned column
	// follows (SetCaps's rule: the rails live at the door).
	//
	// SINGLE-BOX INERT as of this column's introduction: nothing reads it
	// yet. See provision.Record.Steering's doc comment for the task-by-task
	// rollout that gives it effect.
	//
	// Deliberately NOT in Fingerprint(): steering changes what DNS answers,
	// not what the edge serves; the converger is a host housekeeper duty
	// reading the registry directly (Tasks 4-6), never gated on the edge's
	// reload poll. SnapshotFingerprint still covers it, automatically, via
	// routeCols — the restore surface and the reload surface answer
	// different questions (SnapshotFingerprint's own doc comment).
	Steering string

	// UpdateSettings is the instance's effective update settings
	// (release.UpdateSettings' canonical JSON, "" = today's adoption),
	// converged by provision.Reconcile from the record and the app policy.
	// Root reads it (plan 4). Deliberately NOT in Fingerprint(): it
	// changes what root installs, not what the edge serves.
	UpdateSettings string

	// BuildState is the agent's copy of the build-state object (JSON of
	// release.BuildStateCopy; "" = never read), refreshed at reconcile and
	// before every wake. Root reads it. Deliberately NOT in Fingerprint():
	// it changes what root decides, not what the edge serves. The registry
	// holds it as an opaque string (release imports registry, not the other
	// way round).
	BuildState string

	// Replicas is the account's replica placements, comma-joined placement
	// codes in canonical (sorted, deduplicated) order — the registry half of
	// #162. "" on every row that predates the column = no replicas =
	// today's routing, byte for byte. Written by SetReplicas (the provision
	// pass's converge — the value is the record's, and this column must
	// track it, including down to "") and carried by routeFor at the mint.
	//
	// Read by the edges-file pass (a replica placement is where the account
	// is SERVED) and the steering converger (a replica placement earns a
	// latency record); never by the edge's own dispatch. A replica host's
	// own row is a separate route whose Region names its placement, exactly
	// as a home's row does — this column lives on the HOME's row, naming
	// where its replicas sit.
	//
	// Deliberately NOT in Fingerprint(), in Steering's exact shadow: it
	// changes what DNS answers and what the edges file publishes, not what
	// this box's routing map dials. SnapshotFingerprint covers it
	// automatically via routeCols.
	Replicas string

	// Replica marks the row as itself a replica — an edge-checkout shard of
	// the account (#162), holding a one-way mirror of it, selling against a
	// capacity lease from home. Written by routeFor at the mint from
	// Record.Role and converged by SetReplica; never set by an operator
	// verb. Read by the edges-file pass (the row's entries publish with the
	// replica mark, so steering's home-vs-replica fold can tell them apart)
	// and by the reconciler's removal-scope test — never by the edge's own
	// dispatch, which serves the row exactly as any other and never
	// hairpins it.
	//
	// Deliberately NOT in Fingerprint(), Steering's shadow again: a mark
	// changes what the edges file publishes, not what this box dials.
	Replica bool

	CreatedAt int64
}

// AddrList resolves a route's upstream set for a multi-upstream-aware caller:
// Addrs split on the comma when it is set, else Addr folded into a
// one-element list for the legacy single-upstream shape, else nil when the
// route names no TCP upstream at all (a socket-backed or static route). The
// nil case matters to callers that branch on "has upstreams" — an empty
// non-nil slice would read as "has zero upstreams", a different fact.
func (t Route) AddrList() []string {
	if t.Addrs != "" {
		return strings.Split(t.Addrs, ",")
	}
	if t.Addr != "" {
		return []string{t.Addr}
	}
	return nil
}

// IsSidecar reports whether t is a standalone worker (Kind "sidecar") — a
// process that serves no requests and that nothing can dial. A method
// rather than a bare `Kind == "sidecar"` literal at each call site because
// most of those sites are security EXCLUSIONS (the edge's dispatch, a
// fleet's advertised set): a typo in a literal does not fail loudly, it
// routes to or serves a sidecar. A method is typo-proof by construction.
func (t Route) IsSidecar() bool { return t.Kind == "sidecar" }

// BackingUnit marks a route whose process is a carlos-app@<host> systemd unit
// rather than an exec child of the agent. The empty string stays the exec
// default so pre-existing rows keep today's behavior byte-identical.
const BackingUnit = "unit"

// tunnelNameRe: lowercase letters, digits and hyphens, 1-32 characters — the
// same shape as appconfig.ValidEnvironmentName, for the same reason: this
// name travels through a CLI argument an operator types by hand, and later
// work in the remote-host-agent feature (issue #53) matches it against a
// dialed-in tunnel identity and may build keys from it — a permissive shape
// (path separators, spaces, "..") would let a bad value reach the column
// verbatim with nothing downstream able to retroactively fix it.
var tunnelNameRe = regexp.MustCompile(`^[a-z0-9-]{1,32}$`)

// ValidTunnelName reports whether name is usable as a route's Tunnel
// binding. "" is refused as a NAME here — it is how callers ask for "no
// tunnel" (Route.Tunnel's zero value), never a tunnel to bind to; the CLI's
// "-tunnel -" clear sentinel resolves to "" before this is ever called on
// it, the same way -environment's envClear does for ValidEnvironmentName.
// Lives here rather than in a CLI or edge package because Tunnel is this
// package's column — same precedent as BackingUnit above it.
func ValidTunnelName(name string) bool {
	return tunnelNameRe.MatchString(name)
}

// unitNameRe: a systemd unit name that must END in .service — the only unit
// type this column means anything for (a route's process). The character
// class is systemd's own alphabet for the part before the suffix, including
// the '@' of a template instance, so `console.service` and
// `titogo-canary@demo.service` both pass and a typo does not.
//
// The FIRST character is narrower than the rest, and bounded like
// ValidTunnelName's 32 (64 here, because unit names legitimately carry a
// prefix and an instance): a leading '-' is how a name gets mistaken for a
// flag by anything that ever builds an argv from this column, and a leading
// '.' admits `..service`, `..`-shaped names and other things that read as
// paths rather than units. Neither is a name anybody means to type, and both
// are cheaper to refuse than to reason about later.
//
// Lives here rather than in the CLI for the same reason ValidTunnelName does:
// `unit` is this package's column, and this is the shape SetUnit enforces on
// every write, not merely the shape one door happens to check.
var unitNameRe = regexp.MustCompile(`^[A-Za-z0-9@_][A-Za-z0-9@_.\-]{0,63}\.service$`)

// ValidUnitName reports whether name is usable as a route's Unit binding.
// Unlike ValidTunnelName, "" is VALID here: it is the column's default and
// its only "no unit" value, so every caller that can clear a binding
// (`carlos route -unit -`, SetUnit(host, "")) passes it through this same
// gate rather than around it.
//
// The rail is about a typo, not an injection: these strings reach systemctl
// as one argv element of an exec.Command, so there is no shell to escape.
// What there IS, without the rail, is `console.servcie` written silently to
// the column and discovered only as a restart that quietly did nothing —
// exactly the failure the column exists to end.
func ValidUnitName(name string) bool {
	return name == "" || unitNameRe.MatchString(name)
}

// The replication ladder — the vocabulary of the routes.replication column,
// and the ONE place it is written down. internal/activator maps these to
// litestream sync intervals; `carlos route --replication` is the door that
// sets one. They lived only in the activator, unexported, for as long as
// nothing could set the column (discussion #10): three named rungs, one of
// them reachable, and it was the most expensive one.
const (
	// ReplicationDefault is the unset column. It buys what ReplicationStream
	// buys — change-driven ~1s sync — so a registry written before the ladder
	// existed behaves exactly as it did. It is the pre-ladder default rather
	// than a rung anyone chooses.
	ReplicationDefault = ""
	// ReplicationStream syncs on change, ~1s. Loss window: seconds.
	ReplicationStream = "stream"
	// ReplicationBatched syncs at most every 5m. Loss window: minutes.
	ReplicationBatched = "batched"
	// ReplicationDaily syncs at wake and every 24h. Loss window: up to a day.
	ReplicationDaily = "daily"
)

// ReplicationClasses lists the rungs an operator may set, in order of
// decreasing durability. ReplicationDefault is absent on purpose: it is what
// a route has before anyone chooses, and the way back to it is the CLI's
// clear sentinel, not a name to type.
func ReplicationClasses() []string {
	return []string{ReplicationStream, ReplicationBatched, ReplicationDaily}
}

// ValidReplication reports whether class is a value this column may hold.
// "" is valid: it is the column's default and the state a cleared route
// returns to, exactly as ValidUnitName accepts "".
func ValidReplication(class string) bool {
	switch class {
	case ReplicationDefault, ReplicationStream, ReplicationBatched, ReplicationDaily:
		return true
	}
	return false
}

// registryPragmas run on every connection the pool opens, not once on the
// first. busy_timeout FIRST: two processes open this DB concurrently (the
// edge starting while `carlos add` runs), and journal_mode=WAL takes a lock —
// with no timeout set yet it fails immediately with SQLITE_BUSY. A claim
// transaction discards its connection when ROLLBACK or the NORMAL restore
// fails (6b-N1); the replacement must not run with SQLite's defaults
// (busy_timeout 0, synchronous FULL).
var registryPragmas = []string{
	"PRAGMA busy_timeout=5000",
	"PRAGMA journal_mode=WAL",
	"PRAGMA synchronous=NORMAL",
}

func applyRegistryPragmas(conn sqlite.ExecQuerierContext, _ string) error {
	for _, p := range registryPragmas {
		if _, err := conn.ExecContext(context.Background(), p, nil); err != nil {
			return err
		}
	}
	return nil
}

// registryConnector opens every pooled connection through a driver whose
// connection hook applies registryPragmas.
type registryConnector struct {
	d    *sqlite.Driver
	path string
}

func (c registryConnector) Connect(context.Context) (driver.Conn, error) { return c.d.Open(c.path) }
func (c registryConnector) Driver() driver.Driver                        { return c.d }

func Open(path string) (*Registry, error) {
	drv := &sqlite.Driver{}
	drv.RegisterConnectionHook(applyRegistryPragmas)
	d := sql.OpenDB(registryConnector{d: drv, path: path})
	// One writer at a time keeps WAL happy.
	d.SetMaxOpenConns(1)
	// Open the first connection now, so a pragma failure is Open's error as
	// it was when the pragmas ran here.
	if err := d.Ping(); err != nil {
		d.Close()
		return nil, err
	}
	r := &Registry{sql: d, path: path}
	if err := r.migrate(); err != nil {
		d.Close()
		return nil, err
	}
	return r, nil
}

// Path returns the file the registry was opened from.
func (r *Registry) Path() string { return r.path }

// Close releases the long-lived handle. Long-running processes (edge, agent,
// console) deliberately never call this — they hold one handle for the
// process lifetime — but a test that rebuilds a registry file from scratch
// must be able to let go of it first.
func (r *Registry) Close() error {
	if r.remote != nil {
		return r.remote.close()
	}
	return r.sql.Close()
}

// migrate is additive (CREATE TABLE IF NOT EXISTS), so deploying new code
// over an existing registry is always safe. New columns go in the ALTER list —
// the "duplicate column" error is swallowed so the list stays idempotent.
func (r *Registry) migrate() error {
	if _, err := r.sql.Exec(`
CREATE TABLE IF NOT EXISTS routes (
  host         TEXT PRIMARY KEY,
  kind         TEXT NOT NULL,
  socket       TEXT NOT NULL DEFAULT '',
  status       TEXT NOT NULL DEFAULT 'ready',
  last_error   TEXT NOT NULL DEFAULT '',
  db_path      TEXT NOT NULL DEFAULT '',
  version      TEXT NOT NULL DEFAULT '',
  prev_version TEXT NOT NULL DEFAULT '',
  created_at   INTEGER NOT NULL
);`); err != nil {
		return err
	}
	for _, stmt := range []string{
		`ALTER TABLE routes ADD COLUMN slug TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN addr TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN app TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN pool TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN region TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN channel TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN hibernate INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE routes ADD COLUMN canary INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE routes ADD COLUMN replication TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN next_due INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE routes ADD COLUMN backing TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN status_named INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE routes ADD COLUMN platform INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE routes ADD COLUMN dns_status TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN dns_checked_at INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE routes ADD COLUMN cert_expires_at INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE routes ADD COLUMN account TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN environment TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN caps TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN tunnel TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN provisioned INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE routes ADD COLUMN seed TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN unit TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN fleet_box TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN addrs TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN health TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN steering TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN gate TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN args TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN idle_seconds INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE routes ADD COLUMN max_run_seconds INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE routes ADD COLUMN replicas TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN replica INTEGER NOT NULL DEFAULT 0`,
		`ALTER TABLE routes ADD COLUMN hold_op TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN good_version TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN good_digest TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN good_demand TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN serving_version TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN update_settings TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN build_state TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN seed_source TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE routes ADD COLUMN import_gate TEXT NOT NULL DEFAULT ''`,
	} {
		if _, err := r.sql.Exec(stmt); err != nil && !strings.Contains(err.Error(), "duplicate column") {
			return err
		}
	}
	// route_aliases is a derived table (a later reconcile pass owns every
	// row, materializing domains.Claim records into it) — a plain CREATE
	// TABLE IF NOT EXISTS, no legacy shape to carry forward.
	if _, err := r.sql.Exec(`
CREATE TABLE IF NOT EXISTS route_aliases (
  alias           TEXT PRIMARY KEY,
  host            TEXT NOT NULL,
  account         TEXT NOT NULL DEFAULT '',
  app             TEXT NOT NULL DEFAULT '',
  dns_status      TEXT NOT NULL DEFAULT '',
  dns_checked_at  INTEGER NOT NULL DEFAULT 0,
  cert_expires_at INTEGER NOT NULL DEFAULT 0,
  created_at      INTEGER NOT NULL DEFAULT 0
);`); err != nil {
		return err
	}
	// wildcard_domains is a derived table (a later reconcile pass owns every
	// row, materializing domains.Claim records whose Wildcard capability is
	// set into it) — a plain CREATE TABLE IF NOT EXISTS, same shape as
	// route_aliases above: additive, so existing DBs migrate on open.
	if _, err := r.sql.Exec(`
CREATE TABLE IF NOT EXISTS wildcard_domains (
  host       TEXT PRIMARY KEY,
  account    TEXT NOT NULL DEFAULT '',
  app        TEXT NOT NULL DEFAULT '',
  created_at INTEGER NOT NULL DEFAULT 0
);`); err != nil {
		return err
	}
	// route_paths is a derived table (the domains reconcile pass owns every
	// row, materializing a claim's Paths map into it) — a plain CREATE TABLE
	// IF NOT EXISTS, the same additive shape as route_aliases and
	// wildcard_domains above, so existing DBs migrate on open with zero rows
	// and no behaviour change.
	//
	// The key is the PAIR: `routes` has host as its primary key and must
	// stay one row per hostname (that row owns the cert, the home box, the
	// DNS state and the aliases), so a hostname's prefixes live here instead.
	// prefix is a BARE segment — "paulca", never "/paulca".
	if _, err := r.sql.Exec(`
CREATE TABLE IF NOT EXISTS route_paths (
  host    TEXT NOT NULL,
  prefix  TEXT NOT NULL,
  target  TEXT NOT NULL,
  account TEXT NOT NULL DEFAULT '',
  app     TEXT NOT NULL DEFAULT '',
  PRIMARY KEY (host, prefix)
);`); err != nil {
		return err
	}
	if _, err := r.sql.Exec(`CREATE TABLE IF NOT EXISTS timers (
  host        TEXT NOT NULL,
  name        TEXT NOT NULL,
  kind        TEXT NOT NULL,
  spec        TEXT NOT NULL,
  path        TEXT NOT NULL,
  next_at     INTEGER NOT NULL,
  last_at     INTEGER NOT NULL DEFAULT 0,
  last_status TEXT NOT NULL DEFAULT '',
  last_ms     INTEGER NOT NULL DEFAULT 0,
  attempts    INTEGER NOT NULL DEFAULT 0,
  created_at  INTEGER NOT NULL,
  PRIMARY KEY (host, name)
);
CREATE INDEX IF NOT EXISTS timers_due ON timers (next_at) WHERE next_at > 0;`); err != nil {
		return err
	}
	// Additive, same rule as the routes list above: a registry created by a
	// build before due_at existed gains the column on open, and re-opening
	// swallows "duplicate column". Rows carry 0 until the runner next
	// advances them; readers fall back to next_at (registry.Timer.DueAt).
	if _, err := r.sql.Exec(`ALTER TABLE timers ADD COLUMN due_at INTEGER NOT NULL DEFAULT 0`); err != nil && !strings.Contains(err.Error(), "duplicate column") {
		return err
	}
	if _, err := r.sql.Exec(`ALTER TABLE timers ADD COLUMN registration TEXT NOT NULL DEFAULT ''`); err != nil && !strings.Contains(err.Error(), "duplicate column") {
		return err
	}
	if err := r.migrateImportJournal(); err != nil {
		return err
	}
	if err := r.migrateFenceGenerations(); err != nil {
		return err
	}
	if err := r.migrateAccounts(); err != nil {
		return err
	}
	if err := r.migrateClaimOwnership(); err != nil {
		return err
	}
	return r.migrateFleets()
}

// migrateAccounts brings the accounts table to its multi-account shape:
//
//	id INTEGER PRIMARY KEY (supplied by the caller, NOT autoincrement)
//	sqid TEXT (the frozen encoder over that id — the account's public identity)
//	name TEXT (free text, deliberately NOT unique — see the spec: identity is
//	           the sqid, so there is nothing to squat)
//
// The pre-multi-account shape was `(id INTEGER PRIMARY KEY AUTOINCREMENT,
// name TEXT UNIQUE NOT NULL)`, and its rows are exactly the bug this change
// fixes: ids assigned in bucket-listing order, so a rebuild re-homed every
// platform domain. That table is also a pure MATERIALIZATION — every row
// except the pinned ops row is re-derived from the bucket's own
// accounts/<sqid>/account.json manifests on the next reconcile pass, and it
// holds no truth of its own. So the legacy shape is dropped and rebuilt
// rather than ALTERed: carrying the rows forward would preserve wrong ids,
// and carrying the UNIQUE(name) constraint forward would refuse two accounts
// that are both legitimately called "Blog". This is the one deliberate
// exception to the additive-only migration rule, and it is safe precisely
// because there is nothing here to lose.
func (r *Registry) migrateAccounts() error {
	legacy, err := r.accountsTableIsLegacy()
	if err != nil {
		return err
	}
	if legacy {
		if _, err := r.sql.Exec(`DROP TABLE accounts`); err != nil {
			return err
		}
	}
	if _, err := r.sql.Exec(`
CREATE TABLE IF NOT EXISTS accounts (
  id   INTEGER PRIMARY KEY,
  sqid TEXT NOT NULL DEFAULT '',
  name TEXT NOT NULL DEFAULT ''
);`); err != nil {
		return err
	}
	// PARTIAL unique index: real sqids are unique, but a row that predates the
	// sqid column (sqid '') must not collide with another such row — an
	// unmigrated registry has to open before it can be re-materialized.
	if _, err := r.sql.Exec(
		`CREATE UNIQUE INDEX IF NOT EXISTS accounts_sqid ON accounts (sqid) WHERE sqid != ''`); err != nil {
		return err
	}
	// Account #1 is "ops" — the deployment operator's built-in account, created
	// by migration with a PINNED id so its sqid ("bab") is identical on every
	// deployment ever bootstrapped. INSERT OR IGNORE keeps re-runs (every Open
	// migrates) idempotent, and never overwrites a rename.
	_, err = r.sql.Exec(`INSERT OR IGNORE INTO accounts (id, sqid, name) VALUES (1, 'bab', 'ops')`)
	return err
}

// accountsTableIsLegacy reports whether an accounts table exists WITHOUT the
// sqid column — i.e. the pre-multi-account shape.
func (r *Registry) accountsTableIsLegacy() (bool, error) {
	rows, err := r.sql.Query(`PRAGMA table_info(accounts)`)
	if err != nil {
		return false, err
	}
	defer rows.Close()
	exists, hasSqid := false, false
	for rows.Next() {
		var cid int
		var name, typ string
		var notNull int
		var dflt sql.NullString
		var pk int
		if err := rows.Scan(&cid, &name, &typ, &notNull, &dflt, &pk); err != nil {
			return false, err
		}
		exists = true
		if name == "sqid" {
			hasSqid = true
		}
	}
	if err := rows.Err(); err != nil {
		return false, err
	}
	return exists && !hasSqid, nil
}

// Account is one tenancy root: a console-minted id, its frozen sqid (the
// public identity in every platform domain and every bucket key), and a
// human name that is deliberately not unique.
type Account struct {
	ID   int64
	Sqid string
	Name string
}

// PutAccount materializes an account the BUCKET already decided on —
// idempotent upsert BY ID. The registry never assigns an account id: the id
// arrives from accounts/<sqid>/account.json (or, for ops, from migration),
// which is what makes account identity survive a registry rebuild and be
// identical on every box in a deployment.
func (r *Registry) PutAccount(id int64, sqid, name string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "PutAccount", IdValue: id, Sqid: sqid, Name: name}, nil)
		return err
	}

	_, err := r.sql.Exec(`INSERT INTO accounts (id, sqid, name) VALUES (?, ?, ?)
ON CONFLICT(id) DO UPDATE SET sqid = excluded.sqid, name = excluded.name`, id, sqid, name)
	return err
}

// DeleteAccount drops a retired account's row. Called only by the reconcile
// pass, for a manifest the bucket has tombstoned — the bucket decides, this
// follows. Idempotent: an account already gone is a success, because the
// caller's goal is "not here", not "I removed it".
//
// Routes are deliberately untouched. An account cannot be deleted while it
// holds apps, so there should be none; if some route still names this sqid,
// silently unhooking it would take a live site down to tidy up a listing.
// The ops account is pinned and never deletable. The id is duplicated here
// rather than imported from domains.ReservedAccountID because the dependency
// runs the other way — domains imports registry — and a constant is a cheaper
// thing to keep in step than a cycle is to break.
func (r *Registry) DeleteAccount(sqid string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "DeleteAccount", Sqid: sqid}, nil)
		return err
	}

	_, err := r.sql.Exec(`DELETE FROM accounts WHERE sqid = ? AND id != ?`, sqid, opsAccountID)
	return err
}

const opsAccountID = 1

// AccountBySqid resolves an account's public identity to its id and name, or
// (0, "", nil) when absent — "no such account" is an answer, not an error.
func (r *Registry) AccountBySqid(sqid string) (int64, string, error) {
	if r.remote != nil {
		out, err := r.remote.call(nil, remoteRequest{Method: "AccountBySqid", Sqid: sqid}, nil)
		return out.Number64, out.Text, err
	}

	var id int64
	var name string
	err := r.sql.QueryRow(`SELECT id, name FROM accounts WHERE sqid = ?`, sqid).Scan(&id, &name)
	if err == sql.ErrNoRows {
		return 0, "", nil
	}
	if err != nil {
		return 0, "", err
	}
	return id, name, nil
}

// Accounts returns every materialized account, ordered by id (so ops is
// always first).
func (r *Registry) Accounts() ([]Account, error) {
	if r.remote != nil {
		out, err := r.remote.call(nil, remoteRequest{Method: "Accounts"}, nil)
		return out.Accounts, err
	}

	rows, err := r.sql.Query(`SELECT id, sqid, name FROM accounts ORDER BY id`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Account
	for rows.Next() {
		var a Account
		if err := rows.Scan(&a.ID, &a.Sqid, &a.Name); err != nil {
			return nil, err
		}
		out = append(out, a)
	}
	return out, rows.Err()
}

// routeCols is the column list every route read and every route insert
// share: routeColCount names, in this exact order. scanRoute's destinations
// and routeArgs's values count from here, and every insert (Upsert, the
// import swap, coldhandover.go's restore) binds through routeArgs. Adding a
// column means adding it in SIX places: the Route struct, the migration
// list, routeCols with routeColCount and scanRoute, routeArgs, Upsert's CASE
// arm, and remote_dispatch.go's remoteRequest and dispatch case for its
// narrow setter. A miscount is a silent column shift rather than an error
// (TestUpsertRoundTripsAllColumns is what catches one; an insert whose
// values miss a column fails on column count).
const routeCols = `host, slug, kind, socket, addr, status, last_error, db_path,
version, prev_version, app, pool, region, channel, hibernate, canary,
replication, next_due, backing, status_named, platform, dns_status,
dns_checked_at, cert_expires_at, account, environment, caps, tunnel,
provisioned, seed, unit, fleet_box, addrs, health, steering, gate, created_at,
args, idle_seconds, max_run_seconds, replicas, replica, claim_incarnation, claim_stamp, claim_owner, claim_row_id, hold_op,
good_version, good_digest, good_demand, serving_version, update_settings, build_state,
seed_source, import_gate`

// routeColCount is the number of names in routeCols.
const routeColCount = 55

// placeholders is n comma-joined bind parameters.
func placeholders(n int) string {
	return strings.TrimSuffix(strings.Repeat("?,", n), ",")
}

func scanRoute(scan func(...any) error) (Route, error) {
	var t Route
	var hib, can, sn, plat, prov, rep int
	err := scan(&t.Host, &t.Slug, &t.Kind, &t.Socket, &t.Addr, &t.Status,
		&t.LastError, &t.DBPath, &t.Version, &t.PrevVersion, &t.App, &t.Pool,
		&t.Region, &t.Channel, &hib, &can, &t.Replication, &t.NextDue, &t.Backing,
		&sn, &plat, &t.DNSStatus, &t.DNSCheckedAt, &t.CertExpiresAt, &t.Account,
		&t.Environment, &t.Caps, &t.Tunnel, &prov, &t.Seed, &t.Unit, &t.FleetBox,
		&t.Addrs, &t.Health, &t.Steering, &t.Gate, &t.CreatedAt,
		&t.Args, &t.IdleSeconds, &t.MaxRunSeconds, &t.Replicas, &rep, &t.ClaimIncarnation, &t.ClaimStamp, &t.ClaimOwner, &t.ClaimRowID, &t.HoldOp,
		&t.GoodVersion, &t.GoodDigest, &t.GoodDemand, &t.ServingVersion, &t.UpdateSettings, &t.BuildState,
		&t.SeedSource, &t.ImportGate)
	t.Hibernate = hib != 0
	t.Canary = can != 0
	t.StatusNamed = sn != 0
	t.Platform = plat != 0
	t.Provisioned = prov != 0
	t.Replica = rep != 0
	return t, err
}

// List returns every route, ordered by host, over a FRESH connection — not
// the long-lived pool. modernc/sqlite's long-lived connections can serve a
// stale WAL snapshot for commits made by OTHER processes; a Reload built on a
// stale List sticks permanently because the fingerprint watcher, already
// matching, never retriggers. (Observed live in titogo, 2026-07-25.) List is
// reload-rare, so the ~1ms open cost is nothing.
func (r *Registry) List() ([]Route, error) {
	if r.remote != nil {
		out, err := r.remote.call(nil, remoteRequest{Method: "List"}, nil)
		return out.Routes, err
	}

	db, err := sql.Open("sqlite", r.path)
	if err != nil {
		return nil, err
	}
	defer db.Close()
	// busy_timeout only: the DB is already WAL, and this connection only reads.
	if _, err := db.Exec("PRAGMA busy_timeout=5000"); err != nil {
		return nil, err
	}
	rows, err := db.Query(`SELECT ` + routeCols + ` FROM routes ORDER BY host`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Route
	for rows.Next() {
		t, err := scanRoute(rows.Scan)
		if err != nil {
			return nil, err
		}
		out = append(out, t)
	}
	return out, rows.Err()
}

// Get returns the route for host, or (nil, nil) if there is none.
func (r *Registry) Get(host string) (*Route, error) {
	if r.remote != nil {
		out, err := r.remote.call(nil, remoteRequest{Method: "Get", Host: host}, nil)
		return out.Route, err
	}

	row := r.sql.QueryRow(`SELECT `+routeCols+` FROM routes WHERE host = ?`, host)
	t, err := scanRoute(row.Scan)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &t, nil
}

// Delete removes a route by host (data-safe: it stops routing the host but
// never touches its DB).
func (r *Registry) Delete(host string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "Delete", Host: host}, nil)
		return err
	}

	_, err := r.sql.Exec(`DELETE FROM routes WHERE host = ?`, host)
	return err
}

// Upsert inserts or updates a route by host. On conflict it NEVER changes
// hold_op, and a row that is held keeps its status too, whatever the incoming
// Route carries: only SetHold, ReleaseHold and DeleteIfHold change a hold
// (moves spec §5.2; a stale Upsert carrying a finished operation's HoldOp must
// not hand the row back to it). A fresh INSERT does write the incoming HoldOp,
// so a held row can be materialised.
// Twelve columns are deliberately
// not plain overwrites — `environment`, `caps`, `tunnel`, `provisioned`,
// `seed`, `unit`, `fleet_box`, `replication`, `gate`, `args`, `idle_seconds`
// and `max_run_seconds` all keep the existing row's value when the incoming
// Route carries the zero one.
//
// The reason is the same for all twelve, and it is structural: a full-row
// Upsert caller (`carlos add`, the domains reconciler's two mint paths, the
// provision pass's mint) builds its Route from what IT knows, and none of
// them knows about the columns another feature owns. `environment` was the
// first to be caught (2026-08-02 review, IMPORTANT 3): a plain
// excluded.environment silently unbound a route on any re-add or re-mint,
// and the next config sync rendered the default bundle over its env file and
// restarted its unit. `caps` is the same shape with a worse blast radius —
// a plain excluded.caps would silently REVOKE an operator's `port:25` the
// next time `carlos add` re-ran or the reconciler re-minted the row, hours
// later, when the converge pass next ran. `provisioned` and `seed` raise
// the stakes again: `carlos add` on a provisioned host would write
// provisioned=0, and the reconcile pass would then lose removal authority
// over that route FOREVER — deleting the bucket record could no longer
// retire it, and only a human noticing would. `fleet_box` is the same shape
// as `tunnel`: a placement pass stamps which box is CURRENTLY serving a
// route, and a full-row Upsert from provisioning (which does not know about
// fleet placement at all) must not un-stamp it mid-lifecycle.
//
// `unit` is the same shape as `tunnel` with the blast radius of `caps`: a
// re-`carlos add` on the console's row would drop the stamp that is the only
// thing making adoption cycle that process, and nothing would report it —
// the next promote would stamp X-Carlos-Version onto a console still serving
// the old build, which is issue #110 returning silently.
//
// `args`, `idle_seconds` and `max_run_seconds` are the same shape as `seed`
// and for the same reason (2026-08-17 fix round 1): they are materialized
// from a sidecar's manifest by the reconcile pass, and a full-row Upsert
// from a caller that knows nothing about sidecars (`carlos add`) must not
// blank a live argv or reset a worker's ceilings to the platform default.
// Omitting them from this clause entirely — the original shape of this
// change — was tried and rejected: omission is not preserve-if-empty, it
// silently ignores even a genuine NEW value, so a sidecar whose manifest
// argv changed would keep stale argv on the row until a narrow SetArgs
// happened to run.
//
// `replication` joined them when the ladder got a door (discussion #10) —
// the same CASE as `environment`, but NOT, as an earlier draft of this
// paragraph claimed, because the provisioning reconciler re-Upserts owned
// rows. It does not: its only reg.Upsert is the mint branch (reconcile.go,
// `case !ok`), and the owned branch converges through narrow setters
// (convergeOwned, SetHibernate, SetSeed), exactly as the addrs/health
// paragraph below already says. The domains reconciler's two Upserts are
// mint-only too, guarded by byHost/hasApex. A reader who checks the old
// claim finds it false and reasonably concludes the protection is
// unnecessary, so it is worth stating what is actually true.
//
// The caller that really does Upsert over an EXISTING row is `carlos add`
// (cli.go's runAdd — it prints "updated <host> (existing route)" when it
// does). It builds its Route literal from flags alone, and it has no
// replication flag: without this CASE, a re-add to restamp a unit or
// repoint an addr would return an operator's `daily` route to the
// every-second default, silently, at roughly 100x the S3 traffic the
// setting was chosen to avoid. That is the same gap `environment` was
// caught in and the same gap Addrs/Health/Steering are closed against at
// the CLI's own door below.
//
// Against the two mint-only reconcilers the CASE is defensive rather than
// load-bearing, and it stays that way deliberately: the rule for this
// column is then identical to the seven beside it, so the NEXT full-row
// Upsert caller — which will know nothing about replication either — is
// covered before it is written rather than after it is diagnosed.
//
// An explicit non-zero value still wins in every case; clearing keeps
// exactly one door per column, none of which goes through Upsert:
// SetEnvironment (`carlos route -environment -`), SetCaps (`carlos route
// -revoke`), SetTunnel (`carlos route -tunnel -`), SetUnit (`carlos route
// -unit -`), SetReplication (`carlos route -replication -`), SetSeed,
// SetFleetBox, SetArgs, SetWorkerLimits, SetUpdateSettings, SetBuildState, and for
// `provisioned` the row's own deletion.
//
// `replication` is the one column with a SECOND clearing door, and it is not
// an operator's: SetHibernate clears it when a route stops hibernating,
// because a rung is read only on the activator's wake path and describes
// nothing on a route that no longer sleeps. This statement's own
// `excluded.hibernate = 0` arm is that same rule reaching the full-row
// callers. See SetHibernate for the whole of it.
//
// `addrs` and `health` (multi-upstream routing, issue #152) are, by
// contrast, PLAIN overwrites — see their ON CONFLICT clause below — same as
// `addr`, `status` and the rest of the un-preserved majority. That is safe
// against the provision reconcile pass specifically: provision.routeFor
// (reconcile.go, func routeFor) now carries Record.Addrs/Health onto every
// route it mints, and the mint branch (reconcile.go, routeFor → reg.Upsert)
// is the only reconcile branch that calls Upsert at all — the owned-route
// branch (reconcile.go, the `existing.Provisioned && ...` case) converges
// through narrow setters (convergeOwned, SetHibernate, SetSeed), never a
// full-row Upsert, so a reconcile tick on an already-owned host cannot blank
// what SetUpstreams wrote. The one remaining plain-overwrite risk was
// `carlos add` re-run over an existing host: its Upsert builds a Route
// literal from flags alone, none of which name Addrs/Health, so re-adding a
// host with an established multi-upstream set would blank it, the same way a
// plain excluded.environment once silently unbound a route. Closed at the
// CLI's own door (cli.go's runAdd, Task 8): when a route already exists for
// the host, its Addrs/Health (and Addr, when the invocation named none of its
// own) are copied onto the literal before this call, the same
// preserve-what-you-don't-manage shape as the CASE columns above, just done
// by the caller instead of the ON CONFLICT clause.
//
// `steering` (multi-region ingress design Part 4) joins `addrs`/`health` as a
// PLAIN overwrite: provision.routeFor carries Record.Steering onto every
// route it mints, and the owned-route branch converges it through
// SetSteering rather than a full-row Upsert. `carlos add` carries no
// `-steering` flag, but it IS a caller that can reach this Upsert with an
// unrelated Route literal — the same gap Addrs/Health had — so it is closed
// the same way, at the CLI's own door: a re-add over an existing host copies
// existing.Steering onto the literal before this call, beside Addrs/Health.
// Without that copy, a bare re-add during the self-heal window after the
// steering housekeeper duty (Task 6) is armed would silently clear a route's
// Route53 latency records — a real incident this repo caught in review before
// it shipped, not a hypothetical.
// `update_settings` and `build_state` keep an existing value only while the
// row's tenant (effective account, app) stays the same. An Upsert that moves
// the hostname to another tenant takes the incoming values, empty included:
// one tenant's manual or major hold, or its build state, is never another
// tenant's (settings-5 task 8). The account compared is the effective one
// (EffectiveAccount, as release.RouteAccount reads it), so a legacy row's
// empty account re-added as the ops account is the same tenant and keeps
// both (Astra r4 finding 2). SQLite evaluates every SET expression against
// the row as it was, so `routes.account` and `routes.app` there are still
// the old tenant's.
// `good_version`, `good_digest`, `good_demand` and `serving_version`
// (zero-downtime spec §8.5) are absent from the ON CONFLICT clause on
// purpose: an existing row keeps them whatever the incoming Route carries.
// They have one writer each (SetGoodBuild, SetServingVersion), and no
// full-row caller knows what an instance last served.
// opsAccount is the ops account's sqid: account #1, pinned by migrate. It is
// release.OpsAccount (release cannot be imported here; a test in release
// pins the two together through EffectiveAccount).
const opsAccount = "bab"

// EffectiveAccount is the account a route's stored account names: itself
// when it is a valid account sqid (controlname.ValidAccount), else the ops
// account. It is release.RouteAccount on the account alone, which root and
// the agent use for a route's tenant; Upsert compares tenants by it.
func EffectiveAccount(account string) string {
	if controlname.ValidAccount(account) {
		return account
	}
	return opsAccount
}

// effectiveAccountSQL is EffectiveAccount as a SQLite expression over col.
// controlname.ValidAccount is ^[a-z]{3,16}$: three to sixteen characters,
// none outside a-z. SQLite's length and GLOB both stop at an embedded NUL,
// so the NUL is rejected explicitly (instr compares the whole value);
// without that "abc\x00x" would read as the valid "abc" while Go rejects it.
// TestEffectiveAccountSQLIsEffectiveAccount pins the two together.
func effectiveAccountSQL(col string) string {
	return `(CASE WHEN length(` + col + `) BETWEEN 3 AND 16 AND instr(` + col + `, char(0)) = 0 AND ` + col +
		` NOT GLOB '*[^a-z]*' THEN ` + col +
		` ELSE '` + opsAccount + `' END)`
}

// tenantChangedSQL is true in Upsert's ON CONFLICT clause when the incoming
// row names another tenant than the stored one: another effective account
// or another app.
var tenantChangedSQL = effectiveAccountSQL("routes.account") + ` != ` + effectiveAccountSQL("excluded.account") +
	` OR routes.app != excluded.app`

func (r *Registry) Upsert(t Route) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "Upsert", TValue: t}, nil)
		return err
	}

	res, err := r.sql.Exec(`
INSERT INTO routes (`+routeCols+`)
VALUES (`+placeholders(routeColCount)+`)
ON CONFLICT(host) DO UPDATE SET
  slug=excluded.slug, kind=excluded.kind, socket=excluded.socket,
  addr=excluded.addr,
  status=CASE WHEN routes.hold_op != '' THEN routes.status ELSE excluded.status END,
  last_error=excluded.last_error,
  db_path=excluded.db_path, version=excluded.version,
  prev_version=excluded.prev_version, app=excluded.app, pool=excluded.pool,
  region=excluded.region, channel=excluded.channel,
  hibernate=excluded.hibernate, canary=excluded.canary,
  next_due=excluded.next_due,
  backing=excluded.backing, status_named=excluded.status_named,
  platform=excluded.platform, dns_status=excluded.dns_status,
  dns_checked_at=excluded.dns_checked_at,
  cert_expires_at=excluded.cert_expires_at, account=excluded.account,
  environment=CASE WHEN excluded.environment = '' THEN routes.environment ELSE excluded.environment END,
  replication=CASE WHEN excluded.hibernate = 0 THEN '' WHEN excluded.replication = '' THEN routes.replication ELSE excluded.replication END,
  caps=CASE WHEN excluded.caps = '' THEN routes.caps ELSE excluded.caps END,
  tunnel=CASE WHEN excluded.tunnel = '' THEN routes.tunnel ELSE excluded.tunnel END,
  provisioned=CASE WHEN excluded.provisioned = 0 THEN routes.provisioned ELSE excluded.provisioned END,
  seed=CASE WHEN excluded.seed = '' THEN routes.seed ELSE excluded.seed END,
  unit=CASE WHEN excluded.unit = '' THEN routes.unit ELSE excluded.unit END,
  fleet_box=CASE WHEN excluded.fleet_box = '' THEN routes.fleet_box ELSE excluded.fleet_box END,
  addrs=excluded.addrs, health=excluded.health, steering=excluded.steering,
  replicas=excluded.replicas,
  gate=CASE WHEN excluded.gate = '' THEN routes.gate ELSE excluded.gate END,
  args=CASE WHEN excluded.args = '' THEN routes.args ELSE excluded.args END,
  idle_seconds=CASE WHEN excluded.idle_seconds = 0 THEN routes.idle_seconds ELSE excluded.idle_seconds END,
  max_run_seconds=CASE WHEN excluded.max_run_seconds = 0 THEN routes.max_run_seconds ELSE excluded.max_run_seconds END,
  update_settings=CASE WHEN `+tenantChangedSQL+` THEN excluded.update_settings
    WHEN excluded.update_settings = '' THEN routes.update_settings ELSE excluded.update_settings END,
  build_state=CASE WHEN `+tenantChangedSQL+` THEN excluded.build_state
    WHEN excluded.build_state = '' THEN routes.build_state ELSE excluded.build_state END,
  seed_source=CASE WHEN excluded.seed_source = '' THEN routes.seed_source ELSE excluded.seed_source END
WHERE routes.claim_incarnation=excluded.claim_incarnation AND routes.claim_stamp=excluded.claim_stamp
AND routes.claim_owner=excluded.claim_owner AND routes.claim_row_id=excluded.claim_row_id`,
		routeArgs(normalizeRoute(t))...)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err == nil && n != 1 {
		return ErrClaimOwnership
	}
	return err
}

// normalizeRoute applies Upsert's defaults and invariants to a row about to
// be inserted. The import swap's new row goes through it too; a restored
// original does not (it goes back exactly as it was stored).
func normalizeRoute(t Route) Route {
	if t.CreatedAt == 0 {
		t.CreatedAt = time.Now().Unix()
	}
	if t.Status == "" {
		t.Status = "ready"
	}
	if t.Kind == "" {
		t.Kind = "instance"
	}
	// The replication invariant, INSERT half (the ON CONFLICT half is the
	// `excluded.hibernate = 0` arm in Upsert): a non-empty class on a row that
	// does not hibernate is a column that lies. Nothing reads it there —
	// the activator's wake path is its only reader, and box-wide
	// /etc/litestream.yml covers every non-hibernating database at its own
	// interval — so a stored `daily` would be reported by `carlos routes`
	// while the database was streamed every second. See SetHibernate.
	if !t.Hibernate {
		t.Replication = ""
	}
	// #162's replica invariant, its clean form: a replica SET on a row that
	// IS itself a replica is a column that lies — a replica of a replica is
	// not a shape any supported writer produces, and a replica row's
	// placement is its own Region. Dropped silently, like the replication
	// rung above.
	if t.Replica {
		t.Replicas = ""
	}
	return t
}

// routeArgs is t's values in routeCols order, exactly as given: every route
// insert binds through it, so a column added later is added here once.
func routeArgs(t Route) []any {
	return []any{
		t.Host, t.Slug, t.Kind, t.Socket, t.Addr, t.Status, t.LastError,
		t.DBPath, t.Version, t.PrevVersion, t.App, t.Pool, t.Region, t.Channel,
		b2iDB(t.Hibernate), b2iDB(t.Canary), t.Replication, t.NextDue, t.Backing,
		b2iDB(t.StatusNamed), b2iDB(t.Platform), t.DNSStatus, t.DNSCheckedAt,
		t.CertExpiresAt, t.Account, t.Environment, t.Caps, t.Tunnel,
		b2iDB(t.Provisioned), t.Seed, t.Unit, t.FleetBox, t.Addrs, t.Health,
		t.Steering, t.Gate, t.CreatedAt, t.Args, t.IdleSeconds, t.MaxRunSeconds,
		t.Replicas, b2iDB(t.Replica), t.ClaimIncarnation, t.ClaimStamp, t.ClaimOwner, t.ClaimRowID, t.HoldOp,
		t.GoodVersion, t.GoodDigest, t.GoodDemand, t.ServingVersion, t.UpdateSettings, t.BuildState,
		t.SeedSource, t.ImportGate,
	}
}

// SetSeedSource converges a row's seed column from its record.
func (r *Registry) SetSeedSource(host, v string) error {
	if r.remote != nil {
		return errRemoteMethod
	}
	res, err := r.sql.Exec(`UPDATE routes SET seed_source = ? WHERE host = ?`, v, host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// HoldStatuses are the row statuses the edge never serves, ticks refuse and
// no ordinary wake reaches (moves spec §5.2).
var HoldStatuses = map[string]bool{"moving": true, "landing": true, "restoring": true, "releasing": true, "relocating": true}

// IsHold reports whether status is one of HoldStatuses.
func IsHold(status string) bool { return HoldStatuses[status] }

// SetHold writes a hold status with the operation holding it. It refuses a
// status that is not in HoldStatuses and an empty op, and errors when no row
// matched.
//
// Every SetHold caller also touches the stage-requested marker (moves Task
// 13's rollback hold rule relies on it); SetHold itself does not, because the
// marker lives outside this table.
func (r *Registry) SetHold(host, status, op string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetHold", Host: host, Status: status, Op: op}, nil)
		return err
	}

	if !IsHold(status) || op == "" {
		return fmt.Errorf("registry: %q with op %q is not a hold", status, op)
	}
	res, err := r.sql.Exec(`UPDATE routes SET status = ?, hold_op = ? WHERE host = ?`, status, op, host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// SetHoldIf is SetHold conditioned on the row's current hold_op, in one
// statement: the row is held status under op only while its hold_op is
// still expectOp, which is "" (the caller saw the row unheld) or op itself
// (an op changing the status of its own hold). Any other expectOp is
// refused: a row held by another op leaves that hold only through
// ReleaseHold, under the op that holds it (§5.2), never by takeover. false
// with no error means nothing matched (the row's hold changed since the
// caller read it, or there is no row), so a hold decided from an earlier
// read never lands over what is there now (moves Task 6 fix round 1).
//
// As with SetHold, every caller touches the stage-requested marker after a
// write that landed.
func (r *Registry) SetHoldIf(host, status, op, expectOp string) (bool, error) {
	if r.remote != nil {
		out, err := r.remote.call(nil, remoteRequest{Method: "SetHoldIf", Host: host, Status: status, Op: op, ExpectOp: expectOp}, nil)
		return out.Changed, err
	}

	if !IsHold(status) || op == "" {
		return false, fmt.Errorf("registry: %q with op %q is not a hold", status, op)
	}
	if expectOp != "" && expectOp != op {
		return false, fmt.Errorf("registry: hold %s by %q expecting %q: another op's hold is released by that op, not taken over", host, op, expectOp)
	}
	res, err := r.sql.Exec(`UPDATE routes SET status = ?, hold_op = ? WHERE host = ? AND hold_op = ?`, status, op, host, expectOp)
	if err != nil {
		return false, err
	}
	n, err := res.RowsAffected()
	return n > 0, err
}

// HandHold hands a held row from one op to another in one statement: the
// row is held status under toOp only while its hold_op is still fromOp
// (moves Plan 4, ruling J3). It is the one takeover there is, between the
// two ops of one move: the move id hands its landing or moving hold to the
// rollback id. Releasing first and holding after would leave the row
// unheld between the two writes. An unheld row is never taken (SetHoldIf
// with expectOp "" is that), and a row held by any other op is left alone.
// false with no error means nothing matched: the row's hold changed since
// the caller read it (a replay finds it already handed over), or there is
// no row. The box never calls this directly: internal/host's handHold
// wraps it under WithHostHoldLock and touches the stage-requested marker
// after a hand-over that landed, which this package cannot reach.
func (r *Registry) HandHold(host, status, toOp, fromOp string) (bool, error) {
	if r.remote != nil {
		out, err := r.remote.call(nil, remoteRequest{Method: "HandHold", Host: host, Status: status, ToOp: toOp, FromOp: fromOp}, nil)
		return out.Changed, err
	}

	if !IsHold(status) || toOp == "" || fromOp == "" || toOp == fromOp {
		return false, fmt.Errorf("registry: hand %s from %q to %q as %q is not a hand-over between two ops' holds", host, fromOp, toOp, status)
	}
	res, err := r.sql.Exec(`UPDATE routes SET status = ?, hold_op = ? WHERE host = ? AND hold_op = ?`, status, toOp, host, fromOp)
	if err != nil {
		return false, err
	}
	n, err := res.RowsAffected()
	return n > 0, err
}

// ReleaseHold is §5.2's one conditional statement: a row leaves a hold only
// under the op that holds it. false means nothing matched (a late write from a
// finished operation, an unheld row, or an empty op).
func (r *Registry) ReleaseHold(host, op string) (bool, error) {
	if r.remote != nil {
		out, err := r.remote.call(nil, remoteRequest{Method: "ReleaseHold", Host: host, Op: op}, nil)
		return out.Changed, err
	}

	if op == "" {
		return false, nil
	}
	res, err := r.sql.Exec(`UPDATE routes SET status = 'ready', hold_op = '' WHERE host = ? AND hold_op = ?`, host, op)
	if err != nil {
		return false, err
	}
	n, err := res.RowsAffected()
	return n > 0, err
}

// DeleteIfHold deletes host's row only while op holds it: the source's row
// deletion at finalization (moves spec §7.9 step 3). A row held by another
// op, or not held, is not deleted and the answer is false.
func (r *Registry) DeleteIfHold(host, op string) (bool, error) {
	if r.remote != nil {
		out, err := r.remote.call(nil, remoteRequest{Method: "DeleteIfHold", Host: host, Op: op}, nil)
		return out.Changed, err
	}

	if op == "" {
		return false, nil
	}
	res, err := r.sql.Exec(`DELETE FROM routes WHERE host = ? AND hold_op = ?`, host, op)
	if err != nil {
		return false, err
	}
	n, err := res.RowsAffected()
	return n > 0, err
}

// DeleteIfHoldWithTimers is the source's finalization (moves spec §7.9 step
// 3, ruling H5(d)): host's row and every timer row of host are deleted in one
// transaction, and only while op holds the row. false means nothing matched
// and nothing was deleted, timers included, so a timers clear never runs
// ahead of the row it belongs to or lands on a successor's rows.
func (r *Registry) DeleteIfHoldWithTimers(host, op string) (bool, error) {
	if r.remote != nil {
		out, err := r.remote.call(nil, remoteRequest{Method: "DeleteIfHoldWithTimers", Host: host, Op: op}, nil)
		return out.Changed, err
	}

	if op == "" {
		return false, nil
	}
	tx, err := r.sql.Begin()
	if err != nil {
		return false, err
	}
	defer tx.Rollback()
	res, err := tx.Exec(`DELETE FROM routes WHERE host = ? AND hold_op = ?`, host, op)
	if err != nil {
		return false, err
	}
	if n, err := res.RowsAffected(); err != nil || n == 0 {
		return false, err
	}
	if _, err := tx.Exec(`DELETE FROM timers WHERE host = ?`, host); err != nil {
		return false, err
	}
	if err := tx.Commit(); err != nil {
		return false, err
	}
	return true, nil
}

// HoldRows returns every row held by an operation (a non-empty hold_op), ordered by
// host. Over a fresh connection, like List, so a reader in another process
// sees the latest commit.
func (r *Registry) HoldRows() ([]Route, error) {
	if r.remote != nil {
		out, err := r.remote.call(nil, remoteRequest{Method: "HoldRows"}, nil)
		return out.Routes, err
	}

	db, err := sql.Open("sqlite", r.path)
	if err != nil {
		return nil, err
	}
	defer db.Close()
	if _, err := db.Exec("PRAGMA busy_timeout=5000"); err != nil {
		return nil, err
	}
	rows, err := db.Query(`SELECT ` + routeCols + ` FROM routes WHERE hold_op != '' ORDER BY host`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Route
	for rows.Next() {
		t, err := scanRoute(rows.Scan)
		if err != nil {
			return nil, err
		}
		out = append(out, t)
	}
	return out, rows.Err()
}

// WithHoldsFrozen runs fn with the rows an operation holds, while no other
// connection can write the registry: it takes SQLite's write lock first
// (BEGIN IMMEDIATE), so a SetHold or ReleaseHold elsewhere waits on its
// busy_timeout until fn returns, and the rows fn was given are still the
// held rows when it does. It is root's activation guard (moves Task 13): the
// check that no row is held and the step that puts an older executable in
// place happen with the answer held still. fn must be quick (a rename);
// writers give up after busy_timeout. Nothing is written: the transaction is
// rolled back. fn's error is returned.
func (r *Registry) WithHoldsFrozen(fn func(held []Route) error) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "WithHoldsFrozen"}, func(out remoteResponse) error { return fn(out.Held) })
		return err
	}

	ctx := context.Background()
	conn, err := r.sql.Conn(ctx)
	if err != nil {
		return err
	}
	defer conn.Close()
	if _, err := conn.ExecContext(ctx, "BEGIN IMMEDIATE"); err != nil {
		return err
	}
	defer conn.ExecContext(ctx, "ROLLBACK")
	rows, err := conn.QueryContext(ctx, `SELECT `+routeCols+` FROM routes WHERE hold_op != '' ORDER BY host`)
	if err != nil {
		return err
	}
	var held []Route
	for rows.Next() {
		t, err := scanRoute(rows.Scan)
		if err != nil {
			rows.Close()
			return err
		}
		held = append(held, t)
	}
	if err := rows.Err(); err != nil {
		rows.Close()
		return err
	}
	rows.Close()
	return fn(held)
}

// SetAccount stamps a route's app→account linkage as a narrow UPDATE — the
// reconcile pass re-stamps this every tick from the bucket manifests, and a
// read-modify-write Upsert racing a deploy's SetVersions (or the activator's
// SetNextDue) would clobber those columns.
func (r *Registry) SetAccount(host, account string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetAccount", Host: host, Account: account}, nil)
		return err
	}

	_, err := r.sql.Exec(`UPDATE routes SET account = ? WHERE host = ?`, account, host)
	return err
}

// SetChannel repoints a route's release-channel subscription as a narrow
// UPDATE (same rationale as SetAccount: a read-modify-write Upsert racing a
// deploy's SetVersions or the activator's SetNextDue would clobber those
// columns). Unlike SetAccount it errors when no row matched, so `carlos
// route` can report an unknown host rather than silently doing nothing — this
// is the one setter a human types a hostname into directly.
func (r *Registry) SetChannel(host, channel string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetChannel", Host: host, Channel: channel}, nil)
		return err
	}

	res, err := r.sql.Exec(`UPDATE routes SET channel = ? WHERE host = ?`, channel, host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// SetStatus writes a route's lifecycle status word. Same contract as
// SetChannel: a narrow UPDATE that cannot clobber concurrent column writes,
// and an error when no row matched. The residence-move cut (internal/host's
// move worker) is its first caller — status "moving" takes a route out of
// the edge's served set and the activator's managed set in one write, which
// is what quiesces the child ahead of the final mirror round.
func (r *Registry) SetStatus(host, status string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetStatus", Host: host, Status: status}, nil)
		return err
	}

	res, err := r.sql.Exec(`UPDATE routes SET status = ? WHERE host = ?`, status, host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// SetCaps writes a route's operator-granted capability tokens — the ONLY
// writer of that column, and deliberately the only one: `carlos route
// -grant/-revoke` is the operator's door to a transfer of box authority, and
// a second writer (a full-row Upsert, a reconcile pass) would be a way for a
// grant to appear or disappear without anyone typing it. Callers pass the
// canonical value caps.Grant/caps.Revoke returned; this setter validates
// nothing, exactly as SetChannel validates no channel name — the rails live
// at the door.
//
// Same contract and same rationale as SetChannel: a narrow UPDATE (so it
// cannot clobber a concurrent SetVersions or SetNextDue), and an error when
// no row matched, because a human types a hostname into this one directly
// and a silent no-op on a typo would be worse than a refusal.
//
// The write is a compare-and-swap on the column's prior value, because the
// caller computed `to` FROM `from`: two operators racing this door (or one
// racing a slow shell) would otherwise merge stale reads — B's grant built on
// a read taken before A's revoke would silently re-assert the very token A
// just removed, on the column that hands out :25 and the box's IAM role. A
// lost race is a refusal that names the current value; the operator re-runs
// against reality.
func (r *Registry) SetCaps(host, from, to string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetCaps", Host: host, From: from, To: to}, nil)
		return err
	}

	res, err := r.sql.Exec(`UPDATE routes SET caps = ? WHERE host = ? AND caps = ?`, to, host, from)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		var cur string
		serr := r.sql.QueryRow(`SELECT caps FROM routes WHERE host = ?`, host).Scan(&cur)
		if serr == sql.ErrNoRows {
			return fmt.Errorf("no route for host %q", host)
		}
		if serr != nil {
			return serr
		}
		return fmt.Errorf("grants for %q changed underneath this command (now %q) — re-run it", host, capsOrNone(cur))
	}
	return nil
}

// capsOrNone renders a caps column value for an error message — "none"
// rather than "" so a lost race against a revoke reads as an answer.
func capsOrNone(caps string) string {
	if caps == "" {
		return "none"
	}
	return caps
}

// SetDBPath stamps a route's database path narrowly. The converge pass uses
// it to teach ops.FleetDBs (the box-wide litestream unit's config generator)
// where a `data.replicate: platform` app's databases actually live, instead
// of depending on an operator having remembered `-db` at `carlos add` — the
// manifest names the database FILES, the platform knows the state directory,
// and this is where the two meet.
//
// Errors on an unmatched host, like SetChannel and SetCaps: the caller is a
// pass that has just read the row, so "the row is gone" is a race worth
// reporting rather than swallowing.
//
// Compare-and-swap on the column's prior value, the same shape and the same
// reason as SetCaps: the caller decided to write `to` BECAUSE it read `from`
// — the whole stamp rule is "only when it differs, and only over a value this
// pass recognises". An hourly root pass racing an operator's `carlos add -db`
// (or a second box's pass against a shared registry) would otherwise overwrite
// a path nobody has looked at, and the column decides which file the box-wide
// replicator streams. A lost race is a refusal naming the current value, and
// the next pass re-decides against reality.
func (r *Registry) SetDBPath(host, from, to string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetDBPath", Host: host, From: from, To: to}, nil)
		return err
	}

	res, err := r.sql.Exec(`UPDATE routes SET db_path = ? WHERE host = ? AND db_path = ?`, to, host, from)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		var cur string
		serr := r.sql.QueryRow(`SELECT db_path FROM routes WHERE host = ?`, host).Scan(&cur)
		if serr == sql.ErrNoRows {
			return fmt.Errorf("no route for host %q", host)
		}
		if serr != nil {
			return serr
		}
		return fmt.Errorf("db_path for %q changed underneath this pass (now %q) — leaving it alone; the next pass decides against the new value", host, cur)
	}
	return nil
}

// ErrPathsChanged is SetInstancePaths's answer when the row's two path
// columns no longer hold the values the caller compared against.
var ErrPathsChanged = errors.New("registry: instance paths changed underneath this pass")

// SetInstancePaths moves a row between the flat and the nested layout
// (isolated-exec design §8.2 step 8): one UPDATE guarded by BOTH columns'
// current values, so a row that was migrated, reverted or re-added between
// the pass's read and its write is left exactly as it is, and the caller
// learns what it holds now. It is SetDBPath's sibling; the two columns are
// swapped together because a row with one flat path and one nested path is
// neither layout, and every consumer would refuse it.
func (r *Registry) SetInstancePaths(host, fromDB, fromSock, toDB, toSock string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetInstancePaths", Host: host, FromDB: fromDB, FromSock: fromSock, ToDB: toDB, ToSock: toSock}, nil)
		return err
	}

	res, err := r.sql.Exec(`UPDATE routes SET db_path = ?, socket = ? WHERE host = ? AND db_path = ? AND socket = ?`, toDB, toSock, host, fromDB, fromSock)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		var curDB, curSock string
		serr := r.sql.QueryRow(`SELECT db_path, socket FROM routes WHERE host = ?`, host).Scan(&curDB, &curSock)
		if serr == sql.ErrNoRows {
			return fmt.Errorf("no route for host %q", host)
		}
		if serr != nil {
			return serr
		}
		return fmt.Errorf("%w: %s now holds db %q socket %q", ErrPathsChanged, host, curDB, curSock)
	}
	return nil
}

// SetBacking stamps who owns a route's process: "" (an exec child of the
// agent) or BackingUnit (a carlos-app@<host> systemd unit). It is the door
// the app-capabilities migration (design §5b, §5c) needs and did not have:
// backing could be chosen at `carlos add` and never changed afterwards, so
// the reversible cutover — stop the hand-provisioned unit, copy the data,
// stamp the row, let the adopt pass converge it — had no way to stamp, and
// the reverse move no way to undo.
//
// Narrow UPDATE with an error on an unmatched host, like SetChannel and
// SetCaps: a human types the hostname into this one directly, and a silent
// no-op on a typo would be worse than a refusal. It validates nothing — the
// value rail and the row-ownership rails live at the door in cli.go, exactly
// as they do for the caps column.
//
// Deliberately NOT a compare-and-swap. Backing is not a set an operator
// merges into (SetCaps's case) and not a value an hourly pass rewrites
// (SetDBPath's): it is one operator, at one step of a documented migration,
// naming the value they want.
func (r *Registry) SetBacking(host, backing string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetBacking", Host: host, Backing: backing}, nil)
		return err
	}

	res, err := r.sql.Exec(`UPDATE routes SET backing = ? WHERE host = ?`, backing, host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// SetUnit stamps the systemd unit that owns a route's process — the door
// issue #110 needed and did not have: a console is an addr-backed route under
// an operator-run `console.service`, which the adopt pass's restart dispatch
// could reach by neither of its two arms (Bounce owns no such process, and
// there is no carlos-app@<host> unit to try-restart), so promote → adopt
// stamped a new Version behind a process still running the old build.
//
// Narrow UPDATE with an error on an unmatched host, the SetBacking contract
// and for the same reason: a human types the hostname into this one directly
// (`carlos route -unit console.service` against a live registry is how the
// production consoles get theirs), and a silent no-op on a typo would be
// worse than a refusal.
//
// Deliberately NOT a compare-and-swap, again following SetBacking: unit is
// not a set an operator merges into (SetCaps's case) and not a value a pass
// rewrites hourly (SetDBPath's) — it is one operator naming the unit that
// owns one process.
//
// It DOES validate, which SetBacking does not, and the difference is the
// point: backing has two legal values and the doors compare against both, so
// a bad value cannot survive the flag. A unit name is free text an operator
// types once, on the row whose whole purpose is to be handed to systemctl
// months later — a typo would be discovered as a restart that silently did
// nothing, i.e. as the very bug this column closes. "" is accepted, because
// clearing the binding is a legitimate write (see ValidUnitName).
func (r *Registry) SetUnit(host, unit string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetUnit", Host: host, Unit: unit}, nil)
		return err
	}

	if !ValidUnitName(unit) {
		return fmt.Errorf("unit %q: want a systemd service unit name (e.g. console.service) or \"\" to clear", unit)
	}
	res, err := r.sql.Exec(`UPDATE routes SET unit = ? WHERE host = ?`, unit, host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// SetReplication puts a route on a rung of the replication ladder, as a
// narrow UPDATE for the reason SetChannel and SetUnit are: a
// read-modify-write Upsert racing a deploy's SetVersions or the activator's
// SetNextDue would clobber those columns.
//
// It validates, like SetUnit and unlike SetBacking: the value reaches
// internal/activator's replicationSyncInterval, which answers an unknown
// class with litestream's default — so `--replication dayly` would be
// accepted, written, and discovered as a route that quietly never left the
// most expensive rung. That is the failure the column's whole existence is
// meant to end.
//
// "" is a legal value: it is the pre-ladder default a cleared route returns
// to. It reaches here only from `carlos route --replication -`, never from
// a full-row Upsert, which preserves the column instead.
func (r *Registry) SetReplication(host, class string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetReplication", Host: host, Class: class}, nil)
		return err
	}

	if !ValidReplication(class) {
		return fmt.Errorf("replication %q: want one of %s, or \"\" for the default", class, strings.Join(ReplicationClasses(), ", "))
	}
	res, err := r.sql.Exec(`UPDATE routes SET replication = ? WHERE host = ?`, class, host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// SetUpstreams repoints a route's multi-upstream set as a narrow UPDATE —
// same rationale as SetChannel/SetUnit: a read-modify-write Upsert racing a
// deploy's SetVersions or the activator's SetNextDue would clobber those
// columns, and this is how the provision reconcile pass converges them
// independently of everything else a full-row Upsert would touch. Unlike
// environment/caps/tunnel/provisioned/seed/unit/fleet_box, addrs and health
// are PLAIN overwrites in Upsert (registry.go:684), not preserve-when-empty
// — but provision.routeFor now carries Record.Addrs/Health onto every route
// it mints, and the reconcile pass's owned-route branch never calls Upsert
// (it converges through narrow setters, this one included), so a reconcile
// tick cannot blank what a prior SetUpstreams call wrote. The one other
// plain-overwrite path, `carlos add` re-run over an existing host, is closed
// at the CLI's own door (cli.go's runAdd, Task 8) rather than here: that verb
// now copies an existing route's Addrs/Health onto the literal it builds
// before calling Upsert, so a re-add cannot blank what this setter wrote.
//
// All THREE upstream columns move together, and that is the point: addr is
// the legacy single-upstream spelling of the same fact addrs carries, and
// AddrList folds addr in whenever addrs is empty. A setter that wrote addrs
// and left addr standing would leave the row saying two different things —
// the drift log firing every pass forever on a row nothing can converge, and,
// the moment the addrs set is cleared again, AddrList quietly folding the
// STALE addr back in and sending live traffic to a decommissioned upstream
// (found in review, 2026-08-12). Callers hand over the whole upstream shape:
// addr is the single legacy upstream ("" = none), addrs the already
// comma-joined set ("" = none), health the probe-config JSON verbatim ("" =
// unprobed). None is validated here — the rails live at the provision pass's
// own door (ValidateShape), which runs before a record ever reaches these
// columns, exactly as SetCaps's callers validate before calling it.
//
// Errors on an unmatched host, the SetChannel/SetUnit contract: a silent
// no-op on a host that does not exist would hide a reconcile pass writing to
// the wrong row.
func (r *Registry) SetUpstreams(host, addr, addrs, health string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetUpstreams", Host: host, Addr: addr, Addrs: addrs, Health: health}, nil)
		return err
	}

	res, err := r.sql.Exec(`UPDATE routes SET addr = ?, addrs = ?, health = ? WHERE host = ?`,
		addr, addrs, health, host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// SetSteering repoints a route's DNS steering opt-in ("" or "latency") as a
// narrow UPDATE — same rationale as SetUpstreams: a read-modify-write Upsert
// racing a deploy's SetVersions or the activator's SetNextDue would clobber
// other columns, and this is how the provision reconcile pass converges
// steering independently of everything else a full-row Upsert would touch.
// Unlike SetUpstreams this column is single — steering is one fact, not
// three columns describing one — so there is nothing here for a second
// argument to move together with.
//
// Value validation is NOT this door's job: the rail is
// provision.ValidSteering, run before a value ever reaches a record, the
// same discipline SetUpstreams follows for its own columns (the rails live
// at the provision pass's door, per SetCaps's rule).
//
// Errors on an unmatched host, the SetChannel/SetUpstreams contract: a
// reconcile pass that has just read the row must notice it vanishing
// underneath, not silently do nothing.
func (r *Registry) SetSteering(host, steering string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetSteering", Host: host, Steering: steering}, nil)
		return err
	}

	res, err := r.sql.Exec(`UPDATE routes SET steering = ? WHERE host = ?`, steering, host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// SetUpdateSettings repoints a route's effective update settings as a narrow
// UPDATE: reconcile converges it from the record every pass, in both
// directions, including down to "". Errors on an unmatched host, like
// SetSteering.
func (r *Registry) SetUpdateSettings(host, col string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetUpdateSettings", Host: host, UpdateSettings: col}, nil)
		return err
	}

	res, err := r.sql.Exec(`UPDATE routes SET update_settings = ? WHERE host = ?`, col, host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// SetBuildState stores the agent's copy of a route's build-state object as a
// narrow UPDATE: reconcile and the wake path refresh it, in both directions,
// including down to "". Errors on an unmatched host, like SetUpdateSettings.
func (r *Registry) SetBuildState(host, col string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetBuildState", Host: host, BuildState: col}, nil)
		return err
	}

	res, err := r.sql.Exec(`UPDATE routes SET build_state = ? WHERE host = ?`, col, host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// SetReplicas repoints a route's replica placement set (#162) as a narrow
// UPDATE: the provision pass converges this column every tick from the
// record, and the record's value is authoritative in BOTH directions —
// including down to "". That asymmetry with the CASE-preserved columns
// above is the whole point: a set that shrank (a replica deleted in a
// region the account left) must actually shrink on the row, or the edges
// file goes on publishing a replica placement the account no longer has —
// a residence lie, not a cosmetic one. Errors on an unmatched host,
// SetChannel's contract: the reconciler must notice the row vanishing
// underneath rather than silently do nothing.
func (r *Registry) SetReplicas(host, replicas string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetReplicas", Host: host, Replicas: replicas}, nil)
		return err
	}

	res, err := r.sql.Exec(`UPDATE routes SET replicas = ? WHERE host = ?`, replicas, host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// SetReplica stamps or clears a route's replica mark (#162) as a narrow
// UPDATE — the provision pass converges it from Record.Role. The mark is
// never an operator verb (a role flip is a data-plane event; delete and
// re-create is the supported transition, Record.Role's doc), so unlike
// SetChannel there is no human-typing-hostnames caller to keep the
// error-on-unmatched contract for — but it is kept anyway, SetChannel's
// contract verbatim, because a reconcile pass that has just read the row
// must notice it vanishing underneath, not silently do nothing.
func (r *Registry) SetReplica(host string, replica bool) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetReplica", Host: host, Replica: replica}, nil)
		return err
	}

	res, err := r.sql.Exec(`UPDATE routes SET replica = ? WHERE host = ?`, b2iDB(replica), host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// b2iDB is Upsert's b2i for the narrow setters, which sit outside that
// function's closure.
func b2iDB(b bool) int {
	if b {
		return 1
	}
	return 0
}

// SetGate stamps or clears a route's password gate — the narrow UPDATE the
// provision converge uses (never a full-row Upsert, which preserves a set
// gate by design and so could not clear one). Errors on an unmatched host,
// SetBacking's contract.
func (r *Registry) SetGate(host, hash string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetGate", Host: host, Hash: hash}, nil)
		return err
	}

	res, err := r.sql.Exec(`UPDATE routes SET gate = ? WHERE host = ?`, hash, host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// SetChannels repoints every host in hosts to channel as ONE transaction —
// every row moves or none do. `carlos route -app` uses this for its bulk
// form: the caller runs every route's safety-rail check to completion FIRST
// (see cli.go's runRoute), and only once every route has cleared it does it
// call this to write. Without that, a check-then-write-per-route loop can
// leave an app split across two channels the moment one route mid-batch
// fails the rail (or the process dies mid-loop) — an app that is meant to
// move as one unit (Tito's eighteen routes off canary/platform-adoption)
// must never be left half-moved. Same "error on an unmatched host" contract
// as SetChannel, but any single failure rolls the whole batch back.
func (r *Registry) SetChannels(hosts []string, channel string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetChannels", Hosts: hosts, Channel: channel}, nil)
		return err
	}

	if len(hosts) == 0 {
		return nil
	}
	tx, err := r.sql.Begin()
	if err != nil {
		return err
	}
	stmt, err := tx.Prepare(`UPDATE routes SET channel = ? WHERE host = ?`)
	if err != nil {
		tx.Rollback()
		return err
	}
	for _, h := range hosts {
		res, err := stmt.Exec(channel, h)
		if err != nil {
			stmt.Close()
			tx.Rollback()
			return err
		}
		n, err := res.RowsAffected()
		if err != nil {
			stmt.Close()
			tx.Rollback()
			return err
		}
		if n == 0 {
			stmt.Close()
			tx.Rollback()
			return fmt.Errorf("no route for host %q", h)
		}
	}
	stmt.Close()
	return tx.Commit()
}

// SetEnvironment repoints a route's named-environment binding as a narrow
// UPDATE — same rationale and same "error on an unmatched host" contract as
// SetChannel (a human types a hostname into `carlos route -environment`
// directly, and a silent no-op on a typo would be worse than a refusal).
// env == "" clears the binding back to the app's default bundle alone; the
// CLI's "-environment -" sentinel resolves to that before calling here, so
// this function itself has no notion of "clear" beyond writing "".
func (r *Registry) SetEnvironment(host, env string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetEnvironment", Host: host, Env: env}, nil)
		return err
	}

	res, err := r.sql.Exec(`UPDATE routes SET environment = ? WHERE host = ?`, env, host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// SetTunnel repoints a route's reverse-channel binding as a narrow UPDATE —
// same rationale and same "error on an unmatched host" contract as
// SetEnvironment (a human types a hostname into `carlos route -tunnel`
// directly, and a silent no-op on a typo would be worse than a refusal).
// tunnel == "" clears the binding back to a Socket/Addr-routed upstream; the
// CLI's "-tunnel -" sentinel resolves to that before calling here, so this
// function itself has no notion of "clear" beyond writing "".
func (r *Registry) SetTunnel(host, tunnel string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetTunnel", Host: host, Tunnel: tunnel}, nil)
		return err
	}

	res, err := r.sql.Exec(`UPDATE routes SET tunnel = ? WHERE host = ?`, tunnel, host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// SetFleetBox stamps the label of the fleet box CURRENTLY serving this
// route as a narrow UPDATE — same rationale and same "error on an unmatched
// host" contract as SetTunnel. label == "" clears the binding; this is a
// placement pass's door, distinct from SetPlacement (which writes the
// placement TARGET, not the box currently fulfilling it).
func (r *Registry) SetFleetBox(host, label string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetFleetBox", Host: host, Label: label}, nil)
		return err
	}

	res, err := r.sql.Exec(`UPDATE routes SET fleet_box = ? WHERE host = ?`, label, host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// SetPlacement repoints a route's placement TARGET — stored in the Region
// column, which already carries a route's residency constraint and, as of
// this task, doubles as where a fleet-bound route wants to run (see
// FleetPlacement/PlacementTarget, fleets.go, for the grammar that
// distinguishes the two). target == "" clears the binding. Any other value
// must parse as "fleet/<name>" via FleetPlacement — a bare region code like
// "eu" is a FUTURE placement form this v1 does not accept here (readers use
// FleetPlacement to tell the two apart; it simply answers ("", false) for a
// bare code, same as for anything else this setter refuses).
//
// Same "error on an unmatched host" contract as SetTunnel/SetFleetBox: a
// human or a placement pass typing a hostname here should get a refusal on a
// typo, not a silent no-op.
func (r *Registry) SetPlacement(host, target string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetPlacement", Host: host, Target: target}, nil)
		return err
	}

	if target != "" {
		if _, ok := FleetPlacement(target); !ok {
			return fmt.Errorf("invalid placement target %q (want \"\" or \"fleet/<name>\")", target)
		}
	}
	res, err := r.sql.Exec(`UPDATE routes SET region = ? WHERE host = ?`, target, host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// SetEnvironments is SetEnvironment's bulk sibling, mirroring SetChannels:
// one transaction across every host in hosts, so `carlos route -app
// -environment` can never leave an app split across two environments the
// moment one route mid-batch fails — the caller (cli.go's runRoute) runs
// every route's existence-rail check to completion FIRST, and only once
// every route has cleared it does it call this to write.
func (r *Registry) SetEnvironments(hosts []string, env string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetEnvironments", Hosts: hosts, Env: env}, nil)
		return err
	}

	if len(hosts) == 0 {
		return nil
	}
	tx, err := r.sql.Begin()
	if err != nil {
		return err
	}
	stmt, err := tx.Prepare(`UPDATE routes SET environment = ? WHERE host = ?`)
	if err != nil {
		tx.Rollback()
		return err
	}
	for _, h := range hosts {
		res, err := stmt.Exec(env, h)
		if err != nil {
			stmt.Close()
			tx.Rollback()
			return err
		}
		n, err := res.RowsAffected()
		if err != nil {
			stmt.Close()
			tx.Rollback()
			return err
		}
		if n == 0 {
			stmt.Close()
			tx.Rollback()
			return fmt.Errorf("no route for host %q", h)
		}
	}
	stmt.Close()
	return tx.Commit()
}

// SetChannelsAndEnvironments is SetChannels and SetEnvironments' combined
// sibling for `carlos route -channel X -environment Y`: BOTH columns, for
// every host in hosts, in ONE transaction — so a combined move can never
// commit the channel repoint and then fail the environment repoint (or vice
// versa), leaving a route split between the two writes the caller asked for
// as a single atomic change. Same all-or-nothing contract as its two
// single-column siblings; the caller (runRoute) has already run both rails'
// checks to completion before this is called.
func (r *Registry) SetChannelsAndEnvironments(hosts []string, channel, env string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetChannelsAndEnvironments", Hosts: hosts, Channel: channel, Env: env}, nil)
		return err
	}

	if len(hosts) == 0 {
		return nil
	}
	tx, err := r.sql.Begin()
	if err != nil {
		return err
	}
	stmt, err := tx.Prepare(`UPDATE routes SET channel = ?, environment = ? WHERE host = ?`)
	if err != nil {
		tx.Rollback()
		return err
	}
	for _, h := range hosts {
		res, err := stmt.Exec(channel, env, h)
		if err != nil {
			stmt.Close()
			tx.Rollback()
			return err
		}
		n, err := res.RowsAffected()
		if err != nil {
			stmt.Close()
			tx.Rollback()
			return err
		}
		if n == 0 {
			stmt.Close()
			tx.Rollback()
			return fmt.Errorf("no route for host %q", h)
		}
	}
	stmt.Close()
	return tx.Commit()
}

// SetDNSStatus stamps a route's DNS health verdict + check time, narrowly
// (same rationale as SetAccount; this lands every few minutes on every host).
func (r *Registry) SetDNSStatus(host, status string, checkedAt int64) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetDNSStatus", Host: host, Status: status, CheckedAt: checkedAt}, nil)
		return err
	}

	_, err := r.sql.Exec(`UPDATE routes SET dns_status = ?, dns_checked_at = ? WHERE host = ?`, status, checkedAt, host)
	return err
}

// SetCertExpiresAt stamps a route's cached-cert NotAfter, narrowly. 0 =
// unknown (no cert in the cache, or one we couldn't parse).
func (r *Registry) SetCertExpiresAt(host string, at int64) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetCertExpiresAt", Host: host, At: at}, nil)
		return err
	}

	_, err := r.sql.Exec(`UPDATE routes SET cert_expires_at = ? WHERE host = ?`, at, host)
	return err
}

// SetVersions records a host's current + previous version as a narrow UPDATE,
// so a slow deploy's read-modify-write can never clobber concurrent writes to
// other columns.
func (r *Registry) SetVersions(host, ver, prev string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetVersions", Host: host, Ver: ver, Prev: prev}, nil)
		return err
	}

	_, err := r.sql.Exec(`UPDATE routes SET version = ?, prev_version = ? WHERE host = ?`, ver, prev, host)
	return err
}

// GoodBuild is the good-build triple of zero-downtime spec §8.5.
type GoodBuild struct{ Version, Digest, Demand string }

// Good returns the route's good-build triple.
func (t Route) Good() GoodBuild {
	return GoodBuild{Version: t.GoodVersion, Digest: t.GoodDigest, Demand: t.GoodDemand}
}

// ErrGoodBuildChanged is SetGoodBuild's answer when the row no longer holds
// the triple the caller expected to replace.
var ErrGoodBuildChanged = errors.New("registry: good build changed underneath this write")

// SetGoodBuild replaces host's good build with next, but only if the row
// still holds expect (spec §8.5: "written with an expected-current
// condition"). Two writers that each saw a different generation become
// current cannot both win; the loser learns what the row holds now.
func (r *Registry) SetGoodBuild(host string, expect, next GoodBuild) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetGoodBuild", Host: host, ExpectGood: expect, NextGood: next}, nil)
		return err
	}

	res, err := r.sql.Exec(`UPDATE routes SET good_version = ?, good_digest = ?, good_demand = ?
WHERE host = ? AND good_version = ? AND good_digest = ? AND good_demand = ?`,
		next.Version, next.Digest, next.Demand, host, expect.Version, expect.Digest, expect.Demand)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 1 {
		return nil
	}
	var cur GoodBuild
	serr := r.sql.QueryRow(`SELECT good_version, good_digest, good_demand FROM routes WHERE host = ?`, host).
		Scan(&cur.Version, &cur.Digest, &cur.Demand)
	if serr == sql.ErrNoRows {
		return fmt.Errorf("no route for host %q", host)
	}
	if serr != nil {
		return serr
	}
	return fmt.Errorf("%w: %s now holds %+v", ErrGoodBuildChanged, host, cur)
}

// SetServingVersion records the build of the generation that just became
// current (spec §3), as a narrow UPDATE.
func (r *Registry) SetServingVersion(host, ver string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetServingVersion", Host: host, Ver: ver}, nil)
		return err
	}

	_, err := r.sql.Exec(`UPDATE routes SET serving_version = ? WHERE host = ?`, ver, host)
	return err
}

// SetHibernate flips the per-host hibernation opt-in as a narrow UPDATE.
// Turning hibernation OFF also clears `replication`, and that is the point
// of doing it here rather than in each caller. The replication class is read
// in exactly one place — the activator's wake path — and box-wide
// /etc/litestream.yml deliberately covers every NON-hibernating database at
// its own interval (internal/ops/litestream.go, ops.FleetDBsWithSkipped).
// So the moment a route stops hibernating, a stored `daily` describes
// nothing that happens: `carlos routes` would keep printing it while
// litestream streamed the database every second, which is the lying column
// the `carlos route --replication` rail (cli.go's replicationRail) exists to
// prevent, merely relocated to a later moment.
//
// Both doors that turn hibernation off come through here — `carlos route
// --hibernate=false` (cli.go's runRouteHibernate) and the provisioning
// converge, which flips the column from the instance record on every pass —
// and any door added later will too. The third writer, a full-row Upsert
// carrying hibernate=0 (`carlos add` with no --hibernate), is covered by the
// matching arm of that statement's ON CONFLICT clause. Between them the
// invariant is total: a non-empty `replication` implies a hibernating row.
//
// Clearing rather than preserving-for-later is deliberate. A preserved class
// is invisible state that changes behaviour weeks after it was typed, and
// the operator turning hibernation back on is already at a prompt where
// naming the rung again costs one command; runRouteHibernate prints what was
// dropped so it is a fact they are told, not one they discover.
func (r *Registry) SetHibernate(host string, on bool) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetHibernate", Host: host, On: on}, nil)
		return err
	}

	if !on {
		_, err := r.sql.Exec(`UPDATE routes SET hibernate = 0, replication = '' WHERE host = ?`, host)
		return err
	}
	_, err := r.sql.Exec(`UPDATE routes SET hibernate = 1 WHERE host = ?`, host)
	return err
}

// SetNextDue records the earliest server-side scheduled work for a host, so
// the activator can wake a sleeping instance for its own schedule. 0 = none.
func (r *Registry) SetNextDue(host string, due int64) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetNextDue", Host: host, Due: due}, nil)
		return err
	}

	_, err := r.sql.Exec(`UPDATE routes SET next_due = ? WHERE host = ?`, due, host)
	return err
}

// ClearNextDueIf CONSUMES a fired due time: it clears routes.next_due for
// host, but only while the row still holds due — the exact value that fired.
//
// A fired timer is one-shot, and this is what makes it so. The instance path
// gets that for free: an instance re-records next_due from its own probe on
// every stop, so a passed due time is overwritten the moment the host sleeps
// again. A WORKER's stop deliberately writes nothing (it has no socket to
// probe, and a failed probe records 0 — erasing the schedule that is a
// sleeping sidecar's only way back up), so without this the row keeps its
// passed value and DueInstances returns it on EVERY sweep: wake, restore,
// idle, park, re-wake, forever, with nothing in the logs that reads as a
// failure.
//
// Conditional, not a bare clear, because the child that this wake just
// spawned may already have posted its NEXT due time over the agent socket
// (SetWorkerNextDue) before the wake resolved. Clearing unconditionally would
// erase a schedule that was set after the one being consumed; the WHERE
// next_due = ? is what keeps consumption from eating it. A row that moved
// under us is simply left alone.
func (r *Registry) ClearNextDueIf(host string, due int64) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "ClearNextDueIf", Host: host, Due: due}, nil)
		return err
	}

	_, err := r.sql.Exec(`UPDATE routes SET next_due = 0 WHERE host = ? AND next_due = ?`, host, due)
	return err
}

// AlwaysOnWorkers returns every non-hibernating sidecar route in a runnable
// status — design §7.2's "the record exists ⇒ the process runs", asked as a
// query so the activator's sweep can start one that appeared AFTER the
// agent's boot pass ran (the boot pass runs once, at arming, and never
// again).
//
// Status is filtered exactly as the due-time sweep and the boot pass filter
// it (host's workersToStart): only "ready", or the legacy "" some pre-status
// rows carry — a row still provisioning, or draining, must not be spawned.
func (r *Registry) AlwaysOnWorkers() ([]Route, error) {
	if r.remote != nil {
		out, err := r.remote.call(nil, remoteRequest{Method: "AlwaysOnWorkers"}, nil)
		return out.Routes, err
	}

	rows, err := r.sql.Query(`SELECT ` + routeCols + ` FROM routes
WHERE kind = 'sidecar' AND hibernate = 0 AND (status = '' OR status = 'ready') ORDER BY host`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Route
	for rows.Next() {
		t, err := scanRoute(rows.Scan)
		if err != nil {
			return nil, err
		}
		out = append(out, t)
	}
	return out, rows.Err()
}

// SetSeed stamps a route's first-boot init argv as a narrow UPDATE — same
// rationale as SetAccount (a read-modify-write Upsert racing a deploy's
// SetVersions or the activator's SetNextDue would clobber it). Unlike
// SetAccount it errors when no row matched (the SetChannel contract): the
// instance-provisioning reconcile pass re-stamps this every tick and must
// notice a row that vanished under it rather than silently doing nothing.
func (r *Registry) SetSeed(host, seed string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetSeed", Host: host, Seed: seed}, nil)
		return err
	}

	res, err := r.sql.Exec(`UPDATE routes SET seed = ? WHERE host = ?`, seed, host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// SetArgs repoints a sidecar's argv — the narrow UPDATE the sidecar
// reconcile pass uses when the app's manifest changed.
func (r *Registry) SetArgs(host, args string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetArgs", Host: host, Args: args}, nil)
		return err
	}

	_, err := r.sql.Exec(`UPDATE routes SET args = ? WHERE host = ?`, args, host)
	return err
}

// SetWorkerLimits repoints a sidecar's idle window and run ceiling, both in
// seconds, 0 meaning "use the platform default". Same narrow-UPDATE shape
// and same reason as SetArgs: the manifest is the truth, this is the copy
// the sweeper reads on every tick.
func (r *Registry) SetWorkerLimits(host string, idle, maxRun int) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetWorkerLimits", Host: host, Idle: idle, MaxRun: maxRun}, nil)
		return err
	}

	_, err := r.sql.Exec(
		`UPDATE routes SET idle_seconds = ?, max_run_seconds = ? WHERE host = ?`,
		idle, maxRun, host)
	return err
}

// SetProvisioned stamps (or clears) the flag that marks a route as the
// instance-provisioning reconcile pass's own — a narrow UPDATE, same
// rationale as SetSeed. It errors when no row matched, the SetChannel
// contract: the pass must notice a row that vanished under it rather than
// silently doing nothing.
//
// This flag is an authority transfer, not a label: it is the condition that
// GRANTS the reconcile pass's removal phase authority over a row (that loop
// also gates on region/pool scope and on the record being gone). Only that pass's
// adoption step (provision.Reconcile, design §4.2) should ever set it, and
// it does so only after matching the row's shape against the record.
func (r *Registry) SetProvisioned(host string, on bool) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetProvisioned", Host: host, On: on}, nil)
		return err
	}

	v := 0
	if on {
		v = 1
	}
	res, err := r.sql.Exec(`UPDATE routes SET provisioned = ? WHERE host = ?`, v, host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// SetStatusNamed flips a route's public-status-page naming opt-in as a narrow
// UPDATE, same rationale as SetSeed (a read-modify-write Upsert racing a
// deploy's SetVersions or the activator's SetNextDue would clobber those). It
// errors when no row matched, the SetChannel contract: the provisioning
// reconcile pass converges this column from the instance record and must
// notice a row that vanished under it rather than silently doing nothing.
func (r *Registry) SetStatusNamed(host string, on bool) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetStatusNamed", Host: host, On: on}, nil)
		return err
	}

	v := 0
	if on {
		v = 1
	}
	res, err := r.sql.Exec(`UPDATE routes SET status_named = ? WHERE host = ?`, v, host)
	if err != nil {
		return err
	}
	n, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if n == 0 {
		return fmt.Errorf("no route for host %q", host)
	}
	return nil
}

// DueInstances returns hibernatable instance AND sidecar routes whose
// next_due has arrived (0 means no schedule and never matches). Sidecars
// are included because the due-time sweep is their ONLY timer wake source:
// nothing can dial a sidecar, so no request will ever wake one.
func (r *Registry) DueInstances(now int64) ([]Route, error) {
	if r.remote != nil {
		out, err := r.remote.call(nil, remoteRequest{Method: "DueInstances", Now: now}, nil)
		return out.Routes, err
	}

	rows, err := r.sql.Query(`SELECT `+routeCols+` FROM routes
WHERE kind IN ('instance','sidecar') AND next_due > 0 AND next_due <= ? ORDER BY host`, now)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Route
	for rows.Next() {
		t, err := scanRoute(rows.Scan)
		if err != nil {
			return nil, err
		}
		out = append(out, t)
	}
	return out, rows.Err()
}

// RouteAlias is one derived row of the route_aliases table: an extra
// hostname that resolves to an existing route's host, materialized from a
// domains.Claim by a later reconcile pass (this package owns the storage
// shape only — nothing here decides which claims win). Alias is the extra
// hostname being attached; Host is the route it points at. DNSStatus,
// DNSCheckedAt and CertExpiresAt are box-stamped health state, same shape as
// the identically-named columns on routes, and are deliberately excluded
// from Fingerprint (see there) so a stamping pass alone never forces an edge
// reload.
type RouteAlias struct {
	Alias         string
	Host          string
	Account       string
	App           string
	DNSStatus     string
	DNSCheckedAt  int64
	CertExpiresAt int64
	CreatedAt     int64
}

// ListAliases returns every route alias, ordered by alias.
func (r *Registry) ListAliases() ([]RouteAlias, error) {
	if r.remote != nil {
		out, err := r.remote.call(nil, remoteRequest{Method: "ListAliases"}, nil)
		return out.Aliases, err
	}

	rows, err := r.sql.Query(`SELECT alias, host, account, app, dns_status,
dns_checked_at, cert_expires_at, created_at FROM route_aliases ORDER BY alias`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []RouteAlias
	for rows.Next() {
		var a RouteAlias
		if err := rows.Scan(&a.Alias, &a.Host, &a.Account, &a.App, &a.DNSStatus,
			&a.DNSCheckedAt, &a.CertExpiresAt, &a.CreatedAt); err != nil {
			return nil, err
		}
		out = append(out, a)
	}
	return out, rows.Err()
}

// UpsertAlias inserts or updates an alias row by its hostname. Only host,
// account and app are overwritten on conflict — the DNS/cert stamps and
// created_at are reconcile-pass- and box-stamp-owned state that a repoint
// must not reset out from under the passes that maintain them (the same
// preserved-column shape Upsert uses for routes, applied here by simply
// leaving those columns out of the SET list rather than a CASE guard, since
// every writer of this table already knows it must not carry stamps).
func (r *Registry) UpsertAlias(a RouteAlias) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "UpsertAlias", A: a}, nil)
		return err
	}

	_, err := r.sql.Exec(`
INSERT INTO route_aliases (alias, host, account, app, dns_status, dns_checked_at, cert_expires_at, created_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(alias) DO UPDATE SET host=excluded.host, account=excluded.account, app=excluded.app`,
		a.Alias, a.Host, a.Account, a.App, a.DNSStatus, a.DNSCheckedAt, a.CertExpiresAt, a.CreatedAt)
	return err
}

// DeleteAlias removes an alias by its hostname. Idempotent: an alias already
// gone is a success, same contract as Delete on routes.
func (r *Registry) DeleteAlias(alias string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "DeleteAlias", Alias: alias}, nil)
		return err
	}

	_, err := r.sql.Exec(`DELETE FROM route_aliases WHERE alias = ?`, alias)
	return err
}

// SetAliasDNSStatus stamps an alias's DNS health verdict + check time,
// narrowly (same rationale as SetDNSStatus on routes: it lands on its own
// cadence and must not race a concurrent UpsertAlias repoint).
func (r *Registry) SetAliasDNSStatus(alias, status string, at int64) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetAliasDNSStatus", Alias: alias, Status: status, At: at}, nil)
		return err
	}

	_, err := r.sql.Exec(`UPDATE route_aliases SET dns_status = ?, dns_checked_at = ? WHERE alias = ?`, status, at, alias)
	return err
}

// SetAliasCertExpiresAt stamps an alias's cached-cert NotAfter, narrowly.
// 0 = unknown (same rationale as SetCertExpiresAt on routes).
func (r *Registry) SetAliasCertExpiresAt(alias string, at int64) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SetAliasCertExpiresAt", Alias: alias, At: at}, nil)
		return err
	}

	_, err := r.sql.Exec(`UPDATE route_aliases SET cert_expires_at = ? WHERE alias = ?`, at, alias)
	return err
}

// WildcardDomain is one derived row of the wildcard_domains table: a
// customer domain whose claim carries wildcard:true and whose primary
// route lives on this box — i.e. a delegated wildcard-cert parent the
// edge should obtain *.host for. Wholly pass-owned, like route_aliases:
// every row traces to a claim, so removal needs no ownership flag.
type WildcardDomain struct {
	Host      string
	Account   string
	App       string
	CreatedAt int64
}

// ListWildcardDomains returns every wildcard domain, ordered by host.
func (r *Registry) ListWildcardDomains() ([]WildcardDomain, error) {
	if r.remote != nil {
		out, err := r.remote.call(nil, remoteRequest{Method: "ListWildcardDomains"}, nil)
		return out.Wildcards, err
	}

	rows, err := r.sql.Query(`SELECT host, account, app, created_at FROM wildcard_domains ORDER BY host`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []WildcardDomain
	for rows.Next() {
		var w WildcardDomain
		if err := rows.Scan(&w.Host, &w.Account, &w.App, &w.CreatedAt); err != nil {
			return nil, err
		}
		out = append(out, w)
	}
	return out, rows.Err()
}

// UpsertWildcardDomain inserts or updates a row by host.
func (r *Registry) UpsertWildcardDomain(w WildcardDomain) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "UpsertWildcardDomain", W: w}, nil)
		return err
	}

	_, err := r.sql.Exec(`
INSERT INTO wildcard_domains (host, account, app, created_at)
VALUES (?, ?, ?, ?)
ON CONFLICT(host) DO UPDATE SET account=excluded.account, app=excluded.app, created_at=excluded.created_at`,
		w.Host, w.Account, w.App, w.CreatedAt)
	return err
}

// DeleteWildcardDomain removes host's row. Idempotent: a host already gone
// is a success, same contract as DeleteAlias.
func (r *Registry) DeleteWildcardDomain(host string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "DeleteWildcardDomain", Host: host}, nil)
		return err
	}

	_, err := r.sql.Exec(`DELETE FROM wildcard_domains WHERE host = ?`, host)
	return err
}

// RoutePath is one derived row of the route_paths table: a leading URL path
// segment on Host that is served by the route at Target instead of by Host's
// own route. Wholly pass-owned, like route_aliases and wildcard_domains:
// every row traces to a claim's Paths entry, so removal needs no ownership
// flag.
//
// A path row deliberately carries NO upstream, channel or environment of its
// own. It is a pointer; everything about how the traffic is served comes from
// Target's ordinary route row. That is what makes pointing a subset of
// prefixes at a second route a working canary rather than a special case.
//
// Prefix is a bare segment — "paulca", never "/paulca".
type RoutePath struct {
	Host    string
	Prefix  string
	Target  string
	Account string
	App     string
}

// ListPaths returns every path route, ordered by host then prefix.
func (r *Registry) ListPaths() ([]RoutePath, error) {
	if r.remote != nil {
		out, err := r.remote.call(nil, remoteRequest{Method: "ListPaths"}, nil)
		return out.Paths, err
	}

	rows, err := r.sql.Query(`SELECT host, prefix, target, account, app
FROM route_paths ORDER BY host, prefix`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []RoutePath
	for rows.Next() {
		var p RoutePath
		if err := rows.Scan(&p.Host, &p.Prefix, &p.Target, &p.Account, &p.App); err != nil {
			return nil, err
		}
		out = append(out, p)
	}
	return out, rows.Err()
}

// UpsertPath inserts or repoints one path route by its (host, prefix) key.
// Unlike UpsertAlias there are no stamp columns to preserve — nothing on this
// table is maintained on a separate cadence — so every column is overwritten.
func (r *Registry) UpsertPath(p RoutePath) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "UpsertPath", P: p}, nil)
		return err
	}

	_, err := r.sql.Exec(`
INSERT INTO route_paths (host, prefix, target, account, app)
VALUES (?, ?, ?, ?, ?)
ON CONFLICT(host, prefix) DO UPDATE SET
  target=excluded.target, account=excluded.account, app=excluded.app`,
		p.Host, p.Prefix, p.Target, p.Account, p.App)
	return err
}

// DeletePath removes one path route. Idempotent: a row already gone is a
// success, the same contract as DeleteAlias — the reconcile pass deletes
// unconditionally from its own view of what is no longer desired.
func (r *Registry) DeletePath(host, prefix string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "DeletePath", Host: host, Prefix: prefix}, nil)
		return err
	}

	_, err := r.sql.Exec(`DELETE FROM route_paths WHERE host = ? AND prefix = ?`, host, prefix)
	return err
}

// Fingerprint summarizes the routes, fleets, fleet_boxes, route_aliases,
// wildcard_domains and route_paths tables over a fresh connection, for the
// edge's cheap
// change poll. It is content-accurate: it hashes the actual VALUES of the
// reload-relevant columns, not a length-weighted aggregate, so two
// DIFFERENT values of the SAME length
// (channel canary/1 -> canary/2, a git short-sha version bump, a rotated
// sha256 token hash, a failover restamp box-a -> box-b) change the result
// exactly like any other write. `grants` stays deliberately excluded
// (registry.go:93's rule: grants change unit shape, not routing), and so does
// `unit` (it changes who restarts the process, not what the edge serves). The
// route count is folded into the result so an empty table and a never-opened
// one still differ from a populated one.
// Still a fresh connection per poll — it must see other processes' commits
// (same WAL-snapshot quirk as List).
//
// Closes #15: the edge's X-Carlos-Version response header reads Version off
// the route the fingerprint gated in, and git short shas are same-length by
// construction — the old length-weighted aggregate could not see a version
// bump land, so the header would serve stale until an unrelated column
// happened to change too.
//
// db_path is hashed too (issue #69): a re-add that only re-points a route's
// database must reach a running edge, or the activator keeps waking the host
// on the stale cached path.
//
// `addrs` and `health` are hashed too (issue #152, multi-upstream routing): a
// SetUpstreams call that resizes or reshuffles a route's upstream set, or
// changes its probe config, is exactly as reload-relevant as an addr rewrite
// — an edge that never noticed would keep dispatching to a dropped upstream
// or never start probing a newly-added one.
//
// `region` and `fleet_box` are hashed for the customer-fleets placement path,
// and the two-box e2e (internal/edge/fleet_e2e_test.go) is what proved they
// have to be: a failover restamp (ReconcileFleetBindings -> SetFleetBox,
// box-a -> box-b) left the old aggregate byte-identical and the edge kept
// dispatching to the box that had died — rule 1 pointing at a corpse the
// moment it re-dialed, with no operator around to SIGHUP, because a restamp
// is UNATTENDED where an operator's `carlos route` edit is not. Note that a
// LENGTH-based fold would not have fixed it either: fleet box labels are
// overwhelmingly the same length as each other ("pi-1"/"pi-2",
// "box-a"/"box-b"), which is precisely the blind spot hashing the values
// closes.
//
// `steering` (multi-region ingress design Part 4) is deliberately NOT hashed
// here — see Route.Steering's own doc comment for the exclusion, stated in
// the same place `unit`'s exclusion is (registry.go:93's rule, restated):
// steering changes what DNS answers, not what the edge serves, and the
// converger that acts on it is a host housekeeper duty reading the registry
// directly, never gated on this poll (plan amendment b485ec5, re-ruling this
// task's own first pass, which hashed it here on the wrong premise that
// SnapshotFingerprint's routeCols-derived coverage extended to this function
// too — it does not; this function's column list is hand-curated).
//
// The fleets and fleet_boxes tables are hashed into the SAME digest, each
// under its own section marker so a value can never migrate across sections
// and cancel a change out. That is what lets the edge's Watch poll
// (edge.go:830) notice a fleet mutation — a new box, a detach, a token
// rotation — and reload, exactly as it notices a route write. Both tables'
// `rev` counters (bumped by UpsertFleet, AddFleetBox, SetFleetBoxToken,
// SetFleetBoxStatus) are hashed alongside their columns: with content
// hashing they are belt-and-braces rather than load-bearing, but they keep
// the guarantee true for any future writer that mutates a row into a
// byte-identical shape.
//
// wildcard_domains (Task 4 carry from Task 2's review) is hashed for the
// same reload-latency reason as fleets/fleet_boxes above: the claim-derived
// delegated wildcard parents (internal/domains' ReconcileAliases, Task 3)
// land here, and wildcard.go's refresh() runs off Edge.OnReload — which
// only fires when Watch's 2s Fingerprint poll actually moves. Without this
// section a new delegated parent (or one going away) would be invisible to
// that poll and wait for the wildcard manager's own 12h tick instead of the
// usual few-second bound every other route/alias mutation gets. All four
// columns are hashed — there is no cadence-stamped column on this table to
// exclude, unlike route_aliases' dns_status/cert_expires_at.
//
// route_paths is hashed for the same reload-latency reason, and it is the
// section most worth stating explicitly: a path flip is the ONLY routing
// change in the system whose whole observable effect is in this table. A
// missing section here would not degrade a flip, it would make flips do
// nothing at all — reconciled correctly, stored correctly, never served —
// until some unrelated route write happened to move the digest.
//
// The ORDER BY clauses are what make the digest deterministic across polls;
// a hypothetical reordering would only cost a redundant reload (idempotent),
// never a missed change — the failure mode worth engineering against is the
// false NEGATIVE.
func (r *Registry) Fingerprint() (string, error) {
	if r.remote != nil {
		out, err := r.remote.call(nil, remoteRequest{Method: "Fingerprint"}, nil)
		return out.Text, err
	}

	db, err := sql.Open("sqlite", r.path)
	if err != nil {
		return "", err
	}
	defer db.Close()
	if _, err := db.Exec("PRAGMA busy_timeout=5000"); err != nil {
		return "", err
	}
	rows, err := db.Query(`
SELECT rowid, status, socket, addr, version, hibernate, channel, backing, tunnel,
       db_path, region, fleet_box, addrs, health, gate, hold_op, import_gate,
       claim_incarnation, claim_stamp, claim_owner, claim_row_id
FROM routes ORDER BY rowid`)
	if err != nil {
		return "", err
	}
	defer rows.Close()
	h := sha256.New()
	n := 0
	for rows.Next() {
		var rowid, hibernate int64
		var claim ClaimOwnership
		var status, socket, addr, version, channel, backing, tunnel, dbPath, region, fleetBox, addrs, health, gate, holdOp, importGate string
		if err := rows.Scan(&rowid, &status, &socket, &addr, &version, &hibernate,
			&channel, &backing, &tunnel, &dbPath, &region, &fleetBox, &addrs, &health, &gate, &holdOp, &importGate,
			&claim.ClaimIncarnation, &claim.ClaimStamp, &claim.ClaimOwner, &claim.ClaimRowID); err != nil {
			return "", err
		}
		fmt.Fprintf(h, "%d\x1f%s\x1f%s\x1f%s\x1f%s\x1f%d\x1f%s\x1f%s\x1f%s\x1f%s\x1f%s\x1f%s\x1f%s\x1f%s\x1f%s",
			rowid, status, socket, addr, version, hibernate, channel, backing, tunnel,
			dbPath, region, fleetBox, addrs, health, gate)
		// Only a held row carries the extra field: an unheld registry's digest
		// is byte-identical to the pre-hold_op encoding (switch-off contract),
		// while a hold handed to another op with the status unchanged still
		// moves it.
		if holdOp != "" {
			fmt.Fprintf(h, "\x1fhold_op=%s", holdOp)
		}
		// An import's gate follows the same rule: the edge must reload when a
		// gate is set, closed or cleared, and an ungated registry digests as
		// it did before the column existed.
		if importGate != "" {
			fmt.Fprintf(h, "\x1fimport_gate=%q", importGate)
		}
		// Ownership follows the same rule: the all-default value is omitted,
		// and each non-default field is tagged and quoted so a partial value
		// cannot alias another field's.
		if claim.ClaimIncarnation != "" {
			fmt.Fprintf(h, "\x1fclaim_incarnation=%q", claim.ClaimIncarnation)
		}
		if claim.ClaimStamp != 0 {
			fmt.Fprintf(h, "\x1fclaim_stamp=%d", claim.ClaimStamp)
		}
		if claim.ClaimOwner != "" {
			fmt.Fprintf(h, "\x1fclaim_owner=%q", claim.ClaimOwner)
		}
		if claim.ClaimRowID != "" {
			fmt.Fprintf(h, "\x1fclaim_row_id=%q", claim.ClaimRowID)
		}
		fmt.Fprint(h, "\x1e")
		n++
	}
	if err := rows.Err(); err != nil {
		return "", err
	}

	// Section marker: everything after this belongs to fleets, never to
	// routes — without it a fleet name could in principle produce the same
	// byte stream a route column did and mask a change.
	fmt.Fprint(h, "\x1dfleets\x1e")
	frows, err := db.Query(`SELECT name, account, bucket, rev FROM fleets ORDER BY name`)
	if err != nil {
		return "", err
	}
	defer frows.Close()
	for frows.Next() {
		var name, account, bucket string
		var rev int64
		if err := frows.Scan(&name, &account, &bucket, &rev); err != nil {
			return "", err
		}
		fmt.Fprintf(h, "%s\x1f%s\x1f%s\x1f%d\x1e", name, account, bucket, rev)
	}
	if err := frows.Err(); err != nil {
		return "", err
	}

	fmt.Fprint(h, "\x1dfleet_boxes\x1e")
	brows, err := db.Query(`SELECT fleet, label, token_hash, status, rev
FROM fleet_boxes ORDER BY fleet, label`)
	if err != nil {
		return "", err
	}
	defer brows.Close()
	for brows.Next() {
		var fleet, label, tokenHash, status string
		var rev int64
		if err := brows.Scan(&fleet, &label, &tokenHash, &status, &rev); err != nil {
			return "", err
		}
		fmt.Fprintf(h, "%s\x1f%s\x1f%s\x1f%s\x1f%d\x1e", fleet, label, tokenHash, status, rev)
	}
	if err := brows.Err(); err != nil {
		return "", err
	}

	// route_aliases: only alias/host/account/app decide which hostnames the
	// edge routes and where. dns_status/dns_checked_at/cert_expires_at are
	// deliberately excluded here for the same reason the routes SELECT above
	// omits its own dns_status/cert_expires_at columns — a box-side pass
	// stamps them on a 5-minute cadence, and hashing a value that moves on
	// that cadence would force a pointless fleet-wide edge reload every pass.
	fmt.Fprint(h, "\x1droute_aliases\x1e")
	arows, err := db.Query(`SELECT alias, host, account, app FROM route_aliases ORDER BY alias`)
	if err != nil {
		return "", err
	}
	defer arows.Close()
	for arows.Next() {
		var alias, host, account, app string
		if err := arows.Scan(&alias, &host, &account, &app); err != nil {
			return "", err
		}
		fmt.Fprintf(h, "%s\x1f%s\x1f%s\x1f%s\x1e", alias, host, account, app)
	}
	if err := arows.Err(); err != nil {
		return "", err
	}

	// wildcard_domains: see the doc comment above — this is what lets a
	// claim-derived delegated wildcard parent reach wildcard.go's refresh()
	// on the edge's normal 2s poll instead of waiting on its own 12h tick.
	fmt.Fprint(h, "\x1dwildcard_domains\x1e")
	wrows, err := db.Query(`SELECT host, account, app, created_at FROM wildcard_domains ORDER BY host`)
	if err != nil {
		return "", err
	}
	defer wrows.Close()
	for wrows.Next() {
		var host, account, app string
		var createdAt int64
		if err := wrows.Scan(&host, &account, &app, &createdAt); err != nil {
			return "", err
		}
		fmt.Fprintf(h, "%s\x1f%s\x1f%s\x1f%d\x1e", host, account, app, createdAt)
	}
	if err := wrows.Err(); err != nil {
		return "", err
	}

	// route_paths: which prefix on which hostname reaches which route is a
	// routing decision in exactly the sense route_aliases' alias/host is, so
	// it hashes here or a flip never reaches the live map. All five columns
	// hash — unlike route_aliases there is no box-stamped, cadence-driven
	// column on this table to exclude, so there is nothing here that could
	// force a pointless fleet-wide reload.
	fmt.Fprint(h, "\x1droute_paths\x1e")
	prows, err := db.Query(`SELECT host, prefix, target, account, app
FROM route_paths ORDER BY host, prefix`)
	if err != nil {
		return "", err
	}
	defer prows.Close()
	for prows.Next() {
		var host, prefix, target, account, app string
		if err := prows.Scan(&host, &prefix, &target, &account, &app); err != nil {
			return "", err
		}
		fmt.Fprintf(h, "%s\x1f%s\x1f%s\x1f%s\x1f%s\x1e", host, prefix, target, account, app)
	}
	if err := prows.Err(); err != nil {
		return "", err
	}

	return fmt.Sprintf("%d:%x", n, h.Sum(nil)), nil
}

// SnapshotFingerprint summarizes the registry for the SNAPSHOT pass, where
// Fingerprint summarizes it for the edge's reload poll. The two answer
// different questions and must not be conflated:
//
// Fingerprint hashes only the columns a reload cares about, and deliberately
// EXCLUDES caps, unit, replication, environment, account, canary and
// provisioned — they change who restarts a process or what a tenant may do,
// not what the edge serves. That exclusion is correct for reloads and wrong
// for snapshots: those box-scoped columns are exactly the rows nothing else
// in the bucket can rebuild, so a cold-standby restore that misses them
// silently drops caps grants and console unit stamps. Gating the snapshot on
// Fingerprint meant a caps grant or a unit stamp never triggered a write at
// all. So SnapshotFingerprint hashes EVERY column of routes, plus accounts,
// fleets and fleet_boxes — anything the restore has to reproduce.
//
// The routes query is `SELECT rowid, ` + routeCols, so a column added to
// routeCols is covered here automatically (no fourth place to update); the
// values are scanned generically and written in column order, which is why
// this needs no per-column typing the way Fingerprint does.
//
// "Every column" has exactly four exceptions, snapshotChurnCols: the
// scheduling stamps (next_due, dns_checked_at), the serving_version rollout
// stamp, and build_state, the agent's copy of each instance's build state
// (its ReadAt moves on every refresh). The box rewrites them on its own
// clock with no operator change behind them. Hashing those made the gate
// never hold (#207: both production boxes wrote a generation on
// essentially every 5-minute tick, so the 8-generation history was ~40
// minutes deep, not 8 changes deep). They are still IN the snapshot; only
// the decision to take one ignores them. A restore therefore loses up to
// one tick of those stamps, which the activator and DNS sweeps and the
// build-state refresh re-derive on their first pass anyway.
//
// route_aliases and wildcard_domains are not hashed either: both are
// pass-owned materializations of domain claims (domains.Reconcile rebuilds
// every row from the claim records), so a restore recovers them by
// running.
//
// Hashing the VACUUM INTO output instead was rejected: SQLite's page image
// is not byte-deterministic across runs of identical content (freelist and
// page-ordering effects), so it would report a change on every pass and
// snapshot forever.
//
// Fresh connection, same reason as Fingerprint and List: a long-lived
// modernc connection can serve a stale WAL snapshot of another process's
// commits.
// snapshotChurnCols are the routes columns SnapshotFingerprint skips: stamps
// the box's own sweeps rewrite with nothing behind them an operator did.
// Keep this to columns that are re-derived within one sweep of a restore.
var snapshotChurnCols = map[string]bool{
	"next_due":        true, // activator scheduling (SetNextDue, every pass)
	"dns_checked_at":  true, // DNS sweep stamp (SetDNSStatus, every sweep)
	"serving_version": true, // rollout stamp (SetServingVersion, every wake and swap)
	"build_state":     true, // the agent's build-state copy (SetBuildState, ReadAt moves every refresh)
}

// snapshotClaimCols are the ownership columns, hashed only when non-default.
var snapshotClaimCols = map[string]bool{
	"claim_incarnation": true,
	"claim_stamp":       true,
	"claim_owner":       true,
	"claim_row_id":      true,
}

// snapshotWhenSetCols are the good-build columns (zero-downtime spec §8.5)
// and the update-settings column. A restore has to reproduce them, so they
// are hashed, but only when set and tagged, on the hold_op rule: a registry
// that has never recorded a good build or set update settings digests exactly
// as it did before the columns existed, so upgrading does not force a snapshot
// on every box.
var snapshotWhenSetCols = map[string]bool{
	"good_version":    true,
	"good_digest":     true,
	"good_demand":     true,
	"update_settings": true,
}

func (r *Registry) SnapshotFingerprint() (string, error) {
	if r.remote != nil {
		out, err := r.remote.call(nil, remoteRequest{Method: "SnapshotFingerprint"}, nil)
		return out.Text, err
	}

	db, err := sql.Open("sqlite", r.path)
	if err != nil {
		return "", err
	}
	defer db.Close()
	if _, err := db.Exec("PRAGMA busy_timeout=5000"); err != nil {
		return "", err
	}

	rows, err := db.Query(`SELECT rowid, ` + routeCols + ` FROM routes ORDER BY rowid`)
	if err != nil {
		return "", err
	}
	defer rows.Close()
	cols, err := rows.Columns()
	if err != nil {
		return "", err
	}
	h := sha256.New()
	n := 0
	vals := make([]sql.NullString, len(cols))
	dest := make([]any, len(cols))
	for i := range vals {
		dest[i] = &vals[i]
	}
	for rows.Next() {
		if err := rows.Scan(dest...); err != nil {
			return "", err
		}
		for i, v := range vals {
			if snapshotChurnCols[cols[i]] {
				continue
			}
			// hold_op is hashed only when set, tagged, so a registry with no
			// held rows digests exactly as it did before the column existed.
			if cols[i] == "hold_op" {
				if v.Valid && v.String != "" {
					fmt.Fprintf(h, "hold_op=%s\x1f", v.String)
				}
				continue
			}
			// seed_source follows the same rule: a registry with no seeded
			// rows digests exactly as it did before the column existed.
			if cols[i] == "seed_source" {
				if v.Valid && v.String != "" {
					fmt.Fprintf(h, "seed_source=%s\x1f", v.String)
				}
				continue
			}
			// So does import_gate: a registry with no gated rows digests
			// exactly as it did before the column existed.
			if cols[i] == "import_gate" {
				if v.Valid && v.String != "" {
					fmt.Fprintf(h, "import_gate=%s\x1f", v.String)
				}
				continue
			}
			// Claim ownership columns follow the hold_op rule: default
			// values are omitted, anything else is tagged and quoted.
			if snapshotClaimCols[cols[i]] {
				if v.Valid && v.String != "" && (cols[i] != "claim_stamp" || v.String != "0") {
					fmt.Fprintf(h, "%s=%q\x1f", cols[i], v.String)
				}
				continue
			}
			if snapshotWhenSetCols[cols[i]] {
				if v.Valid && v.String != "" {
					fmt.Fprintf(h, "%s=%q\x1f", cols[i], v.String)
				}
				continue
			}
			// NULL and "" must not hash alike: a column that has never been
			// written is a different restore than one explicitly cleared.
			if v.Valid {
				fmt.Fprintf(h, "%s\x1f", v.String)
			} else {
				fmt.Fprint(h, "\x00\x1f")
			}
		}
		fmt.Fprint(h, "\x1e")
		n++
	}
	if err := rows.Err(); err != nil {
		return "", err
	}

	// Section markers, same discipline as Fingerprint: a value can never
	// migrate across sections and cancel another change out.
	fmt.Fprint(h, "\x1daccounts\x1e")
	arows, err := db.Query(`SELECT id, sqid, name FROM accounts ORDER BY id`)
	if err != nil {
		return "", err
	}
	defer arows.Close()
	for arows.Next() {
		var id int64
		var sqid, name string
		if err := arows.Scan(&id, &sqid, &name); err != nil {
			return "", err
		}
		fmt.Fprintf(h, "%d\x1f%s\x1f%s\x1e", id, sqid, name)
	}
	if err := arows.Err(); err != nil {
		return "", err
	}

	fmt.Fprint(h, "\x1dfleets\x1e")
	frows, err := db.Query(`SELECT name, account, bucket, rev FROM fleets ORDER BY name`)
	if err != nil {
		return "", err
	}
	defer frows.Close()
	for frows.Next() {
		var name, account, bucket string
		var rev int64
		if err := frows.Scan(&name, &account, &bucket, &rev); err != nil {
			return "", err
		}
		fmt.Fprintf(h, "%s\x1f%s\x1f%s\x1f%d\x1e", name, account, bucket, rev)
	}
	if err := frows.Err(); err != nil {
		return "", err
	}

	fmt.Fprint(h, "\x1dfleet_boxes\x1e")
	brows, err := db.Query(`SELECT fleet, label, token_hash, status, rev
FROM fleet_boxes ORDER BY fleet, label`)
	if err != nil {
		return "", err
	}
	defer brows.Close()
	for brows.Next() {
		var fleet, label, tokenHash, status string
		var rev int64
		if err := brows.Scan(&fleet, &label, &tokenHash, &status, &rev); err != nil {
			return "", err
		}
		fmt.Fprintf(h, "%s\x1f%s\x1f%s\x1f%s\x1f%d\x1e", fleet, label, tokenHash, status, rev)
	}
	if err := brows.Err(); err != nil {
		return "", err
	}
	if err := hashImportJournal(db, h); err != nil {
		return "", err
	}

	return fmt.Sprintf("%d:%x", n, h.Sum(nil)), nil
}

// SnapshotTo writes a consistent single-file copy of the registry to path
// via VACUUM INTO on a fresh connection — same fresh-connection recipe as
// Fingerprint (modernc WAL visibility). path must not already exist:
// modernc's VACUUM INTO silently overwrites an existing file rather than
// refusing it (unlike upstream SQLite's CLI), so we check first — callers
// stage into a temp dir. The bound-parameter form (not fmt.Sprintf/%q) is
// required: %q is Go string escaping, not SQL escaping, so a path
// containing a backslash gets VACUUM INTO writing to a different on-disk
// path than the one os.Stat just checked, silently defeating the guard.
func (r *Registry) SnapshotTo(path string) error {
	if r.remote != nil {
		_, err := r.remote.call(nil, remoteRequest{Method: "SnapshotTo", Path: path}, nil)
		return err
	}

	if _, err := os.Stat(path); err == nil {
		return fmt.Errorf("registry: snapshot target already exists: %s", path)
	} else if !os.IsNotExist(err) {
		return err
	}
	db, err := sql.Open("sqlite", r.path)
	if err != nil {
		return err
	}
	defer db.Close()
	_, err = db.Exec("VACUUM INTO ?", path)
	return err
}