← Back to Blog
Feature Announcement December 3, 2025 10 min read

Killboard Implementation: Tracking PvP Activity Across EVE Frontier

What if you could see exactly who's hunting in your region, which tribes dominate PvP, and where the hottest combat zones are—all from a single dashboard? That's what we built with the EVE Frontier Map killboard, a comprehensive PvP tracking system that aggregates kill mail data from the blockchain into actionable intelligence.

This article walks through the entire journey: from investigating the Postgres schema to deploying a production killboard with time-based filtering, tribe leaderboards, and system heatmaps. Along the way, we hit several hurdles—schema mismatches, unique killer counting bugs, and an auto-refresh issue that threatened to blow through our Cloudflare KV quota.

The Starting Point: What We Had

EVE Frontier's blockchain-based World contract emits kill mail events whenever a player destroys another player's ship. These events are decoded by our Primordium indexer and stored in a Postgres table called evefrontier__kill_mail. Each record contains:

At the time of implementation, we had 4,390 kill mails in the database—enough data to build meaningful leaderboards and activity patterns.

Architecture Decision: Snapshot to KV

We faced a choice: should the frontend query Postgres directly via a Worker endpoint, or should we pre-compute aggregates and cache them?

We chose the snapshot approach, following the pattern we'd established for Smart Assemblies and live events. A Docker cron job polls Postgres every 5 minutes, generates a comprehensive JSON snapshot, and publishes it to Cloudflare KV.

Why Snapshots Over Direct Queries?

Cloudflare Workers have a 50ms CPU time limit. Complex SQL aggregations with character name joins would exceed this. Pre-computing everything in a Node.js container lets us run 2-3 second queries without timing out, and the snapshot is served from KV's edge cache in ~5ms.

The Data Pipeline

Postgres (Primordium)               Cloudflare KV
┌─────────────────────┐            ┌──────────────────────┐
│ evefrontier__       │            │ EF_SNAPSHOTS         │
│   kill_mail         │──[export]─►│   killboard_v1       │
│ evefrontier__       │  (5-min)   │                      │
│   characters        │            └──────────┬───────────┘
│ world_api_dlt.      │                       │
│   smartcharacter    │                       ▼
│   tribe             │            ┌──────────────────────┐
└─────────────────────┘            │ Cloudflare Worker    │
                                   │ /api/killboard-...   │
                                   └──────────┬───────────┘
                                              │
                                              ▼
                                   ┌──────────────────────┐
                                   │ React Frontend       │
                                   │ KillboardPanel.tsx   │
                                   └──────────────────────┘

Building the Snapshot Exporter

The exporter (tools/worldapi-cron/killboard_exporter.js, 746 lines) runs seven parallel time-windowed queries for each aggregate type. We compute leaderboards for:

Each time period generates three leaderboards: top players by kills, top tribes by kills, and top systems by kill count. We also maintain character and tribe indexes with per-period breakdowns for search functionality.

The Unique Killers Bug

Our first hurdle appeared in the Tribes tab. A tribe showed uniqueKillers: 0 despite having 1,500+ kills. The bug was subtle: we were aggregating kills correctly, but the SQL for counting unique killers was joining on the wrong column.

-- Before (buggy): counted unique kill_mail_ids
SELECT COUNT(DISTINCT km.kill_mail_id) AS unique_killers

-- After (fixed): counted unique killer character IDs
SELECT COUNT(DISTINCT km.killer_character_id) AS unique_killers

Browser testing caught this immediately—the top tribe (WOLF) should have had 17 unique killers, not 0.

Frontend Implementation: Three Tabs

The KillboardPanel.tsx component (1,092 lines added) presents data in three views:

Tab Content Sorting
Players Top 50 by kills, with K/D ratio, deaths, tribe affiliation Kills (desc), then K/D
Tribes Top 50 by kills, with member count, unique killers, deaths Kills (desc)
Systems Top 50 hottest systems by kill count Kills (desc)

All tabs respond to the time period slider. Selecting "Last 24h" filters all three views to show only activity from the past day. A search box filters player/tribe names in real-time (client-side filtering against the snapshot).

Tribe Filtering Integration

We added an extra feature: tribe dropdown filtering. If you select a tribe from the dropdown (synced with the main Smart Assemblies tribe filter), the killboard filters to show only that tribe's members and their activity. This lets tribe leaders see their own PvP statistics without scrolling through global leaderboards.

The Auto-Refresh Disaster

Here's where we almost made a costly mistake. The initial implementation included a 5-minute auto-refresh interval:

// Initial (problematic) implementation
useEffect(() => {
  if (!visible) return;
  fetchData();
  const interval = setInterval(() => fetchData(), 5 * 60 * 1000);
  return () => clearInterval(interval);
}, [visible, fetchData]);

This seemed harmless—refresh every 5 minutes to pick up new kills. But the problem became clear during testing:

The KV Read Problem

Every user with the killboard panel visible triggers a KV read every 5 minutes. With 100 concurrent users, that's 1,200 reads/hour, or 28,800 reads/day—just from the killboard. Cloudflare's free tier includes 100,000 reads/day, and we have multiple other features hitting KV. Auto-refresh would burn through our quota.

The fix was simple: remove the interval entirely. Users can click the refresh button when they want fresh data. The 5-minute exporter cadence already ensures data is reasonably current.

// Fixed implementation
useEffect(() => {
  if (!visible) return;
  fetchData();
}, [visible, fetchData]);

Browser Testing Results

After deployment to a preview URL, we verified all functionality:

What's Next: Map Visualization

The current implementation is complete for leaderboard display. Future enhancements include:

These are marked as stretch goals—the core killboard functionality ships now, and we'll iterate on visualization features based on community feedback.

Technical Summary

Component Lines Added Key Files
Snapshot Exporter 746 killboard_exporter.js
Worker API 138 _worker.js additions
Frontend Panel 1,092 KillboardPanel.tsx + CSS

Total implementation time: approximately 4 hours across two sessions, including schema investigation, frontend iteration, and the auto-refresh fix. The vibe coding methodology continues to prove effective for full-stack feature development.

Related Posts

killboardpvp trackingblockchain indexingkill mailcloudflare kvdocker croneve frontierleaderboardstribe statistics