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

Timestamp:

07/22/2026 07:12:11 AM (7 weeks ago)

Author:

Robin <roboburr@…>

Branches:

main

Children:

053bf51

Parents:

dc4aa08

git-author:

Robin <roboburr@…> (07/22/2026 07:00:29 AM)

git-committer:

Robin <roboburr@…> (07/22/2026 07:12:11 AM)

Message:

Feature: web push slice 1, VAPID keys + subscription store

The foundation for background notifications (docs/webpush-design.md).

• Dependency (approved): web-push for RFC 8292 VAPID JWTs and RFC 8291 payload encryption. Lazy import so a canary that autofollows before npm ci never crashes on boot (same pattern as @simplewebauthn/server).
• VAPID keys: env (VAPID_PUBLIC_KEY/VAPID_PRIVATE_KEY/VAPID_SUBJECT) wins, else auto-generated once into storage/.vapid (0600), never regenerated while the file exists: new keys would invalidate every subscription. Subject: PUBLIC_BASE_URL, else mailto from SMTP_FROM.
• push_subscriptions table: one row per device, client keys for encrypted payloads, per-type alert preferences (follow/reply on, like/boost off, dm on by default), self-pruning on 404/410 in the send path.
• notifyUser/notifySite: honour alert prefs, cap title/body length, fire-and-forget at call sites (slice 3 wires the triggers).

Changed files:
package.json, package-lock.json

• web-push@3.6.7

src/config/database.js

• push_subscriptions table (additive)

src/routes/posts.js

• RESERVED_SLUGS: add 'push' (and the missing 'paid') so a post can't shadow the mounted routes

New file:
src/services/PushService.js

• VAPID key resolve/persist, subscription CRUD, encrypted send with pruning, notifyUser/notifySite

test/push.test.js

• key autogen (0600, persists, served=stored), subscription CRUD, upsert-not-duplicate, refuse incomplete payloads

-robo
Co-Authored-By: Claude Opus 4.8 <noreply@…>

File:

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

src/config/database.js

@@ -335 +335 @@
       expires_at INTEGER NOT NULL,      -- unix seconds; re-link after
       created_at DATETIME DEFAULT CURRENT_TIMESTAMP
+    );
+    -- Web Push (docs/webpush-design.md): one row per browser/device the owner
+    -- enabled notifications on. Payloads are encrypted to p256dh/auth (RFC 8291).
+    CREATE TABLE IF NOT EXISTS push_subscriptions (
+      endpoint TEXT PRIMARY KEY,       -- push-service URL for this device
+      user_id TEXT NOT NULL,
+      p256dh TEXT NOT NULL,            -- client public key
+      auth TEXT NOT NULL,              -- client auth secret
+      alert_types TEXT,                -- JSON {follow,reply,like,boost,dm}
+      ua_label TEXT,
+      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
+      last_ok_at DATETIME
     );
     CREATE TABLE IF NOT EXISTS ap_outbox (