Changeset 1794fac in Klonkt

Timestamp:

06/19/2026 04:07:24 AM (3 months ago)

Author:

roboburr <roboburr@…>

Branches:

main

Children:

6be57b4

Parents:

37edecd

Message:

Pro stats (premium feature #5): referrers + period selector + all-time

Extension of the cookie-free StatsService.

• DB: stat_referrer (site_id, host, count).
• StatsService: recordPageview now also counts the external referrer host (Referer header; empty/own-site/invalid skipped, www- normalised). getStats(days) clamps to 7/14/30/90 + returns top-10 referrers (instance-wide) + all-time (pageviews, visitor days).
• admin-stats route: ?days=7|14|30|90.
• admin-stats view: period buttons, dynamic labels, Sources section, all-time row.

node --check passed.

Co-Authored-By: Claude <noreply@…>

Location:

src

Files:

• config/database.js (modified) (1 diff)
• routes/admin-stats.js (modified) (1 diff)
• services/StatsService.js (modified) (4 diffs)
• views/pages/admin-stats.ejs (modified) (3 diffs)

src/config/database.js

@@ -146 +146 @@
     );
     CREATE INDEX IF NOT EXISTS idx_stat_visitor_day ON stat_visitor_day(site_id, day);
+    CREATE TABLE IF NOT EXISTS stat_referrer (
+      site_id TEXT NOT NULL,
+      host TEXT NOT NULL,
+      count INTEGER NOT NULL DEFAULT 0,
+      PRIMARY KEY (site_id, host)
+    );
   `);
 

src/routes/admin-stats.js

@@ -31 +31 @@
     } catch { linkClicks = []; }
   }
+  const days = [7, 14, 30, 90].includes(parseInt(req.query.days, 10)) ? parseInt(req.query.days, 10) : 14;
   renderPage(req, res, 'pages/admin-stats', {
     pageTitle: 'Statistieken',
     bodyClass: 'on-admin',
-    stats: getStats(14),
+    stats: getStats(days),
     linkClicks,
   });

src/services/StatsService.js

@@ -58 +58 @@
     bumpPost: db.prepare('UPDATE posts SET view_count = COALESCE(view_count, 0) + 1 WHERE id = ?'),
     bumpTrack: db.prepare('UPDATE audio_tracks SET play_count = COALESCE(play_count, 0) + 1 WHERE id = ?'),
+    bumpReferrer: db.prepare(`
+      INSERT INTO stat_referrer (site_id, host, count) VALUES (?, ?, 1)
+      ON CONFLICT(site_id, host) DO UPDATE SET count = count + 1
+    `),
   };
   return _s;
+}
+
+// Externe referrer-host uit de Referer-header (pro-stats #5). Lege/eigen-site/
+// ongeldige referrers worden overgeslagen → alleen echte externe bronnen tellen.
+function recordReferrer(siteId, req) {
+  try {
+    const ref = req && req.headers && (req.headers.referer || req.headers.referrer);
+    if (!ref) return;
+    const host = new URL(ref).host.replace(/^www\./, '').toLowerCase();
+    if (!host) return;
+    const own = ((req.headers && req.headers.host) || '').replace(/^www\./, '').toLowerCase();
+    if (host === own) return; // interne navigatie telt niet als bron
+    stmts().bumpReferrer.run(siteId, host.slice(0, 120));
+  } catch { /* geen geldige referrer-URL → overslaan */ }
 }
 
@@ -68 +86 @@
     stmts().bumpDaily.run(siteId, d);
     stmts().addVisitor.run(siteId, d, visitorHash(req));
+    recordReferrer(siteId, req);
   } catch { /* statistieken mogen nooit een request breken */ }
 }
@@ -86 +105 @@
 // Instance-brede statistieken (solo = de site, hub = alle sites samen).
 export function getStats(days = 14) {
+  days = [7, 14, 30, 90].includes(Number(days)) ? Number(days) : 14;
   const pvMap = Object.fromEntries(
     db.prepare('SELECT day, SUM(pageviews) AS pv FROM stat_daily GROUP BY day').all().map((r) => [r.day, r.pv]),
@@ -113 +133 @@
     ORDER BY play_count DESC LIMIT 5
   `).all();
-  return { totals, series, topPosts, topTracks, days };
+  // Top externe bronnen (pro #5) — instance-breed geaggregeerd per host.
+  let referrers = [];
+  try {
+    referrers = db.prepare(
+      'SELECT host, SUM(count) AS n FROM stat_referrer GROUP BY host ORDER BY n DESC LIMIT 10'
+    ).all();
+  } catch { referrers = []; }
+  // All-time totalen (cookieloze unieke bezoekers = som van dag-uniques).
+  const allTime = {
+    pageviews: db.prepare('SELECT COALESCE(SUM(pageviews),0) AS n FROM stat_daily').get().n,
+    visitorDays: db.prepare('SELECT COUNT(*) AS n FROM stat_visitor_day').get().n,
+  };
+  return { totals, series, topPosts, topTracks, referrers, allTime, days };
 }

src/views/pages/admin-stats.ejs

@@ -9 +9 @@
 
   <div class="stats-cards">
-    <div class="stat-card"><div class="stat-num"><%= s.totals.visitors %></div><div class="stat-label">Bezoekers (14d)</div></div>
-    <div class="stat-card"><div class="stat-num"><%= s.totals.pageviews %></div><div class="stat-label">Weergaven (14d)</div></div>
+    <div class="stat-card"><div class="stat-num"><%= s.totals.visitors %></div><div class="stat-label">Bezoekers (<%= s.days %>d)</div></div>
+    <div class="stat-card"><div class="stat-num"><%= s.totals.pageviews %></div><div class="stat-label">Weergaven (<%= s.days %>d)</div></div>
     <div class="stat-card"><div class="stat-num"><%= s.totals.plays %></div><div class="stat-label">Plays (totaal)</div></div>
     <div class="stat-card"><div class="stat-num"><%= s.totals.postViews %></div><div class="stat-label">Post-weergaven (totaal)</div></div>
   </div>
 
+  <% if (typeof s.allTime !== 'undefined') { %>
+    <p class="stats-note">All-time: <strong><%= s.allTime.pageviews %></strong> weergaven · <strong><%= s.allTime.visitorDays %></strong> bezoeker-dagen.</p>
+  <% } %>
+
+  <div class="stats-period">
+    Periode:
+    <% [7,14,30,90].forEach(function(n){ %>
+      <a href="/admin/stats?days=<%= n %>" class="<%= s.days === n ? 'is-on' : '' %>"><%= n %>d</a>
+    <% }); %>
+  </div>
+
   <section class="stats-chart-wrap">
     <div class="stats-chart-head">
-      <h2>Laatste 14 dagen</h2>
+      <h2>Laatste <%= s.days %> dagen</h2>
       <div class="stats-legend">
         <span class="lg lg-vis">Bezoekers</span>
@@ -66 +77 @@
   </div>
 
+  <% if (typeof s.referrers !== 'undefined' && s.referrers.length) { %>
+    <section class="stats-top" style="margin-top:1.2rem">
+      <h2>🌐 Bronnen (waar bezoekers vandaan komen)</h2>
+      <ol>
+        <% s.referrers.forEach(function(r){ %>
+          <li><span><%= r.host %></span><span class="stats-count"><%= r.n %></span></li>
+        <% }); %>
+      </ol>
+    </section>
+  <% } %>
+
   <% if (typeof linkClicks !== 'undefined' && linkClicks.length) { %>
     <section class="stats-top" style="margin-top:1.2rem">
@@ -112 +134 @@
 .stats-count { color: var(--accent); font-weight: 700; font-variant-numeric: tabular-nums; }
 .stats-empty { color: var(--ink-muted); font-size: .88rem; margin: 0; }
+.stats-period { margin: .2rem 0 1.2rem; font-size: .9rem; color: var(--ink-muted); }
+.stats-period a { display: inline-block; margin-left: .4rem; padding: .2rem .6rem; border: 1px solid var(--rule); border-radius: 999px; text-decoration: none; color: var(--ink); }
+.stats-period a.is-on { background: var(--accent); color: #fff; border-color: var(--accent); }
 </style>