1d9287a199
The security review of 39d874a (FRE-369) found the address fix recorded
the wrong thing twice. Both are fixed here; the branch is the same one
under review, this commit does not rewrite it.
Q1 — the first x-forwarded-for hop is attacker-chosen text. In
client → Cloudflare → same-host proxy → app, both proxies *append*, so
the header arrives as `<client spoof…>, <true client>, <CF edge>` and
`split(",")[0]` took the sender's own invention — the loopback-peer gate
was sound, the hop selection defeated it. Preference is now
`cf-connecting-ip` (Cloudflare overwrites it, so a client value never
survives the edge), then x-forwarded-for counted from the right with one
trusted hop discounted, then the direct peer. Every candidate must parse
as an address. h3's `getRequestIP(…, { xForwardedFor: true })` has the
same leftmost flaw and is documented as not a fix.
Q4 — the table held full client addresses indefinitely. Every analytics
entry now passes through `truncateIpAddress` in `enrichAnalyticsEntry`
(IPv4 /24, IPv6 /56, unparseable dropped), so no writer can persist a
full address by accident. Retention got the schedule it never had:
`/api/analytics/retention` on a daily Vercel cron, Bearer-authenticated
and fail-closed, with the window parameterised (the old
`cleanupOldAnalytics` spliced the number into the SQL). Default 365 days
rather than the review's 90 so enabling the cron is not itself a
destructive event — the tighter window is an operational decision, named
on the task.
Also from the review: the module doc now forbids feeding this helper into
any security decision; `isLoopbackAddress` parses instead of
string-matching (`::ffff:127.9.9.9`, `0:0:0:0:0:0:0:1`); and `host` is
normalised (lowercased, port stripped) so `nook.freno.me:443` cannot
silently miss the `host = ?` scope. The weekly loopback caveat matches
the truncated shapes writers produce now, and says plainly that "unique
visitors" counts prefixes.
44 lines
2.2 KiB
Markdown
44 lines
2.2 KiB
Markdown
# Weekly funnel snapshot
|
||
|
||
Window: **2026-10-22 18:05:00 → 2026-10-29 18:05:00 UTC** (7 days). Previous: 2026-10-15 18:05:00 → 2026-10-22 18:05:00 UTC.
|
||
|
||
Generated by `bun --env-file=.env scripts/weekly-metrics.ts`; regenerated weekly, never hand-edited. The analysis is the CMO's.
|
||
|
||
## Funnel
|
||
|
||
| Stage | This week | Last week | Change |
|
||
|---|---:|---:|---:|
|
||
| visit (nook.freno.me page views) | 0 | 0 | 0 |
|
||
| download click | 0 | 0 | 0 |
|
||
| DMG served | 0 | 0 | 0 |
|
||
| trial started | 2 | 0 | +2 |
|
||
| checkout started (Stripe session) | 0 | 0 | 0 |
|
||
| purchase (licence issued) | 0 | 0 | 0 |
|
||
| activation | 0 | 0 | 0 |
|
||
|
||
## Campaigns
|
||
|
||
Top 10 by volume, untagged excluded — the same grouping `getAnalyticsSummary.topCampaigns` returns.
|
||
|
||
| Source | Medium | Campaign | Rows |
|
||
|---|---|---|---:|
|
||
| x | — | — | 1 |
|
||
|
||
1 of 291 rows in the window carry campaign tags; the rest are untagged and therefore unattributed.
|
||
|
||
## The one thing that moved
|
||
|
||
**trial started: 0 → 2 (+2).**
|
||
|
||
## Before acting on these numbers
|
||
|
||
- **291 of 291 rows in the window predate the FRE-366 instrumentation.** They carry no host, and their recorded address is the production proxy's loopback (127.0.0.1 / ::1) — the writers then recorded the direct peer, not the visitor. They are excluded from every host-scoped stage above (visits, and the download click/served split), and "unique visitors" over them still collapses to one host. Rows recorded since carry the site they addressed and the visitor's address truncated to a prefix (/24, or /56 for IPv6 — the FRE-369 privacy floor), so "unique visitors" counts networks, not machines.
|
||
- **Download rows are automation-heavy.** Of this window's 137 download rows, 0 are clicks and 0 are served files; the remaining 137 predate the click/served stage label and cannot be split, so they count in neither stage. 112 of all 137 are not browser user agents (curl, Sparkle, link-preview crawlers).
|
||
- **No conversion rate before the cohort matures.** Trial→paid belongs to the mature Day 28–35 read (FRE-197); a ratio from a partial cohort is not a metric.
|
||
|
||
## Sources
|
||
|
||
- `VisitorAnalytics` (freno.me Turso DB) — visits, downloads, campaigns.
|
||
- `trials`, `licenses`, `activations` (the-nook Turso DB) — the paid funnel.
|
||
- Generated 2026-10-29T18:05:00Z.
|