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. All collected data is displayed publicly on /about-this-site.

The database schema

Everything is stored in a single pageviews table:

sqlCopy code
CREATE TABLE IF NOT EXISTS pageviews (
  id SERIAL PRIMARY KEY,
  path TEXT NOT NULL,
  referrer TEXT,
  visitor_hash TEXT NOT NULL,
  country TEXT,
  city TEXT,
  latitude REAL,
  longitude REAL,
  user_agent TEXT,
  device_type TEXT,
  browser TEXT,
  os TEXT,
  language TEXT,
  screen_width INT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Each row represents a single pageview. There are no sessions, no persistent user IDs and no cookies. Unique visitors are identified by a daily rotating hash described below.

To keep queries fast I added partial indexes on the columns used most often:

sqlCopy code
CREATE INDEX ON pageviews (created_at);
CREATE INDEX ON pageviews (path);
CREATE INDEX ON pageviews (visitor_hash, created_at);
CREATE INDEX ON pageviews (latitude, longitude) WHERE latitude IS NOT NULL AND longitude IS NOT NULL;

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(() => {
    if (pathname === lastTrackedPath.current) return;
    if (localStorage.getItem("notrack") === "1") return;
    lastTrackedPath.current = pathname;

    const payload = JSON.stringify({
      path: pathname,
      referrer: document.referrer || null,
      screenWidth: window.screen?.width || 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;
}

It uses usePathname to detect route changes in the Next.js App Router and fires on every navigation. navigator.sendBeacon is preferred over fetch because it is non-blocking and survives page unloads reliably.

The pageview API route

The API route at /api/pageview handles each incoming event. It does several things before writing to the database:

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

typescriptCopy code
const rateLimitMap = new Map<string, { count: number; resetTime: number }>();

if (!entry || now > entry.resetTime) {
  rateLimitMap.set(ip, { count: 1, resetTime: now + interval });
  return { success: true, remaining: limit - 1 };
}
if (entry.count >= limit) {
  return { success: false, remaining: 0 };
}

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 by prepending a fake IP.

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

Same-origin validation rejects requests that did not originate from the site. It parses the Origin or Referer header with new URL() and compares the host against the request Host header. A simple substring check would allow evil-mxd.codes to pass, so exact host matching is important. Requests with no Origin or Referer are allowed through. Those are same-site form submissions or direct navigations where the browser does not send either header.

Geolocation looks up the visitor's IP using the MaxMind GeoLite2 City database. The .mmdb file is loaded on the first request and cached in memory via @maxmind/geoip2-node and reused for every request with no external API call and no network latency. Coordinates are rounded to one decimal place to reduce precision:

typescriptCopy code
latitude: Math.round(response.location.latitude * 10) / 10

User-Agent parsing is done with plain regex functions instead of a library. The browser, OS and device type are each extracted with a short chain of pattern checks. The order matters. Edge and Opera both include chrome in their User-Agent string, so they have to be matched first.

Visitor hashing creates a daily identifier without storing any persistent user data. The hash is a SHA-256 of the IP address, a server-side secret and the current date. The same visitor gets the same hash all day, which makes unique visitor counting possible. On the next day the hash is different, so there is no cross-day tracking.

Querying the data

All analytics queries live in src/lib/analytics.ts. The functions cover the most common dimensions:

• getAnalyticsStats() returns total pageviews and total unique visitor-days
• getCurrentVisitors() counts distinct hashes seen in the last 5 minutes
• getTopPages(limit) groups by path and orders by view count
• getTopReferrers(limit) filters out self-referrals and empty referrers
• getTopBrowsers, getTopOS, getTopLanguages, getTopCountries, getTopCities each group by their column
• getDeviceBreakdown() splits into mobile, tablet and desktop
• getScreenWidthDistribution() buckets screen widths into four categories
• getPageviewsOverTime(days) returns a daily count for the last N days for the sparkline chart
• getVisitorLocations() returns grouped coordinates for the visitor map

The /api/stats endpoint handles the overall pageview and visit counts, current visitor count and a few additional counts from other tables (comments, webmentions, emoji reactions, subscribers). It caches the result in memory for 24 hours. The more detailed breakdown queries (top pages, referrers, device types, countries and so on) are called directly from the about-this-site page on the server at request time.

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 page fetches all the analytics functions in parallel on the server and passes the results down as props.

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`;

The area fill uses an SVG <path> element and the line is a <polyline>. Normalizing against the range (maxViews - minViews) rather than the absolute max keeps the chart readable even 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:

Visitor locations are rendered on an interactive map using OpenLayers with cluster styling that scales logarithmically with the number of visits from each location.

The rest of the page uses simple HTML lists and inline percentage bars rendered with CSS width set proportionally to the maximum value in each group.

Privacy

No cookies are set. No data is shared with third parties. The IP address is used only to look up a rough location and to create the daily hash, then it is discarded. Coordinates are stored with reduced precision.

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