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/discover"
9)
10
11type postgresDiscoverRepo struct {
12 *feedRepoBase
13}
14
15// sortClauses maps sort types to safe SQL ORDER BY clauses
16var discoverSortClauses = 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 for discover feed
23const discoverHotRankExpression = `(p.score / POWER(EXTRACT(EPOCH FROM (NOW() - p.created_at))/3600 + 2, 1.5))`
24
25// NewDiscoverRepository creates a new PostgreSQL discover repository
26func NewDiscoverRepository(db *sql.DB, cursorSecret string) discover.Repository {
27 return &postgresDiscoverRepo{
28 feedRepoBase: newFeedRepoBase(db, discoverHotRankExpression, discoverSortClauses, cursorSecret),
29 }
30}
31
32// GetDiscover retrieves posts from ALL communities (public feed)
33func (r *postgresDiscoverRepo) GetDiscover(ctx context.Context, req discover.GetDiscoverRequest) ([]*discover.FeedViewPost, *string, error) {
34 // Build ORDER BY clause based on sort type
35 orderBy, timeFilter := r.buildSortClause(req.Sort, req.Timeframe)
36
37 // Build cursor filter for pagination
38 // Discover uses $2+ for cursor params (after $1=limit)
39 cursorFilter, cursorValues, err := r.feedRepoBase.parseCursor(req.Cursor, req.Sort, 2)
40 if err != nil {
41 return nil, nil, discover.ErrInvalidCursor
42 }
43
44 // Build the main query
45 var selectClause string
46 if req.Sort == "hot" {
47 selectClause = fmt.Sprintf(`
48 SELECT
49 p.uri, p.cid, p.rkey,
50 p.author_did, u.handle as author_handle,
51 p.community_did, c.handle as community_handle, c.name as community_name, c.avatar_cid as community_avatar,
52 p.title, p.content, p.content_facets, p.embed, p.content_labels,
53 p.created_at, p.edited_at, p.indexed_at,
54 p.upvote_count, p.downvote_count, p.score, p.comment_count,
55 %s as hot_rank
56 FROM posts p`, discoverHotRankExpression)
57 } else {
58 selectClause = `
59 SELECT
60 p.uri, p.cid, p.rkey,
61 p.author_did, u.handle as author_handle,
62 p.community_did, c.handle as community_handle, c.name as community_name, c.avatar_cid as community_avatar,
63 p.title, p.content, p.content_facets, p.embed, p.content_labels,
64 p.created_at, p.edited_at, p.indexed_at,
65 p.upvote_count, p.downvote_count, p.score, p.comment_count,
66 NULL::numeric as hot_rank
67 FROM posts p`
68 }
69
70 // No subscription filter - show ALL posts from ALL communities
71 query := fmt.Sprintf(`
72 %s
73 INNER JOIN users u ON p.author_did = u.did
74 INNER JOIN communities c ON p.community_did = c.did
75 WHERE p.deleted_at IS NULL
76 %s
77 %s
78 ORDER BY %s
79 LIMIT $1
80 `, selectClause, timeFilter, cursorFilter, orderBy)
81
82 // Prepare query arguments
83 args := []interface{}{req.Limit + 1} // +1 to check for next page
84 args = append(args, cursorValues...)
85
86 // Execute query
87 rows, err := r.db.QueryContext(ctx, query, args...)
88 if err != nil {
89 return nil, nil, fmt.Errorf("failed to query discover feed: %w", err)
90 }
91 defer func() {
92 if err := rows.Close(); err != nil {
93 fmt.Printf("Warning: failed to close rows: %v\n", err)
94 }
95 }()
96
97 // Scan results
98 var feedPosts []*discover.FeedViewPost
99 var hotRanks []float64
100 for rows.Next() {
101 postView, hotRank, err := r.feedRepoBase.scanFeedPost(rows)
102 if err != nil {
103 return nil, nil, fmt.Errorf("failed to scan discover post: %w", err)
104 }
105 feedPosts = append(feedPosts, &discover.FeedViewPost{Post: postView})
106 hotRanks = append(hotRanks, hotRank)
107 }
108
109 if err := rows.Err(); err != nil {
110 return nil, nil, fmt.Errorf("error iterating discover results: %w", err)
111 }
112
113 // Handle pagination cursor
114 var cursor *string
115 if len(feedPosts) > req.Limit && req.Limit > 0 {
116 feedPosts = feedPosts[:req.Limit]
117 hotRanks = hotRanks[:req.Limit]
118 lastPost := feedPosts[len(feedPosts)-1].Post
119 lastHotRank := hotRanks[len(hotRanks)-1]
120 cursorStr := r.feedRepoBase.buildCursor(lastPost, req.Sort, lastHotRank)
121 cursor = &cursorStr
122 }
123
124 return feedPosts, cursor, nil
125}