scores

Gamification scoring events. Each row is an event that awarded points to a user. Append-only.

• ETL strategy: append
• PK: id

Columns

Column	Type	Description
id	BIGINT	PK.
id_tenant	BIGINT	FK → tenants.id.
id_user	BIGINT	FK → users.id.
id_product	BIGINT	FK → products.id. Resolves to products.id_origin when replicated.
id_item	BIGINT	Free-form id of the item that produced the score (lesson, comment, …).
type	TEXT	Event category. Enum — see below.
key	TEXT	Free-form identifier (key of the rule that produced the score, when applicable).
score	INTEGER	Points awarded.
created_at	TIMESTAMPTZ

Enum · type

Value	Meaning
lesson_completed	Lesson marked as completed.
course_started	Course started by the learner.
course_progress_50	Reached 50% of the course.
course_progress_75	Reached 75% of the course.
course_progress_90	Reached 90% of the course.
course_completed	Course completed (100%).
certificate_issued	Certificate issued.
question_asked	Question / inquiry posted.
comment_posted	Comment posted.
exam_passed	Exam passed.
manual	Points awarded manually by an admin.

The same names appear in tenants.meta.gamification.events — match scores.type against that map to know how many points each event awards.

The key column complements type with a free-form identifier when the admin defines the rule (custom campaigns).

Patterns

-- Top 10 learners this month
SELECT id_user, sum(score) AS month_points
FROM lms.scores
WHERE created_at >= date_trunc('month', now())
GROUP BY id_user
ORDER BY 2 DESC
LIMIT 10;

-- Event distribution
SELECT type, count(*), sum(score)
FROM lms.scores
WHERE created_at >= now() - interval '7 days'
GROUP BY type
ORDER BY 2 DESC;