summaryrefslogtreecommitdiffstats
path: root/adenosine-pds/src/atp_db.sql
blob: 918a89c30c7361ee6f60522d396cd0514c15e9a9 (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

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

CREATE TABLE account(
    did                 TEXT PRIMARY KEY NOT NULL,
    username            TEXT NOT NULL,
    email               TEXT NOT NULL,
    password_bcrypt     TEXT NOT NULL,
    signing_key         TEXT NOT NULL,
);
CREATE UNIQUE INDEX account_username_uniq_idx on account(lower(username));
CREATE UNIQUE INDEX account_email_uniq_idx on account(lower(email));

CREATE TABLE did_doc(
    did                 TEXT PRIMARY KEY NOT NULL,
    doc_json            TEXT NOT NULL,
    seen_at             TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
);

CREATE TABLE session(
    did                 TEXT NOT NULL,
    jwt                 TEXT NOT NULL,
    created_at          TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
    PRIMARY KEY(did, jwt)
);

CREATE TABLE repo(
    did                 TEXT PRIMARY KEY NOT NULL,
    head_commit         TEXT NOT NULL,
);

CREATE TABLE record(
    did                 TEXT NOT NULL,
    collection          TEXT NOT NULL,
    tid                 TEXT NOT NULL,
    record_cid          TEXT NOT NULL,
    record_json         TEXT NOT NULL,
    PRIMARY KEY(did, collection, tid)
);

CREATE TABLE password_reset(
    did                 TEXT NOT NULL,
    token               TEXT NOT NULL,
    PRIMARY KEY(did, token)
);


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