← Back to Blog
Technical Deep Dive December 30, 2025 8 min read

Killboard FOAM Fix: From Random Selection to Deterministic Matching

A community report about a 42 FOAM discrepancy led us down a rabbit hole of PostgreSQL behavior, timestamp precision, and ultimately to a complete rewrite of how we match structure kills to killmails. What we found was a subtle but critical bug: the same database query could return different results on every run, causing player statistics to fluctuate randomly.

The Bug Report

A player reported that their FOAM (Fuel of Autonomous Machines) destroyed total on the killboard showed 418.15, but they believed it should be 376.15—or maybe it was the other way around. The numbers kept changing. This was concerning: FOAM is a key metric in EVE Frontier that measures combat impact, and players use these statistics to track their progress and compare performance.

Our killboard system had been running successfully for weeks, processing over 1,000 kills and attributing FOAM values based on structure types (Small Gates = 43 FOAM, Smart Turrets = 1 FOAM, etc.). The values are derived from matching killmails to structure destruction events that occur within a 60-second window. But apparently, something was wrong with how we were doing that matching.

Understanding the Data Model

To understand the bug, you need to understand how we calculate FOAM. In EVE Frontier, when you destroy a player's ship, any structures they own that are destroyed around the same time get attributed to that kill. We have two data sources:

  1. Kill mails from the blockchain—recording who killed whom, when, and in what system
  2. Structure destruction events—recording which structures were destroyed, when, and who owned them

The challenge is matching these two datasets. A killmail says "Player A destroyed Player B's ship at 12:34:56". A structure destruction event says "Player B's Small Gate was destroyed at 12:34:57". If the times are close (within 60 seconds) and it's the same victim, we attribute that structure's FOAM to the killer.

The Timestamp Precision Problem

Here's where it gets interesting. Killmails have second-level precision—you might have three kills all timestamped at exactly 12:34:56. But structure destructions have millisecond precision—12:34:56.234, 12:34:56.789, etc. When players are rapidly destroying structures, you can have multiple killmails in the same second, each potentially matching multiple structures.

The question becomes: which kill gets credit for which structure?

The Original (Broken) Approach

Our original SQL query used PostgreSQL's DISTINCT ON clause to ensure each kill only matched one structure:

SELECT DISTINCT ON (kill_mail_id)
  km.kill_mail_id,
  dao.assembly_id,
  dao.type_id
FROM kill_mails km
JOIN destroyed_structures dao
  ON dao.owner = km.victim
  AND ABS(km.timestamp - dao.timestamp) < 60 seconds

This looks reasonable—for each kill, pick one matching structure. But there's a critical oversight: we didn't specify an ORDER BY clause.

PostgreSQL DISTINCT ON Behavior

When you use DISTINCT ON (column) without an ORDER BY, PostgreSQL picks an arbitrary row from each group. The database documentation explicitly warns: "The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). The ORDER BY clause will normally contain additional expression(s) that determine the desired precedence of rows within each DISTINCT ON group."

Without ORDER BY, the row selected depends on internal factors like index order, table organization, and query planning—none of which are deterministic across runs.

Why This Caused Fluctuating Values

Consider a scenario: Player A kills Player B at 12:34:56. Player B had two Small Gates destroyed—one at 12:34:56.234 and another at 12:34:56.789. Both are within the 60-second window, so both are valid matches.

Without ORDER BY, PostgreSQL might pick the first gate on Monday's run, but the second gate on Tuesday's run. If another kill from Player C was also matching one of those gates, the FOAM attribution could flip between players randomly.

This explains the 42 FOAM difference reported—the equivalent of one Small Gate (43 FOAM) being attributed differently between runs.

Investigating the Specific Case

We dug into the data for the reported player. They had 211 total kills and 96 kills that matched at least one structure. The structure breakdown showed:

Structure Type Type ID Unique Structures Total Match Candidates
Smart Turret 84556 69 205
Small Gate 88086 6 9
Refinery L 88064 3 3
Hangar L 88094 2 2

The key insight: 6 unique Small Gates, but 9 total match candidates. This means some gates could match multiple kills. The old query was randomly picking which kill got which gate—explaining the fluctuating totals.

The Solution: Greedy 1:1 Matching

Instead of letting the database arbitrarily pick, we implemented a greedy matching algorithm in JavaScript:

  1. Fetch all candidates: Query returns ALL possible (kill, structure) pairs within the 60-second window, sorted by time difference ascending
  2. Process in order: Iterate through the sorted candidates
  3. Match closest first: For each candidate, if neither the kill nor structure has been matched yet, match them together
  4. Mark as used: Track used kills and structures in Sets to prevent double-matching
// Greedy 1:1 matching algorithm
const usedKills = new Set();
const usedStructures = new Set();

// Candidates are pre-sorted by time_diff ascending
for (const row of candidateRows) {
  const killId = row.kill_mail_id;
  const structureId = row.assembly_id;
  
  // Skip if already matched
  if (usedKills.has(killId) || usedStructures.has(structureId)) {
    continue;
  }
  
  // Match this kill to this structure
  usedKills.add(killId);
  usedStructures.add(structureId);
  
  const foamValue = getFoamValue(row.type_id);
  structureKillsMap.set(killId, { foamValue, ... });
}

Why This Works

The greedy approach ensures:

The key insight is that by sorting ALL candidates globally (across all killers) by time difference, we naturally resolve conflicts. If two different players' kills both could match the same structure, the one with the smaller time difference wins.

Verification

After deploying the fix, we ran the exporter multiple times and compared outputs:

Metric Run 1 Run 2 Run 3
Total FOAM 663.65 663.65 663.65
Matched Kills 231 231 231
Candidate Rows 377 377 377
Output Size 332,495 bytes 332,495 bytes 332,495 bytes

Identical results across all runs—exactly what we wanted.

Lessons Learned

Key Takeaways

  • Always ORDER BY with DISTINCT ON: PostgreSQL's behavior without an explicit ORDER BY is undefined and non-deterministic
  • Timestamp precision matters: When joining tables with different precision levels, conflicts can arise that need explicit resolution
  • Move complex matching to application code: SQL is great for filtering and sorting, but for complex 1:1 matching with constraints, procedural code is clearer and more maintainable
  • Test idempotency: Run data processing jobs multiple times and compare outputs—fluctuating results indicate hidden non-determinism

This bug had been present since the original killboard implementation, but went unnoticed because the fluctuations were small enough that they seemed like normal data changes. It took a player carefully tracking their stats to catch it.

The Fix in Production

The fix was deployed with a Docker image backup for easy rollback if needed. The exporter now runs every 5 minutes with consistent, reproducible results. The reported player's FOAM total stabilized at 412.15—not 418.15 or 376.15, but the correct value based on the deterministic matching algorithm.

The slight difference from their expected values came from the global nature of the matching: some structures they could have claimed were actually matched to other players whose kills had a smaller time difference. The greedy algorithm ensures fairness across all players, not just optimal attribution for any single player.

Related Posts

killboardfoampostgresqldeterministicgreedy algorithmsql distinct onstructure killseve frontierdatabase optimization