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