Data Model¶
This page documents the SQLAlchemy 2.x models that back the CTFHive per-tenant
application (ctfapp/models/). The control plane has its own separate
database (see Control Plane overview).
Relationship overview¶
User ──────┬── TeamMember ──── Team ──── Principal (kind=team)
│
└── Principal (kind=solo)
Principal ─┬── Submission
├── Solve
├── ScoreEvent
├── TeamFlag
└── Instance ──┬── VpnKey
└── PacketCaptureChunk
Challenge ─┬── ChallengeFile
├── DockerImageCache
├── Submission
├── Solve
├── ScoreEvent
├── TeamFlag
├── Instance
├── VpnKey
└── PacketCaptureChunk
Principal is the scoring hub. Every score, solve, flag, and lab instance is owned by a Principal rather than by a User or Team directly, allowing the same code paths to handle both team and solo events.
Encryption¶
Columns marked Enc are stored encrypted using EncryptedString or
EncryptedBinary from ctfapp/models/cipher_wrapper.py.
The cipher is XChaCha20-Poly1305 with Argon2id key derivation. Wire format:
The result is base64url-encoded and stored in a TEXT column. Encryption and
decryption are transparent to SQLAlchemy consumers; the TypeDecorator handles
them on process_bind_param / process_result_value.
The encryption key is resolved at runtime from app.config["ENCRYPTION_KEY"]
(env ENCRYPTION_KEY), falling back to SECRET_KEY. The cipher instance is
lazily initialised on first use and cached for the process lifetime.
Key rotation
There is no automated key-rotation path for encrypted columns. All
ciphertext must be re-encrypted manually if ENCRYPTION_KEY changes.
DBCipher.rotate_key(old_blob, new_key_str) provides the low-level
primitive.
Entities¶
User (users)¶
Core identity record. One row per registered account.
| Column | Type | Enc | Notes |
|---|---|---|---|
id |
Integer PK | ||
public_id |
String(36) | UUIDv4, safe to expose in URLs. | |
username |
String(80) UNIQUE | Stored plaintext (required for unique index). | |
email |
String(255) UNIQUE | Stored plaintext (required for unique index). | |
password_hash |
String(512) | Enc | bcrypt hash, encrypted at rest. |
is_admin |
Boolean | False by default. |
|
state |
String(20) | active | other values. |
|
email_verified_at |
DateTime(tz) | NULL until email link clicked. | |
email_token |
String(128) | One-time email verification token. | |
reset_token |
String(128) | Password-reset token (indexed). | |
reset_token_expires |
DateTime(tz) | Expiry for reset token. | |
totp_secret |
String(128) | Enc | TOTP scaffold; not fully wired in v1. |
created_at |
DateTime(tz) | UTC, set at creation. |
Relationships: team_memberships → [TeamMember], principal → Principal
TeamMember (team_members)¶
Junction table linking users to teams, with a uniqueness constraint on
(team_id, user_id).
| Column | Type | Notes |
|---|---|---|
id |
Integer PK | |
team_id |
Integer FK → teams | |
user_id |
Integer FK → users | |
role |
String(20) | captain | member |
joined_at |
DateTime(tz) | UTC. |
active |
Boolean | Soft-delete flag. |
Team (teams)¶
Represents a competing team. One team maps to one Principal for scoring.
| Column | Type | Notes |
|---|---|---|
id |
Integer PK | |
team_uid |
String(36) UNIQUE | UUIDv4; opaque external ID. |
name |
String(80) UNIQUE | Display name, indexed. |
join_token |
String(16) UNIQUE | URL-safe token for invite links (secrets.token_urlsafe(12)). |
password_hash |
String(255) | Optional; set by captain, checked on join. |
captain_user_id |
Integer FK → users | The founding/current captain. |
max_size |
Integer | Default 4; configurable per team. |
state |
String(20) | active | other values. |
created_at |
DateTime(tz) | UTC. |
Computed properties: member_count (count of active members), is_full.
Principal (principals)¶
The scoring identity abstraction. Unifies team and solo play.
| Column | Type | Enc | Notes |
|---|---|---|---|
id |
Integer PK | ||
kind |
String(10) | team | solo. |
|
public_id |
String(36) UNIQUE | UUIDv4; safe for scoreboards. | |
team_secret |
Binary(32) | Enc (EncryptedBinary) |
32 bytes from os.urandom(32). The per-principal HMAC input for flag derivation. Never expose this. |
team_id |
Integer FK → teams, UNIQUE | Set for kind=team; NULL for solo. |
|
user_id |
Integer FK → users, UNIQUE | Set for kind=solo; NULL for team. |
|
score_total |
Integer | Denormalised; updated on every solve. score_events is the append-only source of truth. |
|
last_solve_at |
DateTime(tz) | Used for tiebreaking. | |
active |
Boolean | False disables participation. |
|
created_at |
DateTime(tz) | UTC. |
Exactly one of team_id / user_id is non-NULL. The UNIQUE constraints on
both foreign keys enforce one principal per team and one per solo user.
Display name (display_name property): team name for kind=team,
username for kind=solo.
Challenge (challenges)¶
One row per challenge. Flags are not stored here; they are derived from
ADMIN_KEY + principal.team_secret + challenge.id.
| Column | Type | Notes |
|---|---|---|
id |
Integer PK | Used as the challenge_id in flag derivation (4-byte big-endian). |
title |
String(200) | |
slug |
String(120) UNIQUE | URL-safe identifier, indexed. |
category_slug |
String(40) | e.g. web, crypto, pwn. Indexed. |
description_md |
Text | Markdown source rendered in UI. |
points |
Integer | Point value. |
flag_type |
String(20) | derived | dynamic. |
flag_prefix |
String(40) | Per-challenge prefix; defaults to DEFAULT_FLAG_PREFIX. |
is_dynamic |
Boolean | True if a container/lab is spawned. |
container_image |
String(255) | Docker image reference (optional). |
container_port |
Integer | Port exposed by the container (optional). |
container_scheme |
String(32) | http | https (default http). |
container_transport |
String(16) | tcp | udp (default tcp). |
container_sha256 |
String(64) | Expected image digest for pinning. |
lab_stack_spec |
Text | JSON ChallengeStackSpec for multi-container labs. |
artifact_generation_status |
String(32) | pending | done | error. |
artifact_generation_message |
Text | Error details if generation failed. |
artifact_generated_at |
DateTime(tz) | When artifact generation completed. |
status |
String(20) | hidden | visible. Only visible challenges are included in flag pre-generation. |
created_at |
DateTime(tz) | UTC. |
Computed: solve_count (count of Solve rows for this challenge).
ChallengeFile (challenge_files)¶
Downloadable attachments linked to a challenge. Cascade-deleted when the parent challenge is deleted.
| Column | Type | Notes |
|---|---|---|
id |
Integer PK | |
challenge_id |
Integer FK → challenges (CASCADE) | |
filename |
String(255) | Display name. |
storage_path |
String(512) | Path relative to uploads/ or an absolute path. |
size |
Integer | Byte count. |
checksum |
String(64) | SHA-256 hex of the file (optional). |
DockerImageCache (docker_image_cache)¶
Index of Docker images that have been pulled or built locally. Used to avoid re-pulling unchanged images.
| Column | Type | Notes |
|---|---|---|
id |
Integer PK | |
image_ref |
String(512) | The requested reference (may include tag). |
image_ref_hash |
String(64) UNIQUE | SHA-256 of image_ref; used as a stable lookup key. |
resolved_ref |
String(512) | The reference after tag resolution. |
image_id |
String(128) | Docker image ID (short or long hash). |
repo_digest |
String(512) | Full digest (repo@sha256:...). |
challenge_slug |
String(120) | Associated challenge (optional). |
built_at |
DateTime(tz) | When cached. |
last_used_at |
DateTime(tz) | Updated on each lab spawn. |
Instance (instances)¶
A running (or recently stopped) container/lab for one principal on one
challenge. Unique per (challenge_id, principal_id).
| Column | Type | Enc | Notes |
|---|---|---|---|
id |
Integer PK | ||
challenge_id |
Integer FK → challenges (CASCADE) | ||
principal_id |
Integer FK → principals (CASCADE) | ||
subdomain |
String(120) UNIQUE | Hostname used for routing. | |
container_id |
String(80) | Docker container ID. | |
lab_lease_id |
String(64) | Lab orchestration lease, indexed. | |
lab_metadata |
Text | JSON teardown metadata for multi-container labs. | |
lab_access_mode |
String(20) | host_port (default) or other access modes. |
|
flag_override |
String(512) | Enc | Non-null only for flag_type=dynamic challenges that generate their own flag at spawn time. |
spawned_at |
DateTime(tz) | ||
expires_at |
DateTime(tz) | Set to now + INSTANCE_TTL_SECONDS. |
|
status |
String(20) | running | stopped | expired. |
Relationships: vpn_keys → [VpnKey], packet_capture_chunks → [PacketCaptureChunk]
VpnKey (vpn_keys)¶
Per-player WireGuard credentials issued when a lab is started. Multiple rows per lease when a team has more than one active player.
| Column | Type | Enc | Notes |
|---|---|---|---|
id |
Integer PK | ||
instance_id |
Integer FK → instances (SET NULL) | Nullable; key may outlive the instance row. | |
principal_id |
Integer FK → principals (CASCADE) | ||
challenge_id |
Integer FK → challenges (CASCADE) | ||
lease_id |
String(64) | Matches Instance.lab_lease_id. |
|
player_index |
Integer | 0-based index within the team (up to LAB_VPN_PLAYERS_PER_TEAM). |
|
name |
String(80) | Human-readable label. | |
vpn_ip |
String(45) | Assigned IP within LAB_VPN_PEER_POOL_CIDR. |
|
private_key |
String(256) | Enc | WireGuard private key. |
public_key |
String(128) UNIQUE | WireGuard public key. | |
config |
String(4096) | Enc | Full wg-quick config block. |
server_allowed_ips |
Text | JSON list of CIDRs allowed through the VPN. | |
status |
String(20) | active | revoked. |
|
created_at |
DateTime(tz) | ||
revoked_at |
DateTime(tz) | Null until revoked. |
Submission (submissions)¶
Every flag submission attempt, whether correct or wrong.
| Column | Type | Enc | Notes |
|---|---|---|---|
id |
Integer PK | ||
principal_id |
Integer FK → principals | Indexed. | |
challenge_id |
Integer FK → challenges | Indexed. | |
flag_submitted_hash |
String(256) | Enc | sha256(raw_submitted_flag) stored encrypted. The raw flag is never persisted. |
result |
String(10) | correct | wrong. |
|
ip |
String(256) | Enc | Submitter IP address. |
user_agent |
String(1024) | Enc | Submitter user agent. |
created_at |
DateTime(tz) | Indexed. |
Solve (solves)¶
One row per correct first solve. Unique on (principal_id, challenge_id) —
a principal cannot solve the same challenge twice.
| Column | Type | Notes |
|---|---|---|
id |
Integer PK | |
principal_id |
Integer FK → principals | Indexed. |
challenge_id |
Integer FK → challenges | Indexed. |
points_awarded |
Integer | The point value at solve time (may differ from current Challenge.points if scoring changed). |
solved_at |
DateTime(tz) | UTC. |
ScoreEvent (score_events)¶
Append-only ledger of all point deltas. Principal.score_total is the
denormalised sum; this table is the authoritative record.
| Column | Type | Notes |
|---|---|---|
id |
Integer PK | |
principal_id |
Integer FK → principals | Indexed. |
challenge_id |
Integer FK → challenges | Nullable (admin adjustments may not be challenge-linked). |
delta |
Integer | Positive (award) or negative (penalty/adjustment). |
reason |
String(255) | Human-readable description, e.g. "solve", "admin_adjust". |
created_at |
DateTime(tz) | UTC. |
TeamFlag (team_flags)¶
Durable DB mirror of the Redis team_flags:{principal_id} hash. Unique on
(principal_id, challenge_id).
| Column | Type | Enc | Notes |
|---|---|---|---|
id |
Integer PK | ||
principal_id |
Integer FK → principals | Indexed. | |
challenge_id |
Integer FK → challenges | Indexed. | |
flag_value |
String(512) | Enc | The full derived flag string, e.g. GRIZZ{a3f7...}. |
See Flag Derivation for the derivation algorithm.
EventLog (event_log)¶
The tamper-evident application audit log. See Audit Chain for the full HMAC scheme.
| Column | Type | Notes |
|---|---|---|
id |
Integer PK | |
type |
String(60) | Event code, indexed. |
severity |
String(10) | INFO | WARNING | CRITICAL. |
actor_user_id |
Integer FK → users | Nullable. |
principal_id |
Integer FK → principals | Nullable. |
challenge_id |
Integer FK → challenges | Nullable. |
payload_json |
Text | Sorted-key JSON context. |
prev_sig |
String(64) | Previous entry's HMAC. |
sig |
String(64) | This entry's HMAC. |
created_at |
DateTime(tz) | UTC, indexed. |
AppSetting (app_settings)¶
Key-value store for admin-configurable runtime settings. Cached in memory (5-minute TTL) for fast reads.
| Column | Type | Notes |
|---|---|---|
key |
String(80) PK | Setting name. |
value |
Text | String value; use get_setting_bool() / get_setting_int() helpers. |
description |
String(255) | Human-readable description shown in admin panel. |
Seeded defaults (from models/settings.py):
| Key | Default | Description |
|---|---|---|
max_team_size |
4 |
Maximum members per team |
active_theme |
cyberpunk |
Active visual theme ID |
active_layout |
terminal-core |
Active UI layout preset ID |
active_font_pack |
terminal-default |
Active font pack preset ID |
ui_density |
spacious |
compact | cozy | spacious |
ui_motion |
full |
full | reduced |
default_bg_animation |
matrix |
Default background animation |
allow_solo_play |
true |
Allow solo (non-team) participation |
allow_team_switch |
true |
Allow players to switch from solo to team |
registration_open |
true |
Whether new registration is open |
draining_workers |
[] |
JSON list of worker IDs excluded from new lab assignments |
PacketCaptureChunk (packet_capture_chunks)¶
Encrypted packet-capture chunks for one lab lease / player / direction.
Requires LAB_PACKET_CAPTURE_ENABLED=true.
| Column | Type | Notes |
|---|---|---|
id |
Integer PK | |
instance_id |
Integer FK → instances (SET NULL) | Nullable. |
principal_id |
Integer FK → principals (CASCADE) | Indexed. |
challenge_id |
Integer FK → challenges (CASCADE) | Indexed. |
lease_id |
String(64) | Matches Instance.lab_lease_id. |
player_index |
Integer | Player within the team. |
vpn_ip |
String(45) | Player's VPN IP at capture time. |
direction |
String(12) | ingress | egress. |
chunk_index |
Integer | Sequential chunk counter per lease/player/direction. |
packet_count |
Integer | Number of packets in this chunk. |
raw_size_bytes |
Integer | Uncompressed size. |
stored_size_bytes |
Integer | After compression. |
codec |
String(64) | Default: xchacha20poly1305+gzip+base64. |
payload_b64 |
Text | Base64-encoded ciphertext. |
started_at |
DateTime(tz) | Capture window start. |
ended_at |
DateTime(tz) | Capture window end. |
created_at |
DateTime(tz) | Row creation time. |