A SQL analysis lab for the core questions of ban-evasion enforcement: how often banned actors return, how fast they return, which identity-linking signals catch them, and whether deployed controls actually reduce return rates. It runs on a synthetic account and event dataset (millions to tens of millions of rows, DuckDB) that retains ground-truth actor identity, so every detection claim can be honestly audited for precision and recall. Production enforcement teams can rarely do that, because in production nobody hands you the answer key.
This lab takes two premises seriously. First, a ban an actor can evade in five minutes is not enforcement, it is friction. Second, in severe-harm enforcement, the cost of a false positive is asymmetric. A missed return is unacceptable, and so is banning an innocent user on a noisy signal. Every analysis here is shaped by that tension.
scripts/generate_data.py simulates an 18-month platform window (January 2025 through June 2026) with adversarial users. Actors carry a sophistication level that governs how aggressively they rotate identity signals (device fingerprint, payment instrument, IP subnet, email naming pattern) when they return after a ban. Ban reasons carry severity tiers, with child-safety enforcement as the highest-severity population. On January 1, 2026, a registration-time control deploys: device-fingerprint reuse by severe-ban populations blocks re-registration, forcing device rotation. Because rotation costs effort, some actors stop returning, which creates a measurable natural experiment.
Three scales are available: small (about 70K accounts and 830K events, seconds to generate), medium (about 290K accounts and 4.6M events), and full (1.04M accounts and 49.8M events, sized for a 64GB workstation). Ground truth (actor_id) is written to the dataset but is never used by any deployable detection rule. Only the audit queries that grade those rules are allowed to consult it.
01. Signal precision and recall. For each linking signal, the account pairs it links are graded against ground truth. Results at full scale (1,043,028 accounts and 49,794,842 events):
| signal | precision | recall |
|---|---|---|
| device_fp + payment_fp (compound) | 1.000 | 0.124 |
| device_fp | 0.849 | 0.204 |
| payment_fp | 0.809 | 0.252 |
| email_pattern | 0.062 | 0.391 |
| ip_subnet | 0.007 | 0.445 |
No single signal is both precise and complete. A rule built on subnet alone would link over 20M account pairs at 0.7% precision, an enforcement catastrophe. A rule requiring a compound device and payment match never wrongly links anyone but misses 88% of true multi-accounting. This is the precision and recall tradeoff that has to be reasoned about explicitly rather than assumed away.
02. Evasion cluster linking. Connected components over high-precision edges (device or payment reuse), built through iterative label propagation in pure SQL. At full scale: 77,324 multi-account clusters, average purity 0.83, 65% fully pure, largest cluster 14 accounts, and a fragmentation cost of 1.93 clusters per true actor, which is the price of refusing low-precision edges. Purity and fragmentation are the two failure directions of any linking system. Group too eagerly and innocent accounts get swept in. Group too cautiously and one actor looks like several.
03. The recidivism measurement framework. Per ban cohort: return rate (ground truth), time to return, and detection rate under a deployable rule, meaning the successor shares a device or payment fingerprint with any previously banned account and no ground truth is consulted. At full scale, return rates run 51% to 56% across cohorts, median time to return is 67 to 70 hours, and the fastest decile of evaders is back within 10 to 11 hours of the ban. The fingerprint-reuse rule catches 54% to 61% of returns, meaning roughly four in ten returning actors, disproportionately the sophisticated ones, walk back in undetected by fingerprints alone.
04. Control effectiveness. The registration-time control cut the severe-population 30-day successful return rate from 0.551 to 0.461, a 16% relative reduction, while blocking 22.4% of return attempts at the gate. The sophistication breakdown is the real finding: low-sophistication actors collapsed from 0.344 to 0.122 (a 64% drop), medium fell from 0.595 to 0.465, and high-sophistication actors barely moved (0.853 to 0.837) because they rotate devices and absorb the cost. The control works exactly where cheap evasion lives, and the residual risk concentrates in the actors most capable of causing harm. That is the brief a policy team needs: this control is worth keeping, and it is not sufficient.
pip install -r requirements.txt
python scripts/generate_data.py --scale medium
python scripts/run_analysis.py
Results print to stdout and are written to results/ as markdown. All queries are plain DuckDB SQL in sql/ and run unmodified at any scale.
Synthetic data encodes the modeler's assumptions. Real evasion behavior is messier, and real signals drift. The value of the synthetic approach is the audit: because ground truth exists, every precision and recall number here is exact rather than estimated, and the queries that compute them transfer directly to production datasets, where ground truth arrives later and only partially, through appeals, chargebacks, and confirmed re-identifications. Deployable-rule detection rates here should be read as mechanics demonstrations, not benchmarks.
Built by Meca Dismukes (github.com/Mecapixel) as part of a portfolio in Trust & Safety and abuse investigation, alongside WhisperWard, a child-safety OSINT investigation platform, and Warden, a zero-trust security runtime for AI agents published to PyPI as warden-security.