pds: more skeleton progress

1 files changed, 50 insertions, 0 deletions

diff --git a/adenosine-pds/src/atp_db.sql b/adenosine-pds/src/atp_db.sql
new file mode 100644
index 0000000..918a89c
--- /dev/null
+++ b/adenosine-pds/src/atp_db.sql
@@ -0,0 +1,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
+
+