Heavily customized version of smokesignal - https://whtwnd.com/kayrozen.com/3lpwe4ymowg2t
kayrozen.com
1-- Event bookmarks migration
2-- This creates the core tables for the bookmark calendar feature
3
4-- Event bookmarks table (local cache for performance)
5CREATE TABLE event_bookmarks (
6 id SERIAL PRIMARY KEY,
7 did VARCHAR(512) NOT NULL,
8 bookmark_aturi VARCHAR(1024) NOT NULL, -- ATproto bookmark record URI
9 event_aturi VARCHAR(1024) NOT NULL, -- Event being bookmarked
10 tags TEXT[] NOT NULL DEFAULT '{}', -- Tags for organization
11 synced_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
12 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
13 UNIQUE(did, event_aturi), -- Prevent duplicate event bookmarks
14 FOREIGN KEY (event_aturi) REFERENCES events(aturi) ON DELETE CASCADE
15);
16
17-- Bookmark calendars table (user-created collections)
18CREATE TABLE bookmark_calendars (
19 id SERIAL PRIMARY KEY,
20 calendar_id VARCHAR(64) NOT NULL, -- Public identifier for sharing
21 did VARCHAR(512) NOT NULL,
22 name VARCHAR(256) NOT NULL,
23 description TEXT DEFAULT NULL,
24 tags TEXT[] NOT NULL, -- Tags that define this calendar
25 tag_operator VARCHAR(16) NOT NULL DEFAULT 'OR', -- 'AND' or 'OR' for tag matching
26 is_public BOOLEAN NOT NULL DEFAULT FALSE,
27 event_count INTEGER NOT NULL DEFAULT 0,
28 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
29 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
30 UNIQUE(calendar_id),
31 CHECK(array_length(tags, 1) > 0), -- Ensure at least one tag per calendar
32 CHECK(tag_operator IN ('AND', 'OR')),
33 CHECK(array_length(tags, 1) <= 10) -- Maximum 10 tags per calendar
34);
35
36-- Indexes for event_bookmarks
37CREATE INDEX idx_event_bookmarks_did ON event_bookmarks(did);
38CREATE INDEX idx_event_bookmarks_event_aturi ON event_bookmarks(event_aturi);
39CREATE INDEX idx_event_bookmarks_tags ON event_bookmarks USING GIN(tags);
40CREATE INDEX idx_event_bookmarks_synced_at ON event_bookmarks(synced_at);
41
42-- Indexes for bookmark_calendars
43CREATE INDEX idx_bookmark_calendars_did ON bookmark_calendars(did);
44CREATE INDEX idx_bookmark_calendars_public ON bookmark_calendars(is_public) WHERE is_public = true;
45CREATE INDEX idx_bookmark_calendars_tags ON bookmark_calendars USING GIN(tags);
46CREATE INDEX idx_bookmark_calendars_calendar_id ON bookmark_calendars(calendar_id);