nandi.uk
This repository has no description
1import { sql } from 'drizzle-orm';
2import { PostgresJsDatabase } from 'drizzle-orm/postgres-js';
3
4export async function createTestSchema(db: PostgresJsDatabase) {
5 // Create extension
6 await db.execute(sql`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`);
7
8 // Create tables in dependency order using raw SQL with proper column names
9 const tableCreationQueries = [
10 // Published records table (no dependencies)
11 sql`CREATE TABLE IF NOT EXISTS published_records (
12 id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
13 uri TEXT NOT NULL,
14 cid TEXT NOT NULL,
15 recorded_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
16 UNIQUE(uri, cid)
17 )`,
18
19 // Cards table (references published_records and self-references)
20 sql`CREATE TABLE IF NOT EXISTS cards (
21 id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
22 author_id TEXT NOT NULL,
23 type TEXT NOT NULL,
24 content_data JSONB NOT NULL,
25 url TEXT,
26 url_type TEXT,
27 parent_card_id UUID REFERENCES cards(id),
28 via_card_id UUID REFERENCES cards(id),
29 published_record_id UUID REFERENCES published_records(id),
30 library_count INTEGER NOT NULL DEFAULT 0,
31 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
32 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
33 )`,
34
35 // Library memberships table (references cards and published_records)
36 sql`CREATE TABLE IF NOT EXISTS library_memberships (
37 card_id UUID NOT NULL REFERENCES cards(id) ON DELETE CASCADE,
38 user_id TEXT NOT NULL,
39 added_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
40 published_record_id UUID REFERENCES published_records(id),
41 PRIMARY KEY (card_id, user_id)
42 )`,
43
44 // Collections table (references published_records)
45 sql`CREATE TABLE IF NOT EXISTS collections (
46 id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
47 author_id TEXT NOT NULL,
48 name TEXT NOT NULL,
49 description TEXT,
50 access_type TEXT NOT NULL,
51 card_count INTEGER NOT NULL DEFAULT 0,
52 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
53 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
54 published_record_id UUID REFERENCES published_records(id)
55 )`,
56
57 // Collection collaborators table (references collections)
58 sql`CREATE TABLE IF NOT EXISTS collection_collaborators (
59 id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
60 collection_id UUID NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
61 collaborator_id TEXT NOT NULL,
62 UNIQUE(collection_id, collaborator_id)
63 )`,
64
65 // Collection cards table (references collections, cards, and published_records)
66 sql`CREATE TABLE IF NOT EXISTS collection_cards (
67 id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
68 collection_id UUID NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
69 card_id UUID NOT NULL REFERENCES cards(id) ON DELETE CASCADE,
70 added_by TEXT NOT NULL,
71 added_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
72 via_card_id UUID REFERENCES cards(id),
73 published_record_id UUID REFERENCES published_records(id),
74 UNIQUE(collection_id, card_id)
75 )`,
76 sql`
77 CREATE TABLE IF NOT EXISTS feed_activities (
78 id UUID PRIMARY KEY,
79 actor_id TEXT NOT NULL,
80 card_id TEXT,
81 type TEXT NOT NULL,
82 metadata JSONB NOT NULL,
83 url_type TEXT,
84 source TEXT,
85 created_at TIMESTAMP NOT NULL DEFAULT NOW()
86 )`,
87
88 // Notifications table (no dependencies)
89 sql`CREATE TABLE IF NOT EXISTS notifications (
90 id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
91 recipient_user_id TEXT NOT NULL,
92 actor_user_id TEXT NOT NULL,
93 type TEXT NOT NULL,
94 metadata JSONB NOT NULL,
95 read BOOLEAN NOT NULL DEFAULT false,
96 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
97 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
98 )`,
99
100 // Sync statuses table (no dependencies)
101 sql`CREATE TABLE IF NOT EXISTS sync_statuses (
102 id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
103 curator_id TEXT NOT NULL UNIQUE,
104 sync_state TEXT NOT NULL,
105 last_synced_at TIMESTAMP WITH TIME ZONE,
106 last_sync_attempt_at TIMESTAMP WITH TIME ZONE,
107 sync_error_message TEXT,
108 records_processed INTEGER,
109 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
110 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
111 )`,
112
113 // Follows table (no dependencies)
114 sql`CREATE TABLE IF NOT EXISTS follows (
115 follower_id TEXT NOT NULL,
116 target_id TEXT NOT NULL,
117 target_type TEXT NOT NULL,
118 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
119 PRIMARY KEY (follower_id, target_id, target_type)
120 )`,
121 ];
122
123 // Execute table creation queries in order
124 for (const query of tableCreationQueries) {
125 await db.execute(query);
126 }
127
128 // Create indexes
129 await db.execute(
130 sql`CREATE INDEX IF NOT EXISTS idx_user_cards ON library_memberships(user_id)`,
131 );
132 await db.execute(
133 sql`CREATE INDEX IF NOT EXISTS idx_card_users ON library_memberships(card_id)`,
134 );
135
136 // Performance indexes
137 // Covering index for getUrlCardsOfUser - avoids table lookups by including cardId
138 await db.execute(sql`
139 CREATE INDEX IF NOT EXISTS idx_library_memberships_user_type_covering
140 ON library_memberships(user_id, added_at DESC)
141 INCLUDE (card_id)
142 `);
143
144 // Optimizes sorting cards by type and update time in query results
145 await db.execute(sql`
146 CREATE INDEX IF NOT EXISTS idx_cards_type_updated_at
147 ON cards(type, updated_at DESC)
148 `);
149
150 // Index for getLibrariesForUrl and getCollectionsWithUrl - fast URL+type lookups with card ID included
151 await db.execute(sql`
152 CREATE INDEX IF NOT EXISTS idx_cards_url_type
153 ON cards(url, type) INCLUDE (id)
154 `);
155
156 // Index for filtering by URL type
157 await db.execute(sql`
158 CREATE INDEX IF NOT EXISTS idx_cards_url_type_filter
159 ON cards(url_type)
160 `);
161
162 // Covering index for getCardsInCollection - sorted by add time with cardId included
163 await db.execute(sql`
164 CREATE INDEX IF NOT EXISTS idx_collection_cards_collection_added
165 ON collection_cards(collection_id, added_at DESC)
166 INCLUDE (card_id)
167 `);
168
169 // Partial index for finding NOTE cards by parent - only indexes NOTE type cards
170 await db.execute(sql`
171 CREATE INDEX IF NOT EXISTS idx_cards_parent_type
172 ON cards(parent_card_id, type) WHERE type = 'NOTE'
173 `);
174
175 // Covering index for finding collections containing a card - avoids table lookups
176 await db.execute(sql`
177 CREATE INDEX IF NOT EXISTS idx_collection_cards_card_collection
178 ON collection_cards(card_id) INCLUDE (collection_id)
179 `);
180 // Feed activities indexes
181 await db.execute(sql`
182 CREATE INDEX IF NOT EXISTS feed_activities_type_idx ON feed_activities(type);
183 `);
184 await db.execute(sql`
185 CREATE INDEX IF NOT EXISTS feed_activities_url_type_idx ON feed_activities(url_type);
186 `);
187 await db.execute(sql`
188 CREATE INDEX IF NOT EXISTS feed_activities_created_at_idx ON feed_activities(created_at DESC);
189 `);
190 await db.execute(sql`
191 CREATE INDEX IF NOT EXISTS feed_activities_type_created_at_idx ON feed_activities(type, created_at DESC);
192 `);
193 await db.execute(sql`
194 CREATE INDEX IF NOT EXISTS feed_activities_url_type_created_at_idx ON feed_activities(url_type, created_at DESC);
195 `);
196 await db.execute(sql`
197 CREATE INDEX IF NOT EXISTS feed_activities_type_url_type_created_at_idx ON feed_activities(type, url_type, created_at DESC);
198 `);
199 await db.execute(sql`
200 CREATE INDEX IF NOT EXISTS feed_activities_dedup_idx ON feed_activities(actor_id, card_id, created_at DESC);
201 `);
202 await db.execute(sql`
203 CREATE INDEX IF NOT EXISTS feed_activities_card_id_idx ON feed_activities(card_id);
204 `);
205 await db.execute(sql`
206 CREATE INDEX IF NOT EXISTS idx_feed_activities_actor_id ON feed_activities(actor_id);
207 `);
208 await db.execute(sql`
209 CREATE INDEX IF NOT EXISTS feed_activities_source_idx ON feed_activities(source);
210 `);
211
212 // Index for efficient AT URI look ups
213 await db.execute(sql`
214 CREATE INDEX IF NOT EXISTS published_records_uri_idx ON published_records(uri);
215 `);
216
217 // Notifications table indexes
218 await db.execute(sql`
219 CREATE INDEX IF NOT EXISTS notifications_recipient_idx ON notifications(recipient_user_id);
220 `);
221 await db.execute(sql`
222 CREATE INDEX IF NOT EXISTS notifications_recipient_created_at_idx ON notifications(recipient_user_id, created_at DESC);
223 `);
224 await db.execute(sql`
225 CREATE INDEX IF NOT EXISTS notifications_recipient_read_idx ON notifications(recipient_user_id, read);
226 `);
227
228 // Cards table indexes
229 await db.execute(sql`
230 CREATE INDEX IF NOT EXISTS cards_author_url_idx ON cards(author_id, url);
231 `);
232 await db.execute(sql`
233 CREATE INDEX IF NOT EXISTS cards_author_id_idx ON cards(author_id);
234 `);
235
236 // Collections table indexes
237 await db.execute(sql`
238 CREATE INDEX IF NOT EXISTS collections_author_id_idx ON collections(author_id);
239 `);
240 await db.execute(sql`
241 CREATE INDEX IF NOT EXISTS collections_author_updated_at_idx ON collections(author_id, updated_at);
242 `);
243
244 // Collection cards table indexes
245 await db.execute(sql`
246 CREATE INDEX IF NOT EXISTS collection_cards_card_id_idx ON collection_cards(card_id);
247 `);
248 await db.execute(sql`
249 CREATE INDEX IF NOT EXISTS collection_cards_collection_id_idx ON collection_cards(collection_id);
250 `);
251
252 // Follows table indexes
253 await db.execute(sql`
254 CREATE INDEX IF NOT EXISTS idx_follows_follower ON follows(follower_id);
255 `);
256 await db.execute(sql`
257 CREATE INDEX IF NOT EXISTS idx_follows_target ON follows(target_id, target_type);
258 `);
259}