Lesson Engine

Lesson Engine

The lesson engine turns practice from hardcoded components into data. A lesson is a row; an exercise is a { type, payload } object; the client picks a renderer from a registry. Shipped in phases E1–E5.

Two lanes make it up: a content lane (read lessons) and a progress lane (write what happened). They are deliberately separate — content is public and cacheable, progress is per-user and must not be lost.

The content lane

Lessons live in the lessons table and are served by GET /api/v1/content/lessons. Contract: docs/api/content.md.

create table public.lessons (
  id           uuid primary key default gen_random_uuid(),
  pack_id      text not null default 'de',
  course_code  text not null default 'de',
  level        text not null,          -- a1 | a2 | b1
  unit_number  integer not null check (unit_number >= 1),
  tab          text not null,          -- chat | alphabet | vocab | translate
  exercises    jsonb not null default '[]'::jsonb,
  ...
  unique (pack_id, course_code, level, tab, unit_number)
);

Three things about this table are worth noticing.

It is not user data. No user_id, publicly readable, service-role-only writes. It is deliberately excluded from the account export/delete table union, whose assertion covers tables that have an owner.

The closed sets are database constraints, not client validation. level, tab, and course_code are all check constraints, so a bad row fails at insert rather than at first render. exercises is constrained to be a JSON array.

The index matches the only read path. Lookups are always (course_code, level, tab) ordered by unit_number, and lessons_lookup_idx is exactly that.

Content is seeded with npm run seed:lessons.

The exercise registry

An exercise row is { id, type, payload }. The client maps type to a component:

export const EXERCISE_TYPES = ['flashcard', 'translate', 'chat', 'multiple-choice'];

export function getExerciseComponent(type) {
  return REGISTRY[type] ?? UnknownExercise;
}

Source: src/components/exercises/.

The set is closed on purpose — adding a type is a contract change, not a quiet insertion into the map. And the fallback matters: an unknown or missing type renders UnknownExercise rather than throwing, so one bad row cannot take down the whole viewer. The server sanitizes too, dropping malformed exercises while still serving their siblings and logging how many it dropped.

The progress lane

Answers do not write to the database directly. They go through a durable queue and then a single RPC.

answer → applyEvent (local daily, instant UI)
       → progressQueue (localStorage, own key)
       → POST /api/v1/progress/events
       → apply_progress_event(...)   ← the only writer

The queue lives at its own localStorage key (deutsch-app-progress-queue-v1), separate from the main state blob, for two reasons: answers must survive a quota failure on that blob, and the queue is not part of the LWW settings merge.

apply_progress_event is a security definer function with set search_path = '' that validates tab, level, and verdict against closed sets and raises on anything else. Because it is the only path that writes progress counters, there is exactly one writer on stats_daily — no second writer to disagree with. Events carry a UUID and are deduplicated via progress_events_seen, so a retry is safe.

The outage this design came from

E4 merged its code without its migration being applied. Production kept the 7-argument apply_progress_event while the deployed client called the 8-argument form. Server-side progress writes were dead for roughly 26 hours, with no fallback.

Two durable lessons came out of it:

1. A merged migration file is not an applied migration. The Migration Drift workflow now fails when production is missing a committed migration. See Operations Runbooks.
2. pg_get_function_identity_arguments includes the parameter name, so comparing signatures with it will happily tell you two different functions match. Use to_regprocedure instead, or your verification lies to you.

Lesson progress and unit state

src/lib/lessonProgress.js derives locked / in-progress / completed unit states from the same progress data, so unit state is a projection rather than a second source of truth that can drift.

Related

• Architecture Overview — where these lanes sit
• Offline-First Sync Model — the other write path
• docs/api/progress.md