aboutsummaryrefslogtreecommitdiffstats
path: root/adenosine-pds/src/atp_db.sql
blob: e26ac4ebd437a3a682d16c3a6a343985ad3222e9 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

----------- atproto PDS system tables

CREATE TABLE account(
    did                 TEXT PRIMARY KEY NOT NULL,
    handle              TEXT NOT NULL,
    email               TEXT NOT NULL,
    password_bcrypt     TEXT NOT NULL,
    recovery_pubkey     TEXT NOT NULL,
    created_at          TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( strftime('%Y-%m-%dT%H:%M:%fZ', 'now') )
);
CREATE UNIQUE INDEX account_handle_uniq_idx on account(lower(handle));
CREATE UNIQUE INDEX account_email_uniq_idx on account(lower(email));

CREATE TABLE did_doc(
    did                 TEXT PRIMARY KEY NOT NULL,
    -- TODO: handle              TEXT NOT NULL,
    doc_json            TEXT NOT NULL,
    indexed_at          TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( strftime('%Y-%m-%dT%H:%M:%fZ', 'now') )
);

CREATE TABLE session(
    did                 TEXT NOT NULL,
    jwt                 TEXT NOT NULL,
    created_at          TIMESTAMP WITH TIME ZONE  NOT NULL DEFAULT ( strftime('%Y-%m-%dT%H:%M:%fZ', 'now') ),
    PRIMARY KEY(did, jwt)
);

----------- bsky app/index tables

CREATE TABLE bsky_post(
    did                 TEXT NOT NULL,
    tid                 TEXT NOT NULL,
    cid                 TEXT NOT NULL,
    record_json         TEXT NOT NULL,
    reply_to_parent_uri    TEXT,
    reply_to_root_uri      TEXT,
    created_at          TIMESTAMP WITH TIME ZONE  NOT NULL,
    indexed_at          TIMESTAMP WITH TIME ZONE  NOT NULL DEFAULT ( strftime('%Y-%m-%dT%H:%M:%fZ', 'now') ),
    PRIMARY KEY(did, tid)
);
CREATE INDEX bsky_post_reply_to_parent_uri_idx on bsky_post(reply_to_parent_uri);
CREATE INDEX bsky_post_reply_to_root_uri_idx on bsky_post(reply_to_root_uri);

CREATE TABLE bsky_ref(
    ref_type            TEXT NOT NULL,
    did                 TEXT NOT NULL,
    tid                 TEXT NOT NULL,
    subject_uri         TEXT NOT NULL,
    -- TODO: NOT NULL on subject_cid
    subject_cid         TEXT,
    created_at          TIMESTAMP WITH TIME ZONE  NOT NULL,
    indexed_at          TIMESTAMP WITH TIME ZONE  NOT NULL DEFAULT ( strftime('%Y-%m-%dT%H:%M:%fZ', 'now') ),
    PRIMARY KEY(ref_type, did, tid)
);
CREATE INDEX bsky_ref_subject_uri_idx on bsky_ref(subject_uri);

CREATE TABLE bsky_follow(
    did                 TEXT NOT NULL,
    tid                 TEXT NOT NULL,
    subject_did         TEXT NOT NULL,
    -- TODO: NOT NULL on subject_cid
    subject_cid         TEXT,
    created_at          TIMESTAMP WITH TIME ZONE  NOT NULL,
    indexed_at          TIMESTAMP WITH TIME ZONE  NOT NULL DEFAULT ( strftime('%Y-%m-%dT%H:%M:%fZ', 'now') ),
    PRIMARY KEY(did, tid)
);
CREATE INDEX bsky_follow_subject_did_idx on bsky_follow(subject_did);

-- TODO: notifications
CREATE TABLE bsky_notification(
    pk                  INTEGER PRIMARY KEY AUTOINCREMENT,
    user_did            TEXT NOT NULL,
    subject_uri         TEXT NOT NULL,
    -- TODO: NOT NULL on subject_cid
    subject_cid         TEXT,
    reason              TEXT NOT NULL,
    seen_at             TIMESTAMP WITH TIME ZONE,
    indexed_at          TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( strftime('%Y-%m-%dT%H:%M:%fZ', 'now') )
);