Self-hosted Analytics with Next.js and PostgreSQL

For a long time I used Plausible Analytics to track pageviews on this site. It is a great product, but I was already running a PostgreSQL instance for comments, webmentions and location data. Adding another service just for analytics felt unnecessary. So I built my own.

The result is a simple, self-hosted analytics system built entirely on Next.js API routes and PostgreSQL. No cookies, no third-party scripts, no vendor lock-in. The data that gets stored is genuinely anonymous, so no consent banner is needed. All collected data is displayed publicly on /about-this-site.

The database schema

Everything is stored as a single aggregate counter:

sqlCopy code
CREATE TABLE pageviews (
  path TEXT NOT NULL,
  referrer TEXT NOT NULL DEFAULT '',
  country VARCHAR(2) NOT NULL DEFAULT 'XX',
  device_type VARCHAR(20) NOT NULL DEFAULT 'unknown',
  day DATE NOT NULL DEFAULT CURRENT_DATE,
  views INT NOT NULL DEFAULT 0,
  PRIMARY KEY (path, referrer, country, device_type, day)
);

CREATE INDEX pageviews_day_idx ON pageviews (day);
CREATE INDEX pageviews_path_idx ON pageviews (path);
CREATE INDEX pageviews_country_idx ON pageviews (country);
CREATE INDEX pageviews_referrer_idx ON pageviews (referrer);

There is no row per pageview, only one row per (path, referrer, country, device_type, day) combination with a counter. There are no sessions, no visitor identifiers, no IP addresses, no fingerprints.

The path keeps its query string so I can see which links from newsletters or campaigns drive traffic (?utm_source=...). The referrer is reduced to the hostname only, lowercased and stripped of a leading www., so www.google.com and google.com end up in the same bucket. Self-referrals and direct visits are stored as an empty string.

Tracking pageviews from the client

The tracking component is a small React component included in the root layout:

tsxCopy code
"use client";

import { usePathname } from "next/navigation";
import { useEffect, useRef } from "react";

export default function PageviewTracker() {
  const pathname = usePathname();
  const lastTrackedPath = useRef<string | null>(null);

  useEffect(() => {
    const search = typeof window !== "undefined" ? window.location.search : "";
    const fullPath = `${pathname}${search}`;

    if (fullPath === lastTrackedPath.current) return;
    if (localStorage.getItem("notrack") === "1") return;
    lastTrackedPath.current = fullPath;

    const payload = JSON.stringify({
      path: fullPath,
      referrer: document.referrer || null,
    });

    if (navigator.sendBeacon) {
      const blob = new Blob([payload], { type: "application/json" });
      navigator.sendBeacon("/api/pageview", blob);
    } else {
      fetch("/api/pageview", {
        method: "POST",
        headers: { "Content-Type": "application/json" },
        body: payload,
        keepalive: true,
      }).catch(() => {});
    }
  }, [pathname]);

  return null;
}

usePathname detects route changes in the Next.js App Router so the tracker fires on every navigation. The query string is read straight from window.location.search inside the effect rather than via useSearchParams, which avoids forcing a Suspense boundary during static prerendering of routes like /_not-found. The path keeps its query string so attribution links from newsletters or campaigns are visible. navigator.sendBeacon is preferred over fetch because it is non-blocking and survives page unloads reliably. The notrack localStorage flag is the opt-out and short-circuits the request entirely.

The pageview API route

The route at /api/pageview does a few things before incrementing the counter:

Rate limiting rejects more than 30 requests per minute from the same IP. The limiter is a plain Map in memory. No Redis, no external dependency.

The client IP is read from x-real-ip first (set by nginx), falling back to the last entry in x-forwarded-for. Using the last entry rather than the first prevents clients from spoofing the header.

Same-origin validation rejects requests that did not originate from the site by parsing the Origin or Referer header and comparing the host against the request Host. Exact host matching prevents evil-mxd.codes from passing a substring check.

Bot filtering tests the User-Agent against a regex of known crawler patterns before doing any database work.

Country lookup runs the visitor's IP through the MaxMind GeoLite2 City database. The .mmdb file is loaded once on the first request, cached in memory via @maxmind/geoip2-node, and reused for every request with no external API call. Only the two-letter ISO country code is read and returned.

Device classification is a simple regex against the User-Agent that returns desktop, mobile or tablet. Three buckets, nothing more.

Counter upsert writes everything in a single statement:

sqlCopy code
INSERT INTO pageviews (path, referrer, country, device_type, day, views)
VALUES ($1, $2, $3, $4, CURRENT_DATE, 1)
ON CONFLICT (path, referrer, country, device_type, day) DO UPDATE
SET views = pageviews.views + 1;

The IP and User-Agent never leave the request handler. After the row is upserted the request returns 204 and the in-memory values are garbage collected.

Querying the data

All queries live in src/lib/analytics.ts and read from pageviews:

• getAnalyticsStats() returns the total view count
• getTopPages(limit) groups by path and orders by views
• getTopReferrers(limit) groups by referrer host
• getTopCountries(limit) groups by country
• getDeviceBreakdown() groups by device type
• getPageviewsOverTime(days) returns daily totals for the sparkline chart

Each query aggregates across the dimensions you do not group by, so the table is the only source needed for every breakdown on the dashboard.

The /api/country-stats endpoint exposes the country totals as JSON for the choropleth map and caches the result for 5 minutes using unstable_cache from Next.js.

Rendering the dashboard

Everything is displayed at /about-this-site, a server-rendered page with dynamic = "force-dynamic" so it always shows fresh data.

The 30-day pageview trend is rendered as an SVG sparkline chart built without any charting library:

tsxCopy code
const w = 400;
const h = 80;
const padding = 4;
const range = maxViews - minViews || 1;

const coords = data.map((d, i) => {
  const x = (i / (data.length - 1)) * (w - padding * 2) + padding;
  const y = h - padding - ((d.views - minViews) / range) * (h - padding * 2);
  return { x, y };
});

const polyline = coords.map((c) => `${c.x},${c.y}`).join(" ");
const areaPath = `M${coords[0].x},${coords[0].y} ${coords
  .slice(1)
  .map((c) => `L${c.x},${c.y}`)
  .join(" ")} L${w - padding},${h - padding} L${padding},${h - padding} Z`;

Normalizing against the range rather than the absolute max keeps the chart readable on low-traffic days. Colors come from the CSS custom property --secondary-color so the chart respects the site's light and dark theme automatically. Here is the live chart for this site:

The visitor map is now a choropleth rendered with MapLibre GL JS using a static Natural Earth country GeoJSON. Countries are filled with a ColorBrewer Blues 5-class palette based on their total view count and the legend explains the buckets. The map joins the GeoJSON polygons with the totals from /api/country-stats by ISO 3166-1 alpha-2 code.

The device breakdown and top countries list are simple HTML rows with inline percentage bars rendered with CSS width set proportionally to the maximum value in each group.

Privacy

The data this system stores cannot be linked back to any individual:

• No cookies, no localStorage tracking (except the optional notrack opt-out)
• No visitor identifier of any kind
• No IP address, no User-Agent, no browser version, no operating system, no language, no screen size, no location more precise than country
• The full IP and User-Agent are read in memory only and immediately discarded after the country and device class are derived
• All data is aggregated rather than stored per-request

Because the data is aggregate and contains no identifiers there is no consent banner, no retention period and no legal basis required for processing. If you still prefer not to be counted you can opt out at /api/notrack.

The full dashboard is publicly visible at /about-this-site, which I think is a reasonable trade-off: if I am collecting any data at all, I should be transparent about what it shows.