Range Day Pro

Data Model

7 min read

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:

MethodFormulaUsed By
hit_factorpoints / timeUSPSA, IPSC (Comstock)
time_plusraw time + penalty secondsIDPA
points_onlysum of points (no time)NRA Bullseye
time_onlyfastest time winsSteel Challenge
pass_failminimum hits within time limitLE/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)
}

Location

A physical place where training happens — a range, facility, or personal address. Can be reused across events.

Location {
  id
  org_id            → Org (nullable — null for personal locations)
  user_id           → User (nullable — set for personal locations)
  name              (e.g. "Eagle Eye Range", "My Backyard")
  address_line_1
  address_line_2    (nullable — suite, gate code, etc.)
  city
  state
  postal_code
  country
  latitude          (nullable — for map display)
  longitude         (nullable)
  notes             (nullable — private instructions: door codes, custom directions)
  visibility        (private | public)
  created_at
  updated_at
}
  • Org-scoped: org_id set, visibility: private — only org members see it
  • Personal: user_id set, no org_id, visibility: private — user’s saved locations
  • Public: visibility: public — well-known ranges, reusable by anyone

Event

A scheduled training session, range day, class, or competition. Belongs to an org, has an owner who is the score authority. Personal range days are events in the user’s personal org with event_type: personal_range_day.

Event {
  id
  org_id            → Org
  owner_id          → User
  event_type        (org_event | personal_range_day)
  name
  location_id       → Location (nullable)
  date_start
  date_end
  status            (draft | active | completed)
  created_at
}

Personal range days use event_drills for selected drills to practice. Rosters and event_courses are optional — a personal range day is just a user, a date, a location, and a list of drills.

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  Location
       │                │      │          │
       │            source?  CourseDrill  ├── EventCourse
       │              │                   ├── EventDrill
       │        ScoringProfile            ├── Roster ── User
       │                                  ├── Location?
       │                                  │
       └──────────────────────── 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_id chains

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.

Graph View

Backlinks