user_lesson_progress

Lesson-level progress events from learners. Append-only, no PK — each interaction produces a new row. A lesson's current state is the sum of its actions.

• ETL strategy: append (no dedup — there is no PK)

Columns

Column	Type	Description
id_tenant	BIGINT	FK → tenants.id.
id_user	BIGINT	FK → users.id.
id_item	BIGINT	FK → lessons.id. The name item is kept for backward compatibility.
action	SMALLINT	Progress delta. Enum — see below.
created_at	TIMESTAMPTZ	Event timestamp.

Enum · action

SMALLINT storing a progress delta — not a boolean. The current status of a lesson is SUM(action) grouped by (id_user, id_item). If the sum is >= 1, the learner completed it.

Value	Meaning
+1	Lesson marked as completed (advance).
-1	Completion undone (rollback).
0	Viewed without status change.

Computing current status

-- Lessons completed by user X
SELECT id_item AS id_lesson
FROM lms.user_lesson_progress
WHERE id_user = 12345
GROUP BY id_tenant, id_user, id_item
HAVING SUM(action) >= 1;

This table feeds user_product_progress, which already exposes (user, product) completion percentage so you don't need to compute it.

Children

• user_product_progress — derived.
• metrics.lesson_progress_aggregates — aggregates distinct user_ids per lesson.