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 // Following feed items table (references feed_activities)
123 sql`CREATE TABLE IF NOT EXISTS following_feed_items (
124 user_id TEXT NOT NULL,
125 activity_id UUID NOT NULL REFERENCES feed_activities(id) ON DELETE CASCADE,
126 created_at TIMESTAMP NOT NULL,
127 PRIMARY KEY (user_id, activity_id)
128 )`,
129 ];
130
131 // Execute table creation queries in order
132 for (const query of tableCreationQueries) {
133 await db.execute(query);
134 }
135
136 // Create indexes
137 await db.execute(
138 sql`CREATE INDEX IF NOT EXISTS idx_user_cards ON library_memberships(user_id)`,
139 );
140 await db.execute(
141 sql`CREATE INDEX IF NOT EXISTS idx_card_users ON library_memberships(card_id)`,
142 );
143
144 // Performance indexes
145 // Covering index for getUrlCardsOfUser - avoids table lookups by including cardId
146 await db.execute(sql`
147 CREATE INDEX IF NOT EXISTS idx_library_memberships_user_type_covering
148 ON library_memberships(user_id, added_at DESC)
149 INCLUDE (card_id)
150 `);
151
152 // Optimizes sorting cards by type and update time in query results
153 await db.execute(sql`
154 CREATE INDEX IF NOT EXISTS idx_cards_type_updated_at
155 ON cards(type, updated_at DESC)
156 `);
157
158 // Index for getLibrariesForUrl and getCollectionsWithUrl - fast URL+type lookups with card ID included
159 await db.execute(sql`
160 CREATE INDEX IF NOT EXISTS idx_cards_url_type
161 ON cards(url, type) INCLUDE (id)
162 `);
163
164 // Index for filtering by URL type
165 await db.execute(sql`
166 CREATE INDEX IF NOT EXISTS idx_cards_url_type_filter
167 ON cards(url_type)
168 `);
169
170 // Covering index for getCardsInCollection - sorted by add time with cardId included
171 await db.execute(sql`
172 CREATE INDEX IF NOT EXISTS idx_collection_cards_collection_added
173 ON collection_cards(collection_id, added_at DESC)
174 INCLUDE (card_id)
175 `);
176
177 // Partial index for finding NOTE cards by parent - only indexes NOTE type cards
178 await db.execute(sql`
179 CREATE INDEX IF NOT EXISTS idx_cards_parent_type
180 ON cards(parent_card_id, type) WHERE type = 'NOTE'
181 `);
182
183 // Covering index for finding collections containing a card - avoids table lookups
184 await db.execute(sql`
185 CREATE INDEX IF NOT EXISTS idx_collection_cards_card_collection
186 ON collection_cards(card_id) INCLUDE (collection_id)
187 `);
188 // Feed activities indexes
189 await db.execute(sql`
190 CREATE INDEX IF NOT EXISTS feed_activities_type_idx ON feed_activities(type);
191 `);
192 await db.execute(sql`
193 CREATE INDEX IF NOT EXISTS feed_activities_url_type_idx ON feed_activities(url_type);
194 `);
195 await db.execute(sql`
196 CREATE INDEX IF NOT EXISTS feed_activities_created_at_idx ON feed_activities(created_at DESC);
197 `);
198 await db.execute(sql`
199 CREATE INDEX IF NOT EXISTS feed_activities_type_created_at_idx ON feed_activities(type, created_at DESC);
200 `);
201 await db.execute(sql`
202 CREATE INDEX IF NOT EXISTS feed_activities_url_type_created_at_idx ON feed_activities(url_type, created_at DESC);
203 `);
204 await db.execute(sql`
205 CREATE INDEX IF NOT EXISTS feed_activities_type_url_type_created_at_idx ON feed_activities(type, url_type, created_at DESC);
206 `);
207 await db.execute(sql`
208 CREATE INDEX IF NOT EXISTS feed_activities_dedup_idx ON feed_activities(actor_id, card_id, created_at DESC);
209 `);
210 await db.execute(sql`
211 CREATE INDEX IF NOT EXISTS feed_activities_card_id_idx ON feed_activities(card_id);
212 `);
213 await db.execute(sql`
214 CREATE INDEX IF NOT EXISTS idx_feed_activities_actor_id ON feed_activities(actor_id);
215 `);
216 await db.execute(sql`
217 CREATE INDEX IF NOT EXISTS feed_activities_source_idx ON feed_activities(source);
218 `);
219
220 // Index for efficient AT URI look ups
221 await db.execute(sql`
222 CREATE INDEX IF NOT EXISTS published_records_uri_idx ON published_records(uri);
223 `);
224
225 // Notifications table indexes
226 await db.execute(sql`
227 CREATE INDEX IF NOT EXISTS notifications_recipient_idx ON notifications(recipient_user_id);
228 `);
229 await db.execute(sql`
230 CREATE INDEX IF NOT EXISTS notifications_recipient_created_at_idx ON notifications(recipient_user_id, created_at DESC);
231 `);
232 await db.execute(sql`
233 CREATE INDEX IF NOT EXISTS notifications_recipient_read_idx ON notifications(recipient_user_id, read);
234 `);
235
236 // Cards table indexes
237 await db.execute(sql`
238 CREATE INDEX IF NOT EXISTS cards_author_url_idx ON cards(author_id, url);
239 `);
240 await db.execute(sql`
241 CREATE INDEX IF NOT EXISTS cards_author_id_idx ON cards(author_id);
242 `);
243
244 // Collections table indexes
245 await db.execute(sql`
246 CREATE INDEX IF NOT EXISTS collections_author_id_idx ON collections(author_id);
247 `);
248 await db.execute(sql`
249 CREATE INDEX IF NOT EXISTS collections_author_updated_at_idx ON collections(author_id, updated_at);
250 `);
251
252 // Collection cards table indexes
253 await db.execute(sql`
254 CREATE INDEX IF NOT EXISTS collection_cards_card_id_idx ON collection_cards(card_id);
255 `);
256 await db.execute(sql`
257 CREATE INDEX IF NOT EXISTS collection_cards_collection_id_idx ON collection_cards(collection_id);
258 `);
259
260 // Follows table indexes
261 await db.execute(sql`
262 CREATE INDEX IF NOT EXISTS idx_follows_follower ON follows(follower_id);
263 `);
264 await db.execute(sql`
265 CREATE INDEX IF NOT EXISTS idx_follows_target ON follows(target_id, target_type);
266 `);
267
268 // Following feed items indexes
269 await db.execute(sql`
270 CREATE INDEX IF NOT EXISTS idx_following_feed_user_time ON following_feed_items(user_id, created_at DESC);
271 `);
272}