diff options
Diffstat (limited to 'adenosine-pds/src/atp_db.sql')
| -rw-r--r-- | adenosine-pds/src/atp_db.sql | 65 |
1 files changed, 49 insertions, 16 deletions
diff --git a/adenosine-pds/src/atp_db.sql b/adenosine-pds/src/atp_db.sql index a6fce1c..259ab9d 100644 --- a/adenosine-pds/src/atp_db.sql +++ b/adenosine-pds/src/atp_db.sql @@ -1,20 +1,22 @@ ------------ atproto system tables +----------- atproto PDS 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 + recovery_pubkey TEXT NOT NULL, + created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( DATETIME('now') ) ); 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, + -- TODO: username TEXT NOT NULL, doc_json TEXT NOT NULL, - seen_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( DATETIME('now') ) + indexed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( DATETIME('now') ) ); CREATE TABLE session( @@ -24,26 +26,57 @@ CREATE TABLE session( PRIMARY KEY(did, jwt) ); -CREATE TABLE repo( - did TEXT PRIMARY KEY NOT NULL, - head_commit TEXT NOT NULL -); +----------- bsky app/index tables -CREATE TABLE record( +CREATE TABLE bsky_post( did TEXT NOT NULL, - collection TEXT NOT NULL, tid TEXT NOT NULL, - record_cid TEXT NOT NULL, + cid TEXT NOT NULL, record_json TEXT NOT NULL, - PRIMARY KEY(did, collection, tid) + reply_root_uri TEXT, + created_at TIMESTAMP WITH TIME ZONE NOT NULL, + indexed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( DATETIME('now') ), + PRIMARY KEY(did, tid) ); +CREATE INDEX bsky_post_reply_root_uri_idx on bsky_post(reply_root_uri); -CREATE TABLE password_reset( +CREATE TABLE bsky_repost( did TEXT NOT NULL, - token TEXT NOT NULL, - PRIMARY KEY(did, token) + subject_uri TEXT NOT NULL, + cid TEXT NOT NULL, + created_at TIMESTAMP WITH TIME ZONE NOT NULL, + indexed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( DATETIME('now') ), + PRIMARY KEY(did, subject_uri) ); +CREATE INDEX bsky_repost_subject_uri_idx on bsky_repost(subject_uri); ------------ bsky app/index tables +CREATE TABLE bsky_like( + did TEXT NOT NULL, + subject_uri TEXT NOT NULL, + cid TEXT NOT NULL, + created_at TIMESTAMP WITH TIME ZONE NOT NULL, + indexed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( DATETIME('now') ), + PRIMARY KEY(did, subject_uri) +); +CREATE INDEX bsky_like_subject_uri_idx on bsky_like(subject_uri); --- TODO +CREATE TABLE bsky_follow( + did TEXT NOT NULL, + subject_did TEXT NOT NULL, + cid TEXT NOT NULL, + created_at TIMESTAMP WITH TIME ZONE NOT NULL, + indexed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( DATETIME('now') ), + PRIMARY KEY(did, subject_did) +); +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, + subject_cid TEXT NOT NULL, + reason TEXT NOT NULL, + seen_at TIMESTAMP WITH TIME ZONE, + indexed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT ( DATETIME('now') ), +); |