Antonio Mika
·
2026-10-11
1package prose
2
3import (
4 "fmt"
5 "math"
6 "sync"
7 "time"
8
9 "github.com/hashicorp/golang-lru/v2/expirable"
10 "github.com/jmoiron/sqlx"
11 "github.com/picosh/pico/pkg/db"
12 "github.com/picosh/pico/pkg/db/postgres"
13)
14
15// DB is the storage prose uses.
16type DB interface {
17 db.AuthDB
18 db.PostDB
19 db.TagDB
20 FindUserPostsByTag(pager *db.Pager, tag, userID, space string) (*db.Paginate[*db.Post], error)
21 FindPopularPosts() ([]*db.Post, error)
22}
23
24// PsqlDB is prose's postgres storage: the queries apps share, and the ones
25// only prose makes.
26type PsqlDB struct {
27 *postgres.PsqlDB
28
29 popularPostsCache *expirable.LRU[string, []*db.Post]
30 popularPostsMu sync.Mutex
31}
32
33var _ DB = (*PsqlDB)(nil)
34
35func NewDB(base *postgres.PsqlDB) *PsqlDB {
36 return &PsqlDB{
37 PsqlDB: base,
38 popularPostsCache: expirable.NewLRU[string, []*db.Post](16, nil, 15*time.Minute),
39 }
40}
41
42func (me *PsqlDB) ClearPopularPostsCache() {
43 if me.popularPostsCache != nil {
44 me.popularPostsCache.Purge()
45 }
46}
47
48func (me *PsqlDB) FindPopularPosts() ([]*db.Post, error) {
49 if me.popularPostsCache != nil {
50 if posts, ok := me.popularPostsCache.Get("popular_posts"); ok {
51 return posts, nil
52 }
53 }
54
55 me.popularPostsMu.Lock()
56 defer me.popularPostsMu.Unlock()
57
58 if me.popularPostsCache != nil {
59 if posts, ok := me.popularPostsCache.Get("popular_posts"); ok {
60 return posts, nil
61 }
62 }
63
64 query := `
65 WITH candidate_posts AS (
66 SELECT
67 posts.id,
68 posts.user_id,
69 posts.filename,
70 posts.slug,
71 posts.title,
72 posts.text,
73 posts.description,
74 posts.publish_at,
75 app_users.name AS username,
76 posts.updated_at,
77 posts.mime_type
78 FROM posts
79 LEFT JOIN app_users ON app_users.id = posts.user_id
80 WHERE
81 hidden = FALSE
82 AND publish_at::date <= CURRENT_DATE
83 AND publish_at >= NOW() - INTERVAL '30 days'
84 AND cur_space = 'prose'
85 AND EXISTS (
86 SELECT 1 FROM feature_flags
87 WHERE feature_flags.user_id = posts.user_id
88 AND (name = 'plus' OR name = 'prose')
89 AND expires_at > NOW()
90 )
91 ),
92 post_visitors AS (
93 SELECT post_id, COUNT(DISTINCT ip_address) AS unique_visitors
94 FROM analytics_visits
95 WHERE post_id IN (SELECT id FROM candidate_posts)
96 AND status = 200
97 AND created_at >= NOW() - INTERVAL '30 days'
98 GROUP BY post_id
99 ),
100 scored_posts AS (
101 SELECT
102 cp.*,
103 COALESCE(pv.unique_visitors, 0) AS score,
104 (
105 LOG(GREATEST(COALESCE(pv.unique_visitors, 0), 1)) +
106 (EXTRACT(EPOCH FROM cp.publish_at) - 1700000000) / 345600.0
107 ) AS rank_score,
108 ROW_NUMBER() OVER (
109 PARTITION BY cp.user_id
110 ORDER BY (
111 LOG(GREATEST(COALESCE(pv.unique_visitors, 0), 1)) +
112 (EXTRACT(EPOCH FROM cp.publish_at) - 1700000000) / 345600.0
113 ) DESC, cp.publish_at DESC
114 ) AS user_rank
115 FROM candidate_posts cp
116 LEFT JOIN post_visitors pv ON pv.post_id = cp.id
117 )
118 SELECT
119 id,
120 user_id,
121 filename,
122 slug,
123 title,
124 text,
125 description,
126 publish_at,
127 username,
128 updated_at,
129 mime_type,
130 score
131 FROM scored_posts
132 WHERE user_rank <= 3
133 ORDER BY rank_score DESC, publish_at DESC
134 LIMIT 30`
135 rs, err := me.Db.Queryx(query)
136 if err != nil {
137 return nil, err
138 }
139 defer func() { _ = rs.Close() }()
140
141 var posts []*db.Post
142 for rs.Next() {
143 post := &db.Post{}
144 err := rs.Scan(
145 &post.ID,
146 &post.UserID,
147 &post.Filename,
148 &post.Slug,
149 &post.Title,
150 &post.Text,
151 &post.Description,
152 &post.PublishAt,
153 &post.Username,
154 &post.UpdatedAt,
155 &post.MimeType,
156 &post.Score,
157 )
158 if err != nil {
159 return nil, err
160 }
161
162 posts = append(posts, post)
163 }
164 if rs.Err() != nil {
165 return nil, rs.Err()
166 }
167
168 if me.popularPostsCache != nil {
169 me.popularPostsCache.Add("popular_posts", posts)
170 }
171
172 return posts, nil
173}
174
175func (me *PsqlDB) FindUserPostsByTag(page *db.Pager, tag, userID, space string) (*db.Paginate[*db.Post], error) {
176 var posts []*db.Post
177 query := fmt.Sprintf(`
178 SELECT %s
179 FROM posts
180 LEFT JOIN app_users ON app_users.id = posts.user_id
181 LEFT JOIN post_tags ON post_tags.post_id = posts.id
182 WHERE
183 hidden = FALSE AND
184 user_id = $1 AND
185 (post_tags.name = $2 OR hidden = true) AND
186 publish_at::date <= CURRENT_DATE AND
187 cur_space = $3
188 ORDER BY publish_at DESC
189 LIMIT $4 OFFSET $5`, postgres.SelectPost)
190 err := me.Db.Select(
191 &posts,
192 query,
193 userID,
194 tag,
195 space,
196 page.Num,
197 page.Num*page.Page,
198 )
199 if err != nil {
200 return nil, err
201 }
202
203 var count int
204 err = me.Db.QueryRow(`SELECT count(id) FROM posts WHERE hidden = FALSE AND cur_space=$1`, space).Scan(&count)
205 if err != nil {
206 return nil, err
207 }
208
209 pager := &db.Paginate[*db.Post]{
210 Data: posts,
211 Total: int(math.Ceil(float64(count) / float64(page.Num))),
212 }
213 return pager, nil
214}
215
216func (me *PsqlDB) FindPostsByTag(pager *db.Pager, tag, space string) (*db.Paginate[*db.Post], error) {
217 query := `
218 SELECT
219 posts.id,
220 user_id,
221 filename,
222 slug,
223 title,
224 text,
225 description,
226 publish_at,
227 app_users.name as username,
228 posts.updated_at,
229 posts.mime_type
230 FROM posts
231 LEFT JOIN app_users ON app_users.id = posts.user_id
232 LEFT JOIN post_tags ON post_tags.post_id = posts.id
233 WHERE
234 post_tags.name = $3 AND
235 publish_at::date <= CURRENT_DATE AND
236 cur_space = $4
237 ORDER BY publish_at DESC
238 LIMIT $1 OFFSET $2`
239 rs, err := me.Db.Queryx(
240 query,
241 pager.Num,
242 pager.Num*pager.Page,
243 tag,
244 space,
245 )
246 if err != nil {
247 return nil, err
248 }
249 defer func() { _ = rs.Close() }()
250
251 return me.postPager(rs, pager.Num, space, tag)
252}
253
254func (me *PsqlDB) postPager(rs *sqlx.Rows, pageNum int, space string, tag string) (*db.Paginate[*db.Post], error) {
255 var posts []*db.Post
256 for rs.Next() {
257 post := &db.Post{}
258 err := rs.Scan(
259 &post.ID,
260 &post.UserID,
261 &post.Filename,
262 &post.Slug,
263 &post.Title,
264 &post.Text,
265 &post.Description,
266 &post.PublishAt,
267 &post.Username,
268 &post.UpdatedAt,
269 &post.MimeType,
270 )
271 if err != nil {
272 return nil, err
273 }
274
275 posts = append(posts, post)
276 }
277 if rs.Err() != nil {
278 return nil, rs.Err()
279 }
280
281 var count int
282 var err error
283 if tag == "" {
284 err = me.Db.QueryRow(`SELECT count(id) FROM posts WHERE hidden = FALSE AND cur_space=$1`, space).Scan(&count)
285 } else {
286 err = me.Db.QueryRow(`
287 SELECT count(posts.id)
288 FROM posts
289 LEFT JOIN post_tags ON post_tags.post_id = posts.id
290 WHERE hidden = FALSE AND cur_space=$1 and post_tags.name = $2`, space, tag).Scan(&count)
291 }
292 if err != nil {
293 return nil, err
294 }
295
296 pager := &db.Paginate[*db.Post]{
297 Data: posts,
298 Total: int(math.Ceil(float64(count) / float64(pageNum))),
299 }
300
301 return pager, nil
302}
303
304func (me *PsqlDB) FindPopularTags(space string) ([]string, error) {
305 tags := make([]string, 0)
306 query := `
307 SELECT name, count(post_id) as "tally"
308 FROM post_tags
309 LEFT JOIN posts ON posts.id = post_id
310 WHERE posts.cur_space = $1
311 GROUP BY name
312 ORDER BY tally DESC
313 LIMIT 5`
314 rs, err := me.Db.Queryx(query, space)
315 if err != nil {
316 return tags, err
317 }
318 defer func() { _ = rs.Close() }()
319 for rs.Next() {
320 name := ""
321 tally := 0
322 err := rs.Scan(&name, &tally)
323 if err != nil {
324 return tags, err
325 }
326
327 tags = append(tags, name)
328 }
329 if rs.Err() != nil {
330 return tags, rs.Err()
331 }
332 return tags, nil
333}