desertthunder.dev
learn and share notes on atproto (wip) 馃
malfestio.stormlightlabs.org/
readability
solid
axum
atproto
srs
1-- Social Layer: Follows and Comments
2-- Implements Milestone H requirements
3
4-- Follows table: User A follows User B
5CREATE TABLE follows (
6 follower_did TEXT NOT NULL,
7 subject_did TEXT NOT NULL,
8 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
9 PRIMARY KEY (follower_did, subject_did)
10);
11
12CREATE INDEX idx_follows_follower ON follows(follower_did);
13CREATE INDEX idx_follows_subject_did ON follows(subject_did);
14
15-- Comments table: Threaded comments on Decks (and potentially Cards in future)
16CREATE TABLE comments (
17 id UUID PRIMARY KEY,
18 deck_id UUID NOT NULL REFERENCES decks(id) ON DELETE CASCADE,
19 author_did TEXT NOT NULL,
20 content TEXT NOT NULL,
21 parent_id UUID REFERENCES comments(id) ON DELETE CASCADE, -- For threading
22 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
23 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
24);
25
26CREATE INDEX idx_comments_deck_id ON comments(deck_id);
27CREATE INDEX idx_comments_parent_id ON comments(parent_id);
28CREATE INDEX idx_comments_author_did ON comments(author_did);
29CREATE INDEX idx_comments_created_at ON comments(created_at);
30
31CREATE TRIGGER update_comments_updated_at BEFORE UPDATE ON comments
32 FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();