this repo has no description
lewis.moe
1CREATE TYPE notification_channel AS ENUM ('email', 'discord', 'telegram', 'signal');
2CREATE TYPE notification_status AS ENUM ('pending', 'processing', 'sent', 'failed');
3CREATE TYPE notification_type AS ENUM (
4 'welcome',
5 'email_verification',
6 'password_reset',
7 'email_update',
8 'account_deletion',
9 'admin_email'
10);
11
12CREATE TABLE IF NOT EXISTS users (
13 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
14 handle TEXT NOT NULL UNIQUE,
15 email TEXT NOT NULL UNIQUE,
16 did TEXT NOT NULL UNIQUE,
17 password_hash TEXT NOT NULL,
18 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
19 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
20
21 -- status & moderation
22 deactivated_at TIMESTAMPTZ,
23 invites_disabled BOOLEAN DEFAULT FALSE,
24 takedown_ref TEXT,
25
26 -- notifs
27 preferred_notification_channel notification_channel NOT NULL DEFAULT 'email',
28
29 -- auth & verification
30 password_reset_code TEXT,
31 password_reset_code_expires_at TIMESTAMPTZ,
32
33 email_pending_verification TEXT,
34 email_confirmation_code TEXT,
35 email_confirmation_code_expires_at TIMESTAMPTZ
36);
37
38CREATE INDEX IF NOT EXISTS idx_users_password_reset_code ON users(password_reset_code) WHERE password_reset_code IS NOT NULL;
39CREATE INDEX IF NOT EXISTS idx_users_email_confirmation_code ON users(email_confirmation_code) WHERE email_confirmation_code IS NOT NULL;
40
41CREATE TABLE IF NOT EXISTS invite_codes (
42 code TEXT PRIMARY KEY,
43 available_uses INT NOT NULL DEFAULT 1,
44 created_by_user UUID NOT NULL REFERENCES users(id),
45 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
46 disabled BOOLEAN DEFAULT FALSE
47);
48
49CREATE TABLE IF NOT EXISTS invite_code_uses (
50 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
51 code TEXT NOT NULL REFERENCES invite_codes(code),
52 used_by_user UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
53 used_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
54 UNIQUE(code, used_by_user)
55);
56
57-- TODO: encrypt at rest!
58CREATE TABLE IF NOT EXISTS user_keys (
59 user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
60 key_bytes BYTEA NOT NULL,
61 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
62);
63
64CREATE TABLE IF NOT EXISTS repos (
65 user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
66 repo_root_cid TEXT NOT NULL,
67 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
68 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
69);
70
71-- content addressable storage
72CREATE TABLE IF NOT EXISTS blocks (
73 cid BYTEA PRIMARY KEY,
74 data BYTEA NOT NULL,
75 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
76);
77
78-- denormalized index for fast queries
79CREATE TABLE IF NOT EXISTS records (
80 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
81 repo_id UUID NOT NULL REFERENCES repos(user_id) ON DELETE CASCADE,
82 collection TEXT NOT NULL,
83 rkey TEXT NOT NULL,
84 record_cid TEXT NOT NULL,
85 takedown_ref TEXT,
86 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
87 UNIQUE(repo_id, collection, rkey)
88);
89
90CREATE TABLE IF NOT EXISTS blobs (
91 cid TEXT PRIMARY KEY,
92 mime_type TEXT NOT NULL,
93 size_bytes BIGINT NOT NULL,
94 created_by_user UUID NOT NULL REFERENCES users(id),
95 storage_key TEXT NOT NULL,
96 takedown_ref TEXT,
97 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
98);
99
100CREATE TABLE IF NOT EXISTS sessions (
101 access_jwt TEXT PRIMARY KEY,
102 refresh_jwt TEXT NOT NULL UNIQUE,
103 did TEXT NOT NULL REFERENCES users(did) ON DELETE CASCADE,
104 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
105);
106
107CREATE TABLE IF NOT EXISTS app_passwords (
108 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
109 user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
110 name TEXT NOT NULL,
111 password_hash TEXT NOT NULL,
112 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
113 privileged BOOLEAN NOT NULL DEFAULT FALSE,
114 UNIQUE(user_id, name)
115);
116
117-- naughty list
118CREATE TABLE reports (
119 id BIGINT PRIMARY KEY,
120 reason_type TEXT NOT NULL,
121 reason TEXT,
122 subject_json JSONB NOT NULL,
123 reported_by_did TEXT NOT NULL,
124 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
125);
126
127CREATE TABLE IF NOT EXISTS account_deletion_requests (
128 token TEXT PRIMARY KEY,
129 did TEXT NOT NULL REFERENCES users(did) ON DELETE CASCADE,
130 expires_at TIMESTAMPTZ NOT NULL,
131 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
132);
133
134CREATE TABLE IF NOT EXISTS notification_queue (
135 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
136 user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
137 channel notification_channel NOT NULL DEFAULT 'email',
138 notification_type notification_type NOT NULL,
139 status notification_status NOT NULL DEFAULT 'pending',
140 recipient TEXT NOT NULL,
141 subject TEXT,
142 body TEXT NOT NULL,
143 metadata JSONB,
144 attempts INT NOT NULL DEFAULT 0,
145 max_attempts INT NOT NULL DEFAULT 3,
146 last_error TEXT,
147 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
148 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
149 scheduled_for TIMESTAMPTZ NOT NULL DEFAULT NOW(),
150 processed_at TIMESTAMPTZ
151);
152
153CREATE INDEX idx_notification_queue_status_scheduled
154 ON notification_queue(status, scheduled_for)
155 WHERE status = 'pending';
156
157CREATE INDEX idx_notification_queue_user_id ON notification_queue(user_id);