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 // Users table (no dependencies)
11 sql`CREATE TABLE IF NOT EXISTS users (
12 id TEXT PRIMARY KEY,
13 handle TEXT,
14 linked_at TIMESTAMP WITH TIME ZONE NOT NULL,
15 last_login_at TIMESTAMP WITH TIME ZONE NOT NULL
16 )`,
17
18 // Published records table (no dependencies)
19 sql`CREATE TABLE IF NOT EXISTS published_records (
20 id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
21 uri TEXT NOT NULL,
22 cid TEXT NOT NULL,
23 recorded_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
24 UNIQUE(uri, cid)
25 )`,
26
27 // Cards table (references published_records and self-references)
28 sql`CREATE TABLE IF NOT EXISTS cards (
29 id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
30 author_id TEXT NOT NULL,
31 type TEXT NOT NULL,
32 content_data JSONB NOT NULL,
33 url TEXT,
34 url_type TEXT,
35 parent_card_id UUID REFERENCES cards(id),
36 via_card_id UUID REFERENCES cards(id),
37 published_record_id UUID REFERENCES published_records(id),
38 library_count INTEGER NOT NULL DEFAULT 0,
39 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
40 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
41 )`,
42
43 // Library memberships table (references cards and published_records)
44 sql`CREATE TABLE IF NOT EXISTS library_memberships (
45 card_id UUID NOT NULL REFERENCES cards(id) ON DELETE CASCADE,
46 user_id TEXT NOT NULL,
47 added_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
48 published_record_id UUID REFERENCES published_records(id),
49 PRIMARY KEY (card_id, user_id)
50 )`,
51
52 // Collections table (references published_records)
53 sql`CREATE TABLE IF NOT EXISTS collections (
54 id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
55 author_id TEXT NOT NULL,
56 name TEXT NOT NULL,
57 description TEXT,
58 access_type TEXT NOT NULL,
59 card_count INTEGER NOT NULL DEFAULT 0,
60 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
61 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
62 published_record_id UUID REFERENCES published_records(id)
63 )`,
64
65 // Collection collaborators table (references collections)
66 sql`CREATE TABLE IF NOT EXISTS collection_collaborators (
67 id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
68 collection_id UUID NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
69 collaborator_id TEXT NOT NULL,
70 UNIQUE(collection_id, collaborator_id)
71 )`,
72
73 // Collection cards table (references collections, cards, and published_records)
74 sql`CREATE TABLE IF NOT EXISTS collection_cards (
75 id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
76 collection_id UUID NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
77 card_id UUID NOT NULL REFERENCES cards(id) ON DELETE CASCADE,
78 added_by TEXT NOT NULL,
79 added_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
80 via_card_id UUID REFERENCES cards(id),
81 published_record_id UUID REFERENCES published_records(id),
82 UNIQUE(collection_id, card_id)
83 )`,
84 sql`
85 CREATE TABLE IF NOT EXISTS feed_activities (
86 id UUID PRIMARY KEY,
87 actor_id TEXT NOT NULL,
88 card_id TEXT,
89 connection_id TEXT,
90 type TEXT NOT NULL,
91 metadata JSONB NOT NULL,
92 url_type TEXT,
93 source TEXT,
94 created_at TIMESTAMP NOT NULL DEFAULT NOW()
95 )`,
96
97 // Notifications table (no dependencies)
98 sql`CREATE TABLE IF NOT EXISTS notifications (
99 id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
100 recipient_user_id TEXT NOT NULL,
101 actor_user_id TEXT NOT NULL,
102 type TEXT NOT NULL,
103 metadata JSONB NOT NULL,
104 read BOOLEAN NOT NULL DEFAULT false,
105 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
106 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
107 )`,
108
109 // Sync statuses table (no dependencies)
110 sql`CREATE TABLE IF NOT EXISTS sync_statuses (
111 id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
112 curator_id TEXT NOT NULL UNIQUE,
113 sync_state TEXT NOT NULL,
114 last_synced_at TIMESTAMP WITH TIME ZONE,
115 last_sync_attempt_at TIMESTAMP WITH TIME ZONE,
116 sync_error_message TEXT,
117 records_processed INTEGER,
118 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
119 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
120 )`,
121
122 // Follows table (references published_records)
123 sql`CREATE TABLE IF NOT EXISTS follows (
124 follower_id TEXT NOT NULL,
125 target_id TEXT NOT NULL,
126 target_type TEXT NOT NULL,
127 published_record_id UUID REFERENCES published_records(id),
128 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
129 PRIMARY KEY (follower_id, target_id, target_type)
130 )`,
131
132 // Connections table (references published_records)
133 sql`CREATE TABLE IF NOT EXISTS connections (
134 id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
135 curator_id TEXT NOT NULL,
136 source_type TEXT NOT NULL,
137 source_value TEXT NOT NULL,
138 source_url_metadata JSONB,
139 target_type TEXT NOT NULL,
140 target_value TEXT NOT NULL,
141 target_url_metadata JSONB,
142 connection_type TEXT,
143 note TEXT,
144 published_record_id UUID REFERENCES published_records(id),
145 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
146 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
147 )`,
148
149 // Following feed items table (references feed_activities)
150 sql`CREATE TABLE IF NOT EXISTS following_feed_items (
151 user_id TEXT NOT NULL,
152 activity_id UUID NOT NULL REFERENCES feed_activities(id) ON DELETE CASCADE,
153 created_at TIMESTAMP NOT NULL,
154 PRIMARY KEY (user_id, activity_id)
155 )`,
156 ];
157
158 // Execute table creation queries in order
159 for (const query of tableCreationQueries) {
160 await db.execute(query);
161 }
162
163 // Create indexes
164 await db.execute(
165 sql`CREATE INDEX IF NOT EXISTS idx_user_cards ON library_memberships(user_id)`,
166 );
167 await db.execute(
168 sql`CREATE INDEX IF NOT EXISTS idx_card_users ON library_memberships(card_id)`,
169 );
170
171 // Performance indexes
172 // Covering index for getUrlCardsOfUser - avoids table lookups by including cardId
173 await db.execute(sql`
174 CREATE INDEX IF NOT EXISTS idx_library_memberships_user_type_covering
175 ON library_memberships(user_id, added_at DESC)
176 INCLUDE (card_id)
177 `);
178
179 // Optimizes sorting cards by type and update time in query results
180 await db.execute(sql`
181 CREATE INDEX IF NOT EXISTS idx_cards_type_updated_at
182 ON cards(type, updated_at DESC)
183 `);
184
185 // Index for getLibrariesForUrl and getCollectionsWithUrl - fast URL+type lookups with card ID included
186 await db.execute(sql`
187 CREATE INDEX IF NOT EXISTS idx_cards_url_type
188 ON cards(url, type) INCLUDE (id)
189 `);
190
191 // Index for filtering by URL type
192 await db.execute(sql`
193 CREATE INDEX IF NOT EXISTS idx_cards_url_type_filter
194 ON cards(url_type)
195 `);
196
197 // Covering index for getCardsInCollection - sorted by add time with cardId included
198 await db.execute(sql`
199 CREATE INDEX IF NOT EXISTS idx_collection_cards_collection_added
200 ON collection_cards(collection_id, added_at DESC)
201 INCLUDE (card_id)
202 `);
203
204 // Partial index for finding NOTE cards by parent - only indexes NOTE type cards
205 await db.execute(sql`
206 CREATE INDEX IF NOT EXISTS idx_cards_parent_type
207 ON cards(parent_card_id, type) WHERE type = 'NOTE'
208 `);
209
210 // Covering index for finding collections containing a card - avoids table lookups
211 await db.execute(sql`
212 CREATE INDEX IF NOT EXISTS idx_collection_cards_card_collection
213 ON collection_cards(card_id) INCLUDE (collection_id)
214 `);
215 // Feed activities indexes
216 await db.execute(sql`
217 CREATE INDEX IF NOT EXISTS feed_activities_type_idx ON feed_activities(type);
218 `);
219 await db.execute(sql`
220 CREATE INDEX IF NOT EXISTS feed_activities_url_type_idx ON feed_activities(url_type);
221 `);
222 await db.execute(sql`
223 CREATE INDEX IF NOT EXISTS feed_activities_created_at_idx ON feed_activities(created_at DESC);
224 `);
225 await db.execute(sql`
226 CREATE INDEX IF NOT EXISTS feed_activities_type_created_at_idx ON feed_activities(type, created_at DESC);
227 `);
228 await db.execute(sql`
229 CREATE INDEX IF NOT EXISTS feed_activities_url_type_created_at_idx ON feed_activities(url_type, created_at DESC);
230 `);
231 await db.execute(sql`
232 CREATE INDEX IF NOT EXISTS feed_activities_type_url_type_created_at_idx ON feed_activities(type, url_type, created_at DESC);
233 `);
234 await db.execute(sql`
235 CREATE INDEX IF NOT EXISTS feed_activities_dedup_idx ON feed_activities(actor_id, card_id, created_at DESC);
236 `);
237 await db.execute(sql`
238 CREATE INDEX IF NOT EXISTS feed_activities_card_id_idx ON feed_activities(card_id);
239 `);
240 await db.execute(sql`
241 CREATE INDEX IF NOT EXISTS feed_activities_connection_id_idx ON feed_activities(connection_id);
242 `);
243 await db.execute(sql`
244 CREATE INDEX IF NOT EXISTS idx_feed_activities_actor_id ON feed_activities(actor_id);
245 `);
246 await db.execute(sql`
247 CREATE INDEX IF NOT EXISTS feed_activities_source_idx ON feed_activities(source);
248 `);
249
250 // Index for efficient AT URI look ups
251 await db.execute(sql`
252 CREATE INDEX IF NOT EXISTS published_records_uri_idx ON published_records(uri);
253 `);
254
255 // Notifications table indexes
256 await db.execute(sql`
257 CREATE INDEX IF NOT EXISTS notifications_recipient_idx ON notifications(recipient_user_id);
258 `);
259 await db.execute(sql`
260 CREATE INDEX IF NOT EXISTS notifications_recipient_created_at_idx ON notifications(recipient_user_id, created_at DESC);
261 `);
262 await db.execute(sql`
263 CREATE INDEX IF NOT EXISTS notifications_recipient_read_idx ON notifications(recipient_user_id, read);
264 `);
265
266 // Cards table indexes
267 await db.execute(sql`
268 CREATE INDEX IF NOT EXISTS cards_author_url_idx ON cards(author_id, url);
269 `);
270 await db.execute(sql`
271 CREATE INDEX IF NOT EXISTS cards_author_id_idx ON cards(author_id);
272 `);
273
274 // Collections table indexes
275 await db.execute(sql`
276 CREATE INDEX IF NOT EXISTS collections_author_id_idx ON collections(author_id);
277 `);
278 await db.execute(sql`
279 CREATE INDEX IF NOT EXISTS collections_author_updated_at_idx ON collections(author_id, updated_at);
280 `);
281
282 // Collection cards table indexes
283 await db.execute(sql`
284 CREATE INDEX IF NOT EXISTS collection_cards_card_id_idx ON collection_cards(card_id);
285 `);
286 await db.execute(sql`
287 CREATE INDEX IF NOT EXISTS collection_cards_collection_id_idx ON collection_cards(collection_id);
288 `);
289
290 // Follows table indexes
291 await db.execute(sql`
292 CREATE INDEX IF NOT EXISTS idx_follows_follower ON follows(follower_id);
293 `);
294 await db.execute(sql`
295 CREATE INDEX IF NOT EXISTS idx_follows_target ON follows(target_id, target_type);
296 `);
297
298 // Connections table indexes
299 await db.execute(sql`
300 CREATE INDEX IF NOT EXISTS connections_curator_id_idx ON connections(curator_id);
301 `);
302 await db.execute(sql`
303 CREATE INDEX IF NOT EXISTS connections_source_idx ON connections(source_type, source_value);
304 `);
305 await db.execute(sql`
306 CREATE INDEX IF NOT EXISTS connections_target_idx ON connections(target_type, target_value);
307 `);
308 await db.execute(sql`
309 CREATE INDEX IF NOT EXISTS connections_created_at_idx ON connections(created_at DESC);
310 `);
311 await db.execute(sql`
312 CREATE INDEX IF NOT EXISTS connections_curator_created_at_idx ON connections(curator_id, created_at DESC);
313 `);
314
315 // Following feed items indexes
316 await db.execute(sql`
317 CREATE INDEX IF NOT EXISTS idx_following_feed_user_time ON following_feed_items(user_id, created_at DESC);
318 `);
319}