perf(db): add missing indexes, drop unused ones, trim queries

Commit `de92f37`

Antonio Mika · 2026-10-25 14:56:38 -0400 EDT

parent 0ff8a38

perf(db): add missing indexes, drop unused ones, trim queries

15 files changed, +189, -227

M Makefile

M pkg/apps/auth/analytics_aggregate.go

M pkg/apps/pgs/db/postgres.go

A pkg/apps/pgs/db/postgres_test.go

M pkg/apps/prose/db.go

M pkg/apps/prose/db_test.go

M pkg/db/db.go

M pkg/db/postgres/analytics.go

M pkg/db/postgres/posts.go

M pkg/db/postgres/storage_test.go

M pkg/db/stub/stub.go

A sql/migrations/20261025_add_access_logs_user_created_idx.sql

A sql/migrations/20261025_add_is_mobile_to_analytics.sql

A sql/migrations/20261025_add_lookup_indexes.sql

A sql/migrations/20261025_drop_unused_analytics_indexes.sql

M Makefile

+5, -1

......@@ -205,10 +205,14 @@ migrate:
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260716_block_signups.sql
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260925_add_invites.sql
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20261010_add_pastes_invite_feature.sql
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20261025_add_access_logs_user_created_idx.sql
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20261025_add_is_mobile_to_analytics.sql
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20261025_add_lookup_indexes.sql
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20261025_drop_unused_analytics_indexes.sql
.PHONY: migrate

latest:
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20261010_add_pastes_invite_feature.sql
	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20261025_drop_unused_analytics_indexes.sql
.PHONY: latest

psql:

M pkg/apps/auth/analytics_aggregate.go

+2, -23

......@@ -4,9 +4,9 @@ import (
	"context"
	"fmt"
	"log/slog"
	"strings"
	"time"

	"github.com/picosh/pico/pkg/db"
	"github.com/picosh/pico/pkg/db/postgres"
)

......@@ -224,7 +224,7 @@ func aggregateDays(visits []visitRow) map[string]*dayStats {
		}

		ds.AllIPs[v.IPAddress] = true
		if isMobile(v.UserAgent) {
		if db.IsMobileUserAgent(v.UserAgent) {
			ds.MobileIPs[v.IPAddress] = true
		} else {
			ds.DesktopIPs[v.IPAddress] = true

......@@ -387,27 +387,6 @@ func upsertUserSite(dbpool *postgres.PsqlDB, userID, host string, monthUnique in
	return err
}

// isMobile checks common mobile user-agent patterns.
// TODO: replace with a proper UA parser library (e.g., mssola/useragent).
func isMobile(userAgent string) bool {
	ua := strings.ToLower(userAgent)
	mobileKeywords := []string{
		"mobile",
		"android",
		"iphone",
		"ipad",
		"ipod",
		"blackberry",
		"windows phone",
	}
	for _, kw := range mobileKeywords {
		if strings.Contains(ua, kw) {
			return true
		}
	}
	return false
}

// deleteAggregatedVisits deletes raw visit data from analytics_visits for the given month.
// Data is preserved in summary tables, so this is safe regardless of feature flags.
// Raw data for the current and previous months is never deleted since visitUniqueFromRaw

M pkg/apps/pgs/db/postgres.go

+7, -3

......@@ -252,14 +252,18 @@ func (me *PgsPsqlDB) FindProjectsByUser(userID string) ([]*db.Project, error) {
}

func (me *PgsPsqlDB) FindProjects(by string) ([]*db.Project, error) {
	// A bind parameter in ORDER BY sorts by a constant, so map to a column.
	orderBy := "p.created_at"
	if by == "updated_at" {
		orderBy = "p.updated_at"
	}
	projects := []*db.Project{}
	err := me.Db.Select(
		&projects,
		`SELECT p.id, p.user_id, u.name as username, p.name, p.project_dir, p.acl, p.blocked, p.created_at, p.updated_at
		fmt.Sprintf(`SELECT p.id, p.user_id, u.name as username, p.name, p.project_dir, p.acl, p.blocked, p.created_at, p.updated_at
		FROM projects AS p
		LEFT JOIN app_users AS u ON u.id = p.user_id
		ORDER BY $1 DESC`,
		by,
		ORDER BY %s DESC`, orderBy),
	)
	return projects, err
}

A pkg/apps/pgs/db/postgres_test.go

+63, -0

......@@ -0,0 +1,63 @@
package pgsdb

import (
	"log/slog"
	"testing"
	"time"

	"github.com/jmoiron/sqlx"
	"github.com/picosh/pico/pkg/db/postgres/pgtest"
)

func TestMain(m *testing.M) {
	pgtest.Main(m)
}

func TestFindProjectsOrder(t *testing.T) {
	conn := sqlx.MustConnect("postgres", pgtest.URL(t))
	t.Cleanup(func() { _ = conn.Close() })
	pgtest.Clean(t, conn)
	testDB := NewDBWithConn(conn, slog.New(slog.DiscardHandler))

	var userID string
	if err := conn.Get(&userID, `INSERT INTO app_users (name) VALUES ('projectsowner') RETURNING id`); err != nil {
		t.Fatalf("insert user: %v", err)
	}

	// "older" was created first but updated last, so the two orderings differ.
	now := time.Now()
	for _, p := range []struct {
		name      string
		createdAt time.Time
		updatedAt time.Time
	}{
		{"older", now.Add(-2 * time.Hour), now},
		{"newer", now.Add(-1 * time.Hour), now.Add(-1 * time.Hour)},
	} {
		id, err := testDB.InsertProject(userID, p.name, p.name)
		if err != nil {
			t.Fatalf("insert project %s: %v", p.name, err)
		}
		_, err = conn.Exec(`UPDATE projects SET created_at = $1, updated_at = $2 WHERE id = $3`, p.createdAt, p.updatedAt, id)
		if err != nil {
			t.Fatalf("set project %s times: %v", p.name, err)
		}
	}

	for by, want := range map[string][]string{
		"created_at": {"newer", "older"},
		"updated_at": {"older", "newer"},
	} {
		projects, err := testDB.FindProjects(by)
		if err != nil {
			t.Fatalf("FindProjects(%s) failed: %v", by, err)
		}
		var got []string
		for _, p := range projects {
			got = append(got, p.Name)
		}
		if len(got) != len(want) || got[0] != want[0] || got[1] != want[1] {
			t.Errorf("FindProjects(%s) = %v, want %v", by, got, want)
		}
	}
}

M pkg/apps/prose/db.go

+1, -132

......@@ -2,12 +2,10 @@ package prose

import (
	"fmt"
	"math"
	"sync"
	"time"

	"github.com/hashicorp/golang-lru/v2/expirable"
	"github.com/jmoiron/sqlx"
	"github.com/picosh/pico/pkg/db"
	"github.com/picosh/pico/pkg/db/postgres"
)

......@@ -200,134 +198,5 @@ func (me *PsqlDB) FindUserPostsByTag(page *db.Pager, tag, userID, space string)
		return nil, err
	}

	var count int
	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
	}

	pager := &db.Paginate[*db.Post]{
		Data:  posts,
		Total: int(math.Ceil(float64(count) / float64(page.Num))),
	}
	return pager, nil
}

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(
		query,
		pager.Num,
		pager.Num*pager.Page,
		tag,
		space,
	)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rs.Close() }()

	return me.postPager(rs, pager.Num, space, tag)
}

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{}
		err := rs.Scan(
			&post.ID,
			&post.UserID,
			&post.Filename,
			&post.Slug,
			&post.Title,
			&post.Text,
			&post.Description,
			&post.PublishAt,
			&post.Username,
			&post.UpdatedAt,
			&post.MimeType,
		)
		if err != nil {
			return nil, err
		}

		posts = append(posts, post)
	}
	if rs.Err() != nil {
		return nil, rs.Err()
	}

	var count int
	var err error
	if tag == "" {
		err = me.Db.QueryRow(`SELECT count(id) FROM posts WHERE hidden = FALSE AND cur_space=$1`, space).Scan(&count)
	} else {
		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
	}

	pager := &db.Paginate[*db.Post]{
		Data:  posts,
		Total: int(math.Ceil(float64(count) / float64(pageNum))),
	}

	return pager, nil
}

func (me *PsqlDB) FindPopularTags(space string) ([]string, error) {
	tags := make([]string, 0)
	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
	}
	defer func() { _ = rs.Close() }()
	for rs.Next() {
		name := ""
		tally := 0
		err := rs.Scan(&name, &tally)
		if err != nil {
			return tags, err
		}

		tags = append(tags, name)
	}
	if rs.Err() != nil {
		return tags, rs.Err()
	}
	return tags, nil
	return &db.Paginate[*db.Post]{Data: posts}, nil
}

M pkg/apps/prose/db_test.go

+0, -36

......@@ -143,40 +143,4 @@ func TestFindUserPostsByTag(t *testing.T) {
	}
}

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.ReplaceTagsByPost([]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.ReplaceTagsByPost([]string{"popular"}, post1.ID)
	_ = testDB.ReplaceTagsByPost([]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 ============

M pkg/db/db.go

+22, -2

......@@ -164,8 +164,7 @@ type Post struct {
}

type Paginate[T any] struct {
	Data  []T
	Total int
	Data []T
}

type VisitInterval struct {

......@@ -218,6 +217,27 @@ type AnalyticsVisits struct {
	ContentType string `json:"content_type" db:"content_type"`
}

// IsMobileUserAgent checks common mobile user-agent patterns.
// TODO: replace with a proper UA parser library (e.g., mssola/useragent).
func IsMobileUserAgent(userAgent string) bool {
	ua := strings.ToLower(userAgent)
	mobileKeywords := []string{
		"mobile",
		"android",
		"iphone",
		"ipad",
		"ipod",
		"blackberry",
		"windows phone",
	}
	for _, kw := range mobileKeywords {
		if strings.Contains(ua, kw) {
			return true
		}
	}
	return false
}

type AccessLogData struct{}

func (p *AccessLogData) Scan(value any) error {

M pkg/db/postgres/analytics.go

+8, -10

......@@ -9,12 +9,9 @@ import (
	"github.com/picosh/pico/pkg/db"
)

// mobileUserAgentExpr is a SQL expression to detect mobile user agents.
const mobileUserAgentExpr = `(user_agent ILIKE '%mobile%' OR user_agent ILIKE '%android%' OR user_agent ILIKE '%iphone%' OR user_agent ILIKE '%ipad%' OR user_agent ILIKE '%ipod%' OR user_agent ILIKE '%blackberry%' OR user_agent ILIKE '%windows phone%')`

func (me *PsqlDB) InsertVisit(visit *db.AnalyticsVisits) error {
	_, err := me.Db.Exec(
		`INSERT INTO analytics_visits (user_id, project_id, post_id, namespace, host, path, ip_address, user_agent, referer, status, content_type) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11);`,
		`INSERT INTO analytics_visits (user_id, project_id, post_id, namespace, host, path, ip_address, user_agent, referer, status, content_type, is_mobile) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12);`,
		visit.UserID,
		newNullString(visit.ProjectID),
		newNullString(visit.PostID),

......@@ -26,6 +23,7 @@ func (me *PsqlDB) InsertVisit(visit *db.AnalyticsVisits) error {
		visit.Referer,
		visit.Status,
		visit.ContentType,
		db.IsMobileUserAgent(visit.UserAgent),
	)
	return err
}

......@@ -138,14 +136,14 @@ func (me *PsqlDB) visitUniqueFromRaw(opts *db.SummaryOpts) ([]*db.VisitInterval,
	uniqueVisitors := fmt.Sprintf(`
		SELECT
			date_trunc('%s', created_at)::timestamptz as interval_start,
			count(DISTINCT CASE WHEN %s THEN ip_address END) as mobile_visitors,
			count(DISTINCT CASE WHEN NOT %s THEN ip_address END) as desktop_visitors,
			count(DISTINCT CASE WHEN is_mobile THEN ip_address END) as mobile_visitors,
			count(DISTINCT CASE WHEN NOT is_mobile THEN ip_address END) as desktop_visitors,
			count(DISTINCT ip_address) as unique_visitors
		FROM analytics_visits
		WHERE created_at >= $1 AND created_at < $2 AND %s = $3 AND user_id = $4 AND status <> 404
		GROUP BY interval_start
		ORDER BY interval_start`,
		opts.Interval, mobileUserAgentExpr, mobileUserAgentExpr, where)
		opts.Interval, where)

	rows, err := me.Db.Queryx(uniqueVisitors, effectiveStart, currentMonthStart.AddDate(0, 1, 0), with, opts.UserID)
	if err != nil {

......@@ -320,7 +318,7 @@ func (me *PsqlDB) visitRefererFromSummary(opts *db.SummaryOpts) ([]*db.VisitUrl,
	originMonthStart := time.Date(opts.Origin.Year(), opts.Origin.Month(), 1, 0, 0, 0, 0, time.UTC)

	where := ""
	args := []interface{}{opts.UserID, originMonthStart, currentMonthStart}
	args := []interface{}{opts.UserID, originMonthStart, previousMonthStart}
	if opts.Host != "" {
		where = "AND host = $4"
		args = append(args, opts.Host)

......@@ -435,7 +433,7 @@ func (me *PsqlDB) visitUrlFromSummary(opts *db.SummaryOpts) ([]*db.VisitUrl, err
	originMonthStart := time.Date(opts.Origin.Year(), opts.Origin.Month(), 1, 0, 0, 0, 0, time.UTC)

	where := ""
	args := []interface{}{opts.UserID, originMonthStart, currentMonthStart}
	args := []interface{}{opts.UserID, originMonthStart, previousMonthStart}
	if opts.Host != "" {
		where = "AND host = $4"
		args = append(args, opts.Host)

......@@ -555,7 +553,7 @@ func (me *PsqlDB) visitUrlNotFoundFromSummary(opts *db.SummaryOpts, limit int) (
	originMonthStart := time.Date(opts.Origin.Year(), opts.Origin.Month(), 1, 0, 0, 0, 0, time.UTC)

	where := ""
	args := []interface{}{opts.UserID, originMonthStart, currentMonthStart}
	args := []interface{}{opts.UserID, originMonthStart, previousMonthStart}
	argIdx := 4
	if opts.Host != "" {
		where = "AND host = $" + fmt.Sprintf("%d", argIdx)

M pkg/db/postgres/posts.go

+1, -12

......@@ -3,7 +3,6 @@ package postgres
import (
	"database/sql"
	"fmt"
	"math"
	"strings"

	"github.com/picosh/pico/pkg/db"

......@@ -309,17 +308,7 @@ func (me *PsqlDB) FindPostsByUser(page *db.Pager, userID string, space string) (
		return nil, rs.Err()
	}

	var count int
	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
	}

	pager := &db.Paginate[*db.Post]{
		Data:  posts,
		Total: int(math.Ceil(float64(count) / float64(page.Num))),
	}
	return pager, nil
	return &db.Paginate[*db.Post]{Data: posts}, nil
}

func (me *PsqlDB) FindAllPostsByUser(userID string, space string) ([]*db.Post, error) {

M pkg/db/postgres/storage_test.go

+49, -0

......@@ -824,6 +824,55 @@ func TestVisitSummaryDeviceSplit(t *testing.T) {
	}
}

func TestVisitSummaryPreviousMonthCountedOnce(t *testing.T) {
	cleanupTestData(t)

	user, _ := testDB.RegisterUser("prevmonthowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI prevmonthowner", "comment", "")

	now := time.Now()
	currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
	previousMonthStart := currentMonthStart.AddDate(0, -1, 0)

	// Raw rows for the previous month are kept after it has been aggregated.
	_, err := testDB.Db.Exec(
		`INSERT INTO analytics_visits (user_id, host, path, ip_address, referer, status, created_at) VALUES ($1, 'prev.com', '/page', '192.168.1.20', 'ref.com', 200, $2)`,
		user.ID, previousMonthStart.Add(24*time.Hour),
	)
	if err != nil {
		t.Fatalf("insert raw visit: %v", err)
	}
	_, err = testDB.Db.Exec(
		`INSERT INTO analytics_monthly_top_urls (user_id, host, month, path, unique_visits, status_code, rank) VALUES ($1, 'prev.com', $2, '/page', 1, 200, 1)`,
		user.ID, previousMonthStart,
	)
	if err != nil {
		t.Fatalf("insert top url: %v", err)
	}
	_, err = testDB.Db.Exec(
		`INSERT INTO analytics_monthly_top_referers (user_id, host, month, referer, unique_visits, rank) VALUES ($1, 'prev.com', $2, 'ref.com', 1, 1)`,
		user.ID, previousMonthStart,
	)
	if err != nil {
		t.Fatalf("insert top referer: %v", err)
	}

	summary, err := testDB.VisitSummary(&db.SummaryOpts{
		Interval: "month",
		Origin:   currentMonthStart.AddDate(0, -3, 0),
		Host:     "prev.com",
		UserID:   user.ID,
	})
	if err != nil {
		t.Fatalf("VisitSummary failed: %v", err)
	}
	if len(summary.TopUrls) != 1 || summary.TopUrls[0].Count != 1 {
		t.Errorf("got top urls %+v, want /page counted once", summary.TopUrls)
	}
	if len(summary.TopReferers) != 1 || summary.TopReferers[0].Count != 1 {
		t.Errorf("got top referers %+v, want ref.com counted once", summary.TopReferers)
	}
}

func TestFindVisitSiteList(t *testing.T) {
	cleanupTestData(t)

M pkg/db/stub/stub.go

+0, -8

......@@ -144,14 +144,6 @@ func (me *StubDB) FindUserPostsByTag(page *db.Pager, tag, userID, space string)
	return &db.Paginate[*db.Post]{}, errNotImpl
}

func (me *StubDB) FindPostsByTag(pager *db.Pager, tag, space string) (*db.Paginate[*db.Post], error) {
	return &db.Paginate[*db.Post]{}, errNotImpl
}

func (me *StubDB) FindPopularTags(space string) ([]string, error) {
	return []string{}, errNotImpl
}

func (me *StubDB) FindFeature(userID string, feature string) (*db.FeatureFlag, error) {
	return nil, errNotImpl
}

A sql/migrations/20261025_add_access_logs_user_created_idx.sql

+1, -0

......@@ -0,0 +1 @@
CREATE INDEX CONCURRENTLY IF NOT EXISTS access_logs_user_created_idx ON access_logs (user_id, created_at);

A sql/migrations/20261025_add_is_mobile_to_analytics.sql

+15, -0

......@@ -0,0 +1,15 @@
ALTER TABLE analytics_visits ADD COLUMN IF NOT EXISTS is_mobile boolean;

-- Only the current and previous month are read from raw visits. Matches db.IsMobileUserAgent.
UPDATE analytics_visits
SET is_mobile = (
  COALESCE(user_agent, '') ILIKE '%mobile%'
  OR COALESCE(user_agent, '') ILIKE '%android%'
  OR COALESCE(user_agent, '') ILIKE '%iphone%'
  OR COALESCE(user_agent, '') ILIKE '%ipad%'
  OR COALESCE(user_agent, '') ILIKE '%ipod%'
  OR COALESCE(user_agent, '') ILIKE '%blackberry%'
  OR COALESCE(user_agent, '') ILIKE '%windows phone%'
)
WHERE is_mobile IS NULL
  AND created_at >= date_trunc('month', now() AT TIME ZONE 'UTC') - interval '1 month';

A sql/migrations/20261025_add_lookup_indexes.sql

+6, -0

......@@ -0,0 +1,6 @@
CREATE INDEX IF NOT EXISTS feed_items_post_idx ON feed_items (post_id);
CREATE INDEX IF NOT EXISTS tuns_event_logs_user_created_idx ON tuns_event_logs (user_id, created_at);
CREATE INDEX IF NOT EXISTS feature_flags_user_name_idx ON feature_flags (user_id, name);
CREATE INDEX IF NOT EXISTS public_keys_public_key_idx ON public_keys (public_key);
CREATE INDEX IF NOT EXISTS post_aliases_slug_idx ON post_aliases (slug);
CREATE INDEX IF NOT EXISTS post_aliases_post_idx ON post_aliases (post_id);

A sql/migrations/20261025_drop_unused_analytics_indexes.sql

+9, -0

......@@ -0,0 +1,9 @@
DROP INDEX IF EXISTS
  analytics_visits_status_idx,
  analytics_visits_user_id_idx,
  analytics_visits_ip_addr_idx,
  analytics_visits_host_idx,
  analytics_visits_path_idx,
  analytics_visits_user_id_host_ip_address_idx,
  analytics_visits_user_created_idx,
  analytics_visits_user_path_created_status_idx;

...	...		@@ -387,27 +387,6 @@ func upsertUserSite(dbpool *postgres.PsqlDB, userID, host string, monthUnique in
387	387		return err
388	388		}
389	389
390		-	// isMobile checks common mobile user-agent patterns.
391		-	// TODO: replace with a proper UA parser library (e.g., mssola/useragent).
392		-	func isMobile(userAgent string) bool {
393		-	ua := strings.ToLower(userAgent)
394		-	mobileKeywords := []string{
395		-	"mobile",
396		-	"android",
397		-	"iphone",
398		-	"ipad",
399		-	"ipod",
400		-	"blackberry",
401		-	"windows phone",
402		-	}
403		-	for _, kw := range mobileKeywords {
404		-	if strings.Contains(ua, kw) {
405		-	return true
406		-	}
407		-	}
408		-	return false
409		-	}
410		-
411	390		// deleteAggregatedVisits deletes raw visit data from analytics_visits for the given month.
412	391		// Data is preserved in summary tables, so this is safe regardless of feature flags.
413	392		// Raw data for the current and previous months is never deleted since visitUniqueFromRaw

...	...		@@ -143,40 +143,4 @@ func TestFindUserPostsByTag(t *testing.T) {
143	143		}
144	144		}
145	145
146		-	func TestFindPostsByTag(t *testing.T) {
147		-	cleanupTestData(t)
148		-
149		-	user, _ := testDB.RegisterUser("tagsearchowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI tagsearchowner", "comment", "")
150		-	now := time.Now()
151		-	post := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "tagsearch.md", Slug: "tagsearch", Title: "Tag Search", Space: "prose", PublishAt: &now})
152		-	_ = testDB.ReplaceTagsByPost([]string{"searchtag"}, post.ID)
153		-
154		-	pager := &db.Pager{Num: 10, Page: 0}
155		-	result, err := testDB.FindPostsByTag(pager, "searchtag", "prose")
156		-	if err != nil {
157		-	t.Fatalf("FindPostsByTag failed: %v", err)
158		-	}
159		-	if len(result.Data) != 1 {
160		-	t.Errorf("expected 1 post with tag, got %d", len(result.Data))
161		-	}
162		-	}
163		-
164		-	func TestFindPopularTags(t *testing.T) {
165		-	cleanupTestData(t)
166		-
167		-	user, _ := testDB.RegisterUser("populartagowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI populartagowner", "comment", "")
168		-	post1 := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "pop1.md", Slug: "pop1", Title: "Pop 1", Space: "prose"})
169		-	post2 := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "pop2.md", Slug: "pop2", Title: "Pop 2", Space: "prose"})
170		-	_ = testDB.ReplaceTagsByPost([]string{"popular"}, post1.ID)
171		-	_ = testDB.ReplaceTagsByPost([]string{"popular"}, post2.ID)
172		-
173		-	tags, err := testDB.FindPopularTags("prose")
174		-	if err != nil {
175		-	t.Fatalf("FindPopularTags failed: %v", err)
176		-	}
177		-	if len(tags) < 1 {
178		-	t.Errorf("expected at least 1 popular tag, got %d", len(tags))
179		-	}
180		-	}
181		-
182	146		// ============ Aliases Tests ============

...	...		@@ -218,6 +217,27 @@ type AnalyticsVisits struct {
218	217		ContentType string `json:"content_type" db:"content_type"`
219	218		}
220	219
	220	+	// IsMobileUserAgent checks common mobile user-agent patterns.
	221	+	// TODO: replace with a proper UA parser library (e.g., mssola/useragent).
	222	+	func IsMobileUserAgent(userAgent string) bool {
	223	+	ua := strings.ToLower(userAgent)
	224	+	mobileKeywords := []string{
	225	+	"mobile",
	226	+	"android",
	227	+	"iphone",
	228	+	"ipad",
	229	+	"ipod",
	230	+	"blackberry",
	231	+	"windows phone",
	232	+	}
	233	+	for _, kw := range mobileKeywords {
	234	+	if strings.Contains(ua, kw) {
	235	+	return true
	236	+	}
	237	+	}
	238	+	return false
	239	+	}
	240	+
221	241		type AccessLogData struct{}
222	242
223	243		func (p *AccessLogData) Scan(value any) error {

...	...		@@ -824,6 +824,55 @@ func TestVisitSummaryDeviceSplit(t *testing.T) {
824	824		}
825	825		}
826	826
	827	+	func TestVisitSummaryPreviousMonthCountedOnce(t *testing.T) {
	828	+	cleanupTestData(t)
	829	+
	830	+	user, _ := testDB.RegisterUser("prevmonthowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI prevmonthowner", "comment", "")
	831	+
	832	+	now := time.Now()
	833	+	currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
	834	+	previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
	835	+
	836	+	// Raw rows for the previous month are kept after it has been aggregated.
	837	+	_, err := testDB.Db.Exec(
	838	+	`INSERT INTO analytics_visits (user_id, host, path, ip_address, referer, status, created_at) VALUES ($1, 'prev.com', '/page', '192.168.1.20', 'ref.com', 200, $2)`,
	839	+	user.ID, previousMonthStart.Add(24*time.Hour),
	840	+	)
	841	+	if err != nil {
	842	+	t.Fatalf("insert raw visit: %v", err)
	843	+	}
	844	+	_, err = testDB.Db.Exec(
	845	+	`INSERT INTO analytics_monthly_top_urls (user_id, host, month, path, unique_visits, status_code, rank) VALUES ($1, 'prev.com', $2, '/page', 1, 200, 1)`,
	846	+	user.ID, previousMonthStart,
	847	+	)
	848	+	if err != nil {
	849	+	t.Fatalf("insert top url: %v", err)
	850	+	}
	851	+	_, err = testDB.Db.Exec(
	852	+	`INSERT INTO analytics_monthly_top_referers (user_id, host, month, referer, unique_visits, rank) VALUES ($1, 'prev.com', $2, 'ref.com', 1, 1)`,
	853	+	user.ID, previousMonthStart,
	854	+	)
	855	+	if err != nil {
	856	+	t.Fatalf("insert top referer: %v", err)
	857	+	}
	858	+
	859	+	summary, err := testDB.VisitSummary(&db.SummaryOpts{
	860	+	Interval: "month",
	861	+	Origin: currentMonthStart.AddDate(0, -3, 0),
	862	+	Host: "prev.com",
	863	+	UserID: user.ID,
	864	+	})
	865	+	if err != nil {
	866	+	t.Fatalf("VisitSummary failed: %v", err)
	867	+	}
	868	+	if len(summary.TopUrls) != 1 \|\| summary.TopUrls[0].Count != 1 {
	869	+	t.Errorf("got top urls %+v, want /page counted once", summary.TopUrls)
	870	+	}
	871	+	if len(summary.TopReferers) != 1 \|\| summary.TopReferers[0].Count != 1 {
	872	+	t.Errorf("got top referers %+v, want ref.com counted once", summary.TopReferers)
	873	+	}
	874	+	}
	875	+
827	876		func TestFindVisitSiteList(t *testing.T) {
828	877		cleanupTestData(t)
829	878

...	...		@@ -144,14 +144,6 @@ func (me StubDB) FindUserPostsByTag(page db.Pager, tag, userID, space string)
144	144		return &db.Paginate[*db.Post]{}, errNotImpl
145	145		}
146	146
147		-	func (me StubDB) FindPostsByTag(pager db.Pager, tag, space string) (db.Paginate[db.Post], error) {
148		-	return &db.Paginate[*db.Post]{}, errNotImpl
149		-	}
150		-
151		-	func (me *StubDB) FindPopularTags(space string) ([]string, error) {
152		-	return []string{}, errNotImpl
153		-	}
154		-
155	147		func (me StubDB) FindFeature(userID string, feature string) (db.FeatureFlag, error) {
156	148		return nil, errNotImpl
157	149		}

...	...		@@ -205,10 +205,14 @@ migrate:
205	205		$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260716_block_signups.sql
206	206		$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260925_add_invites.sql
207	207		$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20261010_add_pastes_invite_feature.sql
	208	+	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20261025_add_access_logs_user_created_idx.sql
	209	+	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20261025_add_is_mobile_to_analytics.sql
	210	+	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20261025_add_lookup_indexes.sql
	211	+	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20261025_drop_unused_analytics_indexes.sql
208	212		.PHONY: migrate
209	213
210	214		latest:
211		-	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20261010_add_pastes_invite_feature.sql
	215	+	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20261025_drop_unused_analytics_indexes.sql
212	216		.PHONY: latest
213	217
214	218		psql:

...	...		@@ -4,9 +4,9 @@ import (
4	4		"context"
5	5		"fmt"
6	6		"log/slog"
7		-	"strings"
8	7		"time"
9	8
	9	+	"github.com/picosh/pico/pkg/db"
10	10		"github.com/picosh/pico/pkg/db/postgres"
11	11		)
12	12

...	...		@@ -224,7 +224,7 @@ func aggregateDays(visits []visitRow) map[string]*dayStats {
224	224		}
225	225
226	226		ds.AllIPs[v.IPAddress] = true
227		-	if isMobile(v.UserAgent) {
	227	+	if db.IsMobileUserAgent(v.UserAgent) {
228	228		ds.MobileIPs[v.IPAddress] = true
229	229		} else {
230	230		ds.DesktopIPs[v.IPAddress] = true

...	...		@@ -252,14 +252,18 @@ func (me PgsPsqlDB) FindProjectsByUser(userID string) ([]db.Project, error) {
252	252		}
253	253
254	254		func (me PgsPsqlDB) FindProjects(by string) ([]db.Project, error) {
	255	+	// A bind parameter in ORDER BY sorts by a constant, so map to a column.
	256	+	orderBy := "p.created_at"
	257	+	if by == "updated_at" {
	258	+	orderBy = "p.updated_at"
	259	+	}
255	260		projects := []*db.Project{}
256	261		err := me.Db.Select(
257	262		&projects,
258		-	`SELECT p.id, p.user_id, u.name as username, p.name, p.project_dir, p.acl, p.blocked, p.created_at, p.updated_at
	263	+	fmt.Sprintf(`SELECT p.id, p.user_id, u.name as username, p.name, p.project_dir, p.acl, p.blocked, p.created_at, p.updated_at
259	264		FROM projects AS p
260	265		LEFT JOIN app_users AS u ON u.id = p.user_id
261		-	ORDER BY $1 DESC`,
262		-	by,
	266	+	ORDER BY %s DESC`, orderBy),
263	267		)
264	268		return projects, err
265	269		}

...	...		@@ -0,0 +1,63 @@
	1	+	package pgsdb
	2	+
	3	+	import (
	4	+	"log/slog"
	5	+	"testing"
	6	+	"time"
	7	+
	8	+	"github.com/jmoiron/sqlx"
	9	+	"github.com/picosh/pico/pkg/db/postgres/pgtest"
	10	+	)
	11	+
	12	+	func TestMain(m *testing.M) {
	13	+	pgtest.Main(m)
	14	+	}
	15	+
	16	+	func TestFindProjectsOrder(t *testing.T) {
	17	+	conn := sqlx.MustConnect("postgres", pgtest.URL(t))
	18	+	t.Cleanup(func() { _ = conn.Close() })
	19	+	pgtest.Clean(t, conn)
	20	+	testDB := NewDBWithConn(conn, slog.New(slog.DiscardHandler))
	21	+
	22	+	var userID string
	23	+	if err := conn.Get(&userID, `INSERT INTO app_users (name) VALUES ('projectsowner') RETURNING id`); err != nil {
	24	+	t.Fatalf("insert user: %v", err)
	25	+	}
	26	+
	27	+	// "older" was created first but updated last, so the two orderings differ.
	28	+	now := time.Now()
	29	+	for _, p := range []struct {
	30	+	name string
	31	+	createdAt time.Time
	32	+	updatedAt time.Time
	33	+	}{
	34	+	{"older", now.Add(-2 * time.Hour), now},
	35	+	{"newer", now.Add(-1 * time.Hour), now.Add(-1 * time.Hour)},
	36	+	} {
	37	+	id, err := testDB.InsertProject(userID, p.name, p.name)
	38	+	if err != nil {
	39	+	t.Fatalf("insert project %s: %v", p.name, err)
	40	+	}
	41	+	_, err = conn.Exec(`UPDATE projects SET created_at = $1, updated_at = $2 WHERE id = $3`, p.createdAt, p.updatedAt, id)
	42	+	if err != nil {
	43	+	t.Fatalf("set project %s times: %v", p.name, err)
	44	+	}
	45	+	}
	46	+
	47	+	for by, want := range map[string][]string{
	48	+	"created_at": {"newer", "older"},
	49	+	"updated_at": {"older", "newer"},
	50	+	} {
	51	+	projects, err := testDB.FindProjects(by)
	52	+	if err != nil {
	53	+	t.Fatalf("FindProjects(%s) failed: %v", by, err)
	54	+	}
	55	+	var got []string
	56	+	for _, p := range projects {
	57	+	got = append(got, p.Name)
	58	+	}
	59	+	if len(got) != len(want) \|\| got[0] != want[0] \|\| got[1] != want[1] {
	60	+	t.Errorf("FindProjects(%s) = %v, want %v", by, got, want)
	61	+	}
	62	+	}
	63	+	}

...	...		@@ -2,12 +2,10 @@ package prose
2	2
3	3		import (
4	4		"fmt"
5		-	"math"
6	5		"sync"
7	6		"time"
8	7
9	8		"github.com/hashicorp/golang-lru/v2/expirable"
10		-	"github.com/jmoiron/sqlx"
11	9		"github.com/picosh/pico/pkg/db"
12	10		"github.com/picosh/pico/pkg/db/postgres"
13	11		)

...	...		@@ -200,134 +198,5 @@ func (me PsqlDB) FindUserPostsByTag(page db.Pager, tag, userID, space string)
200	198		return nil, err
201	199		}
202	200
203		-	var count int
204		-	err = me.Db.QueryRow(`SELECT count(id) FROM posts WHERE hidden = FALSE AND cur_space=$1`, space).Scan(&count)
205		-	if err != nil {
206		-	return nil, err
207		-	}
208		-
209		-	pager := &db.Paginate[*db.Post]{
210		-	Data: posts,
211		-	Total: int(math.Ceil(float64(count) / float64(page.Num))),
212		-	}
213		-	return pager, nil
214		-	}
215		-
216		-	func (me PsqlDB) FindPostsByTag(pager db.Pager, tag, space string) (db.Paginate[db.Post], error) {
217		-	query := `
218		-	SELECT
219		-	posts.id,
220		-	user_id,
221		-	filename,
222		-	slug,
223		-	title,
224		-	text,
225		-	description,
226		-	publish_at,
227		-	app_users.name as username,
228		-	posts.updated_at,
229		-	posts.mime_type
230		-	FROM posts
231		-	LEFT JOIN app_users ON app_users.id = posts.user_id
232		-	LEFT JOIN post_tags ON post_tags.post_id = posts.id
233		-	WHERE
234		-	post_tags.name = $3 AND
235		-	publish_at::date <= CURRENT_DATE AND
236		-	cur_space = $4
237		-	ORDER BY publish_at DESC
238		-	LIMIT $1 OFFSET $2`
239		-	rs, err := me.Db.Queryx(
240		-	query,
241		-	pager.Num,
242		-	pager.Num*pager.Page,
243		-	tag,
244		-	space,
245		-	)
246		-	if err != nil {
247		-	return nil, err
248		-	}
249		-	defer func() { _ = rs.Close() }()
250		-
251		-	return me.postPager(rs, pager.Num, space, tag)
252		-	}
253		-
254		-	func (me PsqlDB) postPager(rs sqlx.Rows, pageNum int, space string, tag string) (db.Paginate[db.Post], error) {
255		-	var posts []*db.Post
256		-	for rs.Next() {
257		-	post := &db.Post{}
258		-	err := rs.Scan(
259		-	&post.ID,
260		-	&post.UserID,
261		-	&post.Filename,
262		-	&post.Slug,
263		-	&post.Title,
264		-	&post.Text,
265		-	&post.Description,
266		-	&post.PublishAt,
267		-	&post.Username,
268		-	&post.UpdatedAt,
269		-	&post.MimeType,
270		-	)
271		-	if err != nil {
272		-	return nil, err
273		-	}
274		-
275		-	posts = append(posts, post)
276		-	}
277		-	if rs.Err() != nil {
278		-	return nil, rs.Err()
279		-	}
280		-
281		-	var count int
282		-	var err error
283		-	if tag == "" {
284		-	err = me.Db.QueryRow(`SELECT count(id) FROM posts WHERE hidden = FALSE AND cur_space=$1`, space).Scan(&count)
285		-	} else {
286		-	err = me.Db.QueryRow(`
287		-	SELECT count(posts.id)
288		-	FROM posts
289		-	LEFT JOIN post_tags ON post_tags.post_id = posts.id
290		-	WHERE hidden = FALSE AND cur_space=$1 and post_tags.name = $2`, space, tag).Scan(&count)
291		-	}
292		-	if err != nil {
293		-	return nil, err
294		-	}
295		-
296		-	pager := &db.Paginate[*db.Post]{
297		-	Data: posts,
298		-	Total: int(math.Ceil(float64(count) / float64(pageNum))),
299		-	}
300		-
301		-	return pager, nil
302		-	}
303		-
304		-	func (me *PsqlDB) FindPopularTags(space string) ([]string, error) {
305		-	tags := make([]string, 0)
306		-	query := `
307		-	SELECT name, count(post_id) as "tally"
308		-	FROM post_tags
309		-	LEFT JOIN posts ON posts.id = post_id
310		-	WHERE posts.cur_space = $1
311		-	GROUP BY name
312		-	ORDER BY tally DESC
313		-	LIMIT 5`
314		-	rs, err := me.Db.Queryx(query, space)
315		-	if err != nil {
316		-	return tags, err
317		-	}
318		-	defer func() { _ = rs.Close() }()
319		-	for rs.Next() {
320		-	name := ""
321		-	tally := 0
322		-	err := rs.Scan(&name, &tally)
323		-	if err != nil {
324		-	return tags, err
325		-	}
326		-
327		-	tags = append(tags, name)
328		-	}
329		-	if rs.Err() != nil {
330		-	return tags, rs.Err()
331		-	}
332		-	return tags, nil
	201	+	return &db.Paginate[*db.Post]{Data: posts}, nil
333	202		}

...	...		@@ -164,8 +164,7 @@ type Post struct {
164	164		}
165	165
166	166		type Paginate[T any] struct {
167		-	Data []T
168		-	Total int
	167	+	Data []T
169	168		}
170	169
171	170		type VisitInterval struct {

...	...		@@ -9,12 +9,9 @@ import (
9	9		"github.com/picosh/pico/pkg/db"
10	10		)
11	11
12		-	// mobileUserAgentExpr is a SQL expression to detect mobile user agents.
13		-	const mobileUserAgentExpr = `(user_agent ILIKE '%mobile%' OR user_agent ILIKE '%android%' OR user_agent ILIKE '%iphone%' OR user_agent ILIKE '%ipad%' OR user_agent ILIKE '%ipod%' OR user_agent ILIKE '%blackberry%' OR user_agent ILIKE '%windows phone%')`
14		-
15	12		func (me PsqlDB) InsertVisit(visit db.AnalyticsVisits) error {
16	13		_, err := me.Db.Exec(
17		-	`INSERT INTO analytics_visits (user_id, project_id, post_id, namespace, host, path, ip_address, user_agent, referer, status, content_type) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11);`,
	14	+	`INSERT INTO analytics_visits (user_id, project_id, post_id, namespace, host, path, ip_address, user_agent, referer, status, content_type, is_mobile) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12);`,
18	15		visit.UserID,
19	16		newNullString(visit.ProjectID),
20	17		newNullString(visit.PostID),

...	...		@@ -26,6 +23,7 @@ func (me PsqlDB) InsertVisit(visit db.AnalyticsVisits) error {
26	23		visit.Referer,
27	24		visit.Status,
28	25		visit.ContentType,
	26	+	db.IsMobileUserAgent(visit.UserAgent),
29	27		)
30	28		return err
31	29		}

...	...		@@ -138,14 +136,14 @@ func (me PsqlDB) visitUniqueFromRaw(opts db.SummaryOpts) ([]*db.VisitInterval,
138	136		uniqueVisitors := fmt.Sprintf(`
139	137		SELECT
140	138		date_trunc('%s', created_at)::timestamptz as interval_start,
141		-	count(DISTINCT CASE WHEN %s THEN ip_address END) as mobile_visitors,
142		-	count(DISTINCT CASE WHEN NOT %s THEN ip_address END) as desktop_visitors,
	139	+	count(DISTINCT CASE WHEN is_mobile THEN ip_address END) as mobile_visitors,
	140	+	count(DISTINCT CASE WHEN NOT is_mobile THEN ip_address END) as desktop_visitors,
143	141		count(DISTINCT ip_address) as unique_visitors
144	142		FROM analytics_visits
145	143		WHERE created_at >= $1 AND created_at < $2 AND %s = $3 AND user_id = $4 AND status <> 404
146	144		GROUP BY interval_start
147	145		ORDER BY interval_start`,
148		-	opts.Interval, mobileUserAgentExpr, mobileUserAgentExpr, where)
	146	+	opts.Interval, where)
149	147
150	148		rows, err := me.Db.Queryx(uniqueVisitors, effectiveStart, currentMonthStart.AddDate(0, 1, 0), with, opts.UserID)
151	149		if err != nil {

...	...		@@ -320,7 +318,7 @@ func (me PsqlDB) visitRefererFromSummary(opts db.SummaryOpts) ([]*db.VisitUrl,
320	318		originMonthStart := time.Date(opts.Origin.Year(), opts.Origin.Month(), 1, 0, 0, 0, 0, time.UTC)
321	319
322	320		where := ""
323		-	args := []interface{}{opts.UserID, originMonthStart, currentMonthStart}
	321	+	args := []interface{}{opts.UserID, originMonthStart, previousMonthStart}
324	322		if opts.Host != "" {
325	323		where = "AND host = $4"
326	324		args = append(args, opts.Host)

...	...		@@ -435,7 +433,7 @@ func (me PsqlDB) visitUrlFromSummary(opts db.SummaryOpts) ([]*db.VisitUrl, err
435	433		originMonthStart := time.Date(opts.Origin.Year(), opts.Origin.Month(), 1, 0, 0, 0, 0, time.UTC)
436	434
437	435		where := ""
438		-	args := []interface{}{opts.UserID, originMonthStart, currentMonthStart}
	436	+	args := []interface{}{opts.UserID, originMonthStart, previousMonthStart}
439	437		if opts.Host != "" {
440	438		where = "AND host = $4"
441	439		args = append(args, opts.Host)

...	...		@@ -555,7 +553,7 @@ func (me PsqlDB) visitUrlNotFoundFromSummary(opts db.SummaryOpts, limit int) (
555	553		originMonthStart := time.Date(opts.Origin.Year(), opts.Origin.Month(), 1, 0, 0, 0, 0, time.UTC)
556	554
557	555		where := ""
558		-	args := []interface{}{opts.UserID, originMonthStart, currentMonthStart}
	556	+	args := []interface{}{opts.UserID, originMonthStart, previousMonthStart}
559	557		argIdx := 4
560	558		if opts.Host != "" {
561	559		where = "AND host = $" + fmt.Sprintf("%d", argIdx)

...	...		@@ -3,7 +3,6 @@ package postgres
3	3		import (
4	4		"database/sql"
5	5		"fmt"
6		-	"math"
7	6		"strings"
8	7
9	8		"github.com/picosh/pico/pkg/db"

...	...		@@ -309,17 +308,7 @@ func (me PsqlDB) FindPostsByUser(page db.Pager, userID string, space string) (
309	308		return nil, rs.Err()
310	309		}
311	310
312		-	var count int
313		-	err = me.Db.QueryRow(`SELECT count(id) FROM posts WHERE hidden = FALSE AND cur_space=$1`, space).Scan(&count)
314		-	if err != nil {
315		-	return nil, err
316		-	}
317		-
318		-	pager := &db.Paginate[*db.Post]{
319		-	Data: posts,
320		-	Total: int(math.Ceil(float64(count) / float64(page.Num))),
321		-	}
322		-	return pager, nil
	311	+	return &db.Paginate[*db.Post]{Data: posts}, nil
323	312		}
324	313
325	314		func (me PsqlDB) FindAllPostsByUser(userID string, space string) ([]db.Post, error) {

...	...		@@ -0,0 +1 @@
	1	+	CREATE INDEX CONCURRENTLY IF NOT EXISTS access_logs_user_created_idx ON access_logs (user_id, created_at);

...	...		@@ -0,0 +1,15 @@
	1	+	ALTER TABLE analytics_visits ADD COLUMN IF NOT EXISTS is_mobile boolean;
	2	+
	3	+	-- Only the current and previous month are read from raw visits. Matches db.IsMobileUserAgent.
	4	+	UPDATE analytics_visits
	5	+	SET is_mobile = (
	6	+	COALESCE(user_agent, '') ILIKE '%mobile%'
	7	+	OR COALESCE(user_agent, '') ILIKE '%android%'
	8	+	OR COALESCE(user_agent, '') ILIKE '%iphone%'
	9	+	OR COALESCE(user_agent, '') ILIKE '%ipad%'
	10	+	OR COALESCE(user_agent, '') ILIKE '%ipod%'
	11	+	OR COALESCE(user_agent, '') ILIKE '%blackberry%'
	12	+	OR COALESCE(user_agent, '') ILIKE '%windows phone%'
	13	+	)
	14	+	WHERE is_mobile IS NULL
	15	+	AND created_at >= date_trunc('month', now() AT TIME ZONE 'UTC') - interval '1 month';

...	...		@@ -0,0 +1,6 @@
	1	+	CREATE INDEX IF NOT EXISTS feed_items_post_idx ON feed_items (post_id);
	2	+	CREATE INDEX IF NOT EXISTS tuns_event_logs_user_created_idx ON tuns_event_logs (user_id, created_at);
	3	+	CREATE INDEX IF NOT EXISTS feature_flags_user_name_idx ON feature_flags (user_id, name);
	4	+	CREATE INDEX IF NOT EXISTS public_keys_public_key_idx ON public_keys (public_key);
	5	+	CREATE INDEX IF NOT EXISTS post_aliases_slug_idx ON post_aliases (slug);
	6	+	CREATE INDEX IF NOT EXISTS post_aliases_post_idx ON post_aliases (post_id);

...	...		@@ -0,0 +1,9 @@
	1	+	DROP INDEX IF EXISTS
	2	+	analytics_visits_status_idx,
	3	+	analytics_visits_user_id_idx,
	4	+	analytics_visits_ip_addr_idx,
	5	+	analytics_visits_host_idx,
	6	+	analytics_visits_path_idx,
	7	+	analytics_visits_user_id_host_ip_address_idx,
	8	+	analytics_visits_user_created_idx,
	9	+	analytics_visits_user_path_created_status_idx;

Commit de92f37

Commit `de92f37`