Changeset d549549 in Klonkt for src/config/database.js

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@…>

File:

• src/config/database.js (modified) (2 diffs)

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) ─────────────────────────────────────