diffdown.com
Diffdown is a real-time collaborative Markdown editor/previewer built on the AT Protocol
diffdown.com
1-- 001_baseline.sql
2-- Consolidated baseline schema replacing migrations 001–008.
3
4CREATE TABLE users (
5 id TEXT PRIMARY KEY,
6 did TEXT UNIQUE NOT NULL
7);
8
9CREATE TABLE atproto_sessions (
10 user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
11 did TEXT NOT NULL,
12 pds_url TEXT NOT NULL,
13 access_token TEXT NOT NULL,
14 refresh_token TEXT NOT NULL,
15 dpop_key_jwk TEXT NOT NULL,
16 dpop_nonce TEXT DEFAULT '',
17 token_endpoint TEXT NOT NULL,
18 expires_at DATETIME NOT NULL,
19 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
20);
21
22CREATE TABLE invites (
23 id TEXT PRIMARY KEY,
24 document_rkey TEXT NOT NULL,
25 token TEXT NOT NULL UNIQUE,
26 created_by_did TEXT NOT NULL,
27 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
28 expires_at DATETIME NOT NULL
29);
30
31CREATE INDEX idx_invites_document ON invites(document_rkey);
32CREATE INDEX idx_invites_token ON invites(token);
33
34CREATE TABLE doc_steps (
35 id INTEGER PRIMARY KEY AUTOINCREMENT,
36 doc_rkey TEXT NOT NULL,
37 version INTEGER NOT NULL,
38 step_json TEXT NOT NULL,
39 client_id TEXT NOT NULL,
40 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
41 UNIQUE(doc_rkey, version)
42);
43
44CREATE INDEX idx_doc_steps_rkey_version ON doc_steps(doc_rkey, version);
45
46CREATE TABLE collaborations (
47 collaborator_did TEXT NOT NULL,
48 owner_did TEXT NOT NULL,
49 document_rkey TEXT NOT NULL,
50 added_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
51 PRIMARY KEY (collaborator_did, owner_did, document_rkey)
52);
53
54CREATE INDEX idx_collaborations_did_added ON collaborations(collaborator_did, added_at DESC);