Changeset d549549 in Klonkt

Timestamp:

06/16/2026 09:41:28 AM (3 months ago)

Author:

roboburr <roboburr@…>

Branches:

main

Children:

029f047

Parents:

3f893bf

Message:

Statistics module (Phase 3): cookie-free tracking + premium stats page

Counters: posts.view_count, audio_tracks.play_count, stat_daily
(pageviews/day) + stat_visitor_day (unique visitors via daily rotating
salted hash of IP+UA, never stored → no cookie, no consent needed).
StatsService records pageview on home + post-view (skips admins), play on
the initial player stream fetch. /admin/stats (god-only +
premiumUnlocked-gated): cards + 14-day cookie-free bar chart
(visitors/views) + top posts/tracks. Dashboard button is premium-gated.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@…>

Location:

src

Files:

• config/database.js (modified) (2 diffs)
• routes/admin-stats.js (added)
• routes/audio.js (modified) (2 diffs)
• routes/posts.js (modified) (3 diffs)
• server.js (modified) (2 diffs)
• services/StatsService.js (added)
• views/pages/admin-stats.ejs (added)
• views/pages/admin.ejs (modified) (1 diff)

src/config/database.js

@@ -71 +71 @@
   ensureColumn('posts', 'type',    "TEXT DEFAULT 'post'");  // post | foto | video | audio
 
+  // Statistieken (premium-module) — kale tellers, cookievrij.
+  ensureColumn('posts', 'view_count', 'INTEGER DEFAULT 0');         // weergaven per post
+  ensureColumn('audio_tracks', 'play_count', 'INTEGER DEFAULT 0');  // plays per track
+
   // Playlists (v9 feature) — first-class entity. CREATE IF NOT EXISTS is
   // idempotent so it's safe to run on every boot regardless of DB age.
@@ -108 +112 @@
   `);
   db.prepare("INSERT OR IGNORE INTO app_settings (key, value) VALUES ('tenancy', 'solo')").run();
+
+  // ── Statistieken (premium) — cookievrij ─────────────────────
+  // stat_daily: per dag per site het aantal pageviews (kale teller).
+  // stat_visitor_day: per dag per site een rij per UNIEKE bezoeker-hash
+  //   (sha256 van IP+UA+dag-salt; de salt roteert dagelijks en wordt nooit
+  //   bewaard → geen persistente identifier, geen cookie, geen toestemming nodig).
+  db.exec(`
+    CREATE TABLE IF NOT EXISTS stat_daily (
+      site_id TEXT NOT NULL,
+      day TEXT NOT NULL,
+      pageviews INTEGER NOT NULL DEFAULT 0,
+      PRIMARY KEY (site_id, day)
+    );
+    CREATE TABLE IF NOT EXISTS stat_visitor_day (
+      site_id TEXT NOT NULL,
+      day TEXT NOT NULL,
+      visitor_hash TEXT NOT NULL,
+      PRIMARY KEY (site_id, day, visitor_hash)
+    );
+    CREATE INDEX IF NOT EXISTS idx_stat_visitor_day ON stat_visitor_day(site_id, day);
+  `);
 
   // ── Cirkels (federatie) ─────────────────────────────────────

src/routes/audio.js

@@ -28 +28 @@
 import path from 'path';
 import { fileURLToPath } from 'url';
+import db from '../config/database.js';
+import { recordPlay } from '../services/StatsService.js';
 
 const __dirname = path.dirname(fileURLToPath(import.meta.url));
@@ -90 +92 @@
   const range = req.headers.range;
 
+  // Statistieken: tel één play bij de initiële player-fetch (niet bij scrub/
+  // range-continuaties; replays binnen 24u komen uit de browsercache → geen
+  // dubbeltelling). Best-effort, mag nooit de stream breken.
+  if (req.get('X-Audio-Player') === '1' && (!range || /^bytes=0-/.test(range))) {
+    try {
+      const tr = db.prepare(`
+        SELECT t.id FROM audio_tracks t JOIN media m ON t.media_id = m.id
+        WHERE m.storage_path = ? OR m.storage_path LIKE ? LIMIT 1
+      `).get(filename, '%' + filename);
+      if (tr) recordPlay(tr.id);
+    } catch {}
+  }
+
   // Common headers
   res.setHeader('Content-Type', mime);

src/routes/posts.js

@@ -8 +8 @@
 import { requireAuth } from '../middleware/auth.js';
 import { renderPage } from '../middleware/render.js';
+import { recordPageview, recordPostView } from '../services/StatsService.js';
 import PermissionsService from '../services/PermissionsService.js';
 import MarkdownService from '../services/MarkdownService.js';
@@ -113 +114 @@
   `).all(site.id);
 
+  recordPageview(site.id, req);
+
   renderPage(req, res, 'pages/home', {
     pinnedPosts,
@@ -391 +394 @@
     if (!canEdit) return res.status(403).send('Not published');
   }
+
+  // Statistieken: tel de weergave (skipt beheerders + niet-gepubliceerd-eigen-preview).
+  if (post.status === 'published') recordPostView(post, req);
 
   // Render content. Content is now user-authored HTML (already sanitized on

src/server.js

@@ -49 +49 @@
 import adminUpdatesRoutes from './routes/admin-updates.js';
 import adminPatreonRoutes from './routes/admin-patreon.js';
+import adminStatsRoutes from './routes/admin-stats.js';
 import circleRoutes from './routes/circle.js';
 
@@ -241 +242 @@
 app.use('/admin/updates', adminUpdatesRoutes);
 app.use('/admin/patreon', adminPatreonRoutes);
+app.use('/admin/stats', adminStatsRoutes);
 app.use('/admin', adminRoutes);
 app.use('/prutter', prutterRoutes);

src/views/pages/admin.ejs

@@ -29 +29 @@
       <a href="/admin/settings" class="btn">⚙️ Instellingen</a>
     <% } %>
+    <% if (typeof premiumUnlocked === 'undefined' || premiumUnlocked) { %>
+      <a href="/admin/stats" class="btn">📊 Statistieken</a>
+    <% } %>
     <a href="/admin/updates" class="btn">🔄 Updates</a>
   </nav>