Overview
urlsnip is an async URL shortener built to go deep on PostgreSQL. The domain is intentionally simple — shorten a URL, redirect, track clicks — so all complexity lives in the database and infrastructure layer: composite indexes, CTE analytics queries, generate_series zero-fill, RANK() window functions, a PL/pgSQL trigger, Redis cache-aside, and a batched async click queue via ARQ.
Problem Solved
Standard URL shorteners write a click to the database on every redirect. Under load, that synchronous INSERT becomes the bottleneck. urlsnip decouples the write path — redirects only touch Redis, a background ARQ worker batch-inserts up to 200 clicks per cycle — while the analytics layer demonstrates every major PostgreSQL query feature to serve dashboard data efficiently.
Key Modules
Async Click Queue
Redirect endpoint does one SELECT (slug lookup) and one LPUSH to Redis — then returns 302 instantly. An ARQ worker drains the queue every 5 seconds with a single bulk INSERT, keeping the DB write path fully decoupled from request latency.
Redis Cache-Aside
All analytics endpoints check Redis first. On a miss, Postgres runs the query; the result is stored with a 60s TTL. On link deletion, both stats and clicks-per-day cache keys are explicitly invalidated.
CTE Analytics
A single WITH query computes total clicks, unique visitors, top countries, and top referrers in one Postgres round-trip — no N+1, no ORM magic.
generate_series Zero-Fill
Daily click chart queries use generate_series to produce a full date spine, then LEFT JOIN against actual click data — days with zero clicks show 0, not a gap.
Window Functions
Top-links endpoint uses RANK() OVER and SUM() OVER to rank links and compute per-link traffic share percentage in a single query.
PL/pgSQL Trigger
A database-level AFTER INSERT trigger on the clicks table keeps links.click_count in sync automatically — no application-side counter update needed.
Technical Architecture
FastAPI app with three route domains: links (CRUD + redirect), analytics (stats, clicks-per-day, top links, cleanup), and redemption (one-time redeemable links with SELECT FOR UPDATE to guarantee exactly-one-winner under concurrency). Repository pattern separates raw SQL from route handlers. ARQ worker runs as a separate process, sharing only the Redis connection and Postgres engine. Alembic manages 4 versioned migrations including table partitioning and a materialized view for the fast clicks-per-day endpoint.
Design Language
API-only service with a minimal static frontend. Documentation auto-generated via FastAPI's OpenAPI integration at /docs.