How-to · Updated 30 October 2026
How to Build a Self-Hosted Next.js Analytics Dashboard
TL;DR
A self-hosted Next.js analytics dashboard is an App Router app you run yourself. A public route accepts pageview and time-spent events. Drizzle stores one Postgres row per browser session. A date_trunc query, or a daily rollup once that scan grows, feeds a shadcn/ui card with a Recharts area chart. The read API requires a signed-in user. You operate the database and the deploy.
Data Lighthouse is that dashboard already assembled, including realtime traffic and optional Stripe revenue, for $59 once. See the live demo, the quickstart, and checkout. This guide does not publish a measured build time.
What you are building
The pipeline below is the minimum that answers “how many people saw the product, on which day, and where did they land?” It is the same shape this repository uses: a beacon in the root layout, a Zod-validated POST route, a session row in Postgres, a daily aggregation, and Clerk on the route that returns the series. Names in the snippets are the ones you would create in a new app. The template’s production names are dl_user_sessions, POST /api/datalighthouse/events, and GET /api/datalighthouse/analytics.
Snippets follow those files as of 30 October 2026, plus one deliberate change: session days are bucketed in UTC so a chart does not move when the database TimeZone setting changes. The template’s summary query calls date_trunc on first_seen directly, which follows the database timezone.
1. Collect pageview events
usePathname() already omits the query string, so the beacon can send the path without copying window.location.search. Leave tokens and email addresses out of the payload. Store the referrer hostname rather than the full previous URL, which often still carries campaign parameters.
Keep the visitor id in localStorage so it survives new tabs. Keep the session id in sessionStorage so a refresh in the same tab stays one session and a closed tab starts another. The template’s tracking component keeps the session id in a ref, so a full reload mints a new session there too. Wrap storage access in try/catch. Private-mode browsers can throw.
Send the pageview with fetch and keepalive: true. On cleanup, send time spent with navigator.sendBeacon. Cleanup runs on client-side route changes, which is what the template does. A hard tab close is not guaranteed to flush that beacon.
"use client"
import { useEffect, useRef } from "react"
import { usePathname } from "next/navigation"
const VISITOR_KEY = "analytics_visitor_id"
const SESSION_KEY = "analytics_session_id"
function referrerHost(value: string) {
try {
return new URL(value).hostname
} catch {
return undefined
}
}
export function AnalyticsBeacon() {
const pathname = usePathname()
const startedAt = useRef(0)
useEffect(() => {
let visitorId = ""
let sessionId = ""
try {
visitorId = window.localStorage.getItem(VISITOR_KEY) ?? crypto.randomUUID()
window.localStorage.setItem(VISITOR_KEY, visitorId)
sessionId = window.sessionStorage.getItem(SESSION_KEY) ?? crypto.randomUUID()
window.sessionStorage.setItem(SESSION_KEY, sessionId)
} catch (error) {
console.warn("[Data Lighthouse] Unable to persist analytics ids", error)
return
}
startedAt.current = Date.now()
const page = pathname.split("?")[0]
void fetch("/api/analytics/events", {
method: "POST",
headers: { "Content-Type": "application/json" },
body: JSON.stringify({
type: "pageview",
siteId: "default",
sessionId,
visitorId,
page,
referrer: document.referrer ? referrerHost(document.referrer) : undefined,
}),
keepalive: true,
})
return () => {
const timeSpent = Math.floor((Date.now() - startedAt.current) / 1000)
const body = JSON.stringify({
type: "timespent",
siteId: "default",
sessionId,
visitorId,
page,
timeSpent,
})
navigator.sendBeacon(
"/api/analytics/events",
new Blob([body], { type: "application/json" }),
)
}
}, [pathname])
return null
}Mount <AnalyticsBeacon /> once in the root layout. The ingest route stays public. Validate the body with Zod and cap every string. Cap timeSpent at 86,400 seconds, one day, which is the same ceiling as the template’s event schema. Return 400 for malformed JSON. If Postgres is down, log the error and still return 200 so a transient database failure does not surface as a browser error on every page. Watch those logs. A swallowed insert drops data.
Put a rate limit on this route at your host or in the handler. The template’s events route notes rate limiting as follow-up work and does not ship a limiter. A persistent visitor id is a first-party identifier. Whether it needs consent depends on where you operate. The GDPR analytics checker is a self-assessment, and the privacy-first analytics guide covers the trade-offs. Neither is legal advice.
import { NextResponse, type NextRequest } from "next/server"
import { z } from "zod"
import { recordAnalyticsEvent } from "@/lib/analytics"
const eventSchema = z.object({
type: z.enum(["pageview", "timespent"]),
sessionId: z.string().min(1).max(128),
visitorId: z.string().min(1).max(128),
page: z.string().min(1).max(2048),
referrer: z.string().max(253).optional(),
timeSpent: z.number().finite().nonnegative().max(86_400).optional(),
siteId: z.string().min(1).max(64).optional(),
})
export async function POST(request: NextRequest) {
let raw: unknown
try {
raw = await request.json()
} catch {
return NextResponse.json({ error: "Invalid event payload" }, { status: 400 })
}
const parsed = eventSchema.safeParse(raw)
if (!parsed.success) {
return NextResponse.json({ error: "Invalid event payload" }, { status: 400 })
}
if (parsed.data.type === "timespent" && parsed.data.timeSpent === undefined) {
return NextResponse.json({ error: "timeSpent is required" }, { status: 400 })
}
try {
await recordAnalyticsEvent({
...parsed.data,
siteId: parsed.data.siteId ?? "default",
})
} catch (error) {
console.error("[Data Lighthouse] Failed to persist analytics event", error)
}
return NextResponse.json({ ok: true })
}2. Postgres schema with Drizzle
One row per session is enough for a visitors-by-day chart and a landing-page table. session_id is unique. (site_id, first_seen) supports the date filter the chart uses. visitor_id supports the “new visitors” query later.
The template table dl_user_sessions adds country, city, latitude, and longitude. Its events route fills those from an IP lookup and, on localhost, writes a fixed San Francisco point. Leave geo null until a CDN country header or a provider you trust supplies it. Do not copy that localhost fallback into production, and do not store the raw IP on the session row.
import {
index,
integer,
pgTable,
serial,
text,
timestamp,
uniqueIndex,
varchar,
} from "drizzle-orm/pg-core"
export const analyticsSessions = pgTable(
"analytics_sessions",
{
id: serial("id").primaryKey(),
siteId: varchar("site_id", { length: 64 }).notNull(),
visitorId: varchar("visitor_id", { length: 128 }).notNull(),
sessionId: varchar("session_id", { length: 128 }).notNull(),
landingPage: text("landing_page"),
lastPage: text("last_page"),
referrer: text("referrer"),
pageviews: integer("pageviews").notNull().default(0),
totalDurationSeconds: integer("total_duration_seconds").notNull().default(0),
firstSeen: timestamp("first_seen", { withTimezone: true }).notNull().defaultNow(),
lastSeen: timestamp("last_seen", { withTimezone: true }).notNull().defaultNow(),
},
(table) => ({
sessionUnique: uniqueIndex("analytics_sessions_session_unique").on(table.sessionId),
siteFirstSeenIdx: index("analytics_sessions_site_first_seen_idx").on(table.siteId, table.firstSeen),
visitorIdx: index("analytics_sessions_visitor_idx").on(table.visitorId),
}),
)getDb() returns null when DATABASE_URL is missing so a static build of a marketing page does not open a connection. The template also accepts POSTGRES_URL. prepare: false is required for transaction-mode poolers (Supabase’s pooler, Neon’s pooler, PgBouncer). max: 1 matches the template and suits a serverless invocation. A long-running Node server can raise that pool size. Cache the client on globalThis in development so hot reload does not open a new pool on every save.
import { drizzle } from "drizzle-orm/postgres-js"
import postgres from "postgres"
import * as schema from "./schema"
type SqlClient = ReturnType<typeof postgres>
const globalForDb = globalThis as unknown as { analyticsSql?: SqlClient }
export function getDb() {
const connectionString = process.env.DATABASE_URL
if (!connectionString) return null
const sqlClient =
globalForDb.analyticsSql ??
postgres(connectionString, {
prepare: false,
max: 1,
idle_timeout: 20,
connect_timeout: 10,
})
if (process.env.NODE_ENV !== "production") {
globalForDb.analyticsSql = sqlClient
}
return drizzle(sqlClient, { schema })
}Apply it with the same Drizzle Kit command this repo uses. drizzle.config.ts reads DATABASE_URL.
bun run db:push
# drizzle-kit push --config ./drizzle.config.tsThe write path is one upsert. The first event inserts the landing page and referrer. A conflicting session_id updates last seen, last page, and either pageviews or duration. Referrer stays as first written. The template reads the row first and, on a later event, overwrites referrer when the new payload includes one. Two concurrent first pageviews are safe with onConflictDoUpdate: one insert wins, the other increments.
import { sql } from "drizzle-orm"
import { getDb } from "./client"
import { analyticsSessions } from "./schema"
type AnalyticsEvent = {
type: "pageview" | "timespent"
siteId: string
sessionId: string
visitorId: string
page: string
referrer?: string
timeSpent?: number
}
export async function recordAnalyticsEvent(event: AnalyticsEvent) {
const database = getDb()
if (!database) return
const now = new Date()
const isPageview = event.type === "pageview"
const seconds = isPageview ? 0 : Math.round(event.timeSpent ?? 0)
const update: {
lastSeen: Date
lastPage: string
pageviews?: ReturnType<typeof sql>
totalDurationSeconds?: ReturnType<typeof sql>
} = {
lastSeen: now,
lastPage: event.page,
}
if (isPageview) {
update.pageviews = sql`${analyticsSessions.pageviews} + 1`
}
if (seconds > 0) {
update.totalDurationSeconds = sql`${analyticsSessions.totalDurationSeconds} + ${seconds}`
}
await database
.insert(analyticsSessions)
.values({
siteId: event.siteId,
visitorId: event.visitorId,
sessionId: event.sessionId,
landingPage: event.page,
lastPage: event.page,
referrer: event.referrer,
pageviews: isPageview ? 1 : 0,
totalDurationSeconds: seconds,
firstSeen: now,
lastSeen: now,
})
.onConflictDoUpdate({
target: analyticsSessions.sessionId,
set: update,
})
}That session row remembers landing_page and last_page only. A top-pages chart grouped by landing page, which is what the template’s summary returns, is not a count of every path viewed. Add a analytics_pageviews row per pageview, with path and occurred_at, when you need per-path counts. Insert it in the same transaction as the session upsert. The event-based analytics guide covers naming those events before the table grows past pageviews.
3. Aggregation and rollups
Bucket by the UTC day of first_seen. A session that starts Monday and continues Tuesday counts on Monday. The WHERE clause is an instant (“sessions that started after this timestamp”). The bucket is a calendar day. Keep those two ideas separate or the last partial day will look short for a reason that is not traffic.
Group by that same YYYY-MM-DD string. Postgres then sees one expression in both the select list and GROUP BY. Count distinct session_id and distinct visitor_id. Skip a SQL LIMIT below the number of days in the range, or you drop the oldest buckets. The template’s trend query orders days descending, limits to 30, and reverses the array in JavaScript. For a 30-day window the date filter is enough.
import { and, eq, gte, sql } from "drizzle-orm"
import { getDb } from "./client"
import { analyticsSessions } from "./schema"
export async function dailyVisitors(siteId: string, since: Date) {
const database = getDb()
if (!database) return []
const day = sql`to_char(date_trunc('day', ${analyticsSessions.firstSeen} at time zone 'utc'), 'YYYY-MM-DD')`
const rows = await database
.select({
day,
sessions: sql`count(distinct ${analyticsSessions.sessionId})`,
visitors: sql`count(distinct ${analyticsSessions.visitorId})`,
})
.from(analyticsSessions)
.where(and(eq(analyticsSessions.siteId, siteId), gte(analyticsSessions.firstSeen, since)))
.groupBy(day)
.orderBy(day)
return rows.map((row) => ({
day: String(row.day),
sessions: Number(row.sessions ?? 0),
visitors: Number(row.visitors ?? 0),
}))
}“New visitors” is the count of visitor ids whose earliest first_seen falls inside the range. The date filter belongs in HAVING, not WHERE. Filtering the rows to the window first makes every visitor in that window look new. This scan reads history for the site. A boolean stored at insert time avoids the scan and can be wrong when two first sessions race. The template stores new_visitor on insert. Trust the min(first_seen) query when the number has to be right.
select count(*)::int as new_visitors
from (
select visitor_id
from analytics_sessions
where site_id = $1
group by visitor_id
having min(first_seen) >= $2
) first_sessions;Read-time aggregation is the right default, and it is what getAnalyticsSummary does. Add analytics_daily when that scan gets slow. There is no row-count threshold on this page. The job recomputes today and yesterday. Today is incomplete, and a late event can still land on yesterday. ON CONFLICT (site_id, day) needs the unique index below. Run the statement on a schedule you control. The chart can keep reading the session table until you point it at the summary.
import { date, integer, pgTable, serial, uniqueIndex, varchar } from "drizzle-orm/pg-core"
export const analyticsDaily = pgTable(
"analytics_daily",
{
id: serial("id").primaryKey(),
siteId: varchar("site_id", { length: 64 }).notNull(),
day: date("day").notNull(),
sessions: integer("sessions").notNull(),
visitors: integer("visitors").notNull(),
pageviews: integer("pageviews").notNull(),
},
(table) => ({
siteDayUnique: uniqueIndex("analytics_daily_site_day_unique").on(table.siteId, table.day),
}),
)insert into analytics_daily (site_id, day, sessions, visitors, pageviews)
select
site_id,
(first_seen at time zone 'utc')::date as day,
count(distinct session_id)::int,
count(distinct visitor_id)::int,
coalesce(sum(pageviews), 0)::int
from analytics_sessions
where first_seen >= ((date_trunc('day', now() at time zone 'utc') - interval '1 day') at time zone 'utc')
group by site_id, (first_seen at time zone 'utc')::date
on conflict (site_id, day) do update
set sessions = excluded.sessions,
visitors = excluded.visitors,
pageviews = excluded.pageviews;4. Charts with shadcn/ui and Recharts
Recharts needs a client component. The server page loads the series and passes plain objects across the boundary. ResponsiveContainer needs a height. minWidth={0} is the fix this repo uses so a chart inside a flex or grid parent does not collapse to zero width. Mark the dashboard page force-dynamic. It reads live Postgres and should not be frozen at build time. dailyVisitors returns an empty array when DATABASE_URL is missing, so the page renders an empty chart instead of throwing during a build that has no database.
The template’s traffic card is also a client component. In postgres mode it maps trend.uniqueVisitors from GET /api/datalighthouse/analytics. The signed-in overview’s “Revenue vs. visitors” chart is a different series: visitor points there are generated from the Stripe revenue series, which the Stripe template page describes. Use the query on this page when the line must be measured visitors.
"use client"
import { Area, AreaChart, ResponsiveContainer, Tooltip, XAxis, YAxis } from "recharts"
import { Card, CardContent, CardHeader, CardTitle } from "@/components/ui/card"
export function VisitorsChart({ data }: { data: { day: string; visitors: number }[] }) {
return (
<Card>
<CardHeader>
<CardTitle className="text-base">Visitors by day</CardTitle>
</CardHeader>
<CardContent>
<ResponsiveContainer width="100%" height={240} minWidth={0}>
<AreaChart data={data}>
<XAxis dataKey="day" stroke="#71717a" fontSize={12} tickLine={false} axisLine={false} />
<YAxis
stroke="#71717a"
fontSize={12}
tickLine={false}
axisLine={false}
width={32}
allowDecimals={false}
/>
<Tooltip />
<Area type="monotone" dataKey="visitors" stroke="#6366f1" fill="#6366f1" fillOpacity={0.2} />
</AreaChart>
</ResponsiveContainer>
</CardContent>
</Card>
)
}import { dailyVisitors } from "@/lib/analytics"
import { VisitorsChart } from "@/components/visitors-chart"
export const dynamic = "force-dynamic"
export default async function DashboardPage() {
const since = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000)
const series = await dailyVisitors("default", since)
return <VisitorsChart data={series.map((row) => ({ day: row.day, visitors: row.visitors }))} />
}5. Auth on the read API
The beacon cannot attach a user session for anonymous traffic. Keep POST ingest public. Require a user on anything that returns counts, paths, or raw sessions. This stack uses Clerk. currentUser() returns null when the request has no session, and the analytics route responds with 401. The same check belongs on a Server-Sent Events stream of live sessions. The template does that on GET /api/datalighthouse/events.
import { NextResponse, type NextRequest } from "next/server"
import { currentUser } from "@clerk/nextjs/server"
import { dailyVisitors } from "@/lib/analytics"
export async function GET(request: NextRequest) {
const user = await currentUser()
if (!user) {
return NextResponse.json({ error: "Authentication required" }, { status: 401 })
}
const days = request.nextUrl.searchParams.get("range") === "7d" ? 7 : 30
const since = new Date(Date.now() - days * 24 * 60 * 60 * 1000)
const series = await dailyVisitors("default", since)
return NextResponse.json({ series })
}This repository gates routes in proxy.ts (Next.js 16). Add the ingest path to the public matcher. If it is missing, Clerk answers the beacon before your handler runs. Dashboard pages stay protected. The same clerkMiddleware callback is what earlier Next.js versions export from middleware.ts.
import { clerkMiddleware, createRouteMatcher } from "@clerk/nextjs/server"
const isPublicRoute = createRouteMatcher([
"/",
"/api/analytics/events",
])
export default clerkMiddleware(async (auth, request) => {
if (!isPublicRoute(request)) {
await auth.protect()
}
})
export const config = {
matcher: [
"/((?!_next|[^?]*\\.(?:html?|css|js(?!on)|jpe?g|webp|png|gif|svg|ttf|woff2?|ico)).*)",
"/(api|trpc)(.*)",
],
}6. Deploy
Set DATABASE_URL in the environment the server runs in and in the environment where you run bun run db:push. Add the Clerk keys your middleware expects: NEXT_PUBLIC_CLERK_PUBLISHABLE_KEY and CLERK_SECRET_KEY. Stripe is optional and separate. The template reads STRIPE_SECRET_KEY only for the revenue API. Checkout for the Data Lighthouse license uses Polar, which the quickstart spells out.
Run the production server on the port the host assigns (PORT). Keep events in Postgres. A host with an ephemeral disk will drop files you write locally, and an in-memory map such as the template’s realtime store lives in one Node process. It is the right source for “who is on the site in this process” and the wrong source for a chart after a restart or a second instance. Point charts at the session table or the daily rollup.
Backups, retention, and upgrades are part of running this. The self-hosted analytics stack checklist is the operations list: database backups, access control, and retention. This guide stops at a working dashboard.
Build vs buy
One-time purchase of a self-hosted Next.js template. Full source, lifetime updates, and use in unlimited projects. The license is $59 once. The pricing page also shows $99 as a compare-at price. Hosting and Postgres are a separate bill whether you build or buy. This page does not publish a measured build time, a customer count, or a traffic figure.
Build from this guide
- A beacon and a validated public ingest route
- One Postgres session table and a daily query
- An optional daily rollup for when that scan grows
- One Recharts area chart and auth on the read API
- A deploy you operate, including backups and retention
Buy the $59 template
- Full Next.js + TypeScript source code
- Real-time analytics with 3D globe
- Stripe revenue dashboard
- Supabase customer vault setup
- Automated GitHub repo invites
- Lifetime updates
- Developer documentation
- Use in unlimited projects
The template adds the realtime map, the dashboard chrome, and the optional Stripe revenue cards on top of a pipeline like this one. Those cards have limits. The Next.js Stripe analytics dashboard page lists them, including the charge and subscription caps and the fact that yearly prices are not divided by 12. The best Next.js analytics dashboard templates page compares this template with Umami, OpenPanel, Rybbit, Plausible Community Edition, PostHog’s hobby deploy, and UI-only starters. Those are the right links if you want a standalone analytics product instead of code inside your app.
The live demo renders sample data. It does not call Stripe, Clerk, Polar, or the database. The quickstart is the template setup after you have the source. Checkout is $59 once, with GitHub sign-in before Polar payment so the buyer can be invited to the private repository.
7-day no-questions refund. If the template is not a fit, request it within 7 days through the purchase contact on the contact page.
Build-guide FAQ
Short answers for how to build a self-hosted Next.js analytics dashboard, and when the template is the shorter path.
How do I build an analytics dashboard in Next.js?
Add a client beacon, a public POST route that validates pageview and time-spent JSON, a Drizzle session table in Postgres, a daily date_trunc query, and a Recharts area chart inside a shadcn/ui card. Protect the read API with your auth provider. Deploy the Next.js app against a Postgres database you operate. The steps on this page are that path.
What is a self-hosted Next.js analytics dashboard?
It is a dashboard that runs inside your own Next.js app and stores events in a database you control. Data Lighthouse is one template for that: Next.js, TypeScript, optional Postgres, and an optional Stripe revenue view. Umami, OpenPanel, and Rybbit are separate analytics apps you host beside your product. The comparison is on the best Next.js analytics dashboard templates page.
How do I store Next.js analytics events in Postgres with Drizzle?
Create a table with one row per session id, a unique index on session_id, and an index on site_id plus first_seen. Upsert on that session id: the first event inserts the landing page and referrer, and later events increment pageviews or duration. Use the postgres.js driver. Set prepare: false when DATABASE_URL points at a transaction pooler such as Supabase's pooler or PgBouncer.
Do I need a rollup table for a Next.js analytics dashboard?
Not to start. Grouping the session table with date_trunc is enough while the table is small, and it is what the Data Lighthouse summary query does. Add a daily summary table when that scan gets slow. Recompute today and yesterday on each run, because the current day is still receiving events. This page does not publish a row count where that switch is required.
Should the analytics event endpoint require a login?
The ingest endpoint should stay public, with a strict JSON schema and a rate limit at the edge or in the route. The endpoint that returns visitors, charts, or raw sessions should require a signed-in user. In this stack the read route calls Clerk currentUser() and responds 401 when no user is present. The POST ingest path is listed as a public route.
Should I build a Next.js analytics dashboard or buy the $59 template?
Build from this guide when you want a small first-party pipeline and you will operate Postgres yourself. Buy Data Lighthouse when you want that pipeline plus the realtime map, the optional Stripe revenue cards, and the dashboard UI already wired. The license is $59 once. The pricing page also shows $99 as a compare-at price. Hosting is a separate bill either way. This page does not publish a measured build time. The sample dashboard is at https://datalighthouse.dev/demo, setup notes are at https://datalighthouse.dev/quickstart, and checkout is at https://datalighthouse.dev/checkout. The refund is 7 days, requested from the contact page.
Sources
Official references for the APIs in the snippets. Product behavior above was checked against this repository.