A community based topic aggregation platform built on atproto
bretton.dev
1package postgres
2
3import (
4 "Coves/internal/core/posts"
5 "crypto/hmac"
6 "crypto/sha256"
7 "database/sql"
8 "encoding/base64"
9 "encoding/hex"
10 "encoding/json"
11 "fmt"
12 "strconv"
13 "strings"
14 "time"
15
16 "github.com/lib/pq"
17)
18
19// feedRepoBase contains shared logic for timeline and discover feed repositories
20// This eliminates ~85% code duplication and ensures bug fixes apply to both feeds
21//
22// DATABASE INDEXES REQUIRED:
23// The feed queries rely on these indexes (created in migration 011_create_posts_table.sql):
24//
25// 1. idx_posts_community_created ON posts(community_did, created_at DESC) WHERE deleted_at IS NULL
26// - Used by: Both timeline and discover for "new" sort
27// - Covers: Community filtering + chronological ordering + soft delete filter
28//
29// 2. idx_posts_community_score ON posts(community_did, score DESC, created_at DESC) WHERE deleted_at IS NULL
30// - Used by: Both timeline and discover for "top" sort
31// - Covers: Community filtering + score ordering + tie-breaking + soft delete filter
32//
33// 3. idx_subscriptions_user_community ON community_subscriptions(user_did, community_did)
34// - Used by: Timeline feed (JOIN with subscriptions)
35// - Covers: User subscription lookup
36//
37// 4. Hot sort uses computed expression: (score / POWER(age_hours + 2, 1.5))
38// - Cannot be indexed directly (computed at query time)
39// - Uses idx_posts_community_created for base ordering
40// - Performance: ~10-20ms for timeline, ~8-15ms for discover (acceptable for alpha)
41//
42// PERFORMANCE NOTES:
43// - All queries use single execution (no N+1)
44// - JOINs are minimal (3 for timeline, 2 for discover)
45// - Partial indexes (WHERE deleted_at IS NULL) eliminate soft-deleted posts efficiently
46// - Cursor pagination is stable (no offset drift)
47// - Limit+1 pattern checks for next page without extra query
48type feedRepoBase struct {
49 db *sql.DB
50 hotRankExpression string
51 sortClauses map[string]string
52 cursorSecret string // HMAC secret for cursor integrity protection
53}
54
55// newFeedRepoBase creates a new base repository with shared feed logic
56func newFeedRepoBase(db *sql.DB, hotRankExpr string, sortClauses map[string]string, cursorSecret string) *feedRepoBase {
57 return &feedRepoBase{
58 db: db,
59 hotRankExpression: hotRankExpr,
60 sortClauses: sortClauses,
61 cursorSecret: cursorSecret,
62 }
63}
64
65// buildSortClause returns the ORDER BY SQL and optional time filter
66// Uses whitelist map to prevent SQL injection via dynamic ORDER BY
67func (r *feedRepoBase) buildSortClause(sort, timeframe string) (string, string) {
68 // Use whitelist map for ORDER BY clause (defense-in-depth against SQL injection)
69 orderBy := r.sortClauses[sort]
70 if orderBy == "" {
71 orderBy = r.sortClauses["hot"] // safe default
72 }
73
74 // Add time filter for "top" sort
75 var timeFilter string
76 if sort == "top" {
77 timeFilter = r.buildTimeFilter(timeframe)
78 }
79
80 return orderBy, timeFilter
81}
82
83// buildTimeFilter returns SQL filter for timeframe
84func (r *feedRepoBase) buildTimeFilter(timeframe string) string {
85 if timeframe == "" || timeframe == "all" {
86 return ""
87 }
88
89 var interval string
90 switch timeframe {
91 case "hour":
92 interval = "1 hour"
93 case "day":
94 interval = "1 day"
95 case "week":
96 interval = "1 week"
97 case "month":
98 interval = "1 month"
99 case "year":
100 interval = "1 year"
101 default:
102 return ""
103 }
104
105 return fmt.Sprintf("AND p.created_at > NOW() - INTERVAL '%s'", interval)
106}
107
108// parseCursor decodes and validates pagination cursor
109// paramOffset is the starting parameter number for cursor values ($2 for discover, $3 for timeline)
110func (r *feedRepoBase) parseCursor(cursor *string, sort string, paramOffset int) (string, []interface{}, error) {
111 if cursor == nil || *cursor == "" {
112 return "", nil, nil
113 }
114
115 // Decode base64 cursor
116 decoded, err := base64.StdEncoding.DecodeString(*cursor)
117 if err != nil {
118 return "", nil, fmt.Errorf("invalid cursor encoding")
119 }
120
121 // Parse cursor: payload::signature
122 parts := strings.Split(string(decoded), "::")
123 if len(parts) < 2 {
124 return "", nil, fmt.Errorf("invalid cursor format")
125 }
126
127 // Verify HMAC signature
128 signatureHex := parts[len(parts)-1]
129 payload := strings.Join(parts[:len(parts)-1], "::")
130
131 expectedMAC := hmac.New(sha256.New, []byte(r.cursorSecret))
132 expectedMAC.Write([]byte(payload))
133 expectedSignature := hex.EncodeToString(expectedMAC.Sum(nil))
134
135 if !hmac.Equal([]byte(signatureHex), []byte(expectedSignature)) {
136 return "", nil, fmt.Errorf("invalid cursor signature")
137 }
138
139 // Parse payload based on sort type
140 payloadParts := strings.Split(payload, "::")
141
142 switch sort {
143 case "new":
144 // Cursor format: timestamp::uri
145 if len(payloadParts) != 2 {
146 return "", nil, fmt.Errorf("invalid cursor format")
147 }
148
149 createdAt := payloadParts[0]
150 uri := payloadParts[1]
151
152 // Validate timestamp format
153 if _, err := time.Parse(time.RFC3339Nano, createdAt); err != nil {
154 return "", nil, fmt.Errorf("invalid cursor timestamp")
155 }
156
157 // Validate URI format (must be AT-URI)
158 if !strings.HasPrefix(uri, "at://") {
159 return "", nil, fmt.Errorf("invalid cursor URI")
160 }
161
162 filter := fmt.Sprintf(`AND (p.created_at < $%d OR (p.created_at = $%d AND p.uri < $%d))`,
163 paramOffset, paramOffset, paramOffset+1)
164 return filter, []interface{}{createdAt, uri}, nil
165
166 case "top":
167 // Cursor format: score::timestamp::uri
168 if len(payloadParts) != 3 {
169 return "", nil, fmt.Errorf("invalid cursor format for %s sort", sort)
170 }
171
172 scoreStr := payloadParts[0]
173 createdAt := payloadParts[1]
174 uri := payloadParts[2]
175
176 // Validate score is numeric
177 score := 0
178 if _, err := fmt.Sscanf(scoreStr, "%d", &score); err != nil {
179 return "", nil, fmt.Errorf("invalid cursor score")
180 }
181
182 // Validate timestamp format
183 if _, err := time.Parse(time.RFC3339Nano, createdAt); err != nil {
184 return "", nil, fmt.Errorf("invalid cursor timestamp")
185 }
186
187 // Validate URI format (must be AT-URI)
188 if !strings.HasPrefix(uri, "at://") {
189 return "", nil, fmt.Errorf("invalid cursor URI")
190 }
191
192 filter := fmt.Sprintf(`AND (p.score < $%d OR (p.score = $%d AND p.created_at < $%d) OR (p.score = $%d AND p.created_at = $%d AND p.uri < $%d))`,
193 paramOffset, paramOffset, paramOffset+1, paramOffset, paramOffset+1, paramOffset+2)
194 return filter, []interface{}{score, createdAt, uri}, nil
195
196 case "hot":
197 // Cursor format: hot_rank::timestamp::uri
198 // CRITICAL: Must use computed hot_rank, not raw score, to prevent pagination bugs
199 if len(payloadParts) != 3 {
200 return "", nil, fmt.Errorf("invalid cursor format for hot sort")
201 }
202
203 hotRankStr := payloadParts[0]
204 createdAt := payloadParts[1]
205 uri := payloadParts[2]
206
207 // Validate hot_rank is numeric (float)
208 hotRank := 0.0
209 if _, err := fmt.Sscanf(hotRankStr, "%f", &hotRank); err != nil {
210 return "", nil, fmt.Errorf("invalid cursor hot rank")
211 }
212
213 // Validate timestamp format
214 if _, err := time.Parse(time.RFC3339Nano, createdAt); err != nil {
215 return "", nil, fmt.Errorf("invalid cursor timestamp")
216 }
217
218 // Validate URI format (must be AT-URI)
219 if !strings.HasPrefix(uri, "at://") {
220 return "", nil, fmt.Errorf("invalid cursor URI")
221 }
222
223 // CRITICAL: Compare against the computed hot_rank expression, not p.score
224 filter := fmt.Sprintf(`AND ((%s < $%d OR (%s = $%d AND p.created_at < $%d) OR (%s = $%d AND p.created_at = $%d AND p.uri < $%d)) AND p.uri != $%d)`,
225 r.hotRankExpression, paramOffset,
226 r.hotRankExpression, paramOffset, paramOffset+1,
227 r.hotRankExpression, paramOffset, paramOffset+1, paramOffset+2,
228 paramOffset+3)
229 return filter, []interface{}{hotRank, createdAt, uri, uri}, nil
230
231 default:
232 return "", nil, nil
233 }
234}
235
236// buildCursor creates HMAC-signed pagination cursor from last post
237// SECURITY: Cursor is signed with HMAC-SHA256 to prevent manipulation
238func (r *feedRepoBase) buildCursor(post *posts.PostView, sort string, hotRank float64) string {
239 var payload string
240 // Use :: as delimiter following Bluesky convention
241 const delimiter = "::"
242
243 switch sort {
244 case "new":
245 // Format: timestamp::uri
246 payload = fmt.Sprintf("%s%s%s", post.CreatedAt.Format(time.RFC3339Nano), delimiter, post.URI)
247
248 case "top":
249 // Format: score::timestamp::uri
250 score := 0
251 if post.Stats != nil {
252 score = post.Stats.Score
253 }
254 payload = fmt.Sprintf("%d%s%s%s%s", score, delimiter, post.CreatedAt.Format(time.RFC3339Nano), delimiter, post.URI)
255
256 case "hot":
257 // Format: hot_rank::timestamp::uri
258 // CRITICAL: Use computed hot_rank with full precision
259 hotRankStr := strconv.FormatFloat(hotRank, 'g', -1, 64)
260 payload = fmt.Sprintf("%s%s%s%s%s", hotRankStr, delimiter, post.CreatedAt.Format(time.RFC3339Nano), delimiter, post.URI)
261
262 default:
263 payload = post.URI
264 }
265
266 // Sign the payload with HMAC-SHA256
267 mac := hmac.New(sha256.New, []byte(r.cursorSecret))
268 mac.Write([]byte(payload))
269 signature := hex.EncodeToString(mac.Sum(nil))
270
271 // Append signature to payload
272 signed := payload + delimiter + signature
273
274 return base64.StdEncoding.EncodeToString([]byte(signed))
275}
276
277// scanFeedPost scans a database row into a PostView
278// This is the shared scanning logic used by both timeline and discover feeds
279func (r *feedRepoBase) scanFeedPost(rows *sql.Rows) (*posts.PostView, float64, error) {
280 var (
281 postView posts.PostView
282 authorView posts.AuthorView
283 communityRef posts.CommunityRef
284 title, content sql.NullString
285 facets, embed sql.NullString
286 labels pq.StringArray
287 editedAt sql.NullTime
288 communityAvatar sql.NullString
289 hotRank sql.NullFloat64
290 )
291
292 err := rows.Scan(
293 &postView.URI, &postView.CID, &postView.RKey,
294 &authorView.DID, &authorView.Handle,
295 &communityRef.DID, &communityRef.Name, &communityAvatar,
296 &title, &content, &facets, &embed, &labels,
297 &postView.CreatedAt, &editedAt, &postView.IndexedAt,
298 &postView.UpvoteCount, &postView.DownvoteCount, &postView.Score, &postView.CommentCount,
299 &hotRank,
300 )
301 if err != nil {
302 return nil, 0, err
303 }
304
305 // Build author view
306 postView.Author = &authorView
307
308 // Build community ref
309 communityRef.Avatar = nullStringPtr(communityAvatar)
310 postView.Community = &communityRef
311
312 // Set optional fields
313 postView.Title = nullStringPtr(title)
314 postView.Text = nullStringPtr(content)
315
316 // Parse facets JSON
317 if facets.Valid {
318 var facetArray []interface{}
319 if err := json.Unmarshal([]byte(facets.String), &facetArray); err == nil {
320 postView.TextFacets = facetArray
321 }
322 }
323
324 // Parse embed JSON
325 if embed.Valid {
326 var embedData interface{}
327 if err := json.Unmarshal([]byte(embed.String), &embedData); err == nil {
328 postView.Embed = embedData
329 }
330 }
331
332 // Build stats
333 postView.Stats = &posts.PostStats{
334 Upvotes: postView.UpvoteCount,
335 Downvotes: postView.DownvoteCount,
336 Score: postView.Score,
337 CommentCount: postView.CommentCount,
338 }
339
340 // Build the record (required by lexicon)
341 record := map[string]interface{}{
342 "$type": "social.coves.post.record",
343 "community": communityRef.DID,
344 "author": authorView.DID,
345 "createdAt": postView.CreatedAt.Format(time.RFC3339),
346 }
347
348 // Add optional fields to record if present
349 if title.Valid {
350 record["title"] = title.String
351 }
352 if content.Valid {
353 record["content"] = content.String
354 }
355 if facets.Valid {
356 var facetArray []interface{}
357 if err := json.Unmarshal([]byte(facets.String), &facetArray); err == nil {
358 record["facets"] = facetArray
359 }
360 }
361 if embed.Valid {
362 var embedData interface{}
363 if err := json.Unmarshal([]byte(embed.String), &embedData); err == nil {
364 record["embed"] = embedData
365 }
366 }
367 if len(labels) > 0 {
368 record["contentLabels"] = labels
369 }
370
371 postView.Record = record
372
373 // Return the computed hot_rank (0.0 if NULL for non-hot sorts)
374 hotRankValue := 0.0
375 if hotRank.Valid {
376 hotRankValue = hotRank.Float64
377 }
378
379 return &postView, hotRankValue, nil
380}