§10 Data Model
Data Model#
FlagForge's schema (identical shape across SQLite/PostgreSQL/MySQL — the dialect only changes
column types, never table/column names) for anyone querying flagforge.db (or the configured
PostgreSQL/MySQL database — see Configuration § storage.yml)
directly, or building an external dashboard against it. Every table is prefixed ff_. Timestamps
are stored as ISO-8601 strings uniformly across every dialect.
This is read-only reference material for direct SQL access; day-to-day use should go through
/ff history//ff finding//ff inspect (see Staff Commands) or
the flagforge-api risk() call instead, which stay stable
across schema migrations in a way raw table access does not.
ff_players#
One row per player ever seen. Never stores an IP address.
| Column | Meaning |
|---|---|
player_uuid (PK) |
The player's UUID. |
last_known_name |
Most recent username seen for this UUID. |
first_seen_at |
When this player was first tracked. |
last_seen_at |
Most recent session activity. |
ff_sessions#
One row per join-to-leave session.
| Column | Meaning |
|---|---|
session_id (PK) |
Session identifier. |
player_uuid (FK → ff_players) |
The session's player. |
server_id |
The config.yml server.id value the session occurred under. |
joined_at |
Session start. |
left_at |
Session end, or NULL while still active. |
Indexed on player_uuid.
ff_findings#
One row per individual detection, the core evidence table. This is what
retention.detailed-evidence-days (see Privacy & Retention)
purges from.
| Column | Meaning |
|---|---|
finding_id (PK) |
Finding identifier — what /ff finding <id> looks up. |
player_uuid, session_id, server_id |
Who/when/where. |
check_id, check_version |
Which check produced this, and its version (for reproducing exactly how a past finding was evaluated even after the check's logic changes). |
category |
The check's category: ECONOMY, INVENTORY, MOVEMENT, COMBAT, WORLD, or PACKET. |
evidence_class |
ANOMALY/HEURISTIC/PROTOCOL_INVALID/SERVER_IMPOSSIBLE. |
occurred_at, observation_sequence |
When, and this observation's sequence number (for ordering findings from the same server run that share a timestamp). Each run counts up from a random starting point, so numbers from different runs or servers can't be compared. |
severity, confidence |
The check's own severity rating and its confidence (0–1) in this specific finding. |
vl_delta, vl_after |
How much violation level this finding added, and the player's running total afterward. |
measured_value, expected_value, unit |
The actual vs. expected value that triggered the finding, with its unit — e.g. a speed check's measured vs. envelope-allowed blocks/second. |
explanation |
Human-readable one-line explanation (what /ff finding and Discord embeds display). |
evidence_json |
The full structured evidence blob — check-specific, richer than the flattened columns above. |
tps, ping_millis, jitter_millis |
Server/connection quality at the moment of the finding — used to compute confidence, and to help staff judge whether lag was a factor. |
game_mode, world |
Player context at the time. |
location_x, location_y, location_z |
Block coordinates (already block-granularity in storage; "coarse location, not exact" in Privacy & Retention refers to what's displayed to staff, since discord.privacy.include-exact-coordinates can independently withhold precision from the Discord embed even though the stored value is already block-level, never sub-block). |
correlation_key |
Groups findings that a correlation rule may match together — what /ff correlations and the incident-level views key on. |
cancellable |
Whether this finding's underlying event was structurally cancellable (see Known Limitations § CANCEL is not yet live through the normal policy pipeline). |
response_ceiling |
The check's own response-ceiling (checks.yml) at the time this finding was recorded. |
alert_eligible |
Whether this finding was eligible to generate a staff alert (independent of whether one actually fired — cooldowns/aggregation/confidence thresholds still apply on top). |
retention_expires_at |
When the scheduled purge task (see storage.yml's retention.check-interval) will delete this row. |
Indexed on (player_uuid, occurred_at), (check_id, occurred_at), correlation_key, and
retention_expires_at.
ff_actions#
One row per action the policy engine attempted, including ones that never actually executed —
this is the honest record behind /ff status's action counters. Not currently purged by anything:
unlike ff_findings, this table has no expiry column, and storage.yml's
retention.ledger-entries-days is parsed but not backed by any purge logic — see
Known Limitations. It grows
unbounded until addressed some other way (e.g. a manual DELETE against old rows).
| Column | Meaning |
|---|---|
action_id (PK) |
Action identifier. |
finding_id, incident_id |
The single finding, or correlated incident, that triggered this action (either may be NULL depending on which triggered it). |
player_uuid |
The target player. |
policy_id |
The actions.yml policy or correlation ID that matched. |
action_type |
One of the four preventable responses ActionDispatcher actually executes: CANCEL, SETBACK, KICK, COMMAND. NOTIFY/MONITOR-ceiling findings never reach this table — they only ever go through the alert pipeline (ff_alert_deliveries, below). |
requested_at, executed_at |
When it was decided on, and when (if ever) it actually ran. |
status |
PENDING (transient, immediately before execution), then one of SUCCEEDED, SUPPRESSED (observe-only/enforcement-off, bypass permission, rate limit, or — for CANCEL — no live trigger yet; see Known Limitations), FAILED. |
sanitized_command |
For COMMAND actions, the command actually run — after placeholder substitution, never containing a raw player-controlled value outside the allowlist (see Configuration § actions.yml). |
result, error |
Outcome detail / error message, if any. |
idempotency_key (unique) |
Prevents the same logical action from executing twice, e.g. on a retry after a partial failure. |
Indexed on player_uuid.
ff_audit_log#
Append-only record of every mutating staff action — what backs the audit-log guarantee mentioned throughout Staff Commands and Privacy & Retention. Not subject to the same retention purge as findings — it's the accountability trail, kept independently.
| Column | Meaning |
|---|---|
audit_id (PK) |
Entry identifier. |
actor |
Who performed the action (a player/console identifier). |
action |
The subcommand invoked, e.g. reload, exempt, purge, enforcement. |
target |
The affected player/entity, if any. |
occurred_at |
When. |
details_json |
Structured detail specific to that action (reason text, scope, old/new values, etc.). |
Indexed on occurred_at.
ff_alert_deliveries#
One row per attempted alert delivery (in-game or Discord), including failed/suppressed attempts —
what /ff discord status summarizes.
| Column | Meaning |
|---|---|
delivery_id (PK) |
Delivery identifier. |
finding_id, incident_id, player_uuid |
What triggered this delivery. |
destination_type |
INGAME or DISCORD. |
routing_policy |
Which alerts.yml delivery-mode/rule routed this. |
aggregation_key |
Groups deliveries combined into one message by aggregate-window. |
requested_at, first_attempt_at, last_attempt_at, delivered_at |
Delivery timing, including retries. |
status |
e.g. DELIVERED, RETRYING, FAILED, SUPPRESSED. |
attempt_count |
Retry attempts so far. |
http_status |
Discord's own HTTP response status, when applicable. |
error_category |
Classified failure reason (transient vs. permanent — see Discord Staff Alerts). |
role_mentioned |
Whether this specific delivery included the configured role mention. |
idempotency_key (unique) |
Prevents double-delivery on retry. |
Indexed on player_uuid.