chore: remove dev artifacts

Commit `dc6ed61`

Eric Bower · 2026-05-04 23:37:38 -0400 EDT

parent 23438de

chore: remove dev artifacts

2 files changed, +0, -642

D cmd/scripts/compare-analytics/main.go

D docs/analytics-retention-proposal.md

D cmd/scripts/compare-analytics/main.go

+0, -306

......@@ -1,306 +0,0 @@
package main

import (
	"flag"
	"fmt"
	"log/slog"
	"os"
	"time"

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

func main() {
	host := flag.String("host", "", "host to compare (required)")
	userID := flag.String("user-id", "", "user ID to compare (required)")
	interval := flag.String("interval", "day", "interval: day, week, month")
	origin := flag.String("origin", "", "origin date in YYYY-MM-DD format (default: start of year)")
	flag.Parse()

	if *host == "" || *userID == "" {
		fmt.Fprintln(os.Stderr, "usage: compare-analytics -host example.com -user-id <uuid> [-interval day|week|month] [-origin 2025-01-01]")
		os.Exit(1)
	}

	logger := slog.Default()
	dbURL := os.Getenv("DATABASE_URL")
	if dbURL == "" {
		fmt.Fprintln(os.Stderr, "DATABASE_URL must be set")
		os.Exit(1)
	}

	dbpool := postgres.NewDB(dbURL, logger)
	defer func() { _ = dbpool.Close() }()

	originTime := time.Date(time.Now().AddDate(-1, 0, 0).Year(), time.January, 1, 0, 0, 0, 0, time.UTC)
	if *origin != "" {
		var err error
		originTime, err = time.Parse("2006-01-02", *origin)
		if err != nil {
			logger.Error("invalid origin format, use YYYY-MM-DD", "err", err)
			os.Exit(1)
		}
	}

	opts := &db.SummaryOpts{
		Host:     *host,
		UserID:   *userID,
		Interval: *interval,
		Origin:   originTime,
	}

	// New path: reads from summary tables + current month raw
	newSummary, err := dbpool.VisitSummary(opts)
	if err != nil {
		logger.Error("VisitSummary failed", "err", err)
		os.Exit(1)
	}

	// Old path: direct queries against analytics_visits
	oldSummary, err := queryRawVisits(dbpool, opts)
	if err != nil {
		logger.Error("raw query failed", "err", err)
		os.Exit(1)
	}

	// Compare
	fmt.Println("=== Unique Visitors (Intervals) ===")
	compareIntervals(newSummary.Intervals, oldSummary.Intervals)

	fmt.Println("\n=== Top URLs ===")
	compareUrls(newSummary.TopUrls, oldSummary.TopUrls)

	fmt.Println("\n=== Top Referers ===")
	compareUrls(newSummary.TopReferers, oldSummary.TopReferers)

	fmt.Println("\n=== 404 URLs ===")
	compareUrls(newSummary.NotFoundUrls, oldSummary.NotFoundUrls)
}

func queryRawVisits(dbpool *postgres.PsqlDB, opts *db.SummaryOpts) (*db.SummaryVisits, error) {
	intervals, err := rawVisitUnique(dbpool, opts)
	if err != nil {
		return nil, fmt.Errorf("raw visit unique: %w", err)
	}
	urls, err := rawVisitUrl(dbpool, opts)
	if err != nil {
		return nil, fmt.Errorf("raw visit url: %w", err)
	}
	refs, err := rawVisitReferer(dbpool, opts)
	if err != nil {
		return nil, fmt.Errorf("raw visit referer: %w", err)
	}
	notFound, err := rawVisitUrlNotFound(dbpool, opts)
	if err != nil {
		return nil, fmt.Errorf("raw visit url not found: %w", err)
	}
	return &db.SummaryVisits{
		Intervals:    intervals,
		TopUrls:      urls,
		TopReferers:  refs,
		NotFoundUrls: notFound,
	}, nil
}

func rawVisitUnique(dbpool *postgres.PsqlDB, opts *db.SummaryOpts) ([]*db.VisitInterval, error) {
	where, with := visitFilterBy(opts)
	query := fmt.Sprintf(`
		SELECT date_trunc('%s', created_at)::timestamptz as interval_start,
		       count(DISTINCT ip_address) as unique_visitors
		FROM analytics_visits
		WHERE created_at >= $1 AND %s = $2 AND user_id = $3 AND status <> 404
		GROUP BY interval_start
		ORDER BY interval_start`, opts.Interval, where)

	rows, err := dbpool.Db.Queryx(query, opts.Origin, with, opts.UserID)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var intervals []*db.VisitInterval
	for rows.Next() {
		iv := &db.VisitInterval{}
		if err := rows.Scan(&iv.Interval, &iv.Visitors); err != nil {
			return nil, err
		}
		intervals = append(intervals, iv)
	}
	return intervals, rows.Err()
}

func rawVisitUrl(dbpool *postgres.PsqlDB, opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
	where, with := visitFilterBy(opts)
	query := fmt.Sprintf(`
		SELECT path as url, count(DISTINCT ip_address) as count
		FROM analytics_visits
		WHERE created_at >= $1 AND %s = $2 AND user_id = $3 AND path <> '' AND status <> 404
		GROUP BY path
		ORDER BY count DESC
		LIMIT 10`, where)

	rows, err := dbpool.Db.Queryx(query, opts.Origin, with, opts.UserID)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var urls []*db.VisitUrl
	for rows.Next() {
		u := &db.VisitUrl{}
		if err := rows.Scan(&u.Url, &u.Count); err != nil {
			return nil, err
		}
		urls = append(urls, u)
	}
	return urls, rows.Err()
}

func rawVisitReferer(dbpool *postgres.PsqlDB, opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
	where, with := visitFilterBy(opts)
	query := fmt.Sprintf(`
		SELECT referer as url, count(DISTINCT ip_address) as count
		FROM analytics_visits
		WHERE created_at >= $1 AND %s = $2 AND user_id = $3 AND referer <> '' AND status <> 404
		GROUP BY referer
		ORDER BY count DESC
		LIMIT 10`, where)

	rows, err := dbpool.Db.Queryx(query, opts.Origin, with, opts.UserID)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var urls []*db.VisitUrl
	for rows.Next() {
		u := &db.VisitUrl{}
		if err := rows.Scan(&u.Url, &u.Count); err != nil {
			return nil, err
		}
		urls = append(urls, u)
	}
	return urls, rows.Err()
}

func rawVisitUrlNotFound(dbpool *postgres.PsqlDB, opts *db.SummaryOpts) ([]*db.VisitUrl, error) {
	where, with := visitFilterBy(opts)
	limit := opts.Limit
	if limit == 0 {
		limit = 10
	}
	query := fmt.Sprintf(`
		SELECT path as url, count(DISTINCT ip_address) as count
		FROM analytics_visits
		WHERE created_at >= $1 AND %s = $2 AND user_id = $3 AND path <> '' AND status = 404
		GROUP BY path
		ORDER BY count DESC
		LIMIT %d`, where, limit)

	rows, err := dbpool.Db.Queryx(query, opts.Origin, with, opts.UserID)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var urls []*db.VisitUrl
	for rows.Next() {
		u := &db.VisitUrl{}
		if err := rows.Scan(&u.Url, &u.Count); err != nil {
			return nil, err
		}
		urls = append(urls, u)
	}
	return urls, rows.Err()
}

func visitFilterBy(opts *db.SummaryOpts) (string, string) {
	if opts.Host != "" {
		return "host", opts.Host
	}
	if opts.Path != "" {
		return "path", opts.Path
	}
	return "host", ""
}

func compareIntervals(newData, oldData []*db.VisitInterval) {
	newMap := make(map[string]int)
	for _, iv := range newData {
		key := iv.Interval.Format("2006-01-02")
		newMap[key] = iv.Visitors
	}
	oldMap := make(map[string]int)
	for _, iv := range oldData {
		key := iv.Interval.Format("2006-01-02")
		oldMap[key] = iv.Visitors
	}

	// Collect all keys
	keys := make(map[string]bool)
	for k := range newMap {
		keys[k] = true
	}
	for k := range oldMap {
		keys[k] = true
	}

	mismatches := 0
	for k := range keys {
		n, nOk := newMap[k]
		o, oOk := oldMap[k]
		if nOk && oOk {
			if n != o {
				fmt.Printf("  MISMATCH %s: new=%d old=%d\n", k, n, o)
				mismatches++
			}
		} else if nOk {
			fmt.Printf("  NEW ONLY %s: %d\n", k, n)
			mismatches++
		} else {
			fmt.Printf("  OLD ONLY %s: %d\n", k, o)
			mismatches++
		}
	}
	if mismatches == 0 {
		fmt.Println("  OK: all intervals match")
	} else {
		fmt.Printf("  %d mismatches\n", mismatches)
	}
}

func compareUrls(newData, oldData []*db.VisitUrl) {
	newMap := make(map[string]int)
	for _, u := range newData {
		newMap[u.Url] = u.Count
	}
	oldMap := make(map[string]int)
	for _, u := range oldData {
		oldMap[u.Url] = u.Count
	}

	mismatches := 0
	for url, nCount := range newMap {
		if oCount, ok := oldMap[url]; ok {
			if nCount != oCount {
				fmt.Printf("  MISMATCH %s: new=%d old=%d\n", url, nCount, oCount)
				mismatches++
			}
		} else {
			fmt.Printf("  NEW ONLY %s: %d\n", url, nCount)
			mismatches++
		}
	}
	for url, oCount := range oldMap {
		if _, ok := newMap[url]; !ok {
			fmt.Printf("  OLD ONLY %s: %d\n", url, oCount)
			mismatches++
		}
	}
	if mismatches == 0 {
		fmt.Println("  OK: all entries match")
	} else {
		fmt.Printf("  %d mismatches\n", mismatches)
	}
}

D docs/analytics-retention-proposal.md

+0, -336

......@@ -1,336 +0,0 @@
# Analytics Data Retention Proposal

## Problem

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.

## Goal

- Keep `analytics_visits` small by deleting raw data older than **1 month**
- Pre-aggregate monthly stats into summary tables so historical queries remain fast
- Preserve all data the user currently sees in the TUI and SSH CLI

---

## What We Display Today

### TUI Analytics Screen (`pkg/tui/analytics.go`)

When a user views analytics for a site, we show:

| Section | Query | Aggregation |
|---------|-------|-------------|
| **Site list** (left pane) | `visitHost()` | `COUNT(DISTINCT ip_address)` grouped by `host` |
| **Visits by period** | `visitUnique()` | `COUNT(DISTINCT ip_address)` grouped by `date_trunc(interval, created_at)` |
| **Top URLs** | `visitUrl()` | `COUNT(DISTINCT ip_address)` grouped by `path`, LIMIT 10, excludes 404s |
| **Top Referers** | `visitReferer()` | `COUNT(DISTINCT ip_address)` grouped by `referer`, LIMIT 10, excludes 404s |
| **Top 404s** | `VisitUrlNotFound()` | `COUNT(DISTINCT ip_address)` grouped by `path`, LIMIT 10, status=404 |

The TUI supports two intervals:
- **"day"** — daily buckets from start of current month (`StartOfMonth()`)
- **"month"** — monthly buckets from 1 year ago (`StartOfYear()` which is `now - 1 year`)

### SSH CLI (`pkg/apps/pico/cli.go`)

| Command | Query | Aggregation |
|---------|-------|-------------|
| `not-found hostname.com [year\|month]` | `VisitUrlNotFound()` | `COUNT(DISTINCT ip_address)` grouped by `path`, LIMIT 100, status=404 |

---

## Proposed Schema

### New Table: `analytics_user_sites`

Tracks every site a user has had traffic on. Powers the site list in the left pane of the TUI.

```sql
CREATE TABLE analytics_user_sites (
    id              SERIAL PRIMARY KEY,
    user_id         UUID NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
    host            VARCHAR(253) NOT NULL,
    total_visits    INT NOT NULL DEFAULT 0,       -- running total of all-time unique visitors
    last_seen       DATE NOT NULL,                 -- most recent month with traffic
    created_at      TIMESTAMP NOT NULL DEFAULT now(),
    updated_at      TIMESTAMP NOT NULL DEFAULT now(),

    UNIQUE (user_id, host)
);

CREATE INDEX idx_user_sites_user ON analytics_user_sites (user_id);
```

**How it's populated:** During monthly aggregation, for each `(user_id, host)` pair found in the previous month's raw data:
- `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.

**Query replacement:** `visitHost()` becomes:
```sql
SELECT host, total_visits as host_count
FROM analytics_user_sites
WHERE user_id = $1
ORDER BY total_visits DESC
```

No GROUP BY, no scan of raw data — just an indexed lookup.

### New Table: `analytics_monthly_visits`

Stores pre-aggregated daily unique visitor counts per host. This powers the "visits by period" chart and site list.

```sql
CREATE TABLE analytics_monthly_visits (
    id              SERIAL PRIMARY KEY,
    user_id         UUID NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
    host            VARCHAR(253) NOT NULL,
    visit_date      DATE NOT NULL,              -- the day this count is for
    unique_visits   INT NOT NULL DEFAULT 0,     -- COUNT(DISTINCT ip_address) for that day
    mobile_visits   INT NOT NULL DEFAULT 0,     -- unique visitors from mobile user-agents
    desktop_visits  INT NOT NULL DEFAULT 0,     -- unique visitors from desktop user-agents
    created_at      TIMESTAMP NOT NULL DEFAULT now(),

    UNIQUE (user_id, host, visit_date)
);

CREATE INDEX idx_monthly_visits_user_host ON analytics_monthly_visits (user_id, host);
CREATE INDEX idx_monthly_visits_user_date ON analytics_monthly_visits (user_id, visit_date DESC);
```

**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)`.

**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.

### New Table: `analytics_monthly_top_urls`

Stores the top URLs per host per month. Powers "Top URLs" and "Top 404s" sections.

```sql
CREATE TABLE analytics_monthly_top_urls (
    id            SERIAL PRIMARY KEY,
    user_id       UUID NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
    host          VARCHAR(253) NOT NULL,
    month         DATE NOT NULL,           -- first day of the month (e.g., 2025-01-01)
    path          VARCHAR(2048) NOT NULL,
    unique_visits INT NOT NULL DEFAULT 0,  -- COUNT(DISTINCT ip_address) for that path
    is_404        BOOLEAN NOT NULL DEFAULT false,
    rank          INT NOT NULL,            -- 1-10 (top 10) or 1-100 for CLI
    created_at    TIMESTAMP NOT NULL DEFAULT now(),

    UNIQUE (user_id, host, month, path, is_404)
);

CREATE INDEX idx_monthly_top_urls_user_host_month ON analytics_monthly_top_urls (user_id, host, month);
CREATE INDEX idx_monthly_top_urls_404 ON analytics_monthly_top_urls (user_id, host, month, is_404, rank);
```

### New Table: `analytics_monthly_top_referers`

Stores top referers per host per month. Powers "Top Referers" section.

```sql
CREATE TABLE analytics_monthly_top_referers (
    id            SERIAL PRIMARY KEY,
    user_id       UUID NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
    host          VARCHAR(253) NOT NULL,
    month         DATE NOT NULL,           -- first day of the month
    referer       VARCHAR(253) NOT NULL,
    unique_visits INT NOT NULL DEFAULT 0,
    rank          INT NOT NULL,            -- 1-10
    created_at    TIMESTAMP NOT NULL DEFAULT now(),

    UNIQUE (user_id, host, month, referer)
);

CREATE INDEX idx_monthly_referers_user_host_month ON analytics_monthly_top_referers (user_id, host, month);
```

---

## Monthly Aggregation Job

A script (e.g., `cmd/scripts/analytics-aggregate/main.go`) runs **once per month** (e.g., on the 1st). It:

### Step 1: Aggregate the previous month

For each user with the `analytics` feature flag who has raw visits in the previous month:

```
FOR each (user_id, host) pair in analytics_visits WHERE created_at IN previous_month:
    -- Daily unique visitors (with device breakdown)
    -- Device detection runs in Go (user-agent parsing), not pure SQL.
    -- Pseudo-SQL for clarity:
    INSERT INTO analytics_monthly_visits (user_id, host, visit_date, unique_visits, mobile_visits, desktop_visits)
    SELECT user_id, host, date_trunc('day', created_at)::date,
           COUNT(DISTINCT ip_address),
           COUNT(DISTINCT CASE WHEN is_mobile(user_agent) THEN ip_address END),
           COUNT(DISTINCT CASE WHEN NOT is_mobile(user_agent) THEN ip_address END)
    FROM analytics_visits
    WHERE user_id = $1 AND host = $2 AND created_at >= start_of_month AND created_at < start_of_next_month
    GROUP BY user_id, host, date_trunc('day', created_at)
    ON CONFLICT (user_id, host, visit_date) DO NOTHING;

    -- In practice, `is_mobile()` is a Go function. The aggregation job fetches
    -- raw rows in batches, classifies each user_agent, and issues INSERTs.

    -- Top 10 URLs (non-404)
    INSERT INTO analytics_monthly_top_urls (user_id, host, month, path, unique_visits, is_404, rank)
    SELECT user_id, host, date_trunc('month', created_at)::date, path, COUNT(DISTINCT ip_address), false,
           ROW_NUMBER() OVER (PARTITION BY user_id, host ORDER BY COUNT(DISTINCT ip_address) DESC)
    FROM analytics_visits
    WHERE user_id = $1 AND host = $2 AND created_at >= start_of_month AND created_at < start_of_next_month AND status <> 404
    GROUP BY user_id, host, path
    HAVING ROW_NUMBER() <= 10
    ON CONFLICT (user_id, host, month, path, is_404) DO NOTHING;

    -- Top 10 URLs (404s)
    INSERT INTO analytics_monthly_top_urls (user_id, host, month, path, unique_visits, is_404, rank)
    SELECT user_id, host, date_trunc('month', created_at)::date, path, COUNT(DISTINCT ip_address), true,
           ROW_NUMBER() OVER (PARTITION BY user_id, host ORDER BY COUNT(DISTINCT ip_address) DESC)
    FROM analytics_visits
    WHERE user_id = $1 AND host = $2 AND created_at >= start_of_month AND created_at < start_of_next_month AND status = 404
    GROUP BY user_id, host, path
    HAVING ROW_NUMBER() <= 100   -- 100 for CLI, 10 for TUI (use 100 to cover both)
    ON CONFLICT (user_id, host, month, path, is_404) DO NOTHING;

    -- Top 10 referers
    INSERT INTO analytics_monthly_top_referers (user_id, host, month, referer, unique_visits, rank)
    SELECT user_id, host, date_trunc('month', created_at)::date, referer, COUNT(DISTINCT ip_address),
           ROW_NUMBER() OVER (PARTITION BY user_id, host ORDER BY COUNT(DISTINCT ip_address) DESC)
    FROM analytics_visits
    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
    GROUP BY user_id, host, referer
    HAVING ROW_NUMBER() <= 10
    ON CONFLICT (user_id, host, month, referer) DO NOTHING;
```

### Step 2: Delete raw data older than 1 month

```sql
DELETE FROM analytics_visits
WHERE created_at < date_trunc('month', now())::timestamp;
```

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).

### Step 3: Backfill (one-time)

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.

---

## Query Changes

### Current queries → New queries

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.

#### `visitUnique()` — visits by period

```
-- For "day" interval (current month only, always from raw):
-- No change — reads from analytics_visits

-- For "month" interval (historical):
-- Read from analytics_monthly_visits, sum daily counts per month:
SELECT visit_date::date as interval_start, SUM(unique_visits) as visitors
FROM analytics_monthly_visits
WHERE user_id = $1 AND host = $2 AND visit_date >= $3
GROUP BY date_trunc('month', visit_date), visit_date
ORDER BY visit_date
```

#### `visitUrl()` — top URLs

```
-- Current month: read from analytics_visits (no change)
-- Historical months: read from analytics_monthly_top_urls WHERE is_404 = false
```

#### `visitReferer()` — top referers

```
-- Current month: read from analytics_visits (no change)  
-- Historical months: read from analytics_monthly_top_referers
```

#### `VisitUrlNotFound()` — top 404s

```
-- Current month: read from analytics_visits (no change)
-- Historical months: read from analytics_monthly_top_urls WHERE is_404 = true
```

#### `visitHost()` — site list

```
-- Replaced entirely by analytics_user_sites lookup:
SELECT host, total_visits as host_count
FROM analytics_user_sites
WHERE user_id = $1
ORDER BY total_visits DESC
```

No raw table scan. Current month's new hosts are upserted during the monthly aggregation job.

---

## Implementation Phases

### Phase 1: Schema + Backfill (no behavior change)
1. Create the four new tables (user_sites, monthly_visits, monthly_top_urls, monthly_top_referers)
2. Write the aggregation script (`cmd/scripts/analytics-aggregate/`)
3. Run backfill for all historical data
4. **Do not delete any data yet** — verify summary tables are correct

### Phase 2: Query migration
1. Add new DB interface methods that read from summary tables
2. Modify existing query methods to union summary + raw data
3. Verify TUI and CLI output matches before/after
4. Run `make check`

### Phase 3: Enable deletion
1. Add raw data deletion to the aggregation job
2. Schedule the job to run monthly (cron, systemd timer, or app-level scheduler)
3. Monitor `analytics_visits` row count after first deletion

### Phase 4: Cleanup
1. Remove any code paths that no longer need raw historical data
2. Consider dropping unused indexes on `analytics_visits` since the table is now small

---

## Trade-offs

| Aspect | Before | After |
|--------|--------|-------|
| `analytics_visits` size | 18M+ rows (unbounded) | ~1 month of data (~150K-500K) |
| Query latency | Scans full table each time | Reads small table or pre-aggregated rows |
| Historical granularity | Full raw data | Monthly top-10/100 summaries |
| Disk overhead | Single table | 4 additional tables (user_sites, monthly_visits, monthly_top_urls, monthly_top_referers) |
| Mobile/desktop | Not tracked | Tracked via user-agent classification during aggregation |
| Data loss | None | Raw data older than 1 month is gone |

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.

---

## Open Questions

1. **Scheduling**: How do we run the monthly job? Options:
   - Cron job on the server
   - `systemd` timer
   - Built into one of our services as a startup check
   - External scheduler (GitHub Actions, etc.)

2. **`visitHost()` (site list)**: Solved by `analytics_user_sites` table — simple indexed lookup, no aggregation needed.

3. **Retention period**: Is 1 month the right window for raw data? Could adjust based on observed query patterns.

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).

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.

6. **Device detection library**: Which Go user-agent parser to use for mobile/desktop classification? Candidates:
   - [`mssola/useragent`](https://github.com/mssola/useragent) — lightweight, device.OS / device.Type
   - [`pdericx/ua-parser`](https://github.com/pdericx/ua-parser) — fuller UAParser port
   - [`danielgtaylor/mobile`](https://github.com/danielgtaylor/mobile) — simple mobile-only detection
   The existing codebase already uses `x-way/crawlerdetect` for bot filtering, so a similar pattern fits.

...	...		@@ -1,306 +0,0 @@
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		-	}

...	...		@@ -1,336 +0,0 @@
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.

Commit dc6ed61

Commit `dc6ed61`