spikerj/spikersoft-issues
1
0
Fork 0
Code Issues 196 Pull Requests Actions Packages Projects Releases Wiki Activity

architecture: SQL Playground as first-class LessonStrategy — DuckDB WASM + worker grading + language-runner parity #132

New Issue
Closed
opened 2026-05-13 00:14:18 +00:00 by spikerj · 2 comments
spikerj commented 2026-05-13 00:14:18 +00:00
Owner
Copy Link
Copy Source

Elevator pitch

SQL playgrounds should plug into SpikerSoft’s existing lesson catalog, prerequisites, offline progression, authoritative regrading, language-runner shell, IndexedDB queues, runtime adapters, and worker RPC — not as “JSON-in-an-editor”, but as a first-class LessonStrategy runtime beside C#/Python/JavaScript/Regex.

Non-negotiable: the API never executes learner SQL directly; grading follows lesson.regrade.requests → RabbitMQ → SQL grading worker → response, matching today’s offline regrade pattern.

What we already have (leverage)

  • Lesson catalogs · prerequisite systems · offline progression · browser runtimes
  • Authoritative regrading · runtime abstraction (platform/language-runner)
  • Worker-based execution · IndexedDB sync · runtime adapters · lesson strategy hydration · curriculum auditing philosophy (No concept used before introduction)

SQL should reuse these seams with minimal invention.

Backend: curriculum & strategy

  • Do not ship SQL curriculum as loose JSON blobs only.

  • SqlLessonDefinition / first-class lesson strategy: align with implicit curriculum contracts; each lesson declares:

    • ConceptsIntroduced
    • ConceptsRequired
    • ConceptsForbidden

  • New curriculum tree (mirror existing tier naming):

    • SpikerSoft.Business/Domain/Lessons/Curriculum/Sql/
    • Example tiers: Tier00_Welcome/, Tier01_Select/, Tier02_Filtering/, Tier03_Sorting/, Tier04_Aggregation/, Tier05_Joins/, …
    • Pedagogy: inherit exactly the “no concept before introduction” rule from the README / auditing philosophy.

  • Critical SQL-specific: schema snapshots

    SQL environments are mutable. Each lesson needs deterministic reset:

    • Embed assets e.g. schema.sql, seed.sql (and/or equivalent on LessonStrategyBase).
    • Example shape sketch (refine during implementation):
public class SqlLessonDefinition : LessonStrategyBase
{
    public string SchemaSql { get; }
    public string SeedSql { get; }
    public string[] ConceptsIntroduced { get; }
    public string[] ConceptsForbidden { get; }
    public SqlValidationStrategy ValidationStrategy { get; }
}

Backend: grading runtime scaling (same factories as peers)

  • Add LessonGradingRuntime.Sql and wire through:

    • ILessonGradingExecutorFactory
    • IFreePlayCodeExecutorFactory (if SQL free-play is in scope)
    • IStudentCodeHintAnalyzerFactory

  • New executor: SqlLessonGradingExecutor under:

    SpikerSoft.Business/Domain/CodeExecution/Execution/

    alongside RoslynLessonGradingExecutor, PythonLessonGradingExecutor, JavaScriptLessonGradingExecutor, RegexLessonGradingExecutor, etc.

  • Server runtime: Prefer DuckDB server-side (not EF, not SQLite as the primary story) for:

    parity with browser WASM semantics, analytical SQL, and future Quack alignment.

  • Traffic path: Offline / batch regrade MUST use existing worker RPC pattern (already documented for authoritative regrading); SQL stays out of SpikerSoft.Api execution path.

Frontend: language-runner parity (do not fork UI)

  • libraries/features/dev-tools-sql-runner/ modeled on:

    dev-tools-csharp-runner, dev-tools-python-runner, dev-tools-javascript-runner.

  • SQL playground must not invent a separate shell: wrap platform/language-runner via:

    • LANGUAGE_RUNNER_CONFIG
    • Runtime adapters · progress sync · lesson panes · tutorial panels · offline queue · lesson sidebar

  • sql-runtime.adapter.ts analogous to:

    csharp-runtime.adapter.ts, python-runtime.adapter.ts, javascript-runtime.adapter.ts.

    Responsibilities:

    • Execute SQL locally (browser)
    • Load / reset lesson DB
    • Return result grids
    • Map SQL dialect errors → lesson-friendly output
    • Submit grading attempts / integrate queue & attempt tokens

Browser runtime

README direction: DuckDB WASM

  • Web Worker-hosted, lazy-loaded, preloadable via Offline / Site Settings (same ergonomics as Pyodide / QuickJS / Roslyn WASM).

Hints: SqlStudentCodeHintAnalyzer

Extend pre-flight hint architecture for SQL, e.g.:

  • Missing GROUP BY · Cartesian joins · SELECT * · aggregate misuse · ambiguous columns · alias confusion · WHERE vs HAVING

This is a differentiator versus generic editors.

Product synergy (later phases)

  • games-clue-for-sql: unify curriculum SQL + detective gameplay SQL on the same runtime/adapters.

  • Quack / future: Collaborative query sessions, instructor classrooms, shared DB worlds — piggyback on existing offline/worker/SignalR foundations without reinventing transport.

Definition of done checklist (architecture)

  • SQL integrates with language-runner (same shell UX)
  • Offline execution + offline grading queue + attemptToken / IndexedDB path
  • Authoritative worker regrading only (API never executes student SQL)
  • Runtime preload path in Site / Offline settings
  • Curriculum / prerequisite contracts on strategies
  • Tutorial panels · lesson hydration · strategy registry wired
  • SqlStudentCodeHintAnalyzer in factory registry
  • Lesson assets: schema.sql / seed.sql (or equivalent) per lesson
  • LessonGradingRuntime.Sql registered in grading + hint factories

Guiding principle

Do NOT treat SQL as “just another editor”. Treat it as a first-class runtime in SpikerSoft’s educational OS.

Labels

enhancement · architecture · frontend · backend · curriculum · offline

## Elevator pitch SQL playgrounds should plug into SpikerSoft’s existing **lesson catalog, prerequisites, offline progression, authoritative regrading, language-runner shell, IndexedDB queues, runtime adapters, and worker RPC** — not as “JSON-in-an-editor”, but as a **first-class `LessonStrategy` runtime** beside C#/Python/JavaScript/Regex. **Non-negotiable:** the API never executes learner SQL directly; grading follows **`lesson.regrade.requests` → RabbitMQ → SQL grading worker → response**, matching today’s offline regrade pattern. --- ## What we already have (leverage) - Lesson catalogs · prerequisite systems · offline progression · browser runtimes - Authoritative regrading · runtime abstraction (`platform/language-runner`) - Worker-based execution · IndexedDB sync · runtime adapters · lesson strategy hydration · curriculum auditing philosophy (`No concept used before introduction`) SQL should reuse these seams with **minimal invention**. --- ## Backend: curriculum & strategy - **Do not** ship SQL curriculum as loose JSON blobs only. - **`SqlLessonDefinition` / first-class lesson strategy:** align with implicit curriculum contracts; each lesson declares: - `ConceptsIntroduced` - `ConceptsRequired` - `ConceptsForbidden` - **New curriculum tree** (mirror existing tier naming): - `SpikerSoft.Business/Domain/Lessons/Curriculum/Sql/` - Example tiers: `Tier00_Welcome/`, `Tier01_Select/`, `Tier02_Filtering/`, `Tier03_Sorting/`, `Tier04_Aggregation/`, `Tier05_Joins/`, … - Pedagogy: inherit **exactly** the “no concept before introduction” rule from the README / auditing philosophy. - **Critical SQL-specific: schema snapshots** SQL environments are mutable. Each lesson needs **deterministic reset**: - Embed assets e.g. `schema.sql`, `seed.sql` (and/or equivalent on `LessonStrategyBase`). - Example shape sketch (refine during implementation): ```csharp public class SqlLessonDefinition : LessonStrategyBase { public string SchemaSql { get; } public string SeedSql { get; } public string[] ConceptsIntroduced { get; } public string[] ConceptsForbidden { get; } public SqlValidationStrategy ValidationStrategy { get; } } ``` --- ## Backend: grading runtime scaling (same factories as peers) - Add **`LessonGradingRuntime.Sql`** and wire through: - `ILessonGradingExecutorFactory` - `IFreePlayCodeExecutorFactory` (if SQL free-play is in scope) - `IStudentCodeHintAnalyzerFactory` - **New executor:** `SqlLessonGradingExecutor` under: `SpikerSoft.Business/Domain/CodeExecution/Execution/` alongside `RoslynLessonGradingExecutor`, `PythonLessonGradingExecutor`, `JavaScriptLessonGradingExecutor`, `RegexLessonGradingExecutor`, etc. - **Server runtime:** Prefer **DuckDB** server-side (**not EF, not SQLite** as the primary story) for: parity with browser WASM semantics, analytical SQL, and future **Quack** alignment. - **Traffic path:** Offline / batch regrade MUST use existing **worker RPC** pattern (already documented for authoritative regrading); SQL stays out of `SpikerSoft.Api` execution path. --- ## Frontend: language-runner parity (do not fork UI) - **`libraries/features/dev-tools-sql-runner/`** modeled on: `dev-tools-csharp-runner`, `dev-tools-python-runner`, `dev-tools-javascript-runner`. - SQL playground **must not** invent a separate shell: wrap **`platform/language-runner`** via: - `LANGUAGE_RUNNER_CONFIG` - Runtime adapters · progress sync · lesson panes · tutorial panels · offline queue · lesson sidebar - **`sql-runtime.adapter.ts`** analogous to: `csharp-runtime.adapter.ts`, `python-runtime.adapter.ts`, `javascript-runtime.adapter.ts`. Responsibilities: - Execute SQL locally (browser) - Load / reset lesson DB - Return result grids - Map SQL dialect errors → lesson-friendly output - Submit grading attempts / integrate queue & attempt tokens --- ## Browser runtime README direction: **DuckDB WASM** - Web Worker-hosted, lazy-loaded, preloadable via **Offline / Site Settings** (same ergonomics as Pyodide / QuickJS / Roslyn WASM). --- ## Hints: `SqlStudentCodeHintAnalyzer` Extend pre-flight hint architecture for SQL, e.g.: - Missing `GROUP BY` · Cartesian joins · `SELECT *` · aggregate misuse · ambiguous columns · alias confusion · `WHERE` vs `HAVING` This is a differentiator versus generic editors. --- ## Product synergy (later phases) - **`games-clue-for-sql`:** unify curriculum SQL + detective gameplay SQL on the **same** runtime/adapters. - **Quack / future:** Collaborative query sessions, instructor classrooms, shared DB worlds — piggyback on existing offline/worker/SignalR foundations without reinventing transport. --- ## Definition of done checklist (architecture) - [ ] SQL integrates with **language-runner** (same shell UX) - [ ] **Offline execution** + **offline grading queue** + **attemptToken** / IndexedDB path - [ ] **Authoritative worker regrading** only (API never executes student SQL) - [ ] **Runtime preload** path in Site / Offline settings - [ ] **Curriculum / prerequisite contracts** on strategies - [ ] Tutorial panels · lesson hydration · strategy registry wired - [ ] **`SqlStudentCodeHintAnalyzer`** in factory registry - [ ] Lesson assets: **`schema.sql` / `seed.sql`** (or equivalent) per lesson - [ ] `LessonGradingRuntime.Sql` registered in grading + hint factories --- ## Guiding principle Do NOT treat SQL as “just another editor”. Treat it as a **first-class runtime in SpikerSoft’s educational OS**. ## Labels `enhancement` · `architecture` · `frontend` · `backend` · `curriculum` · `offline`
spikerj commented 2026-05-15 01:43:23 +00:00
Author
Owner
Copy Link
Copy Source

Status update — 2026-05-14

First-pass landing of the SQL playground. 7 of 9 DoD bullets are now complete; the remaining 2 have dedicated follow-up tickets so this issue stays open until they close.

Done

  • ✅ Curriculum / prerequisite contracts on strategies

    • SpikerSoft.Business/Domain/Lessons/Curriculum/Sql/ houses 17 chapters (Chapter00_Welcome … Chapter16_Recipes)
    • 58 lesson strategies total (5 tutorials + 53 graded challenges) in the 60000-69999 band
    • SqlLessonStrategyBase mirrors X86LessonStrategyBase / RegexLessonStrategyBase
    • SqlLessonIntroduced partitions concepts into 8 buckets (statements / clauses / joins / operators / aggregates / functions / constructs / window functions) with monotonic prereq closure enforced by SqlCurriculumTests.SqlCurriculum_IntroducedTokensRespectPrerequisites
    • .cursor/rules/sql-curriculum-pedagogy.mdc codifies the authoring contract

  • ✅ Lesson assets: schema.sql / seed.sql per lesson (with a twist)

    • Implemented as three isomorphic themed schemas (Travel / VideoGames / Animals) the student picks at the toolbar
    • Same nine tables, same relationship graph, same row counts; only the table / column names and seed values differ
    • Each lesson ships per-theme bindings (SqlThemeBinding) so the same SQL pattern teaches the same concept in whichever domain the student picks
    • Smoke test SqlChallenge_ExpectedResultIsThemeConsistent asserts the cross-theme shape match

  • ✅ Tutorial panels · lesson hydration · strategy registry wired

    • Chapter 0 ships 5 narrative tutorials (What is SQL, The relational model, Pick your world, The schema three ways, How grading works)
    • LessonCatalogHydrationService now emits Language = "SQL" for LessonGradingRuntime.DuckDbSql
    • All 58 strategies auto-register through the existing assembly scan in LessonStrategyServiceCollectionExtensions
    • GetLessonAttemptQueryHandler persists OfflineLessonAttempt snapshots for DuckDbSql so offline grading works after first online open

  • ✅ SQL integrates with language-runner (same shell UX)

    • New feature lib @spikersoft/feature-dev-tools-sql-runner wraps the shared LanguageRunner exactly like the C# / Python / JS runners
    • SQL_LANGUAGE_RUNNER_CONFIG + SqlRuntimeAdapter plug into LANGUAGE_RUNNER_CONFIG / LANGUAGE_RUNTIME_ADAPTER
    • Theme picker sits above the runner so the choice is visible in both sandbox and lesson modes
    • Route registered at /tools/(tools:sql-playground) (with ?resume=1 when authenticated, matching the other languages)

  • ✅ Offline execution + offline grading queue + attemptToken / IndexedDB path

    • SqlLessonGraderService grades entirely in the browser via DuckDB-WASM
    • All six SqlExpectedResult variants implemented: ExactResultSet, UnorderedResultSet, RowCount, ScalarValue, ColumnSchema, ContainsRows
    • progressSync.enqueue writes language: "sql" items to the existing IndexedDB outbox with attemptToken + studentCode
    • Lesson resume from offline cache works (SqlRunnerService.getAttempt falls back to LessonOfflineCacheService when offline or on API error)

  • ✅ Runtime preload path in Site / Offline settings

    • DuckDB-WASM bundles already live in WASM_MODULE_REGISTRY and the offline-cache verifier (wasm-verifier-bootstrap.ts) registered them when the standalone DuckDB tool landed. The lesson runner shares the same /assets/duckdb-wasm/* bundle, so existing preload UI Just Works

  • ✅ Cardinal-rule smoke tests

    • SqlCurriculumTests (~9 theories x 58 lessons = ~522 cases) covers: lesson-number range, prereq integrity, plan deserialization, theme parity, cross-theme shape consistency, reference-solution presence, and the cardinal-rule token check
    • Compound-token handling for ORDER BY / IS NOT NULL / INNER JOIN / UNION ALL / CASE WHEN / PARTITION BY
    • SqlReferenceSolutions.Map ships 174 canonical reference queries (58 lessons × 3 themes)
    • Final test count: 2445 backend curriculum tests + 1755 frontend tests, all green

Open (tracked as follow-ups)

  • ❌ Authoritative worker regrading only (API never executes student SQL) → #138

    • SqlLessonGradingExecutor + DuckDB.NET.Data NuGet + worker registration
    • Until this lands, offline-graded passes sit in the outbox as unverified

  • ❌ SqlStudentCodeHintAnalyzer in factory registry → #141

    • Pre-flight hints for the classic SQL pitfalls (missing GROUP BY, Cartesian joins, WHERE vs HAVING, = NULL, ambiguous columns, alias confusion, SELECT * with GROUP BY)
    • The differentiator vs. a generic SQL editor that's called out in the issue body

  • ❌ LessonGradingRuntime.Sql registered in grading + hint factories — partial:

    • Enum value LessonGradingRuntime.DuckDbSql = 5 added ✅
    • Grading-factory registration: blocked on #138
    • Hint-factory registration: blocked on #141

Additional follow-ups (polish, not on the original DoD)

  • #139 — sandbox starter schema (the empty DuckDB connection isn't beginner-friendly)
  • #140 — theme-switch UX polish (output reset, lesson-instructions reload, toast feedback)

Recommendation

Keep #132 open until #138 and #141 close. The two unchecked DoD bullets both concern the worker-side trust boundary the issue body calls non-negotiable ("the API never executes learner SQL directly; grading follows lesson.regrade.requests → RabbitMQ → SQL grading worker"). Without #138 the regrade pipeline isn't actually wired; without #141 the hint analyzer isn't in the factory registry.

The shipped surface is fully functional for students today — they can navigate to Learn → Coding → SQL, pick a theme, work through 53 graded lessons + 5 tutorials, grade locally, and have completions survive a refresh. The remaining work is the server-side trust boundary and pre-flight pedagogy hints, both well-scoped in their dedicated tickets.

## Status update — 2026-05-14 First-pass landing of the SQL playground. **7 of 9 DoD bullets are now complete**; the remaining 2 have dedicated follow-up tickets so this issue stays open until they close. ### Done - ✅ **Curriculum / prerequisite contracts on strategies** - `SpikerSoft.Business/Domain/Lessons/Curriculum/Sql/` houses 17 chapters (`Chapter00_Welcome` … `Chapter16_Recipes`) - 58 lesson strategies total (5 tutorials + 53 graded challenges) in the 60000-69999 band - `SqlLessonStrategyBase` mirrors `X86LessonStrategyBase` / `RegexLessonStrategyBase` - `SqlLessonIntroduced` partitions concepts into 8 buckets (statements / clauses / joins / operators / aggregates / functions / constructs / window functions) with monotonic prereq closure enforced by `SqlCurriculumTests.SqlCurriculum_IntroducedTokensRespectPrerequisites` - `.cursor/rules/sql-curriculum-pedagogy.mdc` codifies the authoring contract - ✅ **Lesson assets: `schema.sql` / `seed.sql` per lesson** (with a twist) - Implemented as **three isomorphic themed schemas** (`Travel` / `VideoGames` / `Animals`) the student picks at the toolbar - Same nine tables, same relationship graph, same row counts; only the table / column names and seed values differ - Each lesson ships per-theme bindings (`SqlThemeBinding`) so the same SQL pattern teaches the same concept in whichever domain the student picks - Smoke test `SqlChallenge_ExpectedResultIsThemeConsistent` asserts the cross-theme shape match - ✅ **Tutorial panels · lesson hydration · strategy registry wired** - Chapter 0 ships 5 narrative tutorials (`What is SQL`, `The relational model`, `Pick your world`, `The schema three ways`, `How grading works`) - `LessonCatalogHydrationService` now emits `Language = "SQL"` for `LessonGradingRuntime.DuckDbSql` - All 58 strategies auto-register through the existing assembly scan in `LessonStrategyServiceCollectionExtensions` - `GetLessonAttemptQueryHandler` persists `OfflineLessonAttempt` snapshots for `DuckDbSql` so offline grading works after first online open - ✅ **SQL integrates with language-runner (same shell UX)** - New feature lib `@spikersoft/feature-dev-tools-sql-runner` wraps the shared `LanguageRunner` exactly like the C# / Python / JS runners - `SQL_LANGUAGE_RUNNER_CONFIG` + `SqlRuntimeAdapter` plug into `LANGUAGE_RUNNER_CONFIG` / `LANGUAGE_RUNTIME_ADAPTER` - Theme picker sits above the runner so the choice is visible in both sandbox and lesson modes - Route registered at `/tools/(tools:sql-playground)` (with `?resume=1` when authenticated, matching the other languages) - ✅ **Offline execution + offline grading queue + attemptToken / IndexedDB path** - `SqlLessonGraderService` grades entirely in the browser via DuckDB-WASM - All six `SqlExpectedResult` variants implemented: `ExactResultSet`, `UnorderedResultSet`, `RowCount`, `ScalarValue`, `ColumnSchema`, `ContainsRows` - `progressSync.enqueue` writes `language: "sql"` items to the existing IndexedDB outbox with `attemptToken` + `studentCode` - Lesson resume from offline cache works (`SqlRunnerService.getAttempt` falls back to `LessonOfflineCacheService` when offline or on API error) - ✅ **Runtime preload path in Site / Offline settings** - DuckDB-WASM bundles already live in [`WASM_MODULE_REGISTRY`](https://git.spikersoft.com/spikerj/spikersoft/src/branch/main/spikersoft-angular/projects/spikersoft/src/app/_services/offline-cache/wasm-module-registry.ts) and the offline-cache verifier (`wasm-verifier-bootstrap.ts`) registered them when the standalone DuckDB tool landed. The lesson runner shares the same `/assets/duckdb-wasm/*` bundle, so existing preload UI Just Works - ✅ **Cardinal-rule smoke tests** - `SqlCurriculumTests` (~9 theories x 58 lessons = ~522 cases) covers: lesson-number range, prereq integrity, plan deserialization, theme parity, cross-theme shape consistency, reference-solution presence, and the cardinal-rule token check - Compound-token handling for `ORDER BY` / `IS NOT NULL` / `INNER JOIN` / `UNION ALL` / `CASE WHEN` / `PARTITION BY` - `SqlReferenceSolutions.Map` ships 174 canonical reference queries (58 lessons × 3 themes) - **Final test count: 2445 backend curriculum tests + 1755 frontend tests, all green** ### Open (tracked as follow-ups) - ❌ **Authoritative worker regrading only (API never executes student SQL)** → #138 - `SqlLessonGradingExecutor` + `DuckDB.NET.Data` NuGet + worker registration - Until this lands, offline-graded passes sit in the outbox as `unverified` - ❌ **`SqlStudentCodeHintAnalyzer` in factory registry** → #141 - Pre-flight hints for the classic SQL pitfalls (missing GROUP BY, Cartesian joins, WHERE vs HAVING, `= NULL`, ambiguous columns, alias confusion, SELECT * with GROUP BY) - The differentiator vs. a generic SQL editor that's called out in the issue body - ❌ **`LessonGradingRuntime.Sql` registered in grading + hint factories** — partial: - Enum value `LessonGradingRuntime.DuckDbSql = 5` added ✅ - Grading-factory registration: blocked on #138 - Hint-factory registration: blocked on #141 ### Additional follow-ups (polish, not on the original DoD) - #139 — sandbox starter schema (the empty DuckDB connection isn't beginner-friendly) - #140 — theme-switch UX polish (output reset, lesson-instructions reload, toast feedback) ### Recommendation Keep #132 open until #138 and #141 close. The two unchecked DoD bullets both concern the worker-side trust boundary the issue body calls **non-negotiable** ("the API never executes learner SQL directly; grading follows `lesson.regrade.requests` → RabbitMQ → SQL grading worker"). Without #138 the regrade pipeline isn't actually wired; without #141 the hint analyzer isn't in the factory registry. The shipped surface is fully functional for students today — they can navigate to Learn → Coding → SQL, pick a theme, work through 53 graded lessons + 5 tutorials, grade locally, and have completions survive a refresh. The remaining work is the server-side trust boundary and pre-flight pedagogy hints, both well-scoped in their dedicated tickets.
spikerj commented 2026-05-15 02:12:27 +00:00
Author
Owner
Copy Link
Copy Source

All DoD bullets closed — 2026-05-14

Follow-ups landed:

  • #138 ✅ — SqlLessonGradingExecutor + DuckDB.NET.Data.Full NuGet + worker registration. The lesson.regrade.requests queue now drains SQL items and produces verified completions instead of unverified.
  • #141 ✅ — SqlStudentCodeHintAnalyzer + SqlHintPatternLibrary (5 patterns implemented; 2 deferred for future schema-aware parsing). Registered in StudentCodeHintAnalyzerFactory. Frontend port runs the same patterns pre-flight so yellow-banner hints appear in the browser without a server roundtrip.
  • #139 ✅ — sandbox starter schema (bonus, not on the original DoD): the empty DuckDB connection now pre-loads the active theme's tables so the first SELECT returns rows.
  • #140 ✅ — theme-switch UX polish (also bonus): toast feedback, stale-output clearing, lesson display projection.

DoD bullet recap

Numbers

  • Backend: 3099 tests passing across the SQL curriculum, hint analyzer, grading executor, and existing C# / Python / JS / Regex / x86 suites
  • Frontend: 1755 spikersoft tests + 34 sql-runner library tests (25 hint patterns + 9 theme persistence)
  • Lessons shipped: 58 SQL lessons (5 tutorials + 53 challenges) across 17 chapters in the 60000-69999 band
  • Themes: 3 isomorphic (Travel / Video Games / Animals) with full theme parity enforced by smoke tests

Closing. SQL is now a first-class runtime in SpikerSoft's educational OS — same shell, same trust boundary, same offline-first guarantees as C# / Python / JavaScript / Regex / x86.

## All DoD bullets closed — 2026-05-14 Follow-ups landed: - #138 ✅ — `SqlLessonGradingExecutor` + `DuckDB.NET.Data.Full` NuGet + worker registration. The `lesson.regrade.requests` queue now drains SQL items and produces verified completions instead of `unverified`. - #141 ✅ — `SqlStudentCodeHintAnalyzer` + `SqlHintPatternLibrary` (5 patterns implemented; 2 deferred for future schema-aware parsing). Registered in `StudentCodeHintAnalyzerFactory`. Frontend port runs the same patterns pre-flight so yellow-banner hints appear in the browser without a server roundtrip. - #139 ✅ — sandbox starter schema (bonus, not on the original DoD): the empty DuckDB connection now pre-loads the active theme's tables so the first `SELECT` returns rows. - #140 ✅ — theme-switch UX polish (also bonus): toast feedback, stale-output clearing, lesson display projection. ### DoD bullet recap - ✅ SQL integrates with language-runner (same shell UX) - ✅ Offline execution + offline grading queue + attemptToken / IndexedDB path - ✅ **Authoritative worker regrading only (API never executes student SQL)** — closed by #138 - ✅ Runtime preload path in Site / Offline settings - ✅ Curriculum / prerequisite contracts on strategies - ✅ Tutorial panels · lesson hydration · strategy registry wired - ✅ **`SqlStudentCodeHintAnalyzer` in factory registry** — closed by #141 - ✅ Lesson assets: `schema.sql` / `seed.sql` (or equivalent) per lesson - ✅ `LessonGradingRuntime.Sql` registered in grading + hint factories — both factory dispatches now resolve `DuckDbSql` ### Numbers - **Backend**: 3099 tests passing across the SQL curriculum, hint analyzer, grading executor, and existing C# / Python / JS / Regex / x86 suites - **Frontend**: 1755 spikersoft tests + 34 sql-runner library tests (25 hint patterns + 9 theme persistence) - **Lessons shipped**: 58 SQL lessons (5 tutorials + 53 challenges) across 17 chapters in the 60000-69999 band - **Themes**: 3 isomorphic (Travel / Video Games / Animals) with full theme parity enforced by smoke tests Closing. SQL is now a first-class runtime in SpikerSoft's educational OS — same shell, same trust boundary, same offline-first guarantees as C# / Python / JavaScript / Regex / x86.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
agentic
Actionable surfaced by the agentic architecture & security review
 bug
Something is not working
 duplicate
This issue or pull request already exists
 enhancement
New feature
 help wanted
Need some help
 invalid
Something is wrong
 question
More information is needed
 sonarqube
Code quality issue surfaced by SonarQube (bug / code smell)
 wontfix
This won't be fixed
No labels
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
spikerj (Joseph Spiker)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: spikerj/spikersoft-issues#132