learn and share notes on atproto (wip) 馃
malfestio.stormlightlabs.org/
readability
solid
axum
atproto
srs
1-- Initial schema for Malfestio
2-- This migration creates the core tables for decks, notes, and cards
3-- Note: ATProto lexicon records go to PDS, this DB is for blob storage & private data
4
5CREATE TABLE IF NOT EXISTS schema_migrations (
6 id SERIAL PRIMARY KEY,
7 version TEXT NOT NULL UNIQUE,
8 applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
9);
10
11CREATE TABLE decks (
12 id UUID PRIMARY KEY,
13 owner_did TEXT NOT NULL,
14 title TEXT NOT NULL,
15 description TEXT NOT NULL,
16 tags TEXT[] NOT NULL DEFAULT '{}',
17 visibility JSONB NOT NULL, -- Stores { type: "Private" | "Unlisted" | "Public" | "SharedWith", content?: [...] }
18 published_at TIMESTAMPTZ,
19 fork_of UUID,
20 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
21 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
22);
23
24CREATE INDEX idx_decks_owner_did ON decks(owner_did);
25CREATE INDEX idx_decks_visibility ON decks USING GIN(visibility);
26CREATE INDEX idx_decks_created_at ON decks(created_at DESC);
27
28CREATE TABLE cards (
29 id UUID PRIMARY KEY,
30 owner_did TEXT NOT NULL,
31 deck_id UUID NOT NULL REFERENCES decks(id) ON DELETE CASCADE,
32 front TEXT NOT NULL,
33 back TEXT NOT NULL,
34 media_url TEXT,
35 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
36 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
37);
38
39CREATE INDEX idx_cards_deck_id ON cards(deck_id);
40CREATE INDEX idx_cards_owner_did ON cards(owner_did);
41
42CREATE TABLE notes (
43 id UUID PRIMARY KEY,
44 owner_did TEXT NOT NULL,
45 title TEXT NOT NULL,
46 body TEXT NOT NULL,
47 tags TEXT[] NOT NULL DEFAULT '{}',
48 visibility JSONB NOT NULL,
49 published_at TIMESTAMPTZ,
50 links TEXT[] NOT NULL DEFAULT '{}', -- WikiLink style references to other notes
51 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
52 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
53);
54
55CREATE INDEX idx_notes_owner_did ON notes(owner_did);
56CREATE INDEX idx_notes_visibility ON notes USING GIN(visibility);
57CREATE INDEX idx_notes_created_at ON notes(created_at DESC);
58CREATE INDEX idx_notes_links ON notes USING GIN(links);
59
60CREATE OR REPLACE FUNCTION update_updated_at_column()
61RETURNS TRIGGER AS $$
62BEGIN
63 NEW.updated_at = NOW();
64 RETURN NEW;
65END;
66$$ LANGUAGE plpgsql;
67
68CREATE TRIGGER update_decks_updated_at BEFORE UPDATE ON decks
69 FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
70
71CREATE TRIGGER update_cards_updated_at BEFORE UPDATE ON cards
72 FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
73
74CREATE TRIGGER update_notes_updated_at BEFORE UPDATE ON notes
75 FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();