# 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=1` marks 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.