dashboard / erock/pico / refactor: remove unused db methods #97 rss

open · opened on 2025-12-18T04:19:09Z by erock
Help
checkout latest patchset: 
ssh pr.pico.sh print pr-97 | git am -3
checkout any patchset in a patch request: 
ssh pr.pico.sh print ps-X | git am -3
add changes to patch request: 
git format-patch main --stdout | ssh pr.pico.sh pr add 97
add review to patch request: 
git format-patch main --stdout | ssh pr.pico.sh pr add --review 97
accept PR: 
ssh pr.pico.sh pr accept 97
close PR: 
ssh pr.pico.sh pr close 97

Logs

erock created pr with ps-176 on 2025-12-18T04:19:09Z
erock added ps-177 on 2025-12-18T04:36:52Z

Patchsets

ps-176 by erock on 2025-12-18T04:19:09Z
Range Diff ↕ rd-177
1: fdc255e = 1: fdc255e refactor: remove unused db methods
2: 3f855f6 = 2: 3f855f6 chore: add tests for postgres db impl
3: 7cd29a6 = 3: 7cd29a6 refactor: use sqlx interface
4: acd449e = 4: acd449e chore: add db tags
5: f6ed0d7 = 5: f6ed0d7 refactor: use sqlx
6: 29035c6 = 6: 29035c6 refactor: replace custom sql functions with sqlx
7: d4ea6d9 = 7: d4ea6d9 refactor: inline all sql queries
-: ------- > 8: 4bd37ed refactor: use `select * from` where possible
ps-177 by erock on 2025-12-18T04:36:52Z

Patchset ps-176

refactor: remove unused db methods

Eric Bower
2025-12-18T03:03:10Z
.gitignore
+1 -0
pkg/db/db.go
+1 -14
pkg/db/postgres/storage.go
+14 -176
pkg/db/stub/stub.go
+2 -62

chore: add tests for postgres db impl

Eric Bower
2025-12-18T03:17:57Z
go.mod
+48 -12
go.sum
+101 -26
pkg/db/postgres/storage_test.go
+1472 -0

refactor: use sqlx interface

Eric Bower
2025-12-18T03:27:57Z
cmd/scripts/dates/dates.go
+2 -1
cmd/scripts/migrate/migrate.go
+2 -1
cmd/scripts/tags/tags.go
+2 -2
pkg/db/postgres/storage.go
+3 -2
pkg/db/postgres/storage_test.go
+4 -4

chore: add db tags

Eric Bower
2025-12-18T03:31:25Z
pkg/db/db.go
+68 -68

refactor: use sqlx

Eric Bower
2025-12-18T03:43:01Z
pkg/apps/pico/file_handler.go
+1 -1
pkg/db/db.go
+1 -1
pkg/db/postgres/storage.go
+61 -299
pkg/db/postgres/storage_test.go
+4 -4
pkg/db/stub/stub.go
+1 -2
pkg/tui/pubkeys.go
+1 -1

refactor: replace custom sql functions with sqlx

Eric Bower
2025-12-18T03:57:00Z
pkg/db/postgres/storage.go
+35 -96

refactor: inline all sql queries

Eric Bower
2025-12-18T04:02:50Z
pkg/db/postgres/storage.go
+181 -232
Back to top

refactor: remove unused db methods

+1 -0 .gitignore link 
1
2
3
4
5
6
7
8
9

diff --git a/.gitignore b/.gitignore
index eb84888..c09b7dc 100644
--- a/.gitignore
+++ b/.gitignore
@@ -16,3 +16,4 @@ __debug_bin
 .aider*
 ssh
 web
+.beads/
+1 -14 pkg/db/db.go link 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

diff --git a/pkg/db/db.go b/pkg/db/db.go
index daae96c..7f2b711 100644
--- a/pkg/db/db.go
+++ b/pkg/db/db.go
@@ -389,28 +389,21 @@ var DenyList = []string{
 
 type DB interface {
 	RegisterUser(name, pubkey, comment string) (*User, error)
-	RemoveUsers(userIDs []string) error
 	UpdatePublicKey(pubkeyID, name string) (*PublicKey, error)
 	InsertPublicKey(userID, pubkey, name string, tx *sql.Tx) error
-	FindPublicKeyForKey(pubkey string) (*PublicKey, error)
-	FindPublicKey(pubkeyID string) (*PublicKey, error)
 	FindKeysForUser(user *User) ([]*PublicKey, error)
 	RemoveKeys(pubkeyIDs []string) error
 
 	FindUsers() ([]*User, error)
 	FindUserByName(name string) (*User, error)
-	FindUserForNameAndKey(name string, pubkey string) (*User, error)
 	FindUserForKey(name string, pubkey string) (*User, error)
 	FindUserByPubkey(pubkey string) (*User, error)
 	FindUser(userID string) (*User, error)
-	ValidateName(name string) (bool, error)
-	SetUserName(userID string, name string) error
 
 	FindUserForToken(token string) (*User, error)
 	FindTokensForUser(userID string) ([]*Token, error)
 	InsertToken(userID, name string) (string, error)
 	UpsertToken(userID, name string) (string, error)
-	FindTokenByName(userID, name string) (string, error)
 	RemoveToken(tokenID string) error
 
 	FindPosts() ([]*Post, error)
@@ -418,13 +411,10 @@ type DB interface {
 	FindPostsForUser(pager *Pager, userID string, space string) (*Paginate[*Post], error)
 	FindAllPostsForUser(userID string, space string) ([]*Post, error)
 	FindUsersWithPost(space string) ([]*User, error)
-	FindPostsBeforeDate(date *time.Time, space string) ([]*Post, error)
 	FindExpiredPosts(space string) ([]*Post, error)
-	FindUpdatedPostsForUser(userID string, space string) ([]*Post, error)
 	FindPostWithFilename(filename string, userID string, space string) (*Post, error)
 	FindPostWithSlug(slug string, userID string, space string) (*Post, error)
 	FindPostsForFeed(pager *Pager, space string) (*Paginate[*Post], error)
-	FindAllUpdatedPosts(pager *Pager, space string) (*Paginate[*Post], error)
 	InsertPost(post *Post) (*Post, error)
 	UpdatePost(post *Post) (*Post, error)
 	RemovePosts(postIDs []string) error
@@ -433,9 +423,6 @@ type DB interface {
 	FindUserPostsByTag(pager *Pager, tag, userID, space string) (*Paginate[*Post], error)
 	FindPostsByTag(pager *Pager, tag, space string) (*Paginate[*Post], error)
 	FindPopularTags(space string) ([]string, error)
-	FindTagsForPost(postID string) ([]string, error)
-	FindTagsForUser(userID string, space string) ([]string, error)
-
 	ReplaceAliasesForPost(aliases []string, postID string) error
 
 	InsertVisit(view *AnalyticsVisits) error
@@ -447,7 +434,7 @@ type DB interface {
 	FindFeature(userID string, feature string) (*FeatureFlag, error)
 	FindFeaturesForUser(userID string) ([]*FeatureFlag, error)
 	HasFeatureForUser(userID string, feature string) bool
-	FindTotalSizeForUser(userID string) (int, error)
+
 	InsertFeature(userID, name string, expiresAt time.Time) (*FeatureFlag, error)
 	RemoveFeature(userID, names string) error
+14 -176 pkg/db/postgres/storage.go link 
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374

diff --git a/pkg/db/postgres/storage.go b/pkg/db/postgres/storage.go
index e3ae3fc..d401ff9 100644
--- a/pkg/db/postgres/storage.go
+++ b/pkg/db/postgres/storage.go
@@ -29,12 +29,6 @@ var (
 	FROM posts
 	LEFT JOIN app_users ON app_users.id = posts.user_id`, SelectPost)
 
-	sqlSelectPostsBeforeDate = fmt.Sprintf(`
-	SELECT %s
-	FROM posts
-	LEFT JOIN app_users ON app_users.id = posts.user_id
-	WHERE publish_at::date <= $1 AND cur_space = $2`, SelectPost)
-
 	sqlSelectPostWithFilename = fmt.Sprintf(`
 	SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
 	FROM posts
@@ -57,13 +51,6 @@ var (
 	LEFT JOIN app_users ON app_users.id = posts.user_id
 	WHERE posts.id = $1`, SelectPost)
 
-	sqlSelectUpdatedPostsForUser = fmt.Sprintf(`
-	SELECT %s
-	FROM posts
-	LEFT JOIN app_users ON app_users.id = posts.user_id
-	WHERE user_id = $1 AND publish_at::date <= CURRENT_DATE AND cur_space = $2
-	ORDER BY posts.updated_at DESC`, SelectPost)
-
 	sqlSelectExpiredPosts = fmt.Sprintf(`
 		SELECT %s
 		FROM posts
@@ -153,7 +140,6 @@ const (
 	sqlSelectTokenByNameForUser = `SELECT token FROM tokens WHERE user_id = $1 AND name = $2`
 
 	sqlSelectFeatureForUser = `SELECT id, user_id, payment_history_id, name, data, created_at, expires_at FROM feature_flags WHERE user_id = $1 AND name = $2 ORDER BY expires_at DESC LIMIT 1`
-	sqlSelectSizeForUser    = `SELECT COALESCE(sum(file_size), 0) FROM posts WHERE user_id = $1`
 
 	sqlSelectPostIdByAliasSlug = `SELECT post_id FROM post_aliases WHERE slug = $1`
 	sqlSelectTagPostCount      = `
@@ -161,25 +147,7 @@ const (
 	FROM posts
 	LEFT JOIN post_tags ON post_tags.post_id = posts.id
 	WHERE hidden = FALSE AND cur_space=$1 and post_tags.name = $2`
-	sqlSelectPostCount       = `SELECT count(id) FROM posts WHERE hidden = FALSE AND cur_space=$1`
-	sqlSelectAllUpdatedPosts = `
-	SELECT
-		posts.id,
-		user_id,
-		filename,
-		slug,
-		title,
-		text,
-		description,
-		publish_at,
-		app_users.name as username,
-		posts.updated_at,
-		posts.mime_type
-	FROM posts
-	LEFT JOIN app_users ON app_users.id = posts.user_id
-	WHERE hidden = FALSE AND publish_at::date <= CURRENT_DATE AND cur_space = $3
-	ORDER BY updated_at DESC
-	LIMIT $1 OFFSET $2`
+	sqlSelectPostCount = `SELECT count(id) FROM posts WHERE hidden = FALSE AND cur_space=$1`
 	// add some users to deny list since they are robogenerating a bunch of posts
 	// per day and are creating a lot of noise.
 	sqlSelectPostsByRank = `
@@ -216,13 +184,6 @@ const (
 	GROUP BY name
 	ORDER BY tally DESC
 	LIMIT 5`
-	sqlSelectTagsForUser = `
-	SELECT name
-	FROM post_tags
-	LEFT JOIN posts ON posts.id = post_id
-	WHERE posts.user_id = $1 AND posts.cur_space = $2
-	GROUP BY name`
-	sqlSelectTagsForPost     = `SELECT name FROM post_tags WHERE post_id=$1`
 	sqlSelectFeedItemsByPost = `SELECT id, post_id, guid, data, created_at FROM feed_items WHERE post_id=$1`
 
 	sqlInsertPost = `
@@ -241,13 +202,11 @@ const (
 	SET slug = $1, title = $2, text = $3, description = $4, updated_at = $5, publish_at = $6,
 		file_size = $7, shasum = $8, data = $9, hidden = $11, expires_at = $12
 	WHERE id = $10`
-	sqlUpdateUserName = `UPDATE app_users SET name = $1 WHERE id = $2`
 
 	sqlRemoveAliasesByPost = `DELETE FROM post_aliases WHERE post_id = $1`
 	sqlRemoveTagsByPost    = `DELETE FROM post_tags WHERE post_id = $1`
 	sqlRemovePosts         = `DELETE FROM posts WHERE id = ANY($1::uuid[])`
 	sqlRemoveKeys          = `DELETE FROM public_keys WHERE id = ANY($1::uuid[])`
-	sqlRemoveUsers         = `DELETE FROM app_users WHERE id = ANY($1::uuid[])`
 
 	sqlInsertProject     = `INSERT INTO projects (user_id, name, project_dir) VALUES ($1, $2, $3) RETURNING id;`
 	sqlUpdateProject     = `UPDATE projects SET updated_at = $3 WHERE user_id = $1 AND name = $2;`
@@ -348,7 +307,7 @@ func NewDB(databaseUrl string, logger *slog.Logger) *PsqlDB {
 
 func (me *PsqlDB) RegisterUser(username, pubkey, comment string) (*db.User, error) {
 	lowerName := strings.ToLower(username)
-	valid, err := me.ValidateName(lowerName)
+	valid, err := me.validateName(lowerName)
 	if !valid {
 		return nil, err
 	}
@@ -389,14 +348,8 @@ func (me *PsqlDB) RegisterUser(username, pubkey, comment string) (*db.User, erro
 	return me.FindUserForKey(username, pubkey)
 }
 
-func (me *PsqlDB) RemoveUsers(userIDs []string) error {
-	param := "{" + strings.Join(userIDs, ",") + "}"
-	_, err := me.Db.Exec(sqlRemoveUsers, param)
-	return err
-}
-
 func (me *PsqlDB) InsertPublicKey(userID, key, name string, tx *sql.Tx) error {
-	pk, _ := me.FindPublicKeyForKey(key)
+	pk, _ := me.findPublicKeyForKey(key)
 	if pk != nil {
 		return db.ErrPublicKeyTaken
 	}
@@ -415,7 +368,7 @@ func (me *PsqlDB) InsertPublicKey(userID, key, name string, tx *sql.Tx) error {
 }
 
 func (me *PsqlDB) UpdatePublicKey(pubkeyID, name string) (*db.PublicKey, error) {
-	pk, err := me.FindPublicKey(pubkeyID)
+	pk, err := me.findPublicKey(pubkeyID)
 	if err != nil {
 		return nil, err
 	}
@@ -426,14 +379,14 @@ func (me *PsqlDB) UpdatePublicKey(pubkeyID, name string) (*db.PublicKey, error)
 		return nil, err
 	}
 
-	pk, err = me.FindPublicKey(pubkeyID)
+	pk, err = me.findPublicKey(pubkeyID)
 	if err != nil {
 		return nil, err
 	}
 	return pk, nil
 }
 
-func (me *PsqlDB) FindPublicKeyForKey(key string) (*db.PublicKey, error) {
+func (me *PsqlDB) findPublicKeyForKey(key string) (*db.PublicKey, error) {
 	var keys []*db.PublicKey
 	rs, err := me.Db.Query(sqlSelectPublicKey, key)
 	if err != nil {
@@ -469,7 +422,7 @@ func (me *PsqlDB) FindPublicKeyForKey(key string) (*db.PublicKey, error) {
 	return keys[0], nil
 }
 
-func (me *PsqlDB) FindPublicKey(pubkeyID string) (*db.PublicKey, error) {
+func (me *PsqlDB) findPublicKey(pubkeyID string) (*db.PublicKey, error) {
 	var keys []*db.PublicKey
 	rs, err := me.Db.Query(`SELECT id, user_id, name, public_key, created_at FROM public_keys WHERE id = $1`, pubkeyID)
 	if err != nil {
@@ -524,28 +477,6 @@ func (me *PsqlDB) RemoveKeys(keyIDs []string) error {
 	return err
 }
 
-func (me *PsqlDB) FindPostsBeforeDate(date *time.Time, space string) ([]*db.Post, error) {
-	// now := time.Now()
-	// expired := now.AddDate(0, 0, -3)
-	var posts []*db.Post
-	rs, err := me.Db.Query(sqlSelectPostsBeforeDate, date, space)
-	if err != nil {
-		return posts, err
-	}
-	for rs.Next() {
-		post, err := CreatePostFromRow(rs)
-		if err != nil {
-			return nil, err
-		}
-
-		posts = append(posts, post)
-	}
-	if rs.Err() != nil {
-		return posts, rs.Err()
-	}
-	return posts, nil
-}
-
 func (me *PsqlDB) FindUsersWithPost(space string) ([]*db.User, error) {
 	var users []*db.User
 	rs, err := me.Db.Query(
@@ -582,7 +513,7 @@ func (me *PsqlDB) FindUsersWithPost(space string) ([]*db.User, error) {
 
 func (me *PsqlDB) FindUserForKey(username string, key string) (*db.User, error) {
 	me.Logger.Info("attempting to find user with only public key", "key", key)
-	pk, err := me.FindPublicKeyForKey(key)
+	pk, err := me.findPublicKeyForKey(key)
 	if err == nil {
 		me.Logger.Info("found pubkey, looking for user", "key", key, "userId", pk.UserID)
 		user, err := me.FindUser(pk.UserID)
@@ -595,7 +526,7 @@ func (me *PsqlDB) FindUserForKey(username string, key string) (*db.User, error)
 
 	if errors.Is(err, &db.ErrMultiplePublicKeys{}) {
 		me.Logger.Info("detected multiple users with same public key", "user", username)
-		user, err := me.FindUserForNameAndKey(username, key)
+		user, err := me.findUserForNameAndKey(username, key)
 		if err != nil {
 			me.Logger.Info("could not find user by username and public key", "user", username, "key", key)
 			// this is a little hacky but if we cannot find a user by name and public key
@@ -611,7 +542,7 @@ func (me *PsqlDB) FindUserForKey(username string, key string) (*db.User, error)
 
 func (me *PsqlDB) FindUserByPubkey(key string) (*db.User, error) {
 	me.Logger.Info("attempting to find user with only public key", "key", key)
-	pk, err := me.FindPublicKeyForKey(key)
+	pk, err := me.findPublicKeyForKey(key)
 	if err != nil {
 		return nil, err
 	}
@@ -639,7 +570,7 @@ func (me *PsqlDB) FindUser(userID string) (*db.User, error) {
 	return user, nil
 }
 
-func (me *PsqlDB) ValidateName(name string) (bool, error) {
+func (me *PsqlDB) validateName(name string) (bool, error) {
 	lower := strings.ToLower(name)
 	if slices.Contains(db.DenyList, lower) {
 		return false, fmt.Errorf("%s is on deny list: %w", lower, db.ErrNameDenied)
@@ -665,7 +596,7 @@ func (me *PsqlDB) FindUserByName(name string) (*db.User, error) {
 	return user, nil
 }
 
-func (me *PsqlDB) FindUserForNameAndKey(name string, key string) (*db.User, error) {
+func (me *PsqlDB) findUserForNameAndKey(name string, key string) (*db.User, error) {
 	user := &db.User{}
 	pk := &db.PublicKey{}
 
@@ -691,17 +622,6 @@ func (me *PsqlDB) FindUserForToken(token string) (*db.User, error) {
 	return user, nil
 }
 
-func (me *PsqlDB) SetUserName(userID string, name string) error {
-	lowerName := strings.ToLower(name)
-	valid, err := me.ValidateName(lowerName)
-	if !valid {
-		return err
-	}
-
-	_, err = me.Db.Exec(sqlUpdateUserName, lowerName, userID)
-	return err
-}
-
 func (me *PsqlDB) FindPostWithFilename(filename string, persona_id string, space string) (*db.Post, error) {
 	r := me.Db.QueryRow(sqlSelectPostWithFilename, filename, persona_id, space)
 	post, err := CreatePostWithTagsFromRow(r)
@@ -794,14 +714,6 @@ func (me *PsqlDB) FindPostsForFeed(page *db.Pager, space string) (*db.Paginate[*
 	return me.postPager(rs, page.Num, space, "")
 }
 
-func (me *PsqlDB) FindAllUpdatedPosts(page *db.Pager, space string) (*db.Paginate[*db.Post], error) {
-	rs, err := me.Db.Query(sqlSelectAllUpdatedPosts, page.Num, page.Num*page.Page, space)
-	if err != nil {
-		return nil, err
-	}
-	return me.postPager(rs, page.Num, space, "")
-}
-
 func (me *PsqlDB) InsertPost(post *db.Post) (*db.Post, error) {
 	var id string
 	err := me.Db.QueryRow(
@@ -956,26 +868,6 @@ func (me *PsqlDB) FindExpiredPosts(space string) ([]*db.Post, error) {
 	return posts, nil
 }
 
-func (me *PsqlDB) FindUpdatedPostsForUser(userID string, space string) ([]*db.Post, error) {
-	var posts []*db.Post
-	rs, err := me.Db.Query(sqlSelectUpdatedPostsForUser, userID, space)
-	if err != nil {
-		return posts, err
-	}
-	for rs.Next() {
-		post, err := CreatePostFromRow(rs)
-		if err != nil {
-			return nil, err
-		}
-
-		posts = append(posts, post)
-	}
-	if rs.Err() != nil {
-		return posts, rs.Err()
-	}
-	return posts, nil
-}
-
 func (me *PsqlDB) Close() error {
 	me.Logger.Info("Closing db")
 	return me.Db.Close()
@@ -1447,51 +1339,6 @@ func (me *PsqlDB) FindPopularTags(space string) ([]string, error) {
 	return tags, nil
 }
 
-func (me *PsqlDB) FindTagsForUser(userID string, space string) ([]string, error) {
-	tags := []string{}
-	rs, err := me.Db.Query(sqlSelectTagsForUser, userID, space)
-	if err != nil {
-		return tags, err
-	}
-	for rs.Next() {
-		name := ""
-		err := rs.Scan(&name)
-		if err != nil {
-			return tags, err
-		}
-
-		tags = append(tags, name)
-	}
-	if rs.Err() != nil {
-		return tags, rs.Err()
-	}
-	return tags, nil
-}
-
-func (me *PsqlDB) FindTagsForPost(postID string) ([]string, error) {
-	tags := make([]string, 0)
-	rs, err := me.Db.Query(sqlSelectTagsForPost, postID)
-	if err != nil {
-		return tags, err
-	}
-
-	for rs.Next() {
-		name := ""
-		err := rs.Scan(&name)
-		if err != nil {
-			return tags, err
-		}
-
-		tags = append(tags, name)
-	}
-
-	if rs.Err() != nil {
-		return tags, rs.Err()
-	}
-
-	return tags, nil
-}
-
 func (me *PsqlDB) FindFeature(userID string, feature string) (*db.FeatureFlag, error) {
 	ff := &db.FeatureFlag{}
 	// payment history is allowed to be null
@@ -1560,15 +1407,6 @@ func (me *PsqlDB) HasFeatureForUser(userID string, feature string) bool {
 	return ff.IsValid()
 }
 
-func (me *PsqlDB) FindTotalSizeForUser(userID string) (int, error) {
-	var fileSize int
-	err := me.Db.QueryRow(sqlSelectSizeForUser, userID).Scan(&fileSize)
-	if err != nil {
-		return 0, err
-	}
-	return fileSize, nil
-}
-
 func (me *PsqlDB) InsertFeedItems(postID string, items []*db.FeedItem) error {
 	ctx := context.Background()
 	tx, err := me.Db.BeginTx(ctx, nil)
@@ -1677,7 +1515,7 @@ func (me *PsqlDB) InsertToken(userID, name string) (string, error) {
 }
 
 func (me *PsqlDB) UpsertToken(userID, name string) (string, error) {
-	token, _ := me.FindTokenByName(userID, name)
+	token, _ := me.findTokenByName(userID, name)
 	if token != "" {
 		return token, nil
 	}
@@ -1686,7 +1524,7 @@ func (me *PsqlDB) UpsertToken(userID, name string) (string, error) {
 	return token, err
 }
 
-func (me *PsqlDB) FindTokenByName(userID, name string) (string, error) {
+func (me *PsqlDB) findTokenByName(userID, name string) (string, error) {
 	var token string
 	err := me.Db.QueryRow(sqlSelectTokenByNameForUser, userID, name).Scan(&token)
 	if err != nil {
+2 -62 pkg/db/stub/stub.go link 
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150

diff --git a/pkg/db/stub/stub.go b/pkg/db/stub/stub.go
index 2b81890..e25e7ee 100644
--- a/pkg/db/stub/stub.go
+++ b/pkg/db/stub/stub.go
@@ -29,24 +29,12 @@ func (me *StubDB) RegisterUser(username, pubkey, comment string) (*db.User, erro
 	return nil, errNotImpl
 }
 
-func (me *StubDB) RemoveUsers(userIDs []string) error {
-	return errNotImpl
-}
-
-func (me *StubDB) InsertPublicKey(userID, key, name string, tx *sql.Tx) error {
-	return errNotImpl
-}
-
 func (me *StubDB) UpdatePublicKey(pubkeyID, name string) (*db.PublicKey, error) {
 	return nil, errNotImpl
 }
 
-func (me *StubDB) FindPublicKeyForKey(key string) (*db.PublicKey, error) {
-	return nil, errNotImpl
-}
-
-func (me *StubDB) FindPublicKey(pubkeyID string) (*db.PublicKey, error) {
-	return nil, errNotImpl
+func (me *StubDB) InsertPublicKey(userID, key, name string, tx *sql.Tx) error {
+	return errNotImpl
 }
 
 func (me *StubDB) FindKeysForUser(user *db.User) ([]*db.PublicKey, error) {
@@ -57,10 +45,6 @@ func (me *StubDB) RemoveKeys(keyIDs []string) error {
 	return errNotImpl
 }
 
-func (me *StubDB) FindPostsBeforeDate(date *time.Time, space string) ([]*db.Post, error) {
-	return []*db.Post{}, errNotImpl
-}
-
 func (me *StubDB) FindUserForKey(username string, key string) (*db.User, error) {
 	return nil, errNotImpl
 }
@@ -73,26 +57,14 @@ func (me *StubDB) FindUser(userID string) (*db.User, error) {
 	return nil, errNotImpl
 }
 
-func (me *StubDB) ValidateName(name string) (bool, error) {
-	return false, errNotImpl
-}
-
 func (me *StubDB) FindUserByName(name string) (*db.User, error) {
 	return nil, errNotImpl
 }
 
-func (me *StubDB) FindUserForNameAndKey(name string, key string) (*db.User, error) {
-	return nil, errNotImpl
-}
-
 func (me *StubDB) FindUserForToken(token string) (*db.User, error) {
 	return nil, errNotImpl
 }
 
-func (me *StubDB) SetUserName(userID string, name string) error {
-	return errNotImpl
-}
-
 func (me *StubDB) FindPostWithFilename(filename string, persona_id string, space string) (*db.Post, error) {
 	return nil, errNotImpl
 }
@@ -109,10 +81,6 @@ func (me *StubDB) FindPostsForFeed(page *db.Pager, space string) (*db.Paginate[*
 	return &db.Paginate[*db.Post]{}, errNotImpl
 }
 
-func (me *StubDB) FindAllUpdatedPosts(page *db.Pager, space string) (*db.Paginate[*db.Post], error) {
-	return &db.Paginate[*db.Post]{}, errNotImpl
-}
-
 func (me *StubDB) InsertPost(post *db.Post) (*db.Post, error) {
 	return nil, errNotImpl
 }
@@ -141,10 +109,6 @@ func (me *StubDB) FindExpiredPosts(space string) ([]*db.Post, error) {
 	return []*db.Post{}, errNotImpl
 }
 
-func (me *StubDB) FindUpdatedPostsForUser(userID string, space string) ([]*db.Post, error) {
-	return []*db.Post{}, errNotImpl
-}
-
 func (me *StubDB) Close() error {
 	return errNotImpl
 }
@@ -185,10 +149,6 @@ func (me *StubDB) FindPopularTags(space string) ([]string, error) {
 	return []string{}, errNotImpl
 }
 
-func (me *StubDB) FindTagsForPost(postID string) ([]string, error) {
-	return []string{}, errNotImpl
-}
-
 func (me *StubDB) FindFeature(userID string, feature string) (*db.FeatureFlag, error) {
 	return nil, errNotImpl
 }
@@ -201,10 +161,6 @@ func (me *StubDB) HasFeatureForUser(userID string, feature string) bool {
 	return false
 }
 
-func (me *StubDB) FindTotalSizeForUser(userID string) (int, error) {
-	return 0, errNotImpl
-}
-
 func (me *StubDB) InsertFeedItems(postID string, items []*db.FeedItem) error {
 	return errNotImpl
 }
@@ -217,14 +173,6 @@ func (me *StubDB) UpsertProject(userID, name, projectDir string) (*db.Project, e
 	return nil, errNotImpl
 }
 
-func (me *StubDB) InsertProject(userID, name, projectDir string) (string, error) {
-	return "", errNotImpl
-}
-
-func (me *StubDB) UpdateProject(userID, name string) error {
-	return errNotImpl
-}
-
 func (me *StubDB) FindProjectByName(userID, name string) (*db.Project, error) {
 	return &db.Project{}, errNotImpl
 }
@@ -237,10 +185,6 @@ func (me *StubDB) UpsertToken(userID, name string) (string, error) {
 	return "", errNotImpl
 }
 
-func (me *StubDB) FindTokenByName(userID, name string) (string, error) {
-	return "", errNotImpl
-}
-
 func (me *StubDB) RemoveToken(tokenID string) error {
 	return errNotImpl
 }
@@ -261,10 +205,6 @@ func (me *StubDB) AddPicoPlusUser(username, email, paymentType, txId string) err
 	return errNotImpl
 }
 
-func (me *StubDB) FindTagsForUser(userID string, tag string) ([]string, error) {
-	return []string{}, errNotImpl
-}
-
 func (me *StubDB) FindUserStats(userID string) (*db.UserStats, error) {
 	return nil, errNotImpl
 }

chore: add tests for postgres db impl

+48 -12 go.mod link 
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196

diff --git a/go.mod b/go.mod
index 0545b71..6ef8cd2 100644
--- a/go.mod
+++ b/go.mod
@@ -1,8 +1,6 @@
 module github.com/picosh/pico
 
-go 1.24
-
-toolchain go1.24.0
+go 1.24.0
 
 // replace github.com/picosh/tunkit => ../tunkit
 
@@ -55,6 +53,8 @@ require (
 	github.com/prometheus/client_golang v1.22.0
 	github.com/sabhiram/go-gitignore v0.0.0-20210923224102-525f6e181f06
 	github.com/simplesurance/go-ip-anonymizer v0.0.0-20200429124537-35a880f8e87d
+	github.com/testcontainers/testcontainers-go v0.40.0
+	github.com/testcontainers/testcontainers-go/modules/postgres v0.40.0
 	github.com/x-way/crawlerdetect v0.2.28
 	github.com/yuin/goldmark v1.7.8
 	github.com/yuin/goldmark-highlighting/v2 v2.0.0-20230729083705-37449abec8cc
@@ -62,7 +62,7 @@ require (
 	go.abhg.dev/goldmark/anchor v0.2.0
 	go.abhg.dev/goldmark/hashtag v0.3.1
 	go.abhg.dev/goldmark/toc v0.11.0
-	golang.org/x/crypto v0.38.0
+	golang.org/x/crypto v0.43.0
 	google.golang.org/protobuf v1.36.6
 	gopkg.in/yaml.v2 v2.4.0
 	modernc.org/sqlite v1.36.2
@@ -71,9 +71,10 @@ require (
 require (
 	cel.dev/expr v0.22.1 // indirect
 	codeberg.org/emersion/go-scfg v0.1.0 // indirect
-	dario.cat/mergo v1.0.1 // indirect
+	dario.cat/mergo v1.0.2 // indirect
 	filippo.io/edwards25519 v1.1.0 // indirect
 	github.com/AndreasBriese/bbloom v0.0.0-20190825152654-46b345b51c96 // indirect
+	github.com/Azure/go-ansiterm v0.0.0-20210617225240-d185dfc1b5a1 // indirect
 	github.com/KimMachineGun/automemlimit v0.7.1 // indirect
 	github.com/Masterminds/goutils v1.1.1 // indirect
 	github.com/Masterminds/semver/v3 v3.3.1 // indirect
@@ -98,13 +99,19 @@ require (
 	github.com/caddyserver/certmagic v0.23.0 // indirect
 	github.com/caddyserver/zerossl v0.1.3 // indirect
 	github.com/ccoveille/go-safecast v1.6.1 // indirect
+	github.com/cenkalti/backoff/v4 v4.3.0 // indirect
 	github.com/cespare/xxhash v1.1.0 // indirect
 	github.com/cespare/xxhash/v2 v2.3.0 // indirect
 	github.com/chzyer/readline v1.5.1 // indirect
 	github.com/cloudflare/circl v1.6.0 // indirect
+	github.com/containerd/errdefs v1.0.0 // indirect
+	github.com/containerd/errdefs/pkg v0.3.0 // indirect
+	github.com/containerd/log v0.1.0 // indirect
+	github.com/containerd/platforms v0.2.1 // indirect
 	github.com/coreos/go-oidc/v3 v3.13.0 // indirect
 	github.com/coreos/go-semver v0.3.1 // indirect
 	github.com/coreos/go-systemd/v22 v22.5.0 // indirect
+	github.com/cpuguy83/dockercfg v0.3.2 // indirect
 	github.com/cpuguy83/go-md2man/v2 v2.0.6 // indirect
 	github.com/darkweak/go-esi v0.0.6 // indirect
 	github.com/darkweak/storages/badger v0.0.14 // indirect
@@ -115,6 +122,7 @@ require (
 	github.com/darkweak/storages/otter v0.0.14 // indirect
 	github.com/darkweak/storages/redis v0.0.14 // indirect
 	github.com/darkweak/storages/simplefs v0.0.14 // indirect
+	github.com/davecgh/go-spew v1.1.2-0.20180830191138-d8f796af33cc // indirect
 	github.com/delthas/go-libnp v0.1.0 // indirect
 	github.com/delthas/go-localeinfo v0.1.0 // indirect
 	github.com/dgraph-io/badger v1.6.2 // indirect
@@ -124,7 +132,11 @@ require (
 	github.com/dgryski/go-farm v0.0.0-20240924180020-3414d57e47da // indirect
 	github.com/dgryski/go-rendezvous v0.0.0-20200823014737-9f7001d12a5f // indirect
 	github.com/disintegration/imaging v1.6.2 // indirect
+	github.com/distribution/reference v0.6.0 // indirect
 	github.com/dlclark/regexp2 v1.11.5 // indirect
+	github.com/docker/docker v28.5.1+incompatible // indirect
+	github.com/docker/go-connections v0.6.0 // indirect
+	github.com/docker/go-units v0.5.0 // indirect
 	github.com/dolthub/maphash v0.1.0 // indirect
 	github.com/dsoprea/go-exif v0.0.0-20230826092837-6579e82b732d // indirect
 	github.com/dsoprea/go-exif/v2 v2.0.0-20230826092837-6579e82b732d // indirect
@@ -134,6 +146,8 @@ require (
 	github.com/dsoprea/go-png-image-structure v0.0.0-20210512210324-29b889a6093d // indirect
 	github.com/dsoprea/go-utility v0.0.0-20221003172846-a3e1774ef349 // indirect
 	github.com/dustin/go-humanize v1.0.1 // indirect
+	github.com/ebitengine/purego v0.8.4 // indirect
+	github.com/felixge/httpsnoop v1.0.4 // indirect
 	github.com/forPelevin/gomoji v1.3.0 // indirect
 	github.com/francoispqt/gojay v1.2.13 // indirect
 	github.com/gammazero/deque v1.0.0 // indirect
@@ -143,6 +157,8 @@ require (
 	github.com/go-ini/ini v1.67.0 // indirect
 	github.com/go-jose/go-jose/v3 v3.0.4 // indirect
 	github.com/go-jose/go-jose/v4 v4.0.5 // indirect
+	github.com/go-logr/logr v1.4.2 // indirect
+	github.com/go-logr/stdr v1.2.2 // indirect
 	github.com/go-ole/go-ole v1.3.0 // indirect
 	github.com/go-redis/redis/v8 v8.11.5 // indirect
 	github.com/go-sql-driver/mysql v1.9.1 // indirect
@@ -186,6 +202,7 @@ require (
 	github.com/kr/text v0.2.0 // indirect
 	github.com/libdns/libdns v1.0.0-beta.1 // indirect
 	github.com/lufia/plan9stats v0.0.0-20250317134145-8bc96cf8fc35 // indirect
+	github.com/magiconair/properties v1.8.10 // indirect
 	github.com/manifoldco/promptui v0.9.0 // indirect
 	github.com/maruel/natural v1.1.1 // indirect
 	github.com/mattn/go-colorable v0.1.14 // indirect
@@ -204,8 +221,16 @@ require (
 	github.com/mitchellh/reflectwalk v1.0.2 // indirect
 	github.com/mmcdole/goxpp v1.1.1 // indirect
 	github.com/mmcloughlin/md4 v0.1.2 // indirect
+	github.com/moby/docker-image-spec v1.3.1 // indirect
+	github.com/moby/go-archive v0.1.0 // indirect
+	github.com/moby/patternmatcher v0.6.0 // indirect
+	github.com/moby/sys/sequential v0.6.0 // indirect
+	github.com/moby/sys/user v0.4.0 // indirect
+	github.com/moby/sys/userns v0.1.0 // indirect
+	github.com/moby/term v0.5.0 // indirect
 	github.com/modern-go/concurrent v0.0.0-20180306012644-bacd9c7ef1dd // indirect
 	github.com/modern-go/reflect2 v1.0.2 // indirect
+	github.com/morikuni/aec v1.0.0 // indirect
 	github.com/mschoch/smat v0.2.0 // indirect
 	github.com/munnerz/goautoneg v0.0.0-20191010083416-a7dc8b61c822 // indirect
 	github.com/nats-io/nats.go v1.40.1 // indirect
@@ -215,10 +240,13 @@ require (
 	github.com/neurosnap/go-jpeg-image-structure v0.0.0-20221010133817-70b1c1ff679e // indirect
 	github.com/nutsdb/nutsdb v1.0.4 // indirect
 	github.com/onsi/ginkgo/v2 v2.23.3 // indirect
+	github.com/opencontainers/go-digest v1.0.0 // indirect
+	github.com/opencontainers/image-spec v1.1.1 // indirect
 	github.com/pbnjay/memory v0.0.0-20210728143218-7b4eea64cf58 // indirect
 	github.com/philhofer/fwd v1.1.3-0.20240916144458-20a13a1f6b7c // indirect
 	github.com/pierrec/lz4/v4 v4.1.22 // indirect
 	github.com/pkg/errors v0.9.1 // indirect
+	github.com/pmezard/go-difflib v1.0.1-0.20181226105442-5d4384ee4fb2 // indirect
 	github.com/power-devops/perfstat v0.0.0-20240221224432-82ca36839d55 // indirect
 	github.com/pquerna/cachecontrol v0.2.0 // indirect
 	github.com/prometheus/client_model v0.6.2 // indirect
@@ -238,9 +266,11 @@ require (
 	github.com/sean-/seed v0.0.0-20170313163322-e2103e2c3529 // indirect
 	github.com/secure-io/sio-go v0.3.1 // indirect
 	github.com/shirou/gopsutil/v3 v3.24.5 // indirect
+	github.com/shirou/gopsutil/v4 v4.25.6 // indirect
 	github.com/shoenig/go-m1cpu v0.1.6 // indirect
 	github.com/shopspring/decimal v1.4.0 // indirect
 	github.com/shurcooL/sanitized_anchor_name v1.0.0 // indirect
+	github.com/sirupsen/logrus v1.9.3 // indirect
 	github.com/slackhq/nebula v1.9.5 // indirect
 	github.com/smallstep/certificates v0.28.3 // indirect
 	github.com/smallstep/cli-utils v0.12.1 // indirect
@@ -254,6 +284,7 @@ require (
 	github.com/spf13/cobra v1.9.1 // indirect
 	github.com/spf13/pflag v1.0.6 // indirect
 	github.com/stoewer/go-strcase v1.3.0 // indirect
+	github.com/stretchr/testify v1.11.1 // indirect
 	github.com/tailscale/tscert v0.0.0-20240608151842-d3f834017e53 // indirect
 	github.com/tidwall/btree v1.7.0 // indirect
 	github.com/tidwall/gjson v1.17.0 // indirect
@@ -276,6 +307,11 @@ require (
 	go.etcd.io/etcd/client/pkg/v3 v3.5.21 // indirect
 	go.etcd.io/etcd/client/v3 v3.5.21 // indirect
 	go.opencensus.io v0.24.0 // indirect
+	go.opentelemetry.io/auto/sdk v1.1.0 // indirect
+	go.opentelemetry.io/contrib/instrumentation/net/http/otelhttp v0.59.0 // indirect
+	go.opentelemetry.io/otel v1.35.0 // indirect
+	go.opentelemetry.io/otel/metric v1.35.0 // indirect
+	go.opentelemetry.io/otel/trace v1.35.0 // indirect
 	go.step.sm/crypto v0.60.0 // indirect
 	go.uber.org/automaxprocs v1.6.0 // indirect
 	go.uber.org/mock v0.5.0 // indirect
@@ -285,15 +321,15 @@ require (
 	golang.org/x/crypto/x509roots/fallback v0.0.0-20250317152234-d0a798f77473 // indirect
 	golang.org/x/exp v0.0.0-20250305212735-054e65f0b394 // indirect
 	golang.org/x/image v0.25.0 // indirect
-	golang.org/x/mod v0.24.0 // indirect
-	golang.org/x/net v0.38.0 // indirect
+	golang.org/x/mod v0.28.0 // indirect
+	golang.org/x/net v0.45.0 // indirect
 	golang.org/x/oauth2 v0.28.0 // indirect
-	golang.org/x/sync v0.14.0 // indirect
-	golang.org/x/sys v0.33.0 // indirect
-	golang.org/x/term v0.32.0 // indirect
-	golang.org/x/text v0.25.0 // indirect
+	golang.org/x/sync v0.17.0 // indirect
+	golang.org/x/sys v0.37.0 // indirect
+	golang.org/x/term v0.36.0 // indirect
+	golang.org/x/text v0.30.0 // indirect
 	golang.org/x/time v0.11.0 // indirect
-	golang.org/x/tools v0.31.0 // indirect
+	golang.org/x/tools v0.37.0 // indirect
 	google.golang.org/genproto/googleapis/api v0.0.0-20250324211829-b45e905df463 // indirect
 	google.golang.org/genproto/googleapis/rpc v0.0.0-20250324211829-b45e905df463 // indirect
 	google.golang.org/grpc v1.71.0 // indirect
+101 -26 go.sum link 
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349

diff --git a/go.sum b/go.sum
index 3682724..05ef440 100644
--- a/go.sum
+++ b/go.sum
@@ -20,8 +20,8 @@ cloud.google.com/go/longrunning v0.6.6 h1:XJNDo5MUfMM05xK3ewpbSdmt7R2Zw+aQEMbdQR
 cloud.google.com/go/longrunning v0.6.6/go.mod h1:hyeGJUrPHcx0u2Uu1UFSoYZLn4lkMrccJig0t4FI7yw=
 codeberg.org/emersion/go-scfg v0.1.0 h1:6dnGU0ZI4gX+O5rMjwhoaySItzHG710eXL5TIQKl+uM=
 codeberg.org/emersion/go-scfg v0.1.0/go.mod h1:0nooW1ufBB4SlJEdTtiVN9Or+bnNM1icOkQ6Tbrq6O0=
-dario.cat/mergo v1.0.1 h1:Ra4+bf83h2ztPIQYNP99R6m+Y7KfnARDfID+a+vLl4s=
-dario.cat/mergo v1.0.1/go.mod h1:uNxQE+84aUszobStD9th8a29P2fMDhsBdgRYvZOxGmk=
+dario.cat/mergo v1.0.2 h1:85+piFYR1tMbRrLcDwR18y4UKJ3aH1Tbzi24VRW1TK8=
+dario.cat/mergo v1.0.2/go.mod h1:E/hbnu0NxMFBjpMIE34DRGLWqDy0g5FuKDhCb31ngxA=
 dmitri.shuralyov.com/app/changes v0.0.0-20180602232624-0a106ad413e3/go.mod h1:Yl+fi1br7+Rr3LqpNJf1/uxUdtRUV+Tnj0o93V2B9MU=
 dmitri.shuralyov.com/html/belt v0.0.0-20180602232347-f7d459c86be0/go.mod h1:JLBrvjyP0v+ecvNYvCpyZgu5/xkfAUhi6wJj28eUfSU=
 dmitri.shuralyov.com/service/change v0.0.0-20181023043359-a85b471d5412/go.mod h1:a1inKt/atXimZ4Mv927x+r7UpyzRUf4emIoiiSC2TN4=
@@ -33,8 +33,12 @@ git.sr.ht/~delthas/senpai v0.4.0 h1:G1cycL0ARqYkqOErjEx/7Cneqykq3POfCsMYnpkWhUY=
 git.sr.ht/~delthas/senpai v0.4.0/go.mod h1:mzdu4o3wANA6cYzRnrz3w+uGPHA2z3j02JDrr/M3Myc=
 git.sr.ht/~rockorager/vaxis v0.14.1-0.20250527151737-5530f9f4bcf6 h1:lmSD6ksbKNMLdhT8R2k5FQPz8ObjslX8NkCM6z1kifI=
 git.sr.ht/~rockorager/vaxis v0.14.1-0.20250527151737-5530f9f4bcf6/go.mod h1:h94aKek3frIV1hJbdXjqnBqaLkbWXvV+UxAsQHg9bns=
+github.com/AdaLogics/go-fuzz-headers v0.0.0-20240806141605-e8a1dd7889d6 h1:He8afgbRMd7mFxO99hRNu+6tazq8nFF9lIwo9JFroBk=
+github.com/AdaLogics/go-fuzz-headers v0.0.0-20240806141605-e8a1dd7889d6/go.mod h1:8o94RPi1/7XTJvwPpRSzSUedZrtlirdB3r9Z20bi2f8=
 github.com/AndreasBriese/bbloom v0.0.0-20190825152654-46b345b51c96 h1:cTp8I5+VIoKjsnZuH8vjyaysT/ses3EvZeaV/1UkF2M=
 github.com/AndreasBriese/bbloom v0.0.0-20190825152654-46b345b51c96/go.mod h1:bOvUY6CB00SOBii9/FifXqc0awNKxLFCL/+pkDPuyl8=
+github.com/Azure/go-ansiterm v0.0.0-20210617225240-d185dfc1b5a1 h1:UQHMgLO+TxOElx5B5HZ4hJQsoJ/PvUvKRhJHDQXO8P8=
+github.com/Azure/go-ansiterm v0.0.0-20210617225240-d185dfc1b5a1/go.mod h1:xomTg63KZ2rFqZQzSB4Vz2SUXa1BpHTVz9L5PTmPC4E=
 github.com/BurntSushi/toml v0.3.1/go.mod h1:xHWCNGjB5oqiDr8zfno3MHue2Ht5sIBksp03qcyfWMU=
 github.com/BurntSushi/toml v1.4.0/go.mod h1:ukJfTF/6rtPPRCnwkur4qwRxa8vTRFBF0uk2lLoLwho=
 github.com/DataDog/datadog-go v3.2.0+incompatible/go.mod h1:LButxg5PwREeZtORoXG3tL4fMGNddJ+vMq1mwgfaqoQ=
@@ -146,6 +150,8 @@ github.com/caddyserver/zerossl v0.1.3 h1:onS+pxp3M8HnHpN5MMbOMyNjmTheJyWRaZYwn+Y
 github.com/caddyserver/zerossl v0.1.3/go.mod h1:CxA0acn7oEGO6//4rtrRjYgEoa4MFw/XofZnrYwGqG4=
 github.com/ccoveille/go-safecast v1.6.1 h1:Nb9WMDR8PqhnKCVs2sCB+OqhohwO5qaXtCviZkIff5Q=
 github.com/ccoveille/go-safecast v1.6.1/go.mod h1:QqwNjxQ7DAqY0C721OIO9InMk9zCwcsO7tnRuHytad8=
+github.com/cenkalti/backoff/v4 v4.3.0 h1:MyRJ/UdXutAwSAT+s3wNd7MfTIcy71VQueUuFK343L8=
+github.com/cenkalti/backoff/v4 v4.3.0/go.mod h1:Y3VNntkOUPxTVeUxJ/G5vcM//AlwfmyYozVcomhLiZE=
 github.com/census-instrumentation/opencensus-proto v0.2.1/go.mod h1:f6KPmirojxKA12rnyqOA5BBL4O983OfeGPqjHWSTneU=
 github.com/cespare/xxhash v1.1.0 h1:a6HrQnmkObjyL+Gs60czilIUGqrzKutQD6XZog3p+ko=
 github.com/cespare/xxhash v1.1.0/go.mod h1:XrSqR1VqqWfGrhpAt58auRo0WTKS1nRRg3ghfAqPWnc=
@@ -170,6 +176,14 @@ github.com/cloudflare/circl v1.6.0/go.mod h1:uddAzsPgqdMAYatqJ0lsjX1oECcQLIlRpzZ
 github.com/cncf/udpa/go v0.0.0-20191209042840-269d4d468f6f/go.mod h1:M8M6+tZqaGXZJjfX53e64911xZQV5JYwmTeXPW+k8Sc=
 github.com/containerd/console v1.0.4 h1:F2g4+oChYvBTsASRTz8NP6iIAi97J3TtSAsLbIFn4ro=
 github.com/containerd/console v1.0.4/go.mod h1:YynlIjWYF8myEu6sdkwKIvGQq+cOckRm6So2avqoYAk=
+github.com/containerd/errdefs v1.0.0 h1:tg5yIfIlQIrxYtu9ajqY42W3lpS19XqdxRQeEwYG8PI=
+github.com/containerd/errdefs v1.0.0/go.mod h1:+YBYIdtsnF4Iw6nWZhJcqGSg/dwvV7tyJ/kCkyJ2k+M=
+github.com/containerd/errdefs/pkg v0.3.0 h1:9IKJ06FvyNlexW690DXuQNx2KA2cUJXx151Xdx3ZPPE=
+github.com/containerd/errdefs/pkg v0.3.0/go.mod h1:NJw6s9HwNuRhnjJhM7pylWwMyAkmCQvQ4GpJHEqRLVk=
+github.com/containerd/log v0.1.0 h1:TCJt7ioM2cr/tfR8GPbGf9/VRAX8D2B4PjzCpfX540I=
+github.com/containerd/log v0.1.0/go.mod h1:VRRf09a7mHDIRezVKTRCrOq78v577GXq3bSa3EhrzVo=
+github.com/containerd/platforms v0.2.1 h1:zvwtM3rz2YHPQsF2CHYM8+KtB5dvhISiXh5ZpSBQv6A=
+github.com/containerd/platforms v0.2.1/go.mod h1:XHCb+2/hzowdiut9rkudds9bE5yJ7npe7dG/wG+uFPw=
 github.com/coreos/etcd v3.3.10+incompatible/go.mod h1:uF7uidLiAD3TWHmW31ZFd/JWoc32PjwdhPthX9715RE=
 github.com/coreos/go-etcd v2.0.0+incompatible/go.mod h1:Jez6KQU2B/sWsbdaef3ED8NzMklzPG4d5KIOhIy30Tk=
 github.com/coreos/go-oidc/v3 v3.13.0 h1:M66zd0pcc5VxvBNM4pB331Wrsanby+QomQYjN8HamW8=
@@ -180,11 +194,15 @@ github.com/coreos/go-semver v0.3.1/go.mod h1:irMmmIw/7yzSRPWryHsK7EYSg09caPQL03V
 github.com/coreos/go-systemd v0.0.0-20181012123002-c6f51f82210d/go.mod h1:F5haX7vjVVG0kc13fIWeqUViNPyEJxv/OmvnBo0Yme4=
 github.com/coreos/go-systemd/v22 v22.5.0 h1:RrqgGjYQKalulkV8NGVIfkXQf6YYmOyiJKk8iXXhfZs=
 github.com/coreos/go-systemd/v22 v22.5.0/go.mod h1:Y58oyj3AT4RCenI/lSvhwexgC+NSVTIJ3seZv2GcEnc=
+github.com/cpuguy83/dockercfg v0.3.2 h1:DlJTyZGBDlXqUZ2Dk2Q3xHs/FtnooJJVaad2S9GKorA=
+github.com/cpuguy83/dockercfg v0.3.2/go.mod h1:sugsbF4//dDlL/i+S+rtpIWp+5h0BHJHfjj5/jFyUJc=
 github.com/cpuguy83/go-md2man v1.0.10/go.mod h1:SmD6nW6nTyfqj6ABTjUi3V3JVMnlJmwcJI5acqYI6dE=
 github.com/cpuguy83/go-md2man/v2 v2.0.5/go.mod h1:tgQtvFlXSQOSOSIRvRPT7W67SCa46tRHOmNcaadrF8o=
 github.com/cpuguy83/go-md2man/v2 v2.0.6 h1:XJtiaUW6dEEqVuZiMTn1ldk455QWwEIsMIJlo5vtkx0=
 github.com/cpuguy83/go-md2man/v2 v2.0.6/go.mod h1:oOW0eioCTA6cOiMLiUPZOpcVxMig6NIQQ7OS05n1F4g=
 github.com/creack/pty v1.1.9/go.mod h1:oKZEueFk5CKHvIhNR5MUki03XCEU+Q6VDXinZuGJ33E=
+github.com/creack/pty v1.1.18 h1:n56/Zwd5o6whRC5PMGretI4IdRLlmBXYNjScPaBgsbY=
+github.com/creack/pty v1.1.18/go.mod h1:MOBLtS5ELjhRRrroQr9kyvTxUAFNvYEK993ew/Vr4O4=
 github.com/darkweak/go-esi v0.0.6 h1:eVHCJfqrZwOHPfRK7JTlSYG9F8lfpX/d4lz/41RQkd8=
 github.com/darkweak/go-esi v0.0.6/go.mod h1:IJSayeQZDUh5R5ayyDC3wUEBykti12aUa0eUxZZeodk=
 github.com/darkweak/souin v1.7.7 h1:pIj/cknEsV/+xgq74VOGE1+0yy0dNnt5I6HGRczJlKk=
@@ -235,10 +253,18 @@ github.com/dgryski/go-rendezvous v0.0.0-20200823014737-9f7001d12a5f h1:lO4WD4F/r
 github.com/dgryski/go-rendezvous v0.0.0-20200823014737-9f7001d12a5f/go.mod h1:cuUVRXasLTGF7a8hSLbxyZXjz+1KgoB3wDUb6vlszIc=
 github.com/disintegration/imaging v1.6.2 h1:w1LecBlG2Lnp8B3jk5zSuNqd7b4DXhcjwek1ei82L+c=
 github.com/disintegration/imaging v1.6.2/go.mod h1:44/5580QXChDfwIclfc/PCwrr44amcmDAg8hxG0Ewe4=
+github.com/distribution/reference v0.6.0 h1:0IXCQ5g4/QMHHkarYzh5l+u8T3t73zM5QvfrDyIgxBk=
+github.com/distribution/reference v0.6.0/go.mod h1:BbU0aIcezP1/5jX/8MP0YiH4SdvB5Y4f/wlDRiLyi3E=
 github.com/dlclark/regexp2 v1.4.0/go.mod h1:2pZnwuY/m+8K6iRw6wQdMtk+rH5tNGR1i55kozfMjCc=
 github.com/dlclark/regexp2 v1.7.0/go.mod h1:DHkYz0B9wPfa6wondMfaivmHpzrQ3v9q8cnmRbL6yW8=
 github.com/dlclark/regexp2 v1.11.5 h1:Q/sSnsKerHeCkc/jSTNq1oCm7KiVgUMZRDUoRu0JQZQ=
 github.com/dlclark/regexp2 v1.11.5/go.mod h1:DHkYz0B9wPfa6wondMfaivmHpzrQ3v9q8cnmRbL6yW8=
+github.com/docker/docker v28.5.1+incompatible h1:Bm8DchhSD2J6PsFzxC35TZo4TLGR2PdW/E69rU45NhM=
+github.com/docker/docker v28.5.1+incompatible/go.mod h1:eEKB0N0r5NX/I1kEveEz05bcu8tLC/8azJZsviup8Sk=
+github.com/docker/go-connections v0.6.0 h1:LlMG9azAe1TqfR7sO+NJttz1gy6KO7VJBh+pMmjSD94=
+github.com/docker/go-connections v0.6.0/go.mod h1:AahvXYshr6JgfUJGdDCs2b5EZG/vmaMAntpSFH5BFKE=
+github.com/docker/go-units v0.5.0 h1:69rxXcBk27SvSaaxTtLh/8llcHD8vYHT7WSdRZ/jvr4=
+github.com/docker/go-units v0.5.0/go.mod h1:fgPhTUdO+D/Jk86RDLlptpiXQzgHJF7gydDDbaIK4Dk=
 github.com/dolthub/maphash v0.1.0 h1:bsQ7JsF4FkkWyrP3oCnFJgrCUAFbFf3kOl4L/QxPDyQ=
 github.com/dolthub/maphash v0.1.0/go.mod h1:gkg4Ch4CdCDu5h6PMriVLawB7koZ+5ijb9puGMV50a4=
 github.com/dsoprea/go-exif v0.0.0-20230826092837-6579e82b732d h1:ygcRCGNKuEiA98k7X35hknEN8RIRUF1jrz7k1rZCvsk=
@@ -268,6 +294,8 @@ github.com/dsoprea/go-utility/v2 v2.0.0-20200717064901-2fccff4aa15e/go.mod h1:uA
 github.com/dustin/go-humanize v1.0.0/go.mod h1:HtrtbFcZ19U5GC7JDqmcUSB87Iq5E25KnS6fMYU6eOk=
 github.com/dustin/go-humanize v1.0.1 h1:GzkhY7T5VNhEkwH0PVJgjz+fX1rhBrR7pRT3mDkpeCY=
 github.com/dustin/go-humanize v1.0.1/go.mod h1:Mu1zIs6XwVuF/gI1OepvI0qD18qycQx+mFykh5fBlto=
+github.com/ebitengine/purego v0.8.4 h1:CF7LEKg5FFOsASUj0+QwaXf8Ht6TlFxg09+S9wz0omw=
+github.com/ebitengine/purego v0.8.4/go.mod h1:iIjxzd6CiRiOG0UyXP+V1+jWqUXVjPKLAI0mRfJZTmQ=
 github.com/emersion/go-sasl v0.0.0-20241020182733-b788ff22d5a6 h1:oP4q0fw+fOSWn3DfFi4EXdT+B+gTtzx8GC9xsc26Znk=
 github.com/emersion/go-sasl v0.0.0-20241020182733-b788ff22d5a6/go.mod h1:iL2twTeMvZnrg54ZoPDNfJaJaqy0xIQFuBdrLsmspwQ=
 github.com/emersion/go-smtp v0.23.0 h1:ZiriTOTK7sKep7jbWqgB5kPsiBp5wnE5auEMnwRMnGc=
@@ -317,6 +345,7 @@ github.com/go-kit/log v0.1.0/go.mod h1:zbhenjAZHb184qTLMA9ZjW7ThYL0H2mk7Q6pNt4vb
 github.com/go-logfmt/logfmt v0.3.0/go.mod h1:Qt1PoO58o5twSAckw1HlFXLmHsOX5/0LbT9GBnD5lWE=
 github.com/go-logfmt/logfmt v0.4.0/go.mod h1:3RMwSq7FuexP4Kalkev3ejPJsZTpXXBr9+V4qmtdjCk=
 github.com/go-logfmt/logfmt v0.5.0/go.mod h1:wCYkCAKZfumFQihp8CzCvQ3paCTfi41vtzG1KdI/P7A=
+github.com/go-logr/logr v1.2.2/go.mod h1:jdQByPbusPIv2/zmleS9BjJVeZ6kBagPoEUsqbVz/1A=
 github.com/go-logr/logr v1.4.2 h1:6pFjapn8bFcIbiKo3XT4j/BhANplGihG6tvd+8rYgrY=
 github.com/go-logr/logr v1.4.2/go.mod h1:9T104GzyrTigFIr8wt5mBrctHMim0Nb2HLGrmQ40KvY=
 github.com/go-logr/stdr v1.2.2 h1:hSWxHoqTgW2S2qGc0LTAI563KZ5YKYRhT3MFKZMbjag=
@@ -437,6 +466,9 @@ github.com/gorilla/websocket v1.5.3 h1:saDtZ6Pbx/0u+bgYQ3q96pZgCzfhKXGPqt7kZ72aN
 github.com/gorilla/websocket v1.5.3/go.mod h1:YR8l580nyteQvAITg2hZ9XVh4b55+EU/adAjf1fMHhE=
 github.com/gregjones/httpcache v0.0.0-20180305231024-9cad4c3443a7/go.mod h1:FecbI9+v66THATjSRHfNgh1IVFe/9kFxbXtjV0ctIMA=
 github.com/grpc-ecosystem/grpc-gateway v1.5.0/go.mod h1:RSKVYQBd5MCa4OVpNdGskqpgL2+G+NZTnrVHpWWfpdw=
+github.com/grpc-ecosystem/grpc-gateway v1.16.0 h1:gmcG1KaJ57LophUzW0Hy8NmPhnMZb4M0+kPpLofRdBo=
+github.com/grpc-ecosystem/grpc-gateway/v2 v2.25.1 h1:VNqngBF40hVlDloBruUehVYC3ArSgIyScOAyMRqBxRg=
+github.com/grpc-ecosystem/grpc-gateway/v2 v2.25.1/go.mod h1:RBRO7fro65R6tjKzYgLAFo0t1QEXY1Dp+i/bvpRiqiQ=
 github.com/hashicorp/errwrap v1.0.0/go.mod h1:YH+1FKiLXxHSkmPseP+kNlulaMuP3n2brvKWEqk/Jc4=
 github.com/hashicorp/errwrap v1.1.0 h1:OxrOeh75EUXMY8TBjag2fzXGZ40LB6IKw45YeGUDY2I=
 github.com/hashicorp/errwrap v1.1.0/go.mod h1:YH+1FKiLXxHSkmPseP+kNlulaMuP3n2brvKWEqk/Jc4=
@@ -536,6 +568,8 @@ github.com/lufia/plan9stats v0.0.0-20250317134145-8bc96cf8fc35 h1:PpXWgLPs+Fqr32
 github.com/lufia/plan9stats v0.0.0-20250317134145-8bc96cf8fc35/go.mod h1:autxFIvghDt3jPTLoqZ9OZ7s9qTGNAWmYCjVFWPX/zg=
 github.com/lunixbochs/vtclean v1.0.0/go.mod h1:pHhQNgMf3btfWnGBVipUOjRYhoOsdGqdm/+2c2E2WMI=
 github.com/magiconair/properties v1.8.0/go.mod h1:PppfXfuXeibc/6YijjN8zIbojt8czPbwD3XqdrwzmxQ=
+github.com/magiconair/properties v1.8.10 h1:s31yESBquKXCV9a/ScB3ESkOjUYYv+X0rg8SYxI99mE=
+github.com/magiconair/properties v1.8.10/go.mod h1:Dhd985XPs7jluiymwWYZ0G4Z61jb3vdS329zhj2hYo0=
 github.com/mailru/easyjson v0.0.0-20190312143242-1de009706dbe/go.mod h1:C1wdFJiN94OJF2b5HbByQZoLdCWB1Yqtg26g4irojpc=
 github.com/manifoldco/promptui v0.9.0 h1:3V4HzJk1TtXW1MTZMP7mdlwbBpIinw3HztaIlYthEiA=
 github.com/manifoldco/promptui v0.9.0/go.mod h1:ka04sppxSGFAtxX0qhlYQjISsg9mR4GWtQEhdbn6Pgg=
@@ -561,6 +595,8 @@ github.com/matttproud/golang_protobuf_extensions v1.0.4 h1:mmDVorXM7PCGKw94cs5zk
 github.com/matttproud/golang_protobuf_extensions v1.0.4/go.mod h1:BSXmuO+STAnVfrANrmjBb36TMTDstsz7MSK+HVaYKv4=
 github.com/maypok86/otter v1.2.4 h1:HhW1Pq6VdJkmWwcZZq19BlEQkHtI8xgsQzBVXJU0nfc=
 github.com/maypok86/otter v1.2.4/go.mod h1:mKLfoI7v1HOmQMwFgX4QkRk23mX6ge3RDvjdHOWG4R4=
+github.com/mdelapenya/tlscert v0.2.0 h1:7H81W6Z/4weDvZBNOfQte5GpIMo0lGYEeWbkGp5LJHI=
+github.com/mdelapenya/tlscert v0.2.0/go.mod h1:O4njj3ELLnJjGdkN7M/vIVCpZ+Cf0L6muqOG4tLSl8o=
 github.com/mgutz/ansi v0.0.0-20200706080929-d51e80ef957d h1:5PJl274Y63IEHC+7izoQE9x6ikvDFZS2mDVS3drnohI=
 github.com/mgutz/ansi v0.0.0-20200706080929-d51e80ef957d/go.mod h1:01TrycV0kFyexm33Z7vhZRXopbI8J3TDReVlkTgMUxE=
 github.com/mholt/acmez/v3 v3.1.2 h1:auob8J/0FhmdClQicvJvuDavgd5ezwLBfKuYmynhYzc=
@@ -596,6 +632,22 @@ github.com/mmcdole/goxpp v1.1.1 h1:RGIX+D6iQRIunGHrKqnA2+700XMCnNv0bAOOv5MUhx8=
 github.com/mmcdole/goxpp v1.1.1/go.mod h1:v+25+lT2ViuQ7mVxcncQ8ch1URund48oH+jhjiwEgS8=
 github.com/mmcloughlin/md4 v0.1.2 h1:kGYl+iNbxhyz4u76ka9a+0TXP9KWt/LmnM0QhZwhcBo=
 github.com/mmcloughlin/md4 v0.1.2/go.mod h1:AAxFX59fddW0IguqNzWlf1lazh1+rXeIt/Bj49cqDTQ=
+github.com/moby/docker-image-spec v1.3.1 h1:jMKff3w6PgbfSa69GfNg+zN/XLhfXJGnEx3Nl2EsFP0=
+github.com/moby/docker-image-spec v1.3.1/go.mod h1:eKmb5VW8vQEh/BAr2yvVNvuiJuY6UIocYsFu/DxxRpo=
+github.com/moby/go-archive v0.1.0 h1:Kk/5rdW/g+H8NHdJW2gsXyZ7UnzvJNOy6VKJqueWdcQ=
+github.com/moby/go-archive v0.1.0/go.mod h1:G9B+YoujNohJmrIYFBpSd54GTUB4lt9S+xVQvsJyFuo=
+github.com/moby/patternmatcher v0.6.0 h1:GmP9lR19aU5GqSSFko+5pRqHi+Ohk1O69aFiKkVGiPk=
+github.com/moby/patternmatcher v0.6.0/go.mod h1:hDPoyOpDY7OrrMDLaYoY3hf52gNCR/YOUYxkhApJIxc=
+github.com/moby/sys/atomicwriter v0.1.0 h1:kw5D/EqkBwsBFi0ss9v1VG3wIkVhzGvLklJ+w3A14Sw=
+github.com/moby/sys/atomicwriter v0.1.0/go.mod h1:Ul8oqv2ZMNHOceF643P6FKPXeCmYtlQMvpizfsSoaWs=
+github.com/moby/sys/sequential v0.6.0 h1:qrx7XFUd/5DxtqcoH1h438hF5TmOvzC/lspjy7zgvCU=
+github.com/moby/sys/sequential v0.6.0/go.mod h1:uyv8EUTrca5PnDsdMGXhZe6CCe8U/UiTWd+lL+7b/Ko=
+github.com/moby/sys/user v0.4.0 h1:jhcMKit7SA80hivmFJcbB1vqmw//wU61Zdui2eQXuMs=
+github.com/moby/sys/user v0.4.0/go.mod h1:bG+tYYYJgaMtRKgEmuueC0hJEAZWwtIbZTB+85uoHjs=
+github.com/moby/sys/userns v0.1.0 h1:tVLXkFOxVu9A64/yh59slHVv9ahO9UIev4JZusOLG/g=
+github.com/moby/sys/userns v0.1.0/go.mod h1:IHUYgu/kao6N8YZlp9Cf444ySSvCmDlmzUcYfDHOl28=
+github.com/moby/term v0.5.0 h1:xt8Q1nalod/v7BqbG21f8mQPqH+xAaC9C3N3wfWbVP0=
+github.com/moby/term v0.5.0/go.mod h1:8FzsFHVUBGZdbDsJw/ot+X+d5HLUbvklYLJ9uGfcI3Y=
 github.com/modern-go/concurrent v0.0.0-20180228061459-e0a39a4cb421/go.mod h1:6dJC0mAP4ikYIbvyc7fijjWJddQyLn8Ig3JB5CqoB9Q=
 github.com/modern-go/concurrent v0.0.0-20180306012644-bacd9c7ef1dd h1:TRLaZ9cD/w8PVh93nsPXa1VrQ6jlwL5oN8l14QlcNfg=
 github.com/modern-go/concurrent v0.0.0-20180306012644-bacd9c7ef1dd/go.mod h1:6dJC0mAP4ikYIbvyc7fijjWJddQyLn8Ig3JB5CqoB9Q=
@@ -603,6 +655,8 @@ github.com/modern-go/reflect2 v0.0.0-20180701023420-4b7aa43c6742/go.mod h1:bx2lN
 github.com/modern-go/reflect2 v1.0.1/go.mod h1:bx2lNnkwVCuqBIxFjflWJWanXIb3RllmbCylyMrvgv0=
 github.com/modern-go/reflect2 v1.0.2 h1:xBagoLtFs94CBntxluKeaWgTMpvLxC4ur3nMaC9Gz0M=
 github.com/modern-go/reflect2 v1.0.2/go.mod h1:yWuevngMOJpCy52FWWMvUC8ws7m/LJsjYzDa0/r8luk=
+github.com/morikuni/aec v1.0.0 h1:nP9CBfwrvYnBRgY6qfDQkygYDmYwOilePFkwzv4dU8A=
+github.com/morikuni/aec v1.0.0/go.mod h1:BbKIizmSmc5MMPqRYbxO4ZU0S0+P200+tUnFx7PXmsc=
 github.com/mschoch/smat v0.2.0 h1:8imxQsjDm8yFEAVBe7azKmKSgzSkZXDuKkSq9374khM=
 github.com/mschoch/smat v0.2.0/go.mod h1:kc9mz7DoBKqDyiRL7VZN8KvXQMWeTaVnttLRXOlotKw=
 github.com/munnerz/goautoneg v0.0.0-20191010083416-a7dc8b61c822 h1:C3w9PqII01/Oq1c1nUAm88MOHcQC9l5mIlSMApZMrHA=
@@ -642,6 +696,10 @@ github.com/onsi/gomega v1.17.0/go.mod h1:HnhC7FXeEQY45zxNK3PPoIUhzk/80Xly9PcubAl
 github.com/onsi/gomega v1.18.1/go.mod h1:0q+aL8jAiMXy9hbwj2mr5GziHiwhAIQpFmmtT5hitRs=
 github.com/onsi/gomega v1.36.2 h1:koNYke6TVk6ZmnyHrCXba/T/MoLBXFjeC1PtvYgw0A8=
 github.com/onsi/gomega v1.36.2/go.mod h1:DdwyADRjrc825LhMEkD76cHR5+pUnjhUN8GlHlRPHzY=
+github.com/opencontainers/go-digest v1.0.0 h1:apOUWs51W5PlhuyGyz9FCeeBIOUDA/6nW8Oi/yOhh5U=
+github.com/opencontainers/go-digest v1.0.0/go.mod h1:0JzlMkj0TRzQZfJkVvzbP0HBR3IKzErnv2BNG4W4MAM=
+github.com/opencontainers/image-spec v1.1.1 h1:y0fUlFfIZhPF1W537XOLg0/fcx6zcHCJwooC2xJA040=
+github.com/opencontainers/image-spec v1.1.1/go.mod h1:qpqAh3Dmcf36wStyyWU+kCeDgrGnAve2nCC8+7h8Q0M=
 github.com/openzipkin/zipkin-go v0.1.1/go.mod h1:NtoC/o8u3JlF1lSlyPNswIbeQH9bJTmOf0Erfk+hxe8=
 github.com/pascaldekloe/goe v0.0.0-20180627143212-57f6aae5913c/go.mod h1:lzWF7FIEvWOWxwDKqyGYQf6ZUaNfKdP144TG7ZOy1lc=
 github.com/pascaldekloe/goe v0.1.0 h1:cBOtyMzM9HTpWjXfbbunk26uA6nG3a8n06Wieeh0MwY=
@@ -746,6 +804,8 @@ github.com/secure-io/sio-go v0.3.1/go.mod h1:+xbkjDzPjwh4Axd07pRKSNriS9SCiYksWnZ
 github.com/sergi/go-diff v1.0.0/go.mod h1:0CfEIISq7TuYL3j771MWULgwwjU+GofnZX9QAmXWZgo=
 github.com/shirou/gopsutil/v3 v3.24.5 h1:i0t8kL+kQTvpAYToeuiVk3TgDeKOFioZO3Ztz/iZ9pI=
 github.com/shirou/gopsutil/v3 v3.24.5/go.mod h1:bsoOS1aStSs9ErQ1WWfxllSeS1K5D+U30r2NfcubMVk=
+github.com/shirou/gopsutil/v4 v4.25.6 h1:kLysI2JsKorfaFPcYmcJqbzROzsBWEOAtw6A7dIfqXs=
+github.com/shirou/gopsutil/v4 v4.25.6/go.mod h1:PfybzyydfZcN+JMMjkF6Zb8Mq1A/VcogFFg7hj50W9c=
 github.com/shoenig/go-m1cpu v0.1.6 h1:nxdKQNcEB6vzgA2E2bvzKIYRuNj7XNJ4S/aRSwKzFtM=
 github.com/shoenig/go-m1cpu v0.1.6/go.mod h1:1JJMcUBvfNwpq05QDQVAnx3gUHr9IYF7GNg9SUEw2VQ=
 github.com/shoenig/test v0.6.4 h1:kVTaSd7WLz5WZ2IaoM0RSzRsUD+m8wRR+5qvntpn4LU=
@@ -829,6 +889,7 @@ github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+
 github.com/stretchr/objx v0.1.1/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME=
 github.com/stretchr/objx v0.4.0/go.mod h1:YvHI0jy2hoMjB+UWwv71VJQ9isScKT/TqJzVSSt89Yw=
 github.com/stretchr/objx v0.5.0/go.mod h1:Yh+to48EsGEfYuaHDzXPcE3xhTkx73EhmCGUpEOglKo=
+github.com/stretchr/objx v0.5.2 h1:xuMeJ0Sdp5ZMRXx/aWO6RZxdr3beISkG5/G/aIRr3pY=
 github.com/stretchr/objx v0.5.2/go.mod h1:FRsXN1f5AsAjCGJKqEizvkpNtU+EGNCLh3NxZ/8L+MA=
 github.com/stretchr/testify v1.2.2/go.mod h1:a8OnRcib4nhh0OaRAV+Yts87kKdq0PP7pXfy6kDkUVs=
 github.com/stretchr/testify v1.3.0/go.mod h1:M5WIy9Dh21IEIfnGCwXGc5bZfKNJtfHm1UVUgZn+9EI=
@@ -841,11 +902,15 @@ github.com/stretchr/testify v1.8.0/go.mod h1:yNjHg4UonilssWZ8iaSj1OCr/vHnekPRkoO
 github.com/stretchr/testify v1.8.1/go.mod h1:w2LPCIKwWwSfY2zedu0+kehJoqGctiVI29o6fzry7u4=
 github.com/stretchr/testify v1.8.4/go.mod h1:sz/lmYIOXD/1dqDmKjjqLyZ2RngseejIcXlSw2iwfAo=
 github.com/stretchr/testify v1.9.0/go.mod h1:r2ic/lqez/lEtzL7wO/rwa5dbSLXVDPFyf8C91i36aY=
-github.com/stretchr/testify v1.10.0 h1:Xv5erBjTwe/5IxqUQTdXv5kgmIvbHo3QQyRwhJsOfJA=
-github.com/stretchr/testify v1.10.0/go.mod h1:r2ic/lqez/lEtzL7wO/rwa5dbSLXVDPFyf8C91i36aY=
+github.com/stretchr/testify v1.11.1 h1:7s2iGBzp5EwR7/aIZr8ao5+dra3wiQyKjjFuvgVKu7U=
+github.com/stretchr/testify v1.11.1/go.mod h1:wZwfW3scLgRK+23gO65QZefKpKQRnfz6sD981Nm4B6U=
 github.com/tailscale/tscert v0.0.0-20240608151842-d3f834017e53 h1:uxMgm0C+EjytfAqyfBG55ZONKQ7mvd7x4YYCWsf8QHQ=
 github.com/tailscale/tscert v0.0.0-20240608151842-d3f834017e53/go.mod h1:kNGUQ3VESx3VZwRwA9MSCUegIl6+saPL8Noq82ozCaU=
 github.com/tarm/serial v0.0.0-20180830185346-98f6abe2eb07/go.mod h1:kDXzergiv9cbyO7IOYJZWg1U88JhDg3PB6klq9Hg2pA=
+github.com/testcontainers/testcontainers-go v0.40.0 h1:pSdJYLOVgLE8YdUY2FHQ1Fxu+aMnb6JfVz1mxk7OeMU=
+github.com/testcontainers/testcontainers-go v0.40.0/go.mod h1:FSXV5KQtX2HAMlm7U3APNyLkkap35zNLxukw9oBi/MY=
+github.com/testcontainers/testcontainers-go/modules/postgres v0.40.0 h1:s2bIayFXlbDFexo96y+htn7FzuhpXLYJNnIuglNKqOk=
+github.com/testcontainers/testcontainers-go/modules/postgres v0.40.0/go.mod h1:h+u/2KoREGTnTl9UwrQ/g+XhasAT8E6dClclAADeXoQ=
 github.com/tidwall/btree v1.1.0/go.mod h1:TzIRzen6yHbibdSfK6t8QimqbUnoxUSrZfeW7Uob0q4=
 github.com/tidwall/btree v1.7.0 h1:L1fkJH/AuEh5zBnnBbmTwQ5Lt+bRJ5A8EWecslvo9iI=
 github.com/tidwall/btree v1.7.0/go.mod h1:twD9XRA5jj9VUQGELzDO4HPQTNJsoWWfYEL+EUQ2cKY=
@@ -928,16 +993,22 @@ go.opentelemetry.io/contrib/instrumentation/google.golang.org/grpc/otelgrpc v0.5
 go.opentelemetry.io/contrib/instrumentation/google.golang.org/grpc/otelgrpc v0.59.0/go.mod h1:ijPqXp5P6IRRByFVVg9DY8P5HkxkHE5ARIa+86aXPf4=
 go.opentelemetry.io/contrib/instrumentation/net/http/otelhttp v0.59.0 h1:CV7UdSGJt/Ao6Gp4CXckLxVRRsRgDHoI8XjbL3PDl8s=
 go.opentelemetry.io/contrib/instrumentation/net/http/otelhttp v0.59.0/go.mod h1:FRmFuRJfag1IZ2dPkHnEoSFVgTVPUd2qf5Vi69hLb8I=
-go.opentelemetry.io/otel v1.34.0 h1:zRLXxLCgL1WyKsPVrgbSdMN4c0FMkDAskSTQP+0hdUY=
-go.opentelemetry.io/otel v1.34.0/go.mod h1:OWFPOQ+h4G8xpyjgqo4SxJYdDQ/qmRH+wivy7zzx9oI=
-go.opentelemetry.io/otel/metric v1.34.0 h1:+eTR3U0MyfWjRDhmFMxe2SsW64QrZ84AOhvqS7Y+PoQ=
-go.opentelemetry.io/otel/metric v1.34.0/go.mod h1:CEDrp0fy2D0MvkXE+dPV7cMi8tWZwX3dmaIhwPOaqHE=
+go.opentelemetry.io/otel v1.35.0 h1:xKWKPxrxB6OtMCbmMY021CqC45J+3Onta9MqjhnusiQ=
+go.opentelemetry.io/otel v1.35.0/go.mod h1:UEqy8Zp11hpkUrL73gSlELM0DupHoiq72dR+Zqel/+Y=
+go.opentelemetry.io/otel/exporters/otlp/otlptrace v1.34.0 h1:OeNbIYk/2C15ckl7glBlOBp5+WlYsOElzTNmiPW/x60=
+go.opentelemetry.io/otel/exporters/otlp/otlptrace v1.34.0/go.mod h1:7Bept48yIeqxP2OZ9/AqIpYS94h2or0aB4FypJTc8ZM=
+go.opentelemetry.io/otel/exporters/otlp/otlptrace/otlptracehttp v1.34.0 h1:BEj3SPM81McUZHYjRS5pEgNgnmzGJ5tRpU5krWnV8Bs=
+go.opentelemetry.io/otel/exporters/otlp/otlptrace/otlptracehttp v1.34.0/go.mod h1:9cKLGBDzI/F3NoHLQGm4ZrYdIHsvGt6ej6hUowxY0J4=
+go.opentelemetry.io/otel/metric v1.35.0 h1:0znxYu2SNyuMSQT4Y9WDWej0VpcsxkuklLa4/siN90M=
+go.opentelemetry.io/otel/metric v1.35.0/go.mod h1:nKVFgxBZ2fReX6IlyW28MgZojkoAkJGaE8CpgeAU3oE=
 go.opentelemetry.io/otel/sdk v1.34.0 h1:95zS4k/2GOy069d321O8jWgYsW3MzVV+KuSPKp7Wr1A=
 go.opentelemetry.io/otel/sdk v1.34.0/go.mod h1:0e/pNiaMAqaykJGKbi+tSjWfNNHMTxoC9qANsCzbyxU=
 go.opentelemetry.io/otel/sdk/metric v1.34.0 h1:5CeK9ujjbFVL5c1PhLuStg1wxA7vQv7ce1EK0Gyvahk=
 go.opentelemetry.io/otel/sdk/metric v1.34.0/go.mod h1:jQ/r8Ze28zRKoNRdkjCZxfs6YvBTG1+YIqyFVFYec5w=
-go.opentelemetry.io/otel/trace v1.34.0 h1:+ouXS2V8Rd4hp4580a8q23bg0azF2nI8cqLYnC8mh/k=
-go.opentelemetry.io/otel/trace v1.34.0/go.mod h1:Svm7lSjQD7kG7KJ/MUHPVXSDGz2OX4h0M2jHBhmSfRE=
+go.opentelemetry.io/otel/trace v1.35.0 h1:dPpEfJu1sDIqruz7BHFG3c7528f6ddfSWfFDVt/xgMs=
+go.opentelemetry.io/otel/trace v1.35.0/go.mod h1:WUk7DtFp1Aw2MkvqGdwiXYDZZNvA/1J8o6xRXLrIkyc=
+go.opentelemetry.io/proto/otlp v1.5.0 h1:xJvq7gMzB31/d406fB8U5CBdyQGw4P399D1aQWU/3i4=
+go.opentelemetry.io/proto/otlp v1.5.0/go.mod h1:keN8WnHxOy8PG0rQZjJJ5A2ebUoafqWp0eVQ4yIXvJ4=
 go.step.sm/crypto v0.60.0 h1:UgSw8DFG5xUOGB3GUID17UA32G4j1iNQ4qoMhBmsVFw=
 go.step.sm/crypto v0.60.0/go.mod h1:Ep83Lv818L4gV0vhFTdPWRKnL6/5fRMpi8SaoP5ArSw=
 go.uber.org/automaxprocs v1.6.0 h1:O3y2/QNTOdbF+e/dpXNNW7Rx2hZ4sTIPyybbxyNqTUs=
@@ -970,8 +1041,8 @@ golang.org/x/crypto v0.23.0/go.mod h1:CKFgDieR+mRhux2Lsu27y0fO304Db0wZe70UKqHu0v
 golang.org/x/crypto v0.27.0/go.mod h1:1Xngt8kV6Dvbssa53Ziq6Eqn0HqbZi5Z6R0ZpwQzt70=
 golang.org/x/crypto v0.31.0/go.mod h1:kDsLvtWBEx7MV9tJOj9bnXsPbxwJQ6csT/x4KIN4Ssk=
 golang.org/x/crypto v0.33.0/go.mod h1:bVdXmD7IV/4GdElGPozy6U7lWdRXA4qyRVGJV57uQ5M=
-golang.org/x/crypto v0.38.0 h1:jt+WWG8IZlBnVbomuhg2Mdq0+BBQaHbtqHEFEigjUV8=
-golang.org/x/crypto v0.38.0/go.mod h1:MvrbAqul58NNYPKnOra203SB9vpuZW0e+RRZV+Ggqjw=
+golang.org/x/crypto v0.43.0 h1:dduJYIi3A3KOfdGOHX8AVZ/jGiyPa3IbBozJ5kNuE04=
+golang.org/x/crypto v0.43.0/go.mod h1:BFbav4mRNlXJL4wNeejLpWxB7wMbc79PdRGhWKncxR0=
 golang.org/x/crypto/x509roots/fallback v0.0.0-20250317152234-d0a798f77473 h1:XxwA7BHJuoBTkv1tIM7d/8rDnl9i9M0M5anwcJWZtbA=
 golang.org/x/crypto/x509roots/fallback v0.0.0-20250317152234-d0a798f77473/go.mod h1:lxN5T34bK4Z/i6cMaU7frUU57VkDXFD4Kamfl/cp9oU=
 golang.org/x/exp v0.0.0-20190121172915-509febef88a4/go.mod h1:CJ0aWSM057203Lf6IL+f9T1iT9GByDxfZKAQTCR3kQA=
@@ -992,8 +1063,8 @@ golang.org/x/mod v0.8.0/go.mod h1:iBbtSCu2XBx23ZKBPSOrRkjjQPZFPuis4dIYUhu/chs=
 golang.org/x/mod v0.12.0/go.mod h1:iBbtSCu2XBx23ZKBPSOrRkjjQPZFPuis4dIYUhu/chs=
 golang.org/x/mod v0.15.0/go.mod h1:hTbmBsO62+eylJbnUtE2MGJUyE7QWk4xUqPFrRgJ+7c=
 golang.org/x/mod v0.17.0/go.mod h1:hTbmBsO62+eylJbnUtE2MGJUyE7QWk4xUqPFrRgJ+7c=
-golang.org/x/mod v0.24.0 h1:ZfthKaKaT4NrhGVZHO1/WDTwGES4De8KtWO0SIbNJMU=
-golang.org/x/mod v0.24.0/go.mod h1:IXM97Txy2VM4PJ3gI61r1YEk/gAj6zAHN3AdZt6S9Ww=
+golang.org/x/mod v0.28.0 h1:gQBtGhjxykdjY9YhZpSlZIsbnaE2+PgjfLWUQTnoZ1U=
+golang.org/x/mod v0.28.0/go.mod h1:yfB/L0NOf/kmEbXjzCPOx1iK1fRutOydrCMsqRhEBxI=
 golang.org/x/net v0.0.0-20180724234803-3673e40ba225/go.mod h1:mL1N/T3taQHkDXs73rZJwtUhF3w3ftmwwsq0BUmARs4=
 golang.org/x/net v0.0.0-20180826012351-8a410e7b638d/go.mod h1:mL1N/T3taQHkDXs73rZJwtUhF3w3ftmwwsq0BUmARs4=
 golang.org/x/net v0.0.0-20180906233101-161cd47e91fd/go.mod h1:mL1N/T3taQHkDXs73rZJwtUhF3w3ftmwwsq0BUmARs4=
@@ -1030,8 +1101,8 @@ golang.org/x/net v0.21.0/go.mod h1:bIjVDfnllIU7BJ2DNgfnXvpSvtn8VRwhlsaeUTyUS44=
 golang.org/x/net v0.25.0/go.mod h1:JkAGAh7GEvH74S6FOH42FLoXpXbE/aqXSrIQjXgsiwM=
 golang.org/x/net v0.29.0/go.mod h1:gLkgy8jTGERgjzMic6DS9+SP0ajcu6Xu3Orq/SpETg0=
 golang.org/x/net v0.33.0/go.mod h1:HXLR5J+9DxmrqMwG9qjGCxZ+zKXxBru04zlTvWlWuN4=
-golang.org/x/net v0.38.0 h1:vRMAPTMaeGqVhG5QyLJHqNDwecKTomGeqbnfZyKlBI8=
-golang.org/x/net v0.38.0/go.mod h1:ivrbrMbzFq5J41QOQh0siUuly180yBYtLp+CKbEaFx8=
+golang.org/x/net v0.45.0 h1:RLBg5JKixCy82FtLJpeNlVM0nrSqpCRYzVU1n8kj0tM=
+golang.org/x/net v0.45.0/go.mod h1:ECOoLqd5U3Lhyeyo/QDCEVQ4sNgYsqvCZ722XogGieY=
 golang.org/x/oauth2 v0.0.0-20180821212333-d2e6202438be/go.mod h1:N/0e6XlmueqKjAGxoOufVs8QHGRruUQn6yWY3a++T0U=
 golang.org/x/oauth2 v0.0.0-20181017192945-9dcd33a902f4/go.mod h1:N/0e6XlmueqKjAGxoOufVs8QHGRruUQn6yWY3a++T0U=
 golang.org/x/oauth2 v0.0.0-20181203162652-d668ce993890/go.mod h1:N/0e6XlmueqKjAGxoOufVs8QHGRruUQn6yWY3a++T0U=
@@ -1056,8 +1127,8 @@ golang.org/x/sync v0.7.0/go.mod h1:Czt+wKu1gCyEFDUtn0jG5QVvpJ6rzVqr5aXyt9drQfk=
 golang.org/x/sync v0.8.0/go.mod h1:Czt+wKu1gCyEFDUtn0jG5QVvpJ6rzVqr5aXyt9drQfk=
 golang.org/x/sync v0.10.0/go.mod h1:Czt+wKu1gCyEFDUtn0jG5QVvpJ6rzVqr5aXyt9drQfk=
 golang.org/x/sync v0.11.0/go.mod h1:Czt+wKu1gCyEFDUtn0jG5QVvpJ6rzVqr5aXyt9drQfk=
-golang.org/x/sync v0.14.0 h1:woo0S4Yywslg6hp4eUFjTVOyKt0RookbpAHG4c1HmhQ=
-golang.org/x/sync v0.14.0/go.mod h1:1dzgHSNfp02xaA81J2MS99Qcpr2w7fw1gpm99rleRqA=
+golang.org/x/sync v0.17.0 h1:l60nONMj9l5drqw6jlhIELNv9I0A4OFgRsG9k2oT9Ug=
+golang.org/x/sync v0.17.0/go.mod h1:9KTHXmSnoGruLpwFjVSX0lNNA75CykiMECbovNTZqGI=
 golang.org/x/sys v0.0.0-20180823144017-11551d06cbcc/go.mod h1:STP8DvDyc/dI5b8T5hshtkjS+E42TnysNCUPdjciGhY=
 golang.org/x/sys v0.0.0-20180830151530-49385e6e1522/go.mod h1:STP8DvDyc/dI5b8T5hshtkjS+E42TnysNCUPdjciGhY=
 golang.org/x/sys v0.0.0-20180905080454-ebe1bf3edb33/go.mod h1:STP8DvDyc/dI5b8T5hshtkjS+E42TnysNCUPdjciGhY=
@@ -1097,10 +1168,12 @@ golang.org/x/sys v0.0.0-20210423082822-04245dca01da/go.mod h1:h1NjWce9XRLGQEsW7w
 golang.org/x/sys v0.0.0-20210510120138-977fb7262007/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=
 golang.org/x/sys v0.0.0-20210603081109-ebe580a85c40/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=
 golang.org/x/sys v0.0.0-20210615035016-665e8c7367d1/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=
+golang.org/x/sys v0.0.0-20210616094352-59db8d763f22/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=
 golang.org/x/sys v0.0.0-20210630005230-0f9fa26af87c/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=
 golang.org/x/sys v0.0.0-20211216021012-1d35b9e2eb4e/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=
 golang.org/x/sys v0.0.0-20220310020820-b874c991c1a5/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=
 golang.org/x/sys v0.0.0-20220520151302-bc2c85ada10a/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=
+golang.org/x/sys v0.0.0-20220715151400-c0bba94af5f8/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=
 golang.org/x/sys v0.0.0-20220722155257-8c9f86f7a55f/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=
 golang.org/x/sys v0.0.0-20220728004956-3c1f35247d10/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=
 golang.org/x/sys v0.0.0-20221010170243-090e33056c14/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=
@@ -1115,8 +1188,8 @@ golang.org/x/sys v0.25.0/go.mod h1:/VUhepiaJMQUp4+oa/7Zr1D23ma6VTLIYjOOTFZPUcA=
 golang.org/x/sys v0.28.0/go.mod h1:/VUhepiaJMQUp4+oa/7Zr1D23ma6VTLIYjOOTFZPUcA=
 golang.org/x/sys v0.29.0/go.mod h1:/VUhepiaJMQUp4+oa/7Zr1D23ma6VTLIYjOOTFZPUcA=
 golang.org/x/sys v0.30.0/go.mod h1:/VUhepiaJMQUp4+oa/7Zr1D23ma6VTLIYjOOTFZPUcA=
-golang.org/x/sys v0.33.0 h1:q3i8TbbEz+JRD9ywIRlyRAQbM0qF7hu24q3teo2hbuw=
-golang.org/x/sys v0.33.0/go.mod h1:BJP2sWEmIv4KK5OTEluFJCKSidICx8ciO85XgH3Ak8k=
+golang.org/x/sys v0.37.0 h1:fdNQudmxPjkdUTPnLn5mdQv7Zwvbvpaxqs831goi9kQ=
+golang.org/x/sys v0.37.0/go.mod h1:OgkHotnGiDImocRcuBABYBEXf8A9a87e/uXjp9XT3ks=
 golang.org/x/telemetry v0.0.0-20240228155512-f48c80bd79b2/go.mod h1:TeRTkGYfJXctD9OcfyVLyj2J3IxLnKwHJR8f4D8a3YE=
 golang.org/x/term v0.0.0-20201126162022-7de9c90e9dd1/go.mod h1:bj7SfCRtBDWHUb9snDiAeCFNEtKQo2Wmx5Cou7ajbmo=
 golang.org/x/term v0.0.0-20210927222741-03fcf44c2211/go.mod h1:jbD1KX2456YbFQfuXm/mYQcufACuNUgVhRMnK/tPxf8=
@@ -1128,8 +1201,8 @@ golang.org/x/term v0.20.0/go.mod h1:8UkIAJTvZgivsXaD6/pH6U9ecQzZ45awqEOzuCvwpFY=
 golang.org/x/term v0.24.0/go.mod h1:lOBK/LVxemqiMij05LGJ0tzNr8xlmwBRJ81PX6wVLH8=
 golang.org/x/term v0.27.0/go.mod h1:iMsnZpn0cago0GOrHO2+Y7u7JPn5AylBrcoWkElMTSM=
 golang.org/x/term v0.29.0/go.mod h1:6bl4lRlvVuDgSf3179VpIxBF0o10JUpXWOnI7nErv7s=
-golang.org/x/term v0.32.0 h1:DR4lr0TjUs3epypdhTOkMmuF5CDFJ/8pOnbzMZPQ7bg=
-golang.org/x/term v0.32.0/go.mod h1:uZG1FhGx848Sqfsq4/DlJr3xGGsYMu/L5GW4abiaEPQ=
+golang.org/x/term v0.36.0 h1:zMPR+aF8gfksFprF/Nc/rd1wRS1EI6nDBGyWAvDzx2Q=
+golang.org/x/term v0.36.0/go.mod h1:Qu394IJq6V6dCBRgwqshf3mPF85AqzYEzofzRdZkWss=
 golang.org/x/text v0.3.0/go.mod h1:NqM8EUOU14njkJ3fqMW+pc6Ldnwhi/IjpwHt7yyuwOQ=
 golang.org/x/text v0.3.1-0.20180807135948-17ff2d5776d2/go.mod h1:NqM8EUOU14njkJ3fqMW+pc6Ldnwhi/IjpwHt7yyuwOQ=
 golang.org/x/text v0.3.2/go.mod h1:bEr9sfX3Q8Zfm5fL9x+3itogRgK3+ptLWKqgva+5dAk=
@@ -1144,8 +1217,8 @@ golang.org/x/text v0.15.0/go.mod h1:18ZOQIKpY8NJVqYksKHtTdi31H5itFRjB5/qKTNYzSU=
 golang.org/x/text v0.18.0/go.mod h1:BuEKDfySbSR4drPmRPG/7iBdf8hvFMuRexcpahXilzY=
 golang.org/x/text v0.21.0/go.mod h1:4IBbMaMmOPCJ8SecivzSH54+73PCFmPWxNTLm+vZkEQ=
 golang.org/x/text v0.22.0/go.mod h1:YRoo4H8PVmsu+E3Ou7cqLVH8oXWIHVoX0jqUWALQhfY=
-golang.org/x/text v0.25.0 h1:qVyWApTSYLk/drJRO5mDlNYskwQznZmkpV2c8q9zls4=
-golang.org/x/text v0.25.0/go.mod h1:WEdwpYrmk1qmdHvhkSTNPm3app7v4rsT8F2UD6+VHIA=
+golang.org/x/text v0.30.0 h1:yznKA/E9zq54KzlzBEAWn1NXSQ8DIp/NYMy88xJjl4k=
+golang.org/x/text v0.30.0/go.mod h1:yDdHFIX9t+tORqspjENWgzaCVXgk0yYnYuSZ8UzzBVM=
 golang.org/x/time v0.0.0-20180412165947-fbb02b2291d2/go.mod h1:tRJNPiyCQ0inRvYxbN9jk5I+vvW/OXSQhTDSoE431IQ=
 golang.org/x/time v0.0.0-20181108054448-85acf8d2951c/go.mod h1:tRJNPiyCQ0inRvYxbN9jk5I+vvW/OXSQhTDSoE431IQ=
 golang.org/x/time v0.11.0 h1:/bpjEDfN9tkoN/ryeYHnv5hcMlc8ncjMcM4XBk5NWV0=
@@ -1167,8 +1240,8 @@ golang.org/x/tools v0.1.12/go.mod h1:hNGJHUnrk76NpqgfD5Aqm5Crs+Hm0VOH/i9J2+nxYbc
 golang.org/x/tools v0.6.0/go.mod h1:Xwgl3UAJ/d3gWutnCtw505GrjyAbvKui8lOU390QaIU=
 golang.org/x/tools v0.13.0/go.mod h1:HvlwmtVNQAhOuCjW7xxvovg8wbNq7LwfXh/k7wXUl58=
 golang.org/x/tools v0.21.1-0.20240508182429-e35e4ccd0d2d/go.mod h1:aiJjzUbINMkxbQROHiO6hDPo2LHcIPhhQsa9DLh0yGk=
-golang.org/x/tools v0.31.0 h1:0EedkvKDbh+qistFTd0Bcwe/YLh4vHwWEkiI0toFIBU=
-golang.org/x/tools v0.31.0/go.mod h1:naFTU+Cev749tSJRXJlna0T3WxKvb1kWEx15xA4SdmQ=
+golang.org/x/tools v0.37.0 h1:DVSRzp7FwePZW356yEAChSdNcQo6Nsp+fex1SUW09lE=
+golang.org/x/tools v0.37.0/go.mod h1:MBN5QPQtLMHVdvsbtarmTNukZDdgwdwlO5qGacAzF0w=
 golang.org/x/xerrors v0.0.0-20190717185122-a985d3407aa7/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
 golang.org/x/xerrors v0.0.0-20191011141410-1b5146add898/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
 golang.org/x/xerrors v0.0.0-20191204190536-9bdfabe68543/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
@@ -1243,6 +1316,8 @@ gopkg.in/yaml.v2 v2.4.0/go.mod h1:RDklbk79AGWmwhnvt/jBztapEOGDOx6ZbXqjP6csGnQ=
 gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=
 gopkg.in/yaml.v3 v3.0.1 h1:fxVm/GzAzEWqLHuvctI91KS9hhNmmWOoWu0XTYJS7CA=
 gopkg.in/yaml.v3 v3.0.1/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=
+gotest.tools/v3 v3.5.2 h1:7koQfIKdy+I8UTetycgUqXWSDwpgv193Ka+qRsmBY8Q=
+gotest.tools/v3 v3.5.2/go.mod h1:LtdLGcnqToBH83WByAAi/wiwSFCArdFIUV/xxN4pcjA=
 grpc.go4.org v0.0.0-20170609214715-11d0a25b4919/go.mod h1:77eQGdRu53HpSqPFJFmuJdjuHRquDANNeA4x7B8WQ9o=
 honnef.co/go/tools v0.0.0-20180728063816-88497007e858/go.mod h1:rf3lG4BRIbNafJWhAfAdb/ePZxsR/4RtNHQocxwk9r4=
 honnef.co/go/tools v0.0.0-20190102054323-c2f93a96b099/go.mod h1:rf3lG4BRIbNafJWhAfAdb/ePZxsR/4RtNHQocxwk9r4=
+1472 -0 pkg/db/postgres/storage_test.go link 
   1
   2
   3
   4
   5
   6
   7
   8
   9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
  40
  41
  42
  43
  44
  45
  46
  47
  48
  49
  50
  51
  52
  53
  54
  55
  56
  57
  58
  59
  60
  61
  62
  63
  64
  65
  66
  67
  68
  69
  70
  71
  72
  73
  74
  75
  76
  77
  78
  79
  80
  81
  82
  83
  84
  85
  86
  87
  88
  89
  90
  91
  92
  93
  94
  95
  96
  97
  98
  99
 100
 101
 102
 103
 104
 105
 106
 107
 108
 109
 110
 111
 112
 113
 114
 115
 116
 117
 118
 119
 120
 121
 122
 123
 124
 125
 126
 127
 128
 129
 130
 131
 132
 133
 134
 135
 136
 137
 138
 139
 140
 141
 142
 143
 144
 145
 146
 147
 148
 149
 150
 151
 152
 153
 154
 155
 156
 157
 158
 159
 160
 161
 162
 163
 164
 165
 166
 167
 168
 169
 170
 171
 172
 173
 174
 175
 176
 177
 178
 179
 180
 181
 182
 183
 184
 185
 186
 187
 188
 189
 190
 191
 192
 193
 194
 195
 196
 197
 198
 199
 200
 201
 202
 203
 204
 205
 206
 207
 208
 209
 210
 211
 212
 213
 214
 215
 216
 217
 218
 219
 220
 221
 222
 223
 224
 225
 226
 227
 228
 229
 230
 231
 232
 233
 234
 235
 236
 237
 238
 239
 240
 241
 242
 243
 244
 245
 246
 247
 248
 249
 250
 251
 252
 253
 254
 255
 256
 257
 258
 259
 260
 261
 262
 263
 264
 265
 266
 267
 268
 269
 270
 271
 272
 273
 274
 275
 276
 277
 278
 279
 280
 281
 282
 283
 284
 285
 286
 287
 288
 289
 290
 291
 292
 293
 294
 295
 296
 297
 298
 299
 300
 301
 302
 303
 304
 305
 306
 307
 308
 309
 310
 311
 312
 313
 314
 315
 316
 317
 318
 319
 320
 321
 322
 323
 324
 325
 326
 327
 328
 329
 330
 331
 332
 333
 334
 335
 336
 337
 338
 339
 340
 341
 342
 343
 344
 345
 346
 347
 348
 349
 350
 351
 352
 353
 354
 355
 356
 357
 358
 359
 360
 361
 362
 363
 364
 365
 366
 367
 368
 369
 370
 371
 372
 373
 374
 375
 376
 377
 378
 379
 380
 381
 382
 383
 384
 385
 386
 387
 388
 389
 390
 391
 392
 393
 394
 395
 396
 397
 398
 399
 400
 401
 402
 403
 404
 405
 406
 407
 408
 409
 410
 411
 412
 413
 414
 415
 416
 417
 418
 419
 420
 421
 422
 423
 424
 425
 426
 427
 428
 429
 430
 431
 432
 433
 434
 435
 436
 437
 438
 439
 440
 441
 442
 443
 444
 445
 446
 447
 448
 449
 450
 451
 452
 453
 454
 455
 456
 457
 458
 459
 460
 461
 462
 463
 464
 465
 466
 467
 468
 469
 470
 471
 472
 473
 474
 475
 476
 477
 478
 479
 480
 481
 482
 483
 484
 485
 486
 487
 488
 489
 490
 491
 492
 493
 494
 495
 496
 497
 498
 499
 500
 501
 502
 503
 504
 505
 506
 507
 508
 509
 510
 511
 512
 513
 514
 515
 516
 517
 518
 519
 520
 521
 522
 523
 524
 525
 526
 527
 528
 529
 530
 531
 532
 533
 534
 535
 536
 537
 538
 539
 540
 541
 542
 543
 544
 545
 546
 547
 548
 549
 550
 551
 552
 553
 554
 555
 556
 557
 558
 559
 560
 561
 562
 563
 564
 565
 566
 567
 568
 569
 570
 571
 572
 573
 574
 575
 576
 577
 578
 579
 580
 581
 582
 583
 584
 585
 586
 587
 588
 589
 590
 591
 592
 593
 594
 595
 596
 597
 598
 599
 600
 601
 602
 603
 604
 605
 606
 607
 608
 609
 610
 611
 612
 613
 614
 615
 616
 617
 618
 619
 620
 621
 622
 623
 624
 625
 626
 627
 628
 629
 630
 631
 632
 633
 634
 635
 636
 637
 638
 639
 640
 641
 642
 643
 644
 645
 646
 647
 648
 649
 650
 651
 652
 653
 654
 655
 656
 657
 658
 659
 660
 661
 662
 663
 664
 665
 666
 667
 668
 669
 670
 671
 672
 673
 674
 675
 676
 677
 678
 679
 680
 681
 682
 683
 684
 685
 686
 687
 688
 689
 690
 691
 692
 693
 694
 695
 696
 697
 698
 699
 700
 701
 702
 703
 704
 705
 706
 707
 708
 709
 710
 711
 712
 713
 714
 715
 716
 717
 718
 719
 720
 721
 722
 723
 724
 725
 726
 727
 728
 729
 730
 731
 732
 733
 734
 735
 736
 737
 738
 739
 740
 741
 742
 743
 744
 745
 746
 747
 748
 749
 750
 751
 752
 753
 754
 755
 756
 757
 758
 759
 760
 761
 762
 763
 764
 765
 766
 767
 768
 769
 770
 771
 772
 773
 774
 775
 776
 777
 778
 779
 780
 781
 782
 783
 784
 785
 786
 787
 788
 789
 790
 791
 792
 793
 794
 795
 796
 797
 798
 799
 800
 801
 802
 803
 804
 805
 806
 807
 808
 809
 810
 811
 812
 813
 814
 815
 816
 817
 818
 819
 820
 821
 822
 823
 824
 825
 826
 827
 828
 829
 830
 831
 832
 833
 834
 835
 836
 837
 838
 839
 840
 841
 842
 843
 844
 845
 846
 847
 848
 849
 850
 851
 852
 853
 854
 855
 856
 857
 858
 859
 860
 861
 862
 863
 864
 865
 866
 867
 868
 869
 870
 871
 872
 873
 874
 875
 876
 877
 878
 879
 880
 881
 882
 883
 884
 885
 886
 887
 888
 889
 890
 891
 892
 893
 894
 895
 896
 897
 898
 899
 900
 901
 902
 903
 904
 905
 906
 907
 908
 909
 910
 911
 912
 913
 914
 915
 916
 917
 918
 919
 920
 921
 922
 923
 924
 925
 926
 927
 928
 929
 930
 931
 932
 933
 934
 935
 936
 937
 938
 939
 940
 941
 942
 943
 944
 945
 946
 947
 948
 949
 950
 951
 952
 953
 954
 955
 956
 957
 958
 959
 960
 961
 962
 963
 964
 965
 966
 967
 968
 969
 970
 971
 972
 973
 974
 975
 976
 977
 978
 979
 980
 981
 982
 983
 984
 985
 986
 987
 988
 989
 990
 991
 992
 993
 994
 995
 996
 997
 998
 999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478

diff --git a/pkg/db/postgres/storage_test.go b/pkg/db/postgres/storage_test.go
new file mode 100644
index 0000000..6a5fd40
--- /dev/null
+++ b/pkg/db/postgres/storage_test.go
@@ -0,0 +1,1472 @@
+package postgres
+
+import (
+	"context"
+	"database/sql"
+	"fmt"
+	"log/slog"
+	"os"
+	"os/exec"
+	"path/filepath"
+	"runtime"
+	"sort"
+	"testing"
+	"time"
+
+	_ "github.com/lib/pq"
+	"github.com/picosh/pico/pkg/db"
+	"github.com/testcontainers/testcontainers-go"
+	"github.com/testcontainers/testcontainers-go/modules/postgres"
+	"github.com/testcontainers/testcontainers-go/wait"
+)
+
+var testDB *PsqlDB
+var testLogger *slog.Logger
+var skipTests bool
+
+func setupContainerRuntime() bool {
+	// Check if DATABASE_URL is set for external postgres (CI/CD or manual testing)
+	if os.Getenv("TEST_DATABASE_URL") != "" {
+		return true
+	}
+
+	// Try podman first
+	if cmd := exec.Command("podman", "info"); cmd.Run() == nil {
+		// For podman, we need to ensure the socket is running
+		// User should run: systemctl --user start podman.socket
+		os.Setenv("TESTCONTAINERS_RYUK_DISABLED", "true")
+
+		// Check if socket exists and is accessible
+		xdgRuntime := os.Getenv("XDG_RUNTIME_DIR")
+		if xdgRuntime != "" {
+			socketPath := xdgRuntime + "/podman/podman.sock"
+			if _, err := os.Stat(socketPath); err == nil {
+				os.Setenv("DOCKER_HOST", "unix://"+socketPath)
+				return true
+			}
+		}
+		// Socket not available, need to start it
+		fmt.Println("Podman detected but socket not running. Run: systemctl --user start podman.socket")
+		return false
+	}
+
+	// Try docker
+	if cmd := exec.Command("docker", "info"); cmd.Run() == nil {
+		return true
+	}
+
+	return false
+}
+
+func TestMain(m *testing.M) {
+	ctx := context.Background()
+	testLogger = slog.New(slog.NewTextHandler(os.Stdout, &slog.HandlerOptions{Level: slog.LevelError}))
+
+	// Check for external database URL first (for CI/CD or manual testing)
+	if dbURL := os.Getenv("TEST_DATABASE_URL"); dbURL != "" {
+		testDB = NewDB(dbURL, testLogger)
+		if err := setupTestSchema(testDB.Db); err != nil {
+			panic(fmt.Sprintf("failed to setup schema: %s", err))
+		}
+		code := m.Run()
+		_ = testDB.Close()
+		os.Exit(code)
+	}
+
+	if !setupContainerRuntime() {
+		fmt.Println("Container runtime not available, skipping integration tests")
+		fmt.Println("To run tests, either:")
+		fmt.Println("  - Set TEST_DATABASE_URL to a postgres connection string")
+		fmt.Println("  - Start podman socket: systemctl --user start podman.socket")
+		fmt.Println("  - Start docker daemon")
+		skipTests = true
+		os.Exit(0)
+	}
+
+	pgContainer, err := postgres.Run(ctx,
+		"postgres:14",
+		postgres.WithDatabase("pico_test"),
+		postgres.WithUsername("postgres"),
+		postgres.WithPassword("postgres"),
+		testcontainers.WithWaitStrategy(
+			wait.ForLog("database system is ready to accept connections").
+				WithOccurrence(2).
+				WithStartupTimeout(30*time.Second)),
+	)
+	if err != nil {
+		fmt.Printf("Failed to start postgres container (Docker may not be running): %s\n", err)
+		skipTests = true
+		os.Exit(0)
+	}
+
+	connStr, err := pgContainer.ConnectionString(ctx, "sslmode=disable")
+	if err != nil {
+		panic(fmt.Sprintf("failed to get connection string: %s", err))
+	}
+
+	testDB = NewDB(connStr, testLogger)
+
+	if err := setupTestSchema(testDB.Db); err != nil {
+		panic(fmt.Sprintf("failed to setup schema: %s", err))
+	}
+
+	code := m.Run()
+
+	_ = testDB.Close()
+	_ = pgContainer.Terminate(ctx)
+
+	os.Exit(code)
+}
+
+func getProjectRoot() string {
+	_, filename, _, ok := runtime.Caller(0)
+	if !ok {
+		panic("failed to get current file path")
+	}
+	// storage_test.go is in pkg/db/postgres/, so go up 4 levels to get project root
+	return filepath.Join(filepath.Dir(filename), "..", "..", "..")
+}
+
+func setupTestSchema(db *sql.DB) error {
+	projectRoot := getProjectRoot()
+	migrationsDir := filepath.Join(projectRoot, "sql", "migrations")
+
+	// Read all migration files
+	entries, err := os.ReadDir(migrationsDir)
+	if err != nil {
+		return fmt.Errorf("failed to read migrations directory: %w", err)
+	}
+
+	// Sort by filename (they're date-prefixed, so alphabetical order is correct)
+	var migrationFiles []string
+	for _, entry := range entries {
+		if !entry.IsDir() && filepath.Ext(entry.Name()) == ".sql" {
+			migrationFiles = append(migrationFiles, entry.Name())
+		}
+	}
+	sort.Strings(migrationFiles)
+
+	// Execute each migration in order
+	for _, filename := range migrationFiles {
+		migrationPath := filepath.Join(migrationsDir, filename)
+		content, err := os.ReadFile(migrationPath)
+		if err != nil {
+			return fmt.Errorf("failed to read migration %s: %w", filename, err)
+		}
+
+		_, err = db.Exec(string(content))
+		if err != nil {
+			return fmt.Errorf("failed to execute migration %s: %w", filename, err)
+		}
+	}
+
+	return nil
+}
+
+func cleanupTestData(t *testing.T) {
+	t.Helper()
+	tables := []string{
+		"access_logs", "tuns_event_logs", "analytics_visits",
+		"feed_items", "post_aliases", "post_tags", "posts",
+		"projects", "feature_flags", "payment_history", "tokens",
+		"public_keys", "app_users",
+	}
+	for _, table := range tables {
+		_, err := testDB.Db.Exec(fmt.Sprintf("DELETE FROM %s", table))
+		if err != nil {
+			t.Fatalf("failed to clean up %s: %v", table, err)
+		}
+	}
+}
+
+func mustInsertPost(t *testing.T, post *db.Post) *db.Post {
+	t.Helper()
+	now := time.Now()
+	if post.UpdatedAt == nil {
+		post.UpdatedAt = &now
+	}
+	if post.PublishAt == nil {
+		post.PublishAt = &now
+	}
+	created, err := testDB.InsertPost(post)
+	if err != nil {
+		t.Fatalf("InsertPost failed: %v", err)
+	}
+	return created
+}
+
+// ============ User Management Tests ============
+
+func TestRegisterUser_Success(t *testing.T) {
+	cleanupTestData(t)
+
+	user, err := testDB.RegisterUser("testuser", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI testkey", "test comment")
+	if err != nil {
+		t.Fatalf("RegisterUser failed: %v", err)
+	}
+	if user == nil {
+		t.Fatal("expected user, got nil")
+	}
+	if user.Name != "testuser" {
+		t.Errorf("expected name 'testuser', got '%s'", user.Name)
+	}
+	if user.PublicKey == nil {
+		t.Fatal("expected public key, got nil")
+	}
+}
+
+func TestRegisterUser_DuplicateName(t *testing.T) {
+	cleanupTestData(t)
+
+	_, err := testDB.RegisterUser("duplicateuser", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI key1", "comment1")
+	if err != nil {
+		t.Fatalf("first RegisterUser failed: %v", err)
+	}
+
+	_, err = testDB.RegisterUser("duplicateuser", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI key2", "comment2")
+	if err == nil {
+		t.Error("expected error for duplicate name, got nil")
+	}
+}
+
+func TestRegisterUser_DeniedName(t *testing.T) {
+	cleanupTestData(t)
+
+	_, err := testDB.RegisterUser("admin", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI adminkey", "comment")
+	if err == nil {
+		t.Error("expected error for denied name 'admin', got nil")
+	}
+}
+
+func TestRegisterUser_InvalidName(t *testing.T) {
+	cleanupTestData(t)
+
+	_, err := testDB.RegisterUser("user@invalid", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI invalidkey", "comment")
+	if err == nil {
+		t.Error("expected error for invalid name, got nil")
+	}
+}
+
+func TestFindUser(t *testing.T) {
+	cleanupTestData(t)
+
+	created, err := testDB.RegisterUser("findme", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findmekey", "comment")
+	if err != nil {
+		t.Fatalf("RegisterUser failed: %v", err)
+	}
+
+	found, err := testDB.FindUser(created.ID)
+	if err != nil {
+		t.Fatalf("FindUser failed: %v", err)
+	}
+	if found.Name != "findme" {
+		t.Errorf("expected name 'findme', got '%s'", found.Name)
+	}
+}
+
+func TestFindUserByName(t *testing.T) {
+	cleanupTestData(t)
+
+	_, err := testDB.RegisterUser("nameduser", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI namedkey", "comment")
+	if err != nil {
+		t.Fatalf("RegisterUser failed: %v", err)
+	}
+
+	found, err := testDB.FindUserByName("nameduser")
+	if err != nil {
+		t.Fatalf("FindUserByName failed: %v", err)
+	}
+	if found.Name != "nameduser" {
+		t.Errorf("expected name 'nameduser', got '%s'", found.Name)
+	}
+}
+
+func TestFindUserByPubkey(t *testing.T) {
+	cleanupTestData(t)
+
+	pubkey := "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI pubkeyuser"
+	_, err := testDB.RegisterUser("pubkeyuser", pubkey, "comment")
+	if err != nil {
+		t.Fatalf("RegisterUser failed: %v", err)
+	}
+
+	found, err := testDB.FindUserByPubkey(pubkey)
+	if err != nil {
+		t.Fatalf("FindUserByPubkey failed: %v", err)
+	}
+	if found.Name != "pubkeyuser" {
+		t.Errorf("expected name 'pubkeyuser', got '%s'", found.Name)
+	}
+}
+
+func TestFindUserForKey(t *testing.T) {
+	cleanupTestData(t)
+
+	pubkey := "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI forkeyfind"
+	_, err := testDB.RegisterUser("forkeyfind", pubkey, "comment")
+	if err != nil {
+		t.Fatalf("RegisterUser failed: %v", err)
+	}
+
+	found, err := testDB.FindUserForKey("forkeyfind", pubkey)
+	if err != nil {
+		t.Fatalf("FindUserForKey failed: %v", err)
+	}
+	if found.Name != "forkeyfind" {
+		t.Errorf("expected name 'forkeyfind', got '%s'", found.Name)
+	}
+}
+
+func TestFindUsers(t *testing.T) {
+	cleanupTestData(t)
+
+	_, _ = testDB.RegisterUser("user1", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI user1key", "comment")
+	_, _ = testDB.RegisterUser("user2", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI user2key", "comment")
+
+	users, err := testDB.FindUsers()
+	if err != nil {
+		t.Fatalf("FindUsers failed: %v", err)
+	}
+	if len(users) != 2 {
+		t.Errorf("expected 2 users, got %d", len(users))
+	}
+}
+
+// ============ Public Key Management Tests ============
+
+func TestInsertPublicKey_Success(t *testing.T) {
+	cleanupTestData(t)
+
+	user, err := testDB.RegisterUser("keyowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI keyowner1", "comment")
+	if err != nil {
+		t.Fatalf("RegisterUser failed: %v", err)
+	}
+
+	err = testDB.InsertPublicKey(user.ID, "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI secondkey", "second key", nil)
+	if err != nil {
+		t.Fatalf("InsertPublicKey failed: %v", err)
+	}
+
+	keys, err := testDB.FindKeysForUser(user)
+	if err != nil {
+		t.Fatalf("FindKeysForUser failed: %v", err)
+	}
+	if len(keys) != 2 {
+		t.Errorf("expected 2 keys, got %d", len(keys))
+	}
+}
+
+func TestInsertPublicKey_Duplicate(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("dupkeyowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI dupkey", "comment")
+
+	err := testDB.InsertPublicKey(user.ID, "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI dupkey", "same key", nil)
+	if err == nil {
+		t.Error("expected error for duplicate key, got nil")
+	}
+}
+
+func TestUpdatePublicKey(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("updatekeyowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI updatekeyowner", "original")
+
+	updated, err := testDB.UpdatePublicKey(user.PublicKey.ID, "new-name")
+	if err != nil {
+		t.Fatalf("UpdatePublicKey failed: %v", err)
+	}
+	if updated.Name != "new-name" {
+		t.Errorf("expected name 'new-name', got '%s'", updated.Name)
+	}
+}
+
+func TestFindKeysForUser(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("multikeyowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI multikeyowner1", "key1")
+	_ = testDB.InsertPublicKey(user.ID, "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI multikeyowner2", "key2", nil)
+
+	keys, err := testDB.FindKeysForUser(user)
+	if err != nil {
+		t.Fatalf("FindKeysForUser failed: %v", err)
+	}
+	if len(keys) != 2 {
+		t.Errorf("expected 2 keys, got %d", len(keys))
+	}
+}
+
+func TestRemoveKeys(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("removekeyowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI removekeyowner", "key1")
+	_ = testDB.InsertPublicKey(user.ID, "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI removekeyowner2", "key2", nil)
+
+	keys, _ := testDB.FindKeysForUser(user)
+	if len(keys) != 2 {
+		t.Fatalf("expected 2 keys before removal, got %d", len(keys))
+	}
+
+	err := testDB.RemoveKeys([]string{keys[1].ID})
+	if err != nil {
+		t.Fatalf("RemoveKeys failed: %v", err)
+	}
+
+	keys, _ = testDB.FindKeysForUser(user)
+	if len(keys) != 1 {
+		t.Errorf("expected 1 key after removal, got %d", len(keys))
+	}
+}
+
+// ============ Token Management Tests ============
+
+func TestInsertToken(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("tokenowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI tokenowner", "comment")
+
+	token, err := testDB.InsertToken(user.ID, "my-token")
+	if err != nil {
+		t.Fatalf("InsertToken failed: %v", err)
+	}
+	if token == "" {
+		t.Error("expected token string, got empty")
+	}
+}
+
+func TestUpsertToken(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("upserttokenowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI upserttokenowner", "comment")
+
+	token1, err := testDB.UpsertToken(user.ID, "upsert-token")
+	if err != nil {
+		t.Fatalf("first UpsertToken failed: %v", err)
+	}
+
+	token2, err := testDB.UpsertToken(user.ID, "upsert-token")
+	if err != nil {
+		t.Fatalf("second UpsertToken failed: %v", err)
+	}
+
+	if token1 != token2 {
+		t.Errorf("expected same token, got different: %s vs %s", token1, token2)
+	}
+}
+
+func TestFindTokensForUser(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("tokensowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI tokensowner", "comment")
+	_, _ = testDB.InsertToken(user.ID, "token1")
+	_, _ = testDB.InsertToken(user.ID, "token2")
+
+	tokens, err := testDB.FindTokensForUser(user.ID)
+	if err != nil {
+		t.Fatalf("FindTokensForUser failed: %v", err)
+	}
+	if len(tokens) != 2 {
+		t.Errorf("expected 2 tokens, got %d", len(tokens))
+	}
+}
+
+func TestFindUserForToken_Valid(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("validtokenowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI validtokenowner", "comment")
+	token, _ := testDB.InsertToken(user.ID, "valid-token")
+
+	found, err := testDB.FindUserForToken(token)
+	if err != nil {
+		t.Fatalf("FindUserForToken failed: %v", err)
+	}
+	if found.Name != "validtokenowner" {
+		t.Errorf("expected name 'validtokenowner', got '%s'", found.Name)
+	}
+}
+
+func TestFindUserForToken_Expired(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("expiredtokenowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI expiredtokenowner", "comment")
+	token, _ := testDB.InsertToken(user.ID, "expired-token")
+
+	_, err := testDB.Db.Exec("UPDATE tokens SET expires_at = NOW() - INTERVAL '1 day' WHERE token = $1", token)
+	if err != nil {
+		t.Fatalf("failed to expire token: %v", err)
+	}
+
+	_, err = testDB.FindUserForToken(token)
+	if err == nil {
+		t.Error("expected error for expired token, got nil")
+	}
+}
+
+func TestRemoveToken(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("removetokenowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI removetokenowner", "comment")
+	_, _ = testDB.InsertToken(user.ID, "remove-token")
+
+	tokens, _ := testDB.FindTokensForUser(user.ID)
+	if len(tokens) != 1 {
+		t.Fatalf("expected 1 token, got %d", len(tokens))
+	}
+
+	err := testDB.RemoveToken(tokens[0].ID)
+	if err != nil {
+		t.Fatalf("RemoveToken failed: %v", err)
+	}
+
+	tokens, _ = testDB.FindTokensForUser(user.ID)
+	if len(tokens) != 0 {
+		t.Errorf("expected 0 tokens after removal, got %d", len(tokens))
+	}
+}
+
+// ============ Post CRUD Tests ============
+
+func TestInsertPost(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("postowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI postowner", "comment")
+
+	now := time.Now()
+	post := &db.Post{
+		UserID:      user.ID,
+		Filename:    "test.md",
+		Slug:        "test-post",
+		Title:       "Test Post",
+		Text:        "Post content",
+		Description: "A test post",
+		PublishAt:   &now,
+		UpdatedAt:   &now,
+		Space:       "prose",
+		MimeType:    "text/markdown",
+	}
+
+	created, err := testDB.InsertPost(post)
+	if err != nil {
+		t.Fatalf("InsertPost failed: %v", err)
+	}
+	if created.ID == "" {
+		t.Error("expected post ID, got empty")
+	}
+	if created.Title != "Test Post" {
+		t.Errorf("expected title 'Test Post', got '%s'", created.Title)
+	}
+}
+
+func TestUpdatePost(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("updatepostowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI updatepostowner", "comment")
+
+	now := time.Now()
+	post := mustInsertPost(t, &db.Post{
+		UserID:   user.ID,
+		Filename: "update.md",
+		Slug:     "update-post",
+		Title:    "Original Title",
+		Text:     "Original content",
+		Space:    "prose",
+	})
+
+	post.Title = "Updated Title"
+	post.Text = "Updated content"
+	post.UpdatedAt = &now
+
+	updated, err := testDB.UpdatePost(post)
+	if err != nil {
+		t.Fatalf("UpdatePost failed: %v", err)
+	}
+	if updated.Title != "Updated Title" {
+		t.Errorf("expected title 'Updated Title', got '%s'", updated.Title)
+	}
+}
+
+func TestRemovePosts(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("removepostowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI removepostowner", "comment")
+
+	post := mustInsertPost(t, &db.Post{
+		UserID:   user.ID,
+		Filename: "remove.md",
+		Slug:     "remove-post",
+		Title:    "To Remove",
+		Space:    "prose",
+	})
+
+	err := testDB.RemovePosts([]string{post.ID})
+	if err != nil {
+		t.Fatalf("RemovePosts failed: %v", err)
+	}
+
+	_, err = testDB.FindPost(post.ID)
+	if err == nil {
+		t.Error("expected error finding removed post, got nil")
+	}
+}
+
+func TestFindPost(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("findpostowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findpostowner", "comment")
+
+	created := mustInsertPost(t, &db.Post{
+		UserID:   user.ID,
+		Filename: "find.md",
+		Slug:     "find-post",
+		Title:    "Find Me",
+		Space:    "prose",
+	})
+
+	found, err := testDB.FindPost(created.ID)
+	if err != nil {
+		t.Fatalf("FindPost failed: %v", err)
+	}
+	if found.Title != "Find Me" {
+		t.Errorf("expected title 'Find Me', got '%s'", found.Title)
+	}
+}
+
+func TestFindPostWithFilename(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("filenamepostowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI filenamepostowner", "comment")
+
+	_ = mustInsertPost(t, &db.Post{
+		UserID:   user.ID,
+		Filename: "byfilename.md",
+		Slug:     "byfilename-post",
+		Title:    "By Filename",
+		Space:    "prose",
+	})
+
+	found, err := testDB.FindPostWithFilename("byfilename.md", user.ID, "prose")
+	if err != nil {
+		t.Fatalf("FindPostWithFilename failed: %v", err)
+	}
+	if found.Title != "By Filename" {
+		t.Errorf("expected title 'By Filename', got '%s'", found.Title)
+	}
+}
+
+func TestFindPostWithSlug(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("slugpostowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI slugpostowner", "comment")
+
+	_ = mustInsertPost(t, &db.Post{
+		UserID:   user.ID,
+		Filename: "byslug.md",
+		Slug:     "byslug-post",
+		Title:    "By Slug",
+		Space:    "prose",
+	})
+
+	found, err := testDB.FindPostWithSlug("byslug-post", user.ID, "prose")
+	if err != nil {
+		t.Fatalf("FindPostWithSlug failed: %v", err)
+	}
+	if found.Title != "By Slug" {
+		t.Errorf("expected title 'By Slug', got '%s'", found.Title)
+	}
+}
+
+func TestFindPosts(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("postsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI postsowner", "comment")
+
+	_ = mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "post1.md", Slug: "post1", Title: "Post 1", Space: "prose"})
+	_ = mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "post2.md", Slug: "post2", Title: "Post 2", Space: "prose"})
+
+	posts, err := testDB.FindPosts()
+	if err != nil {
+		t.Fatalf("FindPosts failed: %v", err)
+	}
+	if len(posts) != 2 {
+		t.Errorf("expected 2 posts, got %d", len(posts))
+	}
+}
+
+func TestFindPostsForUser(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("userpostsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI userpostsowner", "comment")
+	now := time.Now()
+
+	_ = mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "userpost1.md", Slug: "userpost1", Title: "User Post 1", Space: "prose", PublishAt: &now})
+	_ = mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "userpost2.md", Slug: "userpost2", Title: "User Post 2", Space: "prose", PublishAt: &now})
+
+	pager := &db.Pager{Num: 10, Page: 0}
+	result, err := testDB.FindPostsForUser(pager, user.ID, "prose")
+	if err != nil {
+		t.Fatalf("FindPostsForUser failed: %v", err)
+	}
+	if len(result.Data) != 2 {
+		t.Errorf("expected 2 posts, got %d", len(result.Data))
+	}
+}
+
+func TestFindAllPostsForUser(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("allpostsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI allpostsowner", "comment")
+
+	_ = mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "allpost1.md", Slug: "allpost1", Title: "All Post 1", Space: "prose"})
+	_ = mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "allpost2.md", Slug: "allpost2", Title: "All Post 2", Space: "prose"})
+
+	posts, err := testDB.FindAllPostsForUser(user.ID, "prose")
+	if err != nil {
+		t.Fatalf("FindAllPostsForUser failed: %v", err)
+	}
+	if len(posts) != 2 {
+		t.Errorf("expected 2 posts, got %d", len(posts))
+	}
+}
+
+func TestFindUsersWithPost(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("feedspostowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI feedspostowner", "comment")
+
+	_ = mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "feed.txt", Slug: "feed", Title: "Feed", Space: "feeds"})
+
+	users, err := testDB.FindUsersWithPost("feeds")
+	if err != nil {
+		t.Fatalf("FindUsersWithPost failed: %v", err)
+	}
+	if len(users) != 1 {
+		t.Errorf("expected 1 user, got %d", len(users))
+	}
+}
+
+func TestFindExpiredPosts(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("expiredpostowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI expiredpostowner", "comment")
+
+	expired := time.Now().Add(-24 * time.Hour)
+	_ = mustInsertPost(t, &db.Post{
+		UserID:    user.ID,
+		Filename:  "expired.txt",
+		Slug:      "expired",
+		Title:     "Expired",
+		Space:     "pastes",
+		ExpiresAt: &expired,
+	})
+
+	posts, err := testDB.FindExpiredPosts("pastes")
+	if err != nil {
+		t.Fatalf("FindExpiredPosts failed: %v", err)
+	}
+	if len(posts) != 1 {
+		t.Errorf("expected 1 expired post, got %d", len(posts))
+	}
+}
+
+func TestFindPostsForFeed(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("feedowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI feedowner", "comment")
+
+	now := time.Now()
+	_ = mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "feedpost.md", Slug: "feedpost", Title: "Feed Post", Space: "prose", PublishAt: &now})
+
+	pager := &db.Pager{Num: 10, Page: 0}
+	result, err := testDB.FindPostsForFeed(pager, "prose")
+	if err != nil {
+		t.Fatalf("FindPostsForFeed failed: %v", err)
+	}
+	if len(result.Data) < 1 {
+		t.Errorf("expected at least 1 post in feed, got %d", len(result.Data))
+	}
+}
+
+// ============ Tags Tests ============
+
+func TestReplaceTagsForPost(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("tagspostowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI tagspostowner", "comment")
+	post := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "tagged.md", Slug: "tagged", Title: "Tagged", Space: "prose"})
+
+	err := testDB.ReplaceTagsForPost([]string{"tag1", "tag2"}, post.ID)
+	if err != nil {
+		t.Fatalf("ReplaceTagsForPost failed: %v", err)
+	}
+
+	found, _ := testDB.FindPostWithFilename("tagged.md", user.ID, "prose")
+	if len(found.Tags) != 2 {
+		t.Errorf("expected 2 tags, got %d", len(found.Tags))
+	}
+}
+
+func TestFindUserPostsByTag(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("usertagowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI usertagowner", "comment")
+	now := time.Now()
+	post := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "usertag.md", Slug: "usertag", Title: "User Tag", Space: "prose", PublishAt: &now})
+	_ = testDB.ReplaceTagsForPost([]string{"mytag"}, post.ID)
+
+	pager := &db.Pager{Num: 10, Page: 0}
+	result, err := testDB.FindUserPostsByTag(pager, "mytag", user.ID, "prose")
+	if err != nil {
+		t.Fatalf("FindUserPostsByTag failed: %v", err)
+	}
+	if len(result.Data) != 1 {
+		t.Errorf("expected 1 post with tag, got %d", len(result.Data))
+	}
+}
+
+func TestFindPostsByTag(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("tagsearchowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI tagsearchowner", "comment")
+	now := time.Now()
+	post := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "tagsearch.md", Slug: "tagsearch", Title: "Tag Search", Space: "prose", PublishAt: &now})
+	_ = testDB.ReplaceTagsForPost([]string{"searchtag"}, post.ID)
+
+	pager := &db.Pager{Num: 10, Page: 0}
+	result, err := testDB.FindPostsByTag(pager, "searchtag", "prose")
+	if err != nil {
+		t.Fatalf("FindPostsByTag failed: %v", err)
+	}
+	if len(result.Data) != 1 {
+		t.Errorf("expected 1 post with tag, got %d", len(result.Data))
+	}
+}
+
+func TestFindPopularTags(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("populartagowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI populartagowner", "comment")
+	post1 := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "pop1.md", Slug: "pop1", Title: "Pop 1", Space: "prose"})
+	post2 := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "pop2.md", Slug: "pop2", Title: "Pop 2", Space: "prose"})
+	_ = testDB.ReplaceTagsForPost([]string{"popular"}, post1.ID)
+	_ = testDB.ReplaceTagsForPost([]string{"popular"}, post2.ID)
+
+	tags, err := testDB.FindPopularTags("prose")
+	if err != nil {
+		t.Fatalf("FindPopularTags failed: %v", err)
+	}
+	if len(tags) < 1 {
+		t.Errorf("expected at least 1 popular tag, got %d", len(tags))
+	}
+}
+
+// ============ Aliases Tests ============
+
+func TestReplaceAliasesForPost(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("aliasowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI aliasowner", "comment")
+	post := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "aliased.md", Slug: "aliased", Title: "Aliased", Space: "prose"})
+
+	err := testDB.ReplaceAliasesForPost([]string{"alias1", "alias2"}, post.ID)
+	if err != nil {
+		t.Fatalf("ReplaceAliasesForPost failed: %v", err)
+	}
+}
+
+func TestFindPostWithSlug_Alias(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("aliassearchowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI aliassearchowner", "comment")
+	post := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "original.md", Slug: "original", Title: "Original", Space: "prose"})
+	_ = testDB.ReplaceAliasesForPost([]string{"my-alias"}, post.ID)
+
+	found, err := testDB.FindPostWithSlug("my-alias", user.ID, "prose")
+	if err != nil {
+		t.Fatalf("FindPostWithSlug for alias failed: %v", err)
+	}
+	if found.Title != "Original" {
+		t.Errorf("expected title 'Original', got '%s'", found.Title)
+	}
+}
+
+// ============ Analytics Tests ============
+
+func TestInsertVisit(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("visitowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI visitowner", "comment")
+
+	visit := &db.AnalyticsVisits{
+		UserID:    user.ID,
+		Host:      "example.com",
+		Path:      "/test",
+		IpAddress: "192.168.1.1",
+		UserAgent: "TestAgent/1.0",
+		Referer:   "https://referrer.com",
+		Status:    200,
+	}
+
+	err := testDB.InsertVisit(visit)
+	if err != nil {
+		t.Fatalf("InsertVisit failed: %v", err)
+	}
+}
+
+func TestVisitSummary(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("summaryowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI summaryowner", "comment")
+
+	visit := &db.AnalyticsVisits{
+		UserID:    user.ID,
+		Host:      "summary.com",
+		Path:      "/page",
+		IpAddress: "192.168.1.2",
+		Status:    200,
+	}
+	_ = testDB.InsertVisit(visit)
+
+	opts := &db.SummaryOpts{
+		Interval: "day",
+		Origin:   time.Now().Add(-24 * time.Hour),
+		Host:     "summary.com",
+		UserID:   user.ID,
+	}
+
+	summary, err := testDB.VisitSummary(opts)
+	if err != nil {
+		t.Fatalf("VisitSummary failed: %v", err)
+	}
+	if summary == nil {
+		t.Error("expected summary, got nil")
+	}
+}
+
+func TestFindVisitSiteList(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("sitelistowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI sitelistowner", "comment")
+
+	_ = testDB.InsertVisit(&db.AnalyticsVisits{
+		UserID:    user.ID,
+		Host:      "site1.com",
+		Path:      "/",
+		IpAddress: "192.168.1.3",
+		Status:    200,
+	})
+
+	opts := &db.SummaryOpts{UserID: user.ID}
+	sites, err := testDB.FindVisitSiteList(opts)
+	if err != nil {
+		t.Fatalf("FindVisitSiteList failed: %v", err)
+	}
+	if len(sites) < 1 {
+		t.Errorf("expected at least 1 site, got %d", len(sites))
+	}
+}
+
+func TestVisitUrlNotFound(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("notfoundowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI notfoundowner", "comment")
+
+	_ = testDB.InsertVisit(&db.AnalyticsVisits{
+		UserID:    user.ID,
+		Host:      "notfound.com",
+		Path:      "/missing",
+		IpAddress: "192.168.1.4",
+		Status:    404,
+	})
+
+	opts := &db.SummaryOpts{
+		Origin: time.Now().Add(-24 * time.Hour),
+		Host:   "notfound.com",
+		UserID: user.ID,
+	}
+	notFound, err := testDB.VisitUrlNotFound(opts)
+	if err != nil {
+		t.Fatalf("VisitUrlNotFound failed: %v", err)
+	}
+	if len(notFound) < 1 {
+		t.Errorf("expected at least 1 not found URL, got %d", len(notFound))
+	}
+}
+
+// ============ Features Tests ============
+
+func TestInsertFeature(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("featureowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI featureowner", "comment")
+
+	expiresAt := time.Now().Add(365 * 24 * time.Hour)
+	feature, err := testDB.InsertFeature(user.ID, "plus", expiresAt)
+	if err != nil {
+		t.Fatalf("InsertFeature failed: %v", err)
+	}
+	if feature.Name != "plus" {
+		t.Errorf("expected feature name 'plus', got '%s'", feature.Name)
+	}
+}
+
+func TestFindFeature(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("findfeatureowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findfeatureowner", "comment")
+	expiresAt := time.Now().Add(365 * 24 * time.Hour)
+	_, _ = testDB.InsertFeature(user.ID, "plus", expiresAt)
+
+	feature, err := testDB.FindFeature(user.ID, "plus")
+	if err != nil {
+		t.Fatalf("FindFeature failed: %v", err)
+	}
+	if feature.Name != "plus" {
+		t.Errorf("expected feature name 'plus', got '%s'", feature.Name)
+	}
+}
+
+func TestFindFeaturesForUser(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("featuresowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI featuresowner", "comment")
+	expiresAt := time.Now().Add(365 * 24 * time.Hour)
+	_, _ = testDB.InsertFeature(user.ID, "plus", expiresAt)
+	_, _ = testDB.InsertFeature(user.ID, "pro", expiresAt)
+
+	features, err := testDB.FindFeaturesForUser(user.ID)
+	if err != nil {
+		t.Fatalf("FindFeaturesForUser failed: %v", err)
+	}
+	if len(features) != 2 {
+		t.Errorf("expected 2 features, got %d", len(features))
+	}
+}
+
+func TestHasFeatureForUser(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("hasfeatureowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI hasfeatureowner", "comment")
+	expiresAt := time.Now().Add(365 * 24 * time.Hour)
+	_, _ = testDB.InsertFeature(user.ID, "plus", expiresAt)
+
+	has := testDB.HasFeatureForUser(user.ID, "plus")
+	if !has {
+		t.Error("expected HasFeatureForUser to return true")
+	}
+
+	hasNot := testDB.HasFeatureForUser(user.ID, "nonexistent")
+	if hasNot {
+		t.Error("expected HasFeatureForUser to return false for nonexistent feature")
+	}
+}
+
+func TestRemoveFeature(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("removefeatureowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI removefeatureowner", "comment")
+	expiresAt := time.Now().Add(365 * 24 * time.Hour)
+	_, _ = testDB.InsertFeature(user.ID, "plus", expiresAt)
+
+	err := testDB.RemoveFeature(user.ID, "plus")
+	if err != nil {
+		t.Fatalf("RemoveFeature failed: %v", err)
+	}
+
+	_, err = testDB.FindFeature(user.ID, "plus")
+	if err == nil {
+		t.Error("expected error finding removed feature, got nil")
+	}
+}
+
+func TestAddPicoPlusUser(t *testing.T) {
+	cleanupTestData(t)
+
+	_, _ = testDB.RegisterUser("picoplusowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI picoplusowner", "comment")
+
+	err := testDB.AddPicoPlusUser("picoplusowner", "test@example.com", "stripe", "tx123")
+	if err != nil {
+		t.Fatalf("AddPicoPlusUser failed: %v", err)
+	}
+}
+
+// ============ Feed Items Tests ============
+
+func TestInsertFeedItems(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("feeditemsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI feeditemsowner", "comment")
+	post := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "feed.txt", Slug: "feed", Title: "Feed", Space: "feeds"})
+
+	items := []*db.FeedItem{
+		{PostID: post.ID, GUID: "guid-1", Data: db.FeedItemData{Title: "Item 1", Link: "http://example.com/1"}},
+		{PostID: post.ID, GUID: "guid-2", Data: db.FeedItemData{Title: "Item 2", Link: "http://example.com/2"}},
+	}
+
+	err := testDB.InsertFeedItems(post.ID, items)
+	if err != nil {
+		t.Fatalf("InsertFeedItems failed: %v", err)
+	}
+}
+
+func TestFindFeedItemsByPostID(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("findfeeditemsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findfeeditemsowner", "comment")
+	post := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "findfeed.txt", Slug: "findfeed", Title: "Find Feed", Space: "feeds"})
+
+	items := []*db.FeedItem{
+		{PostID: post.ID, GUID: "find-guid-1", Data: db.FeedItemData{Title: "Find Item 1"}},
+	}
+	_ = testDB.InsertFeedItems(post.ID, items)
+
+	found, err := testDB.FindFeedItemsByPostID(post.ID)
+	if err != nil {
+		t.Fatalf("FindFeedItemsByPostID failed: %v", err)
+	}
+	if len(found) != 1 {
+		t.Errorf("expected 1 feed item, got %d", len(found))
+	}
+}
+
+// ============ Projects Tests ============
+
+func TestUpsertProject(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("projectowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI projectowner", "comment")
+
+	project, err := testDB.UpsertProject(user.ID, "my-project", "my-project")
+	if err != nil {
+		t.Fatalf("UpsertProject failed: %v", err)
+	}
+	if project.Name != "my-project" {
+		t.Errorf("expected project name 'my-project', got '%s'", project.Name)
+	}
+
+	project2, err := testDB.UpsertProject(user.ID, "my-project", "my-project")
+	if err != nil {
+		t.Fatalf("UpsertProject (update) failed: %v", err)
+	}
+	if project2.ID != project.ID {
+		t.Error("expected same project ID on upsert")
+	}
+}
+
+func TestFindProjectByName(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("findprojectowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findprojectowner", "comment")
+	_, _ = testDB.UpsertProject(user.ID, "findme-project", "findme-project")
+
+	project, err := testDB.FindProjectByName(user.ID, "findme-project")
+	if err != nil {
+		t.Fatalf("FindProjectByName failed: %v", err)
+	}
+	if project.Name != "findme-project" {
+		t.Errorf("expected project name 'findme-project', got '%s'", project.Name)
+	}
+}
+
+// ============ User Stats Tests ============
+
+func TestFindUserStats(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("statsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI statsowner", "comment")
+	_ = mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "stat.md", Slug: "stat", Title: "Stat", Space: "prose"})
+	_, _ = testDB.UpsertProject(user.ID, "stat-project", "stat-project")
+
+	stats, err := testDB.FindUserStats(user.ID)
+	if err != nil {
+		t.Fatalf("FindUserStats failed: %v", err)
+	}
+	if stats.Prose.Num != 1 {
+		t.Errorf("expected 1 prose post, got %d", stats.Prose.Num)
+	}
+	if stats.Pages.Num != 1 {
+		t.Errorf("expected 1 project, got %d", stats.Pages.Num)
+	}
+}
+
+// ============ Tuns Event Logs Tests ============
+
+func TestInsertTunsEventLog(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("tunsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI tunsowner", "comment")
+
+	log := &db.TunsEventLog{
+		UserId:         user.ID,
+		ServerID:       "server-1",
+		RemoteAddr:     "192.168.1.1:1234",
+		EventType:      "connect",
+		TunnelType:     "http",
+		ConnectionType: "tcp",
+		TunnelID:       "tunnel-123",
+	}
+
+	err := testDB.InsertTunsEventLog(log)
+	if err != nil {
+		t.Fatalf("InsertTunsEventLog failed: %v", err)
+	}
+}
+
+func TestFindTunsEventLogs(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("findtunsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findtunsowner", "comment")
+	_ = testDB.InsertTunsEventLog(&db.TunsEventLog{
+		UserId:         user.ID,
+		ServerID:       "server-1",
+		RemoteAddr:     "192.168.1.1:1234",
+		EventType:      "connect",
+		TunnelType:     "http",
+		ConnectionType: "tcp",
+		TunnelID:       "tunnel-456",
+	})
+
+	logs, err := testDB.FindTunsEventLogs(user.ID)
+	if err != nil {
+		t.Fatalf("FindTunsEventLogs failed: %v", err)
+	}
+	if len(logs) != 1 {
+		t.Errorf("expected 1 log, got %d", len(logs))
+	}
+}
+
+func TestFindTunsEventLogsByAddr(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("tunsaddrowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI tunsaddrowner", "comment")
+	_ = testDB.InsertTunsEventLog(&db.TunsEventLog{
+		UserId:         user.ID,
+		ServerID:       "server-1",
+		RemoteAddr:     "192.168.1.1:1234",
+		EventType:      "connect",
+		TunnelType:     "http",
+		ConnectionType: "tcp",
+		TunnelID:       "tunnel-789",
+	})
+
+	logs, err := testDB.FindTunsEventLogsByAddr(user.ID, "tunnel-789")
+	if err != nil {
+		t.Fatalf("FindTunsEventLogsByAddr failed: %v", err)
+	}
+	if len(logs) != 1 {
+		t.Errorf("expected 1 log, got %d", len(logs))
+	}
+}
+
+// ============ Access Logs Tests ============
+
+func TestInsertAccessLog(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("accesslogowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI accesslogowner", "comment")
+
+	log := &db.AccessLog{
+		UserID:   user.ID,
+		Service:  "pgs",
+		Pubkey:   "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI accesslogowner",
+		Identity: "accesslogowner",
+	}
+
+	err := testDB.InsertAccessLog(log)
+	if err != nil {
+		t.Fatalf("InsertAccessLog failed: %v", err)
+	}
+}
+
+func TestFindAccessLogs(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("findaccesslogowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findaccesslogowner", "comment")
+	_ = testDB.InsertAccessLog(&db.AccessLog{
+		UserID:   user.ID,
+		Service:  "pgs",
+		Pubkey:   "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findaccesslogowner",
+		Identity: "findaccesslogowner",
+	})
+
+	fromDate := time.Now().Add(-24 * time.Hour)
+	logs, err := testDB.FindAccessLogs(user.ID, &fromDate)
+	if err != nil {
+		t.Fatalf("FindAccessLogs failed: %v", err)
+	}
+	if len(logs) != 1 {
+		t.Errorf("expected 1 log, got %d", len(logs))
+	}
+}
+
+func TestFindPubkeysInAccessLogs(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("pubkeylogowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI pubkeylogowner", "comment")
+	_ = testDB.InsertAccessLog(&db.AccessLog{
+		UserID:   user.ID,
+		Service:  "pgs",
+		Pubkey:   "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI pubkeylogowner",
+		Identity: "pubkeylogowner",
+	})
+
+	pubkeys, err := testDB.FindPubkeysInAccessLogs(user.ID)
+	if err != nil {
+		t.Fatalf("FindPubkeysInAccessLogs failed: %v", err)
+	}
+	if len(pubkeys) != 1 {
+		t.Errorf("expected 1 pubkey, got %d", len(pubkeys))
+	}
+}
+
+func TestFindAccessLogsByPubkey(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("accessbypubkeyowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI accessbypubkeyowner", "comment")
+	pubkey := "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI accessbypubkeyowner"
+	_ = testDB.InsertAccessLog(&db.AccessLog{
+		UserID:   user.ID,
+		Service:  "pgs",
+		Pubkey:   pubkey,
+		Identity: "accessbypubkeyowner",
+	})
+
+	fromDate := time.Now().Add(-24 * time.Hour)
+	logs, err := testDB.FindAccessLogsByPubkey(pubkey, &fromDate)
+	if err != nil {
+		t.Fatalf("FindAccessLogsByPubkey failed: %v", err)
+	}
+	if len(logs) != 1 {
+		t.Errorf("expected 1 log, got %d", len(logs))
+	}
+}
+
+// ============ JSONB Roundtrip Tests ============
+
+func TestPostData_JSONBRoundtrip(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("postdataowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI postdataowner", "comment")
+
+	now := time.Now().Truncate(time.Second)
+	postData := db.PostData{
+		ImgPath:    "/images/test.png",
+		LastDigest: &now,
+		Attempts:   5,
+	}
+
+	post := mustInsertPost(t, &db.Post{
+		UserID:   user.ID,
+		Filename: "jsonb.md",
+		Slug:     "jsonb",
+		Title:    "JSONB Test",
+		Space:    "prose",
+		Data:     postData,
+	})
+
+	found, err := testDB.FindPost(post.ID)
+	if err != nil {
+		t.Fatalf("FindPost failed: %v", err)
+	}
+	if found.Data.ImgPath != "/images/test.png" {
+		t.Errorf("expected ImgPath '/images/test.png', got '%s'", found.Data.ImgPath)
+	}
+	if found.Data.Attempts != 5 {
+		t.Errorf("expected Attempts 5, got %d", found.Data.Attempts)
+	}
+}
+
+func TestProjectAcl_JSONBRoundtrip(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("aclowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI aclowner", "comment")
+
+	project, err := testDB.UpsertProject(user.ID, "acl-project", "acl-project")
+	if err != nil {
+		t.Fatalf("UpsertProject failed: %v", err)
+	}
+
+	found, err := testDB.FindProjectByName(user.ID, "acl-project")
+	if err != nil {
+		t.Fatalf("FindProjectByName failed: %v", err)
+	}
+	if found.Acl.Type != "public" {
+		t.Errorf("expected Acl.Type 'public', got '%s'", found.Acl.Type)
+	}
+	_ = project
+}
+
+func TestFeedItemData_JSONBRoundtrip(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("feedjsonbowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI feedjsonbowner", "comment")
+	post := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "feedjsonb.txt", Slug: "feedjsonb", Title: "Feed JSONB", Space: "feeds"})
+
+	now := time.Now().Truncate(time.Second)
+	items := []*db.FeedItem{
+		{
+			PostID: post.ID,
+			GUID:   "jsonb-guid",
+			Data: db.FeedItemData{
+				Title:       "JSONB Item",
+				Description: "Description",
+				Content:     "Content",
+				Link:        "http://example.com",
+				PublishedAt: &now,
+			},
+		},
+	}
+	_ = testDB.InsertFeedItems(post.ID, items)
+
+	found, err := testDB.FindFeedItemsByPostID(post.ID)
+	if err != nil {
+		t.Fatalf("FindFeedItemsByPostID failed: %v", err)
+	}
+	if len(found) != 1 {
+		t.Fatalf("expected 1 item, got %d", len(found))
+	}
+	if found[0].Data.Title != "JSONB Item" {
+		t.Errorf("expected title 'JSONB Item', got '%s'", found[0].Data.Title)
+	}
+}
+
+func TestFeatureFlagData_JSONBRoundtrip(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("featurejsonbowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI featurejsonbowner", "comment")
+
+	err := testDB.AddPicoPlusUser("featurejsonbowner", "test@example.com", "stripe", "tx456")
+	if err != nil {
+		t.Fatalf("AddPicoPlusUser failed: %v", err)
+	}
+
+	feature, err := testDB.FindFeature(user.ID, "plus")
+	if err != nil {
+		t.Fatalf("FindFeature failed: %v", err)
+	}
+	if feature.Data.StorageMax != 10000000000 {
+		t.Errorf("expected StorageMax 10000000000, got %d", feature.Data.StorageMax)
+	}
+	if feature.Data.FileMax != 50000000 {
+		t.Errorf("expected FileMax 50000000, got %d", feature.Data.FileMax)
+	}
+}
+
+func TestPaymentHistoryData_JSONBRoundtrip(t *testing.T) {
+	cleanupTestData(t)
+
+	user, _ := testDB.RegisterUser("paymentjsonbowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI paymentjsonbowner", "comment")
+
+	err := testDB.AddPicoPlusUser("paymentjsonbowner", "payment@example.com", "stripe", "tx789")
+	if err != nil {
+		t.Fatalf("AddPicoPlusUser failed: %v", err)
+	}
+
+	var txId string
+	err = testDB.Db.QueryRow("SELECT data->>'tx_id' FROM payment_history WHERE user_id = $1", user.ID).Scan(&txId)
+	if err != nil {
+		t.Fatalf("failed to query payment history: %v", err)
+	}
+	if txId != "tx789" {
+		t.Errorf("expected tx_id 'tx789', got '%s'", txId)
+	}
+}

refactor: use sqlx interface

+2 -1 cmd/scripts/dates/dates.go link 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

diff --git a/cmd/scripts/dates/dates.go b/cmd/scripts/dates/dates.go
index 4437f01..4e4b079 100644
--- a/cmd/scripts/dates/dates.go
+++ b/cmd/scripts/dates/dates.go
@@ -7,12 +7,13 @@ import (
 	"os"
 	"time"
 
+	"github.com/jmoiron/sqlx"
 	"github.com/picosh/pico/pkg/db"
 	"github.com/picosh/pico/pkg/db/postgres"
 	"github.com/picosh/pico/pkg/shared"
 )
 
-func findPosts(dbpool *sql.DB) ([]*db.Post, error) {
+func findPosts(dbpool *sqlx.DB) ([]*db.Post, error) {
 	var posts []*db.Post
 	rs, err := dbpool.Query(`SELECT
 		id, user_id, filename, title, text, description,
+2 -1 cmd/scripts/migrate/migrate.go link 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

diff --git a/cmd/scripts/migrate/migrate.go b/cmd/scripts/migrate/migrate.go
index 06f1972..e76038b 100644
--- a/cmd/scripts/migrate/migrate.go
+++ b/cmd/scripts/migrate/migrate.go
@@ -7,12 +7,13 @@ import (
 	"log/slog"
 	"os"
 
+	"github.com/jmoiron/sqlx"
 	"github.com/picosh/pico/pkg/db"
 	"github.com/picosh/pico/pkg/db/postgres"
 	"github.com/picosh/pico/pkg/shared"
 )
 
-func findPosts(dbpool *sql.DB) ([]*db.Post, error) {
+func findPosts(dbpool *sqlx.DB) ([]*db.Post, error) {
 	var posts []*db.Post
 	rs, err := dbpool.Query(`SELECT
 		posts.id, user_id, filename, title, text, description,
+2 -2 cmd/scripts/tags/tags.go link 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

diff --git a/cmd/scripts/tags/tags.go b/cmd/scripts/tags/tags.go
index ea2cca8..bf94471 100644
--- a/cmd/scripts/tags/tags.go
+++ b/cmd/scripts/tags/tags.go
@@ -1,16 +1,16 @@
 package main
 
 import (
-	"database/sql"
 	"log/slog"
 	"os"
 
+	"github.com/jmoiron/sqlx"
 	"github.com/picosh/pico/pkg/db"
 	"github.com/picosh/pico/pkg/db/postgres"
 	"github.com/picosh/pico/pkg/shared"
 )
 
-func findPosts(dbpool *sql.DB) ([]*db.Post, error) {
+func findPosts(dbpool *sqlx.DB) ([]*db.Post, error) {
 	var posts []*db.Post
 	rs, err := dbpool.Query(`SELECT
 		posts.id, user_id, filename, title, text, description,
+3 -2 pkg/db/postgres/storage.go link 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

diff --git a/pkg/db/postgres/storage.go b/pkg/db/postgres/storage.go
index d401ff9..8c6c0c1 100644
--- a/pkg/db/postgres/storage.go
+++ b/pkg/db/postgres/storage.go
@@ -12,6 +12,7 @@ import (
 
 	"slices"
 
+	"github.com/jmoiron/sqlx"
 	_ "github.com/lib/pq"
 	"github.com/picosh/pico/pkg/db"
 	"github.com/picosh/utils"
@@ -215,7 +216,7 @@ const (
 
 type PsqlDB struct {
 	Logger *slog.Logger
-	Db     *sql.DB
+	Db     *sqlx.DB
 }
 
 type RowScanner interface {
@@ -297,7 +298,7 @@ func NewDB(databaseUrl string, logger *slog.Logger) *PsqlDB {
 	}
 	d.Logger.Info("Connecting to postgres", "databaseUrl", databaseUrl)
 
-	db, err := sql.Open("postgres", databaseUrl)
+	db, err := sqlx.Connect("postgres", databaseUrl)
 	if err != nil {
 		d.Logger.Error(err.Error())
 	}
+4 -4 pkg/db/postgres/storage_test.go link 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

diff --git a/pkg/db/postgres/storage_test.go b/pkg/db/postgres/storage_test.go
index 6a5fd40..4cfc099 100644
--- a/pkg/db/postgres/storage_test.go
+++ b/pkg/db/postgres/storage_test.go
@@ -2,7 +2,6 @@ package postgres
 
 import (
 	"context"
-	"database/sql"
 	"fmt"
 	"log/slog"
 	"os"
@@ -13,6 +12,7 @@ import (
 	"testing"
 	"time"
 
+	"github.com/jmoiron/sqlx"
 	_ "github.com/lib/pq"
 	"github.com/picosh/pico/pkg/db"
 	"github.com/testcontainers/testcontainers-go"
@@ -34,14 +34,14 @@ func setupContainerRuntime() bool {
 	if cmd := exec.Command("podman", "info"); cmd.Run() == nil {
 		// For podman, we need to ensure the socket is running
 		// User should run: systemctl --user start podman.socket
-		os.Setenv("TESTCONTAINERS_RYUK_DISABLED", "true")
+		_ = os.Setenv("TESTCONTAINERS_RYUK_DISABLED", "true")
 
 		// Check if socket exists and is accessible
 		xdgRuntime := os.Getenv("XDG_RUNTIME_DIR")
 		if xdgRuntime != "" {
 			socketPath := xdgRuntime + "/podman/podman.sock"
 			if _, err := os.Stat(socketPath); err == nil {
-				os.Setenv("DOCKER_HOST", "unix://"+socketPath)
+				_ = os.Setenv("DOCKER_HOST", "unix://"+socketPath)
 				return true
 			}
 		}
@@ -127,7 +127,7 @@ func getProjectRoot() string {
 	return filepath.Join(filepath.Dir(filename), "..", "..", "..")
 }
 
-func setupTestSchema(db *sql.DB) error {
+func setupTestSchema(db *sqlx.DB) error {
 	projectRoot := getProjectRoot()
 	migrationsDir := filepath.Join(projectRoot, "sql", "migrations")

chore: add db tags

+68 -68 pkg/db/db.go link 
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189

diff --git a/pkg/db/db.go b/pkg/db/db.go
index 7f2b711..f418c75 100644
--- a/pkg/db/db.go
+++ b/pkg/db/db.go
@@ -127,29 +127,29 @@ func (p *FeedItemData) Scan(value any) error {
 }
 
 type Post struct {
-	ID          string     `json:"id"`
-	UserID      string     `json:"user_id"`
-	Filename    string     `json:"filename"`
-	Slug        string     `json:"slug"`
-	Title       string     `json:"title"`
-	Text        string     `json:"text"`
-	Description string     `json:"description"`
-	CreatedAt   *time.Time `json:"created_at"`
-	PublishAt   *time.Time `json:"publish_at"`
-	Username    string     `json:"username"`
-	UpdatedAt   *time.Time `json:"updated_at"`
-	ExpiresAt   *time.Time `json:"expires_at"`
-	Hidden      bool       `json:"hidden"`
-	Views       int        `json:"views"`
-	Space       string     `json:"space"`
-	Shasum      string     `json:"shasum"`
-	FileSize    int        `json:"file_size"`
-	MimeType    string     `json:"mime_type"`
-	Data        PostData   `json:"data"`
-	Tags        []string   `json:"tags"`
+	ID          string     `json:"id" db:"id"`
+	UserID      string     `json:"user_id" db:"user_id"`
+	Filename    string     `json:"filename" db:"filename"`
+	Slug        string     `json:"slug" db:"slug"`
+	Title       string     `json:"title" db:"title"`
+	Text        string     `json:"text" db:"text"`
+	Description string     `json:"description" db:"description"`
+	CreatedAt   *time.Time `json:"created_at" db:"created_at"`
+	PublishAt   *time.Time `json:"publish_at" db:"publish_at"`
+	Username    string     `json:"username" db:"name"`
+	UpdatedAt   *time.Time `json:"updated_at" db:"updated_at"`
+	ExpiresAt   *time.Time `json:"expires_at" db:"expires_at"`
+	Hidden      bool       `json:"hidden" db:"hidden"`
+	Views       int        `json:"views" db:"views"`
+	Space       string     `json:"space" db:"cur_space"`
+	Shasum      string     `json:"shasum" db:"shasum"`
+	FileSize    int        `json:"file_size" db:"file_size"`
+	MimeType    string     `json:"mime_type" db:"mime_type"`
+	Data        PostData   `json:"data" db:"data"`
+	Tags        []string   `json:"tags" db:"-"`
 
 	// computed
-	IsVirtual bool
+	IsVirtual bool `db:"-"`
 }
 
 type Paginate[T any] struct {
@@ -158,13 +158,13 @@ type Paginate[T any] struct {
 }
 
 type VisitInterval struct {
-	Interval *time.Time `json:"interval"`
-	Visitors int        `json:"visitors"`
+	Interval *time.Time `json:"interval" db:"interval"`
+	Visitors int        `json:"visitors" db:"visitors"`
 }
 
 type VisitUrl struct {
-	Url   string `json:"url"`
-	Count int    `json:"count"`
+	Url   string `json:"url" db:"url"`
+	Count int    `json:"count" db:"count"`
 }
 
 type SummaryOpts struct {
@@ -184,34 +184,34 @@ type SummaryVisits struct {
 }
 
 type PostAnalytics struct {
-	ID       string
-	PostID   string
-	Views    int
-	UpdateAt *time.Time
+	ID       string     `json:"id" db:"id"`
+	PostID   string     `json:"post_id" db:"post_id"`
+	Views    int        `json:"views" db:"views"`
+	UpdateAt *time.Time `json:"updated_at" db:"updated_at"`
 }
 
 type AnalyticsVisits struct {
-	ID          string `json:"id"`
-	UserID      string `json:"user_id"`
-	ProjectID   string `json:"project_id"`
-	PostID      string `json:"post_id"`
-	Namespace   string `json:"namespace"`
-	Host        string `json:"host"`
-	Path        string `json:"path"`
-	IpAddress   string `json:"ip_address"`
-	UserAgent   string `json:"user_agent"`
-	Referer     string `json:"referer"`
-	Status      int    `json:"status"`
-	ContentType string `json:"content_type"`
+	ID          string `json:"id" db:"id"`
+	UserID      string `json:"user_id" db:"user_id"`
+	ProjectID   string `json:"project_id" db:"project_id"`
+	PostID      string `json:"post_id" db:"post_id"`
+	Namespace   string `json:"namespace" db:"namespace"`
+	Host        string `json:"host" db:"host"`
+	Path        string `json:"path" db:"path"`
+	IpAddress   string `json:"ip_address" db:"ip_address"`
+	UserAgent   string `json:"user_agent" db:"user_agent"`
+	Referer     string `json:"referer" db:"referer"`
+	Status      int    `json:"status" db:"status"`
+	ContentType string `json:"content_type" db:"content_type"`
 }
 
 type AccessLog struct {
-	ID        string     `json:"id"`
-	UserID    string     `json:"user_id"`
-	Service   string     `json:"service"`
-	Pubkey    string     `json:"pubkey"`
-	Identity  string     `json:"identity"`
-	CreatedAt *time.Time `json:"created_at"`
+	ID        string     `json:"id" db:"id"`
+	UserID    string     `json:"user_id" db:"user_id"`
+	Service   string     `json:"service" db:"service"`
+	Pubkey    string     `json:"pubkey" db:"pubkey"`
+	Identity  string     `json:"identity" db:"identity"`
+	CreatedAt *time.Time `json:"created_at" db:"created_at"`
 }
 
 type Pager struct {
@@ -220,19 +220,19 @@ type Pager struct {
 }
 
 type FeedItem struct {
-	ID        string
-	PostID    string
-	GUID      string
-	Data      FeedItemData
-	CreatedAt *time.Time
+	ID        string       `json:"id" db:"id"`
+	PostID    string       `json:"post_id" db:"post_id"`
+	GUID      string       `json:"guid" db:"guid"`
+	Data      FeedItemData `json:"data" db:"data"`
+	CreatedAt *time.Time   `json:"created_at" db:"created_at"`
 }
 
 type Token struct {
-	ID        string     `json:"id"`
-	UserID    string     `json:"user_id"`
-	Name      string     `json:"name"`
-	CreatedAt *time.Time `json:"created_at"`
-	ExpiresAt *time.Time `json:"expires_at"`
+	ID        string     `json:"id" db:"id"`
+	UserID    string     `json:"user_id" db:"user_id"`
+	Name      string     `json:"name" db:"name"`
+	CreatedAt *time.Time `json:"created_at" db:"created_at"`
+	ExpiresAt *time.Time `json:"expires_at" db:"expires_at"`
 }
 
 type FeatureFlag struct {
@@ -352,17 +352,17 @@ type UserServiceStats struct {
 }
 
 type TunsEventLog struct {
-	ID             string     `json:"id"`
-	ServerID       string     `json:"server_id"`
-	Time           *time.Time `json:"time"`
-	User           string     `json:"user"`
-	UserId         string     `json:"user_id"`
-	RemoteAddr     string     `json:"remote_addr"`
-	EventType      string     `json:"event_type"`
-	TunnelID       string     `json:"tunnel_id"`
-	TunnelType     string     `json:"tunnel_type"`
-	ConnectionType string     `json:"connection_type"`
-	CreatedAt      *time.Time `json:"created_at"`
+	ID             string     `json:"id" db:"id"`
+	ServerID       string     `json:"server_id" db:"server_id"`
+	Time           *time.Time `json:"time" db:"time"`
+	User           string     `json:"user" db:"user"`
+	UserId         string     `json:"user_id" db:"user_id"`
+	RemoteAddr     string     `json:"remote_addr" db:"remote_addr"`
+	EventType      string     `json:"event_type" db:"event_type"`
+	TunnelID       string     `json:"tunnel_id" db:"tunnel_id"`
+	TunnelType     string     `json:"tunnel_type" db:"tunnel_type"`
+	ConnectionType string     `json:"connection_type" db:"connection_type"`
+	CreatedAt      *time.Time `json:"created_at" db:"created_at"`
 }
 
 var NameValidator = regexp.MustCompile("^[a-zA-Z0-9]{1,50}$")

refactor: use sqlx

+1 -1 pkg/apps/pico/file_handler.go link 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

diff --git a/pkg/apps/pico/file_handler.go b/pkg/apps/pico/file_handler.go
index 3d43cd4..1d6f79e 100644
--- a/pkg/apps/pico/file_handler.go
+++ b/pkg/apps/pico/file_handler.go
@@ -247,7 +247,7 @@ func (h *UploadHandler) ProcessAuthorizedKeys(text []byte, logger *slog.Logger,
 		_, _ = fmt.Fprintf(s.Stderr(), "adding pubkey (%s)\n", key)
 		logger.Info("adding pubkey", "pubkey", key)
 
-		err = dbpool.InsertPublicKey(user.ID, key, pk.Comment, nil)
+		err = dbpool.InsertPublicKey(user.ID, key, pk.Comment)
 		if err != nil {
 			_, _ = fmt.Fprintf(s.Stderr(), "error: could not insert pubkey: %s (%s)\n", err.Error(), key)
 			logger.Error("could not insert pubkey", "err", err.Error())
+1 -1 pkg/db/db.go link 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

diff --git a/pkg/db/db.go b/pkg/db/db.go
index f418c75..b237899 100644
--- a/pkg/db/db.go
+++ b/pkg/db/db.go
@@ -390,7 +390,7 @@ var DenyList = []string{
 type DB interface {
 	RegisterUser(name, pubkey, comment string) (*User, error)
 	UpdatePublicKey(pubkeyID, name string) (*PublicKey, error)
-	InsertPublicKey(userID, pubkey, name string, tx *sql.Tx) error
+	InsertPublicKey(userID, pubkey, name string) error
 	FindKeysForUser(user *User) ([]*PublicKey, error)
 	RemoveKeys(pubkeyIDs []string) error
+61 -299 pkg/db/postgres/storage.go link 
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615

diff --git a/pkg/db/postgres/storage.go b/pkg/db/postgres/storage.go
index 8c6c0c1..902ee6c 100644
--- a/pkg/db/postgres/storage.go
+++ b/pkg/db/postgres/storage.go
@@ -1,7 +1,6 @@
 package postgres
 
 import (
-	"context"
 	"database/sql"
 	"errors"
 	"fmt"
@@ -125,10 +124,10 @@ var (
 const (
 	sqlSelectPublicKey         = `SELECT id, user_id, name, public_key, created_at FROM public_keys WHERE public_key = $1`
 	sqlSelectPublicKeys        = `SELECT id, user_id, name, public_key, created_at FROM public_keys WHERE user_id = $1 ORDER BY created_at ASC`
-	sqlSelectUser              = `SELECT id, name, created_at FROM app_users WHERE id = $1`
+	sqlSelectUser              = `SELECT id, COALESCE(name, '') as name, created_at FROM app_users WHERE id = $1`
 	sqlSelectUserForName       = `SELECT id, name, created_at FROM app_users WHERE name = $1`
 	sqlSelectUserForNameAndKey = `SELECT app_users.id, app_users.name, app_users.created_at, public_keys.id as pk_id, public_keys.public_key, public_keys.created_at as pk_created_at FROM app_users LEFT JOIN public_keys ON public_keys.user_id = app_users.id WHERE app_users.name = $1 AND public_keys.public_key = $2`
-	sqlSelectUsers             = `SELECT id, name, created_at FROM app_users ORDER BY name ASC`
+	sqlSelectUsers             = `SELECT id, COALESCE(name, '') as name, created_at FROM app_users ORDER BY name ASC`
 
 	sqlSelectUserForToken = `
 	SELECT app_users.id, app_users.name, app_users.created_at
@@ -313,30 +312,21 @@ func (me *PsqlDB) RegisterUser(username, pubkey, comment string) (*db.User, erro
 		return nil, err
 	}
 
-	ctx := context.Background()
-	tx, err := me.Db.BeginTx(ctx, nil)
+	tx, err := me.Db.Beginx()
 	if err != nil {
 		return nil, err
 	}
 	defer func() {
-		err = tx.Rollback()
-	}()
-
-	stmt, err := tx.Prepare(sqlInsertUser)
-	if err != nil {
-		return nil, err
-	}
-	defer func() {
-		_ = stmt.Close()
+		_ = tx.Rollback()
 	}()
 
 	var id string
-	err = stmt.QueryRow(lowerName).Scan(&id)
+	err = tx.QueryRow(sqlInsertUser, lowerName).Scan(&id)
 	if err != nil {
 		return nil, err
 	}
 
-	err = me.InsertPublicKey(id, pubkey, comment, tx)
+	err = me.insertPublicKeyWithTx(id, pubkey, comment, tx)
 	if err != nil {
 		return nil, err
 	}
@@ -349,23 +339,24 @@ func (me *PsqlDB) RegisterUser(username, pubkey, comment string) (*db.User, erro
 	return me.FindUserForKey(username, pubkey)
 }
 
-func (me *PsqlDB) InsertPublicKey(userID, key, name string, tx *sql.Tx) error {
+func (me *PsqlDB) insertPublicKeyWithTx(userID, key, name string, tx *sqlx.Tx) error {
 	pk, _ := me.findPublicKeyForKey(key)
 	if pk != nil {
 		return db.ErrPublicKeyTaken
 	}
 	query := `INSERT INTO public_keys (user_id, public_key, name) VALUES ($1, $2, $3)`
-	var err error
-	if tx != nil {
-		_, err = tx.Exec(query, userID, key, name)
-	} else {
-		_, err = me.Db.Exec(query, userID, key, name)
-	}
-	if err != nil {
-		return err
-	}
+	_, err := tx.Exec(query, userID, key, name)
+	return err
+}
 
-	return nil
+func (me *PsqlDB) InsertPublicKey(userID, key, name string) error {
+	pk, _ := me.findPublicKeyForKey(key)
+	if pk != nil {
+		return db.ErrPublicKeyTaken
+	}
+	query := `INSERT INTO public_keys (user_id, public_key, name) VALUES ($1, $2, $3)`
+	_, err := me.Db.Exec(query, userID, key, name)
+	return err
 }
 
 func (me *PsqlDB) UpdatePublicKey(pubkeyID, name string) (*db.PublicKey, error) {
@@ -424,50 +415,19 @@ func (me *PsqlDB) findPublicKeyForKey(key string) (*db.PublicKey, error) {
 }
 
 func (me *PsqlDB) findPublicKey(pubkeyID string) (*db.PublicKey, error) {
-	var keys []*db.PublicKey
-	rs, err := me.Db.Query(`SELECT id, user_id, name, public_key, created_at FROM public_keys WHERE id = $1`, pubkeyID)
+	pk := &db.PublicKey{}
+	err := me.Db.Get(pk, `SELECT id, user_id, name, public_key, created_at FROM public_keys WHERE id = $1`, pubkeyID)
 	if err != nil {
 		return nil, err
 	}
-
-	for rs.Next() {
-		pk := &db.PublicKey{}
-		err := rs.Scan(&pk.ID, &pk.UserID, &pk.Name, &pk.Key, &pk.CreatedAt)
-		if err != nil {
-			return nil, err
-		}
-
-		keys = append(keys, pk)
-	}
-
-	if rs.Err() != nil {
-		return nil, rs.Err()
-	}
-
-	if len(keys) == 0 {
-		return nil, errors.New("no public keys found for key provided")
-	}
-
-	return keys[0], nil
+	return pk, nil
 }
 
 func (me *PsqlDB) FindKeysForUser(user *db.User) ([]*db.PublicKey, error) {
 	var keys []*db.PublicKey
-	rs, err := me.Db.Query(sqlSelectPublicKeys, user.ID)
+	err := me.Db.Select(&keys, sqlSelectPublicKeys, user.ID)
 	if err != nil {
-		return keys, err
-	}
-	for rs.Next() {
-		pk := &db.PublicKey{}
-		err := rs.Scan(&pk.ID, &pk.UserID, &pk.Name, &pk.Key, &pk.CreatedAt)
-		if err != nil {
-			return keys, err
-		}
-
-		keys = append(keys, pk)
-	}
-	if rs.Err() != nil {
-		return keys, rs.Err()
+		return nil, err
 	}
 	return keys, nil
 }
@@ -559,15 +519,10 @@ func (me *PsqlDB) FindUserByPubkey(key string) (*db.User, error) {
 
 func (me *PsqlDB) FindUser(userID string) (*db.User, error) {
 	user := &db.User{}
-	var un sql.NullString
-	r := me.Db.QueryRow(sqlSelectUser, userID)
-	err := r.Scan(&user.ID, &un, &user.CreatedAt)
+	err := me.Db.Get(user, sqlSelectUser, userID)
 	if err != nil {
 		return nil, err
 	}
-	if un.Valid {
-		user.Name = un.String
-	}
 	return user, nil
 }
 
@@ -589,8 +544,7 @@ func (me *PsqlDB) validateName(name string) (bool, error) {
 
 func (me *PsqlDB) FindUserByName(name string) (*db.User, error) {
 	user := &db.User{}
-	r := me.Db.QueryRow(sqlSelectUserForName, strings.ToLower(name))
-	err := r.Scan(&user.ID, &user.Name, &user.CreatedAt)
+	err := me.Db.Get(user, sqlSelectUserForName, strings.ToLower(name))
 	if err != nil {
 		return nil, err
 	}
@@ -613,13 +567,10 @@ func (me *PsqlDB) findUserForNameAndKey(name string, key string) (*db.User, erro
 
 func (me *PsqlDB) FindUserForToken(token string) (*db.User, error) {
 	user := &db.User{}
-
-	r := me.Db.QueryRow(sqlSelectUserForToken, token)
-	err := r.Scan(&user.ID, &user.Name, &user.CreatedAt)
+	err := me.Db.Get(user, sqlSelectUserForToken, token)
 	if err != nil {
 		return nil, err
 	}
-
 	return user, nil
 }
 
@@ -1126,37 +1077,19 @@ func (me *PsqlDB) FindVisitSiteList(opts *db.SummaryOpts) ([]*db.VisitUrl, error
 
 func (me *PsqlDB) FindUsers() ([]*db.User, error) {
 	var users []*db.User
-	rs, err := me.Db.Query(sqlSelectUsers)
+	err := me.Db.Select(&users, sqlSelectUsers)
 	if err != nil {
-		return users, err
-	}
-	for rs.Next() {
-		var name sql.NullString
-		user := &db.User{}
-		err := rs.Scan(
-			&user.ID,
-			&name,
-			&user.CreatedAt,
-		)
-		if err != nil {
-			return users, err
-		}
-		user.Name = name.String
-
-		users = append(users, user)
-	}
-	if rs.Err() != nil {
-		return users, rs.Err()
+		return nil, err
 	}
 	return users, nil
 }
 
-func (me *PsqlDB) removeTagsForPost(tx *sql.Tx, postID string) error {
+func (me *PsqlDB) removeTagsForPost(tx *sqlx.Tx, postID string) error {
 	_, err := tx.Exec(sqlRemoveTagsByPost, postID)
 	return err
 }
 
-func (me *PsqlDB) insertTagsForPost(tx *sql.Tx, tags []string, postID string) ([]string, error) {
+func (me *PsqlDB) insertTagsForPost(tx *sqlx.Tx, tags []string, postID string) ([]string, error) {
 	ids := make([]string, 0)
 	for _, tag := range tags {
 		id := ""
@@ -1171,13 +1104,12 @@ func (me *PsqlDB) insertTagsForPost(tx *sql.Tx, tags []string, postID string) ([
 }
 
 func (me *PsqlDB) ReplaceTagsForPost(tags []string, postID string) error {
-	ctx := context.Background()
-	tx, err := me.Db.BeginTx(ctx, nil)
+	tx, err := me.Db.Beginx()
 	if err != nil {
 		return err
 	}
 	defer func() {
-		err = tx.Rollback()
+		_ = tx.Rollback()
 	}()
 
 	err = me.removeTagsForPost(tx, postID)
@@ -1194,12 +1126,12 @@ func (me *PsqlDB) ReplaceTagsForPost(tags []string, postID string) error {
 	return err
 }
 
-func (me *PsqlDB) removeAliasesForPost(tx *sql.Tx, postID string) error {
+func (me *PsqlDB) removeAliasesForPost(tx *sqlx.Tx, postID string) error {
 	_, err := tx.Exec(sqlRemoveAliasesByPost, postID)
 	return err
 }
 
-func (me *PsqlDB) insertAliasesForPost(tx *sql.Tx, aliases []string, postID string) ([]string, error) {
+func (me *PsqlDB) insertAliasesForPost(tx *sqlx.Tx, aliases []string, postID string) ([]string, error) {
 	// hardcoded
 	denyList := []string{
 		"rss",
@@ -1241,13 +1173,12 @@ func (me *PsqlDB) insertAliasesForPost(tx *sql.Tx, aliases []string, postID stri
 }
 
 func (me *PsqlDB) ReplaceAliasesForPost(aliases []string, postID string) error {
-	ctx := context.Background()
-	tx, err := me.Db.BeginTx(ctx, nil)
+	tx, err := me.Db.Beginx()
 	if err != nil {
 		return err
 	}
 	defer func() {
-		err = tx.Rollback()
+		_ = tx.Rollback()
 	}()
 
 	err = me.removeAliasesForPost(tx, postID)
@@ -1342,24 +1273,10 @@ func (me *PsqlDB) FindPopularTags(space string) ([]string, error) {
 
 func (me *PsqlDB) FindFeature(userID string, feature string) (*db.FeatureFlag, error) {
 	ff := &db.FeatureFlag{}
-	// payment history is allowed to be null
-	// https://devtidbits.com/2020/08/03/go-sql-error-converting-null-to-string-is-unsupported/
-	var paymentHistoryID sql.NullString
-	err := me.Db.QueryRow(sqlSelectFeatureForUser, userID, feature).Scan(
-		&ff.ID,
-		&ff.UserID,
-		&paymentHistoryID,
-		&ff.Name,
-		&ff.Data,
-		&ff.CreatedAt,
-		&ff.ExpiresAt,
-	)
+	err := me.Db.Get(ff, sqlSelectFeatureForUser, userID, feature)
 	if err != nil {
 		return nil, err
 	}
-
-	ff.PaymentHistoryID = paymentHistoryID
-
 	return ff, nil
 }
 
@@ -1371,31 +1288,9 @@ func (me *PsqlDB) FindFeaturesForUser(userID string) ([]*db.FeatureFlag, error)
 		FROM feature_flags
 		WHERE user_id=$1
 		ORDER BY name, expires_at DESC;`
-	rs, err := me.Db.Query(query, userID)
+	err := me.Db.Select(&features, query, userID)
 	if err != nil {
-		return features, err
-	}
-	for rs.Next() {
-		var paymentHistoryID sql.NullString
-		ff := &db.FeatureFlag{}
-		err := rs.Scan(
-			&ff.ID,
-			&ff.UserID,
-			&paymentHistoryID,
-			&ff.Name,
-			&ff.Data,
-			&ff.CreatedAt,
-			&ff.ExpiresAt,
-		)
-		if err != nil {
-			return features, err
-		}
-		ff.PaymentHistoryID = paymentHistoryID
-
-		features = append(features, ff)
-	}
-	if rs.Err() != nil {
-		return features, rs.Err()
+		return nil, err
 	}
 	return features, nil
 }
@@ -1409,13 +1304,12 @@ func (me *PsqlDB) HasFeatureForUser(userID string, feature string) bool {
 }
 
 func (me *PsqlDB) InsertFeedItems(postID string, items []*db.FeedItem) error {
-	ctx := context.Background()
-	tx, err := me.Db.BeginTx(ctx, nil)
+	tx, err := me.Db.Beginx()
 	if err != nil {
 		return err
 	}
 	defer func() {
-		err = tx.Rollback()
+		_ = tx.Rollback()
 	}()
 
 	for _, item := range items {
@@ -1438,33 +1332,11 @@ func (me *PsqlDB) InsertFeedItems(postID string, items []*db.FeedItem) error {
 }
 
 func (me *PsqlDB) FindFeedItemsByPostID(postID string) ([]*db.FeedItem, error) {
-	// sqlSelectFeedItemsByPost
-	items := make([]*db.FeedItem, 0)
-	rs, err := me.Db.Query(sqlSelectFeedItemsByPost, postID)
+	var items []*db.FeedItem
+	err := me.Db.Select(&items, sqlSelectFeedItemsByPost, postID)
 	if err != nil {
-		return items, err
-	}
-
-	for rs.Next() {
-		item := &db.FeedItem{}
-		err := rs.Scan(
-			&item.ID,
-			&item.PostID,
-			&item.GUID,
-			&item.Data,
-			&item.CreatedAt,
-		)
-		if err != nil {
-			return items, err
-		}
-
-		items = append(items, item)
-	}
-
-	if rs.Err() != nil {
-		return items, rs.Err()
+		return nil, err
 	}
-
 	return items, nil
 }
 
@@ -1488,21 +1360,10 @@ func (me *PsqlDB) UpdateProject(userID, name string) error {
 
 func (me *PsqlDB) FindProjectByName(userID, name string) (*db.Project, error) {
 	project := &db.Project{}
-	r := me.Db.QueryRow(sqlFindProjectByName, userID, name)
-	err := r.Scan(
-		&project.ID,
-		&project.UserID,
-		&project.Name,
-		&project.ProjectDir,
-		&project.Acl,
-		&project.Blocked,
-		&project.CreatedAt,
-		&project.UpdatedAt,
-	)
+	err := me.Db.Get(project, sqlFindProjectByName, userID, name)
 	if err != nil {
 		return nil, err
 	}
-
 	return project, nil
 }
 
@@ -1540,24 +1401,12 @@ func (me *PsqlDB) RemoveToken(tokenID string) error {
 }
 
 func (me *PsqlDB) FindTokensForUser(userID string) ([]*db.Token, error) {
-	var keys []*db.Token
-	rs, err := me.Db.Query(sqlSelectTokensForUser, userID)
+	var tokens []*db.Token
+	err := me.Db.Select(&tokens, sqlSelectTokensForUser, userID)
 	if err != nil {
-		return keys, err
-	}
-	for rs.Next() {
-		pk := &db.Token{}
-		err := rs.Scan(&pk.ID, &pk.UserID, &pk.Name, &pk.CreatedAt, &pk.ExpiresAt)
-		if err != nil {
-			return keys, err
-		}
-
-		keys = append(keys, pk)
-	}
-	if rs.Err() != nil {
-		return keys, rs.Err()
+		return nil, err
 	}
-	return keys, nil
+	return tokens, nil
 }
 
 func (me *PsqlDB) InsertFeature(userID, name string, expiresAt time.Time) (*db.FeatureFlag, error) {
@@ -1602,13 +1451,12 @@ func (me *PsqlDB) AddPicoPlusUser(username, email, paymentType, txId string) err
 		return err
 	}
 
-	ctx := context.Background()
-	tx, err := me.Db.BeginTx(ctx, nil)
+	tx, err := me.Db.Beginx()
 	if err != nil {
 		return err
 	}
 	defer func() {
-		err = tx.Rollback()
+		_ = tx.Rollback()
 	}()
 
 	var paymentHistoryId sql.NullString
@@ -1688,60 +1536,24 @@ func (me *PsqlDB) InsertTunsEventLog(log *db.TunsEventLog) error {
 }
 
 func (me *PsqlDB) FindTunsEventLogsByAddr(userID, addr string) ([]*db.TunsEventLog, error) {
-	logs := []*db.TunsEventLog{}
-	rs, err := me.Db.Query(
+	var logs []*db.TunsEventLog
+	err := me.Db.Select(&logs,
 		`SELECT id, user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, tunnel_id, created_at
 		FROM tuns_event_logs WHERE user_id=$1 AND tunnel_id=$2 ORDER BY created_at DESC`, userID, addr)
 	if err != nil {
 		return nil, err
 	}
-
-	for rs.Next() {
-		log := db.TunsEventLog{}
-		err := rs.Scan(
-			&log.ID, &log.UserId, &log.ServerID, &log.RemoteAddr,
-			&log.EventType, &log.TunnelType, &log.ConnectionType,
-			&log.TunnelID, &log.CreatedAt,
-		)
-		if err != nil {
-			return nil, err
-		}
-		logs = append(logs, &log)
-	}
-
-	if rs.Err() != nil {
-		return nil, rs.Err()
-	}
-
 	return logs, nil
 }
 
 func (me *PsqlDB) FindTunsEventLogs(userID string) ([]*db.TunsEventLog, error) {
-	logs := []*db.TunsEventLog{}
-	rs, err := me.Db.Query(
+	var logs []*db.TunsEventLog
+	err := me.Db.Select(&logs,
 		`SELECT id, user_id, server_id, remote_addr, event_type, tunnel_type, connection_type, tunnel_id, created_at
 		FROM tuns_event_logs WHERE user_id=$1 ORDER BY created_at DESC`, userID)
 	if err != nil {
 		return nil, err
 	}
-
-	for rs.Next() {
-		log := db.TunsEventLog{}
-		err := rs.Scan(
-			&log.ID, &log.UserId, &log.ServerID, &log.RemoteAddr,
-			&log.EventType, &log.TunnelType, &log.ConnectionType,
-			&log.TunnelID, &log.CreatedAt,
-		)
-		if err != nil {
-			return nil, err
-		}
-		logs = append(logs, &log)
-	}
-
-	if rs.Err() != nil {
-		return nil, rs.Err()
-	}
-
 	return logs, nil
 }
 
@@ -1781,79 +1593,29 @@ func (me *PsqlDB) FindUserStats(userID string) (*db.UserStats, error) {
 }
 
 func (me *PsqlDB) FindAccessLogs(userID string, fromDate *time.Time) ([]*db.AccessLog, error) {
-	logs := []*db.AccessLog{}
-	rs, err := me.Db.Query(
-		`SELECT id, user_id, service, pubkey, identity, created_at FROM access_logs WHERE user_id=$1 AND created_at >= $2 ORDER BY created_at DESC`, userID, fromDate)
+	var logs []*db.AccessLog
+	err := me.Db.Select(&logs, `SELECT id, user_id, service, pubkey, identity, created_at FROM access_logs WHERE user_id=$1 AND created_at >= $2 ORDER BY created_at DESC`, userID, fromDate)
 	if err != nil {
 		return nil, err
 	}
-
-	for rs.Next() {
-		log := db.AccessLog{}
-		err := rs.Scan(
-			&log.ID, &log.UserID, &log.Service, &log.Pubkey, &log.Identity, &log.CreatedAt,
-		)
-		if err != nil {
-			return nil, err
-		}
-		logs = append(logs, &log)
-	}
-
-	if rs.Err() != nil {
-		return nil, rs.Err()
-	}
-
 	return logs, nil
 }
 
 func (me *PsqlDB) FindAccessLogsByPubkey(pubkey string, fromDate *time.Time) ([]*db.AccessLog, error) {
-	logs := []*db.AccessLog{}
-	rs, err := me.Db.Query(
-		`SELECT id, user_id, service, pubkey, identity, created_at FROM access_logs WHERE pubkey=$1 AND created_at >= $2 ORDER BY created_at DESC`, pubkey, fromDate)
+	var logs []*db.AccessLog
+	err := me.Db.Select(&logs, `SELECT id, user_id, service, pubkey, identity, created_at FROM access_logs WHERE pubkey=$1 AND created_at >= $2 ORDER BY created_at DESC`, pubkey, fromDate)
 	if err != nil {
 		return nil, err
 	}
-
-	for rs.Next() {
-		log := db.AccessLog{}
-		err := rs.Scan(
-			&log.ID, &log.UserID, &log.Service, &log.Pubkey, &log.Identity, &log.CreatedAt,
-		)
-		if err != nil {
-			return nil, err
-		}
-		logs = append(logs, &log)
-	}
-
-	if rs.Err() != nil {
-		return nil, rs.Err()
-	}
-
 	return logs, nil
 }
 
 func (me *PsqlDB) FindPubkeysInAccessLogs(userID string) ([]string, error) {
-	pubkeys := []string{}
-	rs, err := me.Db.Query(
-		`SELECT DISTINCT(pubkey) FROM access_logs WHERE user_id=$1`, userID,
-	)
+	var pubkeys []string
+	err := me.Db.Select(&pubkeys, `SELECT DISTINCT(pubkey) FROM access_logs WHERE user_id=$1`, userID)
 	if err != nil {
 		return nil, err
 	}
-
-	for rs.Next() {
-		pubkey := ""
-		err := rs.Scan(&pubkey)
-		if err != nil {
-			return nil, err
-		}
-		pubkeys = append(pubkeys, pubkey)
-	}
-
-	if rs.Err() != nil {
-		return nil, rs.Err()
-	}
-
 	return pubkeys, nil
 }
+4 -4 pkg/db/postgres/storage_test.go link 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

diff --git a/pkg/db/postgres/storage_test.go b/pkg/db/postgres/storage_test.go
index 4cfc099..bc4710c 100644
--- a/pkg/db/postgres/storage_test.go
+++ b/pkg/db/postgres/storage_test.go
@@ -342,7 +342,7 @@ func TestInsertPublicKey_Success(t *testing.T) {
 		t.Fatalf("RegisterUser failed: %v", err)
 	}
 
-	err = testDB.InsertPublicKey(user.ID, "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI secondkey", "second key", nil)
+	err = testDB.InsertPublicKey(user.ID, "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI secondkey", "second key")
 	if err != nil {
 		t.Fatalf("InsertPublicKey failed: %v", err)
 	}
@@ -361,7 +361,7 @@ func TestInsertPublicKey_Duplicate(t *testing.T) {
 
 	user, _ := testDB.RegisterUser("dupkeyowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI dupkey", "comment")
 
-	err := testDB.InsertPublicKey(user.ID, "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI dupkey", "same key", nil)
+	err := testDB.InsertPublicKey(user.ID, "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI dupkey", "same key")
 	if err == nil {
 		t.Error("expected error for duplicate key, got nil")
 	}
@@ -385,7 +385,7 @@ func TestFindKeysForUser(t *testing.T) {
 	cleanupTestData(t)
 
 	user, _ := testDB.RegisterUser("multikeyowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI multikeyowner1", "key1")
-	_ = testDB.InsertPublicKey(user.ID, "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI multikeyowner2", "key2", nil)
+	_ = testDB.InsertPublicKey(user.ID, "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI multikeyowner2", "key2")
 
 	keys, err := testDB.FindKeysForUser(user)
 	if err != nil {
@@ -400,7 +400,7 @@ func TestRemoveKeys(t *testing.T) {
 	cleanupTestData(t)
 
 	user, _ := testDB.RegisterUser("removekeyowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI removekeyowner", "key1")
-	_ = testDB.InsertPublicKey(user.ID, "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI removekeyowner2", "key2", nil)
+	_ = testDB.InsertPublicKey(user.ID, "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI removekeyowner2", "key2")
 
 	keys, _ := testDB.FindKeysForUser(user)
 	if len(keys) != 2 {
+1 -2 pkg/db/stub/stub.go link 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

diff --git a/pkg/db/stub/stub.go b/pkg/db/stub/stub.go
index e25e7ee..3f6be8f 100644
--- a/pkg/db/stub/stub.go
+++ b/pkg/db/stub/stub.go
@@ -1,7 +1,6 @@
 package stub
 
 import (
-	"database/sql"
 	"fmt"
 	"log/slog"
 	"time"
@@ -33,7 +32,7 @@ func (me *StubDB) UpdatePublicKey(pubkeyID, name string) (*db.PublicKey, error)
 	return nil, errNotImpl
 }
 
-func (me *StubDB) InsertPublicKey(userID, key, name string, tx *sql.Tx) error {
+func (me *StubDB) InsertPublicKey(userID, key, name string) error {
 	return errNotImpl
 }
+1 -1 pkg/tui/pubkeys.go link 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

diff --git a/pkg/tui/pubkeys.go b/pkg/tui/pubkeys.go
index f549d91..cd92853 100644
--- a/pkg/tui/pubkeys.go
+++ b/pkg/tui/pubkeys.go
@@ -256,7 +256,7 @@ func (m *AddKeyPage) addPubkey(pubkey string) error {
 	key := utils.KeyForKeyText(pk)
 
 	return m.shared.Dbpool.InsertPublicKey(
-		m.shared.User.ID, key, comment, nil,
+		m.shared.User.ID, key, comment,
 	)
 }

refactor: replace custom sql functions with sqlx

+35 -96 pkg/db/postgres/storage.go link 
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322

diff --git a/pkg/db/postgres/storage.go b/pkg/db/postgres/storage.go
index 902ee6c..75f3fae 100644
--- a/pkg/db/postgres/storage.go
+++ b/pkg/db/postgres/storage.go
@@ -222,34 +222,6 @@ type RowScanner interface {
 	Scan(dest ...any) error
 }
 
-func CreatePostFromRow(r RowScanner) (*db.Post, error) {
-	post := &db.Post{}
-	err := r.Scan(
-		&post.ID,
-		&post.UserID,
-		&post.Username,
-		&post.Filename,
-		&post.Slug,
-		&post.Title,
-		&post.Text,
-		&post.Description,
-		&post.CreatedAt,
-		&post.PublishAt,
-		&post.UpdatedAt,
-		&post.Hidden,
-		&post.FileSize,
-		&post.MimeType,
-		&post.Shasum,
-		&post.Data,
-		&post.ExpiresAt,
-		&post.Views,
-	)
-	if err != nil {
-		return nil, err
-	}
-	return post, nil
-}
-
 func CreatePostWithTagsFromRow(r RowScanner) (*db.Post, error) {
 	post := &db.Post{}
 	tagStr := ""
@@ -380,10 +352,11 @@ func (me *PsqlDB) UpdatePublicKey(pubkeyID, name string) (*db.PublicKey, error)
 
 func (me *PsqlDB) findPublicKeyForKey(key string) (*db.PublicKey, error) {
 	var keys []*db.PublicKey
-	rs, err := me.Db.Query(sqlSelectPublicKey, key)
+	rs, err := me.Db.Queryx(sqlSelectPublicKey, key)
 	if err != nil {
 		return nil, err
 	}
+	defer func() { _ = rs.Close() }()
 
 	for rs.Next() {
 		pk := &db.PublicKey{}
@@ -440,7 +413,7 @@ func (me *PsqlDB) RemoveKeys(keyIDs []string) error {
 
 func (me *PsqlDB) FindUsersWithPost(space string) ([]*db.User, error) {
 	var users []*db.User
-	rs, err := me.Db.Query(
+	rs, err := me.Db.Queryx(
 		`SELECT u.id, u.name, u.created_at
 		FROM app_users u
 		INNER JOIN posts ON u.id=posts.user_id
@@ -451,6 +424,7 @@ func (me *PsqlDB) FindUsersWithPost(space string) ([]*db.User, error) {
 	if err != nil {
 		return users, err
 	}
+	defer func() { _ = rs.Close() }()
 	for rs.Next() {
 		var name sql.NullString
 		user := &db.User{}
@@ -603,16 +577,15 @@ func (me *PsqlDB) FindPostWithSlug(slug string, user_id string, space string) (*
 }
 
 func (me *PsqlDB) FindPost(postID string) (*db.Post, error) {
-	r := me.Db.QueryRow(sqlSelectPost, postID)
-	post, err := CreatePostFromRow(r)
+	post := &db.Post{}
+	err := me.Db.Get(post, sqlSelectPost, postID)
 	if err != nil {
 		return nil, err
 	}
-
 	return post, nil
 }
 
-func (me *PsqlDB) postPager(rs *sql.Rows, pageNum int, space string, tag string) (*db.Paginate[*db.Post], error) {
+func (me *PsqlDB) postPager(rs *sqlx.Rows, pageNum int, space string, tag string) (*db.Paginate[*db.Post], error) {
 	var posts []*db.Post
 	for rs.Next() {
 		post := &db.Post{}
@@ -659,10 +632,11 @@ func (me *PsqlDB) postPager(rs *sql.Rows, pageNum int, space string, tag string)
 }
 
 func (me *PsqlDB) FindPostsForFeed(page *db.Pager, space string) (*db.Paginate[*db.Post], error) {
-	rs, err := me.Db.Query(sqlSelectPostsByRank, page.Num, page.Num*page.Page, space)
+	rs, err := me.Db.Queryx(sqlSelectPostsByRank, page.Num, page.Num*page.Page, space)
 	if err != nil {
 		return nil, err
 	}
+	defer func() { _ = rs.Close() }()
 	return me.postPager(rs, page.Num, space, "")
 }
 
@@ -724,7 +698,7 @@ func (me *PsqlDB) RemovePosts(postIDs []string) error {
 
 func (me *PsqlDB) FindPostsForUser(page *db.Pager, userID string, space string) (*db.Paginate[*db.Post], error) {
 	var posts []*db.Post
-	rs, err := me.Db.Query(
+	rs, err := me.Db.Queryx(
 		sqlSelectPostsForUser,
 		userID,
 		space,
@@ -734,6 +708,7 @@ func (me *PsqlDB) FindPostsForUser(page *db.Pager, userID string, space string)
 	if err != nil {
 		return nil, err
 	}
+	defer func() { _ = rs.Close() }()
 	for rs.Next() {
 		post, err := CreatePostWithTagsFromRow(rs)
 		if err != nil {
@@ -762,60 +737,27 @@ func (me *PsqlDB) FindPostsForUser(page *db.Pager, userID string, space string)
 
 func (me *PsqlDB) FindAllPostsForUser(userID string, space string) ([]*db.Post, error) {
 	var posts []*db.Post
-	rs, err := me.Db.Query(sqlSelectAllPostsForUser, userID, space)
+	err := me.Db.Select(&posts, sqlSelectAllPostsForUser, userID, space)
 	if err != nil {
-		return posts, err
-	}
-	for rs.Next() {
-		post, err := CreatePostFromRow(rs)
-		if err != nil {
-			return nil, err
-		}
-
-		posts = append(posts, post)
-	}
-	if rs.Err() != nil {
-		return posts, rs.Err()
+		return nil, err
 	}
 	return posts, nil
 }
 
 func (me *PsqlDB) FindPosts() ([]*db.Post, error) {
 	var posts []*db.Post
-	rs, err := me.Db.Query(sqlSelectPosts)
+	err := me.Db.Select(&posts, sqlSelectPosts)
 	if err != nil {
-		return posts, err
-	}
-	for rs.Next() {
-		post, err := CreatePostFromRow(rs)
-		if err != nil {
-			return nil, err
-		}
-
-		posts = append(posts, post)
-	}
-	if rs.Err() != nil {
-		return posts, rs.Err()
+		return nil, err
 	}
 	return posts, nil
 }
 
 func (me *PsqlDB) FindExpiredPosts(space string) ([]*db.Post, error) {
 	var posts []*db.Post
-	rs, err := me.Db.Query(sqlSelectExpiredPosts, space)
+	err := me.Db.Select(&posts, sqlSelectExpiredPosts, space)
 	if err != nil {
-		return posts, err
-	}
-	for rs.Next() {
-		post, err := CreatePostFromRow(rs)
-		if err != nil {
-			return nil, err
-		}
-
-		posts = append(posts, post)
-	}
-	if rs.Err() != nil {
-		return posts, rs.Err()
+		return nil, err
 	}
 	return posts, nil
 }
@@ -877,10 +819,11 @@ func (me *PsqlDB) visitUnique(opts *db.SummaryOpts) ([]*db.VisitInterval, error)
 	GROUP BY interval_start`, opts.Interval, where)
 
 	intervals := []*db.VisitInterval{}
-	rs, err := me.Db.Query(uniqueVisitors, opts.Origin, with, opts.UserID)
+	rs, err := me.Db.Queryx(uniqueVisitors, opts.Origin, with, opts.UserID)
 	if err != nil {
 		return nil, err
 	}
+	defer func() { _ = rs.Close() }()
 
 	for rs.Next() {
 		interval := &db.VisitInterval{}
@@ -912,10 +855,11 @@ func (me *PsqlDB) visitReferer(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
 	LIMIT 10`, where)
 
 	intervals := []*db.VisitUrl{}
-	rs, err := me.Db.Query(topUrls, opts.Origin, with, opts.UserID)
+	rs, err := me.Db.Queryx(topUrls, opts.Origin, with, opts.UserID)
 	if err != nil {
 		return nil, err
 	}
+	defer func() { _ = rs.Close() }()
 
 	for rs.Next() {
 		interval := &db.VisitUrl{}
@@ -947,10 +891,11 @@ func (me *PsqlDB) visitUrl(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
 	LIMIT 10`, where)
 
 	intervals := []*db.VisitUrl{}
-	rs, err := me.Db.Query(topUrls, opts.Origin, with, opts.UserID)
+	rs, err := me.Db.Queryx(topUrls, opts.Origin, with, opts.UserID)
 	if err != nil {
 		return nil, err
 	}
+	defer func() { _ = rs.Close() }()
 
 	for rs.Next() {
 		interval := &db.VisitUrl{}
@@ -986,10 +931,11 @@ func (me *PsqlDB) VisitUrlNotFound(opts *db.SummaryOpts) ([]*db.VisitUrl, error)
 	LIMIT %d`, where, limit)
 
 	intervals := []*db.VisitUrl{}
-	rs, err := me.Db.Query(topUrls, opts.Origin, with, opts.UserID)
+	rs, err := me.Db.Queryx(topUrls, opts.Origin, with, opts.UserID)
 	if err != nil {
 		return nil, err
 	}
+	defer func() { _ = rs.Close() }()
 
 	for rs.Next() {
 		interval := &db.VisitUrl{}
@@ -1019,10 +965,11 @@ func (me *PsqlDB) visitHost(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
 	ORDER BY host_count DESC`
 
 	intervals := []*db.VisitUrl{}
-	rs, err := me.Db.Query(topUrls, opts.UserID)
+	rs, err := me.Db.Queryx(topUrls, opts.UserID)
 	if err != nil {
 		return nil, err
 	}
+	defer func() { _ = rs.Close() }()
 
 	for rs.Next() {
 		interval := &db.VisitUrl{}
@@ -1197,7 +1144,8 @@ func (me *PsqlDB) ReplaceAliasesForPost(aliases []string, postID string) error {
 
 func (me *PsqlDB) FindUserPostsByTag(page *db.Pager, tag, userID, space string) (*db.Paginate[*db.Post], error) {
 	var posts []*db.Post
-	rs, err := me.Db.Query(
+	err := me.Db.Select(
+		&posts,
 		sqlSelectUserPostsByTag,
 		userID,
 		tag,
@@ -1208,18 +1156,6 @@ func (me *PsqlDB) FindUserPostsByTag(page *db.Pager, tag, userID, space string)
 	if err != nil {
 		return nil, err
 	}
-	for rs.Next() {
-		post, err := CreatePostFromRow(rs)
-		if err != nil {
-			return nil, err
-		}
-
-		posts = append(posts, post)
-	}
-
-	if rs.Err() != nil {
-		return nil, rs.Err()
-	}
 
 	var count int
 	err = me.Db.QueryRow(sqlSelectPostCount, space).Scan(&count)
@@ -1235,7 +1171,7 @@ func (me *PsqlDB) FindUserPostsByTag(page *db.Pager, tag, userID, space string)
 }
 
 func (me *PsqlDB) FindPostsByTag(pager *db.Pager, tag, space string) (*db.Paginate[*db.Post], error) {
-	rs, err := me.Db.Query(
+	rs, err := me.Db.Queryx(
 		sqlSelectPostsByTag,
 		pager.Num,
 		pager.Num*pager.Page,
@@ -1245,16 +1181,18 @@ func (me *PsqlDB) FindPostsByTag(pager *db.Pager, tag, space string) (*db.Pagina
 	if err != nil {
 		return nil, err
 	}
+	defer func() { _ = rs.Close() }()
 
 	return me.postPager(rs, pager.Num, space, tag)
 }
 
 func (me *PsqlDB) FindPopularTags(space string) ([]string, error) {
 	tags := make([]string, 0)
-	rs, err := me.Db.Query(sqlSelectPopularTags, space)
+	rs, err := me.Db.Queryx(sqlSelectPopularTags, space)
 	if err != nil {
 		return tags, err
 	}
+	defer func() { _ = rs.Close() }()
 	for rs.Next() {
 		name := ""
 		tally := 0
@@ -1559,10 +1497,11 @@ func (me *PsqlDB) FindTunsEventLogs(userID string) ([]*db.TunsEventLog, error) {
 
 func (me *PsqlDB) FindUserStats(userID string) (*db.UserStats, error) {
 	stats := db.UserStats{}
-	rs, err := me.Db.Query(`SELECT cur_space, count(id), min(created_at), max(created_at), max(updated_at) FROM posts WHERE user_id=$1 GROUP BY cur_space`, userID)
+	rs, err := me.Db.Queryx(`SELECT cur_space, count(id), min(created_at), max(created_at), max(updated_at) FROM posts WHERE user_id=$1 GROUP BY cur_space`, userID)
 	if err != nil {
 		return nil, err
 	}
+	defer func() { _ = rs.Close() }()
 
 	for rs.Next() {
 		stat := db.UserServiceStats{}

refactor: inline all sql queries

+181 -232 pkg/db/postgres/storage.go link 
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708

diff --git a/pkg/db/postgres/storage.go b/pkg/db/postgres/storage.go
index 75f3fae..49e8340 100644
--- a/pkg/db/postgres/storage.go
+++ b/pkg/db/postgres/storage.go
@@ -23,196 +23,6 @@ var SelectPost = `
 	posts.id, user_id, app_users.name, filename, slug, title, text, description,
 	posts.created_at, publish_at, posts.updated_at, hidden, file_size, mime_type, shasum, data, expires_at, views`
 
-var (
-	sqlSelectPosts = fmt.Sprintf(`
-	SELECT %s
-	FROM posts
-	LEFT JOIN app_users ON app_users.id = posts.user_id`, SelectPost)
-
-	sqlSelectPostWithFilename = fmt.Sprintf(`
-	SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
-	FROM posts
-	LEFT JOIN app_users ON app_users.id = posts.user_id
-	LEFT JOIN post_tags ON post_tags.post_id = posts.id
-	WHERE filename = $1 AND user_id = $2 AND cur_space = $3
-	GROUP BY %s`, SelectPost, SelectPost)
-
-	sqlSelectPostWithSlug = fmt.Sprintf(`
-	SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
-	FROM posts
-	LEFT JOIN app_users ON app_users.id = posts.user_id
-	LEFT JOIN post_tags ON post_tags.post_id = posts.id
-	WHERE slug = $1 AND user_id = $2 AND cur_space = $3
-	GROUP BY %s`, SelectPost, SelectPost)
-
-	sqlSelectPost = fmt.Sprintf(`
-	SELECT %s
-	FROM posts
-	LEFT JOIN app_users ON app_users.id = posts.user_id
-	WHERE posts.id = $1`, SelectPost)
-
-	sqlSelectExpiredPosts = fmt.Sprintf(`
-		SELECT %s
-		FROM posts
-		LEFT JOIN app_users ON app_users.id = posts.user_id
-		WHERE
-			cur_space = $1 AND
-			expires_at <= now();
-	`, SelectPost)
-
-	sqlSelectPostsForUser = fmt.Sprintf(`
-	SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
-	FROM posts
-	LEFT JOIN app_users ON app_users.id = posts.user_id
-	LEFT JOIN post_tags ON post_tags.post_id = posts.id
-	WHERE
-		hidden = FALSE AND
-		user_id = $1 AND
-		publish_at::date <= CURRENT_DATE AND
-		cur_space = $2
-	GROUP BY %s
-	ORDER BY publish_at DESC, slug DESC
-	LIMIT $3 OFFSET $4`, SelectPost, SelectPost)
-
-	sqlSelectAllPostsForUser = fmt.Sprintf(`
-	SELECT %s
-	FROM posts
-	LEFT JOIN app_users ON app_users.id = posts.user_id
-	WHERE
-		user_id = $1 AND
-		cur_space = $2
-	ORDER BY publish_at DESC`, SelectPost)
-
-	sqlSelectPostsByTag = `
-	SELECT
-		posts.id,
-		user_id,
-		filename,
-		slug,
-		title,
-		text,
-		description,
-		publish_at,
-		app_users.name as username,
-		posts.updated_at,
-		posts.mime_type
-	FROM posts
-	LEFT JOIN app_users ON app_users.id = posts.user_id
-	LEFT JOIN post_tags ON post_tags.post_id = posts.id
-	WHERE
-		post_tags.name = $3 AND
-		publish_at::date <= CURRENT_DATE AND
-		cur_space = $4
-	ORDER BY publish_at DESC
-	LIMIT $1 OFFSET $2`
-
-	sqlSelectUserPostsByTag = fmt.Sprintf(`
-	SELECT %s
-	FROM posts
-	LEFT JOIN app_users ON app_users.id = posts.user_id
-	LEFT JOIN post_tags ON post_tags.post_id = posts.id
-	WHERE
-		hidden = FALSE AND
-		user_id = $1 AND
-		(post_tags.name = $2 OR hidden = true) AND
-		publish_at::date <= CURRENT_DATE AND
-		cur_space = $3
-	ORDER BY publish_at DESC
-	LIMIT $4 OFFSET $5`, SelectPost)
-)
-
-const (
-	sqlSelectPublicKey         = `SELECT id, user_id, name, public_key, created_at FROM public_keys WHERE public_key = $1`
-	sqlSelectPublicKeys        = `SELECT id, user_id, name, public_key, created_at FROM public_keys WHERE user_id = $1 ORDER BY created_at ASC`
-	sqlSelectUser              = `SELECT id, COALESCE(name, '') as name, created_at FROM app_users WHERE id = $1`
-	sqlSelectUserForName       = `SELECT id, name, created_at FROM app_users WHERE name = $1`
-	sqlSelectUserForNameAndKey = `SELECT app_users.id, app_users.name, app_users.created_at, public_keys.id as pk_id, public_keys.public_key, public_keys.created_at as pk_created_at FROM app_users LEFT JOIN public_keys ON public_keys.user_id = app_users.id WHERE app_users.name = $1 AND public_keys.public_key = $2`
-	sqlSelectUsers             = `SELECT id, COALESCE(name, '') as name, created_at FROM app_users ORDER BY name ASC`
-
-	sqlSelectUserForToken = `
-	SELECT app_users.id, app_users.name, app_users.created_at
-	FROM app_users
-	LEFT JOIN tokens ON tokens.user_id = app_users.id
-	WHERE tokens.token = $1 AND tokens.expires_at > NOW()`
-	sqlInsertToken              = `INSERT INTO tokens (user_id, name) VALUES($1, $2) RETURNING token;`
-	sqlRemoveToken              = `DELETE FROM tokens WHERE id = $1`
-	sqlSelectTokensForUser      = `SELECT id, user_id, name, created_at, expires_at FROM tokens WHERE user_id = $1`
-	sqlSelectTokenByNameForUser = `SELECT token FROM tokens WHERE user_id = $1 AND name = $2`
-
-	sqlSelectFeatureForUser = `SELECT id, user_id, payment_history_id, name, data, created_at, expires_at FROM feature_flags WHERE user_id = $1 AND name = $2 ORDER BY expires_at DESC LIMIT 1`
-
-	sqlSelectPostIdByAliasSlug = `SELECT post_id FROM post_aliases WHERE slug = $1`
-	sqlSelectTagPostCount      = `
-	SELECT count(posts.id)
-	FROM posts
-	LEFT JOIN post_tags ON post_tags.post_id = posts.id
-	WHERE hidden = FALSE AND cur_space=$1 and post_tags.name = $2`
-	sqlSelectPostCount = `SELECT count(id) FROM posts WHERE hidden = FALSE AND cur_space=$1`
-	// add some users to deny list since they are robogenerating a bunch of posts
-	// per day and are creating a lot of noise.
-	sqlSelectPostsByRank = `
-	SELECT *
-	FROM (
-	    SELECT DISTINCT ON (posts.user_id)
-	        posts.id,
-	        posts.user_id,
-	        posts.filename,
-	        posts.slug,
-	        posts.title,
-	        posts.text,
-	        posts.description,
-	        posts.publish_at,
-	        app_users.name AS username,
-	        posts.updated_at,
-	        posts.mime_type
-	    FROM posts
-	    LEFT JOIN app_users ON app_users.id = posts.user_id
-	    WHERE
-	        hidden = FALSE
-	        AND publish_at::date <= CURRENT_DATE
-	        AND cur_space = $3
-	    ORDER BY posts.user_id, publish_at DESC
-	) AS latest_posts
-	ORDER BY publish_at DESC
-	LIMIT $1 OFFSET $2`
-
-	sqlSelectPopularTags = `
-	SELECT name, count(post_id) as "tally"
-	FROM post_tags
-	LEFT JOIN posts ON posts.id = post_id
-	WHERE posts.cur_space = $1
-	GROUP BY name
-	ORDER BY tally DESC
-	LIMIT 5`
-	sqlSelectFeedItemsByPost = `SELECT id, post_id, guid, data, created_at FROM feed_items WHERE post_id=$1`
-
-	sqlInsertPost = `
-	INSERT INTO posts
-		(user_id, filename, slug, title, text, description, publish_at, hidden, cur_space,
-		file_size, mime_type, shasum, data, expires_at, updated_at)
-	VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
-	RETURNING id`
-	sqlInsertUser      = `INSERT INTO app_users (name) VALUES($1) returning id`
-	sqlInsertTag       = `INSERT INTO post_tags (post_id, name) VALUES($1, $2) RETURNING id;`
-	sqlInsertAliases   = `INSERT INTO post_aliases (post_id, slug) VALUES($1, $2) RETURNING id;`
-	sqlInsertFeedItems = `INSERT INTO feed_items (post_id, guid, data) VALUES ($1, $2, $3) RETURNING id;`
-
-	sqlUpdatePost = `
-	UPDATE posts
-	SET slug = $1, title = $2, text = $3, description = $4, updated_at = $5, publish_at = $6,
-		file_size = $7, shasum = $8, data = $9, hidden = $11, expires_at = $12
-	WHERE id = $10`
-
-	sqlRemoveAliasesByPost = `DELETE FROM post_aliases WHERE post_id = $1`
-	sqlRemoveTagsByPost    = `DELETE FROM post_tags WHERE post_id = $1`
-	sqlRemovePosts         = `DELETE FROM posts WHERE id = ANY($1::uuid[])`
-	sqlRemoveKeys          = `DELETE FROM public_keys WHERE id = ANY($1::uuid[])`
-
-	sqlInsertProject     = `INSERT INTO projects (user_id, name, project_dir) VALUES ($1, $2, $3) RETURNING id;`
-	sqlUpdateProject     = `UPDATE projects SET updated_at = $3 WHERE user_id = $1 AND name = $2;`
-	sqlFindProjectByName = `SELECT id, user_id, name, project_dir, acl, blocked, created_at, updated_at FROM projects WHERE user_id = $1 AND name = $2;`
-)
-
 type PsqlDB struct {
 	Logger *slog.Logger
 	Db     *sqlx.DB
@@ -293,7 +103,7 @@ func (me *PsqlDB) RegisterUser(username, pubkey, comment string) (*db.User, erro
 	}()
 
 	var id string
-	err = tx.QueryRow(sqlInsertUser, lowerName).Scan(&id)
+	err = tx.QueryRow(`INSERT INTO app_users (name) VALUES($1) returning id`, lowerName).Scan(&id)
 	if err != nil {
 		return nil, err
 	}
@@ -352,7 +162,7 @@ func (me *PsqlDB) UpdatePublicKey(pubkeyID, name string) (*db.PublicKey, error)
 
 func (me *PsqlDB) findPublicKeyForKey(key string) (*db.PublicKey, error) {
 	var keys []*db.PublicKey
-	rs, err := me.Db.Queryx(sqlSelectPublicKey, key)
+	rs, err := me.Db.Queryx(`SELECT id, user_id, name, public_key, created_at FROM public_keys WHERE public_key = $1`, key)
 	if err != nil {
 		return nil, err
 	}
@@ -398,7 +208,7 @@ func (me *PsqlDB) findPublicKey(pubkeyID string) (*db.PublicKey, error) {
 
 func (me *PsqlDB) FindKeysForUser(user *db.User) ([]*db.PublicKey, error) {
 	var keys []*db.PublicKey
-	err := me.Db.Select(&keys, sqlSelectPublicKeys, user.ID)
+	err := me.Db.Select(&keys, `SELECT id, user_id, name, public_key, created_at FROM public_keys WHERE user_id = $1 ORDER BY created_at ASC`, user.ID)
 	if err != nil {
 		return nil, err
 	}
@@ -407,7 +217,7 @@ func (me *PsqlDB) FindKeysForUser(user *db.User) ([]*db.PublicKey, error) {
 
 func (me *PsqlDB) RemoveKeys(keyIDs []string) error {
 	param := "{" + strings.Join(keyIDs, ",") + "}"
-	_, err := me.Db.Exec(sqlRemoveKeys, param)
+	_, err := me.Db.Exec(`DELETE FROM public_keys WHERE id = ANY($1::uuid[])`, param)
 	return err
 }
 
@@ -493,7 +303,7 @@ func (me *PsqlDB) FindUserByPubkey(key string) (*db.User, error) {
 
 func (me *PsqlDB) FindUser(userID string) (*db.User, error) {
 	user := &db.User{}
-	err := me.Db.Get(user, sqlSelectUser, userID)
+	err := me.Db.Get(user, `SELECT id, COALESCE(name, '') as name, created_at FROM app_users WHERE id = $1`, userID)
 	if err != nil {
 		return nil, err
 	}
@@ -518,7 +328,7 @@ func (me *PsqlDB) validateName(name string) (bool, error) {
 
 func (me *PsqlDB) FindUserByName(name string) (*db.User, error) {
 	user := &db.User{}
-	err := me.Db.Get(user, sqlSelectUserForName, strings.ToLower(name))
+	err := me.Db.Get(user, `SELECT id, name, created_at FROM app_users WHERE name = $1`, strings.ToLower(name))
 	if err != nil {
 		return nil, err
 	}
@@ -529,7 +339,7 @@ func (me *PsqlDB) findUserForNameAndKey(name string, key string) (*db.User, erro
 	user := &db.User{}
 	pk := &db.PublicKey{}
 
-	r := me.Db.QueryRow(sqlSelectUserForNameAndKey, strings.ToLower(name), key)
+	r := me.Db.QueryRow(`SELECT app_users.id, app_users.name, app_users.created_at, public_keys.id as pk_id, public_keys.public_key, public_keys.created_at as pk_created_at FROM app_users LEFT JOIN public_keys ON public_keys.user_id = app_users.id WHERE app_users.name = $1 AND public_keys.public_key = $2`, strings.ToLower(name), key)
 	err := r.Scan(&user.ID, &user.Name, &user.CreatedAt, &pk.ID, &pk.Key, &pk.CreatedAt)
 	if err != nil {
 		return nil, err
@@ -541,7 +351,11 @@ func (me *PsqlDB) findUserForNameAndKey(name string, key string) (*db.User, erro
 
 func (me *PsqlDB) FindUserForToken(token string) (*db.User, error) {
 	user := &db.User{}
-	err := me.Db.Get(user, sqlSelectUserForToken, token)
+	err := me.Db.Get(user, `
+	SELECT app_users.id, app_users.name, app_users.created_at
+	FROM app_users
+	LEFT JOIN tokens ON tokens.user_id = app_users.id
+	WHERE tokens.token = $1 AND tokens.expires_at > NOW()`, token)
 	if err != nil {
 		return nil, err
 	}
@@ -549,7 +363,14 @@ func (me *PsqlDB) FindUserForToken(token string) (*db.User, error) {
 }
 
 func (me *PsqlDB) FindPostWithFilename(filename string, persona_id string, space string) (*db.Post, error) {
-	r := me.Db.QueryRow(sqlSelectPostWithFilename, filename, persona_id, space)
+	query := fmt.Sprintf(`
+	SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
+	FROM posts
+	LEFT JOIN app_users ON app_users.id = posts.user_id
+	LEFT JOIN post_tags ON post_tags.post_id = posts.id
+	WHERE filename = $1 AND user_id = $2 AND cur_space = $3
+	GROUP BY %s`, SelectPost, SelectPost)
+	r := me.Db.QueryRow(query, filename, persona_id, space)
 	post, err := CreatePostWithTagsFromRow(r)
 	if err != nil {
 		return nil, err
@@ -559,11 +380,18 @@ func (me *PsqlDB) FindPostWithFilename(filename string, persona_id string, space
 }
 
 func (me *PsqlDB) FindPostWithSlug(slug string, user_id string, space string) (*db.Post, error) {
-	r := me.Db.QueryRow(sqlSelectPostWithSlug, slug, user_id, space)
+	query := fmt.Sprintf(`
+	SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
+	FROM posts
+	LEFT JOIN app_users ON app_users.id = posts.user_id
+	LEFT JOIN post_tags ON post_tags.post_id = posts.id
+	WHERE slug = $1 AND user_id = $2 AND cur_space = $3
+	GROUP BY %s`, SelectPost, SelectPost)
+	r := me.Db.QueryRow(query, slug, user_id, space)
 	post, err := CreatePostWithTagsFromRow(r)
 	if err != nil {
 		// attempt to find post inside post_aliases
-		alias := me.Db.QueryRow(sqlSelectPostIdByAliasSlug, slug)
+		alias := me.Db.QueryRow(`SELECT post_id FROM post_aliases WHERE slug = $1`, slug)
 		postID := ""
 		err := alias.Scan(&postID)
 		if err != nil {
@@ -578,7 +406,12 @@ func (me *PsqlDB) FindPostWithSlug(slug string, user_id string, space string) (*
 
 func (me *PsqlDB) FindPost(postID string) (*db.Post, error) {
 	post := &db.Post{}
-	err := me.Db.Get(post, sqlSelectPost, postID)
+	query := fmt.Sprintf(`
+	SELECT %s
+	FROM posts
+	LEFT JOIN app_users ON app_users.id = posts.user_id
+	WHERE posts.id = $1`, SelectPost)
+	err := me.Db.Get(post, query, postID)
 	if err != nil {
 		return nil, err
 	}
@@ -615,9 +448,13 @@ func (me *PsqlDB) postPager(rs *sqlx.Rows, pageNum int, space string, tag string
 	var count int
 	var err error
 	if tag == "" {
-		err = me.Db.QueryRow(sqlSelectPostCount, space).Scan(&count)
+		err = me.Db.QueryRow(`SELECT count(id) FROM posts WHERE hidden = FALSE AND cur_space=$1`, space).Scan(&count)
 	} else {
-		err = me.Db.QueryRow(sqlSelectTagPostCount, space, tag).Scan(&count)
+		err = me.Db.QueryRow(`
+	SELECT count(posts.id)
+	FROM posts
+	LEFT JOIN post_tags ON post_tags.post_id = posts.id
+	WHERE hidden = FALSE AND cur_space=$1 and post_tags.name = $2`, space, tag).Scan(&count)
 	}
 	if err != nil {
 		return nil, err
@@ -632,7 +469,32 @@ func (me *PsqlDB) postPager(rs *sqlx.Rows, pageNum int, space string, tag string
 }
 
 func (me *PsqlDB) FindPostsForFeed(page *db.Pager, space string) (*db.Paginate[*db.Post], error) {
-	rs, err := me.Db.Queryx(sqlSelectPostsByRank, page.Num, page.Num*page.Page, space)
+	query := `
+	SELECT *
+	FROM (
+	    SELECT DISTINCT ON (posts.user_id)
+	        posts.id,
+	        posts.user_id,
+	        posts.filename,
+	        posts.slug,
+	        posts.title,
+	        posts.text,
+	        posts.description,
+	        posts.publish_at,
+	        app_users.name AS username,
+	        posts.updated_at,
+	        posts.mime_type
+	    FROM posts
+	    LEFT JOIN app_users ON app_users.id = posts.user_id
+	    WHERE
+	        hidden = FALSE
+	        AND publish_at::date <= CURRENT_DATE
+	        AND cur_space = $3
+	    ORDER BY posts.user_id, publish_at DESC
+	) AS latest_posts
+	ORDER BY publish_at DESC
+	LIMIT $1 OFFSET $2`
+	rs, err := me.Db.Queryx(query, page.Num, page.Num*page.Page, space)
 	if err != nil {
 		return nil, err
 	}
@@ -642,8 +504,14 @@ func (me *PsqlDB) FindPostsForFeed(page *db.Pager, space string) (*db.Paginate[*
 
 func (me *PsqlDB) InsertPost(post *db.Post) (*db.Post, error) {
 	var id string
+	query := `
+	INSERT INTO posts
+		(user_id, filename, slug, title, text, description, publish_at, hidden, cur_space,
+		file_size, mime_type, shasum, data, expires_at, updated_at)
+	VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
+	RETURNING id`
 	err := me.Db.QueryRow(
-		sqlInsertPost,
+		query,
 		post.UserID,
 		post.Filename,
 		post.Slug,
@@ -668,8 +536,13 @@ func (me *PsqlDB) InsertPost(post *db.Post) (*db.Post, error) {
 }
 
 func (me *PsqlDB) UpdatePost(post *db.Post) (*db.Post, error) {
+	query := `
+	UPDATE posts
+	SET slug = $1, title = $2, text = $3, description = $4, updated_at = $5, publish_at = $6,
+		file_size = $7, shasum = $8, data = $9, hidden = $11, expires_at = $12
+	WHERE id = $10`
 	_, err := me.Db.Exec(
-		sqlUpdatePost,
+		query,
 		post.Slug,
 		post.Title,
 		post.Text,
@@ -692,14 +565,27 @@ func (me *PsqlDB) UpdatePost(post *db.Post) (*db.Post, error) {
 
 func (me *PsqlDB) RemovePosts(postIDs []string) error {
 	param := "{" + strings.Join(postIDs, ",") + "}"
-	_, err := me.Db.Exec(sqlRemovePosts, param)
+	_, err := me.Db.Exec(`DELETE FROM posts WHERE id = ANY($1::uuid[])`, param)
 	return err
 }
 
 func (me *PsqlDB) FindPostsForUser(page *db.Pager, userID string, space string) (*db.Paginate[*db.Post], error) {
 	var posts []*db.Post
+	query := fmt.Sprintf(`
+	SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
+	FROM posts
+	LEFT JOIN app_users ON app_users.id = posts.user_id
+	LEFT JOIN post_tags ON post_tags.post_id = posts.id
+	WHERE
+		hidden = FALSE AND
+		user_id = $1 AND
+		publish_at::date <= CURRENT_DATE AND
+		cur_space = $2
+	GROUP BY %s
+	ORDER BY publish_at DESC, slug DESC
+	LIMIT $3 OFFSET $4`, SelectPost, SelectPost)
 	rs, err := me.Db.Queryx(
-		sqlSelectPostsForUser,
+		query,
 		userID,
 		space,
 		page.Num,
@@ -723,7 +609,7 @@ func (me *PsqlDB) FindPostsForUser(page *db.Pager, userID string, space string)
 	}
 
 	var count int
-	err = me.Db.QueryRow(sqlSelectPostCount, space).Scan(&count)
+	err = me.Db.QueryRow(`SELECT count(id) FROM posts WHERE hidden = FALSE AND cur_space=$1`, space).Scan(&count)
 	if err != nil {
 		return nil, err
 	}
@@ -737,7 +623,15 @@ func (me *PsqlDB) FindPostsForUser(page *db.Pager, userID string, space string)
 
 func (me *PsqlDB) FindAllPostsForUser(userID string, space string) ([]*db.Post, error) {
 	var posts []*db.Post
-	err := me.Db.Select(&posts, sqlSelectAllPostsForUser, userID, space)
+	query := fmt.Sprintf(`
+	SELECT %s
+	FROM posts
+	LEFT JOIN app_users ON app_users.id = posts.user_id
+	WHERE
+		user_id = $1 AND
+		cur_space = $2
+	ORDER BY publish_at DESC`, SelectPost)
+	err := me.Db.Select(&posts, query, userID, space)
 	if err != nil {
 		return nil, err
 	}
@@ -746,7 +640,11 @@ func (me *PsqlDB) FindAllPostsForUser(userID string, space string) ([]*db.Post,
 
 func (me *PsqlDB) FindPosts() ([]*db.Post, error) {
 	var posts []*db.Post
-	err := me.Db.Select(&posts, sqlSelectPosts)
+	query := fmt.Sprintf(`
+	SELECT %s
+	FROM posts
+	LEFT JOIN app_users ON app_users.id = posts.user_id`, SelectPost)
+	err := me.Db.Select(&posts, query)
 	if err != nil {
 		return nil, err
 	}
@@ -755,7 +653,15 @@ func (me *PsqlDB) FindPosts() ([]*db.Post, error) {
 
 func (me *PsqlDB) FindExpiredPosts(space string) ([]*db.Post, error) {
 	var posts []*db.Post
-	err := me.Db.Select(&posts, sqlSelectExpiredPosts, space)
+	query := fmt.Sprintf(`
+		SELECT %s
+		FROM posts
+		LEFT JOIN app_users ON app_users.id = posts.user_id
+		WHERE
+			cur_space = $1 AND
+			expires_at <= now();
+	`, SelectPost)
+	err := me.Db.Select(&posts, query, space)
 	if err != nil {
 		return nil, err
 	}
@@ -1024,7 +930,7 @@ func (me *PsqlDB) FindVisitSiteList(opts *db.SummaryOpts) ([]*db.VisitUrl, error
 
 func (me *PsqlDB) FindUsers() ([]*db.User, error) {
 	var users []*db.User
-	err := me.Db.Select(&users, sqlSelectUsers)
+	err := me.Db.Select(&users, `SELECT id, COALESCE(name, '') as name, created_at FROM app_users ORDER BY name ASC`)
 	if err != nil {
 		return nil, err
 	}
@@ -1032,7 +938,7 @@ func (me *PsqlDB) FindUsers() ([]*db.User, error) {
 }
 
 func (me *PsqlDB) removeTagsForPost(tx *sqlx.Tx, postID string) error {
-	_, err := tx.Exec(sqlRemoveTagsByPost, postID)
+	_, err := tx.Exec(`DELETE FROM post_tags WHERE post_id = $1`, postID)
 	return err
 }
 
@@ -1040,7 +946,7 @@ func (me *PsqlDB) insertTagsForPost(tx *sqlx.Tx, tags []string, postID string) (
 	ids := make([]string, 0)
 	for _, tag := range tags {
 		id := ""
-		err := tx.QueryRow(sqlInsertTag, postID, tag).Scan(&id)
+		err := tx.QueryRow(`INSERT INTO post_tags (post_id, name) VALUES($1, $2) RETURNING id;`, postID, tag).Scan(&id)
 		if err != nil {
 			return nil, err
 		}
@@ -1074,7 +980,7 @@ func (me *PsqlDB) ReplaceTagsForPost(tags []string, postID string) error {
 }
 
 func (me *PsqlDB) removeAliasesForPost(tx *sqlx.Tx, postID string) error {
-	_, err := tx.Exec(sqlRemoveAliasesByPost, postID)
+	_, err := tx.Exec(`DELETE FROM post_aliases WHERE post_id = $1`, postID)
 	return err
 }
 
@@ -1109,7 +1015,7 @@ func (me *PsqlDB) insertAliasesForPost(tx *sqlx.Tx, aliases []string, postID str
 			continue
 		}
 		id := ""
-		err := tx.QueryRow(sqlInsertAliases, postID, alias).Scan(&id)
+		err := tx.QueryRow(`INSERT INTO post_aliases (post_id, slug) VALUES($1, $2) RETURNING id;`, postID, alias).Scan(&id)
 		if err != nil {
 			return nil, err
 		}
@@ -1144,9 +1050,22 @@ func (me *PsqlDB) ReplaceAliasesForPost(aliases []string, postID string) error {
 
 func (me *PsqlDB) FindUserPostsByTag(page *db.Pager, tag, userID, space string) (*db.Paginate[*db.Post], error) {
 	var posts []*db.Post
+	query := fmt.Sprintf(`
+	SELECT %s
+	FROM posts
+	LEFT JOIN app_users ON app_users.id = posts.user_id
+	LEFT JOIN post_tags ON post_tags.post_id = posts.id
+	WHERE
+		hidden = FALSE AND
+		user_id = $1 AND
+		(post_tags.name = $2 OR hidden = true) AND
+		publish_at::date <= CURRENT_DATE AND
+		cur_space = $3
+	ORDER BY publish_at DESC
+	LIMIT $4 OFFSET $5`, SelectPost)
 	err := me.Db.Select(
 		&posts,
-		sqlSelectUserPostsByTag,
+		query,
 		userID,
 		tag,
 		space,
@@ -1158,7 +1077,7 @@ func (me *PsqlDB) FindUserPostsByTag(page *db.Pager, tag, userID, space string)
 	}
 
 	var count int
-	err = me.Db.QueryRow(sqlSelectPostCount, space).Scan(&count)
+	err = me.Db.QueryRow(`SELECT count(id) FROM posts WHERE hidden = FALSE AND cur_space=$1`, space).Scan(&count)
 	if err != nil {
 		return nil, err
 	}
@@ -1171,8 +1090,30 @@ func (me *PsqlDB) FindUserPostsByTag(page *db.Pager, tag, userID, space string)
 }
 
 func (me *PsqlDB) FindPostsByTag(pager *db.Pager, tag, space string) (*db.Paginate[*db.Post], error) {
+	query := `
+	SELECT
+		posts.id,
+		user_id,
+		filename,
+		slug,
+		title,
+		text,
+		description,
+		publish_at,
+		app_users.name as username,
+		posts.updated_at,
+		posts.mime_type
+	FROM posts
+	LEFT JOIN app_users ON app_users.id = posts.user_id
+	LEFT JOIN post_tags ON post_tags.post_id = posts.id
+	WHERE
+		post_tags.name = $3 AND
+		publish_at::date <= CURRENT_DATE AND
+		cur_space = $4
+	ORDER BY publish_at DESC
+	LIMIT $1 OFFSET $2`
 	rs, err := me.Db.Queryx(
-		sqlSelectPostsByTag,
+		query,
 		pager.Num,
 		pager.Num*pager.Page,
 		tag,
@@ -1188,7 +1129,15 @@ func (me *PsqlDB) FindPostsByTag(pager *db.Pager, tag, space string) (*db.Pagina
 
 func (me *PsqlDB) FindPopularTags(space string) ([]string, error) {
 	tags := make([]string, 0)
-	rs, err := me.Db.Queryx(sqlSelectPopularTags, space)
+	query := `
+	SELECT name, count(post_id) as "tally"
+	FROM post_tags
+	LEFT JOIN posts ON posts.id = post_id
+	WHERE posts.cur_space = $1
+	GROUP BY name
+	ORDER BY tally DESC
+	LIMIT 5`
+	rs, err := me.Db.Queryx(query, space)
 	if err != nil {
 		return tags, err
 	}
@@ -1211,7 +1160,7 @@ func (me *PsqlDB) FindPopularTags(space string) ([]string, error) {
 
 func (me *PsqlDB) FindFeature(userID string, feature string) (*db.FeatureFlag, error) {
 	ff := &db.FeatureFlag{}
-	err := me.Db.Get(ff, sqlSelectFeatureForUser, userID, feature)
+	err := me.Db.Get(ff, `SELECT id, user_id, payment_history_id, name, data, created_at, expires_at FROM feature_flags WHERE user_id = $1 AND name = $2 ORDER BY expires_at DESC LIMIT 1`, userID, feature)
 	if err != nil {
 		return nil, err
 	}
@@ -1252,7 +1201,7 @@ func (me *PsqlDB) InsertFeedItems(postID string, items []*db.FeedItem) error {
 
 	for _, item := range items {
 		_, err := tx.Exec(
-			sqlInsertFeedItems,
+			`INSERT INTO feed_items (post_id, guid, data) VALUES ($1, $2, $3) RETURNING id;`,
 			item.PostID,
 			item.GUID,
 			item.Data,
@@ -1271,7 +1220,7 @@ func (me *PsqlDB) InsertFeedItems(postID string, items []*db.FeedItem) error {
 
 func (me *PsqlDB) FindFeedItemsByPostID(postID string) ([]*db.FeedItem, error) {
 	var items []*db.FeedItem
-	err := me.Db.Select(&items, sqlSelectFeedItemsByPost, postID)
+	err := me.Db.Select(&items, `SELECT id, post_id, guid, data, created_at FROM feed_items WHERE post_id=$1`, postID)
 	if err != nil {
 		return nil, err
 	}
@@ -1284,7 +1233,7 @@ func (me *PsqlDB) InsertProject(userID, name, projectDir string) (string, error)
 	}
 
 	var id string
-	err := me.Db.QueryRow(sqlInsertProject, userID, name, projectDir).Scan(&id)
+	err := me.Db.QueryRow(`INSERT INTO projects (user_id, name, project_dir) VALUES ($1, $2, $3) RETURNING id;`, userID, name, projectDir).Scan(&id)
 	if err != nil {
 		return "", err
 	}
@@ -1292,13 +1241,13 @@ func (me *PsqlDB) InsertProject(userID, name, projectDir string) (string, error)
 }
 
 func (me *PsqlDB) UpdateProject(userID, name string) error {
-	_, err := me.Db.Exec(sqlUpdateProject, userID, name, time.Now())
+	_, err := me.Db.Exec(`UPDATE projects SET updated_at = $3 WHERE user_id = $1 AND name = $2;`, userID, name, time.Now())
 	return err
 }
 
 func (me *PsqlDB) FindProjectByName(userID, name string) (*db.Project, error) {
 	project := &db.Project{}
-	err := me.Db.Get(project, sqlFindProjectByName, userID, name)
+	err := me.Db.Get(project, `SELECT id, user_id, name, project_dir, acl, blocked, created_at, updated_at FROM projects WHERE user_id = $1 AND name = $2;`, userID, name)
 	if err != nil {
 		return nil, err
 	}
@@ -1307,7 +1256,7 @@ func (me *PsqlDB) FindProjectByName(userID, name string) (*db.Project, error) {
 
 func (me *PsqlDB) InsertToken(userID, name string) (string, error) {
 	var token string
-	err := me.Db.QueryRow(sqlInsertToken, userID, name).Scan(&token)
+	err := me.Db.QueryRow(`INSERT INTO tokens (user_id, name) VALUES($1, $2) RETURNING token;`, userID, name).Scan(&token)
 	if err != nil {
 		return "", err
 	}
@@ -1326,7 +1275,7 @@ func (me *PsqlDB) UpsertToken(userID, name string) (string, error) {
 
 func (me *PsqlDB) findTokenByName(userID, name string) (string, error) {
 	var token string
-	err := me.Db.QueryRow(sqlSelectTokenByNameForUser, userID, name).Scan(&token)
+	err := me.Db.QueryRow(`SELECT token FROM tokens WHERE user_id = $1 AND name = $2`, userID, name).Scan(&token)
 	if err != nil {
 		return "", err
 	}
@@ -1334,13 +1283,13 @@ func (me *PsqlDB) findTokenByName(userID, name string) (string, error) {
 }
 
 func (me *PsqlDB) RemoveToken(tokenID string) error {
-	_, err := me.Db.Exec(sqlRemoveToken, tokenID)
+	_, err := me.Db.Exec(`DELETE FROM tokens WHERE id = $1`, tokenID)
 	return err
 }
 
 func (me *PsqlDB) FindTokensForUser(userID string) ([]*db.Token, error) {
 	var tokens []*db.Token
-	err := me.Db.Select(&tokens, sqlSelectTokensForUser, userID)
+	err := me.Db.Select(&tokens, `SELECT id, user_id, name, created_at, expires_at FROM tokens WHERE user_id = $1`, userID)
 	if err != nil {
 		return nil, err
 	}