# DNS Analytics — Custom Visualization Catalogue
`dns-analytics.codeandcore.dev` · Built with HTML + SVG + JS · codeandcore design system

No Grafana. No charting libraries you didn't write. Just your schema, your SQL, and your canvas.

---

## Design System Reminder

| Token | Value | Use |
|---|---|---|
| Navy | `#0D2340` | Chart backgrounds, axis lines |
| Gold | `#B8922A` | Primary data series, highlights |
| Ivory | `#F9F6EF` | Card backgrounds |
| Parch | `#EDE8DC` | Alternate rows, grid lines |
| Blue-700 | `#1E4D8C` | Secondary series |
| Blue-500 | `#3970B8` | Tertiary series, labels |
| Ink | `#1C1A16` | Body text |
| Ink-Soft | `#6B6760` | Axis labels, legend text |
| Rule | `#D5CFC3` | Grid lines, borders |
| Font heading | Cormorant Garamond 600 | Chart titles |
| Font label | Jost 600 11px tracked | Axis labels, legends |
| Font mono | JetBrains Mono | Data values, counts |

---

## Section 1 — Operational Overview (Daily Dashboard)

These are the graphs you check every morning. Fast reads, single-glance insight.

---

### VIZ-01 · Live Query Rate Gauge

**What it shows:** Queries per second right now, compared to the 7-day average for this hour.

**Data source:**
```sql
-- Current rate (last 60 seconds)
SELECT COUNT(*) AS qps
FROM dns_queries
WHERE queried_at >= NOW() - INTERVAL '60 seconds';

-- 7-day average for this hour
SELECT ROUND(AVG(query_count)) AS avg_qps
FROM dns_hourly_heatmap
WHERE hour_of_day = EXTRACT(HOUR FROM NOW())
  AND stat_date >= CURRENT_DATE - INTERVAL '7 days';
```

**Visualization:** Circular arc gauge. Gold fill for current value. Navy track. Needle or fill arc sweeping from 0 to max(current × 1.5, avg × 2). Display current qps in JetBrains Mono large, 7-day average in Ink-Soft below.

**Insight delivered:** Is today's resolver traffic normal? Are you being hit unusually hard right now?

---

### VIZ-02 · 24-Hour Query Heatmap Strip

**What it shows:** Query volume by hour across today, yesterday, and last 7-day average in three horizontal strips.

**Data source:**
```sql
SELECT stat_date, hour_of_day, SUM(query_count) AS total
FROM dns_hourly_heatmap
WHERE stat_date >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY stat_date, hour_of_day
ORDER BY stat_date, hour_of_day;
```

**Visualization:** Three horizontal strips, each 24 cells wide (one cell per hour). Cell color: Navy (low) → Gold (peak). Label each strip with the date on the left. Hover tooltip shows exact count and hour. The three strips stack vertically so patterns jump out immediately — peak hours align visually across days.

**Insight delivered:** When is your resolver under load? Is the pattern consistent? Did something spike overnight?

---

### VIZ-03 · Top 10 Domains — Horizontal Bar Race

**What it shows:** Top 10 apex domains by query count for yesterday, with percentage of total traffic.

**Data source:**
```sql
SELECT apex_domain, SUM(hit_count) AS total,
    ROUND(SUM(hit_count) * 100.0 / SUM(SUM(hit_count)) OVER (), 2) AS pct,
    dc.category, dc.provider
FROM dns_daily_stats ds
LEFT JOIN domain_categories dc ON dc.apex_domain = ds.apex_domain
WHERE stat_date = CURRENT_DATE - 1
GROUP BY ds.apex_domain, dc.category, dc.provider
ORDER BY total DESC
LIMIT 10;
```

**Visualization:** Horizontal bars. Bar width = percentage of total. Gold fill for CDN, Blue-700 for streaming, Blue-500 for social, Rule for work/updates, Ink-Soft for uncategorised. Domain name in Jost left of bar. Count + percentage in JetBrains Mono right of bar. Bars animate in left-to-right on load (CSS transition, staggered 50ms per bar).

**Insight delivered:** What is your network actually resolving? Which provider dominates your DNS traffic?

---

### VIZ-04 · Traffic Category Donut

**What it shows:** Share of total queries by category (streaming, CDN, social, work, ads, uncategorised).

**Data source:**
```sql
SELECT COALESCE(dc.category, 'uncategorised') AS category,
    SUM(ds.hit_count) AS total,
    ROUND(SUM(ds.hit_count) * 100.0 / SUM(SUM(ds.hit_count)) OVER (), 1) AS pct
FROM dns_daily_stats ds
LEFT JOIN domain_categories dc ON dc.apex_domain = ds.apex_domain
WHERE ds.stat_date >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY 1
ORDER BY total DESC;
```

**Visualization:** SVG donut chart. Segments colored from the design palette — assign one fixed color per category so the chart is consistent across time ranges. Center of donut: total query count in JetBrains Mono large + "queries" label in Jost. Legend below with color dot, category name, and percentage. Hover segment lifts slightly (CSS transform scale).

**Insight delivered:** Is your network streaming-heavy? Work-heavy? How much of your resolver load is ads?

---

### VIZ-05 · Unique Clients Per Hour (Today)

**What it shows:** How many distinct client IPs are generating queries, by hour, for today.

**Data source:**
```sql
SELECT EXTRACT(HOUR FROM queried_at AT TIME ZONE 'Africa/Kampala') AS hour,
    COUNT(DISTINCT client_ip) AS unique_clients
FROM dns_queries
WHERE queried_at >= CURRENT_DATE AT TIME ZONE 'Africa/Kampala'
GROUP BY 1
ORDER BY 1;
```

**Visualization:** Area chart with smooth curve. X axis: 00:00 to current hour. Y axis: unique client count. Gold fill at 20% opacity under the line. Solid Gold line 2px. Current hour dot: solid Gold circle 6px. Grid lines in Rule color, dashed. X labels in Jost 600 11px.

**Insight delivered:** Is the network busy because of many clients or a few heavy hitters? Useful for diagnosing whether a spike is organic or one device gone rogue.

---

## Section 2 — Traffic Intelligence (Weekly Reports)

These are the graphs you review on Monday morning. Trend-focused, planning-oriented.

---

### VIZ-06 · 7-Day Volume Trend — Stacked Area

**What it shows:** Daily total query volume for the last 7 days, stacked by top 5 categories.

**Data source:**
```sql
SELECT ds.stat_date,
    COALESCE(dc.category, 'other') AS category,
    SUM(ds.hit_count) AS total
FROM dns_daily_stats ds
LEFT JOIN domain_categories dc ON dc.apex_domain = ds.apex_domain
WHERE ds.stat_date >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY ds.stat_date, COALESCE(dc.category, 'other')
ORDER BY ds.stat_date, total DESC;
```

**Visualization:** Stacked area chart. X axis: last 7 days (date labels). Y axis: query count. Each category is a colored band — same fixed color palette as VIZ-04 for consistency. Hover line shows vertical crosshair with tooltip breakdown by category at that date. Smooth bezier curves, not jagged steps.

**Insight delivered:** Is traffic growing week-over-week? Which category is driving growth? Did one category spike on a specific day?

---

### VIZ-07 · Domain League Table (Week-over-Week)

**What it shows:** Top 20 domains this week vs last week — rank change, volume change, trend arrow.

**Data source:**
```sql
-- This week
SELECT apex_domain, total_queries, unique_clients, category,
    RANK() OVER (ORDER BY total_queries DESC) AS rank_this_week
FROM dns_weekly_stats
WHERE week_start = DATE_TRUNC('week', CURRENT_DATE)::DATE

-- Last week (join or second query)
SELECT apex_domain, total_queries,
    RANK() OVER (ORDER BY total_queries DESC) AS rank_last_week
FROM dns_weekly_stats
WHERE week_start = DATE_TRUNC('week', CURRENT_DATE - INTERVAL '7 days')::DATE;
```

**Visualization:** Table card. Columns: rank (this week), domain, category, total queries, WoW change %, rank movement (▲3 in Gold / ▼2 in muted red / NEW badge). Alternate row background Ivory / Parch. JetBrains Mono for numbers. Jost for domain names. Sortable columns. Compact but readable — not a spreadsheet, a league table.

**Insight delivered:** Which domains are rising fast? Are new domains appearing that weren't in the top 20 last week? Early warning for new services or abuse patterns.

---

### VIZ-08 · Peak Hour Comparison — Overlapping Lines

**What it shows:** Hourly query distribution for each of the last 7 days overlaid on one chart, plus the 7-day average line.

**Data source:**
```sql
SELECT stat_date, hour_of_day, SUM(query_count) AS total
FROM dns_hourly_heatmap
WHERE stat_date >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY stat_date, hour_of_day
ORDER BY stat_date, hour_of_day;
```

**Visualization:** Line chart. X axis: 0–23 hours. Y axis: query count. Each day: thin Blue-500 line at 30% opacity. 7-day average: thick Gold line 2.5px, fully opaque. Weekend days optionally dashed. The result shows the "shape" of a typical day and how much individual days deviate from it.

**Insight delivered:** Is your peak hour consistent? Do weekends look different from weekdays? Is there a late-night spike that shouldn't be there?

---

### VIZ-09 · NXDOMAIN Rate Timeline

**What it shows:** NXDOMAIN percentage of total queries per day over the last 30 days. Threshold line at your acceptable rate (e.g. 5%).

**Data source:**
```sql
SELECT stat_date,
    SUM(hit_count) AS total,
    SUM(nxdomain_count) AS failures,
    ROUND(SUM(nxdomain_count) * 100.0 / NULLIF(SUM(hit_count), 0), 2) AS fail_pct
FROM dns_daily_stats
WHERE stat_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY stat_date
ORDER BY stat_date;
```

**Visualization:** Line chart with threshold band. X axis: 30 days. Y axis: NXDOMAIN %. Gold line for the daily rate. Dashed Navy horizontal line at your threshold (5%). Fill the area above the threshold in a very light red/rose at 10% opacity to make breaches obvious. Dots on each data point, colored Gold (below threshold) or rose (above).

**Insight delivered:** Is NXDOMAIN trending up? Did a specific day spike — and if so, what changed? This is your misconfiguration and abuse detection chart.

---

### VIZ-10 · Client Activity Scatter (Top 20 Clients)

**What it shows:** For the last 7 days, each client IP plotted by unique domains queried (X) vs total queries (Y). Outliers jump out.

**Data source:**
```sql
SELECT client_ip,
    COUNT(*) AS total_queries,
    COUNT(DISTINCT apex_domain) AS unique_domains
FROM dns_queries
WHERE queried_at >= NOW() - INTERVAL '7 days'
GROUP BY client_ip
ORDER BY total_queries DESC
LIMIT 50;
```

**Visualization:** Scatter plot. Each dot = one client IP. X axis: unique domains queried. Y axis: total query count. Dot size: proportional to total queries (min 4px, max 16px). Color: Gold. Hover tooltip: IP address (or hostname if reverse-resolved), exact counts. A client with high queries AND low unique domains is a bot or misconfigured app. A client with high queries AND high unique domains is a busy user. The quadrant tells the story.

**Insight delivered:** Who are your heaviest DNS users? Are any clients behaving abnormally? Useful for both capacity and abuse triage.

---

## Section 3 — Monthly Executive Report

These are the graphs you put in a PDF or page for management. Clean, conclusive, story-telling.

---

### VIZ-11 · Monthly Volume Bar Chart (12-Month)

**What it shows:** Total monthly query volume for the last 12 months. MoM growth % label above each bar.

**Data source:**
```sql
SELECT year_month,
    SUM(total_queries) AS total,
    ROUND((SUM(total_queries) - LAG(SUM(total_queries)) OVER (ORDER BY year_month))
        * 100.0 / NULLIF(LAG(SUM(total_queries)) OVER (ORDER BY year_month), 0), 1) AS mom_pct
FROM dns_monthly_stats
WHERE year_month >= TO_CHAR(CURRENT_DATE - INTERVAL '11 months', 'YYYY-MM')
GROUP BY year_month
ORDER BY year_month;
```

**Visualization:** Vertical bar chart. 12 bars. Gold fill, Navy border top only (3px, matching card hover treatment). MoM growth label above each bar: `+4.2%` in Jost 600 Gold or `-1.1%` in Ink-Soft. X axis: month abbreviations. Y axis: formatted counts (1.2M, 450K). Current month bar slightly lighter (in-progress).

**Insight delivered:** Is DNS traffic on your network growing? What is the compound monthly growth rate? Tells the infrastructure investment story.

---

### VIZ-12 · Category Mix Evolution (Stacked Bar, 6-Month)

**What it shows:** How the share of each category has changed month over month over 6 months. Are subscribers shifting from social to streaming?

**Data source:**
```sql
SELECT ms.year_month, COALESCE(dc.category, 'other') AS category,
    SUM(ms.total_queries) AS total
FROM dns_monthly_stats ms
LEFT JOIN domain_categories dc ON dc.apex_domain = ms.apex_domain
WHERE ms.year_month >= TO_CHAR(CURRENT_DATE - INTERVAL '5 months', 'YYYY-MM')
GROUP BY ms.year_month, COALESCE(dc.category, 'other')
ORDER BY ms.year_month, total DESC;
```

**Visualization:** 100% stacked bar chart (normalized to percentage). 6 bars, one per month. Each bar divided into category segments. Same fixed color palette as VIZ-04. This shows share shift, not absolute volume — useful for "streaming is taking over" narratives. Legend below with category + current month percentage.

**Insight delivered:** How is subscriber behavior evolving? Which categories are growing as a share of traffic? Tells the peering and caching investment story.

---

### VIZ-13 · Top Provider Traffic Table (Monthly)

**What it shows:** For the last full month, traffic ranked by provider (Google, Meta, Akamai, Netflix, etc.) with query count, % of total, and unique domain count under that provider.

**Data source:**
```sql
SELECT dc.provider,
    COUNT(DISTINCT ms.apex_domain) AS domains,
    SUM(ms.total_queries) AS total_queries,
    ROUND(SUM(ms.total_queries) * 100.0 / SUM(SUM(ms.total_queries)) OVER (), 2) AS pct
FROM dns_monthly_stats ms
JOIN domain_categories dc ON dc.apex_domain = ms.apex_domain
WHERE ms.year_month = TO_CHAR(CURRENT_DATE - INTERVAL '1 month', 'YYYY-MM')
  AND dc.provider IS NOT NULL
GROUP BY dc.provider
ORDER BY total_queries DESC;
```

**Visualization:** Ranked table with inline mini-bar. Each row: rank, provider logo placeholder (colored initial circle), provider name, domain count, total queries, percentage, mini horizontal bar (Gold, width = pct × max_width). This is the peering negotiation table — it tells you who you should be talking to about caching or direct connectivity.

**Insight delivered:** Which providers dominate your resolver traffic? Google at 60% means Google caching is your highest-impact infrastructure investment.

---

### VIZ-14 · Subscriber Growth Proxy (Unique Clients per Month)

**What it shows:** Monthly count of distinct client IPs seen in DNS queries — a privacy-safe proxy for active subscriber count.

**Data source:**
```sql
SELECT year_month, SUM(unique_clients) AS approx_subscribers
FROM dns_monthly_stats
WHERE year_month >= TO_CHAR(CURRENT_DATE - INTERVAL '11 months', 'YYYY-MM')
GROUP BY year_month
ORDER BY year_month;
```

**Visualization:** Line chart with dots. X axis: 12 months. Y axis: unique clients. Gold line + filled dots. Trend line overlay (linear regression) in Navy dashed — shows the growth trajectory independent of month-to-month noise. Annotation on the last dot: exact count in JetBrains Mono.

**Insight delivered:** Is your active subscriber base growing? Is growth accelerating or flattening? Pairs with VIZ-11 to show traffic-per-subscriber ratio.

---

## Section 4 — Diagnostic & Abuse Detection

These charts are for the NOC view — real-time or near-real-time, operations-focused.

---

### VIZ-15 · NXDOMAIN Leaderboard (Live)

**What it shows:** Top 20 domains generating NXDOMAIN responses in the last 60 minutes.

**Data source:**
```sql
SELECT apex_domain, COUNT(*) AS failures
FROM dns_queries
WHERE queried_at >= NOW() - INTERVAL '60 minutes'
  AND response_rcode = 'NXDOMAIN'
GROUP BY apex_domain
ORDER BY failures DESC
LIMIT 20;
```

**Visualization:** Auto-refreshing table (every 60 seconds via fetch). Ranked list. Domain in Jost, count in JetBrains Mono Gold. Red dot indicator next to domains with > 50 failures in the window. This is your misconfiguration and DNS-based malware beacon detector.

**Insight delivered:** Is something on the network trying to reach a dead domain repeatedly? Bot traffic? Malware C2 beaconing? Stale app config?

---

### VIZ-16 · Query Type Breakdown — Small Multiples

**What it shows:** Distribution of query types (A, AAAA, CNAME, MX, TXT, PTR, SRV) for today, as a set of small individual bar or donut charts.

**Data source:**
```sql
SELECT qtype, COUNT(*) AS total,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct
FROM dns_queries
WHERE queried_at >= CURRENT_DATE AT TIME ZONE 'Africa/Kampala'
GROUP BY qtype
ORDER BY total DESC;
```

**Visualization:** Small multiples — one compact element per qtype, arranged in a 3-4 column grid. Each element: qtype label in Jost 600, count in JetBrains Mono, percentage as a thin arc or bar. Color coded: A (Gold), AAAA (Blue-700), CNAME (Blue-500), others (Ink-Soft). AAAA percentage is a good IPv6 adoption signal.

**Insight delivered:** Is IPv6 adoption growing (AAAA ratio climbing)? Is there unusual PTR or TXT volume (potential reconnaissance)?

---

### VIZ-17 · RPZ Block Rate (after CH-09 is built)

**What it shows:** Percentage of queries blocked by RPZ policy in the last 24 hours, vs resolved. Block count trending over 7 days.

**Data source:**
```sql
-- After RPZ hit logging is set up
SELECT stat_date,
    SUM(hit_count) AS total,
    SUM(nxdomain_count) FILTER (WHERE apex_domain IN (
        SELECT apex_domain FROM domain_categories WHERE cache_priority = 9
    )) AS rpz_blocks
FROM dns_daily_stats
WHERE stat_date >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY stat_date;
```

**Visualization:** Two-segment horizontal bar per day (resolved vs blocked). Blocked segment in a muted rose/terracotta that fits the palette. Daily block % label. This turns your RPZ policy into a visible operational metric.

**Insight delivered:** How much ad/malware traffic is the resolver blocking? Is the block rate growing (more domains being RPZ'd) or shrinking (RPZ list stale)?

---

## Build Order Recommendation

| Phase | Visualizations | Rationale |
|---|---|---|
| Phase 1 — Daily ops | VIZ-03, VIZ-04, VIZ-02 | Highest daily value, simplest SQL |
| Phase 2 — Weekly intel | VIZ-06, VIZ-07, VIZ-09 | Adds trend context to daily view |
| Phase 3 — NOC live | VIZ-01, VIZ-05, VIZ-15 | Requires real-time fetch loop |
| Phase 4 — Executive | VIZ-11, VIZ-12, VIZ-13, VIZ-14 | Monthly cadence, high-presentation value |
| Phase 5 — Deep diagnostics | VIZ-10, VIZ-16, VIZ-17 | After BUG-01 fix + RPZ chapter |

---

## Page / Section Architecture Suggestion

```
dns-analytics.codeandcore.dev/dashboard.html

┌─────────────────────────────────────────────┐
│  LIVE OVERVIEW (auto-refresh 60s)           │
│  VIZ-01 gauge  │  VIZ-05 area  │  VIZ-04   │
│                                  donut       │
├─────────────────────────────────────────────┤
│  TODAY                                      │
│  VIZ-02 heatmap strip (full width)          │
│  VIZ-03 horizontal bars (top 10 domains)   │
├─────────────────────────────────────────────┤
│  THIS WEEK                                  │
│  VIZ-06 stacked area  │  VIZ-09 NXDOMAIN  │
│  VIZ-07 league table (full width)          │
│  VIZ-08 overlapping lines                  │
├─────────────────────────────────────────────┤
│  NOC DIAGNOSTICS                            │
│  VIZ-15 NXDOMAIN live  │  VIZ-16 qtypes   │
└─────────────────────────────────────────────┘

dns-analytics.codeandcore.dev/report.html  (monthly, printable)

  VIZ-11 volume bars
  VIZ-12 category mix evolution
  VIZ-13 provider table
  VIZ-14 subscriber proxy line
```

---