A social knowledge tool for researchers built on ATProto
cosmik.network
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 parent_card_id UUID REFERENCES cards(id),
27 published_record_id UUID REFERENCES published_records(id),
28 library_count INTEGER NOT NULL DEFAULT 0,
29 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
30 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
31 )`,
32
33 // Library memberships table (references cards and published_records)
34 sql`CREATE TABLE IF NOT EXISTS library_memberships (
35 card_id UUID NOT NULL REFERENCES cards(id) ON DELETE CASCADE,
36 user_id TEXT NOT NULL,
37 added_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
38 published_record_id UUID REFERENCES published_records(id),
39 PRIMARY KEY (card_id, user_id)
40 )`,
41
42 // Collections table (references published_records)
43 sql`CREATE TABLE IF NOT EXISTS collections (
44 id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
45 author_id TEXT NOT NULL,
46 name TEXT NOT NULL,
47 description TEXT,
48 access_type TEXT NOT NULL,
49 card_count INTEGER NOT NULL DEFAULT 0,
50 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
51 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
52 published_record_id UUID REFERENCES published_records(id)
53 )`,
54
55 // Collection collaborators table (references collections)
56 sql`CREATE TABLE IF NOT EXISTS collection_collaborators (
57 id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
58 collection_id UUID NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
59 collaborator_id TEXT NOT NULL,
60 UNIQUE(collection_id, collaborator_id)
61 )`,
62
63 // Collection cards table (references collections, cards, and published_records)
64 sql`CREATE TABLE IF NOT EXISTS collection_cards (
65 id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
66 collection_id UUID NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
67 card_id UUID NOT NULL REFERENCES cards(id) ON DELETE CASCADE,
68 added_by TEXT NOT NULL,
69 added_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
70 published_record_id UUID REFERENCES published_records(id),
71 UNIQUE(collection_id, card_id)
72 )`,
73 sql`
74 CREATE TABLE IF NOT EXISTS feed_activities (
75 id UUID PRIMARY KEY,
76 actor_id TEXT NOT NULL,
77 type TEXT NOT NULL,
78 metadata JSONB NOT NULL,
79 created_at TIMESTAMP NOT NULL DEFAULT NOW()
80 )`,
81 ];
82
83 // Execute table creation queries in order
84 for (const query of tableCreationQueries) {
85 await db.execute(query);
86 }
87
88 // Create indexes
89 await db.execute(
90 sql`CREATE INDEX IF NOT EXISTS idx_user_cards ON library_memberships(user_id)`,
91 );
92 await db.execute(
93 sql`CREATE INDEX IF NOT EXISTS idx_card_users ON library_memberships(card_id)`,
94 );
95
96 // Performance indexes
97 // Covering index for getUrlCardsOfUser - avoids table lookups by including cardId
98 await db.execute(sql`
99 CREATE INDEX IF NOT EXISTS idx_library_memberships_user_type_covering
100 ON library_memberships(user_id, added_at DESC)
101 INCLUDE (card_id)
102 `);
103
104 // Optimizes sorting cards by type and update time in query results
105 await db.execute(sql`
106 CREATE INDEX IF NOT EXISTS idx_cards_type_updated_at
107 ON cards(type, updated_at DESC)
108 `);
109
110 // Index for getLibrariesForUrl and getCollectionsWithUrl - fast URL+type lookups with card ID included
111 await db.execute(sql`
112 CREATE INDEX IF NOT EXISTS idx_cards_url_type
113 ON cards(url, type) INCLUDE (id)
114 `);
115
116 // Covering index for getCardsInCollection - sorted by add time with cardId included
117 await db.execute(sql`
118 CREATE INDEX IF NOT EXISTS idx_collection_cards_collection_added
119 ON collection_cards(collection_id, added_at DESC)
120 INCLUDE (card_id)
121 `);
122
123 // Partial index for finding NOTE cards by parent - only indexes NOTE type cards
124 await db.execute(sql`
125 CREATE INDEX IF NOT EXISTS idx_cards_parent_type
126 ON cards(parent_card_id, type) WHERE type = 'NOTE'
127 `);
128
129 // Covering index for finding collections containing a card - avoids table lookups
130 await db.execute(sql`
131 CREATE INDEX IF NOT EXISTS idx_collection_cards_card_collection
132 ON collection_cards(card_id) INCLUDE (collection_id)
133 `);
134 await db.execute(sql`
135 CREATE INDEX IF NOT EXISTS idx_feed_activities_created_at ON feed_activities(created_at DESC);
136 `);
137
138 await db.execute(sql`
139 CREATE INDEX IF NOT EXISTS idx_feed_activities_actor_id ON feed_activities(actor_id);
140 `);
141
142 // Index for efficient AT URI look ups
143 await db.execute(sql`
144 CREATE INDEX IF NOT EXISTS published_records_uri_idx ON published_records(uri);
145 `);
146
147 // Cards table indexes
148 await db.execute(sql`
149 CREATE INDEX IF NOT EXISTS cards_author_url_idx ON cards(author_id, url);
150 `);
151 await db.execute(sql`
152 CREATE INDEX IF NOT EXISTS cards_author_id_idx ON cards(author_id);
153 `);
154
155 // Collections table indexes
156 await db.execute(sql`
157 CREATE INDEX IF NOT EXISTS collections_author_id_idx ON collections(author_id);
158 `);
159 await db.execute(sql`
160 CREATE INDEX IF NOT EXISTS collections_author_updated_at_idx ON collections(author_id, updated_at);
161 `);
162
163 // Collection cards table indexes
164 await db.execute(sql`
165 CREATE INDEX IF NOT EXISTS collection_cards_card_id_idx ON collection_cards(card_id);
166 `);
167 await db.execute(sql`
168 CREATE INDEX IF NOT EXISTS collection_cards_collection_id_idx ON collection_cards(collection_id);
169 `);
170}