repos / pico

pico services - prose.sh, pastes.sh, imgs.sh, feeds.sh, pgs.sh
git clone https://github.com/picosh/pico.git

pico / db / postgres
Antonio Mika · 17 Nov 24

storage.go

   1package postgres
   2
   3import (
   4	"context"
   5	"database/sql"
   6	"errors"
   7	"fmt"
   8	"log/slog"
   9	"math"
  10	"strings"
  11	"time"
  12
  13	"slices"
  14
  15	_ "github.com/lib/pq"
  16	"github.com/picosh/pico/db"
  17	"github.com/picosh/utils"
  18)
  19
  20var PAGER_SIZE = 15
  21
  22var SelectPost = `
  23	posts.id, user_id, app_users.name, filename, slug, title, text, description,
  24	posts.created_at, publish_at, posts.updated_at, hidden, file_size, mime_type, shasum, data, expires_at, views`
  25
  26var (
  27	sqlSelectPosts = fmt.Sprintf(`
  28	SELECT %s
  29	FROM posts
  30	LEFT JOIN app_users ON app_users.id = posts.user_id`, SelectPost)
  31
  32	sqlSelectPostsBeforeDate = fmt.Sprintf(`
  33	SELECT %s
  34	FROM posts
  35	LEFT JOIN app_users ON app_users.id = posts.user_id
  36	WHERE publish_at::date <= $1 AND cur_space = $2`, SelectPost)
  37
  38	sqlSelectPostWithFilename = fmt.Sprintf(`
  39	SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
  40	FROM posts
  41	LEFT JOIN app_users ON app_users.id = posts.user_id
  42	LEFT JOIN post_tags ON post_tags.post_id = posts.id
  43	WHERE filename = $1 AND user_id = $2 AND cur_space = $3
  44	GROUP BY %s`, SelectPost, SelectPost)
  45
  46	sqlSelectPostWithSlug = fmt.Sprintf(`
  47	SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
  48	FROM posts
  49	LEFT JOIN app_users ON app_users.id = posts.user_id
  50	LEFT JOIN post_tags ON post_tags.post_id = posts.id
  51	WHERE slug = $1 AND user_id = $2 AND cur_space = $3
  52	GROUP BY %s`, SelectPost, SelectPost)
  53
  54	sqlSelectPost = fmt.Sprintf(`
  55	SELECT %s
  56	FROM posts
  57	LEFT JOIN app_users ON app_users.id = posts.user_id
  58	WHERE posts.id = $1`, SelectPost)
  59
  60	sqlSelectUpdatedPostsForUser = fmt.Sprintf(`
  61	SELECT %s
  62	FROM posts
  63	LEFT JOIN app_users ON app_users.id = posts.user_id
  64	WHERE user_id = $1 AND publish_at::date <= CURRENT_DATE AND cur_space = $2
  65	ORDER BY posts.updated_at DESC`, SelectPost)
  66
  67	sqlSelectExpiredPosts = fmt.Sprintf(`
  68		SELECT %s
  69		FROM posts
  70		LEFT JOIN app_users ON app_users.id = posts.user_id
  71		WHERE
  72			cur_space = $1 AND
  73			expires_at <= now();
  74	`, SelectPost)
  75
  76	sqlSelectPostsForUser = fmt.Sprintf(`
  77	SELECT %s, STRING_AGG(coalesce(post_tags.name, ''), ',') tags
  78	FROM posts
  79	LEFT JOIN app_users ON app_users.id = posts.user_id
  80	LEFT JOIN post_tags ON post_tags.post_id = posts.id
  81	WHERE
  82		hidden = FALSE AND
  83		user_id = $1 AND
  84		publish_at::date <= CURRENT_DATE AND
  85		cur_space = $2
  86	GROUP BY %s
  87	ORDER BY publish_at DESC, slug DESC
  88	LIMIT $3 OFFSET $4`, SelectPost, SelectPost)
  89
  90	sqlSelectAllPostsForUser = fmt.Sprintf(`
  91	SELECT %s
  92	FROM posts
  93	LEFT JOIN app_users ON app_users.id = posts.user_id
  94	WHERE
  95		user_id = $1 AND
  96		cur_space = $2
  97	ORDER BY publish_at DESC`, SelectPost)
  98
  99	sqlSelectPostsByTag = `
 100	SELECT
 101		posts.id,
 102		user_id,
 103		filename,
 104		slug,
 105		title,
 106		text,
 107		description,
 108		publish_at,
 109		app_users.name as username,
 110		posts.updated_at,
 111		posts.mime_type
 112	FROM posts
 113	LEFT JOIN app_users ON app_users.id = posts.user_id
 114	LEFT JOIN post_tags ON post_tags.post_id = posts.id
 115	WHERE
 116		post_tags.name = $3 AND
 117		publish_at::date <= CURRENT_DATE AND
 118		cur_space = $4
 119	ORDER BY publish_at DESC
 120	LIMIT $1 OFFSET $2`
 121
 122	sqlSelectUserPostsByTag = fmt.Sprintf(`
 123	SELECT %s
 124	FROM posts
 125	LEFT JOIN app_users ON app_users.id = posts.user_id
 126	LEFT JOIN post_tags ON post_tags.post_id = posts.id
 127	WHERE
 128		hidden = FALSE AND
 129		user_id = $1 AND
 130		(post_tags.name = $2 OR hidden = true) AND
 131		publish_at::date <= CURRENT_DATE AND
 132		cur_space = $3
 133	ORDER BY publish_at DESC
 134	LIMIT $4 OFFSET $5`, SelectPost)
 135)
 136
 137const (
 138	sqlSelectPublicKey         = `SELECT id, user_id, name, public_key, created_at FROM public_keys WHERE public_key = $1`
 139	sqlSelectPublicKeys        = `SELECT id, user_id, name, public_key, created_at FROM public_keys WHERE user_id = $1 ORDER BY created_at ASC`
 140	sqlSelectUser              = `SELECT id, name, created_at FROM app_users WHERE id = $1`
 141	sqlSelectUserForName       = `SELECT id, name, created_at FROM app_users WHERE name = $1`
 142	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`
 143	sqlSelectUsers             = `SELECT id, name, created_at FROM app_users ORDER BY name ASC`
 144
 145	sqlSelectUserForToken = `
 146	SELECT app_users.id, app_users.name, app_users.created_at
 147	FROM app_users
 148	LEFT JOIN tokens ON tokens.user_id = app_users.id
 149	WHERE tokens.token = $1 AND tokens.expires_at > NOW()`
 150	sqlInsertToken              = `INSERT INTO tokens (user_id, name) VALUES($1, $2) RETURNING token;`
 151	sqlRemoveToken              = `DELETE FROM tokens WHERE id = $1`
 152	sqlSelectTokensForUser      = `SELECT id, user_id, name, created_at, expires_at FROM tokens WHERE user_id = $1`
 153	sqlSelectTokenByNameForUser = `SELECT token FROM tokens WHERE user_id = $1 AND name = $2`
 154
 155	sqlSelectTotalUsers          = `SELECT count(id) FROM app_users`
 156	sqlSelectUsersAfterDate      = `SELECT count(id) FROM app_users WHERE created_at >= $1`
 157	sqlSelectTotalPosts          = `SELECT count(id) FROM posts WHERE cur_space = $1`
 158	sqlSelectTotalPostsAfterDate = `SELECT count(id) FROM posts WHERE created_at >= $1 AND cur_space = $2`
 159	sqlSelectUsersWithPost       = `SELECT count(app_users.id) FROM app_users WHERE EXISTS (SELECT 1 FROM posts WHERE user_id = app_users.id AND cur_space = $1);`
 160
 161	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`
 162	sqlSelectSizeForUser    = `SELECT COALESCE(sum(file_size), 0) FROM posts WHERE user_id = $1`
 163
 164	sqlSelectPostIdByAliasSlug = `SELECT post_id FROM post_aliases WHERE slug = $1`
 165	sqlSelectTagPostCount      = `
 166	SELECT count(posts.id)
 167	FROM posts
 168	LEFT JOIN post_tags ON post_tags.post_id = posts.id
 169	WHERE hidden = FALSE AND cur_space=$1 and post_tags.name = $2`
 170	sqlSelectPostCount       = `SELECT count(id) FROM posts WHERE hidden = FALSE AND cur_space=$1`
 171	sqlSelectAllUpdatedPosts = `
 172	SELECT
 173		posts.id,
 174		user_id,
 175		filename,
 176		slug,
 177		title,
 178		text,
 179		description,
 180		publish_at,
 181		app_users.name as username,
 182		posts.updated_at,
 183		posts.mime_type
 184	FROM posts
 185	LEFT JOIN app_users ON app_users.id = posts.user_id
 186	WHERE hidden = FALSE AND publish_at::date <= CURRENT_DATE AND cur_space = $3
 187	ORDER BY updated_at DESC
 188	LIMIT $1 OFFSET $2`
 189	// add some users to deny list since they are robogenerating a bunch of posts
 190	// per day and are creating a lot of noise.
 191	sqlSelectPostsByRank = `
 192	SELECT
 193		posts.id,
 194		user_id,
 195		filename,
 196		slug,
 197		title,
 198		text,
 199		description,
 200		publish_at,
 201		app_users.name as username,
 202		posts.updated_at,
 203		posts.mime_type
 204	FROM posts
 205	LEFT JOIN app_users ON app_users.id = posts.user_id
 206	WHERE
 207		hidden = FALSE AND
 208		publish_at::date <= CURRENT_DATE AND
 209		cur_space = $3 AND
 210		app_users.name NOT IN ('algiegray', 'mrrccc')
 211	ORDER BY publish_at DESC
 212	LIMIT $1 OFFSET $2`
 213
 214	sqlSelectPopularTags = `
 215	SELECT name, count(post_id) as "tally"
 216	FROM post_tags
 217	LEFT JOIN posts ON posts.id = post_id
 218	WHERE posts.cur_space = $1
 219	GROUP BY name
 220	ORDER BY tally DESC
 221	LIMIT 5`
 222	sqlSelectTagsForPost     = `SELECT name FROM post_tags WHERE post_id=$1`
 223	sqlSelectFeedItemsByPost = `SELECT id, post_id, guid, data, created_at FROM feed_items WHERE post_id=$1`
 224
 225	sqlInsertPublicKey = `INSERT INTO public_keys (user_id, public_key) VALUES ($1, $2)`
 226	sqlInsertPost      = `
 227	INSERT INTO posts
 228		(user_id, filename, slug, title, text, description, publish_at, hidden, cur_space,
 229		file_size, mime_type, shasum, data, expires_at, updated_at)
 230	VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
 231	RETURNING id`
 232	sqlInsertUser      = `INSERT INTO app_users (name) VALUES($1) returning id`
 233	sqlInsertTag       = `INSERT INTO post_tags (post_id, name) VALUES($1, $2) RETURNING id;`
 234	sqlInsertAliases   = `INSERT INTO post_aliases (post_id, slug) VALUES($1, $2) RETURNING id;`
 235	sqlInsertFeedItems = `INSERT INTO feed_items (post_id, guid, data) VALUES ($1, $2, $3) RETURNING id;`
 236
 237	sqlUpdatePost = `
 238	UPDATE posts
 239	SET slug = $1, title = $2, text = $3, description = $4, updated_at = $5, publish_at = $6,
 240		file_size = $7, shasum = $8, data = $9, hidden = $11, expires_at = $12
 241	WHERE id = $10`
 242	sqlUpdateUserName = `UPDATE app_users SET name = $1 WHERE id = $2`
 243	sqlIncrementViews = `UPDATE posts SET views = views + 1 WHERE id = $1 RETURNING views`
 244
 245	sqlRemoveAliasesByPost = `DELETE FROM post_aliases WHERE post_id = $1`
 246	sqlRemoveTagsByPost    = `DELETE FROM post_tags WHERE post_id = $1`
 247	sqlRemovePosts         = `DELETE FROM posts WHERE id = ANY($1::uuid[])`
 248	sqlRemoveKeys          = `DELETE FROM public_keys WHERE id = ANY($1::uuid[])`
 249	sqlRemoveUsers         = `DELETE FROM app_users WHERE id = ANY($1::uuid[])`
 250
 251	sqlInsertProject        = `INSERT INTO projects (user_id, name, project_dir) VALUES ($1, $2, $3) RETURNING id;`
 252	sqlUpdateProject        = `UPDATE projects SET updated_at = $3 WHERE user_id = $1 AND name = $2;`
 253	sqlUpdateProjectAcl     = `UPDATE projects SET acl = $3, updated_at = $4 WHERE user_id = $1 AND name = $2;`
 254	sqlFindProjectByName    = `SELECT id, user_id, name, project_dir, acl, blocked, created_at, updated_at FROM projects WHERE user_id = $1 AND name = $2;`
 255	sqlSelectProjectCount   = `SELECT count(id) FROM projects`
 256	sqlFindProjectsByUser   = `SELECT id, user_id, name, project_dir, acl, blocked, created_at, updated_at FROM projects WHERE user_id = $1 ORDER BY name ASC, updated_at DESC;`
 257	sqlFindProjectsByPrefix = `SELECT id, user_id, name, project_dir, acl, blocked, created_at, updated_at FROM projects WHERE user_id = $1 AND name = project_dir AND name ILIKE $2 ORDER BY updated_at ASC, name ASC;`
 258	sqlFindProjectLinks     = `SELECT id, user_id, name, project_dir, acl, blocked, created_at, updated_at FROM projects WHERE user_id = $1 AND name != project_dir AND project_dir = $2 ORDER BY name ASC;`
 259	sqlLinkToProject        = `UPDATE projects SET project_dir = $1, updated_at = $2 WHERE id = $3;`
 260	sqlRemoveProject        = `DELETE FROM projects WHERE id = $1;`
 261)
 262
 263type PsqlDB struct {
 264	Logger *slog.Logger
 265	Db     *sql.DB
 266}
 267
 268type RowScanner interface {
 269	Scan(dest ...any) error
 270}
 271
 272func CreatePostFromRow(r RowScanner) (*db.Post, error) {
 273	post := &db.Post{}
 274	err := r.Scan(
 275		&post.ID,
 276		&post.UserID,
 277		&post.Username,
 278		&post.Filename,
 279		&post.Slug,
 280		&post.Title,
 281		&post.Text,
 282		&post.Description,
 283		&post.CreatedAt,
 284		&post.PublishAt,
 285		&post.UpdatedAt,
 286		&post.Hidden,
 287		&post.FileSize,
 288		&post.MimeType,
 289		&post.Shasum,
 290		&post.Data,
 291		&post.ExpiresAt,
 292		&post.Views,
 293	)
 294	if err != nil {
 295		return nil, err
 296	}
 297	return post, nil
 298}
 299
 300func CreatePostWithTagsFromRow(r RowScanner) (*db.Post, error) {
 301	post := &db.Post{}
 302	tagStr := ""
 303	err := r.Scan(
 304		&post.ID,
 305		&post.UserID,
 306		&post.Username,
 307		&post.Filename,
 308		&post.Slug,
 309		&post.Title,
 310		&post.Text,
 311		&post.Description,
 312		&post.CreatedAt,
 313		&post.PublishAt,
 314		&post.UpdatedAt,
 315		&post.Hidden,
 316		&post.FileSize,
 317		&post.MimeType,
 318		&post.Shasum,
 319		&post.Data,
 320		&post.ExpiresAt,
 321		&post.Views,
 322		&tagStr,
 323	)
 324	if err != nil {
 325		return nil, err
 326	}
 327
 328	tags := strings.Split(tagStr, ",")
 329	for _, tag := range tags {
 330		tg := strings.TrimSpace(tag)
 331		if tg == "" {
 332			continue
 333		}
 334		post.Tags = append(post.Tags, tg)
 335	}
 336
 337	return post, nil
 338}
 339
 340func NewDB(databaseUrl string, logger *slog.Logger) *PsqlDB {
 341	var err error
 342	d := &PsqlDB{
 343		Logger: logger,
 344	}
 345	d.Logger.Info("Connecting to postgres", "databaseUrl", databaseUrl)
 346
 347	db, err := sql.Open("postgres", databaseUrl)
 348	if err != nil {
 349		d.Logger.Error(err.Error())
 350	}
 351	d.Db = db
 352	return d
 353}
 354
 355func (me *PsqlDB) RegisterUser(username, pubkey, comment string) (*db.User, error) {
 356	lowerName := strings.ToLower(username)
 357	valid, err := me.ValidateName(lowerName)
 358	if !valid {
 359		return nil, err
 360	}
 361
 362	ctx := context.Background()
 363	tx, err := me.Db.BeginTx(ctx, nil)
 364	if err != nil {
 365		return nil, err
 366	}
 367	defer func() {
 368		err = tx.Rollback()
 369	}()
 370
 371	stmt, err := tx.Prepare(sqlInsertUser)
 372	if err != nil {
 373		return nil, err
 374	}
 375	defer stmt.Close()
 376
 377	var id string
 378	err = stmt.QueryRow(lowerName).Scan(&id)
 379	if err != nil {
 380		return nil, err
 381	}
 382
 383	err = me.InsertPublicKey(id, pubkey, comment, tx)
 384	if err != nil {
 385		return nil, err
 386	}
 387
 388	err = tx.Commit()
 389	if err != nil {
 390		return nil, err
 391	}
 392
 393	return me.FindUserForKey(username, pubkey)
 394}
 395
 396func (me *PsqlDB) RemoveUsers(userIDs []string) error {
 397	param := "{" + strings.Join(userIDs, ",") + "}"
 398	_, err := me.Db.Exec(sqlRemoveUsers, param)
 399	return err
 400}
 401
 402func (me *PsqlDB) InsertPublicKey(userID, key, name string, tx *sql.Tx) error {
 403	pk, _ := me.FindPublicKeyForKey(key)
 404	if pk != nil {
 405		return db.ErrPublicKeyTaken
 406	}
 407	query := `INSERT INTO public_keys (user_id, public_key, name) VALUES ($1, $2, $3)`
 408	var err error
 409	if tx != nil {
 410		_, err = tx.Exec(query, userID, key, name)
 411	} else {
 412		_, err = me.Db.Exec(query, userID, key, name)
 413	}
 414	if err != nil {
 415		return err
 416	}
 417
 418	return nil
 419}
 420
 421func (me *PsqlDB) UpdatePublicKey(pubkeyID, name string) (*db.PublicKey, error) {
 422	pk, err := me.FindPublicKey(pubkeyID)
 423	if err != nil {
 424		return nil, err
 425	}
 426
 427	query := `UPDATE public_keys SET name=$1 WHERE id=$2;`
 428	_, err = me.Db.Exec(query, name, pk.ID)
 429	if err != nil {
 430		return nil, err
 431	}
 432
 433	pk, err = me.FindPublicKey(pubkeyID)
 434	if err != nil {
 435		return nil, err
 436	}
 437	return pk, nil
 438}
 439
 440func (me *PsqlDB) FindPublicKeyForKey(key string) (*db.PublicKey, error) {
 441	var keys []*db.PublicKey
 442	rs, err := me.Db.Query(sqlSelectPublicKey, key)
 443	if err != nil {
 444		return nil, err
 445	}
 446
 447	for rs.Next() {
 448		pk := &db.PublicKey{}
 449		err := rs.Scan(&pk.ID, &pk.UserID, &pk.Name, &pk.Key, &pk.CreatedAt)
 450		if err != nil {
 451			return nil, err
 452		}
 453
 454		keys = append(keys, pk)
 455	}
 456
 457	if rs.Err() != nil {
 458		return nil, rs.Err()
 459	}
 460
 461	if len(keys) == 0 {
 462		return nil, fmt.Errorf("pubkey not found in our database: [%s]", key)
 463	}
 464
 465	// When we run PublicKeyForKey and there are multiple public keys returned from the database
 466	// that should mean that we don't have the correct username for this public key.
 467	// When that happens we need to reject the authentication and ask the user to provide the correct
 468	// username when using ssh.  So instead of `ssh <domain>` it should be `ssh user@<domain>`
 469	if len(keys) > 1 {
 470		return nil, &db.ErrMultiplePublicKeys{}
 471	}
 472
 473	return keys[0], nil
 474}
 475
 476func (me *PsqlDB) FindPublicKey(pubkeyID string) (*db.PublicKey, error) {
 477	var keys []*db.PublicKey
 478	rs, err := me.Db.Query(`SELECT id, user_id, name, public_key, created_at FROM public_keys WHERE id = $1`, pubkeyID)
 479	if err != nil {
 480		return nil, err
 481	}
 482
 483	for rs.Next() {
 484		pk := &db.PublicKey{}
 485		err := rs.Scan(&pk.ID, &pk.UserID, &pk.Name, &pk.Key, &pk.CreatedAt)
 486		if err != nil {
 487			return nil, err
 488		}
 489
 490		keys = append(keys, pk)
 491	}
 492
 493	if rs.Err() != nil {
 494		return nil, rs.Err()
 495	}
 496
 497	if len(keys) == 0 {
 498		return nil, errors.New("no public keys found for key provided")
 499	}
 500
 501	return keys[0], nil
 502}
 503
 504func (me *PsqlDB) FindKeysForUser(user *db.User) ([]*db.PublicKey, error) {
 505	var keys []*db.PublicKey
 506	rs, err := me.Db.Query(sqlSelectPublicKeys, user.ID)
 507	if err != nil {
 508		return keys, err
 509	}
 510	for rs.Next() {
 511		pk := &db.PublicKey{}
 512		err := rs.Scan(&pk.ID, &pk.UserID, &pk.Name, &pk.Key, &pk.CreatedAt)
 513		if err != nil {
 514			return keys, err
 515		}
 516
 517		keys = append(keys, pk)
 518	}
 519	if rs.Err() != nil {
 520		return keys, rs.Err()
 521	}
 522	return keys, nil
 523}
 524
 525func (me *PsqlDB) RemoveKeys(keyIDs []string) error {
 526	param := "{" + strings.Join(keyIDs, ",") + "}"
 527	_, err := me.Db.Exec(sqlRemoveKeys, param)
 528	return err
 529}
 530
 531func (me *PsqlDB) FindSiteAnalytics(space string) (*db.Analytics, error) {
 532	analytics := &db.Analytics{}
 533	r := me.Db.QueryRow(sqlSelectTotalUsers)
 534	err := r.Scan(&analytics.TotalUsers)
 535	if err != nil {
 536		return nil, err
 537	}
 538
 539	r = me.Db.QueryRow(sqlSelectTotalPosts, space)
 540	err = r.Scan(&analytics.TotalPosts)
 541	if err != nil {
 542		return nil, err
 543	}
 544
 545	now := time.Now()
 546	year, month, _ := now.Date()
 547	begMonth := time.Date(year, month, 1, 0, 0, 0, 0, now.Location())
 548
 549	r = me.Db.QueryRow(sqlSelectTotalPostsAfterDate, begMonth, space)
 550	err = r.Scan(&analytics.PostsLastMonth)
 551	if err != nil {
 552		return nil, err
 553	}
 554
 555	r = me.Db.QueryRow(sqlSelectUsersAfterDate, begMonth)
 556	err = r.Scan(&analytics.UsersLastMonth)
 557	if err != nil {
 558		return nil, err
 559	}
 560
 561	r = me.Db.QueryRow(sqlSelectUsersWithPost, space)
 562	err = r.Scan(&analytics.UsersWithPost)
 563	if err != nil {
 564		return nil, err
 565	}
 566
 567	return analytics, nil
 568}
 569
 570func (me *PsqlDB) FindPostsBeforeDate(date *time.Time, space string) ([]*db.Post, error) {
 571	// now := time.Now()
 572	// expired := now.AddDate(0, 0, -3)
 573	var posts []*db.Post
 574	rs, err := me.Db.Query(sqlSelectPostsBeforeDate, date, space)
 575	if err != nil {
 576		return posts, err
 577	}
 578	for rs.Next() {
 579		post, err := CreatePostFromRow(rs)
 580		if err != nil {
 581			return nil, err
 582		}
 583
 584		posts = append(posts, post)
 585	}
 586	if rs.Err() != nil {
 587		return posts, rs.Err()
 588	}
 589	return posts, nil
 590}
 591
 592func (me *PsqlDB) FindUserForKey(username string, key string) (*db.User, error) {
 593	me.Logger.Info("attempting to find user with only public key", "key", key)
 594	pk, err := me.FindPublicKeyForKey(key)
 595	if err == nil {
 596		me.Logger.Info("found pubkey, looking for user", "key", key, "userId", pk.UserID)
 597		user, err := me.FindUser(pk.UserID)
 598		if err != nil {
 599			return nil, err
 600		}
 601		user.PublicKey = pk
 602		return user, nil
 603	}
 604
 605	if errors.Is(err, &db.ErrMultiplePublicKeys{}) {
 606		me.Logger.Info("detected multiple users with same public key", "user", username)
 607		user, err := me.FindUserForNameAndKey(username, key)
 608		if err != nil {
 609			me.Logger.Info("could not find user by username and public key", "user", username, "key", key)
 610			// this is a little hacky but if we cannot find a user by name and public key
 611			// then we return the multiple keys detected error so the user knows to specify their
 612			// when logging in
 613			return nil, &db.ErrMultiplePublicKeys{}
 614		}
 615		return user, nil
 616	}
 617
 618	return nil, err
 619}
 620
 621func (me *PsqlDB) FindUser(userID string) (*db.User, error) {
 622	user := &db.User{}
 623	var un sql.NullString
 624	r := me.Db.QueryRow(sqlSelectUser, userID)
 625	err := r.Scan(&user.ID, &un, &user.CreatedAt)
 626	if err != nil {
 627		return nil, err
 628	}
 629	if un.Valid {
 630		user.Name = un.String
 631	}
 632	return user, nil
 633}
 634
 635func (me *PsqlDB) ValidateName(name string) (bool, error) {
 636	lower := strings.ToLower(name)
 637	if slices.Contains(db.DenyList, lower) {
 638		return false, fmt.Errorf("%s is on deny list: %w", lower, db.ErrNameDenied)
 639	}
 640	v := db.NameValidator.MatchString(lower)
 641	if !v {
 642		return false, fmt.Errorf("%s is invalid: %w", lower, db.ErrNameInvalid)
 643	}
 644	user, _ := me.FindUserForName(lower)
 645	if user == nil {
 646		return true, nil
 647	}
 648	return false, fmt.Errorf("%s already taken: %w", lower, db.ErrNameTaken)
 649}
 650
 651func (me *PsqlDB) FindUserForName(name string) (*db.User, error) {
 652	user := &db.User{}
 653	r := me.Db.QueryRow(sqlSelectUserForName, strings.ToLower(name))
 654	err := r.Scan(&user.ID, &user.Name, &user.CreatedAt)
 655	if err != nil {
 656		return nil, err
 657	}
 658	return user, nil
 659}
 660
 661func (me *PsqlDB) FindUserForNameAndKey(name string, key string) (*db.User, error) {
 662	user := &db.User{}
 663	pk := &db.PublicKey{}
 664
 665	r := me.Db.QueryRow(sqlSelectUserForNameAndKey, strings.ToLower(name), key)
 666	err := r.Scan(&user.ID, &user.Name, &user.CreatedAt, &pk.ID, &pk.Key, &pk.CreatedAt)
 667	if err != nil {
 668		return nil, err
 669	}
 670
 671	user.PublicKey = pk
 672	return user, nil
 673}
 674
 675func (me *PsqlDB) FindUserForToken(token string) (*db.User, error) {
 676	user := &db.User{}
 677
 678	r := me.Db.QueryRow(sqlSelectUserForToken, token)
 679	err := r.Scan(&user.ID, &user.Name, &user.CreatedAt)
 680	if err != nil {
 681		return nil, err
 682	}
 683
 684	return user, nil
 685}
 686
 687func (me *PsqlDB) SetUserName(userID string, name string) error {
 688	lowerName := strings.ToLower(name)
 689	valid, err := me.ValidateName(lowerName)
 690	if !valid {
 691		return err
 692	}
 693
 694	_, err = me.Db.Exec(sqlUpdateUserName, lowerName, userID)
 695	return err
 696}
 697
 698func (me *PsqlDB) FindPostWithFilename(filename string, persona_id string, space string) (*db.Post, error) {
 699	r := me.Db.QueryRow(sqlSelectPostWithFilename, filename, persona_id, space)
 700	post, err := CreatePostWithTagsFromRow(r)
 701	if err != nil {
 702		return nil, err
 703	}
 704
 705	return post, nil
 706}
 707
 708func (me *PsqlDB) FindPostWithSlug(slug string, user_id string, space string) (*db.Post, error) {
 709	r := me.Db.QueryRow(sqlSelectPostWithSlug, slug, user_id, space)
 710	post, err := CreatePostWithTagsFromRow(r)
 711	if err != nil {
 712		// attempt to find post inside post_aliases
 713		alias := me.Db.QueryRow(sqlSelectPostIdByAliasSlug, slug)
 714		postID := ""
 715		err := alias.Scan(&postID)
 716		if err != nil {
 717			return nil, err
 718		}
 719
 720		return me.FindPost(postID)
 721	}
 722
 723	return post, nil
 724}
 725
 726func (me *PsqlDB) FindPost(postID string) (*db.Post, error) {
 727	r := me.Db.QueryRow(sqlSelectPost, postID)
 728	post, err := CreatePostFromRow(r)
 729	if err != nil {
 730		return nil, err
 731	}
 732
 733	return post, nil
 734}
 735
 736func (me *PsqlDB) postPager(rs *sql.Rows, pageNum int, space string, tag string) (*db.Paginate[*db.Post], error) {
 737	var posts []*db.Post
 738	for rs.Next() {
 739		post := &db.Post{}
 740		err := rs.Scan(
 741			&post.ID,
 742			&post.UserID,
 743			&post.Filename,
 744			&post.Slug,
 745			&post.Title,
 746			&post.Text,
 747			&post.Description,
 748			&post.PublishAt,
 749			&post.Username,
 750			&post.UpdatedAt,
 751			&post.MimeType,
 752		)
 753		if err != nil {
 754			return nil, err
 755		}
 756
 757		posts = append(posts, post)
 758	}
 759	if rs.Err() != nil {
 760		return nil, rs.Err()
 761	}
 762
 763	var count int
 764	var err error
 765	if tag == "" {
 766		err = me.Db.QueryRow(sqlSelectPostCount, space).Scan(&count)
 767	} else {
 768		err = me.Db.QueryRow(sqlSelectTagPostCount, space, tag).Scan(&count)
 769	}
 770	if err != nil {
 771		return nil, err
 772	}
 773
 774	pager := &db.Paginate[*db.Post]{
 775		Data:  posts,
 776		Total: int(math.Ceil(float64(count) / float64(pageNum))),
 777	}
 778
 779	return pager, nil
 780}
 781
 782func (me *PsqlDB) FindAllPosts(page *db.Pager, space string) (*db.Paginate[*db.Post], error) {
 783	rs, err := me.Db.Query(sqlSelectPostsByRank, page.Num, page.Num*page.Page, space)
 784	if err != nil {
 785		return nil, err
 786	}
 787	return me.postPager(rs, page.Num, space, "")
 788}
 789
 790func (me *PsqlDB) FindAllUpdatedPosts(page *db.Pager, space string) (*db.Paginate[*db.Post], error) {
 791	rs, err := me.Db.Query(sqlSelectAllUpdatedPosts, page.Num, page.Num*page.Page, space)
 792	if err != nil {
 793		return nil, err
 794	}
 795	return me.postPager(rs, page.Num, space, "")
 796}
 797
 798func (me *PsqlDB) InsertPost(post *db.Post) (*db.Post, error) {
 799	var id string
 800	err := me.Db.QueryRow(
 801		sqlInsertPost,
 802		post.UserID,
 803		post.Filename,
 804		post.Slug,
 805		post.Title,
 806		post.Text,
 807		post.Description,
 808		post.PublishAt,
 809		post.Hidden,
 810		post.Space,
 811		post.FileSize,
 812		post.MimeType,
 813		post.Shasum,
 814		post.Data,
 815		post.ExpiresAt,
 816		post.UpdatedAt,
 817	).Scan(&id)
 818	if err != nil {
 819		return nil, err
 820	}
 821
 822	return me.FindPost(id)
 823}
 824
 825func (me *PsqlDB) UpdatePost(post *db.Post) (*db.Post, error) {
 826	_, err := me.Db.Exec(
 827		sqlUpdatePost,
 828		post.Slug,
 829		post.Title,
 830		post.Text,
 831		post.Description,
 832		post.UpdatedAt,
 833		post.PublishAt,
 834		post.FileSize,
 835		post.Shasum,
 836		post.Data,
 837		post.ID,
 838		post.Hidden,
 839		post.ExpiresAt,
 840	)
 841	if err != nil {
 842		return nil, err
 843	}
 844
 845	return me.FindPost(post.ID)
 846}
 847
 848func (me *PsqlDB) RemovePosts(postIDs []string) error {
 849	param := "{" + strings.Join(postIDs, ",") + "}"
 850	_, err := me.Db.Exec(sqlRemovePosts, param)
 851	return err
 852}
 853
 854func (me *PsqlDB) FindPostsForUser(page *db.Pager, userID string, space string) (*db.Paginate[*db.Post], error) {
 855	var posts []*db.Post
 856	rs, err := me.Db.Query(
 857		sqlSelectPostsForUser,
 858		userID,
 859		space,
 860		page.Num,
 861		page.Num*page.Page,
 862	)
 863	if err != nil {
 864		return nil, err
 865	}
 866	for rs.Next() {
 867		post, err := CreatePostWithTagsFromRow(rs)
 868		if err != nil {
 869			return nil, err
 870		}
 871
 872		posts = append(posts, post)
 873	}
 874
 875	if rs.Err() != nil {
 876		return nil, rs.Err()
 877	}
 878
 879	var count int
 880	err = me.Db.QueryRow(sqlSelectPostCount, space).Scan(&count)
 881	if err != nil {
 882		return nil, err
 883	}
 884
 885	pager := &db.Paginate[*db.Post]{
 886		Data:  posts,
 887		Total: int(math.Ceil(float64(count) / float64(page.Num))),
 888	}
 889	return pager, nil
 890}
 891
 892func (me *PsqlDB) FindAllPostsForUser(userID string, space string) ([]*db.Post, error) {
 893	var posts []*db.Post
 894	rs, err := me.Db.Query(sqlSelectAllPostsForUser, userID, space)
 895	if err != nil {
 896		return posts, err
 897	}
 898	for rs.Next() {
 899		post, err := CreatePostFromRow(rs)
 900		if err != nil {
 901			return nil, err
 902		}
 903
 904		posts = append(posts, post)
 905	}
 906	if rs.Err() != nil {
 907		return posts, rs.Err()
 908	}
 909	return posts, nil
 910}
 911
 912func (me *PsqlDB) FindPosts() ([]*db.Post, error) {
 913	var posts []*db.Post
 914	rs, err := me.Db.Query(sqlSelectPosts)
 915	if err != nil {
 916		return posts, err
 917	}
 918	for rs.Next() {
 919		post, err := CreatePostFromRow(rs)
 920		if err != nil {
 921			return nil, err
 922		}
 923
 924		posts = append(posts, post)
 925	}
 926	if rs.Err() != nil {
 927		return posts, rs.Err()
 928	}
 929	return posts, nil
 930}
 931
 932func (me *PsqlDB) FindExpiredPosts(space string) ([]*db.Post, error) {
 933	var posts []*db.Post
 934	rs, err := me.Db.Query(sqlSelectExpiredPosts, space)
 935	if err != nil {
 936		return posts, err
 937	}
 938	for rs.Next() {
 939		post, err := CreatePostFromRow(rs)
 940		if err != nil {
 941			return nil, err
 942		}
 943
 944		posts = append(posts, post)
 945	}
 946	if rs.Err() != nil {
 947		return posts, rs.Err()
 948	}
 949	return posts, nil
 950}
 951
 952func (me *PsqlDB) FindUpdatedPostsForUser(userID string, space string) ([]*db.Post, error) {
 953	var posts []*db.Post
 954	rs, err := me.Db.Query(sqlSelectUpdatedPostsForUser, userID, space)
 955	if err != nil {
 956		return posts, err
 957	}
 958	for rs.Next() {
 959		post, err := CreatePostFromRow(rs)
 960		if err != nil {
 961			return nil, err
 962		}
 963
 964		posts = append(posts, post)
 965	}
 966	if rs.Err() != nil {
 967		return posts, rs.Err()
 968	}
 969	return posts, nil
 970}
 971
 972func (me *PsqlDB) Close() error {
 973	me.Logger.Info("Closing db")
 974	return me.Db.Close()
 975}
 976
 977func newNullString(s string) sql.NullString {
 978	if len(s) == 0 {
 979		return sql.NullString{}
 980	}
 981	return sql.NullString{
 982		String: s,
 983		Valid:  true,
 984	}
 985}
 986
 987func (me *PsqlDB) InsertVisit(visit *db.AnalyticsVisits) error {
 988	_, err := me.Db.Exec(
 989		`INSERT INTO analytics_visits (user_id, project_id, post_id, namespace, host, path, ip_address, user_agent, referer, status) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10);`,
 990		visit.UserID,
 991		newNullString(visit.ProjectID),
 992		newNullString(visit.PostID),
 993		newNullString(visit.Namespace),
 994		visit.Host,
 995		visit.Path,
 996		visit.IpAddress,
 997		visit.UserAgent,
 998		visit.Referer,
 999		visit.Status,
1000	)
1001	return err
1002}
1003
1004func (me *PsqlDB) visitUniqueBlog(opts *db.SummaryOpts) ([]*db.VisitInterval, error) {
1005	uniqueVisitors := fmt.Sprintf(`SELECT
1006		date_trunc('%s', created_at) as interval_start,
1007        count(DISTINCT ip_address) as unique_visitors
1008	FROM analytics_visits
1009	WHERE %s=$1 AND created_at >= $2 AND status <> 404 %s
1010	GROUP BY interval_start`, opts.Interval, opts.By, opts.Where)
1011
1012	intervals := []*db.VisitInterval{}
1013	rs, err := me.Db.Query(uniqueVisitors, opts.FkID, opts.Origin)
1014	if err != nil {
1015		return nil, err
1016	}
1017
1018	for rs.Next() {
1019		interval := &db.VisitInterval{}
1020		err := rs.Scan(
1021			&interval.Interval,
1022			&interval.Visitors,
1023		)
1024		if err != nil {
1025			return nil, err
1026		}
1027
1028		intervals = append(intervals, interval)
1029	}
1030	if rs.Err() != nil {
1031		return nil, rs.Err()
1032	}
1033	return intervals, nil
1034}
1035
1036func (me *PsqlDB) visitUnique(opts *db.SummaryOpts) ([]*db.VisitInterval, error) {
1037	uniqueVisitors := fmt.Sprintf(`SELECT
1038		post_id,
1039		project_id,
1040		date_trunc('%s', created_at) as interval_start,
1041        count(DISTINCT ip_address) as unique_visitors
1042	FROM analytics_visits
1043	WHERE %s=$1 AND created_at >= $2 AND status <> 404 %s
1044	GROUP BY post_id, project_id, interval_start`, opts.Interval, opts.By, opts.Where)
1045
1046	intervals := []*db.VisitInterval{}
1047	rs, err := me.Db.Query(uniqueVisitors, opts.FkID, opts.Origin)
1048	if err != nil {
1049		return nil, err
1050	}
1051
1052	for rs.Next() {
1053		interval := &db.VisitInterval{}
1054		var postID sql.NullString
1055		var projectID sql.NullString
1056		err := rs.Scan(
1057			&postID,
1058			&projectID,
1059			&interval.Interval,
1060			&interval.Visitors,
1061		)
1062		if err != nil {
1063			return nil, err
1064		}
1065		interval.PostID = postID.String
1066		interval.ProjectID = projectID.String
1067
1068		intervals = append(intervals, interval)
1069	}
1070	if rs.Err() != nil {
1071		return nil, rs.Err()
1072	}
1073	return intervals, nil
1074}
1075
1076func (me *PsqlDB) visitReferer(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
1077	topUrls := fmt.Sprintf(`SELECT
1078		referer,
1079		count(DISTINCT ip_address) as referer_count
1080	FROM analytics_visits
1081	WHERE %s=$1 AND created_at >= $2 AND referer <> '' AND status <> 404 %s
1082	GROUP BY referer
1083	ORDER BY referer_count DESC
1084	LIMIT 10`, opts.By, opts.Where)
1085
1086	intervals := []*db.VisitUrl{}
1087	rs, err := me.Db.Query(topUrls, opts.FkID, opts.Origin)
1088	if err != nil {
1089		return nil, err
1090	}
1091
1092	for rs.Next() {
1093		interval := &db.VisitUrl{}
1094		err := rs.Scan(
1095			&interval.Url,
1096			&interval.Count,
1097		)
1098		if err != nil {
1099			return nil, err
1100		}
1101
1102		intervals = append(intervals, interval)
1103	}
1104	if rs.Err() != nil {
1105		return nil, rs.Err()
1106	}
1107	return intervals, nil
1108}
1109
1110func (me *PsqlDB) visitUrl(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
1111	topUrls := fmt.Sprintf(`SELECT
1112		path,
1113		count(DISTINCT ip_address) as path_count,
1114		post_id,
1115		project_id
1116	FROM analytics_visits
1117	WHERE %s=$1 AND created_at >= $2 AND path <> '' AND status <> 404 %s
1118	GROUP BY path, post_id, project_id
1119	ORDER BY path_count DESC
1120	LIMIT 10`, opts.By, opts.Where)
1121
1122	intervals := []*db.VisitUrl{}
1123	rs, err := me.Db.Query(topUrls, opts.FkID, opts.Origin)
1124	if err != nil {
1125		return nil, err
1126	}
1127
1128	for rs.Next() {
1129		interval := &db.VisitUrl{}
1130		var postID sql.NullString
1131		var projectID sql.NullString
1132		err := rs.Scan(
1133			&interval.Url,
1134			&interval.Count,
1135			&postID,
1136			&projectID,
1137		)
1138		if err != nil {
1139			return nil, err
1140		}
1141		interval.PostID = postID.String
1142		interval.ProjectID = projectID.String
1143
1144		intervals = append(intervals, interval)
1145	}
1146	if rs.Err() != nil {
1147		return nil, rs.Err()
1148	}
1149	return intervals, nil
1150}
1151
1152func (me *PsqlDB) VisitSummary(opts *db.SummaryOpts) (*db.SummaryVisits, error) {
1153	var visitors []*db.VisitInterval
1154	var err error
1155	if opts.Where == "" {
1156		visitors, err = me.visitUnique(opts)
1157		if err != nil {
1158			return nil, err
1159		}
1160	} else {
1161		visitors, err = me.visitUniqueBlog(opts)
1162		if err != nil {
1163			return nil, err
1164		}
1165	}
1166
1167	urls, err := me.visitUrl(opts)
1168	if err != nil {
1169		return nil, err
1170	}
1171	refs, err := me.visitReferer(opts)
1172	if err != nil {
1173		return nil, err
1174	}
1175	return &db.SummaryVisits{
1176		Intervals:   visitors,
1177		TopUrls:     urls,
1178		TopReferers: refs,
1179	}, nil
1180}
1181
1182func (me *PsqlDB) FindUsers() ([]*db.User, error) {
1183	var users []*db.User
1184	rs, err := me.Db.Query(sqlSelectUsers)
1185	if err != nil {
1186		return users, err
1187	}
1188	for rs.Next() {
1189		var name sql.NullString
1190		user := &db.User{}
1191		err := rs.Scan(
1192			&user.ID,
1193			&name,
1194			&user.CreatedAt,
1195		)
1196		if err != nil {
1197			return users, err
1198		}
1199		user.Name = name.String
1200
1201		users = append(users, user)
1202	}
1203	if rs.Err() != nil {
1204		return users, rs.Err()
1205	}
1206	return users, nil
1207}
1208
1209func (me *PsqlDB) removeTagsForPost(tx *sql.Tx, postID string) error {
1210	_, err := tx.Exec(sqlRemoveTagsByPost, postID)
1211	return err
1212}
1213
1214func (me *PsqlDB) insertTagsForPost(tx *sql.Tx, tags []string, postID string) ([]string, error) {
1215	ids := make([]string, 0)
1216	for _, tag := range tags {
1217		id := ""
1218		err := tx.QueryRow(sqlInsertTag, postID, tag).Scan(&id)
1219		if err != nil {
1220			return nil, err
1221		}
1222		ids = append(ids, id)
1223	}
1224
1225	return ids, nil
1226}
1227
1228func (me *PsqlDB) ReplaceTagsForPost(tags []string, postID string) error {
1229	ctx := context.Background()
1230	tx, err := me.Db.BeginTx(ctx, nil)
1231	if err != nil {
1232		return err
1233	}
1234	defer func() {
1235		err = tx.Rollback()
1236	}()
1237
1238	err = me.removeTagsForPost(tx, postID)
1239	if err != nil {
1240		return err
1241	}
1242
1243	_, err = me.insertTagsForPost(tx, tags, postID)
1244	if err != nil {
1245		return err
1246	}
1247
1248	err = tx.Commit()
1249	return err
1250}
1251
1252func (me *PsqlDB) removeAliasesForPost(tx *sql.Tx, postID string) error {
1253	_, err := tx.Exec(sqlRemoveAliasesByPost, postID)
1254	return err
1255}
1256
1257func (me *PsqlDB) insertAliasesForPost(tx *sql.Tx, aliases []string, postID string) ([]string, error) {
1258	// hardcoded
1259	denyList := []string{
1260		"rss",
1261		"rss.xml",
1262		"atom.xml",
1263		"feed.xml",
1264		"smol.css",
1265		"main.css",
1266		"syntax.css",
1267		"card.png",
1268		"favicon-16x16.png",
1269		"favicon-32x32.png",
1270		"apple-touch-icon.png",
1271		"favicon.ico",
1272		"robots.txt",
1273		"atom",
1274		"blog/index.xml",
1275	}
1276
1277	ids := make([]string, 0)
1278	for _, alias := range aliases {
1279		if slices.Contains(denyList, alias) {
1280			me.Logger.Info(
1281				"name is in the deny list for aliases because it conflicts with a static route, skipping",
1282				"alias", alias,
1283			)
1284			continue
1285		}
1286		id := ""
1287		err := tx.QueryRow(sqlInsertAliases, postID, alias).Scan(&id)
1288		if err != nil {
1289			return nil, err
1290		}
1291		ids = append(ids, id)
1292	}
1293
1294	return ids, nil
1295}
1296
1297func (me *PsqlDB) ReplaceAliasesForPost(aliases []string, postID string) error {
1298	ctx := context.Background()
1299	tx, err := me.Db.BeginTx(ctx, nil)
1300	if err != nil {
1301		return err
1302	}
1303	defer func() {
1304		err = tx.Rollback()
1305	}()
1306
1307	err = me.removeAliasesForPost(tx, postID)
1308	if err != nil {
1309		return err
1310	}
1311
1312	_, err = me.insertAliasesForPost(tx, aliases, postID)
1313	if err != nil {
1314		return err
1315	}
1316
1317	err = tx.Commit()
1318	return err
1319}
1320
1321func (me *PsqlDB) FindUserPostsByTag(page *db.Pager, tag, userID, space string) (*db.Paginate[*db.Post], error) {
1322	var posts []*db.Post
1323	rs, err := me.Db.Query(
1324		sqlSelectUserPostsByTag,
1325		userID,
1326		tag,
1327		space,
1328		page.Num,
1329		page.Num*page.Page,
1330	)
1331	if err != nil {
1332		return nil, err
1333	}
1334	for rs.Next() {
1335		post, err := CreatePostFromRow(rs)
1336		if err != nil {
1337			return nil, err
1338		}
1339
1340		posts = append(posts, post)
1341	}
1342
1343	if rs.Err() != nil {
1344		return nil, rs.Err()
1345	}
1346
1347	var count int
1348	err = me.Db.QueryRow(sqlSelectPostCount, space).Scan(&count)
1349	if err != nil {
1350		return nil, err
1351	}
1352
1353	pager := &db.Paginate[*db.Post]{
1354		Data:  posts,
1355		Total: int(math.Ceil(float64(count) / float64(page.Num))),
1356	}
1357	return pager, nil
1358}
1359
1360func (me *PsqlDB) FindPostsByTag(pager *db.Pager, tag, space string) (*db.Paginate[*db.Post], error) {
1361	rs, err := me.Db.Query(
1362		sqlSelectPostsByTag,
1363		pager.Num,
1364		pager.Num*pager.Page,
1365		tag,
1366		space,
1367	)
1368	if err != nil {
1369		return nil, err
1370	}
1371
1372	return me.postPager(rs, pager.Num, space, tag)
1373}
1374
1375func (me *PsqlDB) FindPopularTags(space string) ([]string, error) {
1376	tags := make([]string, 0)
1377	rs, err := me.Db.Query(sqlSelectPopularTags, space)
1378	if err != nil {
1379		return tags, err
1380	}
1381	for rs.Next() {
1382		name := ""
1383		tally := 0
1384		err := rs.Scan(&name, &tally)
1385		if err != nil {
1386			return tags, err
1387		}
1388
1389		tags = append(tags, name)
1390	}
1391	if rs.Err() != nil {
1392		return tags, rs.Err()
1393	}
1394	return tags, nil
1395}
1396
1397func (me *PsqlDB) FindTagsForPost(postID string) ([]string, error) {
1398	tags := make([]string, 0)
1399	rs, err := me.Db.Query(sqlSelectTagsForPost, postID)
1400	if err != nil {
1401		return tags, err
1402	}
1403
1404	for rs.Next() {
1405		name := ""
1406		err := rs.Scan(&name)
1407		if err != nil {
1408			return tags, err
1409		}
1410
1411		tags = append(tags, name)
1412	}
1413
1414	if rs.Err() != nil {
1415		return tags, rs.Err()
1416	}
1417
1418	return tags, nil
1419}
1420
1421func (me *PsqlDB) FindFeatureForUser(userID string, feature string) (*db.FeatureFlag, error) {
1422	ff := &db.FeatureFlag{}
1423	// payment history is allowed to be null
1424	// https://devtidbits.com/2020/08/03/go-sql-error-converting-null-to-string-is-unsupported/
1425	var paymentHistoryID sql.NullString
1426	err := me.Db.QueryRow(sqlSelectFeatureForUser, userID, feature).Scan(
1427		&ff.ID,
1428		&ff.UserID,
1429		&paymentHistoryID,
1430		&ff.Name,
1431		&ff.Data,
1432		&ff.CreatedAt,
1433		&ff.ExpiresAt,
1434	)
1435	if err != nil {
1436		return nil, err
1437	}
1438
1439	ff.PaymentHistoryID = paymentHistoryID.String
1440
1441	return ff, nil
1442}
1443
1444func (me *PsqlDB) FindFeaturesForUser(userID string) ([]*db.FeatureFlag, error) {
1445	var features []*db.FeatureFlag
1446	// https://stackoverflow.com/a/16920077
1447	query := `SELECT DISTINCT ON (name)
1448			id, user_id, payment_history_id, name, data, created_at, expires_at
1449		FROM feature_flags
1450		WHERE user_id=$1
1451		ORDER BY name, expires_at DESC;`
1452	rs, err := me.Db.Query(query, userID)
1453	if err != nil {
1454		return features, err
1455	}
1456	for rs.Next() {
1457		var paymentHistoryID sql.NullString
1458		ff := &db.FeatureFlag{}
1459		err := rs.Scan(
1460			&ff.ID,
1461			&ff.UserID,
1462			&paymentHistoryID,
1463			&ff.Name,
1464			&ff.Data,
1465			&ff.CreatedAt,
1466			&ff.ExpiresAt,
1467		)
1468		if err != nil {
1469			return features, err
1470		}
1471		ff.PaymentHistoryID = paymentHistoryID.String
1472
1473		features = append(features, ff)
1474	}
1475	if rs.Err() != nil {
1476		return features, rs.Err()
1477	}
1478	return features, nil
1479}
1480
1481func (me *PsqlDB) HasFeatureForUser(userID string, feature string) bool {
1482	ff, err := me.FindFeatureForUser(userID, feature)
1483	if err != nil {
1484		return false
1485	}
1486	return ff.IsValid()
1487}
1488
1489func (me *PsqlDB) FindTotalSizeForUser(userID string) (int, error) {
1490	var fileSize int
1491	err := me.Db.QueryRow(sqlSelectSizeForUser, userID).Scan(&fileSize)
1492	if err != nil {
1493		return 0, err
1494	}
1495	return fileSize, nil
1496}
1497
1498func (me *PsqlDB) InsertFeedItems(postID string, items []*db.FeedItem) error {
1499	ctx := context.Background()
1500	tx, err := me.Db.BeginTx(ctx, nil)
1501	if err != nil {
1502		return err
1503	}
1504	defer func() {
1505		err = tx.Rollback()
1506	}()
1507
1508	for _, item := range items {
1509		_, err := tx.Exec(
1510			sqlInsertFeedItems,
1511			item.PostID,
1512			item.GUID,
1513			item.Data,
1514		)
1515		if err != nil {
1516			return err
1517		}
1518	}
1519
1520	err = tx.Commit()
1521	return err
1522}
1523
1524func (me *PsqlDB) FindFeedItemsByPostID(postID string) ([]*db.FeedItem, error) {
1525	// sqlSelectFeedItemsByPost
1526	items := make([]*db.FeedItem, 0)
1527	rs, err := me.Db.Query(sqlSelectFeedItemsByPost, postID)
1528	if err != nil {
1529		return items, err
1530	}
1531
1532	for rs.Next() {
1533		item := &db.FeedItem{}
1534		err := rs.Scan(
1535			&item.ID,
1536			&item.PostID,
1537			&item.GUID,
1538			&item.Data,
1539			&item.CreatedAt,
1540		)
1541		if err != nil {
1542			return items, err
1543		}
1544
1545		items = append(items, item)
1546	}
1547
1548	if rs.Err() != nil {
1549		return items, rs.Err()
1550	}
1551
1552	return items, nil
1553}
1554
1555func (me *PsqlDB) InsertProject(userID, name, projectDir string) (string, error) {
1556	if !utils.IsValidSubdomain(name) {
1557		return "", fmt.Errorf("'%s' is not a valid project name, must match /^[a-z0-9-]+$/", name)
1558	}
1559
1560	var id string
1561	err := me.Db.QueryRow(sqlInsertProject, userID, name, projectDir).Scan(&id)
1562	if err != nil {
1563		return "", err
1564	}
1565	return id, nil
1566}
1567
1568func (me *PsqlDB) UpdateProject(userID, name string) error {
1569	_, err := me.Db.Exec(sqlUpdateProject, userID, name, time.Now())
1570	return err
1571}
1572
1573func (me *PsqlDB) UpdateProjectAcl(userID, name string, acl db.ProjectAcl) error {
1574	_, err := me.Db.Exec(sqlUpdateProjectAcl, userID, name, acl, time.Now())
1575	return err
1576}
1577
1578func (me *PsqlDB) LinkToProject(userID, projectID, projectDir string, commit bool) error {
1579	linkToProject, err := me.FindProjectByName(userID, projectDir)
1580	if err != nil {
1581		return err
1582	}
1583	isAlreadyLinked := linkToProject.Name != linkToProject.ProjectDir
1584	sameProject := linkToProject.ID == projectID
1585
1586	/*
1587		A project linked to another project which is also linked to a
1588		project is forbidden.  CI/CD Example:
1589			- ProjectProd links to ProjectStaging
1590			- ProjectStaging links to ProjectMain
1591			- We merge `main` and trigger a deploy which uploads to ProjectMain
1592			- All three get updated immediately
1593		This scenario was not the intent of our CI/CD.  What we actually
1594		wanted was to create a snapshot of ProjectMain and have ProjectStaging
1595		link to the snapshot, but that's not the intended design of pgs.
1596
1597		So we want to close that gap here.
1598
1599		We ensure that `project.Name` and `project.ProjectDir` are identical
1600		when there is no aliasing.
1601	*/
1602	if !sameProject && isAlreadyLinked {
1603		return fmt.Errorf(
1604			"cannot link (%s) to (%s) because it is also a link to (%s)",
1605			projectID,
1606			projectDir,
1607			linkToProject.ProjectDir,
1608		)
1609	}
1610
1611	if commit {
1612		_, err = me.Db.Exec(
1613			sqlLinkToProject,
1614			projectDir,
1615			time.Now(),
1616			projectID,
1617		)
1618	}
1619	return err
1620}
1621
1622func (me *PsqlDB) RemoveProject(projectID string) error {
1623	_, err := me.Db.Exec(sqlRemoveProject, projectID)
1624	return err
1625}
1626
1627func (me *PsqlDB) FindProjectByName(userID, name string) (*db.Project, error) {
1628	project := &db.Project{}
1629	r := me.Db.QueryRow(sqlFindProjectByName, userID, name)
1630	err := r.Scan(
1631		&project.ID,
1632		&project.UserID,
1633		&project.Name,
1634		&project.ProjectDir,
1635		&project.Acl,
1636		&project.Blocked,
1637		&project.CreatedAt,
1638		&project.UpdatedAt,
1639	)
1640	if err != nil {
1641		return nil, err
1642	}
1643
1644	return project, nil
1645}
1646
1647func (me *PsqlDB) FindProjectLinks(userID, name string) ([]*db.Project, error) {
1648	var projects []*db.Project
1649	rs, err := me.Db.Query(sqlFindProjectLinks, userID, name)
1650	if err != nil {
1651		return nil, err
1652	}
1653	for rs.Next() {
1654		project := &db.Project{}
1655		err := rs.Scan(
1656			&project.ID,
1657			&project.UserID,
1658			&project.Name,
1659			&project.ProjectDir,
1660			&project.Acl,
1661			&project.Blocked,
1662			&project.CreatedAt,
1663			&project.UpdatedAt,
1664		)
1665		if err != nil {
1666			return nil, err
1667		}
1668
1669		projects = append(projects, project)
1670	}
1671
1672	if rs.Err() != nil {
1673		return nil, rs.Err()
1674	}
1675
1676	return projects, nil
1677}
1678
1679func (me *PsqlDB) FindProjectsByPrefix(userID, prefix string) ([]*db.Project, error) {
1680	var projects []*db.Project
1681	rs, err := me.Db.Query(sqlFindProjectsByPrefix, userID, prefix+"%")
1682	if err != nil {
1683		return nil, err
1684	}
1685	for rs.Next() {
1686		project := &db.Project{}
1687		err := rs.Scan(
1688			&project.ID,
1689			&project.UserID,
1690			&project.Name,
1691			&project.ProjectDir,
1692			&project.Acl,
1693			&project.Blocked,
1694			&project.CreatedAt,
1695			&project.UpdatedAt,
1696		)
1697		if err != nil {
1698			return nil, err
1699		}
1700
1701		projects = append(projects, project)
1702	}
1703
1704	if rs.Err() != nil {
1705		return nil, rs.Err()
1706	}
1707
1708	return projects, nil
1709}
1710
1711func (me *PsqlDB) FindProjectsByUser(userID string) ([]*db.Project, error) {
1712	var projects []*db.Project
1713	rs, err := me.Db.Query(sqlFindProjectsByUser, userID)
1714	if err != nil {
1715		return nil, err
1716	}
1717	for rs.Next() {
1718		project := &db.Project{}
1719		err := rs.Scan(
1720			&project.ID,
1721			&project.UserID,
1722			&project.Name,
1723			&project.ProjectDir,
1724			&project.Acl,
1725			&project.Blocked,
1726			&project.CreatedAt,
1727			&project.UpdatedAt,
1728		)
1729		if err != nil {
1730			return nil, err
1731		}
1732
1733		projects = append(projects, project)
1734	}
1735
1736	if rs.Err() != nil {
1737		return nil, rs.Err()
1738	}
1739
1740	return projects, nil
1741}
1742
1743func (me *PsqlDB) FindAllProjects(page *db.Pager, by string) (*db.Paginate[*db.Project], error) {
1744	var projects []*db.Project
1745	sqlFindAllProjects := fmt.Sprintf(`
1746	SELECT projects.id, user_id, app_users.name as username, projects.name, project_dir, projects.acl, projects.blocked, projects.created_at, projects.updated_at
1747	FROM projects
1748	LEFT JOIN app_users ON app_users.id = projects.user_id
1749	ORDER BY %s DESC
1750	LIMIT $1 OFFSET $2`, by)
1751	rs, err := me.Db.Query(sqlFindAllProjects, page.Num, page.Num*page.Page)
1752	if err != nil {
1753		return nil, err
1754	}
1755	for rs.Next() {
1756		project := &db.Project{}
1757		err := rs.Scan(
1758			&project.ID,
1759			&project.UserID,
1760			&project.Username,
1761			&project.Name,
1762			&project.ProjectDir,
1763			&project.Acl,
1764			&project.Blocked,
1765			&project.CreatedAt,
1766			&project.UpdatedAt,
1767		)
1768		if err != nil {
1769			return nil, err
1770		}
1771
1772		projects = append(projects, project)
1773	}
1774
1775	if rs.Err() != nil {
1776		return nil, rs.Err()
1777	}
1778
1779	var count int
1780	err = me.Db.QueryRow(sqlSelectProjectCount).Scan(&count)
1781	if err != nil {
1782		return nil, err
1783	}
1784
1785	pager := &db.Paginate[*db.Project]{
1786		Data:  projects,
1787		Total: int(math.Ceil(float64(count) / float64(page.Num))),
1788	}
1789
1790	return pager, nil
1791}
1792
1793func (me *PsqlDB) InsertToken(userID, name string) (string, error) {
1794	var token string
1795	err := me.Db.QueryRow(sqlInsertToken, userID, name).Scan(&token)
1796	if err != nil {
1797		return "", err
1798	}
1799	return token, nil
1800}
1801
1802func (me *PsqlDB) UpsertToken(userID, name string) (string, error) {
1803	token, _ := me.FindTokenByName(userID, name)
1804	if token != "" {
1805		return token, nil
1806	}
1807
1808	token, err := me.InsertToken(userID, name)
1809	return token, err
1810}
1811
1812func (me *PsqlDB) FindTokenByName(userID, name string) (string, error) {
1813	var token string
1814	err := me.Db.QueryRow(sqlSelectTokenByNameForUser, userID, name).Scan(&token)
1815	if err != nil {
1816		return "", err
1817	}
1818	return token, nil
1819}
1820
1821func (me *PsqlDB) RemoveToken(tokenID string) error {
1822	_, err := me.Db.Exec(sqlRemoveToken, tokenID)
1823	return err
1824}
1825
1826func (me *PsqlDB) FindTokensForUser(userID string) ([]*db.Token, error) {
1827	var keys []*db.Token
1828	rs, err := me.Db.Query(sqlSelectTokensForUser, userID)
1829	if err != nil {
1830		return keys, err
1831	}
1832	for rs.Next() {
1833		pk := &db.Token{}
1834		err := rs.Scan(&pk.ID, &pk.UserID, &pk.Name, &pk.CreatedAt, &pk.ExpiresAt)
1835		if err != nil {
1836			return keys, err
1837		}
1838
1839		keys = append(keys, pk)
1840	}
1841	if rs.Err() != nil {
1842		return keys, rs.Err()
1843	}
1844	return keys, nil
1845}
1846
1847func (me *PsqlDB) InsertFeature(userID, name string, expiresAt time.Time) (*db.FeatureFlag, error) {
1848	var featureID string
1849	err := me.Db.QueryRow(
1850		`INSERT INTO feature_flags (user_id, name, expires_at) VALUES ($1, $2, $3) RETURNING id;`,
1851		userID,
1852		name,
1853		expiresAt,
1854	).Scan(&featureID)
1855	if err != nil {
1856		return nil, err
1857	}
1858
1859	feature, err := me.FindFeatureForUser(userID, name)
1860	if err != nil {
1861		return nil, err
1862	}
1863
1864	return feature, nil
1865}
1866
1867func (me *PsqlDB) RemoveFeature(userID string, name string) error {
1868	_, err := me.Db.Exec(`DELETE FROM feature_flags WHERE user_id = $1 AND name = $2`, userID, name)
1869	return err
1870}
1871
1872func (me *PsqlDB) createFeatureExpiresAt(userID, name string) time.Time {
1873	ff, _ := me.FindFeatureForUser(userID, name)
1874	if ff == nil {
1875		t := time.Now()
1876		return t.AddDate(1, 0, 0)
1877	}
1878	return ff.ExpiresAt.AddDate(1, 0, 0)
1879}
1880
1881func (me *PsqlDB) AddPicoPlusUser(username, paymentType, txId string) error {
1882	user, err := me.FindUserForName(username)
1883	if err != nil {
1884		return err
1885	}
1886
1887	ctx := context.Background()
1888	tx, err := me.Db.BeginTx(ctx, nil)
1889	if err != nil {
1890		return err
1891	}
1892	defer func() {
1893		err = tx.Rollback()
1894	}()
1895
1896	var paymentHistoryId sql.NullString
1897	if paymentType != "" {
1898		data := db.PaymentHistoryData{
1899			Notes: "",
1900			TxID:  txId,
1901		}
1902
1903		err := tx.QueryRow(
1904			`INSERT INTO payment_history (user_id, payment_type, amount, data) VALUES ($1, $2, 24 * 1000000, $3) RETURNING id;`,
1905			user.ID,
1906			paymentType,
1907			data,
1908		).Scan(&paymentHistoryId)
1909		if err != nil {
1910			return err
1911		}
1912	}
1913
1914	plus := me.createFeatureExpiresAt(user.ID, "plus")
1915	plusQuery := `INSERT INTO feature_flags (user_id, name, data, expires_at, payment_history_id)
1916		VALUES ($1, 'plus', '{"storage_max":10000000000, "file_max":50000000}'::jsonb, $2, $3);`
1917	_, err = tx.Exec(plusQuery, user.ID, plus, paymentHistoryId)
1918	if err != nil {
1919		return err
1920	}
1921
1922	return tx.Commit()
1923}