sql: authoritative server-side regrade — SqlLessonGradingExecutor + worker registration #138

New Issue

2026-05-15T01:39:55Z

spikerj commented

2026-05-15 01:39:55 +00:00

Context

Follow-up to #132. The SQL curriculum (60000-69999) and the frontend feature-dev-tools-sql-runner ship together: 58 lessons grade entirely in the browser via DuckDB-WASM. The local pass enqueues a language: "sql" item into the existing offline-progress outbox, but the worker has no handler for it — items currently sit in the queue until the user opens the lesson while online again.

This ticket closes the Authoritative worker regrading only (API never executes student SQL) bullet from #132's Definition-of-Done.

What needs to happen

1. NuGet — add DuckDB.NET

Add DuckDB.NET.Data (or DuckDB.NET.Bindings.Full if we want the explicit native loader) to SpikerSoft.Business.CodeExecution.csproj. Verify the worker container image (Linux) has the matching native lib available, or include the runtime-specific package so the assembly loads on linux-x64.

2. SqlLessonGradingExecutor

New file: SpikerSoft.Business/Domain/CodeExecution/Execution/SqlLessonGradingExecutor.cs

public sealed class SqlLessonGradingExecutor : ILessonGradingExecutor
{
    public LessonGradingRuntime Runtime => LessonGradingRuntime.DuckDbSql;

    public async Task<LessonGradingResult> ExecuteAsync(LessonRegradeRequest request, CancellationToken ct)
    {
        // 1. Parse request.StudentCode  -> { query, theme }
        // 2. Parse request.TestCode      -> SqlLessonPlan (JSON)
        // 3. Pick plan.Themes[theme]     -> SqlThemeBinding
        // 4. Open in-memory DuckDB connection
        // 5. Run binding.SchemaSql + binding.SeedSql
        // 6. Run student query, capture result set
        // 7. Compare to binding.Expected via mirror of SqlLessonGraderService
        // 8. Build LessonGradingResult.TestResults from the comparison
    }
}

Mirror every variant of SqlExpectedResult on the C# side: ExactResultSet, UnorderedResultSet, RowCount, ScalarValue, ColumnSchema, ContainsRows. Tolerant numeric compare for DECIMAL (same 1e-6 * scale tolerance the frontend grader uses) so the SPA's pass doesn't flip to a server FAIL on a trailing zero.

3. Submission shape

LessonRegradeRequest.StudentCode is a flat string today. SQL submissions need both the query and the theme key. Two options:

Recommended: serialize { query, theme } as JSON into StudentCode (like the regex executor does for { pattern, flags, replacement }). Worker JSON-parses. Frontend update: change progressSync.enqueue in sql-runner.service.ts to wrap request.code + activeTheme into that JSON.
Alternative: extend LessonRegradeRequest with a Theme field. More typing rigour but widens the cross-language contract — not worth it for one field.

4. Register in the worker

SpikerSoft.EventHandlers.CodeExecution/Program.cs:

builder.Services.AddSingleton<ILessonGradingExecutor, SqlLessonGradingExecutor>();

5. Smoke test integration

The SqlCurriculumTests cardinal-rule check currently tokenizes reference solutions but does NOT execute them. With DuckDB.NET available we can run every per-theme reference solution against the lesson's schema+seed and assert the result matches binding.Expected. Gate on an env var (same way the Python smoke test gates on SPIKERSOFT_PYTHON) so CI without the native lib still passes structural assertions.

Add a new theory: SqlChallenge_ReferenceSolutionPassesAgainstExpected that loops every (lesson, theme) pair, executes the canonical query in SqlReferenceSolutions.Map, and asserts the result.

Acceptance

DuckDB.NET.Data builds on linux-x64 worker image
SqlLessonGradingExecutor registered; ILessonGradingExecutorFactory.For(DuckDbSql) returns it
Submitting any SQL lesson while online produces a verified completion (no unverified status in the batch response)
Submitting offline then coming online drains the outbox and the lesson promotes from provisional to verified in the UI
All six SqlExpectedResult strategies pass through the executor
Smoke test runs every reference solution end-to-end when the lib is available

Labels

enhancement · architecture · backend · curriculum · sql

## Context Follow-up to [#132](https://git.spikersoft.com/spikerj/spikersoft-issues/issues/132). The SQL curriculum (60000-69999) and the frontend `feature-dev-tools-sql-runner` ship together: 58 lessons grade entirely in the browser via DuckDB-WASM. The local pass enqueues a `language: "sql"` item into the existing offline-progress outbox, but **the worker has no handler for it** — items currently sit in the queue until the user opens the lesson while online again. This ticket closes the `Authoritative worker regrading only (API never executes student SQL)` bullet from #132's Definition-of-Done. ## What needs to happen ### 1. NuGet — add DuckDB.NET Add `DuckDB.NET.Data` (or `DuckDB.NET.Bindings.Full` if we want the explicit native loader) to `SpikerSoft.Business.CodeExecution.csproj`. Verify the worker container image (Linux) has the matching native lib available, or include the runtime-specific package so the assembly loads on `linux-x64`. ### 2. SqlLessonGradingExecutor New file: `SpikerSoft.Business/Domain/CodeExecution/Execution/SqlLessonGradingExecutor.cs` ```csharp public sealed class SqlLessonGradingExecutor : ILessonGradingExecutor { public LessonGradingRuntime Runtime => LessonGradingRuntime.DuckDbSql; public async Task<LessonGradingResult> ExecuteAsync(LessonRegradeRequest request, CancellationToken ct) { // 1. Parse request.StudentCode -> { query, theme } // 2. Parse request.TestCode -> SqlLessonPlan (JSON) // 3. Pick plan.Themes[theme] -> SqlThemeBinding // 4. Open in-memory DuckDB connection // 5. Run binding.SchemaSql + binding.SeedSql // 6. Run student query, capture result set // 7. Compare to binding.Expected via mirror of SqlLessonGraderService // 8. Build LessonGradingResult.TestResults from the comparison } } ``` Mirror every variant of `SqlExpectedResult` on the C# side: `ExactResultSet`, `UnorderedResultSet`, `RowCount`, `ScalarValue`, `ColumnSchema`, `ContainsRows`. Tolerant numeric compare for `DECIMAL` (same `1e-6 * scale` tolerance the frontend grader uses) so the SPA's pass doesn't flip to a server FAIL on a trailing zero. ### 3. Submission shape `LessonRegradeRequest.StudentCode` is a flat string today. SQL submissions need both the query and the theme key. Two options: - **Recommended**: serialize `{ query, theme }` as JSON into `StudentCode` (like the regex executor does for `{ pattern, flags, replacement }`). Worker JSON-parses. Frontend update: change `progressSync.enqueue` in `sql-runner.service.ts` to wrap `request.code` + `activeTheme` into that JSON. - Alternative: extend `LessonRegradeRequest` with a `Theme` field. More typing rigour but widens the cross-language contract — not worth it for one field. ### 4. Register in the worker `SpikerSoft.EventHandlers.CodeExecution/Program.cs`: ```csharp builder.Services.AddSingleton<ILessonGradingExecutor, SqlLessonGradingExecutor>(); ``` ### 5. Smoke test integration The `SqlCurriculumTests` cardinal-rule check currently tokenizes reference solutions but does NOT execute them. With DuckDB.NET available we can run every per-theme reference solution against the lesson's schema+seed and assert the result matches `binding.Expected`. Gate on an env var (same way the Python smoke test gates on `SPIKERSOFT_PYTHON`) so CI without the native lib still passes structural assertions. Add a new theory: `SqlChallenge_ReferenceSolutionPassesAgainstExpected` that loops every `(lesson, theme)` pair, executes the canonical query in `SqlReferenceSolutions.Map`, and asserts the result. ## Acceptance - [ ] `DuckDB.NET.Data` builds on `linux-x64` worker image - [ ] `SqlLessonGradingExecutor` registered; `ILessonGradingExecutorFactory.For(DuckDbSql)` returns it - [ ] Submitting any SQL lesson while online produces a verified completion (no `unverified` status in the batch response) - [ ] Submitting offline then coming online drains the outbox and the lesson promotes from provisional to verified in the UI - [ ] All six `SqlExpectedResult` strategies pass through the executor - [ ] Smoke test runs every reference solution end-to-end when the lib is available ## Labels `enhancement` · `architecture` · `backend` · `curriculum` · `sql`

spikerj referenced this issue

2026-05-15 01:43:23 +00:00

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

spikerj referenced this issue

2026-05-15 02:11:16 +00:00

sql: SqlStudentCodeHintAnalyzer — pre-flight hints for the classic SQL pitfalls #141

spikerj commented

2026-05-15 02:11:35 +00:00

Closed by implementation

Shipped:

NuGet: DuckDB.NET.Data.Full 1.5.2 added to SpikerSoft.Business.CodeExecution.csproj (the slice project), NOT the heavy SpikerSoft.Business parent — so the native binaries land in the worker chain without leaking into the API monolith.
Executor: SpikerSoft.Business.CodeExecution/Sql/SqlLessonGradingExecutor.cs implements ILessonGradingExecutor for LessonGradingRuntime.DuckDbSql. All 6 SqlExpectedResult variants are implemented: ExactResultSet, UnorderedResultSet, RowCount, ScalarValue, ColumnSchema, ContainsRows. Same tolerant numeric compare (1e-6 * scale) the frontend grader uses, so a DECIMAL(10,2) 1500.00 matches an expected 1500m literal.
Submission shape: frontend SqlRunnerService.executeLessonCode now wraps the student's query and active theme as { query, theme } JSON before enqueuing into the offline outbox. The worker tolerates legacy raw-string submissions too (regression-safe for any items pre-envelope that drain after deploy).
Registration: SqlLessonGradingExecutor registered as ILessonGradingExecutor in SpikerSoft.EventHandlers.CodeExecution/Program.cs. LessonGradingExecutorFactory.For(DuckDbSql) now resolves it via the existing DI registration loop.
Smoke tests: 8 end-to-end tests in SqlLessonGradingExecutorTests exercise scalar/row-count/unordered/exact/syntax-error/missing-theme/legacy-string paths against real DuckDB.NET (gated on SPIKERSOFT_SKIP_DUCKDB=1 for CI environments without the native lib).

Verification: 3099 backend tests pass, including the new SQL executor + hint analyzer tests. Frontend executeLessonCode enqueues { query, theme } JSON; the worker's regrade pipeline now drains SQL items and produces verified completions instead of unverified.

Closing.

## Closed by implementation Shipped: - **NuGet**: `DuckDB.NET.Data.Full` 1.5.2 added to [`SpikerSoft.Business.CodeExecution.csproj`](spikersoft-backend/SpikerSoft.Business.CodeExecution/SpikerSoft.Business.CodeExecution.csproj) (the slice project), NOT the heavy `SpikerSoft.Business` parent — so the native binaries land in the worker chain without leaking into the API monolith. - **Executor**: [`SpikerSoft.Business.CodeExecution/Sql/SqlLessonGradingExecutor.cs`](spikersoft-backend/SpikerSoft.Business.CodeExecution/Sql/SqlLessonGradingExecutor.cs) implements `ILessonGradingExecutor` for `LessonGradingRuntime.DuckDbSql`. All 6 `SqlExpectedResult` variants are implemented: `ExactResultSet`, `UnorderedResultSet`, `RowCount`, `ScalarValue`, `ColumnSchema`, `ContainsRows`. Same tolerant numeric compare (`1e-6 * scale`) the frontend grader uses, so a `DECIMAL(10,2)` `1500.00` matches an expected `1500m` literal. - **Submission shape**: frontend `SqlRunnerService.executeLessonCode` now wraps the student's query and active theme as `{ query, theme }` JSON before enqueuing into the offline outbox. The worker tolerates legacy raw-string submissions too (regression-safe for any items pre-envelope that drain after deploy). - **Registration**: `SqlLessonGradingExecutor` registered as `ILessonGradingExecutor` in [`SpikerSoft.EventHandlers.CodeExecution/Program.cs`](spikersoft-backend/SpikerSoft.EventHandlers.CodeExecution/Program.cs). `LessonGradingExecutorFactory.For(DuckDbSql)` now resolves it via the existing DI registration loop. - **Smoke tests**: 8 end-to-end tests in `SqlLessonGradingExecutorTests` exercise scalar/row-count/unordered/exact/syntax-error/missing-theme/legacy-string paths against real DuckDB.NET (gated on `SPIKERSOFT_SKIP_DUCKDB=1` for CI environments without the native lib). **Verification**: 3099 backend tests pass, including the new SQL executor + hint analyzer tests. Frontend `executeLessonCode` enqueues `{ query, theme }` JSON; the worker's regrade pipeline now drains SQL items and produces verified completions instead of `unverified`. Closing.

spikerj closed this issue

2026-05-15 02:11:36 +00:00

spikerj referenced this issue

2026-05-15 02:12:27 +00:00

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

spikerj referenced this issue

2026-07-06 15:36:54 +00:00

[Security][CRITICAL] SignalR hubs missing class-level [Authorize] — CodeExecutionHub / DashboardHub / ClusterHub open to anonymous connections #401

spikerj referenced this issue

2026-07-06 15:37:40 +00:00

[Security][CRITICAL] SignalR hubs missing class-level [Authorize] — CodeExecutionHub / DashboardHub / ClusterHub open to anonymous connections #401

spikerj referenced this issue