TLD Reputation Analysis

Backend and Data Engineering · 2026 · Historical snapshot · 3 min read

Converted a Python and SQLite TLD abuse analysis dashboard into a static portfolio demo backed by versioned JSON exports.

Open interactive demo

Overview

The project analyzes top-level domain reputation signals from CIC category reports and SURBL archive observations, then presents TLD-level rates, smoothed trends, and latest-period rankings without requiring a live Python server.

Problem

The original dashboard was a local Streamlit app over SQLite. It worked for exploration, but it could not be shared from a static portfolio or refreshed directly from a copied URL.

Constraints

  • Preserve the existing Python and SQLite calculations as the source of truth
  • Keep Part 4 daily collection separate from the frozen historical snapshot
  • Serve the demo as static Astro output with no browser SQLite or backend service
  • Show missing metrics as unavailable rather than converting them to zero

Approach

Exported the Part 3 SQLite tables into a frozen JSON snapshot and rebuilt the dashboard interactions in Astro and TypeScript. The client applies category, root-zone, confidence, top-N, and manual TLD filters to the complete latest-per-TLD set before rendering the chart and ranking table.

Key Decisions

Export versioned JSON instead of shipping SQLite to the browser

Reasoning:

The dataset is small after aggregation, and JSON keeps the static demo simple, auditable, and compatible with ordinary static hosting.

Alternatives considered:
  • Run Streamlit behind a VPS
  • Load SQLite through WASM
  • Recompute metrics in TypeScript

Use the existing SQLite metrics as authoritative

Reasoning:

EWMA, velocity, acceleration, denominator handling, and category splitting already existed in the pipeline. Reusing those outputs avoids a second implementation with subtle semantic drift.

Alternatives considered:
  • Port the SQL calculations into browser code
  • Export only raw observations

Rank by each TLD's own latest period

Reasoning:

CIC and SURBL coverage differs by TLD. Showing the row period and coverage range avoids pretending every ranking row has the same freshness.

Tech Stack

  • Python
  • SQLite
  • Astro
  • TypeScript
  • SVG

Result & Impact

  • 0
    Runtime services
  • 6,362
    Series rows exported
  • 1,437
    Categorized TLDs

The dashboard can now be reviewed as a local static build, while the original Streamlit and SQLite workflow remains available as the calculation reference.

Learnings

  • A static snapshot is easier to review when it records source digests, row counts, and coverage windows beside the data
  • Latest-period rankings need explicit dates because stale rows can otherwise look directly comparable to current rows
  • Price data is useful context, but mixing it into an abuse score would require a separate normalization and validation design

Snapshot Scope

The interactive demo currently uses the February 4, 2026 Part 3 SQLite database snapshot. It includes the CIC and SURBL rollup tables, TLD category flags, and the parsed price context available in that database. The daily Part 4 scripts are intentionally not connected to this frozen portfolio demo.

Read Deep Dive

GitHub Repository

All projects