A community based topic aggregation platform built on atproto
bretton.dev
1package postgres
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7
8 "Coves/internal/core/communityFeeds"
9)
10
11type postgresFeedRepo struct {
12 *feedRepoBase
13}
14
15// sortClauses maps sort types to safe SQL ORDER BY clauses
16// This whitelist prevents SQL injection via dynamic ORDER BY construction
17var communityFeedSortClauses = map[string]string{
18 "hot": `(p.score / POWER(EXTRACT(EPOCH FROM (NOW() - p.created_at))/3600 + 2, 1.5)) DESC, p.created_at DESC, p.uri DESC`,
19 "top": `p.score DESC, p.created_at DESC, p.uri DESC`,
20 "new": `p.created_at DESC, p.uri DESC`,
21}
22
23// hotRankExpression is the SQL expression for computing the hot rank
24// NOTE: Uses NOW() which means hot_rank changes over time - this is expected behavior
25// for hot sorting (posts naturally age out). Slight time drift between cursor creation
26// and usage may cause minor reordering but won't drop posts entirely (unlike using raw score).
27const communityFeedHotRankExpression = `(p.score / POWER(EXTRACT(EPOCH FROM (NOW() - p.created_at))/3600 + 2, 1.5))`
28
29// NewCommunityFeedRepository creates a new PostgreSQL feed repository
30func NewCommunityFeedRepository(db *sql.DB, cursorSecret string) communityFeeds.Repository {
31 return &postgresFeedRepo{
32 feedRepoBase: newFeedRepoBase(db, communityFeedHotRankExpression, communityFeedSortClauses, cursorSecret),
33 }
34}
35
36// GetCommunityFeed retrieves posts from a community with sorting and pagination
37// Single query with JOINs for optimal performance
38func (r *postgresFeedRepo) GetCommunityFeed(ctx context.Context, req communityFeeds.GetCommunityFeedRequest) ([]*communityFeeds.FeedViewPost, *string, error) {
39 // Build ORDER BY clause based on sort type
40 orderBy, timeFilter := r.feedRepoBase.buildSortClause(req.Sort, req.Timeframe)
41
42 // Build cursor filter for pagination
43 // Community feed uses $3+ for cursor params (after $1=community and $2=limit)
44 cursorFilter, cursorValues, err := r.feedRepoBase.parseCursor(req.Cursor, req.Sort, 3)
45 if err != nil {
46 return nil, nil, communityFeeds.ErrInvalidCursor
47 }
48
49 // Build the main query
50 // For hot sort, we need to compute and return the hot_rank for cursor building
51 var selectClause string
52 if req.Sort == "hot" {
53 selectClause = fmt.Sprintf(`
54 SELECT
55 p.uri, p.cid, p.rkey,
56 p.author_did, u.handle as author_handle,
57 p.community_did, c.handle as community_handle, c.name as community_name, c.avatar_cid as community_avatar,
58 p.title, p.content, p.content_facets, p.embed, p.content_labels,
59 p.created_at, p.edited_at, p.indexed_at,
60 p.upvote_count, p.downvote_count, p.score, p.comment_count,
61 %s as hot_rank
62 FROM posts p`, communityFeedHotRankExpression)
63 } else {
64 selectClause = `
65 SELECT
66 p.uri, p.cid, p.rkey,
67 p.author_did, u.handle as author_handle,
68 p.community_did, c.handle as community_handle, c.name as community_name, c.avatar_cid as community_avatar,
69 p.title, p.content, p.content_facets, p.embed, p.content_labels,
70 p.created_at, p.edited_at, p.indexed_at,
71 p.upvote_count, p.downvote_count, p.score, p.comment_count,
72 NULL::numeric as hot_rank
73 FROM posts p`
74 }
75
76 query := fmt.Sprintf(`
77 %s
78 INNER JOIN users u ON p.author_did = u.did
79 INNER JOIN communities c ON p.community_did = c.did
80 WHERE p.community_did = $1
81 AND p.deleted_at IS NULL
82 %s
83 %s
84 ORDER BY %s
85 LIMIT $2
86 `, selectClause, timeFilter, cursorFilter, orderBy)
87
88 // Prepare query arguments
89 args := []interface{}{req.Community, req.Limit + 1} // +1 to check for next page
90 args = append(args, cursorValues...)
91
92 // Execute query
93 rows, err := r.db.QueryContext(ctx, query, args...)
94 if err != nil {
95 return nil, nil, fmt.Errorf("failed to query community feed: %w", err)
96 }
97 defer func() {
98 if err := rows.Close(); err != nil {
99 // Log close errors (non-fatal but worth noting)
100 fmt.Printf("Warning: failed to close rows: %v\n", err)
101 }
102 }()
103
104 // Scan results
105 var feedPosts []*communityFeeds.FeedViewPost
106 var hotRanks []float64 // Store hot ranks for cursor building
107 for rows.Next() {
108 postView, hotRank, err := r.feedRepoBase.scanFeedPost(rows)
109 if err != nil {
110 return nil, nil, fmt.Errorf("failed to scan feed post: %w", err)
111 }
112 feedPosts = append(feedPosts, &communityFeeds.FeedViewPost{Post: postView})
113 hotRanks = append(hotRanks, hotRank)
114 }
115
116 if err := rows.Err(); err != nil {
117 return nil, nil, fmt.Errorf("error iterating feed results: %w", err)
118 }
119
120 // Handle pagination cursor
121 var cursor *string
122 if len(feedPosts) > req.Limit && req.Limit > 0 {
123 feedPosts = feedPosts[:req.Limit]
124 hotRanks = hotRanks[:req.Limit]
125 lastPost := feedPosts[len(feedPosts)-1].Post
126 lastHotRank := hotRanks[len(hotRanks)-1]
127 cursorStr := r.feedRepoBase.buildCursor(lastPost, req.Sort, lastHotRank)
128 cursor = &cursorStr
129 }
130
131 return feedPosts, cursor, nil
132}