Database Schema

This document outlines the database schema used in Rapua. The application uses SQLite with the Bun ORM to manage and query data.

Schema Overview

┌───────────────┐        ┌────────────────┐        ┌─────────────┐
│    Instance   │        │    Location    │        │   Marker    │
├───────────────┤        ├────────────────┤        ├─────────────┤
│ id (PK)       │───┐    │ id (PK)        │        │ code (PK)   │
│ name          │   │    │ name           │        │ lat         │
│ user_id       │   │    │ instance_id    │────────│ lng         │
│ is_template   │   │    │ marker_id      │───┐    │ name        │
│ template_id   │   │    │ content_id     │   │    │ total_visits│
│ start_time    │   └────│ criteria       │   │    │ current_count
│ end_time      │        │ order          │   │    │ avg_duration│
└───────────────┘        └────────────────┘   │    └─────────────┘
        │                        │            │            
        │                        │            │            
        │                        │            │            
┌───────┴───────┐        ┌──────┴───────┐    │     ┌─────────────┐
│InstanceSettings│        │    Block     │    │     │   CheckIn   │
├───────────────┤        ├──────────────┤    │     ├─────────────┤
│ instance_id(PK)│        │ id (PK)      │    │     │ team_code   │
│ navigation_mode│        │ location_id  │    │     │ location_id │
│ navigation_meth│        │ type         │    │     │ time_in     │
│ max_next_loc   │        │ data         │    │     │ time_out    │
│ completion_meth│        │ ordering     │    │     │ must_check_out
│ show_team_count│        │ points       │    │     │ points      │
│ enable_points  │        │ validation_req│    │     └─────────────┘
└───────────────┘        └──────────────┘    │             │
        │                        │            │             │
        │                        │            │             │
┌───────┴───────┐        ┌──────┴───────┐    │     ┌───────┴─────┐
│     Team      │        │TeamBlockState │    │     │    Clue     │
├───────────────┤        ├──────────────┤    │     ├─────────────┤
│ code (PK)     │────────│ team_code    │    │     │ id (PK)     │
│ name          │        │ block_id     │    │     │ instance_id  │
│ instance_id   │        │ is_complete  │    │     │ location_id  │
│ has_started   │        │ points_awarded│    │     │ content     │
│ must_scan_out │────────┤ player_data  │    │     └─────────────┘
│ points        │        └──────────────┘    │             
└───────────────┘                           │             
        │                                   │             
        │                                   │             
┌───────┴───────┐                          │      ┌─────────────┐
│  Notification  │                          │      │    User     │
├───────────────┤                          │      ├─────────────┤
│ id (PK)       │                          │      │ id (PK)     │
│ content       │                          │      │ name        │
│ type          │                          │      │ email       │
│ team_code     │                          │      │ password    │
│ dismissed     │                          │      │ provider    │
└───────────────┘                          │      └─────────────┘
                                          │              │
                                          │              │
┌─────────────┐           ┌───────────────┴─┐    ┌──────┴──────┐
│ FacilitatorToken│        │   ShareLink     │    │   Upload    │
├─────────────┤           ├─────────────────┤    ├─────────────┤
│ token (PK)  │           │ id (PK)         │    │ id (PK)     │
│ instance_id │           │ template_id     │    │ user_id     │
│ created_by  │           │ created_by      │    │ filename    │
│ expires_at  │           │ name            │    │ size        │
└─────────────┘           └─────────────────┘    │ content_type│
                                                 └─────────────┘

Tables

Instance

The central table representing a game instance or template.

Field	Type	Description
id	string	Primary key, unique identifier
name	string	Name of the instance
user_id	string	ID of the user who owns this instance
is_template	bool	Whether this instance is a reusable template
template_id	string	ID of the template this instance was created from (if any)
start_time	time	When the game instance is scheduled to start
end_time	time	When the game instance is scheduled to end
is_quick_start_dismissed	bool	Whether the quickstart guide has been dismissed

InstanceSettings

Settings that control how a game instance works.

Field	Type	Description
instance_id	string	Primary key, references instances.id
navigation_mode	int	How players navigate (linear, free choice, etc.)
navigation_method	int	Method of navigation (map, list, etc.)
max_next_locations	int	Maximum number of locations shown in the “next” view
completion_method	int	How location completion is determined
show_team_count	bool	Whether to show the number of teams at each location
enable_points	bool	Whether points are enabled for this game
enable_bonus_points	bool	Whether bonus points are enabled
show_leaderboard	bool	Whether to show the leaderboard to players

Location

A location or station in a game.

Field	Type	Description
id	string	Primary key, unique identifier
name	string	Name of the location
instance_id	string	Foreign key to instances.id
marker_id	string	Foreign key to markers.code
content_id	string	Content identifier (historical)
criteria	string	Criteria for unlocking this location
order	int	Order in which this location appears
total_visits	int	Total number of team visits
current_count	int	Current number of teams at this location
avg_duration	float	Average time teams spend at this location
completion	int	How completion is determined for this location
points	int	Points awarded for visiting this location

Marker

Physical markers that players scan to check into locations.

Field	Type	Description
code	string	Primary key, unique location code (typically 5 characters)
lat	float	Latitude coordinate
lng	float	Longitude coordinate
name	string	Name of the marker
total_visits	int	Total number of visits to this marker
current_count	int	Current number of teams at this marker
avg_duration	float	Average time teams spend at this marker

Block

Content blocks that make up a location’s interactive elements.

Field	Type	Description
id	string	Primary key, unique identifier
location_id	string	Foreign key to locations.id
type	string	Block type (markdown, image, pincode, etc.)
data	json	Block-specific data in JSON format
ordering	int	Display order at the location
points	int	Points that can be awarded for this block
validation_required	bool	Whether validation is required to complete this block

TeamBlockState

Tracks the state of blocks for each team.

Field	Type	Description
team_code	string	Part of composite primary key, references teams.code
block_id	string	Part of composite primary key, references blocks.id
is_complete	bool	Whether the team has completed this block
points_awarded	int	Points awarded to the team for this block
player_data	json	Player-specific data for this block in JSON format

Team

A team of players participating in a game instance.

Field	Type	Description
code	string	Primary key, unique team code
name	string	Team name
instance_id	string	Foreign key to instances.id
has_started	bool	Whether the team has started the game
must_scan_out	string	Marker code that the team must scan to check out (if any)
points	int	Total points earned by the team

CheckIn

Records when teams check in and out of locations.

Field	Type	Description
team_code	string	Part of composite primary key, references teams.code
location_id	string	Part of composite primary key, references locations.id
instance_id	string	Foreign key to instances.id
time_in	time	When the team checked in
time_out	time	When the team checked out
must_check_out	bool	Whether check-out is required
points	int	Points awarded for this check-in
blocks_completed	bool	Whether all blocks at this location have been completed

Clue

Hints or clues about locations.

Field	Type	Description
id	string	Primary key, unique identifier
instance_id	string	Foreign key to instances.id
location_id	string	Foreign key to locations.id
content	string	The clue content

Notification

Messages sent to teams during gameplay.

Field	Type	Description
id	string	Primary key, unique identifier
content	string	Notification content
type	string	Notification type
team_code	string	Foreign key to teams.code
dismissed	bool	Whether the notification has been dismissed

User

User accounts for game administrators.

Field	Type	Description
id	string	Primary key, unique identifier
name	string	User’s name
email	string	User’s email (unique)
email_verified	bool	Whether the email has been verified
email_token	string	Token for email verification
email_token_expiry	time	When the email token expires
password	string	Hashed password
provider	string	Authentication provider (if using OAuth)
current_instance_id	string	ID of the currently active instance

FacilitatorToken

Tokens that allow facilitators to access game instances.

Field	Type	Description
token	string	Primary key, unique token
instance_id	string	Foreign key to instances.id
created_by	string	User ID of the creator
expires_at	time	When the token expires

Upload

Uploaded files (images, etc.)

Field	Type	Description
id	string	Primary key, unique identifier
user_id	string	Foreign key to users.id
filename	string	Original filename
size	int	File size in bytes
content_type	string	MIME type of the file

ShareLink

Links that allow sharing templates.

Field	Type	Description
id	string	Primary key, unique identifier
template_id	string	Foreign key to instances.id (where is_template=true)
created_by	string	User ID of the creator
name	string	Display name for the share link

Key Relationships

Instance to Locations: One-to-many. Each game instance has multiple locations.
Location to Blocks: One-to-many. Each location has multiple content blocks.
Instance to Teams: One-to-many. Each game instance has multiple teams.
Team to CheckIns: One-to-many. Teams can check in to multiple locations.
Location to Marker: Many-to-one. Multiple locations can use the same marker.
Team to TeamBlockState: One-to-many. Teams have state for each block they interact with.
User to Instances: One-to-many. Users can create multiple game instances.
Instance to Template: Many-to-one. Many instances can be created from one template.
Template to ShareLinks: One-to-many. A template can have multiple share links.

Database Indexes

The schema maintains indexes on all primary keys and foreign key relationships to ensure quick lookups. Notable indexes include:

team_code and block_id in TeamBlockState (composite primary key)
team_code and location_id in CheckIn (composite primary key)
instance_id in Location (for finding all locations in a game)
marker_id in Location (for finding locations by marker code)
location_id in Block (for finding all blocks at a location)

Enumerations

The database uses several enum types implemented as integers:

RouteStrategy:
- Controls how the next location is chosen for a player.
NavigationDisplayMode:
- Determines what method players use to find locations.
CompletionMethod:
- How location completion is determined (all blocks, specific blocks, etc.)
GameStatus:
- Represents game states: Closed, Scheduled, Active.

Content Blocks

Guides