Commit 23438de

Eric Bower  ·  2026-05-03 13:36:05 -0400 EDT
parent 4cd5fc0 
refactor(visits): aggregate visit tables

Previously we had an analytics_visits table that held all the raw visit data.
This mean our analytics UI was constantly executing queries across 18 mil records
which was extremely slow.  This change aggregates those analytics every month
and then deletes all the raw data.

We keep 2 months worth of raw data and then everything else gets pushed into
the aggregate tables.
9 files changed,  +1825, -113
M Makefile
A cmd/scripts/analytics-aggregate/main.go
A cmd/scripts/compare-analytics/main.go
A docs/analytics-retention-proposal.md
A pkg/apps/auth/analytics_aggregate.go
M pkg/apps/auth/api.go
M pkg/db/db.go
M pkg/db/postgres/storage.go
A sql/migrations/20260503_add_analytics_summary_tables.sql
M Makefile
+4, -2
......@@ -150,10 +150,11 @@ migrate:
150150 $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20251226_add_pipe_monitoring.sql
151151 $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260116_add_analytics_filter_indexes.sql
152152 $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260305_add_forms_table.sql
153+ $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260503_add_analytics_summary_tables.sql
153154 .PHONY: migrate
154155
155156 latest:
156- $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260305_add_forms_table.sql
157+ $(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260503_add_analytics_summary_tables.sql
157158 .PHONY: latest
158159
159160 psql:
......@@ -164,8 +165,9 @@ dump:
164165 $(DOCKER_CMD) exec $(DB_CONTAINER) pg_dump -U $(PGUSER) $(PGDATABASE) > ./backup.sql
165166 .PHONY: dump
166167
168+BACKUP_FILE?=./backup.sql
167169 restore:
168- $(DOCKER_CMD) cp ./backup.sql $(DB_CONTAINER):/backup.sql
170+ $(DOCKER_CMD) cp $(BACKUP_FILE) $(DB_CONTAINER):/backup.sql
169171 $(DOCKER_CMD) exec -it $(DB_CONTAINER) /bin/bash
170172 # psql postgres -U postgres -d pico < /backup.sql
171173 .PHONY: restore
A cmd/scripts/analytics-aggregate/main.go
+101, -0
......@@ -0,0 +1,101 @@
1+package main
2+
3+import (
4+ "flag"
5+ "fmt"
6+ "log/slog"
7+ "os"
8+ "time"
9+
10+ "github.com/picosh/pico/pkg/apps/auth"
11+ "github.com/picosh/pico/pkg/db/postgres"
12+)
13+
14+func main() {
15+ monthPtr := flag.String("month", "", "target month in YYYY-MM format (default: previous month)")
16+ backfill := flag.Bool("backfill", false, "aggregate all historical months up to the month before last")
17+ dryRun := flag.Bool("dry-run", false, "print months that would be processed without running aggregation")
18+ flag.Parse()
19+
20+ logger := slog.Default()
21+ dbURL := os.Getenv("DATABASE_URL")
22+ if dbURL == "" {
23+ fmt.Fprintln(os.Stderr, "DATABASE_URL must be set")
24+ os.Exit(1)
25+ }
26+ dbpool := postgres.NewDB(dbURL, logger)
27+ defer func() { _ = dbpool.Close() }()
28+
29+ if *backfill {
30+ runBackfill(dbpool, logger, *dryRun)
31+ return
32+ }
33+
34+ targetMonth := parseMonth(*monthPtr, logger)
35+ if err := auth.RunAnalyticsAggregation(dbpool, logger, targetMonth); err != nil {
36+ logger.Error("aggregation failed", "err", err)
37+ os.Exit(1)
38+ }
39+}
40+
41+func runBackfill(dbpool *postgres.PsqlDB, logger *slog.Logger, dryRun bool) {
42+ months, err := fetchHistoricalMonths(dbpool)
43+ if err != nil {
44+ logger.Error("failed to fetch historical months", "err", err)
45+ os.Exit(1)
46+ }
47+ if dryRun {
48+ fmt.Println("Months to backfill:")
49+ for _, m := range months {
50+ fmt.Println(" ", m.Format("2006-01"))
51+ }
52+ return
53+ }
54+ for _, m := range months {
55+ if err := auth.RunAnalyticsAggregation(dbpool, logger, m); err != nil {
56+ logger.Error("aggregation failed for month", "month", m.Format("2006-01"), "err", err)
57+ }
58+ }
59+ logger.Info("backfill complete", "months", len(months))
60+}
61+
62+// fetchHistoricalMonths returns all distinct months that have data in analytics_visits,
63+// excluding the current month and the previous month (handled by the auth service cron).
64+func fetchHistoricalMonths(dbpool *postgres.PsqlDB) ([]time.Time, error) {
65+ cutoff := time.Now().AddDate(0, -1, 0)
66+ cutoffMonth := time.Date(cutoff.Year(), cutoff.Month(), 1, 0, 0, 0, 0, time.UTC)
67+
68+ rows, err := dbpool.Db.Queryx(`
69+ SELECT DISTINCT date_trunc('month', created_at)::date AS month_start
70+ FROM analytics_visits
71+ WHERE created_at < $1
72+ ORDER BY month_start ASC
73+ `, cutoffMonth)
74+ if err != nil {
75+ return nil, err
76+ }
77+ defer func() { _ = rows.Close() }()
78+
79+ var months []time.Time
80+ for rows.Next() {
81+ var monthDate time.Time
82+ if err := rows.Scan(&monthDate); err != nil {
83+ return nil, err
84+ }
85+ months = append(months, monthDate)
86+ }
87+ return months, rows.Err()
88+}
89+
90+func parseMonth(arg string, logger *slog.Logger) time.Time {
91+ now := time.Now()
92+ if arg != "" {
93+ t, err := time.Parse("2006-01", arg)
94+ if err != nil {
95+ logger.Error("invalid month format, use YYYY-MM", "err", err, "input", arg)
96+ os.Exit(1)
97+ }
98+ return t
99+ }
100+ return now.AddDate(0, -1, 0)
101+}
A cmd/scripts/compare-analytics/main.go
+306, -0
......@@ -0,0 +1,306 @@
1+package main
2+
3+import (
4+ "flag"
5+ "fmt"
6+ "log/slog"
7+ "os"
8+ "time"
9+
10+ "github.com/picosh/pico/pkg/db"
11+ "github.com/picosh/pico/pkg/db/postgres"
12+)
13+
14+func main() {
15+ host := flag.String("host", "", "host to compare (required)")
16+ userID := flag.String("user-id", "", "user ID to compare (required)")
17+ interval := flag.String("interval", "day", "interval: day, week, month")
18+ origin := flag.String("origin", "", "origin date in YYYY-MM-DD format (default: start of year)")
19+ flag.Parse()
20+
21+ if *host == "" || *userID == "" {
22+ fmt.Fprintln(os.Stderr, "usage: compare-analytics -host example.com -user-id <uuid> [-interval day|week|month] [-origin 2025-01-01]")
23+ os.Exit(1)
24+ }
25+
26+ logger := slog.Default()
27+ dbURL := os.Getenv("DATABASE_URL")
28+ if dbURL == "" {
29+ fmt.Fprintln(os.Stderr, "DATABASE_URL must be set")
30+ os.Exit(1)
31+ }
32+
33+ dbpool := postgres.NewDB(dbURL, logger)
34+ defer func() { _ = dbpool.Close() }()
35+
36+ originTime := time.Date(time.Now().AddDate(-1, 0, 0).Year(), time.January, 1, 0, 0, 0, 0, time.UTC)
37+ if *origin != "" {
38+ var err error
39+ originTime, err = time.Parse("2006-01-02", *origin)
40+ if err != nil {
41+ logger.Error("invalid origin format, use YYYY-MM-DD", "err", err)
42+ os.Exit(1)
43+ }
44+ }
45+
46+ opts := &db.SummaryOpts{
47+ Host: *host,
48+ UserID: *userID,
49+ Interval: *interval,
50+ Origin: originTime,
51+ }
52+
53+ // New path: reads from summary tables + current month raw
54+ newSummary, err := dbpool.VisitSummary(opts)
55+ if err != nil {
56+ logger.Error("VisitSummary failed", "err", err)
57+ os.Exit(1)
58+ }
59+
60+ // Old path: direct queries against analytics_visits
61+ oldSummary, err := queryRawVisits(dbpool, opts)
62+ if err != nil {
63+ logger.Error("raw query failed", "err", err)
64+ os.Exit(1)
65+ }
66+
67+ // Compare
68+ fmt.Println("=== Unique Visitors (Intervals) ===")
69+ compareIntervals(newSummary.Intervals, oldSummary.Intervals)
70+
71+ fmt.Println("\n=== Top URLs ===")
72+ compareUrls(newSummary.TopUrls, oldSummary.TopUrls)
73+
74+ fmt.Println("\n=== Top Referers ===")
75+ compareUrls(newSummary.TopReferers, oldSummary.TopReferers)
76+
77+ fmt.Println("\n=== 404 URLs ===")
78+ compareUrls(newSummary.NotFoundUrls, oldSummary.NotFoundUrls)
79+}
80+
81+func queryRawVisits(dbpool *postgres.PsqlDB, opts *db.SummaryOpts) (*db.SummaryVisits, error) {
82+ intervals, err := rawVisitUnique(dbpool, opts)
83+ if err != nil {
84+ return nil, fmt.Errorf("raw visit unique: %w", err)
85+ }
86+ urls, err := rawVisitUrl(dbpool, opts)
87+ if err != nil {
88+ return nil, fmt.Errorf("raw visit url: %w", err)
89+ }
90+ refs, err := rawVisitReferer(dbpool, opts)
91+ if err != nil {
92+ return nil, fmt.Errorf("raw visit referer: %w", err)
93+ }
94+ notFound, err := rawVisitUrlNotFound(dbpool, opts)
95+ if err != nil {
96+ return nil, fmt.Errorf("raw visit url not found: %w", err)
97+ }
98+ return &db.SummaryVisits{
99+ Intervals: intervals,
100+ TopUrls: urls,
101+ TopReferers: refs,
102+ NotFoundUrls: notFound,
103+ }, nil
104+}
105+
106+func rawVisitUnique(dbpool *postgres.PsqlDB, opts *db.SummaryOpts) ([]*db.VisitInterval, error) {
107+ where, with := visitFilterBy(opts)
108+ query := fmt.Sprintf(`
109+ SELECT date_trunc('%s', created_at)::timestamptz as interval_start,
110+ count(DISTINCT ip_address) as unique_visitors
111+ FROM analytics_visits
112+ WHERE created_at >= $1 AND %s = $2 AND user_id = $3 AND status <> 404
113+ GROUP BY interval_start
114+ ORDER BY interval_start`, opts.Interval, where)
115+
116+ rows, err := dbpool.Db.Queryx(query, opts.Origin, with, opts.UserID)
117+ if err != nil {
118+ return nil, err
119+ }
120+ defer func() { _ = rows.Close() }()
121+
122+ var intervals []*db.VisitInterval
123+ for rows.Next() {
124+ iv := &db.VisitInterval{}
125+ if err := rows.Scan(&iv.Interval, &iv.Visitors); err != nil {
126+ return nil, err
127+ }
128+ intervals = append(intervals, iv)
129+ }
130+ return intervals, rows.Err()
131+}
132+
133+func rawVisitUrl(dbpool *postgres.PsqlDB, opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
134+ where, with := visitFilterBy(opts)
135+ query := fmt.Sprintf(`
136+ SELECT path as url, count(DISTINCT ip_address) as count
137+ FROM analytics_visits
138+ WHERE created_at >= $1 AND %s = $2 AND user_id = $3 AND path <> '' AND status <> 404
139+ GROUP BY path
140+ ORDER BY count DESC
141+ LIMIT 10`, where)
142+
143+ rows, err := dbpool.Db.Queryx(query, opts.Origin, with, opts.UserID)
144+ if err != nil {
145+ return nil, err
146+ }
147+ defer func() { _ = rows.Close() }()
148+
149+ var urls []*db.VisitUrl
150+ for rows.Next() {
151+ u := &db.VisitUrl{}
152+ if err := rows.Scan(&u.Url, &u.Count); err != nil {
153+ return nil, err
154+ }
155+ urls = append(urls, u)
156+ }
157+ return urls, rows.Err()
158+}
159+
160+func rawVisitReferer(dbpool *postgres.PsqlDB, opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
161+ where, with := visitFilterBy(opts)
162+ query := fmt.Sprintf(`
163+ SELECT referer as url, count(DISTINCT ip_address) as count
164+ FROM analytics_visits
165+ WHERE created_at >= $1 AND %s = $2 AND user_id = $3 AND referer <> '' AND status <> 404
166+ GROUP BY referer
167+ ORDER BY count DESC
168+ LIMIT 10`, where)
169+
170+ rows, err := dbpool.Db.Queryx(query, opts.Origin, with, opts.UserID)
171+ if err != nil {
172+ return nil, err
173+ }
174+ defer func() { _ = rows.Close() }()
175+
176+ var urls []*db.VisitUrl
177+ for rows.Next() {
178+ u := &db.VisitUrl{}
179+ if err := rows.Scan(&u.Url, &u.Count); err != nil {
180+ return nil, err
181+ }
182+ urls = append(urls, u)
183+ }
184+ return urls, rows.Err()
185+}
186+
187+func rawVisitUrlNotFound(dbpool *postgres.PsqlDB, opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
188+ where, with := visitFilterBy(opts)
189+ limit := opts.Limit
190+ if limit == 0 {
191+ limit = 10
192+ }
193+ query := fmt.Sprintf(`
194+ SELECT path as url, count(DISTINCT ip_address) as count
195+ FROM analytics_visits
196+ WHERE created_at >= $1 AND %s = $2 AND user_id = $3 AND path <> '' AND status = 404
197+ GROUP BY path
198+ ORDER BY count DESC
199+ LIMIT %d`, where, limit)
200+
201+ rows, err := dbpool.Db.Queryx(query, opts.Origin, with, opts.UserID)
202+ if err != nil {
203+ return nil, err
204+ }
205+ defer func() { _ = rows.Close() }()
206+
207+ var urls []*db.VisitUrl
208+ for rows.Next() {
209+ u := &db.VisitUrl{}
210+ if err := rows.Scan(&u.Url, &u.Count); err != nil {
211+ return nil, err
212+ }
213+ urls = append(urls, u)
214+ }
215+ return urls, rows.Err()
216+}
217+
218+func visitFilterBy(opts *db.SummaryOpts) (string, string) {
219+ if opts.Host != "" {
220+ return "host", opts.Host
221+ }
222+ if opts.Path != "" {
223+ return "path", opts.Path
224+ }
225+ return "host", ""
226+}
227+
228+func compareIntervals(newData, oldData []*db.VisitInterval) {
229+ newMap := make(map[string]int)
230+ for _, iv := range newData {
231+ key := iv.Interval.Format("2006-01-02")
232+ newMap[key] = iv.Visitors
233+ }
234+ oldMap := make(map[string]int)
235+ for _, iv := range oldData {
236+ key := iv.Interval.Format("2006-01-02")
237+ oldMap[key] = iv.Visitors
238+ }
239+
240+ // Collect all keys
241+ keys := make(map[string]bool)
242+ for k := range newMap {
243+ keys[k] = true
244+ }
245+ for k := range oldMap {
246+ keys[k] = true
247+ }
248+
249+ mismatches := 0
250+ for k := range keys {
251+ n, nOk := newMap[k]
252+ o, oOk := oldMap[k]
253+ if nOk && oOk {
254+ if n != o {
255+ fmt.Printf(" MISMATCH %s: new=%d old=%d\n", k, n, o)
256+ mismatches++
257+ }
258+ } else if nOk {
259+ fmt.Printf(" NEW ONLY %s: %d\n", k, n)
260+ mismatches++
261+ } else {
262+ fmt.Printf(" OLD ONLY %s: %d\n", k, o)
263+ mismatches++
264+ }
265+ }
266+ if mismatches == 0 {
267+ fmt.Println(" OK: all intervals match")
268+ } else {
269+ fmt.Printf(" %d mismatches\n", mismatches)
270+ }
271+}
272+
273+func compareUrls(newData, oldData []*db.VisitUrl) {
274+ newMap := make(map[string]int)
275+ for _, u := range newData {
276+ newMap[u.Url] = u.Count
277+ }
278+ oldMap := make(map[string]int)
279+ for _, u := range oldData {
280+ oldMap[u.Url] = u.Count
281+ }
282+
283+ mismatches := 0
284+ for url, nCount := range newMap {
285+ if oCount, ok := oldMap[url]; ok {
286+ if nCount != oCount {
287+ fmt.Printf(" MISMATCH %s: new=%d old=%d\n", url, nCount, oCount)
288+ mismatches++
289+ }
290+ } else {
291+ fmt.Printf(" NEW ONLY %s: %d\n", url, nCount)
292+ mismatches++
293+ }
294+ }
295+ for url, oCount := range oldMap {
296+ if _, ok := newMap[url]; !ok {
297+ fmt.Printf(" OLD ONLY %s: %d\n", url, oCount)
298+ mismatches++
299+ }
300+ }
301+ if mismatches == 0 {
302+ fmt.Println(" OK: all entries match")
303+ } else {
304+ fmt.Printf(" %d mismatches\n", mismatches)
305+ }
306+}
A docs/analytics-retention-proposal.md
+336, -0
......@@ -0,0 +1,336 @@
1+# Analytics Data Retention Proposal
2+
3+## Problem
4+
5+The `analytics_visits` table has **18 million rows** in production. All analytics queries (unique visitors, top URLs, top referers, 404s) aggregate raw visit data on every request, causing slow query performance. The table continues to grow unbounded.
6+
7+## Goal
8+
9+- Keep `analytics_visits` small by deleting raw data older than **1 month**
10+- Pre-aggregate monthly stats into summary tables so historical queries remain fast
11+- Preserve all data the user currently sees in the TUI and SSH CLI
12+
13+---
14+
15+## What We Display Today
16+
17+### TUI Analytics Screen (`pkg/tui/analytics.go`)
18+
19+When a user views analytics for a site, we show:
20+
21+| Section | Query | Aggregation |
22+|---------|-------|-------------|
23+| **Site list** (left pane) | `visitHost()` | `COUNT(DISTINCT ip_address)` grouped by `host` |
24+| **Visits by period** | `visitUnique()` | `COUNT(DISTINCT ip_address)` grouped by `date_trunc(interval, created_at)` |
25+| **Top URLs** | `visitUrl()` | `COUNT(DISTINCT ip_address)` grouped by `path`, LIMIT 10, excludes 404s |
26+| **Top Referers** | `visitReferer()` | `COUNT(DISTINCT ip_address)` grouped by `referer`, LIMIT 10, excludes 404s |
27+| **Top 404s** | `VisitUrlNotFound()` | `COUNT(DISTINCT ip_address)` grouped by `path`, LIMIT 10, status=404 |
28+
29+The TUI supports two intervals:
30+- **"day"** — daily buckets from start of current month (`StartOfMonth()`)
31+- **"month"** — monthly buckets from 1 year ago (`StartOfYear()` which is `now - 1 year`)
32+
33+### SSH CLI (`pkg/apps/pico/cli.go`)
34+
35+| Command | Query | Aggregation |
36+|---------|-------|-------------|
37+| `not-found hostname.com [year\|month]` | `VisitUrlNotFound()` | `COUNT(DISTINCT ip_address)` grouped by `path`, LIMIT 100, status=404 |
38+
39+---
40+
41+## Proposed Schema
42+
43+### New Table: `analytics_user_sites`
44+
45+Tracks every site a user has had traffic on. Powers the site list in the left pane of the TUI.
46+
47+```sql
48+CREATE TABLE analytics_user_sites (
49+ id SERIAL PRIMARY KEY,
50+ user_id UUID NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
51+ host VARCHAR(253) NOT NULL,
52+ total_visits INT NOT NULL DEFAULT 0, -- running total of all-time unique visitors
53+ last_seen DATE NOT NULL, -- most recent month with traffic
54+ created_at TIMESTAMP NOT NULL DEFAULT now(),
55+ updated_at TIMESTAMP NOT NULL DEFAULT now(),
56+
57+ UNIQUE (user_id, host)
58+);
59+
60+CREATE INDEX idx_user_sites_user ON analytics_user_sites (user_id);
61+```
62+
63+**How it's populated:** During monthly aggregation, for each `(user_id, host)` pair found in the previous month's raw data:
64+- `INSERT ... ON CONFLICT (user_id, host) DO UPDATE` — add that month's unique visitor count to `total_visits`, set `last_seen` to the month's date.
65+
66+**Query replacement:** `visitHost()` becomes:
67+```sql
68+SELECT host, total_visits as host_count
69+FROM analytics_user_sites
70+WHERE user_id = $1
71+ORDER BY total_visits DESC
72+```
73+
74+No GROUP BY, no scan of raw data — just an indexed lookup.
75+
76+### New Table: `analytics_monthly_visits`
77+
78+Stores pre-aggregated daily unique visitor counts per host. This powers the "visits by period" chart and site list.
79+
80+```sql
81+CREATE TABLE analytics_monthly_visits (
82+ id SERIAL PRIMARY KEY,
83+ user_id UUID NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
84+ host VARCHAR(253) NOT NULL,
85+ visit_date DATE NOT NULL, -- the day this count is for
86+ unique_visits INT NOT NULL DEFAULT 0, -- COUNT(DISTINCT ip_address) for that day
87+ mobile_visits INT NOT NULL DEFAULT 0, -- unique visitors from mobile user-agents
88+ desktop_visits INT NOT NULL DEFAULT 0, -- unique visitors from desktop user-agents
89+ created_at TIMESTAMP NOT NULL DEFAULT now(),
90+
91+ UNIQUE (user_id, host, visit_date)
92+);
93+
94+CREATE INDEX idx_monthly_visits_user_host ON analytics_monthly_visits (user_id, host);
95+CREATE INDEX idx_monthly_visits_user_date ON analytics_monthly_visits (user_id, visit_date DESC);
96+```
97+
98+**Why this shape:** The TUI queries `date_trunc('day', created_at)` or `date_trunc('month', created_at)` with `COUNT(DISTINCT ip_address)`. We pre-compute the daily distinct count. For "month" interval view, the app sums daily rows within each month client-side or via a simple `GROUP BY date_trunc('month', visit_date)`.
99+
100+**Device detection:** `mobile_visits` and `desktop_visits` are derived from the `user_agent` column during aggregation. A user-agent library (e.g., [`pdericx/ua-parser`](https://github.com/pdericx/ua-parser) or [`mssola/useragent`](https://github.com/mssola/useragent)) classifies each visit. An IP counted as mobile if its user-agent is mobile, desktop otherwise. Note: `mobile_visits + desktop_visits = unique_visits` since every visit is one or the other.
101+
102+### New Table: `analytics_monthly_top_urls`
103+
104+Stores the top URLs per host per month. Powers "Top URLs" and "Top 404s" sections.
105+
106+```sql
107+CREATE TABLE analytics_monthly_top_urls (
108+ id SERIAL PRIMARY KEY,
109+ user_id UUID NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
110+ host VARCHAR(253) NOT NULL,
111+ month DATE NOT NULL, -- first day of the month (e.g., 2025-01-01)
112+ path VARCHAR(2048) NOT NULL,
113+ unique_visits INT NOT NULL DEFAULT 0, -- COUNT(DISTINCT ip_address) for that path
114+ is_404 BOOLEAN NOT NULL DEFAULT false,
115+ rank INT NOT NULL, -- 1-10 (top 10) or 1-100 for CLI
116+ created_at TIMESTAMP NOT NULL DEFAULT now(),
117+
118+ UNIQUE (user_id, host, month, path, is_404)
119+);
120+
121+CREATE INDEX idx_monthly_top_urls_user_host_month ON analytics_monthly_top_urls (user_id, host, month);
122+CREATE INDEX idx_monthly_top_urls_404 ON analytics_monthly_top_urls (user_id, host, month, is_404, rank);
123+```
124+
125+### New Table: `analytics_monthly_top_referers`
126+
127+Stores top referers per host per month. Powers "Top Referers" section.
128+
129+```sql
130+CREATE TABLE analytics_monthly_top_referers (
131+ id SERIAL PRIMARY KEY,
132+ user_id UUID NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
133+ host VARCHAR(253) NOT NULL,
134+ month DATE NOT NULL, -- first day of the month
135+ referer VARCHAR(253) NOT NULL,
136+ unique_visits INT NOT NULL DEFAULT 0,
137+ rank INT NOT NULL, -- 1-10
138+ created_at TIMESTAMP NOT NULL DEFAULT now(),
139+
140+ UNIQUE (user_id, host, month, referer)
141+);
142+
143+CREATE INDEX idx_monthly_referers_user_host_month ON analytics_monthly_top_referers (user_id, host, month);
144+```
145+
146+---
147+
148+## Monthly Aggregation Job
149+
150+A script (e.g., `cmd/scripts/analytics-aggregate/main.go`) runs **once per month** (e.g., on the 1st). It:
151+
152+### Step 1: Aggregate the previous month
153+
154+For each user with the `analytics` feature flag who has raw visits in the previous month:
155+
156+```
157+FOR each (user_id, host) pair in analytics_visits WHERE created_at IN previous_month:
158+ -- Daily unique visitors (with device breakdown)
159+ -- Device detection runs in Go (user-agent parsing), not pure SQL.
160+ -- Pseudo-SQL for clarity:
161+ INSERT INTO analytics_monthly_visits (user_id, host, visit_date, unique_visits, mobile_visits, desktop_visits)
162+ SELECT user_id, host, date_trunc('day', created_at)::date,
163+ COUNT(DISTINCT ip_address),
164+ COUNT(DISTINCT CASE WHEN is_mobile(user_agent) THEN ip_address END),
165+ COUNT(DISTINCT CASE WHEN NOT is_mobile(user_agent) THEN ip_address END)
166+ FROM analytics_visits
167+ WHERE user_id = $1 AND host = $2 AND created_at >= start_of_month AND created_at < start_of_next_month
168+ GROUP BY user_id, host, date_trunc('day', created_at)
169+ ON CONFLICT (user_id, host, visit_date) DO NOTHING;
170+
171+ -- In practice, `is_mobile()` is a Go function. The aggregation job fetches
172+ -- raw rows in batches, classifies each user_agent, and issues INSERTs.
173+
174+ -- Top 10 URLs (non-404)
175+ INSERT INTO analytics_monthly_top_urls (user_id, host, month, path, unique_visits, is_404, rank)
176+ SELECT user_id, host, date_trunc('month', created_at)::date, path, COUNT(DISTINCT ip_address), false,
177+ ROW_NUMBER() OVER (PARTITION BY user_id, host ORDER BY COUNT(DISTINCT ip_address) DESC)
178+ FROM analytics_visits
179+ WHERE user_id = $1 AND host = $2 AND created_at >= start_of_month AND created_at < start_of_next_month AND status <> 404
180+ GROUP BY user_id, host, path
181+ HAVING ROW_NUMBER() <= 10
182+ ON CONFLICT (user_id, host, month, path, is_404) DO NOTHING;
183+
184+ -- Top 10 URLs (404s)
185+ INSERT INTO analytics_monthly_top_urls (user_id, host, month, path, unique_visits, is_404, rank)
186+ SELECT user_id, host, date_trunc('month', created_at)::date, path, COUNT(DISTINCT ip_address), true,
187+ ROW_NUMBER() OVER (PARTITION BY user_id, host ORDER BY COUNT(DISTINCT ip_address) DESC)
188+ FROM analytics_visits
189+ WHERE user_id = $1 AND host = $2 AND created_at >= start_of_month AND created_at < start_of_next_month AND status = 404
190+ GROUP BY user_id, host, path
191+ HAVING ROW_NUMBER() <= 100 -- 100 for CLI, 10 for TUI (use 100 to cover both)
192+ ON CONFLICT (user_id, host, month, path, is_404) DO NOTHING;
193+
194+ -- Top 10 referers
195+ INSERT INTO analytics_monthly_top_referers (user_id, host, month, referer, unique_visits, rank)
196+ SELECT user_id, host, date_trunc('month', created_at)::date, referer, COUNT(DISTINCT ip_address),
197+ ROW_NUMBER() OVER (PARTITION BY user_id, host ORDER BY COUNT(DISTINCT ip_address) DESC)
198+ FROM analytics_visits
199+ WHERE user_id = $1 AND host = $2 AND created_at >= start_of_month AND created_at < start_of_next_month AND referer <> '' AND status <> 404
200+ GROUP BY user_id, host, referer
201+ HAVING ROW_NUMBER() <= 10
202+ ON CONFLICT (user_id, host, month, referer) DO NOTHING;
203+```
204+
205+### Step 2: Delete raw data older than 1 month
206+
207+```sql
208+DELETE FROM analytics_visits
209+WHERE created_at < date_trunc('month', now())::timestamp;
210+```
211+
212+This keeps only the current month's raw data. After the first run, `analytics_visits` should drop from ~18M rows to ~1 month of data (estimated 150K–500K depending on traffic).
213+
214+### Step 3: Backfill (one-time)
215+
216+On first deployment, run the aggregation for all historical months before deleting anything. This is a separate migration pass that iterates month-by-month from the oldest data to 2 months ago.
217+
218+---
219+
220+## Query Changes
221+
222+### Current queries → New queries
223+
224+Each existing query method gets a `since` parameter. If `since` is within the last month, query `analytics_visits` directly (current behavior). If `since` spans older months, union results from summary tables with raw data.
225+
226+#### `visitUnique()` — visits by period
227+
228+```
229+-- For "day" interval (current month only, always from raw):
230+-- No change — reads from analytics_visits
231+
232+-- For "month" interval (historical):
233+-- Read from analytics_monthly_visits, sum daily counts per month:
234+SELECT visit_date::date as interval_start, SUM(unique_visits) as visitors
235+FROM analytics_monthly_visits
236+WHERE user_id = $1 AND host = $2 AND visit_date >= $3
237+GROUP BY date_trunc('month', visit_date), visit_date
238+ORDER BY visit_date
239+```
240+
241+#### `visitUrl()` — top URLs
242+
243+```
244+-- Current month: read from analytics_visits (no change)
245+-- Historical months: read from analytics_monthly_top_urls WHERE is_404 = false
246+```
247+
248+#### `visitReferer()` — top referers
249+
250+```
251+-- Current month: read from analytics_visits (no change)
252+-- Historical months: read from analytics_monthly_top_referers
253+```
254+
255+#### `VisitUrlNotFound()` — top 404s
256+
257+```
258+-- Current month: read from analytics_visits (no change)
259+-- Historical months: read from analytics_monthly_top_urls WHERE is_404 = true
260+```
261+
262+#### `visitHost()` — site list
263+
264+```
265+-- Replaced entirely by analytics_user_sites lookup:
266+SELECT host, total_visits as host_count
267+FROM analytics_user_sites
268+WHERE user_id = $1
269+ORDER BY total_visits DESC
270+```
271+
272+No raw table scan. Current month's new hosts are upserted during the monthly aggregation job.
273+
274+---
275+
276+## Implementation Phases
277+
278+### Phase 1: Schema + Backfill (no behavior change)
279+1. Create the four new tables (user_sites, monthly_visits, monthly_top_urls, monthly_top_referers)
280+2. Write the aggregation script (`cmd/scripts/analytics-aggregate/`)
281+3. Run backfill for all historical data
282+4. **Do not delete any data yet** — verify summary tables are correct
283+
284+### Phase 2: Query migration
285+1. Add new DB interface methods that read from summary tables
286+2. Modify existing query methods to union summary + raw data
287+3. Verify TUI and CLI output matches before/after
288+4. Run `make check`
289+
290+### Phase 3: Enable deletion
291+1. Add raw data deletion to the aggregation job
292+2. Schedule the job to run monthly (cron, systemd timer, or app-level scheduler)
293+3. Monitor `analytics_visits` row count after first deletion
294+
295+### Phase 4: Cleanup
296+1. Remove any code paths that no longer need raw historical data
297+2. Consider dropping unused indexes on `analytics_visits` since the table is now small
298+
299+---
300+
301+## Trade-offs
302+
303+| Aspect | Before | After |
304+|--------|--------|-------|
305+| `analytics_visits` size | 18M+ rows (unbounded) | ~1 month of data (~150K-500K) |
306+| Query latency | Scans full table each time | Reads small table or pre-aggregated rows |
307+| Historical granularity | Full raw data | Monthly top-10/100 summaries |
308+| Disk overhead | Single table | 4 additional tables (user_sites, monthly_visits, monthly_top_urls, monthly_top_referers) |
309+| Mobile/desktop | Not tracked | Tracked via user-agent classification during aggregation |
310+| Data loss | None | Raw data older than 1 month is gone |
311+
312+The key trade-off is that we lose the ability to run *arbitrary* ad-hoc queries on historical data. But we preserve exactly what the user sees today in the TUI and CLI. If we need new historical queries in the future, we'd add them to the aggregation job.
313+
314+---
315+
316+## Open Questions
317+
318+1. **Scheduling**: How do we run the monthly job? Options:
319+ - Cron job on the server
320+ - `systemd` timer
321+ - Built into one of our services as a startup check
322+ - External scheduler (GitHub Actions, etc.)
323+
324+2. **`visitHost()` (site list)**: Solved by `analytics_user_sites` table — simple indexed lookup, no aggregation needed.
325+
326+3. **Retention period**: Is 1 month the right window for raw data? Could adjust based on observed query patterns.
327+
328+4. **Concurrent writes**: During the aggregation window, new visits are still being inserted. The job needs to handle this (e.g., aggregate up to a specific timestamp, then delete up to that same timestamp).
329+
330+5. **Users who disable analytics**: The banner says "when analytics are disabled we do not purge usage statistics." Deletion should respect this — only delete for users with the `analytics` feature flag active.
331+
332+6. **Device detection library**: Which Go user-agent parser to use for mobile/desktop classification? Candidates:
333+ - [`mssola/useragent`](https://github.com/mssola/useragent) — lightweight, device.OS / device.Type
334+ - [`pdericx/ua-parser`](https://github.com/pdericx/ua-parser) — fuller UAParser port
335+ - [`danielgtaylor/mobile`](https://github.com/danielgtaylor/mobile) — simple mobile-only detection
336+ The existing codebase already uses `x-way/crawlerdetect` for bot filtering, so a similar pattern fits.
A pkg/apps/auth/analytics_aggregate.go
+437, -0
......@@ -0,0 +1,437 @@
1+package auth
2+
3+import (
4+ "context"
5+ "fmt"
6+ "log/slog"
7+ "strings"
8+ "time"
9+
10+ "github.com/picosh/pico/pkg/db/postgres"
11+)
12+
13+// visitRow represents a single raw visit record for aggregation.
14+type visitRow struct {
15+ UserID string
16+ Host string
17+ Path string
18+ IPAddress string
19+ Referer string
20+ Status int
21+ UserAgent string
22+ CreatedAt time.Time
23+}
24+
25+// dayStats accumulates per-day visitor counts for a (user_id, host) pair.
26+type dayStats struct {
27+ Date time.Time
28+ AllIPs map[string]bool
29+ MobileIPs map[string]bool
30+ DesktopIPs map[string]bool
31+}
32+
33+// pathStats accumulates per-path visitor counts for a (user_id, host, status_code) triplet.
34+type pathStats struct {
35+ Path string
36+ Status int
37+ IPs map[string]bool
38+}
39+
40+// userHostPair represents a unique (user_id, host) combination.
41+type userHostPair struct {
42+ UserID string
43+ Host string
44+}
45+
46+// RunAnalyticsAggregation aggregates raw visits for the given month into summary tables.
47+// Exported for use by the analytics-aggregate CLI script.
48+func RunAnalyticsAggregation(dbpool *postgres.PsqlDB, logger *slog.Logger, targetMonth time.Time) error {
49+ monthStart := time.Date(targetMonth.Year(), targetMonth.Month(), 1, 0, 0, 0, 0, time.UTC)
50+ monthEnd := monthStart.AddDate(0, 1, 0)
51+
52+ logger.Info("starting analytics aggregation", "month", targetMonth.Format("2006-01"), "from", monthStart, "to", monthEnd)
53+
54+ pairs, err := fetchUserHostPairs(dbpool, monthStart, monthEnd)
55+ if err != nil {
56+ return fmt.Errorf("fetch user/host pairs: %w", err)
57+ }
58+ if len(pairs) == 0 {
59+ logger.Info("no user/host pairs to aggregate", "month", targetMonth.Format("2006-01"))
60+ return nil
61+ }
62+ logger.Info("found user/host pairs to process", "count", len(pairs), "month", targetMonth.Format("2006-01"))
63+
64+ for _, pair := range pairs {
65+ visits, err := fetchVisitsForPair(dbpool, pair.UserID, pair.Host, monthStart, monthEnd)
66+ if err != nil {
67+ logger.Error("failed to fetch visits", "err", err, "user_id", pair.UserID, "host", pair.Host)
68+ continue
69+ }
70+
71+ // Aggregate daily stats
72+ dayMap := aggregateDays(visits)
73+ if err := insertMonthlyVisits(dbpool, pair.UserID, pair.Host, dayMap); err != nil {
74+ logger.Error("failed to insert monthly visits", "err", err)
75+ continue
76+ }
77+
78+ // Aggregate top URLs grouped by status code
79+ topURLsByStatus := aggregatePaths(visits)
80+ for status, paths := range topURLsByStatus {
81+ if err := insertTopURLs(dbpool, pair.UserID, pair.Host, targetMonth, status, paths); err != nil {
82+ logger.Error("failed to insert top URLs", "err", err, "status", status)
83+ continue
84+ }
85+ }
86+
87+ // Aggregate top referers
88+ topReferers := aggregateReferers(visits)
89+ if err := insertTopReferers(dbpool, pair.UserID, pair.Host, targetMonth, topReferers); err != nil {
90+ logger.Error("failed to insert top referers", "err", err)
91+ continue
92+ }
93+
94+ // Upsert user site
95+ totalUnique := countTotalUnique(dayMap)
96+ if err := upsertUserSite(dbpool, pair.UserID, pair.Host, totalUnique, targetMonth); err != nil {
97+ logger.Error("failed to upsert user site", "err", err)
98+ continue
99+ }
100+ }
101+
102+ // Delete raw visit data for users with analytics enabled
103+ if err := deleteAggregatedVisits(dbpool, logger, monthStart, monthEnd); err != nil {
104+ logger.Error("failed to delete aggregated visits", "err", err, "month", targetMonth.Format("2006-01"))
105+ }
106+
107+ logger.Info("analytics aggregation complete", "month", targetMonth.Format("2006-01"))
108+ return nil
109+}
110+
111+// analyticsAggregationCron runs once per day to check if the previous month needs aggregation.
112+func analyticsAggregationCron(ctx context.Context, dbpool *postgres.PsqlDB, logger *slog.Logger) {
113+ // Check at midnight UTC every day
114+ ticker := time.NewTicker(24 * time.Hour)
115+ defer ticker.Stop()
116+
117+ // Run immediately on startup to catch up if the service was down
118+ if err := checkAndRunAggregation(dbpool, logger); err != nil {
119+ logger.Error("startup analytics aggregation check failed", "err", err)
120+ }
121+
122+ for {
123+ select {
124+ case <-ctx.Done():
125+ logger.Info("analytics aggregation cron stopped")
126+ return
127+ case <-ticker.C:
128+ if err := checkAndRunAggregation(dbpool, logger); err != nil {
129+ logger.Error("analytics aggregation check failed", "err", err)
130+ }
131+ }
132+ }
133+}
134+
135+// checkAndRunAggregation checks if the previous month has been aggregated and runs if needed.
136+func checkAndRunAggregation(dbpool *postgres.PsqlDB, logger *slog.Logger) error {
137+ prevMonth := time.Now().AddDate(0, -1, 0)
138+ monthStart := time.Date(prevMonth.Year(), prevMonth.Month(), 1, 0, 0, 0, 0, time.UTC)
139+ monthEnd := monthStart.AddDate(0, 1, 0)
140+
141+ // Check if any data exists for this month in the summary table
142+ var count int
143+ err := dbpool.Db.QueryRowContext(context.Background(),
144+ `SELECT COUNT(DISTINCT host) FROM analytics_monthly_visits WHERE visit_date >= $1 AND visit_date < $2`,
145+ monthStart, monthEnd,
146+ ).Scan(&count)
147+ if err != nil {
148+ return fmt.Errorf("check existing aggregation: %w", err)
149+ }
150+
151+ if count > 0 {
152+ logger.Info("previous month already aggregated, skipping", "month", prevMonth.Format("2006-01"), "hosts", count)
153+ return nil
154+ }
155+
156+ logger.Info("previous month not aggregated, running now", "month", prevMonth.Format("2006-01"))
157+ return RunAnalyticsAggregation(dbpool, logger, prevMonth)
158+}
159+
160+func fetchUserHostPairs(dbpool *postgres.PsqlDB, monthStart, monthEnd time.Time) ([]userHostPair, error) {
161+ rows, err := dbpool.Db.Queryx(
162+ `SELECT DISTINCT user_id, host FROM analytics_visits WHERE created_at >= $1 AND created_at < $2 AND host <> '' ORDER BY user_id, host`,
163+ monthStart, monthEnd,
164+ )
165+ if err != nil {
166+ return nil, err
167+ }
168+ defer func() { _ = rows.Close() }()
169+
170+ var pairs []userHostPair
171+ for rows.Next() {
172+ var p userHostPair
173+ if err := rows.Scan(&p.UserID, &p.Host); err != nil {
174+ return nil, err
175+ }
176+ pairs = append(pairs, p)
177+ }
178+ return pairs, rows.Err()
179+}
180+
181+func fetchVisitsForPair(dbpool *postgres.PsqlDB, userID, host string, monthStart, monthEnd time.Time) ([]visitRow, error) {
182+ rows, err := dbpool.Db.Queryx(
183+ `SELECT user_id, host, path, ip_address, referer, status, user_agent, created_at
184+ FROM analytics_visits
185+ WHERE user_id = $1 AND host = $2 AND created_at >= $3 AND created_at < $4`,
186+ userID, host, monthStart, monthEnd,
187+ )
188+ if err != nil {
189+ return nil, err
190+ }
191+ defer func() { _ = rows.Close() }()
192+
193+ var visits []visitRow
194+ for rows.Next() {
195+ var v visitRow
196+ if err := rows.Scan(&v.UserID, &v.Host, &v.Path, &v.IPAddress, &v.Referer, &v.Status, &v.UserAgent, &v.CreatedAt); err != nil {
197+ return nil, err
198+ }
199+ visits = append(visits, v)
200+ }
201+ return visits, rows.Err()
202+}
203+
204+func aggregateDays(visits []visitRow) map[string]*dayStats {
205+ dayMap := make(map[string]*dayStats)
206+
207+ for _, v := range visits {
208+ // Exclude 404s from unique visitor counts — bots hitting non-existent
209+ // paths shouldn't count as visitors
210+ if v.Status == 404 {
211+ continue
212+ }
213+
214+ dateKey := v.CreatedAt.UTC().Format("2006-01-02")
215+ ds, ok := dayMap[dateKey]
216+ if !ok {
217+ ds = &dayStats{
218+ Date: time.Date(v.CreatedAt.Year(), v.CreatedAt.Month(), v.CreatedAt.Day(), 0, 0, 0, 0, time.UTC),
219+ AllIPs: make(map[string]bool),
220+ MobileIPs: make(map[string]bool),
221+ DesktopIPs: make(map[string]bool),
222+ }
223+ dayMap[dateKey] = ds
224+ }
225+
226+ ds.AllIPs[v.IPAddress] = true
227+ if isMobile(v.UserAgent) {
228+ ds.MobileIPs[v.IPAddress] = true
229+ } else {
230+ ds.DesktopIPs[v.IPAddress] = true
231+ }
232+ }
233+ return dayMap
234+}
235+
236+func aggregatePaths(visits []visitRow) map[int][]pathStats {
237+ // Maps status_code -> list of pathStats
238+ statusMap := make(map[int]map[string]*pathStats)
239+
240+ for _, v := range visits {
241+ if v.Path == "" {
242+ continue
243+ }
244+
245+ if _, ok := statusMap[v.Status]; !ok {
246+ statusMap[v.Status] = make(map[string]*pathStats)
247+ }
248+
249+ key := fmt.Sprintf("%s/%d", v.Path, v.Status)
250+ ps, ok := statusMap[v.Status][key]
251+ if !ok {
252+ ps = &pathStats{Path: v.Path, Status: v.Status, IPs: make(map[string]bool)}
253+ statusMap[v.Status][key] = ps
254+ }
255+ ps.IPs[v.IPAddress] = true
256+ }
257+
258+ result := make(map[int][]pathStats)
259+ for status, paths := range statusMap {
260+ list := make([]pathStats, 0, len(paths))
261+ for _, ps := range paths {
262+ list = append(list, *ps)
263+ }
264+ sortByCount(list)
265+ result[status] = list
266+ }
267+ return result
268+}
269+
270+func aggregateReferers(visits []visitRow) []pathStats {
271+ refMap := make(map[string]*pathStats)
272+
273+ for _, v := range visits {
274+ if v.Referer == "" || v.Status == 404 {
275+ continue
276+ }
277+
278+ rs, ok := refMap[v.Referer]
279+ if !ok {
280+ rs = &pathStats{Path: v.Referer, IPs: make(map[string]bool)}
281+ refMap[v.Referer] = rs
282+ }
283+ rs.IPs[v.IPAddress] = true
284+ }
285+
286+ result := make([]pathStats, 0, len(refMap))
287+ for _, rs := range refMap {
288+ result = append(result, *rs)
289+ }
290+ sortByCount(result)
291+ return result
292+}
293+
294+func sortByCount(paths []pathStats) {
295+ for i := 1; i < len(paths); i++ {
296+ for j := i; j > 0 && len(paths[j].IPs) > len(paths[j-1].IPs); j-- {
297+ paths[j], paths[j-1] = paths[j-1], paths[j]
298+ }
299+ }
300+}
301+
302+func insertMonthlyVisits(dbpool *postgres.PsqlDB, userID, host string, dayMap map[string]*dayStats) error {
303+ for _, ds := range dayMap {
304+ _, err := dbpool.Db.Exec(
305+ `INSERT INTO analytics_monthly_visits (user_id, host, visit_date, unique_visits, mobile_visits, desktop_visits)
306+ VALUES ($1, $2, $3, $4, $5, $6)
307+ ON CONFLICT (user_id, host, visit_date) DO UPDATE
308+ SET unique_visits = EXCLUDED.unique_visits,
309+ mobile_visits = EXCLUDED.mobile_visits,
310+ desktop_visits = EXCLUDED.desktop_visits`,
311+ userID, host, ds.Date,
312+ len(ds.AllIPs), len(ds.MobileIPs), len(ds.DesktopIPs),
313+ )
314+ if err != nil {
315+ return fmt.Errorf("insert monthly visits for %s: %w", ds.Date.Format("2006-01-02"), err)
316+ }
317+ }
318+ return nil
319+}
320+
321+func insertTopURLs(dbpool *postgres.PsqlDB, userID, host string, month time.Time, statusCode int, paths []pathStats) error {
322+ limit := 10
323+ if statusCode == 404 {
324+ limit = 100
325+ }
326+ if len(paths) > limit {
327+ paths = paths[:limit]
328+ }
329+
330+ for rank, ps := range paths {
331+ _, err := dbpool.Db.Exec(
332+ `INSERT INTO analytics_monthly_top_urls (user_id, host, month, path, unique_visits, status_code, rank)
333+ VALUES ($1, $2, $3, $4, $5, $6, $7)
334+ ON CONFLICT (user_id, host, month, path, status_code) DO UPDATE
335+ SET unique_visits = EXCLUDED.unique_visits,
336+ rank = EXCLUDED.rank`,
337+ userID, host, month, ps.Path, len(ps.IPs), statusCode, rank+1,
338+ )
339+ if err != nil {
340+ return fmt.Errorf("insert top url %s: %w", ps.Path, err)
341+ }
342+ }
343+ return nil
344+}
345+
346+func insertTopReferers(dbpool *postgres.PsqlDB, userID, host string, month time.Time, refs []pathStats) error {
347+ limit := 10
348+ if len(refs) > limit {
349+ refs = refs[:limit]
350+ }
351+
352+ for rank, rs := range refs {
353+ _, err := dbpool.Db.Exec(
354+ `INSERT INTO analytics_monthly_top_referers (user_id, host, month, referer, unique_visits, rank)
355+ VALUES ($1, $2, $3, $4, $5, $6)
356+ ON CONFLICT (user_id, host, month, referer) DO UPDATE
357+ SET unique_visits = EXCLUDED.unique_visits,
358+ rank = EXCLUDED.rank`,
359+ userID, host, month, rs.Path, len(rs.IPs), rank+1,
360+ )
361+ if err != nil {
362+ return fmt.Errorf("insert top referer %s: %w", rs.Path, err)
363+ }
364+ }
365+ return nil
366+}
367+
368+func countTotalUnique(dayMap map[string]*dayStats) int {
369+ allIPs := make(map[string]bool)
370+ for _, ds := range dayMap {
371+ for ip := range ds.AllIPs {
372+ allIPs[ip] = true
373+ }
374+ }
375+ return len(allIPs)
376+}
377+
378+func upsertUserSite(dbpool *postgres.PsqlDB, userID, host string, monthUnique int, month time.Time) error {
379+ _, err := dbpool.Db.Exec(
380+ `INSERT INTO analytics_user_sites (user_id, host, total_visits, last_seen)
381+ VALUES ($1, $2, $3, $4)
382+ ON CONFLICT (user_id, host) DO UPDATE
383+ SET total_visits = analytics_user_sites.total_visits + EXCLUDED.total_visits,
384+ last_seen = EXCLUDED.last_seen`,
385+ userID, host, monthUnique, month,
386+ )
387+ return err
388+}
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+// deleteAggregatedVisits deletes raw visit data from analytics_visits for the given month.
412+// Data is preserved in summary tables, so this is safe regardless of feature flags.
413+// Raw data for the current and previous months is never deleted since visitUniqueFromRaw
414+// still reads from analytics_visits for those months.
415+func deleteAggregatedVisits(dbpool *postgres.PsqlDB, logger *slog.Logger, monthStart, monthEnd time.Time) error {
416+ now := time.Now()
417+ currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
418+ previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
419+
420+ // Never delete raw data for the current or previous month —
421+ // visitUniqueFromRaw still reads from analytics_visits for those months.
422+ if !monthStart.Before(previousMonthStart) {
423+ logger.Info("skipping raw visit deletion for recent month", "month_start", monthStart.Format("2006-01"))
424+ return nil
425+ }
426+
427+ result, err := dbpool.Db.Exec(`
428+ DELETE FROM analytics_visits
429+ WHERE created_at >= $1 AND created_at < $2`, monthStart, monthEnd)
430+ if err != nil {
431+ return fmt.Errorf("delete aggregated visits: %w", err)
432+ }
433+
434+ deleted, _ := result.RowsAffected()
435+ logger.Info("deleted aggregated visits", "month_start", monthStart.Format("2006-01"), "deleted", deleted)
436+ return nil
437+}
M pkg/apps/auth/api.go
+2, -0
......@@ -922,6 +922,8 @@ func StartApiServer() {
922922 go metricDrainSub(ctx, db, logger, cfg.Secret)
923923 // gather connect/disconnect logs from tuns
924924 go tunsEventLogDrainSub(ctx, db, logger, cfg.Secret)
925+ // monthly analytics aggregation (checks daily, runs if previous month is missing)
926+ go analyticsAggregationCron(ctx, db, logger)
925927
926928 apiConfig := &router.ApiConfig{
927929 Cfg: cfg,
M pkg/db/db.go
+4, -2
......@@ -159,8 +159,10 @@ type Paginate[T any] struct {
159159 }
160160
161161 type VisitInterval struct {
162- Interval *time.Time `json:"interval" db:"interval"`
163- Visitors int `json:"visitors" db:"visitors"`
162+ Interval *time.Time `json:"interval" db:"interval"`
163+ Visitors int `json:"visitors" db:"visitors"`
164+ MobileVisitors int `json:"mobile_visitors" db:"mobile_visitors"`
165+ DesktopVisitors int `json:"desktop_visitors" db:"desktop_visitors"`
164166 }
165167
166168 type VisitUrl struct {
M pkg/db/postgres/storage.go
+550, -109
......@@ -7,6 +7,7 @@ import (
77 "log/slog"
88 "math"
99 "slices"
10+ "sort"
1011 "strings"
1112 "time"
1213
......@@ -16,6 +17,9 @@ import (
1617 "github.com/picosh/pico/pkg/shared"
1718 )
1819
20+// mobileUserAgentExpr is a SQL expression to detect mobile user agents.
21+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%'`
22+
1923 var PAGER_SIZE = 15
2024
2125 var SelectPost = `
......@@ -720,109 +724,444 @@ func visitFilterBy(opts *db.SummaryOpts) (string, string) {
720724 }
721725
722726 func (me *PsqlDB) visitUnique(opts *db.SummaryOpts) ([]*db.VisitInterval, error) {
723- where, with := visitFilterBy(opts)
724- uniqueVisitors := fmt.Sprintf(`SELECT
725- date_trunc('%s', created_at) as interval_start,
726- count(DISTINCT ip_address) as unique_visitors
727- FROM analytics_visits
728- WHERE created_at >= $1 AND %s = $2 AND user_id = $3 AND status <> 404
729- GROUP BY interval_start`, opts.Interval, where)
727+ intervals, err := me.visitUniqueFromSummary(opts)
728+ if err != nil {
729+ return nil, fmt.Errorf("query summary visits: %w", err)
730+ }
731+
732+ currentIntervals, err := me.visitUniqueFromRaw(opts)
733+ if err != nil {
734+ return nil, fmt.Errorf("query raw visits: %w", err)
735+ }
736+
737+ // Merge: current month data may overlap with summary data, combine counts
738+ return mergeVisitIntervals(intervals, currentIntervals), nil
739+}
740+
741+// visitUniqueFromSummary reads unique visitor counts from analytics_monthly_visits for historical data.
742+func (me *PsqlDB) visitUniqueFromSummary(opts *db.SummaryOpts) ([]*db.VisitInterval, error) {
743+ now := time.Now()
744+ currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
745+ previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
746+
747+ // Always include at least the previous month in the summary query, since raw data
748+ // for the previous month may have been deleted by aggregation. visitUniqueFromRaw
749+ // covers the previous and current months, but once aggregation runs and deletes raw
750+ // data, the summary table is the only source. Using min(origin, previousMonthStart)
751+ // ensures we don't skip the previous month even when origin is set to the current month.
752+ effectiveOrigin := opts.Origin
753+ if opts.Origin.After(previousMonthStart) {
754+ effectiveOrigin = previousMonthStart
755+ }
756+
757+ // If effective origin is in or after current month, no historical data to fetch
758+ if !effectiveOrigin.Before(currentMonthStart) {
759+ return nil, nil
760+ }
730761
731- intervals := []*db.VisitInterval{}
732- rs, err := me.Db.Queryx(uniqueVisitors, opts.Origin, with, opts.UserID)
762+ where := ""
763+ args := []interface{}{opts.UserID, effectiveOrigin, currentMonthStart}
764+ argIdx := 4
765+ if opts.Host != "" {
766+ where = "AND host = $" + fmt.Sprintf("%d", argIdx)
767+ args = append(args, opts.Host)
768+ }
769+
770+ query := fmt.Sprintf(`
771+ SELECT
772+ date_trunc('%s', visit_date)::timestamptz as interval_start,
773+ sum(unique_visits) as unique_visitors,
774+ sum(mobile_visits) as mobile_visits,
775+ sum(desktop_visits) as desktop_visits
776+ FROM analytics_monthly_visits
777+ WHERE user_id = $1 AND visit_date >= $2 AND visit_date < $3 %s
778+ GROUP BY interval_start
779+ ORDER BY interval_start`, opts.Interval, where)
780+
781+ rows, err := me.Db.Queryx(query, args...)
733782 if err != nil {
734783 return nil, err
735784 }
736- defer func() { _ = rs.Close() }()
785+ defer func() { _ = rows.Close() }()
737786
738- for rs.Next() {
787+ var intervals []*db.VisitInterval
788+ for rows.Next() {
739789 interval := &db.VisitInterval{}
740- err := rs.Scan(
741- &interval.Interval,
742- &interval.Visitors,
743- )
744- if err != nil {
790+ if err := rows.Scan(&interval.Interval, &interval.Visitors, &interval.MobileVisitors, &interval.DesktopVisitors); err != nil {
745791 return nil, err
746792 }
793+ intervals = append(intervals, interval)
794+ }
795+ return intervals, rows.Err()
796+}
797+
798+// visitUniqueFromRaw reads unique visitor counts from analytics_visits for the previous and current months.
799+// This covers the gap between the last aggregated month and the current month.
800+func (me *PsqlDB) visitUniqueFromRaw(opts *db.SummaryOpts) ([]*db.VisitInterval, error) {
801+ now := time.Now()
802+ currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
803+ previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
747804
805+ where, with := visitFilterBy(opts)
806+
807+ // Determine the effective start: max(origin, previousMonthStart)
808+ effectiveStart := previousMonthStart
809+ if opts.Origin.After(previousMonthStart) {
810+ effectiveStart = opts.Origin
811+ }
812+
813+ uniqueVisitors := fmt.Sprintf(`
814+ SELECT
815+ date_trunc('%s', created_at)::timestamptz as interval_start,
816+ count(DISTINCT CASE WHEN %s THEN ip_address END) as mobile_visitors,
817+ count(DISTINCT CASE WHEN NOT %s THEN ip_address END) as desktop_visitors,
818+ count(DISTINCT ip_address) as unique_visitors
819+ FROM analytics_visits
820+ WHERE created_at >= $1 AND created_at < $2 AND %s = $3 AND user_id = $4 AND status <> 404
821+ GROUP BY interval_start
822+ ORDER BY interval_start`,
823+ opts.Interval, mobileUserAgentExpr, mobileUserAgentExpr, where)
824+
825+ rows, err := me.Db.Queryx(uniqueVisitors, effectiveStart, currentMonthStart.AddDate(0, 1, 0), with, opts.UserID)
826+ if err != nil {
827+ return nil, err
828+ }
829+ defer func() { _ = rows.Close() }()
830+
831+ var intervals []*db.VisitInterval
832+ for rows.Next() {
833+ interval := &db.VisitInterval{}
834+ if err := rows.Scan(&interval.Interval, &interval.MobileVisitors, &interval.DesktopVisitors, &interval.Visitors); err != nil {
835+ return nil, err
836+ }
748837 intervals = append(intervals, interval)
749838 }
750- if rs.Err() != nil {
751- return nil, rs.Err()
839+ return intervals, rows.Err()
840+}
841+
842+// mergeVisitIntervals combines historical (summary table) and current (raw) intervals.
843+// Summary data is preferred when both sources have the same interval to avoid double-counting.
844+func mergeVisitIntervals(historical, current []*db.VisitInterval) []*db.VisitInterval {
845+ if len(historical) == 0 {
846+ return current
847+ }
848+ if len(current) == 0 {
849+ return historical
850+ }
851+
852+ // Build a map by interval timestamp for merging.
853+ // Summary data takes precedence over raw data for the same interval
854+ // (e.g. when a month is aggregated but raw data still exists in a local dump).
855+ intervalMap := make(map[int64]*db.VisitInterval)
856+ for _, ci := range current {
857+ ts := ci.Interval.Unix()
858+ intervalMap[ts] = ci
859+ }
860+ for _, hi := range historical {
861+ ts := hi.Interval.Unix()
862+ intervalMap[ts] = hi // summary overwrites raw if both exist
863+ }
864+
865+ // Sort by interval
866+ result := make([]*db.VisitInterval, 0, len(intervalMap))
867+ for _, iv := range intervalMap {
868+ result = append(result, iv)
752869 }
753- return intervals, nil
870+ sort.Slice(result, func(i, j int) bool {
871+ return result[i].Interval.Before(*result[j].Interval)
872+ })
873+ return result
874+}
875+
876+// mergeTopUrls combines historical and current top URLs, summing counts for overlapping URLs,
877+// then returns the top 10 by total count.
878+func mergeTopUrls(historical, current []*db.VisitUrl) []*db.VisitUrl {
879+ if len(historical) == 0 {
880+ return current
881+ }
882+ if len(current) == 0 {
883+ return historical
884+ }
885+
886+ // Build a map by URL for merging
887+ urlMap := make(map[string]*db.VisitUrl)
888+ for _, hu := range historical {
889+ urlMap[hu.Url] = hu
890+ }
891+
892+ for _, cu := range current {
893+ if existing, ok := urlMap[cu.Url]; ok {
894+ existing.Count += cu.Count
895+ } else {
896+ urlMap[cu.Url] = cu
897+ }
898+ }
899+
900+ // Sort by count descending and take top 10
901+ result := make([]*db.VisitUrl, 0, len(urlMap))
902+ for _, u := range urlMap {
903+ result = append(result, u)
904+ }
905+ sort.Slice(result, func(i, j int) bool {
906+ return result[i].Count > result[j].Count
907+ })
908+ if len(result) > 10 {
909+ result = result[:10]
910+ }
911+ return result
912+}
913+
914+// mergeTopReferers combines historical and current top referers, summing counts for overlapping referers,
915+// then returns the top 10 by total count.
916+func mergeTopReferers(historical, current []*db.VisitUrl) []*db.VisitUrl {
917+ return mergeTopUrls(historical, current) // Same logic as mergeTopUrls
918+}
919+
920+// mergeHosts combines historical and current hosts, summing counts for overlapping hosts,
921+// then returns sorted by total count descending.
922+func mergeHosts(historical, current []*db.VisitUrl) []*db.VisitUrl {
923+ if len(historical) == 0 {
924+ return current
925+ }
926+ if len(current) == 0 {
927+ return historical
928+ }
929+
930+ // Build a map by host for merging
931+ hostMap := make(map[string]*db.VisitUrl)
932+ for _, hu := range historical {
933+ hostMap[hu.Url] = hu
934+ }
935+
936+ for _, cu := range current {
937+ if existing, ok := hostMap[cu.Url]; ok {
938+ existing.Count += cu.Count
939+ } else {
940+ hostMap[cu.Url] = cu
941+ }
942+ }
943+
944+ // Sort by count descending
945+ result := make([]*db.VisitUrl, 0, len(hostMap))
946+ for _, h := range hostMap {
947+ result = append(result, h)
948+ }
949+ sort.Slice(result, func(i, j int) bool {
950+ return result[i].Count > result[j].Count
951+ })
952+ return result
754953 }
755954
756955 func (me *PsqlDB) visitReferer(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
757- where, with := visitFilterBy(opts)
758- topUrls := fmt.Sprintf(`SELECT
759- referer,
760- count(DISTINCT ip_address) as referer_count
761- FROM analytics_visits
762- WHERE created_at >= $1 AND %s = $2 AND user_id = $3 AND referer <> '' AND status <> 404
763- GROUP BY referer
764- ORDER BY referer_count DESC
765- LIMIT 10`, where)
956+ historical, err := me.visitRefererFromSummary(opts)
957+ if err != nil {
958+ return nil, fmt.Errorf("query summary referers: %w", err)
959+ }
766960
767- intervals := []*db.VisitUrl{}
768- rs, err := me.Db.Queryx(topUrls, opts.Origin, with, opts.UserID)
961+ current, err := me.visitRefererFromRaw(opts)
962+ if err != nil {
963+ return nil, fmt.Errorf("query raw referers: %w", err)
964+ }
965+
966+ return mergeTopReferers(historical, current), nil
967+}
968+
969+// visitRefererFromSummary reads top referers from analytics_monthly_top_referers for historical data.
970+func (me *PsqlDB) visitRefererFromSummary(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
971+ now := time.Now()
972+ currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
973+ previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
974+
975+ effectiveOrigin := opts.Origin
976+ if opts.Origin.After(previousMonthStart) {
977+ effectiveOrigin = previousMonthStart
978+ }
979+
980+ // If effective origin is in or after current month, no historical data to fetch
981+ if !effectiveOrigin.Before(currentMonthStart) {
982+ return nil, nil
983+ }
984+
985+ // Clamp origin to month boundary for summary table lookup
986+ originMonthStart := time.Date(effectiveOrigin.Year(), effectiveOrigin.Month(), 1, 0, 0, 0, 0, time.UTC)
987+
988+ where := ""
989+ args := []interface{}{opts.UserID, originMonthStart, currentMonthStart}
990+ if opts.Host != "" {
991+ where = "AND host = $4"
992+ args = append(args, opts.Host)
993+ }
994+
995+ query := fmt.Sprintf(`
996+ SELECT referer, sum(unique_visits) as total_visits
997+ FROM analytics_monthly_top_referers
998+ WHERE user_id = $1 AND month >= $2 AND month < $3 %s
999+ GROUP BY referer
1000+ ORDER BY total_visits DESC
1001+ LIMIT 10`, where)
1002+
1003+ rows, err := me.Db.Queryx(query, args...)
7691004 if err != nil {
7701005 return nil, err
7711006 }
772- defer func() { _ = rs.Close() }()
1007+ defer func() { _ = rows.Close() }()
7731008
774- for rs.Next() {
775- interval := &db.VisitUrl{}
776- err := rs.Scan(
777- &interval.Url,
778- &interval.Count,
779- )
780- if err != nil {
1009+ var results []*db.VisitUrl
1010+ for rows.Next() {
1011+ result := &db.VisitUrl{}
1012+ if err := rows.Scan(&result.Url, &result.Count); err != nil {
7811013 return nil, err
7821014 }
1015+ results = append(results, result)
1016+ }
1017+ return results, rows.Err()
1018+}
7831019
784- intervals = append(intervals, interval)
1020+// visitRefererFromRaw reads top referers from analytics_visits for the previous and current months.
1021+func (me *PsqlDB) visitRefererFromRaw(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
1022+ now := time.Now()
1023+ currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
1024+ previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
1025+
1026+ where, with := visitFilterBy(opts)
1027+
1028+ // Determine the effective start: max(origin, previousMonthStart)
1029+ effectiveStart := previousMonthStart
1030+ if opts.Origin.After(previousMonthStart) {
1031+ effectiveStart = opts.Origin
7851032 }
786- if rs.Err() != nil {
787- return nil, rs.Err()
1033+
1034+ topUrls := fmt.Sprintf(`
1035+ SELECT
1036+ referer,
1037+ count(DISTINCT ip_address) as referer_count
1038+ FROM analytics_visits
1039+ WHERE created_at >= $1 AND created_at < $2 AND %s = $3 AND user_id = $4 AND referer <> '' AND status <> 404
1040+ GROUP BY referer
1041+ ORDER BY referer_count DESC
1042+ LIMIT 10`, where)
1043+
1044+ rows, err := me.Db.Queryx(topUrls, effectiveStart, currentMonthStart.AddDate(0, 1, 0), with, opts.UserID)
1045+ if err != nil {
1046+ return nil, err
7881047 }
789- return intervals, nil
1048+ defer func() { _ = rows.Close() }()
1049+
1050+ var results []*db.VisitUrl
1051+ for rows.Next() {
1052+ result := &db.VisitUrl{}
1053+ if err := rows.Scan(&result.Url, &result.Count); err != nil {
1054+ return nil, err
1055+ }
1056+ results = append(results, result)
1057+ }
1058+ return results, rows.Err()
7901059 }
7911060
7921061 func (me *PsqlDB) visitUrl(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
793- where, with := visitFilterBy(opts)
794- topUrls := fmt.Sprintf(`SELECT
795- path,
796- count(DISTINCT ip_address) as path_count
797- FROM analytics_visits
798- WHERE created_at >= $1 AND %s = $2 AND user_id = $3 AND path <> '' AND status <> 404
799- GROUP BY path
800- ORDER BY path_count DESC
801- LIMIT 10`, where)
1062+ historical, err := me.visitUrlFromSummary(opts)
1063+ if err != nil {
1064+ return nil, fmt.Errorf("query summary urls: %w", err)
1065+ }
1066+
1067+ current, err := me.visitUrlFromRaw(opts)
1068+ if err != nil {
1069+ return nil, fmt.Errorf("query raw urls: %w", err)
1070+ }
1071+
1072+ return mergeTopUrls(historical, current), nil
1073+}
1074+
1075+// visitUrlFromSummary reads top URLs from analytics_monthly_top_urls for historical data.
1076+func (me *PsqlDB) visitUrlFromSummary(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
1077+ now := time.Now()
1078+ currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
1079+ previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
8021080
803- intervals := []*db.VisitUrl{}
804- rs, err := me.Db.Queryx(topUrls, opts.Origin, with, opts.UserID)
1081+ effectiveOrigin := opts.Origin
1082+ if opts.Origin.After(previousMonthStart) {
1083+ effectiveOrigin = previousMonthStart
1084+ }
1085+
1086+ // If effective origin is in or after current month, no historical data to fetch
1087+ if !effectiveOrigin.Before(currentMonthStart) {
1088+ return nil, nil
1089+ }
1090+
1091+ // Clamp origin to month boundary for summary table lookup
1092+ originMonthStart := time.Date(effectiveOrigin.Year(), effectiveOrigin.Month(), 1, 0, 0, 0, 0, time.UTC)
1093+
1094+ where := ""
1095+ args := []interface{}{opts.UserID, originMonthStart, currentMonthStart}
1096+ if opts.Host != "" {
1097+ where = "AND host = $4"
1098+ args = append(args, opts.Host)
1099+ }
1100+
1101+ query := fmt.Sprintf(`
1102+ SELECT path, sum(unique_visits) as total_visits
1103+ FROM analytics_monthly_top_urls
1104+ WHERE user_id = $1 AND month >= $2 AND month < $3 AND status_code <> 404 %s
1105+ GROUP BY path
1106+ ORDER BY total_visits DESC
1107+ LIMIT 10`, where)
1108+
1109+ rows, err := me.Db.Queryx(query, args...)
8051110 if err != nil {
8061111 return nil, err
8071112 }
808- defer func() { _ = rs.Close() }()
1113+ defer func() { _ = rows.Close() }()
8091114
810- for rs.Next() {
811- interval := &db.VisitUrl{}
812- err := rs.Scan(
813- &interval.Url,
814- &interval.Count,
815- )
816- if err != nil {
1115+ var results []*db.VisitUrl
1116+ for rows.Next() {
1117+ result := &db.VisitUrl{}
1118+ if err := rows.Scan(&result.Url, &result.Count); err != nil {
8171119 return nil, err
8181120 }
1121+ results = append(results, result)
1122+ }
1123+ return results, rows.Err()
1124+}
8191125
820- intervals = append(intervals, interval)
1126+// visitUrlFromRaw reads top URLs from analytics_visits for the previous and current months.
1127+func (me *PsqlDB) visitUrlFromRaw(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
1128+ now := time.Now()
1129+ currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
1130+ previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
1131+
1132+ where, with := visitFilterBy(opts)
1133+
1134+ // Determine the effective start: max(origin, previousMonthStart)
1135+ effectiveStart := previousMonthStart
1136+ if opts.Origin.After(previousMonthStart) {
1137+ effectiveStart = opts.Origin
8211138 }
822- if rs.Err() != nil {
823- return nil, rs.Err()
1139+
1140+ topUrls := fmt.Sprintf(`
1141+ SELECT
1142+ path,
1143+ count(DISTINCT ip_address) as path_count
1144+ FROM analytics_visits
1145+ WHERE created_at >= $1 AND created_at < $2 AND %s = $3 AND user_id = $4 AND path <> '' AND status <> 404
1146+ GROUP BY path
1147+ ORDER BY path_count DESC
1148+ LIMIT 10`, where)
1149+
1150+ rows, err := me.Db.Queryx(topUrls, effectiveStart, currentMonthStart.AddDate(0, 1, 0), with, opts.UserID)
1151+ if err != nil {
1152+ return nil, err
8241153 }
825- return intervals, nil
1154+ defer func() { _ = rows.Close() }()
1155+
1156+ var results []*db.VisitUrl
1157+ for rows.Next() {
1158+ result := &db.VisitUrl{}
1159+ if err := rows.Scan(&result.Url, &result.Count); err != nil {
1160+ return nil, err
1161+ }
1162+ results = append(results, result)
1163+ }
1164+ return results, rows.Err()
8261165 }
8271166
8281167 func (me *PsqlDB) VisitUrlNotFound(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
......@@ -830,73 +1169,175 @@ func (me *PsqlDB) VisitUrlNotFound(opts *db.SummaryOpts) ([]*db.VisitUrl, error)
8301169 if limit == 0 {
8311170 limit = 10
8321171 }
833- where, with := visitFilterBy(opts)
834- topUrls := fmt.Sprintf(`SELECT
835- path,
836- count(DISTINCT ip_address) as path_count
837- FROM analytics_visits
838- WHERE created_at >= $1 AND %s = $2 AND user_id = $3 AND path <> '' AND status = 404
839- GROUP BY path
840- ORDER BY path_count DESC
841- LIMIT %d`, where, limit)
8421172
843- intervals := []*db.VisitUrl{}
844- rs, err := me.Db.Queryx(topUrls, opts.Origin, with, opts.UserID)
1173+ historical, err := me.visitUrlNotFoundFromSummary(opts, limit)
1174+ if err != nil {
1175+ return nil, fmt.Errorf("query summary 404 urls: %w", err)
1176+ }
1177+
1178+ current, err := me.visitUrlNotFoundFromRaw(opts, limit)
1179+ if err != nil {
1180+ return nil, fmt.Errorf("query raw 404 urls: %w", err)
1181+ }
1182+
1183+ return mergeTopUrls(historical, current), nil
1184+}
1185+
1186+// visitUrlNotFoundFromSummary reads top 404 URLs from analytics_monthly_top_urls for historical data.
1187+func (me *PsqlDB) visitUrlNotFoundFromSummary(opts *db.SummaryOpts, limit int) ([]*db.VisitUrl, error) {
1188+ now := time.Now()
1189+ currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
1190+ previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
1191+
1192+ effectiveOrigin := opts.Origin
1193+ if opts.Origin.After(previousMonthStart) {
1194+ effectiveOrigin = previousMonthStart
1195+ }
1196+
1197+ // If effective origin is in or after current month, no historical data to fetch
1198+ if !effectiveOrigin.Before(currentMonthStart) {
1199+ return nil, nil
1200+ }
1201+
1202+ // Clamp origin to month boundary for summary table lookup
1203+ originMonthStart := time.Date(effectiveOrigin.Year(), effectiveOrigin.Month(), 1, 0, 0, 0, 0, time.UTC)
1204+
1205+ where := ""
1206+ args := []interface{}{opts.UserID, originMonthStart, currentMonthStart}
1207+ argIdx := 4
1208+ if opts.Host != "" {
1209+ where = "AND host = $" + fmt.Sprintf("%d", argIdx)
1210+ args = append(args, opts.Host)
1211+ }
1212+
1213+ query := fmt.Sprintf(`
1214+ SELECT path, sum(unique_visits) as total_visits
1215+ FROM analytics_monthly_top_urls
1216+ WHERE user_id = $1 AND month >= $2 AND month < $3 AND status_code = 404 %s
1217+ GROUP BY path
1218+ ORDER BY total_visits DESC
1219+ LIMIT %d`, where, limit)
1220+
1221+ rows, err := me.Db.Queryx(query, args...)
8451222 if err != nil {
8461223 return nil, err
8471224 }
848- defer func() { _ = rs.Close() }()
1225+ defer func() { _ = rows.Close() }()
8491226
850- for rs.Next() {
851- interval := &db.VisitUrl{}
852- err := rs.Scan(
853- &interval.Url,
854- &interval.Count,
855- )
856- if err != nil {
1227+ var results []*db.VisitUrl
1228+ for rows.Next() {
1229+ result := &db.VisitUrl{}
1230+ if err := rows.Scan(&result.Url, &result.Count); err != nil {
8571231 return nil, err
8581232 }
1233+ results = append(results, result)
1234+ }
1235+ return results, rows.Err()
1236+}
8591237
860- intervals = append(intervals, interval)
1238+// visitUrlNotFoundFromRaw reads top 404 URLs from analytics_visits for the previous and current months.
1239+func (me *PsqlDB) visitUrlNotFoundFromRaw(opts *db.SummaryOpts, limit int) ([]*db.VisitUrl, error) {
1240+ now := time.Now()
1241+ currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
1242+ previousMonthStart := currentMonthStart.AddDate(0, -1, 0)
1243+
1244+ where, with := visitFilterBy(opts)
1245+
1246+ // Determine the effective start: max(origin, previousMonthStart)
1247+ effectiveStart := previousMonthStart
1248+ if opts.Origin.After(previousMonthStart) {
1249+ effectiveStart = opts.Origin
8611250 }
862- if rs.Err() != nil {
863- return nil, rs.Err()
1251+
1252+ topUrls := fmt.Sprintf(`
1253+ SELECT
1254+ path,
1255+ count(DISTINCT ip_address) as path_count
1256+ FROM analytics_visits
1257+ WHERE created_at >= $1 AND created_at < $2 AND %s = $3 AND user_id = $4 AND path <> '' AND status = 404
1258+ GROUP BY path
1259+ ORDER BY path_count DESC
1260+ LIMIT %d`, where, limit)
1261+
1262+ rows, err := me.Db.Queryx(topUrls, effectiveStart, currentMonthStart.AddDate(0, 1, 0), with, opts.UserID)
1263+ if err != nil {
1264+ return nil, err
8641265 }
865- return intervals, nil
1266+ defer func() { _ = rows.Close() }()
1267+
1268+ var results []*db.VisitUrl
1269+ for rows.Next() {
1270+ result := &db.VisitUrl{}
1271+ if err := rows.Scan(&result.Url, &result.Count); err != nil {
1272+ return nil, err
1273+ }
1274+ results = append(results, result)
1275+ }
1276+ return results, rows.Err()
8661277 }
8671278
8681279 func (me *PsqlDB) visitHost(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
869- topUrls := `SELECT
870- host,
871- count(DISTINCT ip_address) as host_count
872- FROM analytics_visits
873- WHERE user_id = $1 AND host <> ''
874- GROUP BY host
875- ORDER BY host_count DESC`
1280+ historical, err := me.visitHostFromSummary(opts)
1281+ if err != nil {
1282+ return nil, fmt.Errorf("query summary hosts: %w", err)
1283+ }
1284+
1285+ current, err := me.visitHostFromRaw(opts)
1286+ if err != nil {
1287+ return nil, fmt.Errorf("query raw hosts: %w", err)
1288+ }
1289+
1290+ return mergeHosts(historical, current), nil
1291+}
8761292
877- intervals := []*db.VisitUrl{}
878- rs, err := me.Db.Queryx(topUrls, opts.UserID)
1293+// visitHostFromSummary reads host data from analytics_user_sites for historical data.
1294+func (me *PsqlDB) visitHostFromSummary(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
1295+ rows, err := me.Db.Queryx(`
1296+ SELECT host, total_visits
1297+ FROM analytics_user_sites
1298+ WHERE user_id = $1 AND host <> ''
1299+ ORDER BY total_visits DESC`, opts.UserID)
8791300 if err != nil {
8801301 return nil, err
8811302 }
882- defer func() { _ = rs.Close() }()
1303+ defer func() { _ = rows.Close() }()
8831304
884- for rs.Next() {
885- interval := &db.VisitUrl{}
886- err := rs.Scan(
887- &interval.Url,
888- &interval.Count,
889- )
890- if err != nil {
1305+ var results []*db.VisitUrl
1306+ for rows.Next() {
1307+ result := &db.VisitUrl{}
1308+ if err := rows.Scan(&result.Url, &result.Count); err != nil {
8911309 return nil, err
8921310 }
1311+ results = append(results, result)
1312+ }
1313+ return results, rows.Err()
1314+}
8931315
894- intervals = append(intervals, interval)
1316+// visitHostFromRaw reads hosts from analytics_visits for the current month that aren't in summary.
1317+func (me *PsqlDB) visitHostFromRaw(opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
1318+ now := time.Now()
1319+ currentMonthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, time.UTC)
1320+
1321+ rows, err := me.Db.Queryx(`
1322+ SELECT host, count(DISTINCT ip_address) as host_count
1323+ FROM analytics_visits
1324+ WHERE created_at >= $1 AND user_id = $2 AND host <> ''
1325+ GROUP BY host
1326+ ORDER BY host_count DESC`, currentMonthStart, opts.UserID)
1327+ if err != nil {
1328+ return nil, err
8951329 }
896- if rs.Err() != nil {
897- return nil, rs.Err()
1330+ defer func() { _ = rows.Close() }()
1331+
1332+ var results []*db.VisitUrl
1333+ for rows.Next() {
1334+ result := &db.VisitUrl{}
1335+ if err := rows.Scan(&result.Url, &result.Count); err != nil {
1336+ return nil, err
1337+ }
1338+ results = append(results, result)
8981339 }
899- return intervals, nil
1340+ return results, rows.Err()
9001341 }
9011342
9021343 func (me *PsqlDB) VisitSummary(opts *db.SummaryOpts) (*db.SummaryVisits, error) {
A sql/migrations/20260503_add_analytics_summary_tables.sql
+85, -0
......@@ -0,0 +1,85 @@
1+-- analytics_user_sites: tracks every site a user has had traffic on
2+CREATE TABLE IF NOT EXISTS analytics_user_sites (
3+ id serial NOT NULL,
4+ user_id uuid NOT NULL,
5+ host character varying(253) NOT NULL,
6+ total_visits integer NOT NULL DEFAULT 0,
7+ last_seen date NOT NULL,
8+ created_at timestamp without time zone NOT NULL DEFAULT now(),
9+ updated_at timestamp without time zone NOT NULL DEFAULT now(),
10+ CONSTRAINT analytics_user_sites_pkey PRIMARY KEY (id),
11+ CONSTRAINT analytics_user_sites_unique UNIQUE (user_id, host),
12+ CONSTRAINT fk_user_sites_app_users
13+ FOREIGN KEY(user_id)
14+ REFERENCES app_users(id)
15+ ON DELETE CASCADE
16+ ON UPDATE CASCADE
17+);
18+
19+CREATE INDEX IF NOT EXISTS idx_user_sites_user ON analytics_user_sites (user_id);
20+
21+-- analytics_monthly_visits: daily unique visitor counts per host with device breakdown
22+CREATE TABLE IF NOT EXISTS analytics_monthly_visits (
23+ id serial NOT NULL,
24+ user_id uuid NOT NULL,
25+ host character varying(253) NOT NULL,
26+ visit_date date NOT NULL,
27+ unique_visits integer NOT NULL DEFAULT 0,
28+ mobile_visits integer NOT NULL DEFAULT 0,
29+ desktop_visits integer NOT NULL DEFAULT 0,
30+ created_at timestamp without time zone NOT NULL DEFAULT now(),
31+ CONSTRAINT analytics_monthly_visits_pkey PRIMARY KEY (id),
32+ CONSTRAINT analytics_monthly_visits_unique UNIQUE (user_id, host, visit_date),
33+ CONSTRAINT fk_monthly_visits_app_users
34+ FOREIGN KEY(user_id)
35+ REFERENCES app_users(id)
36+ ON DELETE CASCADE
37+ ON UPDATE CASCADE
38+);
39+
40+CREATE INDEX IF NOT EXISTS idx_monthly_visits_user_host ON analytics_monthly_visits (user_id, host);
41+CREATE INDEX IF NOT EXISTS idx_monthly_visits_user_date ON analytics_monthly_visits (user_id, visit_date DESC);
42+
43+-- analytics_monthly_top_urls: top URLs per host per month per status code
44+CREATE TABLE IF NOT EXISTS analytics_monthly_top_urls (
45+ id serial NOT NULL,
46+ user_id uuid NOT NULL,
47+ host character varying(253) NOT NULL,
48+ month date NOT NULL,
49+ path character varying(2048) NOT NULL,
50+ unique_visits integer NOT NULL DEFAULT 0,
51+ status_code integer NOT NULL,
52+ rank integer NOT NULL,
53+ created_at timestamp without time zone NOT NULL DEFAULT now(),
54+ CONSTRAINT analytics_monthly_top_urls_pkey PRIMARY KEY (id),
55+ CONSTRAINT analytics_monthly_top_urls_unique UNIQUE (user_id, host, month, path, status_code),
56+ CONSTRAINT fk_monthly_top_urls_app_users
57+ FOREIGN KEY(user_id)
58+ REFERENCES app_users(id)
59+ ON DELETE CASCADE
60+ ON UPDATE CASCADE
61+);
62+
63+CREATE INDEX IF NOT EXISTS idx_monthly_top_urls_user_host_month ON analytics_monthly_top_urls (user_id, host, month);
64+CREATE INDEX IF NOT EXISTS idx_monthly_top_urls_status ON analytics_monthly_top_urls (user_id, host, month, status_code, rank);
65+
66+-- analytics_monthly_top_referers: top referers per host per month
67+CREATE TABLE IF NOT EXISTS analytics_monthly_top_referers (
68+ id serial NOT NULL,
69+ user_id uuid NOT NULL,
70+ host character varying(253) NOT NULL,
71+ month date NOT NULL,
72+ referer character varying(253) NOT NULL,
73+ unique_visits integer NOT NULL DEFAULT 0,
74+ rank integer NOT NULL,
75+ created_at timestamp without time zone NOT NULL DEFAULT now(),
76+ CONSTRAINT analytics_monthly_top_referers_pkey PRIMARY KEY (id),
77+ CONSTRAINT analytics_monthly_top_referers_unique UNIQUE (user_id, host, month, referer),
78+ CONSTRAINT fk_monthly_top_referers_app_users
79+ FOREIGN KEY(user_id)
80+ REFERENCES app_users(id)
81+ ON DELETE CASCADE
82+ ON UPDATE CASCADE
83+);
84+
85+CREATE INDEX IF NOT EXISTS idx_monthly_referers_user_host_month ON analytics_monthly_top_referers (user_id, host, month);