Architecture
How a self-hosted analytics stack fits together: separating writes from reads, choosing storage that matches your query shape, and keeping ingestion cheap while dashboards stay fast. These posts document the trade-offs behind InsightsTrack's PostgreSQL-plus-DuckDB design, and where a different split would serve you better.
25 guides on architecture.
Connecting Your Analytics to Claude Desktop with MCP
How the Model Context Protocol lets an AI assistant query your own analytics. The tool registry pattern, per-user site scoping, and why the MCP server is a thin proxy rather than a second database client.
Building an AI Analyst: The Tool Registry Pattern
How to give an LLM access to your analytics without letting it near your database. Tool schemas, result envelopes, site scoping, and the size guardrails that keep a wide query from blowing the context budget.
Why Did Traffic Drop? Joining SERP Data with Your Analytics
Web analytics knows what happened to a page; a SERP API knows what happened in search. Neither explains the link. How to join them into a plain-English cause, and why an honest 'not search' answer matters.
Safely Letting Users Run SQL Against Your Analytics
Exposing a SQL editor to users means accepting arbitrary queries. Parse-and-allowlist beats regex blocklists, plus row caps, statement timeouts and per-site scoping that cannot be escaped.
Hot and Cold Storage for Analytics Without a Data Warehouse
Recent data should be fast; old data should be cheap. How to tier analytics storage between a local DuckDB file and Parquet on S3, and query across both in one SQL statement.
Request Coalescing: Fixing the Thundering Herd in Analytics
When a cache key expires, every concurrent request misses at once and fires the same expensive query. Coalescing makes one of them do the work and the rest wait on its promise.
Storing Third-Party API Keys Safely in a Self-Hosted App
Users paste credentials into your app. How to encrypt them at rest with AES-256-GCM, why the key must never come back to the browser, and what a masked hint is for.
Geographic Analytics Without Storing IP Addresses
You can report country and city without ever persisting an IP address. Resolve at ingest, keep the result, discard the input — and why that one ordering decision changes your compliance position.
Building an Analytics Data Warehouse on a Single Server
How to run a complete analytics warehouse — ingestion, transformation, rollups, tiered storage, and BI — on one machine, and the signals that mean it is time to scale out.
Collecting Heatmap Data Without Drowning Your Database
Click heatmaps generate far more events than pageviews. Coordinate normalisation, scroll-depth milestones instead of continuous tracking, and aggregating early so the raw stream stays affordable.
Integrating Error Tracking With Your Analytics
Connecting Sentry or similar to analytics means credentials, polling and reconciliation. The adapter pattern, HMAC-verified webhooks, and adaptive backoff that does not hammer a quiet project.
Building Scheduled Reports That Do Not Melt Your Server
Everyone wants a Monday morning email. The generation queue, idempotency so a retry does not double-send, and why scheduled reports are the query pattern most likely to take down an instance.
Writing a Tracking Script That Does Not Slow Down the Site
Your tracking script runs on every page of every customer. Size budgets, sendBeacon over fetch, why it must never block rendering, and failing silently when the collector is down.
Multi-Tenant Analytics: Isolating Sites Properly
One instance, many sites, many users. Membership tables over ownership columns, enforcing scope in middleware rather than per-route, and why the AI and SQL layers need the same check.
Implementing Data Retention Without Breaking Your Reports
GDPR wants a retention limit; your year-over-year chart wants history. Pre-aggregating before deletion, per-site policies, and the sweep that enforces them without locking the table.
Designing a Real-Time Analytics Pipeline (Without Kafka)
How to build a real-time analytics pipeline on a single node — ingestion, batching, backpressure, freshness budgets, and delivering live dashboards with SSE.
Counting Live Visitors Accurately
'Active now' sounds trivial and is not. Choosing the activity window, why heartbeats beat pageviews, deduplicating across tabs, and keeping the query cheap enough to run every ten seconds.
Designing a Custom Dashboard Builder
Letting users arrange their own widgets means a layout model, a widget registry and a migration story. What to store, what to compute, and why saved layouts need versioning from day one.
Benchmarking Analytics Queries Honestly
Most published analytics benchmarks are marketing. Cold vs warm cache, realistic data distributions, measuring percentiles not averages, and publishing the harness so people can check.
Designing an Analytics REST API
Date range parsing, a consistent response envelope, cache headers that match your TTLs, and error responses that help a developer without leaking internals.
Building an Incremental Sync from PostgreSQL to DuckDB
How to stream new rows from PostgreSQL into DuckDB without duplicates or gaps — keyset cursors, batch checkpointing, backfills, and idempotent restarts.
Columnar vs Row Storage: Why OLAP Queries Get 100× Faster
A from-first-principles explanation of columnar storage — how data layout, compression, and vectorized execution combine to make analytical queries dramatically faster.
DuckDB + Parquet: Cheap Cold Storage for Analytics Data
How to tier analytics data between a hot DuckDB table and cold Parquet files on S3 or R2, and stitch them into one queryable view with UNION ALL.
DuckDB vs ClickHouse vs PostgreSQL for Analytics: How to Choose
A practical comparison of DuckDB, ClickHouse, and PostgreSQL for analytical workloads — operational cost, query shape, concurrency, and when each one is the right call.
Why We Built Analytics on PostgreSQL + DuckDB (Architecture Deep-Dive)
A technical deep-dive into the dual-database analytics architecture behind InsightsTrack: PostgreSQL for writes, DuckDB for reads, and how it answers 90-day queries in under 100 ms.