Python
"""Machines registry — the controller's durable record of enrolled boxes.
Lives in coworker.db next to the other stores. Presence is deliberately NOT a
column: liveness belongs to the acceptor's in-memory socket map, the registry
only records identity (pinned pubkey), the user-facing name, and timestamps.
"""
from __future__ import annotations
import sqlite3
import threading
import time
import uuid
from pathlib import Path
from typing import Any, Optional
MAX_NAME_CHARS = 60
def _clean_name(name: str) -> str:
cleaned = " ".join((name or "").split())[:MAX_NAME_CHARS].strip()
return cleaned or "machine"
class MachinesRegistry:
def __init__(self, db_path: str | Path) -> None:
self.db_path = Path(db_path).expanduser()
self._lock = threading.RLock()
self._conn = sqlite3.connect(self.db_path, check_same_thread=False)
self._conn.row_factory = sqlite3.Row
with self._lock:
# Name uniqueness is PER ORG, enforced in code (enroll's suffix loop
# and rename's clash check) — not by a table constraint, so the
# desktop's org-"" world and a multi-tenant deployment share one
# schema. (Pre-tenancy desktop DBs carry a UNIQUE(name) constraint;
# harmless there — one org.)
self._conn.execute(
"""
CREATE TABLE IF NOT EXISTS machines (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
pubkey TEXT UNIQUE NOT NULL,
seal_pubkey TEXT DEFAULT '',
app_version TEXT DEFAULT '',
org_id TEXT NOT NULL DEFAULT '',
created_at REAL NOT NULL,
last_seen REAL
)
"""
)
# Observability Phase 3 (E28, TD3-6): the last Usage rollup each machine answered,
# per request (period, grouping, coworker, ...), so an offline machine still shows
# its numbers on the Usage page, labelled "as of", and only for the question that
# was asked (P3B-3). Counts only (the ledger is content-free). The first build kept
# one row per grouping, whatever the period; that table is dropped.
self._conn.execute("DROP TABLE IF EXISTS machine_rollups")
self._conn.execute(
"""
CREATE TABLE IF NOT EXISTS machine_rollup_answers (
machine_id TEXT NOT NULL,
query_key TEXT NOT NULL,
query TEXT NOT NULL,
payload TEXT NOT NULL,
updated_at REAL NOT NULL,
PRIMARY KEY (machine_id, query_key)
)
"""
)
# Tier-2 snapshot (remote-home-design.md data path): the last-known
# session-LIST METADATA per machine — what keeps an offline machine's
# sessions greyed-but-visible instead of vanished.
self._conn.execute(
"""
CREATE TABLE IF NOT EXISTS machine_sessions (
machine_id TEXT PRIMARY KEY,
payload TEXT NOT NULL,
updated_at REAL NOT NULL
)
"""
)
# Desktop-tier transcript cache (owner amendment 2026-08-25):
# cache-on-view — every transcript the user fetched while the box was
# connected stays readable (READ-ONLY) while it's offline. This is the
# DESKTOP controller only: same user, same device, same trust domain.
# The cloud tier never stores conversation content.
self._conn.execute(
"""
CREATE TABLE IF NOT EXISTS machine_transcripts (
machine_id TEXT NOT NULL,
session_id TEXT NOT NULL,
payload TEXT NOT NULL,
updated_at REAL NOT NULL,
PRIMARY KEY (machine_id, session_id)
)
"""
)
# Databases from before the sealing key / tenancy (idempotent-by-
# error ALTERs). Pre-tenancy rows land in org "" — the desktop org.
for alter in (
"ALTER TABLE machines ADD COLUMN seal_pubkey TEXT DEFAULT ''",
"ALTER TABLE machines ADD COLUMN org_id TEXT NOT NULL DEFAULT ''",
# Fleet under the org (2026-09-02): the member who armed/approved
# the enrollment owns the machine in the admin's view.
"ALTER TABLE machines ADD COLUMN enrolled_by TEXT NOT NULL DEFAULT ''",
# Managed sandboxes (2026-09-02): where the machine came from
# ("" = brought by the user, "fly" = our sandbox) and the
# provisioner's own id for it, so lifecycle code can find the
# box a sandbox became.
"ALTER TABLE machines ADD COLUMN provenance TEXT NOT NULL DEFAULT ''",
"ALTER TABLE machines ADD COLUMN provenance_ref TEXT NOT NULL DEFAULT ''",
):
try:
self._conn.execute(alter)
except sqlite3.OperationalError:
pass # already migrated
# Wallet deployments ledger: WHICH profiles went WHERE, with a hash
# of the deployed value for staleness — values themselves never
# flow back (remote-home-design.md §Keys wallet).
self._conn.execute(
"""
CREATE TABLE IF NOT EXISTS machine_secret_deploys (
machine_id TEXT NOT NULL,
profile TEXT NOT NULL,
value_hash TEXT NOT NULL,
deployed_at REAL NOT NULL,
PRIMARY KEY (machine_id, profile)
)
"""
)
self._conn.commit()
def enroll(
self,
name: str,
pubkey: str,
app_version: str = "",
seal_pubkey: str = "",
org_id: str = "",
enrolled_by: str = "",
provenance: str = "",
provenance_ref: str = "",
) -> dict[str, Any]:
"""Register a new pubkey in an org; collisions on the requested name
auto-suffix (-2, -3…) within that org. `enrolled_by` = the member who
armed/approved the join (the machine's owner in the org's fleet view).
`provenance`/`provenance_ref` = which provisioner made the machine and
its id there ("" for machines the user brought)."""
base = _clean_name(name)
with self._lock:
row = self._conn.execute(
"SELECT id FROM machines WHERE pubkey = ?", (pubkey,)
).fetchone()
if row is not None:
raise ValueError("pubkey already enrolled")
candidate, attempt = base, 1
while self._conn.execute(
"SELECT 1 FROM machines WHERE name = ? AND org_id = ?",
(candidate, org_id),
).fetchone():
attempt += 1
candidate = f"{base}-{attempt}"
machine_id = uuid.uuid4().hex[:12]
now = time.time()
self._conn.execute(
"INSERT INTO machines (id, name, pubkey, seal_pubkey, app_version, org_id, enrolled_by,"
" provenance, provenance_ref, created_at, last_seen)"
" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
(machine_id, candidate, pubkey, seal_pubkey, app_version, org_id, enrolled_by,
provenance, provenance_ref, now, now),
)
self._conn.commit()
return {
"id": machine_id,
"name": candidate,
"pubkey": pubkey,
"org_id": org_id,
"enrolled_by": enrolled_by,
"provenance": provenance,
"provenance_ref": provenance_ref,
}
def by_pubkey(self, pubkey: str) -> Optional[dict[str, Any]]:
with self._lock:
row = self._conn.execute(
"SELECT * FROM machines WHERE pubkey = ?", (pubkey,)
).fetchone()
return dict(row) if row else None
def by_id(self, machine_id: str) -> Optional[dict[str, Any]]:
with self._lock:
row = self._conn.execute(
"SELECT * FROM machines WHERE id = ?", (machine_id,)
).fetchone()
return dict(row) if row else None
def list(self, org_id: Optional[str] = None) -> list[dict[str, Any]]:
with self._lock:
if org_id is None:
rows = self._conn.execute(
"SELECT * FROM machines ORDER BY created_at"
).fetchall()
else:
rows = self._conn.execute(
"SELECT * FROM machines WHERE org_id = ? ORDER BY created_at",
(org_id,),
).fetchall()
return [dict(r) for r in rows]
def touch(self, machine_id: str, app_version: str | None = None) -> None:
with self._lock:
if app_version is None:
self._conn.execute(
"UPDATE machines SET last_seen = ? WHERE id = ?",
(time.time(), machine_id),
)
else:
self._conn.execute(
"UPDATE machines SET last_seen = ?, app_version = ? WHERE id = ?",
(time.time(), app_version, machine_id),
)
self._conn.commit()
def rename(self, machine_id: str, name: str) -> Optional[dict[str, Any]]:
cleaned = _clean_name(name)
with self._lock:
row = self.by_id(machine_id)
if row is None:
return None
clash = self._conn.execute(
"SELECT 1 FROM machines WHERE name = ? AND org_id = ? AND id != ?",
(cleaned, row["org_id"], machine_id),
).fetchone()
if clash:
raise ValueError(f"a machine named '{cleaned}' already exists")
self._conn.execute(
"UPDATE machines SET name = ? WHERE id = ?", (cleaned, machine_id)
)
self._conn.commit()
return self.by_id(machine_id)
def remove(self, machine_id: str) -> bool:
with self._lock:
cur = self._conn.execute(
"DELETE FROM machines WHERE id = ?", (machine_id,)
)
self._conn.execute(
"DELETE FROM machine_sessions WHERE machine_id = ?", (machine_id,)
)
self._conn.execute(
"DELETE FROM machine_transcripts WHERE machine_id = ?", (machine_id,)
)
self._conn.execute(
"DELETE FROM machine_secret_deploys WHERE machine_id = ?",
(machine_id,),
)
self._conn.commit()
return cur.rowcount > 0
def set_seal_pubkey(self, machine_id: str, seal_pubkey: str) -> None:
with self._lock:
self._conn.execute(
"UPDATE machines SET seal_pubkey = ? WHERE id = ?",
(seal_pubkey, machine_id),
)
self._conn.commit()
def record_deploy(self, machine_id: str, profile: str, value_hash: str) -> None:
with self._lock:
self._conn.execute(
"INSERT INTO machine_secret_deploys (machine_id, profile, value_hash, deployed_at)"
" VALUES (?, ?, ?, ?)"
" ON CONFLICT(machine_id, profile) DO UPDATE SET"
" value_hash = excluded.value_hash, deployed_at = excluded.deployed_at",
(machine_id, profile, value_hash, time.time()),
)
self._conn.commit()
def remove_deploy(self, machine_id: str, profile: str) -> None:
with self._lock:
self._conn.execute(
"DELETE FROM machine_secret_deploys WHERE machine_id = ? AND profile = ?",
(machine_id, profile),
)
self._conn.commit()
def deploys(self, machine_id: str) -> list[dict[str, Any]]:
with self._lock:
rows = self._conn.execute(
"SELECT profile, value_hash, deployed_at FROM machine_secret_deploys"
" WHERE machine_id = ? ORDER BY profile",
(machine_id,),
).fetchall()
return [dict(r) for r in rows]
ROLLUPS_KEPT = 50 # per machine: the few periods and coworkers a person looks at
@staticmethod
def rollup_key(query: str) -> str:
"""A rollup request's cache key: its query parameters, sorted, so the same question
asked with its parameters in another order finds the same answer."""
from urllib.parse import parse_qsl, urlencode
return urlencode(sorted(parse_qsl(query or "", keep_blank_values=True)))
def save_rollup(self, machine_id: str, query: str, payload: str) -> None:
"""Keep a machine's answer to one rollup request (P3B-3: the whole request is the key,
not just its grouping). Only the newest `ROLLUPS_KEPT` per machine stay."""
with self._lock:
self._conn.execute(
"INSERT INTO machine_rollup_answers (machine_id, query_key, query, payload, updated_at)"
" VALUES (?, ?, ?, ?, ?) ON CONFLICT(machine_id, query_key) DO UPDATE SET"
" query = excluded.query, payload = excluded.payload, updated_at = excluded.updated_at",
(machine_id, self.rollup_key(query), query, payload, time.time()),
)
self._conn.execute(
"DELETE FROM machine_rollup_answers WHERE machine_id = ? AND query_key NOT IN ("
" SELECT query_key FROM machine_rollup_answers WHERE machine_id = ?"
" ORDER BY updated_at DESC LIMIT ?)",
(machine_id, machine_id, self.ROLLUPS_KEPT),
)
self._conn.commit()
def last_rollup(self, machine_id: str, query: str) -> Optional[dict]:
"""The machine's last answer to this same rollup request: {payload, query, updated_at},
or None when that request was never answered (another period or coworker is not a
stand-in)."""
with self._lock:
row = self._conn.execute(
"SELECT payload, query, updated_at FROM machine_rollup_answers"
" WHERE machine_id = ? AND query_key = ?",
(machine_id, self.rollup_key(query)),
).fetchone()
return dict(row) if row else None
def save_sessions_snapshot(self, machine_id: str, payload: str) -> None:
with self._lock:
self._conn.execute(
"INSERT INTO machine_sessions (machine_id, payload, updated_at)"
" VALUES (?, ?, ?)"
" ON CONFLICT(machine_id) DO UPDATE SET payload = excluded.payload,"
" updated_at = excluded.updated_at",
(machine_id, payload, time.time()),
)
self._conn.commit()
def sessions_snapshot(self, machine_id: str) -> Optional[str]:
with self._lock:
row = self._conn.execute(
"SELECT payload FROM machine_sessions WHERE machine_id = ?",
(machine_id,),
).fetchone()
return row["payload"] if row else None
def save_transcript(self, machine_id: str, session_id: str, payload: str) -> None:
with self._lock:
self._conn.execute(
"INSERT INTO machine_transcripts (machine_id, session_id, payload, updated_at)"
" VALUES (?, ?, ?, ?)"
" ON CONFLICT(machine_id, session_id) DO UPDATE SET"
" payload = excluded.payload, updated_at = excluded.updated_at",
(machine_id, session_id, payload, time.time()),
)
self._conn.commit()
def transcript(self, machine_id: str, session_id: str) -> Optional[str]:
with self._lock:
row = self._conn.execute(
"SELECT payload FROM machine_transcripts"
" WHERE machine_id = ? AND session_id = ?",
(machine_id, session_id),
).fetchone()
return row["payload"] if row else None