diffdown.com
Diffdown is a real-time collaborative Markdown editor/previewer built on the AT Protocol
diffdown.com
1-- 001_initial.sql
2
3CREATE TABLE IF NOT EXISTS users (
4 id TEXT PRIMARY KEY,
5 name TEXT NOT NULL,
6 email TEXT UNIQUE NOT NULL,
7 password_hash TEXT,
8 avatar_url TEXT DEFAULT '',
9 oauth_provider TEXT,
10 oauth_id TEXT,
11 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
12);
13
14CREATE UNIQUE INDEX IF NOT EXISTS idx_users_oauth ON users(oauth_provider, oauth_id)
15 WHERE oauth_provider IS NOT NULL;
16
17CREATE TABLE IF NOT EXISTS repos (
18 id TEXT PRIMARY KEY,
19 name TEXT NOT NULL,
20 slug TEXT NOT NULL,
21 description TEXT DEFAULT '',
22 owner_id TEXT NOT NULL REFERENCES users(id),
23 visibility TEXT DEFAULT 'private' CHECK(visibility IN ('private','public','unlisted')),
24 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
25 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
26);
27
28CREATE UNIQUE INDEX IF NOT EXISTS idx_repos_owner_slug ON repos(owner_id, slug);
29
30CREATE TABLE IF NOT EXISTS repo_collaborators (
31 repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
32 user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
33 role TEXT NOT NULL DEFAULT 'viewer' CHECK(role IN ('owner','editor','commenter','viewer')),
34 PRIMARY KEY (repo_id, user_id)
35);
36
37CREATE TABLE IF NOT EXISTS files (
38 id TEXT PRIMARY KEY,
39 repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
40 path TEXT NOT NULL,
41 content TEXT DEFAULT '',
42 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
43 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
44);
45
46CREATE UNIQUE INDEX IF NOT EXISTS idx_files_repo_path ON files(repo_id, path);
47
48CREATE TABLE IF NOT EXISTS versions (
49 id TEXT PRIMARY KEY,
50 file_id TEXT NOT NULL REFERENCES files(id) ON DELETE CASCADE,
51 content TEXT NOT NULL,
52 author_id TEXT NOT NULL REFERENCES users(id),
53 message TEXT DEFAULT '',
54 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
55);
56
57CREATE INDEX IF NOT EXISTS idx_versions_file ON versions(file_id, created_at DESC);
58
59CREATE TABLE IF NOT EXISTS comments (
60 id TEXT PRIMARY KEY,
61 file_id TEXT NOT NULL REFERENCES files(id) ON DELETE CASCADE,
62 version_id TEXT REFERENCES versions(id),
63 line_start INTEGER NOT NULL,
64 line_end INTEGER NOT NULL,
65 content_hash TEXT DEFAULT '',
66 body TEXT NOT NULL,
67 author_id TEXT NOT NULL REFERENCES users(id),
68 parent_id TEXT REFERENCES comments(id),
69 resolved INTEGER DEFAULT 0,
70 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
71);
72
73CREATE INDEX IF NOT EXISTS idx_comments_file ON comments(file_id);