Changeset 2e247e4 in Klonkt for src/config

Timestamp:

06/19/2026 12:00:48 AM (3 months ago)

Author:

roboburr <roboburr@…>

Branches:

main

Children:

91094a4

Parents:

255e3d3

Message:

Newsletter / mailing list (premium feature #1) + SMTP wiring

Email infrastructure reuses src/config/mailer.js (env SMTP_*, graceful: without
SMTP no sending, sign-ups still work via single opt-in).

• DB: subscribers table (per-site, status pending/confirmed/unsub, token) + newsletters table (send history). Idempotent in initializeDatabase.
• SubscriberService: addSubscriber (double opt-in if SMTP, otherwise single), confirm, unsubscribe, confirmedFor, counts. Email validation + dedupe per (site, email).
• routes/newsletter.js (public, premium-gated; unsubscribe always works): GET/POST /nieuwsbrief, /nieuwsbrief/bevestigen/:token, /nieuwsbrief/uitschrijven/:token. Confirm/unsub links absolute via PUBLIC_BASE_URL + siteUrlBase.
• routes/admin-newsletter.js (site admin + premium): GET /admin/newsletter (compose + counts + history + shareable signup link + SMTP warning), POST /admin/newsletter/send (to confirmed subscribers, unsubscribe link per email, history written).
• views: pages/newsletter.ejs (multi-state) + pages/admin-newsletter.ejs.
• admin dashboard: premium links Newsletter + Press kit added.

Feature #2 (download-for-email) and #8 (notify) reuse subscribers. node --check
passed; local test suite missing better-sqlite3 (runs on the VPS).

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

File:

• src/config/database.js (modified) (1 diff)

src/config/database.js

@@ -188 +188 @@
   // Tags van de originele post — getoond in de cirkel (comma-separated string).
   ensureColumn('remote_posts', 'tags', 'TEXT');
+
+  // Nieuwsbrief / mailinglijst (premium). Abonnees per site; double opt-in als SMTP
+  // er is (status 'pending' tot bevestigd), anders single opt-in ('confirmed').
+  // 'unsub' = uitgeschreven. token = confirm/unsubscribe-sleutel (in de e-maillinks).
+  db.exec(`
+    CREATE TABLE IF NOT EXISTS subscribers (
+      id TEXT PRIMARY KEY,
+      site_id TEXT NOT NULL,
+      email TEXT NOT NULL,
+      status TEXT NOT NULL DEFAULT 'pending',
+      source TEXT DEFAULT 'widget',
+      token TEXT NOT NULL,
+      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
+      confirmed_at DATETIME,
+      UNIQUE(site_id, email)
+    );
+    CREATE INDEX IF NOT EXISTS idx_subscribers_site_status ON subscribers(site_id, status);
+  `);
+
+  // Verstuurde nieuwsbrieven (historie + aantallen).
+  db.exec(`
+    CREATE TABLE IF NOT EXISTS newsletters (
+      id TEXT PRIMARY KEY,
+      site_id TEXT NOT NULL,
+      subject TEXT NOT NULL,
+      body TEXT NOT NULL,
+      sent_at DATETIME DEFAULT CURRENT_TIMESTAMP,
+      recipient_count INTEGER DEFAULT 0
+    );
+  `);
 }