flo-bit.dev
[READ-ONLY] Mirror of https://github.com/flo-bit/atproto-notify.
6.4 kB
163 lines
1-- Canonical schema for the atproto notification relay.
2-- All timestamps are unix milliseconds (Date.now()); booleans are 0/1 integers.
3
4CREATE TABLE users (
5 did TEXT PRIMARY KEY,
6 created_at INTEGER NOT NULL,
7 -- Account-default alert route: a '+'-joined token set (see lexicons/rpc.ts).
8 -- New accounts start at 'inbox' (recorded, no alerts) — it always "works" even
9 -- before any delivery channel is connected; the web app nudges the user to pick
10 -- a real route once they add their first channel.
11 default_route TEXT NOT NULL DEFAULT 'inbox',
12 -- Incoming-request policy: 'all' | 'trusted' (TRUSTED_SENDERS) | 'none'.
13 auto_allow TEXT NOT NULL DEFAULT 'trusted',
14 -- Where permission-request alerts go: a concrete route (token set) or 'off'.
15 pending_route TEXT NOT NULL DEFAULT 'off'
16);
17
18-- Unified delivery targets. Every place a notification can be delivered — a web
19-- push device, a Telegram chat, or a verified email — is one row, discriminated
20-- by `channel` ('push' | 'telegram' | 'email').
21-- * `ref` the channel's natural dedup key (push endpoint / chat id / email
22-- address). Globally unique per channel.
23-- * `id` a stable, opaque token a route references to target this one
24-- instance (e.g. one of several push devices). Survives re-link.
25-- * `label` display name (device name / Telegram username / email address).
26-- * `named` 1 once the user renames it — so the auto label (which for email/
27-- telegram is PII) is never exposed to apps unless user-chosen.
28-- * `verified` gates delivery: email starts 0 until a code is confirmed;
29-- push/telegram are created already verified (1).
30-- * `config` channel-specific JSON: push {p256dh, auth}; email {code, expires}
31-- while unverified (cleared once verified); telegram {}.
32CREATE TABLE delivery_targets (
33 id TEXT PRIMARY KEY,
34 did TEXT NOT NULL,
35 channel TEXT NOT NULL,
36 ref TEXT NOT NULL,
37 label TEXT,
38 named INTEGER NOT NULL DEFAULT 0,
39 verified INTEGER NOT NULL DEFAULT 1,
40 config TEXT NOT NULL DEFAULT '{}',
41 created_at INTEGER NOT NULL,
42 UNIQUE (channel, ref)
43);
44CREATE INDEX delivery_targets_by_did ON delivery_targets (did);
45
46-- Short-lived tokens for the Telegram linking handshake (deep-link → /start).
47-- `label` is an optional user-chosen name carried from the web form through the
48-- round-trip, applied to the delivery target on completion (named = 1).
49CREATE TABLE link_tokens (
50 token TEXT PRIMARY KEY,
51 did TEXT NOT NULL,
52 platform TEXT NOT NULL,
53 label TEXT,
54 expires_at INTEGER NOT NULL
55);
56CREATE INDEX link_tokens_by_did ON link_tokens (did);
57CREATE INDEX link_tokens_by_expires ON link_tokens (expires_at);
58
59-- Cached Bluesky profiles for senders (best-effort display metadata).
60CREATE TABLE senders (
61 did TEXT PRIMARY KEY,
62 handle TEXT,
63 display_name TEXT,
64 avatar_url TEXT,
65 profile_fetched_at INTEGER
66);
67
68-- A sender's request to notify a recipient, awaiting approval. The requester
69-- supplies user-facing display metadata (title/description/icon), copied onto the
70-- grant on approval.
71CREATE TABLE pending_requests (
72 id TEXT PRIMARY KEY,
73 recipient_did TEXT NOT NULL,
74 sender_did TEXT NOT NULL,
75 title TEXT,
76 description TEXT,
77 icon_url TEXT,
78 created_at INTEGER NOT NULL,
79 expires_at INTEGER NOT NULL,
80 UNIQUE (recipient_did, sender_did)
81);
82CREATE INDEX pending_by_recipient ON pending_requests (recipient_did);
83CREATE INDEX pending_by_expires ON pending_requests (expires_at);
84
85-- An approved (recipient, sender) pair. `manage` is the management capability the
86-- user designates for the app: 'none' | 'self' | 'full' (see MANAGEMENT-AUTH.md).
87-- New grants default to 'self' (an app may manage its own routing/inbox); the user
88-- can downgrade to 'none' or upgrade to 'full' per app.
89CREATE TABLE grants (
90 recipient_did TEXT NOT NULL,
91 sender_did TEXT NOT NULL,
92 granted_at INTEGER NOT NULL,
93 muted INTEGER NOT NULL DEFAULT 0,
94 title TEXT,
95 description TEXT,
96 icon_url TEXT,
97 manage TEXT NOT NULL DEFAULT 'self',
98 PRIMARY KEY (recipient_did, sender_did)
99);
100CREATE INDEX grants_by_recipient ON grants (recipient_did);
101
102-- One row per accepted `send`: how many alert channels it fanned out to.
103CREATE TABLE delivery_log (
104 id TEXT PRIMARY KEY,
105 recipient_did TEXT NOT NULL,
106 sender_did TEXT NOT NULL,
107 title TEXT,
108 delivered_count INTEGER NOT NULL,
109 created_at INTEGER NOT NULL
110);
111CREATE INDEX delivery_by_recipient ON delivery_log (recipient_did, created_at DESC);
112
113-- Inbox: the canonical history of every accepted `send`. Routing only decides
114-- which alert channels also fire; everything lands here regardless. `read_at`
115-- null = unread; `actors` is a JSON array of handles/DIDs.
116CREATE TABLE notifications (
117 id TEXT PRIMARY KEY,
118 recipient_did TEXT NOT NULL,
119 sender_did TEXT NOT NULL,
120 category TEXT,
121 title TEXT NOT NULL,
122 body TEXT NOT NULL,
123 uri TEXT,
124 actors TEXT,
125 created_at INTEGER NOT NULL,
126 read_at INTEGER
127);
128CREATE INDEX notifications_by_recipient ON notifications (recipient_did, created_at DESC);
129
130-- Categories per (recipient, sender): auto-discovered from `send`, or declared up
131-- front by an app (setCategories/addCategory). `category` is the routing key; the
132-- optional `title` is a human display name (e.g. a webhook's name). Per-user by
133-- construction (recipient_did in the PK) — never shared across users.
134CREATE TABLE app_categories (
135 recipient_did TEXT NOT NULL,
136 sender_did TEXT NOT NULL,
137 category TEXT NOT NULL,
138 title TEXT,
139 description TEXT,
140 last_seen INTEGER NOT NULL,
141 PRIMARY KEY (recipient_did, sender_did, category)
142);
143CREATE INDEX app_categories_by_pair ON app_categories (recipient_did, sender_did);
144
145-- Routing, resolved bottom-up per notification:
146-- category (routing) → app (app_routing) → account default (users.default_route)
147-- A missing row at a level means "inherit the next level up". Route values are
148-- '+'-joined token sets / 'off' (see lexicons/rpc.ts).
149CREATE TABLE routing (
150 recipient_did TEXT NOT NULL,
151 sender_did TEXT NOT NULL,
152 category TEXT NOT NULL,
153 route TEXT NOT NULL,
154 PRIMARY KEY (recipient_did, sender_did, category)
155);
156CREATE INDEX routing_by_recipient ON routing (recipient_did);
157
158CREATE TABLE app_routing (
159 recipient_did TEXT NOT NULL,
160 sender_did TEXT NOT NULL,
161 route TEXT NOT NULL,
162 PRIMARY KEY (recipient_did, sender_did)
163);