Schema (synthetic authentication-telemetry warehouse)
SQLite dialect (sql/01_schema.sql); portable to Postgres / DuckDB with minor edits. All data is synthetic; all IPs are RFC1918-private.
Layers
| Layer | Object | Purpose |
|---|---|---|
| raw | auth_events_raw |
Append-only, exactly what sources sent, including bad rows. No PK on event_id on purpose: duplicates must be detectable, not rejected at load. |
| trusted | view auth_events |
One row per event_id, non-null user, canonical outcome, in-window timestamp. All analytics read this, never raw. |
| quarantine | view auth_events_quarantine |
Every raw row the trusted view excludes, with a reason. trusted + quarantine = raw (tested). |
| dimensions | users, resources |
Role, home country, rollout wave; sensitivity, remote-facing flag. |
| evaluation-only | labels_truth, labels_observed, benign_truth, counterfactuals, campaigns_truth |
Ground truth and the censored "observed" subset. Never joined by detector features (a test asserts the feature query does not reference them). |
auth_events_raw columns
| Column | Type | Meaning | Allowed values / notes |
|---|---|---|---|
| event_id | TEXT | Source-assigned event id | should be unique (checked: U1) |
| ts | INTEGER | Event time, epoch seconds UTC | must fall in load window (checked: T_window) |
| event_date | TEXT | YYYY-MM-DD derived from ts |
|
| user_id | TEXT | Account authenticating | NOT NULL expected (C_user_id); FK to users |
| src_ip | TEXT | Source address | RFC1918 in this dataset |
| src_ip_class | TEXT | Enrichment: corp_nat, residential, internal, vpn_commercial, proxy, hosting |
Attackers are not always in proxy/hosting: some use residential proxies |
| src_country | TEXT | Enrichment: ISO country | |
| device_managed | INTEGER | 1 if the device is enrolled in device management | |
| dst_resource | TEXT | Target resource; FK to resources |
|
| dst_sensitivity | TEXT | Denormalised sensitivity: low / medium / high | |
| auth_method | TEXT | password, sso_token, kerberos, service_key, mfa_push, mfa_totp | |
| mfa_required | INTEGER | Policy required MFA for this user/resource/time | |
| mfa_satisfied | INTEGER | 1 satisfied, 0 required-but-not-satisfied, NULL not required | Invariant: success ⇒ not (required ∧ unsatisfied) (L_mfa2) |
| outcome | TEXT | success or failure |
exactly these two; variants are corrupted rows |
| failure_reason | TEXT | bad_password, account_locked, device_not_trusted, mfa_timeout, mfa_enrollment_required, mfa_not_completed, mfa_denied | NULL only for successes (C_reason) |
| blocked_by | TEXT | Control that produced the failure: LOCKOUT, DEVICE_TRUST, MFA, or NULL | Invariant: success ⇒ NULL (L_block) |
| added_latency_ms | INTEGER | Extra user-visible delay from the control | only meaningful for MFA challenges |
| source_system | TEXT | idp, vpn, vdi |
Evaluation-only tables (synthetic)
labels_truth(event_id, campaign_id, attack_type, is_compromise)— every attacker-generated event;is_compromise=1marks events where the attacker gained access, including follow-on lateral movement.labels_observed(event_id, campaign_id, is_compromise)— the subset belonging to campaigns marked confirmed (p = 0.55 per campaign). This is what a real IR team would have: incomplete, and biased toward loud campaigns.benign_truth(event_id, pattern)— legitimate-but-odd behaviour (travel, project bursts, Monday-morning NAT rush) so false positives can be attributed.counterfactuals(user_id, day, wave, enforced, would_succeed_no_mfa, would_succeed_mfa, campaign_id)— for every credential attack that used valid credentials, what would have happened with and without MFA, using common random numbers (one uniform draw decides both). Gives the exact number of compromises averted.
Grain and keys
Event grain = one authentication attempt. Analysis grain = user-day (active user × day). user_id + day is the unit for the DiD and the detection evaluation.