Skip to content

AI assistant

The conversational assistant lets staff (Admin/Teacher, in the Stats tab) and parents (dedicated Assistant tab on the portal) ask questions in natural language about engagement and get a chart / table / KPI plus a plain-language answer — never free SQL from the client.

How it works

flowchart LR
  U["Question (NL)"] --> LLM["LLM (OpenRouter)<br/>DeepSeek V3.1 default<br/>function calling"]
  CAT["Semantic catalog<br/>datasets · dims · metrics<br/>expressions SQL versionnées"] --> LLM
  LLM --> SPEC["QuerySpec JSON<br/>dataset, dims, measures,<br/>aggregations, filters, chart"]
  SCOPE["Role scope<br/>admin / teacher / parent"] --> Q["Compiler + exécution"]
  DUCK[("DuckDB en mémoire<br/>vues relationnelles sur parquet<br/>+ dims Postgres")] --> Q
  Q --> EVID["Evidence<br/>méthode + coverage"]
  Q --> CHART["Chart / table validés"]
  CHART --> UI["UI (Stats workspace / Parent)"]
  1. Question → LLM via OpenRouter (deepseek/deepseek-chat-v3.1 by default, OPENROUTER_MODEL to override; function calling + SSE streaming).
  2. The LLM only sees the semantic catalog: dataset ids, dimensions, metrics (labels + units, no SQL expressions in the browser payload).
  3. It answers with a structured QuerySpec (dataset, dimensions, measures, aggregations, filters, chart type) via tool calls — never raw SQL.
  4. The server applies the role scope (see below), compiles and runs the query on DuckDB (in-memory relational views over the lake parquet + Postgres class/subject dimensions).
  5. The chart/table is validated server-side (chart.ts: whitelisted chart types, no callback/HTML) and shown with Evidence (method used, coverage %).

Table structures (what the assistant queries)

All analytical data lives in the lake (processing/lake/). The DuckDB catalog exposes relational views over these tables:

engagement_windows — the fine-grained source (gold)

One row per student per 15 s window — the 7 category labels (A–G), the per-category scores and the final score.

video_name, student_id, window_idx, A, B, C, D, E, F, G,
engagement_score, g2_override, score_a … score_g,
window_flags, level, level_name, level_flag, window_start_sec

A..G hold label codes (A1 on-task gaze, B1 writing, … see Taxonomy); engagement_score = mean of the 7 category scores (0–10); level_name ∈ {Deep, Active, Passive, Disengagement, Withdrawal}.

student_session — one row per student per session (gold)

video_name, student_id, n_windows, avg_engagement_score, engagement_pct,
level, level_name, level_flag, on_task_ratio, attention_transitions,
drift_episodes, drift_total_sec, recovery_speed_windows,
sustained_attention_min, yawn_count, social_distraction_sec,
flagged, flags

class_session — one row per session, class aggregates (gold)

video_name, avg_engagement_score, engagement_pct,
avg_sustained_attention_min, students_flagged, n_students,
avg_on_task_ratio, avg_attention_transitions, avg_drift_sec,
avg_recovery_windows, drift_episodes_total, yawns_total

session_timeline — one row per minute of a session (gold)

video_name, window_idx, avg_score, n_students, share_engaged,
synchronized, minute

student_history — one row per student, cross-session aggregate (gold)

student_id, n_sessions, score_per_session, cum_avg_score,
cum_engagement_pct, last_level_name, sessions_flagged,
cum_on_task_ratio, cum_sustained_attention_min

Reference tables (ref/)

ref/sessions.csv  → video_name, class_id, subject_id, teacher_id, lesson_date, duration_sec
ref/students.csv  → student_id, class_id, display_name

Intermediate (silver)

silver/windows/video_name=<lesson>/part-*.parquet — one row per student × window × category (long format: video_name, student_id, window_idx, cat_letter, label_code), post-normalization, pre-scoring. silver/rejects/ — annotations that could not be mapped to the taxonomy.

Postgres (dims)

classes (name, school_year) and subjects (code, name) are loaded as dimension tables for the DuckDB catalog.

Role scoping & privacy

Role Can query Refused / masked
admin everything, all classes, student names —
teacher own sessions only other teachers' sessions
parent own child (+ anonymous class comparisons when the class has ≥ 5 students) any other student's name, nominative data
  • The browser receives a role-filtered catalog: forbidden dimensions (e.g. student_name for parents) are removed server-side before the answer.
  • Payloads sent to OpenRouter are pseudonymized (student_name → student_001); re-association happens only for the authorized role.
  • No raw email/name in analytics_chat_logs.

Analytics skills (Admin)

Admins can inject Markdown skill documents (api/admin/analytics-skills) that are added to the prompt when the question matches. Skill queries run through a hardened executor (safeSkillQuery: multi-statement, DDL/DML, read_parquet, ATTACH, COPY, PRAGMA, INSTALL, LOAD are all rejected). Active skills are listed in the Evidence (skillsUsed).

Abstention

If the requested period/data is missing or the coverage is below the threshold, the assistant abstains: it explains the lack of data instead of inventing numbers, and returns no chart.

Module map

Concern Module
LLM client (OpenRouter, tool calls, SSE) web/src/lib/server/analytics/llm.ts
Semantic catalog (datasets/dims/metrics/relations) web/src/lib/analytics/catalog.ts
DuckDB catalog (views over lake + dims) web/src/lib/server/analytics/duckdb.ts
Role schema filtering web/src/lib/server/analytics/catalog.ts (schemaForRole)
Scope resolution web/src/lib/server/analytics/scope.ts
Query compile/execution web/src/lib/server/analytics/query.ts
Chart validation web/src/lib/server/analytics/chart.ts
Pseudonymization web/src/lib/server/analytics/pseudonymize.ts
Skills web/src/lib/server/analytics/skills.ts
Data freshness (fingerprint local/R2) web/src/lib/server/analytics/data-source.ts

See also the chat test coverage plan.