"""SQLite-backed job store for the NanoDex training queue.""" import json import os import sqlite3 import threading import time import uuid # Spaces persistent storage mounts at /data. Fall back to a local dir. DATA_DIR = "/data" if os.path.isdir("/data") and os.access("/data", os.W_OK) else \ os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), "runtime") os.makedirs(DATA_DIR, exist_ok=True) DB_PATH = os.path.join(DATA_DIR, "nanodex.db") RUNS_DIR = os.path.join(DATA_DIR, "runs") os.makedirs(RUNS_DIR, exist_ok=True) _lock = threading.RLock() SCHEMA = """ CREATE TABLE IF NOT EXISTS jobs ( id TEXT PRIMARY KEY, username TEXT NOT NULL, display_name TEXT, avatar_url TEXT, model_name TEXT NOT NULL, tier TEXT NOT NULL, n_params INTEGER NOT NULL, target_tokens INTEGER NOT NULL, status TEXT NOT NULL, -- queued|running|done|failed|cancelled gpu_index INTEGER, created_at REAL NOT NULL, started_at REAL, finished_at REAL, tokens_seen INTEGER DEFAULT 0, step INTEGER DEFAULT 0, total_steps INTEGER DEFAULT 0, loss REAL, best_loss REAL, lr REAL, tok_per_s REAL, eta_s REAL, error TEXT, pushed_repo TEXT, seed INTEGER DEFAULT 1337, live_step INTEGER, live_at REAL ); CREATE INDEX IF NOT EXISTS idx_jobs_status ON jobs(status, created_at); CREATE INDEX IF NOT EXISTS idx_jobs_user ON jobs(username, created_at DESC); CREATE TABLE IF NOT EXISTS metrics ( job_id TEXT NOT NULL, step INTEGER NOT NULL, tokens INTEGER NOT NULL, loss REAL NOT NULL, lr REAL, t REAL NOT NULL, PRIMARY KEY (job_id, step) ); CREATE TABLE IF NOT EXISTS logs ( job_id TEXT NOT NULL, t REAL NOT NULL, line TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_logs_job ON logs(job_id, t); """ def _conn(): c = sqlite3.connect(DB_PATH, timeout=10, check_same_thread=False) c.row_factory = sqlite3.Row c.execute("PRAGMA journal_mode=WAL") c.execute("PRAGMA busy_timeout=10000") return c # Columns added after the first release; applied to existing databases on boot. MIGRATIONS = [ "ALTER TABLE jobs ADD COLUMN live_step INTEGER", "ALTER TABLE jobs ADD COLUMN live_at REAL", ] def init(): with _lock, _conn() as c: c.executescript(SCHEMA) for stmt in MIGRATIONS: try: c.execute(stmt) except sqlite3.OperationalError: pass # already present # Any job left 'running' belongs to a container that is gone. This is a # write, so it can contend with another process holding the database (a # rolling deploy briefly runs two containers against the same volume) — # never let that stall the boot. try: with _lock, _conn() as c: c.execute( "UPDATE jobs SET status='failed', " "error='interrupted (the Space restarted)', finished_at=? " "WHERE status='running'", (time.time(),), ) except sqlite3.Error as exc: print(f"[db] could not reap orphaned runs: {exc!r}", flush=True) def create_job(username, display_name, avatar_url, model_name, tier, n_params, target_tokens, total_steps, seed=1337): job_id = uuid.uuid4().hex[:12] with _lock, _conn() as c: c.execute( "INSERT INTO jobs (id, username, display_name, avatar_url, model_name, tier," " n_params, target_tokens, status, created_at, total_steps, seed)" " VALUES (?,?,?,?,?,?,?,?, 'queued', ?, ?, ?)", (job_id, username, display_name, avatar_url, model_name, tier, n_params, target_tokens, time.time(), total_steps, seed), ) return job_id def update_job(job_id, **fields): if not fields: return cols = ", ".join(f"{k}=?" for k in fields) with _lock, _conn() as c: c.execute(f"UPDATE jobs SET {cols} WHERE id=?", (*fields.values(), job_id)) def get_job(job_id): with _lock, _conn() as c: r = c.execute("SELECT * FROM jobs WHERE id=?", (job_id,)).fetchone() return dict(r) if r else None def claim_next_queued(gpu_index): """Atomically move the oldest queued job to running. Returns the job or None.""" with _lock, _conn() as c: r = c.execute( "SELECT * FROM jobs WHERE status='queued' ORDER BY created_at LIMIT 1" ).fetchone() if not r: return None n = c.execute( "UPDATE jobs SET status='running', gpu_index=?, started_at=? " "WHERE id=? AND status='queued'", (gpu_index, time.time(), r["id"]), ).rowcount if n == 0: return None return dict(c.execute("SELECT * FROM jobs WHERE id=?", (r["id"],)).fetchone()) def list_jobs(status=None, username=None, limit=100): q = "SELECT * FROM jobs" where, args = [], [] if status: where.append("status IN (%s)" % ",".join("?" * len(status))) args += list(status) if username: where.append("username=?") args.append(username) if where: q += " WHERE " + " AND ".join(where) q += " ORDER BY created_at DESC LIMIT ?" args.append(limit) with _lock, _conn() as c: return [dict(r) for r in c.execute(q, args).fetchall()] def queue_position(job_id): with _lock, _conn() as c: r = c.execute("SELECT created_at FROM jobs WHERE id=?", (job_id,)).fetchone() if not r: return None return c.execute( "SELECT COUNT(*) FROM jobs WHERE status='queued' AND created_at < ?", (r["created_at"],), ).fetchone()[0] + 1 def user_active_count(username): with _lock, _conn() as c: return c.execute( "SELECT COUNT(*) FROM jobs WHERE username=? AND status IN ('queued','running')", (username,), ).fetchone()[0] def add_metric(job_id, step, tokens, loss, lr, t=None): with _lock, _conn() as c: c.execute( "INSERT OR REPLACE INTO metrics (job_id, step, tokens, loss, lr, t) VALUES (?,?,?,?,?,?)", (job_id, step, tokens, loss, lr, t or time.time()), ) def get_metrics(job_id, limit=3000): with _lock, _conn() as c: return [dict(r) for r in c.execute( "SELECT step, tokens, loss, lr FROM metrics WHERE job_id=? ORDER BY step LIMIT ?", (job_id, limit), ).fetchall()] def add_log(job_id, line): with _lock, _conn() as c: c.execute("INSERT INTO logs (job_id, t, line) VALUES (?,?,?)", (job_id, time.time(), line)) def get_logs(job_id, limit=300): with _lock, _conn() as c: rows = c.execute( "SELECT t, line FROM logs WHERE job_id=? ORDER BY t DESC LIMIT ?", (job_id, limit), ).fetchall() return [dict(r) for r in reversed(rows)] # ---------------------------------------------------------- community ------ _SORTS = { "recent": "COALESCE(finished_at, created_at) DESC", "loss": "best_loss ASC", "tokens": "tokens_seen DESC", "params": "n_params DESC, tokens_seen DESC", } def list_public(tier=None, sort="recent", limit=60, offset=0, username=None): """Finished runs from everyone — the site's shared shelf of models.""" q = ("SELECT * FROM jobs WHERE status='done' AND tokens_seen > 0 " "AND best_loss IS NOT NULL") args = [] if tier: q += " AND tier=?" args.append(tier) if username: q += " AND username=?" args.append(username) q += f" ORDER BY {_SORTS.get(sort, _SORTS['recent'])} LIMIT ? OFFSET ?" args += [limit, offset] with _lock, _conn() as c: return [dict(r) for r in c.execute(q, args).fetchall()] def list_active_public(limit=40): """Everything training or queued right now, from everyone.""" with _lock, _conn() as c: return [dict(r) for r in c.execute( "SELECT * FROM jobs WHERE status IN ('running','queued') " "ORDER BY CASE status WHEN 'running' THEN 0 ELSE 1 END, created_at " "LIMIT ?", (limit,)).fetchall()] def leaderboard(limit_per_tier=3): """Lowest loss per tier. Same tokenizer, data and context for everyone, so the numbers are directly comparable.""" out = {} with _lock, _conn() as c: tiers = [r[0] for r in c.execute( "SELECT DISTINCT tier FROM jobs WHERE status='done' " "AND best_loss IS NOT NULL").fetchall()] for t in tiers: out[t] = [dict(r) for r in c.execute( "SELECT * FROM jobs WHERE status='done' AND tier=? " "AND best_loss IS NOT NULL ORDER BY best_loss ASC LIMIT ?", (t, limit_per_tier)).fetchall()] return out def user_profile(username): """Public totals for one person, plus the avatar they last signed in with.""" with _lock, _conn() as c: row = c.execute( "SELECT COUNT(*) AS runs," " SUM(CASE WHEN status='done' THEN 1 ELSE 0 END) AS done," " SUM(CASE WHEN status IN ('queued','running') THEN 1 ELSE 0 END) AS active," " COALESCE(SUM(tokens_seen),0) AS tokens," " MIN(CASE WHEN status='done' THEN best_loss END) AS best_loss," " SUM(CASE WHEN pushed_repo IS NOT NULL THEN 1 ELSE 0 END) AS published," " MIN(created_at) AS first_seen, MAX(created_at) AS last_seen" " FROM jobs WHERE username=?", (username,)).fetchone() if not row or not row["runs"]: return None meta = c.execute( "SELECT display_name, avatar_url FROM jobs WHERE username=? " "ORDER BY created_at DESC LIMIT 1", (username,)).fetchone() params = c.execute( "SELECT COALESCE(SUM(n_params),0) FROM jobs WHERE username=? " "AND status='done'", (username,)).fetchone()[0] d = dict(row) d.update(username=username, params=params, display_name=(meta["display_name"] if meta else None) or username, avatar_url=meta["avatar_url"] if meta else None) return d def top_trainers(limit=12): with _lock, _conn() as c: return [dict(r) for r in c.execute( "SELECT username, display_name, avatar_url," " COUNT(*) AS runs," " SUM(CASE WHEN status='done' THEN 1 ELSE 0 END) AS done," " COALESCE(SUM(tokens_seen),0) AS tokens," " MIN(CASE WHEN status='done' THEN best_loss END) AS best_loss" " FROM jobs GROUP BY username" " ORDER BY done DESC, tokens DESC LIMIT ?", (limit,)).fetchall()] # -------------------------------------------------- durable round-trip ----- def export_run(job_id): """Everything about one run, in one JSON-serialisable dict.""" j = get_job(job_id) if not j: return None return {"job": j, "metrics": get_metrics(job_id, 5000), "logs": get_logs(job_id, 400), "schema": 1} def import_run(payload): """Insert a run exported earlier. Existing rows are left untouched.""" j = payload.get("job") or {} jid = j.get("id") if not jid: return False cols = [ "id", "username", "display_name", "avatar_url", "model_name", "tier", "n_params", "target_tokens", "status", "gpu_index", "created_at", "started_at", "finished_at", "tokens_seen", "step", "total_steps", "loss", "best_loss", "lr", "tok_per_s", "eta_s", "error", "pushed_repo", "seed", "live_step", "live_at", ] vals = [j.get(c) for c in cols] with _lock, _conn() as c: if c.execute("SELECT 1 FROM jobs WHERE id=?", (jid,)).fetchone(): return False c.execute( f"INSERT INTO jobs ({','.join(cols)}) " f"VALUES ({','.join('?' * len(cols))})", vals) for m in payload.get("metrics", []): c.execute("INSERT OR REPLACE INTO metrics " "(job_id, step, tokens, loss, lr, t) VALUES (?,?,?,?,?,?)", (jid, m.get("step"), m.get("tokens"), m.get("loss"), m.get("lr"), j.get("finished_at") or time.time())) for l in payload.get("logs", []): c.execute("INSERT INTO logs (job_id, t, line) VALUES (?,?,?)", (jid, l.get("t") or time.time(), l.get("line", ""))) return True def known_job_ids(): with _lock, _conn() as c: return {r[0] for r in c.execute("SELECT id FROM jobs").fetchall()} def stats(): with _lock, _conn() as c: row = c.execute( "SELECT " " (SELECT COUNT(*) FROM jobs WHERE status='queued') AS queued," " (SELECT COUNT(*) FROM jobs WHERE status='running') AS running," " (SELECT COUNT(*) FROM jobs WHERE status='done') AS done," " (SELECT COUNT(DISTINCT username) FROM jobs) AS users," " (SELECT COALESCE(SUM(tokens_seen),0) FROM jobs) AS tokens" ).fetchone() return dict(row)