The attodo.app, uhh... app.
shindakun.net
1-- Initial database schema for notification system
2-- Phase 2: Server-side tracking and push subscriptions
3
4-- Users who have enabled notifications
5CREATE TABLE IF NOT EXISTS notification_users (
6 did TEXT PRIMARY KEY,
7 notifications_enabled BOOLEAN NOT NULL DEFAULT 1,
8 last_checked_at DATETIME,
9 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
10 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
11);
12
13-- Index for efficient queries of enabled users
14CREATE INDEX IF NOT EXISTS idx_notification_users_enabled
15ON notification_users(notifications_enabled) WHERE notifications_enabled = 1;
16
17-- Push subscriptions (browser endpoints)
18-- Each user can have multiple devices/browsers subscribed
19CREATE TABLE IF NOT EXISTS push_subscriptions (
20 id INTEGER PRIMARY KEY AUTOINCREMENT,
21 did TEXT NOT NULL,
22 endpoint TEXT NOT NULL UNIQUE,
23 p256dh_key TEXT NOT NULL,
24 auth_secret TEXT NOT NULL,
25 user_agent TEXT,
26 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
27 last_used_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
28 FOREIGN KEY (did) REFERENCES notification_users(did) ON DELETE CASCADE
29);
30
31-- Index for efficient lookups by DID
32CREATE INDEX IF NOT EXISTS idx_push_subscriptions_did
33ON push_subscriptions(did);
34
35-- Index for cleanup of stale subscriptions
36CREATE INDEX IF NOT EXISTS idx_push_subscriptions_last_used
37ON push_subscriptions(last_used_at);
38
39-- Notification history (prevent spam, track delivery)
40CREATE TABLE IF NOT EXISTS notification_history (
41 id INTEGER PRIMARY KEY AUTOINCREMENT,
42 did TEXT NOT NULL,
43 task_uri TEXT NOT NULL,
44 notification_type TEXT NOT NULL CHECK(notification_type IN ('overdue', 'due_today', 'due_soon')),
45 sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
46 status TEXT NOT NULL CHECK(status IN ('sent', 'failed', 'expired')),
47 error_message TEXT
48);
49
50-- Index for spam prevention (check if we recently notified about this task)
51CREATE INDEX IF NOT EXISTS idx_notification_history_task
52ON notification_history(did, task_uri, sent_at);
53
54-- Index for cleanup of old history
55CREATE INDEX IF NOT EXISTS idx_notification_history_sent_at
56ON notification_history(sent_at);