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