Statistics and Tracking

Version française
← Back to index

---

Overview

Racines's statistics system is entirely anonymous — no personal user data is collected. Events are aggregated by day and by language.

Two tables store the data:

Table	Content
statistics	Usage events (card views, audio plays, unlocks)
quiz_sessions	MCQ revision sessions (scores, categories, abandonments)

---

Table statistics

Schema

CREATE TABLE statistics (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  language_id UUID REFERENCES languages(id) ON DELETE CASCADE,
  card_id     UUID REFERENCES cards(id) ON DELETE CASCADE,
  item_id     UUID REFERENCES items(id) ON DELETE CASCADE,
  event_type  VARCHAR(50) NOT NULL,
  count       INTEGER DEFAULT 1,
  event_date  DATE NOT NULL,
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(language_id, card_id, item_id, event_type, event_date)
);

Key behavior: Events are aggregated by day via an upsert:

INSERT INTO statistics (language_id, card_id, item_id, event_type, count, event_date)
VALUES ($1, $2, $3, $4, 1, CURRENT_DATE)
ON CONFLICT (language_id, card_id, item_id, event_type, event_date)
DO UPDATE SET count = statistics.count + 1;

No row per click — 1 row per (language, card, item, type, day), incremented.

Event types

event_type	Trigger	card_id	item_id
unlock_language	Player unlocks a language	null	null
view_card	Player displays a card (word or phrase)	required	null
play_audio	Player plays audio	required	required

---

Tracking API

POST /api/statistics/track

Public endpoint (no authentication). Called automatically by the frontend.

// Triggers in the code:

// unlock_language → LanguageContext.unlockLanguage()
await fetch('/api/statistics/track', {
  method: 'POST',
  body: JSON.stringify({ eventType: 'unlock_language', languageId }),
});

// view_card → pages /[language]/words/[n] and /[language]/phrases/[n]
trackEvent({ eventType: 'view_card', languageId, cardId });

// play_audio → AudioPlayer.handlePlay()
trackEvent({ eventType: 'play_audio', languageId, cardId, itemId });

Body:

{
  "eventType": "play_audio",
  "languageId": "uuid",
  "cardId": "uuid",
  "itemId": "uuid"
}

Response:

{ "success": true, "count": 42 }

count = cumulative number of occurrences for that day.

---

Offline statistics management

IndexedDB queue

When the network is unavailable, events are queued in IndexedDB:

// Store: statistics-queue (racines-db v3)
{
  id: autoIncrement,
  timestamp: Date.now(),
  eventType: "play_audio",
  languageId: "uuid",
  cardId: "uuid",
  itemId: "uuid"
}

Automatic synchronization

// src/components/ServiceWorkerRegistration.tsx

// Attempt on component mount (at app load)
flushStatisticsQueue();

// Also triggered on network reconnection
window.addEventListener("online", () => {
  flushStatisticsQueue();
});

flushStatisticsQueue(): 1. Reads all events from the IndexedDB queue 2. Sends them one by one to /api/statistics/track 3. Deletes successfully sent events 4. Keeps failures for the next attempt

---

Admin dashboard

Endpoint GET /api/statistics/by-language

Aggregates events by language, with support for comparison mode:

# Primary period
GET /api/statistics/by-language?from=2026-01-01&to=2026-01-31

# With comparison period
GET /api/statistics/by-language?from=2026-01-01&to=2026-01-31&compareFrom=2025-12-01&compareTo=2025-12-31

Response with comparison:

{
  "success": true,
  "stats": [
    {
      "language_id": "uuid",
      "language_name": "Pular",
      "language_code": "pul",
      "unlock_count": 245,
      "view_count": 1830,
      "audio_count": 956
    }
  ],
  "compareStats": [
    {
      "language_name": "Pular",
      "unlock_count": 180,
      "view_count": 1420,
      "audio_count": 720
    }
  ]
}

Endpoint GET /api/statistics/top-cards

Most viewed cards over a period:

GET /api/statistics/top-cards?languageId=uuid&limit=10&from=2026-01-01&to=2026-01-31

---

Useful SQL queries

Global view by language (all periods)

SELECT
  l.name AS language,
  SUM(CASE WHEN s.event_type = 'unlock_language' THEN s.count ELSE 0 END) AS unlocks,
  SUM(CASE WHEN s.event_type = 'view_card' THEN s.count ELSE 0 END) AS card_views,
  SUM(CASE WHEN s.event_type = 'play_audio' THEN s.count ELSE 0 END) AS audio_plays
FROM statistics s
JOIN languages l ON s.language_id = l.id
GROUP BY l.id, l.name
ORDER BY unlocks DESC;

Daily activity (last 30 days)

SELECT
  event_date,
  SUM(count) AS total_events
FROM statistics
WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY event_date
ORDER BY event_date;

Top 10 most viewed cards

SELECT
  c.card_number,
  c.type,
  c.theme,
  l.name AS language,
  SUM(s.count) AS views
FROM statistics s
JOIN cards c ON s.card_id = c.id
JOIN languages l ON s.language_id = l.id
WHERE s.event_type = 'view_card'
  AND s.event_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY c.id, c.card_number, c.type, c.theme, l.name
ORDER BY views DESC
LIMIT 10;

Audio engagement rate (audio played vs viewed)

SELECT
  l.name AS language,
  SUM(CASE WHEN s.event_type = 'view_card' THEN s.count ELSE 0 END) AS views,
  SUM(CASE WHEN s.event_type = 'play_audio' THEN s.count ELSE 0 END) AS audio_plays,
  ROUND(
    SUM(CASE WHEN s.event_type = 'play_audio' THEN s.count ELSE 0 END)::numeric /
    NULLIF(SUM(CASE WHEN s.event_type = 'view_card' THEN s.count ELSE 0 END), 0) * 100, 1
  ) AS audio_engagement_pct
FROM statistics s
JOIN languages l ON s.language_id = l.id
GROUP BY l.id, l.name
ORDER BY audio_engagement_pct DESC NULLS LAST;

---

Quiz statistics

Table quiz_sessions

CREATE TABLE quiz_sessions (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  language_id      UUID REFERENCES languages(id) ON DELETE CASCADE,
  category_slug    VARCHAR(255) NOT NULL,
  category_name    VARCHAR(255),
  total_questions  INTEGER NOT NULL,
  score            INTEGER,              -- null if abandoned
  started_at       TIMESTAMPTZ DEFAULT NOW(),
  completed_at     TIMESTAMPTZ,         -- null if abandoned
  created_at       TIMESTAMPTZ DEFAULT NOW()
);

Endpoint GET /api/admin/quiz/stats

# Quiz stats with comparison mode
GET /api/admin/quiz/stats?languageId=uuid&from=2026-01-01&compareFrom=2025-12-01&compareTo=2025-12-31

Global quiz SQL query

SELECT
  l.name AS language,
  COUNT(*) AS total_sessions,
  COUNT(qs.completed_at) AS completed_sessions,
  COUNT(*) - COUNT(qs.completed_at) AS abandoned_sessions,
  ROUND(COUNT(qs.completed_at)::numeric / COUNT(*) * 100, 1) AS completion_rate_pct,
  ROUND(AVG(CASE WHEN qs.completed_at IS NOT NULL
    THEN qs.score::numeric / qs.total_questions * 100 END), 1) AS avg_score_pct
FROM quiz_sessions qs
JOIN languages l ON qs.language_id = l.id
WHERE qs.started_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY l.id, l.name
ORDER BY total_sessions DESC;

Top categories by language

SELECT
  qs.category_name,
  COUNT(*) AS sessions,
  COUNT(qs.completed_at) AS completed,
  ROUND(AVG(CASE WHEN qs.completed_at IS NOT NULL
    THEN qs.score::numeric / qs.total_questions * 100 END), 1) AS avg_score
FROM quiz_sessions qs
WHERE qs.language_id = $1
GROUP BY qs.category_name
ORDER BY sessions DESC
LIMIT 10;

---

Purge and maintenance

GET /api/admin/statistics

Count rows before purge:

curl "http://localhost:3000/api/admin/statistics?languageId=uuid&from=2025-01-01&to=2025-12-31" \
  -b cookies.txt
# → {"count": 45230}

DELETE /api/admin/statistics

Filtered purge:

# Purge a language over a period
curl -X DELETE \
  "http://localhost:3000/api/admin/statistics?languageId=uuid&from=2025-01-01&to=2025-12-31" \
  -b cookies.txt
# → {"success": true, "deleted": 45230}

Full purge:

# Required header to prevent accidental deletions
curl -X DELETE http://localhost:3000/api/admin/statistics \
  -H "x-delete-all-confirmed: true" \
  -b cookies.txt
# → {"success": true, "deleted": 123456}

⚠️ Irreversible action. Deleted statistics cannot be recovered.

---

Data export

The statistics data does not have a built-in CSV export endpoint. To export:

# CSV export via psql
psql -h $PG_HOST -U $PG_USER -d $PG_DATABASE -c \
  "COPY (
    SELECT
      event_date, l.name AS language, event_type,
      c.card_number, c.theme, SUM(s.count) AS total
    FROM statistics s
    JOIN languages l ON s.language_id = l.id
    LEFT JOIN cards c ON s.card_id = c.id
    GROUP BY event_date, l.name, event_type, c.card_number, c.theme
    ORDER BY event_date DESC
  ) TO STDOUT WITH CSV HEADER" > stats_export.csv

# JSON export via admin API
curl "http://localhost:3000/api/statistics/by-language?from=2026-01-01&to=2026-12-31" \
  -b cookies.txt > stats_2026.json

---

Next steps

• 👉 API reference — statistics endpoints
• 👉 Database — statistics and quiz_sessions tables
• 👉 MCQ Revision module — quiz_sessions table