daily_counters

The daily operations dashboard. One row per (tenant, day) with counters covering registrations, learning outcomes and support engagement.

• Grain: (id_tenant, date)
• ETL strategy: merge by PK
• PK: (id_tenant, date)

When to use it

• Daily KPI tracking.
• D-1 / W-1 / M-1 comparisons.
• Anomaly detection (login drops, ratings spikes).

Columns · Keys

Column	Type	Description
id_tenant	BIGINT	FK → tenants.id. Part of PK.
date	DATE	The day the counters refer to. Part of PK.

Columns · Registrations

Column	Executive meaning
users	New learners registered that day.
users_deleted	Learners soft-deleted that day.
user_logins	Active sessions recorded that day (logged-in heartbeat for learners).

Columns · Learning

Column	Executive meaning
certificates_issued	Certificates issued that day (includes reissues).
exams_passed	Exams passed.
exams_failed	Exams failed.
ratings_positive	Lesson likes.
ratings_negative	Lesson dislikes.

Columns · Engagement (messages)

Column	Executive meaning
questions_created	Questions posted by learners.
question_admin_responses	Admin replies to questions.
comments_created	Comments created.
comment_admin_responses	Admin replies to comments.
comments_hidden	Comments hidden (moderation).
user_messages	Total volume of messages sent by users.

Usage pattern

-- Last-week summary
SELECT
    sum(users)                AS new_learners,
    sum(user_logins)          AS sessions,
    sum(certificates_issued)  AS certificates,
    sum(exams_passed)         AS approvals,
    sum(exams_failed)         AS failures
FROM metrics.daily_counters
WHERE date >= current_date - interval '7 days';

-- Busiest day in the last 90 days
SELECT date, sum(user_logins) AS sessions
FROM metrics.daily_counters
WHERE date >= current_date - interval '90 days'
GROUP BY date
ORDER BY sessions DESC
LIMIT 5;