this repo has no description
lewis.moe
1-- A very basic schema to get started.
2-- TODO: PRODUCTIONIZE BABY
3
4CREATE TABLE IF NOT EXISTS users (
5 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
6 handle TEXT NOT NULL UNIQUE,
7 email TEXT NOT NULL UNIQUE,
8 did TEXT NOT NULL UNIQUE,
9 password_hash TEXT NOT NULL,
10 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
11 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
12);
13
14CREATE TABLE IF NOT EXISTS invite_codes (
15 code TEXT PRIMARY KEY,
16 available_uses INT NOT NULL DEFAULT 1,
17 created_by_user UUID NOT NULL REFERENCES users(id),
18 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
19);
20
21CREATE TABLE IF NOT EXISTS invite_code_uses (
22 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
23 code TEXT NOT NULL REFERENCES invite_codes(code),
24 used_by_user UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
25 used_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
26 UNIQUE(code, used_by_user)
27);
28
29-- OIII THIS TABLE CONTAINS PLAINTEXT PRIVATE KEYS, TODO: encrypt at rest!
30CREATE TABLE IF NOT EXISTS user_keys (
31 user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
32 -- Storing as raw bytes
33 -- secp256k1 is 32 bytes
34 key_bytes BYTEA NOT NULL,
35 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
36);
37
38CREATE TABLE IF NOT EXISTS repos (
39 user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
40 repo_root_cid TEXT NOT NULL,
41 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
42 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
43);
44
45CREATE TABLE IF NOT EXISTS blocks (
46 cid BYTEA PRIMARY KEY,
47 data BYTEA NOT NULL,
48 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
49);
50
51-- A denormalized table to quickly query for records
52-- TODO: Do I actually need this?
53CREATE TABLE IF NOT EXISTS records (
54 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
55 repo_id UUID NOT NULL REFERENCES repos(user_id) ON DELETE CASCADE,
56 collection TEXT NOT NULL,
57 rkey TEXT NOT NULL,
58 record_cid TEXT NOT NULL,
59 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
60 UNIQUE(repo_id, collection, rkey)
61);
62
63CREATE TABLE IF NOT EXISTS blobs (
64 cid TEXT PRIMARY KEY,
65 mime_type TEXT NOT NULL,
66 size_bytes BIGINT NOT NULL,
67 created_by_user UUID NOT NULL REFERENCES users(id),
68 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
69
70 -- The key/path in the S3 bucket
71 storage_key TEXT NOT NULL
72);
73
74CREATE TABLE IF NOT EXISTS sessions (
75 access_jwt TEXT PRIMARY KEY,
76 refresh_jwt TEXT NOT NULL UNIQUE,
77 did TEXT NOT NULL REFERENCES users(did) ON DELETE CASCADE,
78 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
79);
80