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

----------- 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 NOT NULL DEFAULT ( DATETIME('now') )
);

CREATE TABLE session(
    did                 TEXT NOT NULL,
    jwt                 TEXT NOT NULL,
    created_at          TIMESTAMP WITH TIME ZONE  NOT NULL DEFAULT ( DATETIME('now') ),
    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

-- TODO