1"""SQLite-backed registry of OpenSpec sources (repos with an ``openspec/`` dir).23Each registered source keeps a stable internal token, but the user-facing copy4format is the source's vanity name (for example ``puzzletea``) plus a derived5change token (for example ``puzzletea/os_a1b2c3``). The OpenSpec plugin's6``openspec_context`` tool resolves the vanity name back to the repo path and7maps the change token to the matching change folder.89The DB lives at ``<hermes_home>/openspec.db`` and is shared between the dashboard10plugin (which owns source CRUD via its ``plugin_api.py`` routes) and the agent11plugin (which only reads to resolve sources). Schema is intentionally tiny —12one row per source. Change contents are not stored; changes are addressed live13by deterministic tokens derived from their folder names. The only per-change14metadata kept here is dashboard-local sequence order, assigned when the15dashboard first observes a change so OpenSpec files remain untouched.16"""1718from __future__ import annotations1920import hashlib21import json22import secrets23import sqlite324import time25from pathlib import Path26from typing import Any, Optional2728from hermes_constants import get_hermes_home2930_TOKEN_PREFIX = "os_"31_TOKEN_NBYTES = 3 # 6 hex chars32_CHANGE_TOKEN_HEXLEN = 6 # os_ + 6 hex chars, derived from the change name333435def db_path() -> Path:36 """Absolute path to the OpenSpec registry DB for the active Hermes home."""37 return get_hermes_home() / "openspec.db"383940def _connect() -> sqlite3.Connection:41 path = db_path()42 path.parent.mkdir(parents=True, exist_ok=True)43 conn = sqlite3.connect(str(path), timeout=10.0)44 conn.row_factory = sqlite3.Row45 conn.execute("PRAGMA journal_mode=WAL")46 conn.execute("PRAGMA busy_timeout=10000")47 conn.execute(48 """49 CREATE TABLE IF NOT EXISTS openspec_sources (50 token TEXT PRIMARY KEY,51 name TEXT NOT NULL DEFAULT '',52 path TEXT NOT NULL,53 created_at REAL NOT NULL54 )55 """56 )57 conn.execute(58 "CREATE UNIQUE INDEX IF NOT EXISTS idx_openspec_sources_path ON openspec_sources(path)"59 )60 conn.execute(61 """62 CREATE TABLE IF NOT EXISTS change_sequence (63 source_id TEXT NOT NULL,64 change_name TEXT NOT NULL,65 group_id TEXT NOT NULL DEFAULT 'default',66 position INTEGER NOT NULL,67 depends_on TEXT NOT NULL DEFAULT '[]',68 first_seen_at REAL NOT NULL,69 updated_at REAL NOT NULL,70 PRIMARY KEY (source_id, change_name)71 )72 """73 )74 _ensure_change_sequence_columns(conn)75 conn.execute(76 "CREATE INDEX IF NOT EXISTS idx_change_sequence_source_position ON change_sequence(source_id, position)"77 )78 return conn798081def _ensure_change_sequence_columns(conn: sqlite3.Connection) -> None:82 cols = {row[1] for row in conn.execute("PRAGMA table_info(change_sequence)").fetchall()}83 if "group_id" not in cols:84 conn.execute("ALTER TABLE change_sequence ADD COLUMN group_id TEXT NOT NULL DEFAULT 'default'")85 if "depends_on" not in cols:86 conn.execute("ALTER TABLE change_sequence ADD COLUMN depends_on TEXT NOT NULL DEFAULT '[]'")878889def _sequence_row(row: sqlite3.Row) -> dict[str, Any]:90 try:91 depends_on = json.loads(row["depends_on"] or "[]")92 except Exception:93 depends_on = []94 if not isinstance(depends_on, list):95 depends_on = []96 return {97 "groupId": row["group_id"] or "default",98 "position": int(row["position"]),99 "dependsOn": [str(item) for item in depends_on],100 "firstSeenAt": row["first_seen_at"],101 "updatedAt": row["updated_at"],102 }103104105def _gen_token(conn: sqlite3.Connection) -> str:106 for _ in range(64):107 token = _TOKEN_PREFIX + secrets.token_hex(_TOKEN_NBYTES)108 exists = conn.execute(109 "SELECT 1 FROM openspec_sources WHERE token = ?", (token,)110 ).fetchone()111 if not exists:112 return token113 # Astronomically unlikely; widen the space rather than fail.114 return _TOKEN_PREFIX + secrets.token_hex(_TOKEN_NBYTES + 2)115116117def _row_to_dict(row: sqlite3.Row) -> dict[str, Any]:118 return {119 "token": row["token"],120 "id": row["token"], # back-compat alias for existing dashboard code121 "name": row["name"] or "",122 "path": row["path"],123 "created_at": row["created_at"],124 }125126127def list_sources() -> list[dict[str, Any]]:128 """All registered sources, oldest first."""129 with _connect() as conn:130 rows = conn.execute(131 "SELECT * FROM openspec_sources ORDER BY created_at ASC"132 ).fetchall()133 return [_row_to_dict(r) for r in rows]134135136def get_source(token: str) -> Optional[dict[str, Any]]:137 token = (token or "").strip()138 if not token:139 return None140 with _connect() as conn:141 row = conn.execute(142 "SELECT * FROM openspec_sources WHERE token = ?", (token,)143 ).fetchone()144 return _row_to_dict(row) if row else None145146147def effective_name(source: dict[str, Any]) -> str:148 """The human-facing vanity name for a source: stored name or path basename."""149 return (source.get("name") or "").strip() or Path(source["path"]).expanduser().name150151152def get_source_by_name(name: str) -> Optional[dict[str, Any]]:153 """Look up a source by its vanity name (case-insensitive). First match wins.154155 Matches against ``effective_name`` so a source registered without an explicit156 display name still resolves by its path basename.157 """158 key = (name or "").strip().lower()159 if not key:160 return None161 for source in list_sources():162 if effective_name(source).lower() == key:163 return source164 return None165166167def change_token(change_name: str) -> str:168 """Deterministic, opaque token for a change, derived from its folder name.169170 Stable across restarts and never stored — the resolver recomputes it for each171 change on disk and matches, so the DB can never drift from the filesystem.172 """173 digest = hashlib.sha256((change_name or "").encode("utf-8")).hexdigest()174 return _TOKEN_PREFIX + digest[:_CHANGE_TOKEN_HEXLEN]175176177def get_source_by_path(path: str) -> Optional[dict[str, Any]]:178 normalized = str(Path(path).expanduser())179 with _connect() as conn:180 row = conn.execute(181 "SELECT * FROM openspec_sources WHERE path = ?", (normalized,)182 ).fetchone()183 return _row_to_dict(row) if row else None184185186def add_source(path: str, name: str = "") -> dict[str, Any]:187 """Register a source. Raises ValueError if the path is already registered."""188 normalized = str(Path(path).expanduser())189 with _connect() as conn:190 existing = conn.execute(191 "SELECT * FROM openspec_sources WHERE path = ?", (normalized,)192 ).fetchone()193 if existing:194 raise ValueError("Source already registered")195 token = _gen_token(conn)196 conn.execute(197 "INSERT INTO openspec_sources (token, name, path, created_at) VALUES (?, ?, ?, ?)",198 (token, (name or "").strip(), normalized, time.time()),199 )200 conn.commit()201 row = conn.execute(202 "SELECT * FROM openspec_sources WHERE token = ?", (token,)203 ).fetchone()204 return _row_to_dict(row)205206207def update_source(token: str, *, path: str, name: str = "") -> Optional[dict[str, Any]]:208 """Update a registered source. Raises ValueError if the new path conflicts."""209 token = (token or "").strip()210 normalized = str(Path(path).expanduser())211 if not token or not normalized:212 return None213 with _connect() as conn:214 current = conn.execute(215 "SELECT * FROM openspec_sources WHERE token = ?", (token,)216 ).fetchone()217 if current is None:218 return None219 conflict = conn.execute(220 "SELECT token FROM openspec_sources WHERE path = ? AND token != ?",221 (normalized, token),222 ).fetchone()223 if conflict:224 raise ValueError("Source already registered")225 conn.execute(226 "UPDATE openspec_sources SET name = ?, path = ? WHERE token = ?",227 ((name or "").strip(), normalized, token),228 )229 conn.commit()230 row = conn.execute(231 "SELECT * FROM openspec_sources WHERE token = ?", (token,)232 ).fetchone()233 return _row_to_dict(row) if row else None234235236def remove_source(token: str) -> bool:237 token = (token or "").strip()238 if not token:239 return False240 with _connect() as conn:241 cur = conn.execute("DELETE FROM openspec_sources WHERE token = ?", (token,))242 conn.execute("DELETE FROM change_sequence WHERE source_id = ?", (token,))243 conn.commit()244 return cur.rowcount > 0245246247def ensure_change_sequence(source_id: str, change_names: list[str]) -> dict[str, dict[str, Any]]:248 """Ensure dashboard-local sequence rows exist for observed changes.249250 Existing positions are preserved. Newly observed change folders append after251 the current max position in the order provided by the scanner. This keeps252 sequencing out of OpenSpec files and avoids parsing naming conventions.253 """254 source_id = (source_id or "").strip()255 names: list[str] = []256 seen: set[str] = set()257 for raw in change_names or []:258 name = str(raw or "").strip()259 if name and name not in seen:260 names.append(name)261 seen.add(name)262 if not source_id or not names:263 return {}264265 now = time.time()266 with _connect() as conn:267 rows = conn.execute(268 "SELECT change_name, group_id, position, depends_on, first_seen_at, updated_at FROM change_sequence WHERE source_id = ?",269 (source_id,),270 ).fetchall()271 existing = {r["change_name"]: r for r in rows}272 max_pos = max((int(r["position"]) for r in rows), default=0)273 for name in names:274 if name in existing:275 continue276 max_pos += 1277 conn.execute(278 "INSERT INTO change_sequence (source_id, change_name, position, first_seen_at, updated_at) VALUES (?, ?, ?, ?, ?)",279 (source_id, name, max_pos, now, now),280 )281 conn.commit()282 rows = conn.execute(283 "SELECT change_name, group_id, position, depends_on, first_seen_at, updated_at FROM change_sequence WHERE source_id = ?",284 (source_id,),285 ).fetchall()286287 wanted = set(names)288 return {289 r["change_name"]: _sequence_row(r)290 for r in rows291 if r["change_name"] in wanted292 }293294295def get_change_sequence(source_id: str, change_names: list[str] | None = None) -> dict[str, dict[str, Any]]:296 """Return dashboard-local sequence metadata for a source."""297 source_id = (source_id or "").strip()298 if not source_id:299 return {}300 names = {str(name or "").strip() for name in (change_names or []) if str(name or "").strip()}301 with _connect() as conn:302 rows = conn.execute(303 "SELECT change_name, group_id, position, depends_on, first_seen_at, updated_at FROM change_sequence WHERE source_id = ? ORDER BY position ASC, change_name ASC",304 (source_id,),305 ).fetchall()306 return {307 r["change_name"]: _sequence_row(r)308 for r in rows309 if not names or r["change_name"] in names310 }311312313def set_change_sequence(314 source_id: str,315 change_names: list[str],316 *,317 group_id: str = "default",318 dependencies: dict[str, list[str]] | None = None,319) -> dict[str, dict[str, Any]]:320 """Explicitly set agent-declared order/dependencies for a set of changes."""321 source_id = (source_id or "").strip()322 group_id = (group_id or "default").strip() or "default"323 names: list[str] = []324 seen: set[str] = set()325 for raw in change_names or []:326 name = str(raw or "").strip()327 if name and name not in seen:328 names.append(name)329 seen.add(name)330 if not source_id or not names:331 return {}332 deps = dependencies or {}333 now = time.time()334 with _connect() as conn:335 existing_rows = conn.execute(336 "SELECT change_name, first_seen_at FROM change_sequence WHERE source_id = ?",337 (source_id,),338 ).fetchall()339 first_seen = {r["change_name"]: r["first_seen_at"] for r in existing_rows}340 for index, name in enumerate(names, start=1):341 depends_on = [str(item).strip() for item in deps.get(name, []) if str(item).strip()]342 conn.execute(343 """344 INSERT INTO change_sequence (source_id, change_name, group_id, position, depends_on, first_seen_at, updated_at)345 VALUES (?, ?, ?, ?, ?, ?, ?)346 ON CONFLICT(source_id, change_name) DO UPDATE SET347 group_id = excluded.group_id,348 position = excluded.position,349 depends_on = excluded.depends_on,350 updated_at = excluded.updated_at351 """,352 (source_id, name, group_id, index, json.dumps(depends_on), first_seen.get(name, now), now),353 )354 conn.commit()355 return get_change_sequence(source_id, names)356357358def parse_identifier(identifier: str) -> tuple[str, Optional[str]]:359 """Split a pasted identifier into ``(source, change_token | None)``.360361 Accepts a vanity name (``puzzletea``) or ``puzzletea/os_a1b2c3``. The first362 segment locates the source (by vanity name, or legacy ``os_`` source token);363 everything after the first ``/`` is the change token. The resolver maps the364 change token back to a change folder by recomputing ``change_token`` for each365 change on disk, so nothing about changes is ever stored.366 """367 raw = (identifier or "").strip()368 if "/" in raw:369 source, _, change = raw.partition("/")370 return source.strip(), (change.strip() or None)371 return raw, None372373374def migrate_from_config_sources(config_sources: list[dict[str, Any]]) -> int:375 """One-time import of legacy ``config.yaml`` ``dashboard.openspec_sources``.376377 Returns the number of rows inserted. Idempotent: paths already present are378 skipped, so calling it repeatedly is safe.379 """380 inserted = 0381 for item in config_sources or []:382 if not isinstance(item, dict):383 continue384 path = str(item.get("path") or "").strip()385 if not path:386 continue387 try:388 add_source(path, str(item.get("name") or "").strip())389 inserted += 1390 except ValueError:391 continue # already migrated392 return inserted