Test coverage plan — Analytics assistant (chat)
Status: authoritative document to test the chat end-to-end with concrete cases
from the database (gold + ref + Postgres), not mock answers. The mock is used
only as a last resort: every E2E scenario runs real DuckDB/Postgres queries and
checks the result against the actual data.
1. Chat scope
| Capability |
Implementation |
Test required |
| Multi-turn (client-side context) |
ChatPanel.ask + history |
E2E 2 related questions |
| Structured tool calls |
query_analytics, resolve_scope, query_scoped_skill_metric, get_published_child_reports |
unit + contract + E2E |
SSE scope_proposal/status/delta/chart/evidence/done/error |
SSE route |
contract (parser) |
| Role-based scope (admin/teacher/parent) |
scope.ts + compiler |
E2E per role + IDOR |
| Pseudonymization + re-association |
pseudonymize.ts |
unit + mock payload capture |
| Evidence (method, coverage) |
server Evidence |
contract + E2E |
| Markdown skills (injection + display) |
skills.ts |
unit + admin E2E |
| Abstention when insufficient data |
coverage threshold |
E2E “no data” scenario |
| Answer language = message language |
prompt |
E2E FR/EN |
| Validated chart (never callback/HTML) |
chart.ts |
adversarial unit |
2. Test levels
| Level |
Target |
Examples |
| Unit |
Pure functions |
catalog, chart-compatibility, safeSkillQuery, pseudonymize, extractChartSpec, skills title |
| Integration |
duckdb + lake fixtures |
query compiler vs small lake, coverage, comparison |
| Contract |
SSE + JSON |
every event parses, stable error codes |
| E2E (browser) |
Workspace + chat |
concrete cases §4, multi-turn, add-to-canvas, parent |
| Security |
Adversarial inputs |
SQL injection skill, prompt injection, PII in payloads |
| Non-regression |
Dashboard outside Stats |
existing specs (dashboard, history, parent, admin) |
3. Chat coverage matrix
3.1 Intents (model-opened)
| Intent |
Sample question |
Expected query |
Expected chart |
| Compare subjects |
“compare engagement by subject” |
class_sessions × subject_name |
bar |
| Trend |
“engagement trend over last month” |
class_sessions × week |
line |
| At-risk students |
“which students need attention” |
student_sessions × student_name + drift |
bar/table |
| Per-student average |
“per-student engagement” |
student_sessions × student_name |
bar |
| Simple KPI |
“what is the average engagement” |
class_sessions, 0 dim |
kpi |
| On-task |
“average on-task ratio per class” |
student_sessions × class_name |
bar |
| Sustained attention |
“sustained attention by subject” |
student_sessions × subject_name |
bar |
| Synchrony |
“class synchrony over time” |
session_timeline × week |
line |
| Insufficient aggregate |
“engagement in July” (no data) |
query → 0 rows |
abstention |
3.2 Roles
| Role |
Authorization |
E2E case |
Negative case |
| Admin |
everything |
compare subjects |
— |
| Teacher |
own sessions |
trend |
question about another teacher's class → refused/reduced scope |
| Parent |
child + class ≥ 5 students |
“how did the lesson go” |
“list all students” → refused, no peer names |
3.3 Languages
- FR question → FR answer.
- EN question → EN answer (UI stays English but the text follows the
question language).
3.4 Scopes
- Explicit: “math in march” → subject + period scope.
- Ambiguous: “the class” (several classes) →
scope_proposal + confirmation.
- Forbidden (parent): another student's name → refusal.
3.5 Skills
- Active skill injected and listed in
Evidence.skillsUsed.
- Inactive skill never injected.
- Question requiring a skill →
query_scoped_skill_metric tool (or
abstention if isolated SQL is impossible).
4. Concrete E2E cases (real database)
Demo data: 15 lessons (math/arab/islam × 5), 31 students S001–S031,
11-18 March 2026, complete gold lake (engagement 0–100 %, drift, flags, yawns).
| # |
Scenario (end-to-end) |
Key assertions |
| C1 |
Admin: “compare engagement by subject” |
bar chart; values = real lake averages (Arab ≈ 52.7 %, Math ≈ 52.4 %, Islam ≈ 52.5 %); evidence coverage 100 % |
| C2 |
Teacher: “engagement trend over the last month” |
line chart by week; scope contains ONLY the teacher's sessions (15); no third-party session |
| C3 |
Admin: “which students need attention” |
per-student chart; names are real (staff re-association); sorted by signal (drift) not opaque score |
| C4 |
Admin: “average on-task ratio per class” |
bar chart; values = lake on_task averages × 100 |
| C5 |
Multi-turn: C1 then “and which subject has the most flags?” |
2 turns; 2nd reuses context |
| C6 |
Add-to-canvas on C1 chart |
widget added; its query re-executed (footer n = …) |
| C7 |
Parent: “how did my child's last lesson go?” |
deterministic answer; child chart; NO other student's name in text+chart+evidence |
| C8 |
Parent: “compare my child to the class average” |
child vs anonymous class comparison |
| C9 |
Admin: no-data question (“in July”) |
abstention: the chat explains the lack of data, no chart |
| C10 |
FR: “Compare l'engagement par matière” |
answer in French |
5. Security (dedicated tests)
safeSkillQuery: multi-statement, DDL/DML, read_parquet, comments,
ATTACH, COPY, PRAGMA, INSTALL, LOAD → all rejected.- Prompt injection via skill: a hostile skill (“ignore previous
instructions…”) stays ineffective (delimiters + system priority).
- No PII in the OpenRouter payload: mock client capture →
student_name
pseudonymized (student_001), never Student S001.
- Re-association only for the authorized role (staff sees names; parent sees
“your child”).
- Logs: no raw email/name in
analytics_chat_logs.
6. Gap analysis — “chat not mature enough”
| Gap |
State |
Action |
| Functional scope confirmation |
resolve_scope returns a proposal but the server does not require confirmation |
wire needsConfirmation → SSE scope_proposal + UI Confirm/Rephrase |
| Server-triggered abstention |
warning coverage < 40 % in Evidence only |
the prompt must force abstention when coverage < threshold |
| Skill question → skill tool |
the mock does not exercise it |
golden skill + E2E C-skill |
| Language: detection by message (not just prompt) |
prompt only |
golden FR/EN test; adjust prompt if needed |
| Stable errors |
partial codes |
normalize SSE error codes (INVALID_QUERY_SPEC…) |
7. Versioned golden questions (§13.3)
Minimal set to keep:
- Admin (10): C1, C3, C4, C9, trend by subject, synchrony, sustained
attention, yawns per class, drift by subject, “list the sessions with the
most flags”.
- Teacher (10): C2, multi-turn, a topic outside scope (refusal), simple
KPI, per-student, “my class vs the school” (unauthorized comparison →
without widening), skills, languages, add-to-canvas, reset filters.
- Parent (10): C7, C8, language, two children (child confirmation), “list
all students” (refusal), published/draft report, fatigue (observation),
class < 5 students (no benchmark), no third-party nominative data,
multi-turn.
8. Global acceptance criteria
- Each case C1–C10 passes in E2E with the mock ONLY for text generation;
values/charts come from real queries.
- 100 % of authorization branches (admin/teacher/parent) covered by a test.
- No name/email in OpenRouter payloads or logs (tested by capture).
- The main dashboard does not regress (existing specs green).
- The full suite is stable (≤ 1 flaky tolerated, CI retries).