Shared reference for aligning with Vincenzo & Alex. Covers the quest entities, the player-tier / audience / badge layer, and how both bolt onto the recording spine we already have. Full detail in quests-feature-design.md.
The thread used "group" two ways. We split it into four precise terms so we all mean the same thing:
The people recording together for one attempt — the live squad, bound to a single game. "Group of 5" / MinPeople is the party. Uses the existing lobbies table.
Who a quest is offered to — a reusable targeting rule, not a squad. A quest can link many audiences, combined as ALL (AND) or ANY (OR). "All"/"Reliable" are audiences.
A player's progression level — ordered, earned, gamified (Rookie → Reliable → Pro). One current tier per player. Most audiences filter on tier.
Orthogonal achievement on top of tier — "reliable player", "top hardware", "best driver". A player holds many; can also gate an audience.
erDiagram
QUESTS ||--o{ QUEST_AUDIENCES : "match all|any"
AUDIENCES ||--o{ QUEST_AUDIENCES : "offered to"
QUESTS ||--o{ QUEST_GAMES : "allowed games"
ELIGIBLE_GAMES ||--o{ QUEST_GAMES : ""
QUESTS ||--o{ QUEST_REWARDS : "rewards"
BADGES ||--o{ QUEST_REWARDS : "badge reward"
QUESTS ||--o{ QUEST_PARTICIPATIONS : "attempts"
PLAYROLL_USERS ||--o{ QUEST_PARTICIPATIONS : "by player"
LOBBIES ||--o{ QUEST_PARTICIPATIONS : "in party"
PLAYER_TIERS ||--o{ PLAYER_TIER_STATE : "current tier"
PLAYROLL_USERS ||--|| PLAYER_TIER_STATE : "has"
PLAYROLL_USERS ||--o{ PLAYER_BADGES : ""
BADGES ||--o{ PLAYER_BADGES : "held by"
QUESTS ||--o{ LOBBIES : "quest_id (party)"
ELIGIBLE_GAMES ||--o{ LOBBIES : "bound game"
LOBBIES ||--o{ RECORDING_SESSIONS : ""
QUESTS ||--o{ RECORDING_SESSIONS : "quest_id (attribution)"
RECORDING_SESSIONS ||--o{ S3_UPLOADS : "validated · trigger gate"
QUESTS {
uuid id PK
text title
text objective
timestamptz starts_at
timestamptz ends_at
int min_party
int max_party
enum status
enum reward_phase "keys|cash"
enum audience_match "all|any"
}
AUDIENCES {
uuid id PK
text key "all|reliable"
jsonb rule "min_tier / has_badge"
}
QUEST_AUDIENCES {
uuid quest_id FK
uuid audience_id FK
}
QUEST_GAMES {
uuid quest_id FK
text game_slug FK
}
QUEST_REWARDS {
uuid quest_id FK
enum kind "xp|cash|game_key|badge|tier_progress"
numeric amount
uuid award_badge_id FK
jsonb speed_bonus "fast-delivery bonus"
}
QUEST_PARTICIPATIONS {
uuid quest_id FK
text user_id FK
uuid lobby_id FK
timestamptz joined_at
timestamptz first_validated_at
bigint validated_seconds
enum status
}
PLAYER_TIERS {
text key PK "rookie|reliable|pro"
int rank
jsonb thresholds
}
PLAYER_TIER_STATE {
text user_id PK
text tier_key FK
numeric responsiveness_p50_h
numeric validated_hours
numeric completion_rate
}
BADGES {
uuid id PK
text key
jsonb criteria
}
PLAYER_BADGES {
text user_id FK
uuid badge_id FK
}
PLAYROLL_USERS {
text id PK
}
ELIGIBLE_GAMES {
text slug PK
}
LOBBIES {
uuid id PK
text game_slug FK
uuid quest_id FK
}
RECORDING_SESSIONS {
text session_id PK
uuid lobby_id FK
uuid quest_id FK
text game_slug
text uploadability_status
}
S3_UPLOADS {
text session_id FK
uuid lobby_id FK
text game_slug
}
Note: an audience's rule references tiers/badges by value (JSON), not a hard FK, so audiences stay flexible — shown by the min_tier/has_badge annotations rather than a drawn edge.
Marco's first tier metric and the org-level question "if we require custom-sourced data, what's the timeline range to execute?" are the same number: lead time from a quest going live to validated data arriving. It's derived from quest_participations, not stored as opinion:
Start-time is the headline metric (matches Marco's definition + answers the org question); join-time is kept as a fairness split. Roll per player → player_tier_state.responsiveness_p50_h (drives tier). Roll per quest / audience → the timeline-range answer for Vincenzo & Alex. validated_seconds also feeds the footer bar (replacing uploaded-hours).
| Entity | Domain | One-liner |
|---|---|---|
| quests | quest | The mission: title, objective, games, window, party size, audience, reward phase. Auto id + slug; stamped onto sessions. |
| quest_games | quest | Which games a quest allows (M:N to eligible_games). A party picks one of these as its bound game. |
| quest_rewards | quest | What you get: game key (pre-training) / cash (post-training) / XP / badge / tier progress, plus optional fast-delivery speed bonus. |
| quest_participations | quest | Fact table — one row per player per attempt. Source of lead time, validated hours, completion. Backbone of all metrics. |
| audiences / quest_audiences | player | Reusable targeting rules ("All", "Reliable", "Top drivers"). A quest links one or many; quests.audience_match combines them as ALL (AND) or ANY (OR). Each rule keys off tier / badge / region. |
| player_tiers | player | The ordered progression ladder + promotion thresholds. Marco owns the values. |
| player_tier_state | player | Each player's current tier + the live metrics that drive it (responsiveness, validated hours, completion rate). |
| badges / player_badges | player | Orthogonal achievements catalogue, and who holds each. Can be awarded by quests or auto-criteria. |
| lobbies / lobby_participants | spine | Existing. The party — gains a quest_id. Already binds to one game and enforces it. |
| recording_sessions | spine | Existing. Gains quest_id (immutable attribution). uploadability_status drives validation. |
| s3_uploads | spine | Existing. A BEFORE-INSERT trigger already rejects any upload whose game ≠ the bound game — the hard boundary, extended for quests. |
Open items for this thread: whether post-training hours count in the footer bar, and initial tiers & promotion thresholds (Marco). Resolved since v1: responsiveness anchor (start-time primary) and multi-audience ALL/ANY. Detail in §12 of the design doc.