Skip to main content

max / makenotwork

22.1 KB · 570 lines History Blame Raw
1 -- Initial schema for Makenotwork
2 -- Squashed from 45 incremental migrations (pre-launch)
3
4 -- ============================================================================
5 -- Extensions & Functions
6 -- ============================================================================
7
8 CREATE EXTENSION IF NOT EXISTS pg_trgm;
9
10 CREATE SCHEMA IF NOT EXISTS tower_sessions;
11
12 CREATE FUNCTION update_updated_at_column() RETURNS trigger
13 LANGUAGE plpgsql AS $$
14 BEGIN
15 NEW.updated_at = NOW();
16 RETURN NEW;
17 END;
18 $$;
19
20 -- ============================================================================
21 -- Core Tables
22 -- ============================================================================
23
24 CREATE TABLE users (
25 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
26 username VARCHAR(50) NOT NULL UNIQUE,
27 email VARCHAR(255) NOT NULL UNIQUE,
28 password_hash VARCHAR(255) NOT NULL,
29 display_name VARCHAR(100),
30 bio TEXT,
31 avatar_url VARCHAR(500),
32 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
33 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
34 -- Stripe Connect
35 stripe_account_id VARCHAR(255),
36 stripe_onboarding_complete BOOLEAN NOT NULL DEFAULT false,
37 stripe_payouts_enabled BOOLEAN NOT NULL DEFAULT false,
38 stripe_charges_enabled BOOLEAN NOT NULL DEFAULT false,
39 -- Email verification
40 email_verified BOOLEAN NOT NULL DEFAULT false,
41 email_verification_token VARCHAR(64),
42 email_verification_sent_at TIMESTAMPTZ,
43 -- Login security
44 failed_login_attempts INT NOT NULL DEFAULT 0,
45 locked_until TIMESTAMPTZ,
46 last_failed_login_at TIMESTAMPTZ,
47 -- Creator access
48 can_create_projects BOOLEAN NOT NULL DEFAULT false,
49 -- Login notifications
50 login_notification_enabled BOOLEAN NOT NULL DEFAULT true,
51 -- 2FA / TOTP
52 totp_secret TEXT,
53 totp_enabled BOOLEAN NOT NULL DEFAULT false
54 );
55
56 CREATE TABLE project_categories (
57 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
58 name VARCHAR(100) NOT NULL UNIQUE,
59 slug VARCHAR(100) NOT NULL UNIQUE,
60 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
61 );
62
63 CREATE TABLE projects (
64 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
65 user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
66 slug VARCHAR(100) NOT NULL,
67 title VARCHAR(200) NOT NULL,
68 description TEXT,
69 project_type VARCHAR(50) NOT NULL DEFAULT 'general',
70 cover_image_url VARCHAR(500),
71 is_public BOOLEAN NOT NULL DEFAULT true,
72 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
73 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
74 category_id UUID REFERENCES project_categories(id) ON DELETE SET NULL,
75 UNIQUE (user_id, slug)
76 );
77
78 CREATE TABLE items (
79 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
80 project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
81 title VARCHAR(200) NOT NULL,
82 description TEXT,
83 price_cents INT NOT NULL DEFAULT 0 CHECK (price_cents >= 0),
84 item_type VARCHAR(50) NOT NULL DEFAULT 'digital',
85 thumbnail_url VARCHAR(500),
86 is_public BOOLEAN NOT NULL DEFAULT true,
87 sort_order INT NOT NULL DEFAULT 0 CHECK (sort_order >= 0),
88 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
89 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
90 -- Text content
91 body TEXT,
92 word_count INT,
93 reading_time_minutes INT,
94 -- Audio content
95 audio_url VARCHAR(500),
96 duration_seconds INT,
97 cover_image_url VARCHAR(500),
98 episode_number INT,
99 -- S3 storage
100 audio_s3_key VARCHAR(500),
101 cover_s3_key VARCHAR(500),
102 -- License keys
103 enable_license_keys BOOLEAN NOT NULL DEFAULT false,
104 default_max_activations INT,
105 -- Denormalized counters
106 sales_count INT NOT NULL DEFAULT 0,
107 play_count INT NOT NULL DEFAULT 0,
108 download_count INT NOT NULL DEFAULT 0,
109 -- Pay-what-you-want
110 pwyw_enabled BOOLEAN NOT NULL DEFAULT false,
111 pwyw_min_cents INT
112 );
113
114 CREATE TABLE versions (
115 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
116 item_id UUID NOT NULL REFERENCES items(id) ON DELETE CASCADE,
117 version_number VARCHAR(50) NOT NULL,
118 changelog TEXT,
119 file_url VARCHAR(500),
120 file_size_bytes BIGINT,
121 file_name VARCHAR(255),
122 download_count INT NOT NULL DEFAULT 0 CHECK (download_count >= 0),
123 is_current BOOLEAN NOT NULL DEFAULT false,
124 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
125 s3_key VARCHAR(500)
126 );
127
128 CREATE TABLE chapters (
129 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
130 item_id UUID NOT NULL REFERENCES items(id) ON DELETE CASCADE,
131 title VARCHAR(200) NOT NULL,
132 start_seconds REAL NOT NULL DEFAULT 0,
133 sort_order INT NOT NULL DEFAULT 0,
134 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
135 );
136
137 -- ============================================================================
138 -- Tags (hierarchical)
139 -- ============================================================================
140
141 CREATE TABLE tags (
142 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
143 name TEXT NOT NULL,
144 slug TEXT NOT NULL UNIQUE,
145 parent_id UUID REFERENCES tags(id) ON DELETE CASCADE,
146 sort_order INT NOT NULL DEFAULT 0,
147 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
148 );
149
150 CREATE TABLE item_tags (
151 item_id UUID NOT NULL REFERENCES items(id) ON DELETE CASCADE,
152 tag_id UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
153 is_primary BOOLEAN NOT NULL DEFAULT false,
154 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
155 PRIMARY KEY (item_id, tag_id)
156 );
157
158 -- ============================================================================
159 -- Transactions & Commerce
160 -- ============================================================================
161
162 CREATE TABLE transactions (
163 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
164 buyer_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
165 seller_id UUID REFERENCES users(id) ON DELETE SET NULL,
166 item_id UUID REFERENCES items(id) ON DELETE SET NULL,
167 amount_cents INT NOT NULL CHECK (amount_cents >= 0),
168 platform_fee_cents INT NOT NULL DEFAULT 0 CHECK (platform_fee_cents >= 0),
169 currency VARCHAR(3) NOT NULL DEFAULT 'USD',
170 status VARCHAR(20) NOT NULL DEFAULT 'pending'
171 CHECK (status IN ('pending', 'completed', 'failed', 'refunded')),
172 stripe_payment_intent_id VARCHAR(255),
173 stripe_checkout_session_id VARCHAR(255),
174 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
175 completed_at TIMESTAMPTZ,
176 -- Denormalized for display after item deletion
177 item_title VARCHAR(200),
178 seller_username VARCHAR(50),
179 share_contact BOOLEAN NOT NULL DEFAULT false
180 );
181
182 CREATE VIEW purchases AS
183 SELECT DISTINCT ON (buyer_id, COALESCE(item_id, id))
184 buyer_id,
185 item_id,
186 id AS transaction_id,
187 created_at AS purchased_at,
188 item_title,
189 amount_cents
190 FROM transactions
191 WHERE status = 'completed'
192 ORDER BY buyer_id, COALESCE(item_id, id), created_at DESC;
193
194 -- ============================================================================
195 -- Subscriptions
196 -- ============================================================================
197
198 CREATE TABLE subscription_tiers (
199 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
200 project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
201 name VARCHAR(100) NOT NULL,
202 description TEXT,
203 price_cents INT NOT NULL CHECK (price_cents > 0),
204 stripe_product_id TEXT,
205 stripe_price_id TEXT,
206 sort_order INT NOT NULL DEFAULT 0,
207 is_active BOOLEAN NOT NULL DEFAULT true,
208 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
209 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
210 );
211
212 CREATE TABLE subscriptions (
213 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
214 subscriber_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
215 tier_id UUID NOT NULL REFERENCES subscription_tiers(id) ON DELETE RESTRICT,
216 project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
217 stripe_subscription_id TEXT NOT NULL,
218 stripe_customer_id TEXT NOT NULL,
219 status VARCHAR(20) NOT NULL DEFAULT 'active',
220 current_period_start TIMESTAMPTZ,
221 current_period_end TIMESTAMPTZ,
222 canceled_at TIMESTAMPTZ,
223 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
224 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
225 );
226
227 CREATE TABLE subscription_events (
228 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
229 subscription_id UUID REFERENCES subscriptions(id) ON DELETE SET NULL,
230 stripe_event_id TEXT NOT NULL UNIQUE,
231 event_type TEXT NOT NULL,
232 payload JSONB NOT NULL DEFAULT '{}',
233 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
234 );
235
236 -- ============================================================================
237 -- License Keys & Download Codes
238 -- ============================================================================
239
240 CREATE TABLE license_keys (
241 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
242 item_id UUID NOT NULL REFERENCES items(id) ON DELETE CASCADE,
243 owner_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
244 transaction_id UUID REFERENCES transactions(id) ON DELETE SET NULL,
245 key_code VARCHAR(50) NOT NULL UNIQUE,
246 max_activations INT,
247 activation_count INT NOT NULL DEFAULT 0,
248 revoked_at TIMESTAMPTZ,
249 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
250 );
251
252 CREATE TABLE license_activations (
253 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
254 license_key_id UUID NOT NULL REFERENCES license_keys(id) ON DELETE CASCADE,
255 machine_id VARCHAR(255) NOT NULL,
256 label VARCHAR(100),
257 activated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
258 last_validated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
259 is_active BOOLEAN NOT NULL DEFAULT true,
260 UNIQUE (license_key_id, machine_id)
261 );
262
263 CREATE TABLE discount_codes (
264 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
265 seller_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
266 code TEXT NOT NULL,
267 discount_type TEXT NOT NULL CHECK (discount_type IN ('percentage', 'fixed')),
268 discount_value INT NOT NULL CHECK (discount_value > 0),
269 min_price_cents INT NOT NULL DEFAULT 0,
270 max_uses INT,
271 use_count INT NOT NULL DEFAULT 0,
272 expires_at TIMESTAMPTZ,
273 item_id UUID REFERENCES items(id) ON DELETE CASCADE,
274 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
275 );
276
277 CREATE TABLE download_codes (
278 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
279 item_id UUID NOT NULL REFERENCES items(id) ON DELETE CASCADE,
280 created_by_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
281 code TEXT NOT NULL UNIQUE,
282 max_uses INT,
283 use_count INT NOT NULL DEFAULT 0,
284 expires_at TIMESTAMPTZ,
285 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
286 );
287
288 -- ============================================================================
289 -- Social (Follows, Blog, Custom Links)
290 -- ============================================================================
291
292 CREATE TABLE follows (
293 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
294 follower_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
295 target_type TEXT NOT NULL CHECK (target_type IN ('user', 'project', 'tag')),
296 target_id UUID NOT NULL,
297 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
298 );
299
300 CREATE TABLE blog_posts (
301 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
302 project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
303 author_id UUID NOT NULL REFERENCES users(id),
304 title VARCHAR(200) NOT NULL,
305 slug VARCHAR(100) NOT NULL,
306 body_markdown TEXT NOT NULL DEFAULT '',
307 body_html TEXT NOT NULL DEFAULT '',
308 published_at TIMESTAMPTZ,
309 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
310 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
311 UNIQUE (project_id, slug)
312 );
313
314 CREATE TABLE custom_links (
315 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
316 user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
317 url VARCHAR(500) NOT NULL,
318 title VARCHAR(100) NOT NULL,
319 description VARCHAR(255),
320 sort_order INT NOT NULL DEFAULT 0 CHECK (sort_order >= 0),
321 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
322 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
323 );
324
325 -- ============================================================================
326 -- SyncKit
327 -- ============================================================================
328
329 CREATE TABLE sync_apps (
330 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
331 creator_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
332 name VARCHAR(100) NOT NULL,
333 api_key VARCHAR(64) NOT NULL UNIQUE,
334 is_active BOOLEAN NOT NULL DEFAULT true,
335 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
336 );
337
338 CREATE TABLE sync_devices (
339 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
340 app_id UUID NOT NULL REFERENCES sync_apps(id) ON DELETE CASCADE,
341 user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
342 device_name VARCHAR(100) NOT NULL,
343 platform VARCHAR(50) NOT NULL,
344 last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
345 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
346 UNIQUE (app_id, user_id, device_name)
347 );
348
349 CREATE TABLE sync_keys (
350 app_id UUID NOT NULL REFERENCES sync_apps(id) ON DELETE CASCADE,
351 user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
352 key_version INT NOT NULL DEFAULT 1,
353 encrypted_key TEXT NOT NULL,
354 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
355 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
356 PRIMARY KEY (app_id, user_id)
357 );
358
359 CREATE TABLE sync_log (
360 seq BIGSERIAL PRIMARY KEY,
361 app_id UUID NOT NULL REFERENCES sync_apps(id) ON DELETE CASCADE,
362 user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
363 device_id UUID NOT NULL REFERENCES sync_devices(id) ON DELETE CASCADE,
364 table_name VARCHAR(100) NOT NULL,
365 operation VARCHAR(10) NOT NULL
366 CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
367 row_id VARCHAR(255) NOT NULL,
368 client_timestamp TIMESTAMPTZ NOT NULL,
369 data JSONB,
370 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
371 );
372
373 -- ============================================================================
374 -- Auth & Sessions
375 -- ============================================================================
376
377 CREATE TABLE login_tokens (
378 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
379 user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
380 token_hash VARCHAR(64) NOT NULL,
381 expires_at TIMESTAMPTZ NOT NULL,
382 used_at TIMESTAMPTZ,
383 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
384 );
385
386 CREATE TABLE user_sessions (
387 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
388 user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
389 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
390 last_active_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
391 user_agent TEXT,
392 ip_address TEXT
393 );
394
395 CREATE TABLE backup_codes (
396 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
397 user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
398 code_hash TEXT NOT NULL,
399 used_at TIMESTAMPTZ,
400 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
401 );
402
403 CREATE TABLE oauth_authorization_codes (
404 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
405 code VARCHAR(64) NOT NULL UNIQUE,
406 app_id UUID NOT NULL REFERENCES sync_apps(id) ON DELETE CASCADE,
407 user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
408 code_challenge VARCHAR(128) NOT NULL,
409 code_challenge_method VARCHAR(10) NOT NULL DEFAULT 'S256',
410 redirect_uri TEXT NOT NULL,
411 expires_at TIMESTAMPTZ NOT NULL,
412 used_at TIMESTAMPTZ,
413 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
414 );
415
416 -- ============================================================================
417 -- Creator Waitlist
418 -- ============================================================================
419
420 CREATE TABLE creator_waves (
421 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
422 wave_number INT NOT NULL UNIQUE,
423 hand_picked_count INT NOT NULL DEFAULT 0,
424 lottery_count INT NOT NULL DEFAULT 0,
425 total_eligible INT NOT NULL DEFAULT 0,
426 note TEXT,
427 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
428 );
429
430 CREATE TABLE creator_waitlist (
431 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
432 user_id UUID NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
433 pitch TEXT NOT NULL,
434 status VARCHAR(20) NOT NULL DEFAULT 'pending',
435 selection_method VARCHAR(20),
436 wave_id UUID REFERENCES creator_waves(id) ON DELETE SET NULL,
437 admin_note TEXT,
438 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
439 reviewed_at TIMESTAMPTZ
440 );
441
442 -- ============================================================================
443 -- Tower Sessions (managed by tower-sessions-sqlx-store)
444 -- ============================================================================
445
446 CREATE TABLE tower_sessions.session (
447 id TEXT PRIMARY KEY,
448 data BYTEA NOT NULL,
449 expiry_date TIMESTAMPTZ NOT NULL
450 );
451
452 -- ============================================================================
453 -- Indexes
454 -- ============================================================================
455
456 -- Users
457 CREATE INDEX idx_users_email ON users(email);
458 CREATE INDEX idx_users_username ON users(username);
459 CREATE INDEX idx_users_email_verified ON users(email_verified) WHERE email_verified = false;
460 CREATE INDEX idx_users_stripe_account ON users(stripe_account_id) WHERE stripe_account_id IS NOT NULL;
461
462 -- Projects
463 CREATE INDEX idx_projects_user_id ON projects(user_id);
464 CREATE INDEX idx_projects_public ON projects(is_public) WHERE is_public = true;
465 CREATE INDEX idx_projects_category_id ON projects(category_id);
466 CREATE INDEX idx_projects_title_trgm ON projects USING gin (title gin_trgm_ops);
467 CREATE INDEX idx_projects_desc_trgm ON projects USING gin (COALESCE(description, '') gin_trgm_ops);
468
469 -- Items
470 CREATE INDEX idx_items_project_id ON items(project_id);
471 CREATE INDEX idx_items_public ON items(is_public, created_at DESC) WHERE is_public = true;
472 CREATE INDEX idx_items_sales_count ON items(sales_count DESC);
473 CREATE INDEX idx_items_search ON items USING gin (
474 to_tsvector('english', title || ' ' || COALESCE(description, '') || ' ' || COALESCE(body, ''))
475 );
476 CREATE INDEX idx_items_title_trgm ON items USING gin (title gin_trgm_ops);
477 CREATE INDEX idx_items_desc_trgm ON items USING gin (COALESCE(description, '') gin_trgm_ops);
478
479 -- Versions
480 CREATE INDEX idx_versions_item_id ON versions(item_id);
481 CREATE INDEX idx_versions_current ON versions(item_id, is_current) WHERE is_current = true;
482 CREATE UNIQUE INDEX idx_versions_unique_current ON versions(item_id) WHERE is_current = true;
483
484 -- Chapters
485 CREATE INDEX idx_chapters_item_id ON chapters(item_id, sort_order);
486
487 -- Tags
488 CREATE INDEX idx_tags_parent ON tags(parent_id);
489 CREATE INDEX idx_tags_slug ON tags(slug);
490 CREATE INDEX idx_tags_name_trgm ON tags USING gin (name gin_trgm_ops);
491 CREATE INDEX idx_item_tags_tag ON item_tags(tag_id);
492 CREATE UNIQUE INDEX idx_item_tags_primary ON item_tags(item_id) WHERE is_primary = true;
493
494 -- Transactions
495 CREATE INDEX idx_transactions_buyer_id ON transactions(buyer_id);
496 CREATE INDEX idx_transactions_seller_id ON transactions(seller_id);
497 CREATE INDEX idx_transactions_item_id ON transactions(item_id);
498 CREATE INDEX idx_transactions_status ON transactions(status);
499 CREATE INDEX idx_transactions_stripe_session ON transactions(stripe_checkout_session_id);
500 CREATE UNIQUE INDEX idx_transactions_buyer_item_completed
501 ON transactions(buyer_id, item_id) WHERE status = 'completed';
502
503 -- Subscriptions
504 CREATE INDEX idx_subscription_tiers_project_id ON subscription_tiers(project_id);
505 CREATE INDEX idx_subscriptions_subscriber_id ON subscriptions(subscriber_id);
506 CREATE INDEX idx_subscriptions_project_id ON subscriptions(project_id);
507 CREATE INDEX idx_subscriptions_stripe_sub_id ON subscriptions(stripe_subscription_id);
508 CREATE INDEX idx_subscriptions_active ON subscriptions(project_id, subscriber_id) WHERE status = 'active';
509 CREATE UNIQUE INDEX idx_subscriptions_active_unique
510 ON subscriptions(subscriber_id, project_id) WHERE status = 'active';
511 CREATE UNIQUE INDEX idx_subscriptions_unique
512 ON subscriptions(subscriber_id, project_id, stripe_subscription_id);
513
514 -- License keys
515 CREATE INDEX idx_license_keys_item ON license_keys(item_id);
516 CREATE INDEX idx_license_keys_owner ON license_keys(owner_id);
517 CREATE INDEX idx_license_keys_code ON license_keys(key_code);
518 CREATE INDEX idx_activations_key ON license_activations(license_key_id);
519
520 -- Discount & download codes
521 CREATE UNIQUE INDEX idx_discount_codes_seller_code ON discount_codes(seller_id, code);
522 CREATE INDEX idx_discount_codes_item_id ON discount_codes(item_id);
523 CREATE UNIQUE INDEX idx_download_codes_code ON download_codes(code);
524 CREATE INDEX idx_download_codes_item_id ON download_codes(item_id);
525
526 -- Follows
527 CREATE UNIQUE INDEX idx_follows_unique ON follows(follower_id, target_type, target_id);
528 CREATE INDEX idx_follows_target ON follows(target_type, target_id);
529
530 -- Blog
531 CREATE INDEX idx_blog_posts_project ON blog_posts(project_id);
532
533 -- Custom links
534 CREATE INDEX idx_custom_links_user_id ON custom_links(user_id, sort_order);
535
536 -- Auth
537 CREATE INDEX idx_login_tokens_user ON login_tokens(user_id);
538 CREATE INDEX idx_login_tokens_expires ON login_tokens(expires_at);
539 CREATE INDEX idx_user_sessions_user_id ON user_sessions(user_id);
540 CREATE INDEX idx_backup_codes_user ON backup_codes(user_id);
541 CREATE INDEX idx_oauth_codes_code ON oauth_authorization_codes(code);
542 CREATE INDEX idx_oauth_codes_expires ON oauth_authorization_codes(expires_at);
543
544 -- Waitlist
545 CREATE INDEX idx_waitlist_status ON creator_waitlist(status);
546
547 -- SyncKit
548 CREATE INDEX idx_sync_apps_creator_id ON sync_apps(creator_id);
549 CREATE INDEX idx_sync_apps_api_key ON sync_apps(api_key);
550 CREATE INDEX idx_sync_devices_app_user ON sync_devices(app_id, user_id);
551 CREATE INDEX idx_sync_log_app_user_seq ON sync_log(app_id, user_id, seq);
552 CREATE INDEX idx_sync_log_created_at ON sync_log(created_at);
553
554 -- ============================================================================
555 -- Triggers
556 -- ============================================================================
557
558 CREATE TRIGGER update_users_updated_at
559 BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
560 CREATE TRIGGER update_projects_updated_at
561 BEFORE UPDATE ON projects FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
562 CREATE TRIGGER update_items_updated_at
563 BEFORE UPDATE ON items FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
564 CREATE TRIGGER update_custom_links_updated_at
565 BEFORE UPDATE ON custom_links FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
566 CREATE TRIGGER set_subscription_tiers_updated_at
567 BEFORE UPDATE ON subscription_tiers FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
568 CREATE TRIGGER set_subscriptions_updated_at
569 BEFORE UPDATE ON subscriptions FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
570