Skip to content

SCHEMA.md

Latest commit

 

History

History
469 lines (364 loc) · 26.6 KB

SCHEMA.md

File metadata and controls

469 lines (364 loc) · 26.6 KB
Raw

SkillForge Database Schema

Database: SQLite (via aiosqlite) File: skillforge.db at project root (overridable via SKILLFORGE_DB env var) Source of truth: this document. skillforge/db/database.py::init_db must match what's documented here.

Conventions

  • Primary keys: UUID strings (id TEXT PRIMARY KEY).
  • Timestamps: ISO-8601 strings in UTC (TEXT). We use datetime.now(UTC).isoformat().
  • Booleans: INTEGER (0 or 1). SQLite has no native bool.
  • Complex fields (lists, dicts, nested objects): serialized as JSON strings in TEXT columns. json.dumps/json.loads at the query layer.
  • Foreign keys: declared but not enforced by default in SQLite. We enable PRAGMA foreign_keys = ON on every connection.
  • Indexes: added where lineage queries or list-by-run queries are hot paths.
  • Reserved run ids:
    • seed-library — the synthetic curated Gen 0 Skill library. Not created by a user POST /evolve call; instead, inserted idempotently at app startup by skillforge/db/seed_loader.py::load_seeds(). Holds len(SEED_SKILLS) SkillGenome rows in a single generation=0 row. Mode is "curated", status is "complete". The Dashboard filters this run out of its list; the Registry renders it as a featured section. Hash-based reload on every boot — if SEED_SKILLS content hash differs from the stored one (embedded in the run's specialization field), the loader deletes and recreates the run.

Tables

evolution_runs

The top-level run record. One row per POST /evolve invocation.

Column Type Nullable Notes
id TEXT PK UUID
mode TEXT NOT NULL "domain" | "meta"
specialization TEXT NOT NULL User-provided description (domain mode) or "meta"
population_size INTEGER NOT NULL Default 5
num_generations INTEGER NOT NULL Default 3
status TEXT NOT NULL "pending" | "running" | "complete" | "failed"
created_at TEXT NOT NULL ISO-8601 UTC
completed_at TEXT NULL ISO-8601 UTC, NULL until status=complete/failed
total_cost_usd REAL NOT NULL Running total of API spend. Default 0.0
max_budget_usd REAL NOT NULL Hard cap; engine aborts when total ≥ max
learning_log TEXT NOT NULL JSON array of strings. Accumulates across all generations
pareto_front_ids TEXT NOT NULL JSON array of skill_genomes.id. Final Pareto front
best_skill_id TEXT NULL FK → skill_genomes.id. Single "winner" for export
failure_reason TEXT NULL Populated when status=failed
family_id TEXT NULL v2.0. FK → skill_families.id. Set by the Taxonomist at run submission time; NULL for pre-v2.0 runs and molecular-mode runs without classification
evolution_mode TEXT NOT NULL v2.0. "molecular" | "atomic". Default "molecular" — everything existing stays backward-compatible

Indexes:

  • idx_runs_status on status (for listing active/recent runs)
  • idx_runs_created_at on created_at DESC
  • idx_runs_family on family_id — v2.0, for listing runs per family

challenges

Auto-generated by the Challenge Designer. One row per challenge per run.

Column Type Nullable Notes
id TEXT PK UUID
run_id TEXT NOT NULL FK → evolution_runs.id
prompt TEXT NOT NULL What to ask Claude when the Skill is loaded
difficulty TEXT NOT NULL "easy" | "medium" | "hard"
evaluation_criteria TEXT NOT NULL JSON dict {criterion: weight}
verification_method TEXT NOT NULL "run_tests" | "judge_review" | "both"
setup_files TEXT NOT NULL JSON dict {path: content} — starter code, test suites
gold_standard_hints TEXT NOT NULL What a great solution looks like

Indexes:

  • idx_challenges_run on run_id

skill_genomes

A single candidate Skill's full DNA + layered fitness. One row per skill per generation. Gen 0 skills and all bred children are stored here.

Column Type Nullable Notes
id TEXT PK UUID
run_id TEXT NOT NULL FK → evolution_runs.id
generation INTEGER NOT NULL 0-indexed
skill_md_content TEXT NOT NULL Full SKILL.md text
frontmatter TEXT NOT NULL JSON dict (parsed YAML frontmatter)
supporting_files TEXT NOT NULL JSON dict {relative_path: content} for scripts/, references/, assets/
traits TEXT NOT NULL JSON array of strings — extracted behavioral traits
meta_strategy TEXT NOT NULL Short approach description
parent_ids TEXT NOT NULL JSON array of skill_genomes.id. Empty for gen 0
mutations TEXT NOT NULL JSON array of mutation descriptions
mutation_rationale TEXT NOT NULL Breeder's diagnostic reasoning
maturity TEXT NOT NULL "draft" | "tested" | "hardened" | "crystallized"
generations_survived INTEGER NOT NULL How many gens this genome (or core traits) persisted
deterministic_scores TEXT NOT NULL L1: JSON dict, per-challenge
trigger_precision REAL NOT NULL L2. Default 0.0
trigger_recall REAL NOT NULL L2. Default 0.0
behavioral_signature TEXT NOT NULL L3: JSON array of action strings
pareto_objectives TEXT NOT NULL L4: JSON dict {objective: score}
is_pareto_optimal INTEGER NOT NULL L4: 0 or 1
trait_attribution TEXT NOT NULL L5: JSON dict {trait: contribution}
trait_diagnostics TEXT NOT NULL L5: JSON dict {trait: explanation}
consistency_score REAL NULL L6 (v1.1). NULL for MVP
variant_id TEXT NULL v2.0. FK-ish → variants.id. Set when a genome is the backing content of a variant in atomic evolution; NULL for molecular-mode genomes. Not a hard FK because genomes can pre-date variant rows within a single transaction

Indexes:

  • idx_genomes_run_gen on (run_id, generation) — the hot path for "get all skills for this generation"
  • idx_genomes_pareto on (run_id, is_pareto_optimal) — for Pareto front queries
  • idx_genomes_variant on variant_id — v2.0, for reverse lookup from genome to owning variant

Lineage note: parent_ids stays as a JSON column rather than a normalized join table. Lineage queries are infrequent (one GET /runs/{id}/lineage per completed run), so the simplicity of JSON outweighs query speed. If lineage becomes a hot path, we can add a lineage_edges(parent_id, child_id) table later without migrating the genome table.

generations

One row per generation per run. Aggregates per-generation metadata.

Column Type Nullable Notes
run_id TEXT PK (part) FK → evolution_runs.id
number INTEGER PK (part) 0-indexed
pareto_front TEXT NOT NULL JSON array of skill_genomes.id on this generation's Pareto front
breeding_report TEXT NOT NULL Breeder's diagnostic reasoning with trace evidence
learning_log_entries TEXT NOT NULL JSON array of new lessons discovered this generation
best_fitness REAL NOT NULL Max aggregate fitness this generation
avg_fitness REAL NOT NULL Mean aggregate fitness this generation
trait_survival TEXT NOT NULL JSON dict {trait: bool} — which traits made it to next gen
trait_emergence TEXT NOT NULL JSON array of new traits that appeared via mutation

Primary key: (run_id, number).

competition_results

One row per Skill × Challenge competition. All layered fitness fields land here.

Column Type Nullable Notes
skill_id TEXT PK (part) FK → skill_genomes.id
challenge_id TEXT PK (part) FK → challenges.id
run_id TEXT NOT NULL FK → evolution_runs.id (denormalized for query speed)
generation INTEGER NOT NULL Denormalized from genome for query speed
output_files TEXT NOT NULL JSON dict {path: content} — what the competitor wrote
trace TEXT NOT NULL JSON array of Agent SDK messages. Can be large
compiles INTEGER NOT NULL L1: 0 or 1
tests_pass INTEGER NULL L1: 0, 1, or NULL (no test suite)
lint_score REAL NULL L1
perf_metrics TEXT NOT NULL L1: JSON dict. Empty dict if no benchmarks
trigger_precision REAL NOT NULL L2
trigger_recall REAL NOT NULL L2
skill_was_loaded INTEGER NOT NULL L3: 0 or 1
instructions_followed TEXT NOT NULL L3: JSON array
instructions_ignored TEXT NOT NULL L3: JSON array
ignored_diagnostics TEXT NOT NULL L3: JSON dict {instruction: reason}
scripts_executed TEXT NOT NULL L3: JSON array
behavioral_signature TEXT NOT NULL L3: JSON array of ordered actions
pairwise_wins TEXT NOT NULL L4: JSON dict {criterion: win_count}
pareto_objectives TEXT NOT NULL L4: JSON dict
trait_contribution TEXT NOT NULL L5: JSON dict
trait_diagnostics TEXT NOT NULL L5: JSON dict
judge_reasoning TEXT NOT NULL L5: free-text judge rationale

Primary key: (skill_id, challenge_id).

Indexes:

  • idx_results_run_gen on (run_id, generation) — generation-level fitness aggregation
  • idx_results_challenge on challenge_id — pairwise comparison lookups

invite_requests

Captures email submissions from the invite-request form. Matt reviews the table manually and sends codes out-of-band — submitting a row does NOT grant access. Valid codes live in the SKILLFORGE_INVITE_CODES env var (comma-separated allowlist), not in the DB.

Column Type Nullable Notes
id TEXT PK UUID hex
email TEXT NOT NULL User-submitted email (regex-validated at API layer)
message TEXT NULL Optional "what would you use it for" message, ≤1000 chars
created_at TEXT NOT NULL ISO-8601 UTC
status TEXT NOT NULL "pending" | "approved" | "rejected" — default "pending"
notes TEXT NULL Free-form admin notes

Indexes:

  • idx_invite_requests_created on created_at DESC

Admin read endpoint GET /api/invites/requests is gated by SKILLFORGE_ADMIN_TOKEN header (X-Admin-Token).

leaked_skills

Bookkeeping for Managed Agents skills that failed best-effort teardown. Per PLAN-V1.2 architectural decision #7, the Phase 1 competitor schedules skill cleanup as a detached asyncio.create_task() so cleanup never blocks the evolution loop. Failures land here for a future batch sweeper to retry.

Column Type Nullable Notes
id TEXT PK UUID hex of the leak record (NOT the skill_id)
skill_id TEXT NOT NULL The Anthropic skill_id that failed to delete
run_id TEXT NULL Evolution run that created the skill (for backtracing)
created_at TEXT NOT NULL ISO-8601 UTC of when the leak was logged
error TEXT NULL Error message from the failed teardown call

Indexes:

  • idx_leaked_skills_created on created_at DESC

No foreign keys — this table is intentionally standalone so a leaked record survives even if the originating run is later deleted. The Anthropic built-in skills (xlsx/pptx/pdf/docx, source="anthropic") are NEVER inserted here because the wrapper's archive_skill guard refuses to even attempt deletion.

candidate_seeds

AI-generated skill packages and evolution winners flagged for potential promotion to the curated seed library. Auto-populated by two sources: (1) the POST /api/spec-assistant/generate-skill endpoint saves every successful generation, and (2) the evolution engine saves the best_skill from every completed run.

Column Type Nullable Notes
id TEXT PK UUID
source TEXT NOT NULL "generated" (AI spec assistant) | "evolved" (evolution winner)
source_run_id TEXT NULL FK → evolution_runs.id. Set for "evolved" source
source_skill_id TEXT NULL FK → skill_genomes.id. Set for "evolved" source
title TEXT NOT NULL Skill display name
specialization TEXT NOT NULL The domain specialization string
category TEXT NOT NULL Default "uncategorized". Set during review
skill_md_content TEXT NOT NULL Full SKILL.md content
supporting_files TEXT NOT NULL JSON dict {path: content}. Default "{}"
traits TEXT NOT NULL JSON array of strings. Default "[]"
fitness_score REAL NULL Aggregate fitness from evolution (NULL for generated)
status TEXT NOT NULL "pending" | "approved" | "rejected" | "promoted". Default "pending"
created_at TEXT NOT NULL ISO-8601 UTC
promoted_at TEXT NULL ISO-8601 UTC. Set when status transitions to "promoted"
notes TEXT NULL Admin review notes

Indexes:

  • idx_candidate_seeds_status on (status, created_at DESC) — for listing pending candidates

No FK constraints — candidates may reference runs/skills that are later cleaned up. The skill_md_content is a snapshot at the time of saving.

Promotion workflow: pending → admin reviews → approved (queued for inclusion) → promoted (added to SEED_SKILLS at next deploy/restart). Alternatively, pending → rejected (with notes explaining why).

taxonomy_nodes (v2.0)

The hierarchy that classifies skills: domain → focus → language. Populated at boot from the 16 Gen 0 seeds (Wave 1-3) and extended at runtime by the Taxonomist agent (Wave 2-1) when no existing node fits a new specialization.

Column Type Nullable Notes
id TEXT PK UUID
level TEXT NOT NULL "domain" | "focus" | "language"
slug TEXT NOT NULL kebab-case slug, unique within (level, parent_id)
label TEXT NOT NULL Human-readable display label
parent_id TEXT NULL FK → taxonomy_nodes.id. NULL for level="domain" rows. A focus row points at a domain; a language row points at a focus. Self-FK with ON DELETE CASCADE
description TEXT NOT NULL Free-form description; default ''
created_at TEXT NOT NULL ISO-8601 UTC

Unique constraint: (level, slug, parent_id) so the same slug can exist at different levels or under different parents but never as a true duplicate.

Indexes:

  • idx_taxonomy_nodes_level_slug on (level, slug)
  • idx_taxonomy_nodes_parent on parent_id

skill_families (v2.0)

A named lineage that groups variants sharing a specialization. One family per Taxonomist classification. The winning assembled composite is referenced by best_assembly_id.

Column Type Nullable Notes
id TEXT PK UUID
slug TEXT NOT NULL UNIQUE Kebab-case, globally unique family slug
label TEXT NOT NULL Display label
specialization TEXT NOT NULL The specialization text the Taxonomist classified
domain_id TEXT NULL FK → taxonomy_nodes.id with ON DELETE SET NULL
focus_id TEXT NULL FK → taxonomy_nodes.id with ON DELETE SET NULL
language_id TEXT NULL FK → taxonomy_nodes.id with ON DELETE SET NULL
tags TEXT NOT NULL JSON array of strings. Default '[]'
decomposition_strategy TEXT NOT NULL "atomic" | "molecular". Default "molecular". Set by the Taxonomist during classification
best_assembly_id TEXT NULL skill_genomes.id of the current winning composite. No hard FK to avoid FK ordering headaches at insert time
created_at TEXT NOT NULL ISO-8601 UTC

Indexes:

  • idx_skill_families_slug on slug
  • idx_skill_families_domain on domain_id
  • idx_skill_families_focus on focus_id
  • idx_skill_families_language on language_id

variants (v2.0)

A single evolved variant within a family's dimension. The atomic unit of v2.0 evolution. One row per variant per dimension; the winning variant per (family_id, dimension) has is_active=1.

Column Type Nullable Notes
id TEXT PK UUID
family_id TEXT NOT NULL FK → skill_families.id ON DELETE CASCADE
dimension TEXT NOT NULL The dimension slug this variant targets (e.g., "mock-strategy")
tier TEXT NOT NULL "foundation" | "capability"
genome_id TEXT NOT NULL FK → skill_genomes.id ON DELETE CASCADE. The underlying SkillGenome for this variant
fitness_score REAL NOT NULL Default 0.0. Aggregate fitness from the Reviewer
is_active INTEGER NOT NULL 0/1. Default 0. Exactly one variant per (family_id, dimension) should have this set
evolution_id TEXT NULL FK → variant_evolutions.id ON DELETE SET NULL. The mini-evolution run that produced this variant
created_at TEXT NOT NULL ISO-8601 UTC

Indexes:

  • idx_variants_family_dim on (family_id, dimension) — primary lookup
  • idx_variants_family_active on (family_id, is_active) — "get the winning variants for this family"
  • idx_variants_genome on genome_id — reverse lookup

variant_evolutions (v2.0)

A mini-evolution run targeting one dimension of a family. Multiple rows per parent evolution_runs row — one per dimension that was atomically evolved.

Column Type Nullable Notes
id TEXT PK UUID
family_id TEXT NOT NULL FK → skill_families.id ON DELETE CASCADE
dimension TEXT NOT NULL Dimension slug
tier TEXT NOT NULL "foundation" | "capability"
parent_run_id TEXT NOT NULL FK → evolution_runs.id ON DELETE CASCADE. The top-level run that scheduled this mini-evolution
population_size INTEGER NOT NULL Default 2
num_generations INTEGER NOT NULL Default 2
status TEXT NOT NULL "pending" | "running" | "complete" | "failed"
winner_variant_id TEXT NULL variants.id of the winning variant. No hard FK (circular with variants.evolution_id); enforced at the query layer
foundation_genome_id TEXT NULL For capability tier: the winning foundation genome used as grounding context. FK → skill_genomes.id ON DELETE SET NULL
challenge_id TEXT NULL FK → challenges.id ON DELETE SET NULL. The focused challenge the Scientist designed for this dimension
created_at TEXT NOT NULL ISO-8601 UTC
completed_at TEXT NULL ISO-8601 UTC. Set on terminal status

Indexes:

  • idx_variant_evolutions_family on (family_id, dimension)
  • idx_variant_evolutions_parent_run on parent_run_id

Circular-FK note: variants.evolution_id references variant_evolutions.id, and variant_evolutions.winner_variant_id conceptually references variants.id. SQLite dislikes declaring both hard FKs because of insert ordering. We declare only variants.evolution_id as a hard FK and validate winner_variant_id at the query layer. CREATE order is: variant_evolutions then variants, so the forward FK always resolves.

run_events

Persisted event stream for post-mortem debugging. Every WebSocket event is also written here via fire-and-forget task.

Column Type Nullable Notes
id INTEGER PK AUTOINCREMENT
run_id TEXT NOT NULL FK → evolution_runs.id
event_type TEXT NOT NULL Event type string
payload TEXT NOT NULL JSON event payload
timestamp TEXT NOT NULL ISO-8601 UTC

Indexes:

  • idx_run_events_run_id on (run_id, id)

benchmark_results

Raw model baseline performance on SKLD-bench challenges — no skill guidance, just the model solving the challenge cold. One row per (challenge, model) pair. Used to measure skill lift and calibrate challenge difficulty.

Column Type Nullable Notes
id TEXT PK UUID
family_slug TEXT NOT NULL e.g. "elixir-phoenix-liveview"
challenge_id TEXT NOT NULL Challenge file ID e.g. "elixir-phoenix-liveview-medium-05"
challenge_path TEXT NOT NULL Relative path to challenge JSON
model TEXT NOT NULL e.g. "claude-sonnet-4-6", "claude-opus-4-6"
tier TEXT NOT NULL "easy" | "medium" | "hard" | "legendary"
dimension TEXT NOT NULL Primary capability dimension from challenge scoring
score REAL NOT NULL L1 score from score.py (0.0–1.0)
passed INTEGER NOT NULL 0 or 1
objectives TEXT NOT NULL JSON dict of per-objective results from scorer
output_files TEXT NOT NULL JSON dict {path: content} — what the model produced
scores TEXT NULL JSON dict with multi-level composite breakdown: {l0, compile, ast, behavioral, template, brevity, composite, weights}. Added in v2.1.3 Phase 0. NULL for rows scored before the overhaul
total_tokens INTEGER NOT NULL Total token count for the dispatch
duration_ms INTEGER NOT NULL Wall-clock time for the dispatch
error TEXT NULL NULL if successful, error message if dispatch failed
created_at TEXT NOT NULL ISO-8601 UTC
scores TEXT NOT NULL v2.1.3. JSON dict with multi-level score breakdown: {l0, compile, ast, behavioral, template, composite}. Default '{}'. Added via additive migration; existing rows get the default

Unique constraint: (challenge_id, model) — one result per challenge per model. Re-running overwrites.

Indexes:

  • idx_benchmark_challenge_model UNIQUE on (challenge_id, model) — primary lookup + upsert
  • idx_benchmark_family on (family_slug, model) — per-family reports
  • idx_benchmark_tier on (tier, model) — tier-level aggregation

dispatch_transcripts (v2.1.3)

Full audit trail for every agent dispatch — competitor outputs, benchmark runs, spawner variants, engineer composites. Every dispatch in the pipeline gets a row here so outputs are never lost to /tmp cleanup.

Column Type Nullable Notes
id TEXT PK Stable ID (e.g., "deep-dive-sonnet-noskill-hard-07") or UUID
run_id TEXT NULL FK → evolution_runs.id. NULL for benchmarks
benchmark_id TEXT NULL FK → benchmark_results.id. NULL for evolution dispatches
family_slug TEXT NOT NULL e.g. "elixir-phoenix-liveview"
challenge_id TEXT NOT NULL Challenge file ID
dispatch_type TEXT NOT NULL "competitor" | "spawner" | "engineer" | "benchmark" | "deep_dive"
model TEXT NOT NULL e.g. "claude-sonnet-4-6"
skill_variant TEXT NULL Variant SKILL.md name. NULL for no-skill dispatches
prompt TEXT NOT NULL Full prompt sent to the model
raw_response TEXT NOT NULL Complete model response text
extracted_files TEXT NOT NULL JSON dict {path: content} — code files extracted from response
scores TEXT NOT NULL JSON dict with multi-level score breakdown. Default '{}'
total_tokens INTEGER NOT NULL Default 0
duration_ms INTEGER NOT NULL Default 0
error TEXT NULL NULL if successful
created_at TEXT NOT NULL ISO-8601 UTC

No foreign key constraints — transcripts are standalone records that survive even if the originating run or benchmark is deleted.

Indexes:

  • idx_dispatch_family on (family_slug, challenge_id) — per-challenge lookups
  • idx_dispatch_type on dispatch_type — filter by dispatch type
  • idx_dispatch_run on run_id — per-run audit trail

Foreign key relationships

evolution_runs (1) ──┬──< (N) challenges
                     ├──< (N) skill_genomes ──< (N) competition_results (challenge_id)
                     ├──< (N) generations
                     ├──< (N) variant_evolutions       (v2.0)
                     └── family_id ──> skill_families  (v2.0)

skill_families (1) ──┬──< (N) variants
                     └──< (N) variant_evolutions

taxonomy_nodes (self) ──< (N) taxonomy_nodes (parent_id)
taxonomy_nodes       <── skill_families.domain_id / focus_id / language_id (SET NULL)

variants.genome_id   ──> skill_genomes (CASCADE)
variants.evolution_id ──> variant_evolutions (SET NULL)
variant_evolutions.foundation_genome_id ──> skill_genomes (SET NULL)
variant_evolutions.challenge_id         ──> challenges    (SET NULL)

All v1.x foreign keys are ON DELETE CASCADE so deleting a run removes everything it owns. v2.0 foreign keys use SET NULL for "soft" relationships (taxonomy nodes, foundation genomes, winning variants) so deleting a taxonomy node or genome doesn't cascade-delete otherwise-healthy families or variant evolutions.

Trace size concerns

competition_results.trace can be tens of KB per row (15 Agent SDK turns × ~1 KB each). At 5 pop × 3 gen × 3 challenges = 45 rows per run, that's ~500 KB–2 MB of trace data per run. SQLite handles this fine; if it becomes a problem we compress with zlib before insert.

Initialization

skillforge/db/database.py::init_db():

  1. Open connection
  2. Execute PRAGMA foreign_keys = ON
  3. Execute CREATE TABLE IF NOT EXISTS for each table in dependency order: evolution_runs → challenges → skill_genomes → generations → competition_results
  4. Execute CREATE INDEX IF NOT EXISTS for each index
  5. Commit, close

Migrations

v2.0 migration (Wave 1-2) introduced an additive, idempotent column-migration hook inside init_db(). On every boot the init routine:

  1. Runs CREATE TABLE IF NOT EXISTS for every table in dependency order (v1.x and v2.0).
  2. Walks a _ADDITIVE_COLUMN_MIGRATIONS list — (table, column, column_sql) triples — and for each entry:
    • Probes PRAGMA table_info(<table>) for the column.
    • If the column is missing, runs ALTER TABLE <table> ADD COLUMN <column> <column_sql>.
    • If present, it's a no-op.
  3. Creates indexes.

This means upgrading a pre-v2.0 database is zero-touch: restart the server, and the missing family_id, evolution_mode, and variant_id columns are added without data loss. Fresh installs already get the columns from the CREATE TABLE DDL, so the ALTER is a no-op there.

Future schema changes should follow the same pattern: add columns via the additive list, never drop or rename columns without a migration script. For structural changes (new tables) just add a new _CREATE_… DDL in dependency order and a DROP entry at the head of _DROP_ORDER.

If we ever need destructive migrations (drop column, change type), we'll add a lightweight version column to a new meta(key, value) table and hand-write migrations.