Data Model
ID Convention
All entity IDs are UUID v7 — time-sortable UUIDs where the first 48 bits are a millisecond Unix timestamp and the remainder is random. Stored as Postgres uuid type (16 bytes). UUID v7 provides sequential insert performance (no random B-tree page splits) and rough chronological ordering from the ID alone, while remaining fully compatible with any system expecting standard UUIDs.
Entities
User
Every person in the system — shooters, instructors, admins, ROs.
User {
id
email (nullable — null for unclaimed placeholders)
display_name
status (placeholder | active)
created_at
}
- Active users have an email, auth credentials, and a personal Org created on signup.
- Placeholder users are created on-the-fly when a walk-on shows up at an event without an account. The RO enters a name, scores are recorded against a real user_id, and the data stays regardless of whether the walk-on ever creates an account. If the RO collects an email, an invite is sent — claiming the invite upgrades the placeholder to active (adds email, auth, personal Org). If the walk-on already has an account, the placeholder is merged into their existing account and references are re-pointed.
Org
An organization — a church security team, training company, PD, military unit, or a user’s personal space. Every user has a personal org created automatically on signup.
Org {
id
name
owner_id → User
is_personal (boolean — true for auto-created personal orgs)
created_at
}
OrgMembership
A user’s role within an org. A user can belong to multiple orgs.
OrgMembership {
org_id → Org
user_id → User
role (admin | instructor | ro | member)
joined_at
}
ScoringProfile
A reusable template defining how a drill is scored — what zones exist, how many points each zone is worth, and how the final result is calculated. Built-in profiles cover standard disciplines (USPSA, IDPA, IPSC, Steel Challenge, etc.). Orgs can create custom profiles for their own scoring needs.
ScoringProfile {
id
org_id → Org (nullable — null for built-in profiles)
name (e.g. "USPSA", "IDPA", "Springfield PD Qual")
calculation (hit_factor | time_plus | points_only | time_only | pass_fail)
zones (structured list — see below)
built_in (boolean — true for system-provided profiles)
created_at
updated_at
}
Each zone defines a scoring region:
Zone {
name (e.g. "Alpha", "Charlie", "Delta", "Miss", "No-Shoot")
default_points (integer — e.g. 5, 3, 1, 0, -10)
is_penalty (boolean — true for No-Shoot, Miss penalties)
}
Calculation methods:
| Method | Formula | Used By |
|---|---|---|
hit_factor | points / time | USPSA, IPSC (Comstock) |
time_plus | raw time + penalty seconds | IDPA |
points_only | sum of points (no time) | NRA Bullseye |
time_only | fastest time wins | Steel Challenge |
pass_fail | minimum hits within time limit | LE/Military quals |
Built-in profiles are seeded on first server start and cannot be modified or deleted. They have no org_id. Custom profiles belong to an org and follow the same visibility/copy rules as drills.
Drill
A shooting exercise definition. Always belongs to an org. Visibility controls who can see and use it.
Drill {
id
org_id → Org
owner_id → User
visibility (private | public | published)
source_drill_id → Drill (optional — copy provenance)
scoring_profile_id → ScoringProfile
name
type (live_fire | dry_fire)
description
par_time (optional — target completion time)
total_rounds (optional — round count)
zone_overrides (optional — per-drill point value overrides for specific zones)
created_at
updated_at
}
Drills reference a ScoringProfile for their zone definitions and calculation method. Most drills use the profile’s defaults as-is. zone_overrides allows a drill to change specific zone point values without creating a whole new profile (e.g. a training drill that penalizes misses more heavily).
The type field distinguishes live fire drills from dry fire drills. Dry fire drills typically use a timer with no live ammunition. Both types use the same scoring profile system.
See Multi-Tenant Model for visibility rules and the copy model.
Course
A bundle of drills, ordered. Belongs to an org.
Course {
id
org_id → Org
name
description
created_at
}
CourseDrill
Join table ordering drills within a course.
CourseDrill {
course_id → Course
drill_id → Drill
position (integer — ordering within the course)
}
Event
A scheduled training session, range day, class, or competition. Belongs to an org, has an owner who is the score authority.
Event {
id
org_id → Org
owner_id → User
name
date_start
date_end
status (draft | active | completed)
created_at
}
EventCourse
Courses assigned to an event.
EventCourse {
event_id → Event
course_id → Course
position (integer — ordering within the event)
}
EventDrill
Standalone drills assigned to an event (not part of a course).
EventDrill {
event_id → Event
drill_id → Drill
position (integer — ordering within the event)
}
Roster
Simple membership: is this user in this event?
Roster {
event_id → Event
user_id → User
enrolled_at
}
No RO assignment at this level. See Future: Drill Assignment below.
Score
A recorded result for one shooter on one drill. Append-only — never edited, only replaced.
Score {
id
event_id → Event
drill_id → Drill
shooter_id → User
recorded_by_id → User (the RO or event owner who recorded it)
replaces_score_id → Score (optional — this score replaces/voids the referenced score)
voided (boolean — true if this is a void entry with no replacement values)
drill_name (snapshot — embedded at creation time)
drill_type (snapshot — embedded at creation time)
scoring_profile_name (snapshot — e.g. "USPSA")
calculation (snapshot — hit_factor | time_plus | points_only | time_only | pass_fail)
zone_breakdown (structured — per-zone hit counts, e.g. {alpha: 4, charlie: 1, miss: 0})
time
points
hit_factor (computed — points / time, null if calculation is not hit_factor)
penalties
pass
timestamp
created_at
}
Querying Final Scores
A valid final score is one that is not voided and has not been replaced by another score:
SELECT s.*
FROM scores s
WHERE s.event_id = ?
AND s.voided = false
AND NOT EXISTS (
SELECT 1 FROM scores r
WHERE r.replaces_score_id = s.id
)The same logic in the core crate filters an in-memory vec of locally synced scores — same result on server (SQL), browser (WASM), and mobile (UniFFI).
Relationships
User ──┬── OrgMembership ──── Org
│ │
│ ┌──────┼──────────┬───┐
│ Drill Course Event ScoringProfile
│ │ │ │
│ source? CourseDrill ├── EventCourse
│ │ ├── EventDrill
│ ScoringProfile ├── Roster ── User
│ │
└──────────────────────── Score ───┘
- A User belongs to many Orgs (via OrgMembership with role)
- An Org has many ScoringProfiles, Drills, Courses, and Events
- A Course contains ordered Drills (via CourseDrill)
- An Event uses Courses (EventCourse) and/or standalone Drills (EventDrill)
- A Roster links Users to Events
- Scores belong to an Event, reference a Drill and Shooter, capture who recorded them
- Scores are append-only; corrections and voids use
replaces_score_idchains
Tenant Isolation
Every entity except User has an org_id. All queries filter by org context. A user’s personal cross-org view queries by user_id across org boundaries (read-only on their own scores).
See Multi-Tenant Model for full details.
Future: Drill Assignment
When pre-assignment of ROs to drills/shooters is needed (large events):
DrillAssignment {
event_id → Event
drill_id → Drill
shooter_id → User
ro_id → User
}
Purely additive — no changes to Roster, Score, or any existing entity. The plan (DrillAssignment) and the reality (Score’s recorded_by_id) are separate concerns.