-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathschema.sql
More file actions
511 lines (437 loc) · 20.7 KB
/
schema.sql
File metadata and controls
511 lines (437 loc) · 20.7 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
-- EXTENSION SETUP
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm"; -- For fuzzy text search
-- CORE ENTITIES
-- Accounts: One per phone number, holds all tokens
CREATE TABLE accounts (
account_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
phone_number VARCHAR(20) UNIQUE NOT NULL,
phone_verified_at TIMESTAMP NOT NULL,
-- Token Economy
token_balance DECIMAL(12, 2) NOT NULL DEFAULT 100.00 CHECK (token_balance >= 0),
tokens_earned_lifetime DECIMAL(12, 2) NOT NULL DEFAULT 0,
tokens_spent_lifetime DECIMAL(12, 2) NOT NULL DEFAULT 0,
-- Taxation & Stipend
last_tax_collected_at TIMESTAMP,
last_stipend_received_at TIMESTAMP,
total_tax_paid DECIMAL(12, 2) NOT NULL DEFAULT 0,
total_stipend_received DECIMAL(12, 2) NOT NULL DEFAULT 0,
-- Activity Tracking
daily_comments_free_remaining INT NOT NULL DEFAULT 2,
daily_comments_reset_at DATE NOT NULL DEFAULT CURRENT_DATE,
last_active_at TIMESTAMP NOT NULL DEFAULT NOW(),
-- Onboarding
is_early_adopter BOOLEAN NOT NULL DEFAULT FALSE,
weeks_since_signup INT NOT NULL DEFAULT 0, -- For new user stipend bonus
-- Stats (denormalized for performance)
posts_created_count INT NOT NULL DEFAULT 0,
comments_created_count INT NOT NULL DEFAULT 0,
-- Metadata
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
CONSTRAINT chk_phone_format CHECK (phone_number ~ '^\+[1-9]\d{1,14}$')
);
CREATE INDEX idx_accounts_phone ON accounts(phone_number);
CREATE INDEX idx_accounts_balance ON accounts(token_balance DESC) WHERE token_balance > 0;
CREATE INDEX idx_accounts_tax ON accounts(last_tax_collected_at, token_balance)
WHERE token_balance > 200;
CREATE INDEX idx_accounts_stipend ON accounts(last_stipend_received_at, last_active_at);
CREATE INDEX idx_accounts_active ON accounts(last_active_at DESC);
-- Pages: User identity (MVP: 1 per account)
CREATE TABLE pages (
page_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
account_id UUID NOT NULL REFERENCES accounts(account_id) ON DELETE CASCADE,
-- Identity
username VARCHAR(30) UNIQUE NOT NULL,
display_name VARCHAR(100) NOT NULL,
bio TEXT,
avatar_url VARCHAR(500),
-- Status
is_active BOOLEAN NOT NULL DEFAULT TRUE,
is_primary BOOLEAN NOT NULL DEFAULT TRUE, -- MVP: always true
-- Stats
followers_count INT NOT NULL DEFAULT 0,
posts_count INT NOT NULL DEFAULT 0,
total_tokens_earned DECIMAL(12, 2) NOT NULL DEFAULT 0,
-- Metadata
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
CONSTRAINT chk_username_format CHECK (username ~ '^[a-z0-9_]{3,30}$'),
CONSTRAINT chk_bio_length CHECK (LENGTH(bio) <= 500)
);
CREATE UNIQUE INDEX idx_pages_username ON pages(LOWER(username));
CREATE INDEX idx_pages_account ON pages(account_id);
CREATE INDEX idx_pages_created ON pages(created_at DESC);
-- Follows: Social graph
CREATE TABLE follows (
follower_account_id UUID REFERENCES accounts(account_id) ON DELETE CASCADE,
following_page_id UUID REFERENCES pages(page_id) ON DELETE CASCADE,
created_at TIMESTAMP DEFAULT NOW(),
PRIMARY KEY (follower_account_id, following_page_id)
);
CREATE INDEX idx_follows_follower ON follows(follower_account_id);
CREATE INDEX idx_follows_following ON follows(following_page_id);
-- Posts: Content created by pages
CREATE TABLE posts (
post_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
page_id UUID NOT NULL REFERENCES pages(page_id) ON DELETE CASCADE,
account_id UUID NOT NULL REFERENCES accounts(account_id) ON DELETE CASCADE,
-- Content
content TEXT NOT NULL,
content_hash VARCHAR(64), -- For duplicate detection
-- Token Metrics
total_tokens_received DECIMAL(12, 2) NOT NULL DEFAULT 0,
unique_spenders_count INT NOT NULL DEFAULT 0,
peak_token_percentage DECIMAL(8, 6) NOT NULL DEFAULT 0, -- For boost eligibility
-- MLFQ (Multi-Level Feedback Queue)
queue_level INT NOT NULL DEFAULT 0 CHECK (queue_level BETWEEN 0 AND 3),
queue_entered_at TIMESTAMP NOT NULL DEFAULT NOW(),
last_queue_promotion_at TIMESTAMP,
last_queue_demotion_at TIMESTAMP,
-- Decay System
decay_rate DECIMAL(5, 4) NOT NULL DEFAULT 0.02, -- 2% weekly in MVP
last_decay_applied_at TIMESTAMP NOT NULL DEFAULT NOW(),
last_token_received_at TIMESTAMP,
tokens_lost_to_decay DECIMAL(12, 2) NOT NULL DEFAULT 0,
-- Boost System (simplified in MVP)
last_boosted_at TIMESTAMP,
boost_count INT NOT NULL DEFAULT 0,
-- Ranking Signals
visibility_score DECIMAL(12, 4) NOT NULL DEFAULT 0,
engagement_velocity DECIMAL(12, 4) NOT NULL DEFAULT 0, -- Tokens per hour
-- Engagement Stats
comments_count INT NOT NULL DEFAULT 0,
views_count BIGINT NOT NULL DEFAULT 0,
-- Lifecycle
is_edited BOOLEAN NOT NULL DEFAULT FALSE,
edited_at TIMESTAMP,
deleted_at TIMESTAMP,
deleted_by UUID REFERENCES accounts(account_id),
-- Metadata
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
CONSTRAINT chk_content_length CHECK (LENGTH(content) BETWEEN 1 AND 2000)
);
CREATE INDEX idx_posts_page ON posts(page_id, created_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX idx_posts_account ON posts(account_id, created_at DESC);
CREATE INDEX idx_posts_created ON posts(created_at DESC) WHERE deleted_at IS NULL;
-- Hot Feed: Queue 0-1, sorted by visibility score then time
CREATE INDEX idx_posts_hot_feed ON posts(queue_level, visibility_score DESC, created_at DESC)
WHERE deleted_at IS NULL AND queue_level IN (0, 1);
-- Token leaderboard
CREATE INDEX idx_posts_tokens ON posts(total_tokens_received DESC) WHERE deleted_at IS NULL;
-- Decay job
CREATE INDEX idx_posts_decay ON posts(last_decay_applied_at, total_tokens_received)
WHERE deleted_at IS NULL AND total_tokens_received > 0;
-- Queue management
CREATE INDEX idx_posts_queue ON posts(queue_level, queue_entered_at) WHERE deleted_at IS NULL;
-- Comments: Responses to posts
CREATE TABLE comments (
comment_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
post_id UUID NOT NULL REFERENCES posts(post_id) ON DELETE CASCADE,
page_id UUID NOT NULL REFERENCES pages(page_id) ON DELETE CASCADE,
account_id UUID NOT NULL REFERENCES accounts(account_id) ON DELETE CASCADE,
-- Content
content TEXT NOT NULL,
-- Token Metrics
total_tokens_received DECIMAL(12, 2) NOT NULL DEFAULT 0,
unique_spenders_count INT NOT NULL DEFAULT 0,
-- Threading (MVP: flat, but schema ready)
parent_comment_id UUID REFERENCES comments(comment_id) ON DELETE CASCADE,
thread_depth INT NOT NULL DEFAULT 0 CHECK (thread_depth >= 0),
-- Cost tracking
was_free BOOLEAN NOT NULL DEFAULT TRUE, -- First 2 daily comments
token_cost DECIMAL(4, 2) NOT NULL DEFAULT 0,
-- Lifecycle
deleted_at TIMESTAMP,
-- Metadata
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
CONSTRAINT chk_comment_length CHECK (LENGTH(content) BETWEEN 1 AND 1000)
);
CREATE INDEX idx_comments_post ON comments(post_id, created_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX idx_comments_page ON comments(page_id, created_at DESC);
CREATE INDEX idx_comments_account ON comments(account_id, created_at DESC);
CREATE INDEX idx_comments_parent ON comments(parent_comment_id) WHERE parent_comment_id IS NOT NULL;
-- TOKEN ECONOMY
-- Token Transactions: Every token movement is recorded
CREATE TABLE token_transactions (
transaction_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
-- Spender
spender_account_id UUID NOT NULL REFERENCES accounts(account_id) ON DELETE RESTRICT,
spender_page_id UUID REFERENCES pages(page_id) ON DELETE SET NULL,
-- Target
target_type VARCHAR(20) NOT NULL CHECK (target_type IN ('post', 'comment')),
target_id UUID NOT NULL,
-- Amounts
tokens_spent DECIMAL(12, 2) NOT NULL CHECK (tokens_spent > 0),
tokens_to_creator DECIMAL(12, 2) NOT NULL CHECK (tokens_to_creator >= 0),
tokens_to_government DECIMAL(12, 2) NOT NULL CHECK (tokens_to_government >= 0),
tokens_burned DECIMAL(12, 2) NOT NULL CHECK (tokens_burned >= 0),
-- Recipient
recipient_account_id UUID REFERENCES accounts(account_id) ON DELETE SET NULL,
recipient_page_id UUID REFERENCES pages(page_id) ON DELETE SET NULL,
-- Unlike window
can_unlike_until TIMESTAMP NOT NULL,
unliked_at TIMESTAMP,
-- Metadata
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
CONSTRAINT chk_amounts_sum CHECK (
tokens_spent = tokens_to_creator + tokens_to_government + tokens_burned
),
CONSTRAINT chk_not_self_spend CHECK (spender_account_id != recipient_account_id)
);
CREATE INDEX idx_transactions_spender ON token_transactions(spender_account_id, created_at DESC);
CREATE INDEX idx_transactions_recipient ON token_transactions(recipient_account_id, created_at DESC)
WHERE recipient_account_id IS NOT NULL;
CREATE INDEX idx_transactions_target ON token_transactions(target_type, target_id, created_at DESC);
DROP INDEX IF EXISTS idx_transactions_unlikeable;
CREATE INDEX idx_transactions_unlikeable ON token_transactions(spender_account_id, can_unlike_until)
WHERE unliked_at IS NULL;
-- Tax Collections: Weekly progressive taxation
CREATE TABLE tax_collections (
collection_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
account_id UUID NOT NULL REFERENCES accounts(account_id) ON DELETE CASCADE,
balance_before DECIMAL(12, 2) NOT NULL,
tax_bracket VARCHAR(20) NOT NULL,
tax_rate DECIMAL(5, 4) NOT NULL CHECK (tax_rate >= 0 AND tax_rate <= 1),
tokens_taxed DECIMAL(12, 2) NOT NULL CHECK (tokens_taxed >= 0),
balance_after DECIMAL(12, 2) NOT NULL,
collected_at TIMESTAMP NOT NULL DEFAULT NOW(),
CONSTRAINT chk_tax_math CHECK (balance_after = balance_before - tokens_taxed)
);
CREATE INDEX idx_tax_account ON tax_collections(account_id, collected_at DESC);
CREATE INDEX idx_tax_collected ON tax_collections(collected_at DESC);
-- Stipend Distributions: Weekly universal basic income
CREATE TABLE stipend_distributions (
distribution_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
account_id UUID NOT NULL REFERENCES accounts(account_id) ON DELETE CASCADE,
base_stipend DECIMAL(12, 2) NOT NULL CHECK (base_stipend >= 0),
engagement_bonus DECIMAL(12, 2) NOT NULL DEFAULT 0 CHECK (engagement_bonus >= 0),
new_user_bonus DECIMAL(12, 2) NOT NULL DEFAULT 0 CHECK (new_user_bonus >= 0),
total_stipend DECIMAL(12, 2) NOT NULL,
was_active BOOLEAN NOT NULL,
tokens_earned_last_week DECIMAL(12, 2) NOT NULL DEFAULT 0,
distributed_at TIMESTAMP NOT NULL DEFAULT NOW(),
CONSTRAINT chk_stipend_sum CHECK (
total_stipend = base_stipend + engagement_bonus + new_user_bonus
)
);
CREATE INDEX idx_stipend_account ON stipend_distributions(account_id, distributed_at DESC);
CREATE INDEX idx_stipend_distributed ON stipend_distributions(distributed_at DESC);
-- Decay Events: Track token decay applications
CREATE TABLE decay_events (
event_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
post_id UUID NOT NULL REFERENCES posts(post_id) ON DELETE CASCADE,
balance_before DECIMAL(12, 2) NOT NULL,
decay_rate DECIMAL(5, 4) NOT NULL,
tokens_decayed DECIMAL(12, 2) NOT NULL CHECK (tokens_decayed >= 0),
balance_after DECIMAL(12, 2) NOT NULL,
applied_at TIMESTAMP NOT NULL DEFAULT NOW(),
CONSTRAINT chk_decay_math CHECK (balance_after = balance_before - tokens_decayed)
);
CREATE INDEX idx_decay_post ON decay_events(post_id, applied_at DESC);
CREATE INDEX idx_decay_applied ON decay_events(applied_at DESC);
-- SYSTEM STATE & MONITORING
-- System State: Global token supply and system metrics
CREATE TABLE system_state (
id INT PRIMARY KEY DEFAULT 1 CHECK (id = 1), -- Singleton
-- Token Supply
total_tokens_in_circulation DECIMAL(15, 2) NOT NULL DEFAULT 0 CHECK (total_tokens_in_circulation >= 0),
total_tokens_locked_on_content DECIMAL(15, 2) NOT NULL DEFAULT 0,
total_tokens_in_government_pool DECIMAL(15, 2) NOT NULL DEFAULT 0,
total_tokens_burned DECIMAL(15, 2) NOT NULL DEFAULT 0,
total_tokens_ever_created DECIMAL(15, 2) NOT NULL DEFAULT 0,
-- System Stats
total_accounts INT NOT NULL DEFAULT 0,
active_accounts_today INT NOT NULL DEFAULT 0,
total_posts INT NOT NULL DEFAULT 0,
total_comments INT NOT NULL DEFAULT 0,
-- Job Execution Tracking
last_tax_collection_at TIMESTAMP,
last_stipend_distribution_at TIMESTAMP,
last_decay_run_at TIMESTAMP,
last_queue_update_at TIMESTAMP,
last_analytics_update_at TIMESTAMP,
-- Metadata
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
-- Initialize system state
INSERT INTO system_state (id) VALUES (1);
-- Phone Verifications: OTP management
CREATE TABLE phone_verifications (
verification_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
phone_number VARCHAR(20) NOT NULL,
verification_code VARCHAR(6) NOT NULL,
code_hash VARCHAR(64) NOT NULL, -- Hashed code for security
attempts INT NOT NULL DEFAULT 0,
max_attempts INT NOT NULL DEFAULT 3,
verified_at TIMESTAMP,
expires_at TIMESTAMP NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
CONSTRAINT chk_code_format CHECK (verification_code ~ '^\d{6}$')
);
CREATE INDEX idx_verifications_phone ON phone_verifications(phone_number, created_at DESC);
CREATE INDEX idx_verifications_expiry ON phone_verifications(expires_at)
WHERE verified_at IS NULL;
-- Notifications: User alerts
CREATE TABLE notifications (
notification_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
account_id UUID NOT NULL REFERENCES accounts(account_id) ON DELETE CASCADE,
type VARCHAR(50) NOT NULL,
title VARCHAR(200) NOT NULL,
message TEXT NOT NULL,
reference_type VARCHAR(20),
reference_id UUID,
is_read BOOLEAN NOT NULL DEFAULT FALSE,
read_at TIMESTAMP,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
CONSTRAINT chk_notification_type CHECK (type IN (
'token_earned', 'comment_reply', 'tax_collected',
'stipend_received', 'post_boosted', 'unlike_refund'
))
);
CREATE INDEX idx_notifications_account ON notifications(account_id, is_read, created_at DESC);
CREATE INDEX idx_notifications_unread ON notifications(account_id, created_at DESC)
WHERE is_read = FALSE;
-- Audit Logs: System-wide audit trail
CREATE TABLE audit_logs (
log_id BIGSERIAL PRIMARY KEY,
account_id UUID REFERENCES accounts(account_id) ON DELETE SET NULL,
page_id UUID REFERENCES pages(page_id) ON DELETE SET NULL,
action VARCHAR(50) NOT NULL,
entity_type VARCHAR(20),
entity_id UUID,
details JSONB,
ip_address INET,
user_agent TEXT,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_audit_account ON audit_logs(account_id, created_at DESC);
CREATE INDEX idx_audit_action ON audit_logs(action, created_at DESC);
CREATE INDEX idx_audit_entity ON audit_logs(entity_type, entity_id, created_at DESC);
CREATE INDEX idx_audit_created ON audit_logs(created_at DESC);
-- VIEWS FOR COMMON QUERIES
-- Hot Feed View
CREATE VIEW v_hot_feed AS
SELECT
p.post_id,
p.content,
p.total_tokens_received,
p.unique_spenders_count,
p.comments_count,
p.visibility_score,
p.queue_level,
p.created_at,
pg.page_id,
pg.username,
pg.display_name,
pg.avatar_url
FROM posts p
JOIN pages pg ON p.page_id = pg.page_id
WHERE p.deleted_at IS NULL
AND p.queue_level IN (0, 1)
ORDER BY p.visibility_score DESC, p.created_at DESC;
-- Account Wealth View
CREATE VIEW v_account_wealth AS
SELECT
a.account_id,
a.token_balance,
a.tokens_earned_lifetime,
a.tokens_spent_lifetime,
a.total_tax_paid,
a.total_stipend_received,
COALESCE(SUM(p.total_tokens_received), 0) as tokens_locked_on_posts,
RANK() OVER (ORDER BY a.token_balance DESC) as wealth_rank
FROM accounts a
LEFT JOIN posts p ON a.account_id = p.account_id
AND p.deleted_at IS NULL
GROUP BY a.account_id;
-- FUNCTIONS & TRIGGERS
-- Update updated_at timestamp
-- CREATE OR REPLACE FUNCTION update_updated_at_column()
-- RETURNS TRIGGER AS $$
-- BEGIN
-- NEW.updated_at = NOW();
-- RETURN NEW;
-- END;
-- $$ language 'plpgsql';
-- CREATE TRIGGER update_accounts_updated_at BEFORE UPDATE ON accounts
-- FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
-- CREATE TRIGGER update_pages_updated_at BEFORE UPDATE ON pages
-- FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
-- CREATE TRIGGER update_posts_updated_at BEFORE UPDATE ON posts
-- FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
-- -- Reset daily free comments
-- CREATE OR REPLACE FUNCTION reset_daily_free_comments()
-- RETURNS TRIGGER AS $$
-- BEGIN
-- IF NEW.daily_comments_reset_at < CURRENT_DATE THEN
-- NEW.daily_comments_free_remaining = 2;
-- NEW.daily_comments_reset_at = CURRENT_DATE;
-- END IF;
-- RETURN NEW;
-- END;
-- $$ language 'plpgsql';
-- CREATE TRIGGER reset_free_comments_on_update BEFORE UPDATE ON accounts
-- FOR EACH ROW EXECUTE FUNCTION reset_daily_free_comments();
-- -- Maintain comment count on posts
-- CREATE OR REPLACE FUNCTION update_post_comment_count()
-- RETURNS TRIGGER AS $$
-- BEGIN
-- IF TG_OP = 'INSERT' AND NEW.deleted_at IS NULL THEN
-- UPDATE posts
-- SET comments_count = comments_count + 1
-- WHERE post_id = NEW.post_id;
-- ELSIF TG_OP = 'UPDATE' AND OLD.deleted_at IS NULL AND NEW.deleted_at IS NOT NULL THEN
-- UPDATE posts
-- SET comments_count = comments_count - 1
-- WHERE post_id = NEW.post_id;
-- ELSIF TG_OP = 'UPDATE' AND OLD.deleted_at IS NOT NULL AND NEW.deleted_at IS NULL THEN
-- UPDATE posts
-- SET comments_count = comments_count + 1
-- WHERE post_id = NEW.post_id;
-- END IF;
-- RETURN NEW;
-- END;
-- $$ language 'plpgsql';
-- CREATE TRIGGER maintain_comment_count AFTER INSERT OR UPDATE ON comments
-- FOR EACH ROW EXECUTE FUNCTION update_post_comment_count();
-- ---
-- ## 🔌 Complete API Design
-- ### API Principles
-- - **RESTful**: Resource-oriented URLs
-- - **Versioned**: `/api/v1/...` for future compatibility
-- - **Consistent**: Standard response formats
-- - **Secure**: JWT authentication, rate limiting
-- - **Validated**: Zod schemas for all inputs
-- - **Documented**: OpenAPI/Swagger spec
-- ### Authentication Flow
-- ┌─────────┐ ┌─────────┐
-- │ Client │ │ Server │
-- └────┬────┘ └────┬────┘
-- │ │
-- │ POST /auth/send-code │
-- │ { phone: "+1234567890" } │
-- ├─────────────────────────────────────────────>│
-- │ │
-- │ Generate OTP
-- │ Send via Twilio
-- │ │
-- │ { verification_id, expires_at } │
-- │<─────────────────────────────────────────────┤
-- │ │
-- │ POST /auth/verify-code │
-- │ { verification_id, code: "123456" } │
-- ├─────────────────────────────────────────────>│
-- │ │
-- │ Validate code
-- │ Create/login account
-- │ Generate JWT
-- │ │
-- │ { account, tokens: { access, refresh } } │
-- │<─────────────────────────────────────────────┤
-- │ │
-- │ All subsequent requests: │
-- │ Authorization: Bearer <access_token> │
-- ├─────────────────────────────────────────────>│