Data Model

Parent: Aligner Tracker Api

---

Entity Relationship Diagram

erDiagram

User ||--o{ Journey : "has"

User ||--o{ ProgressEvent : "has"

User ||--o{ UserImage : "uploads"

User ||--o{ Notification : "receives"

Journey ||--o{ ProgressEvent : "contains"

Journey ||--o{ UserImage : "contains"

Journey ||--o{ Notification : "contains"

ProgressEvent ||--o| Notification : "schedules next"

  

User {

int id PK

datetime created_at

datetime updated_at

string email UK

string first_name

string last_name

string postal_code

int jarvis_id

string hubspot_id UK

int assigned_dentist_id

bool has_pending_dentist_change

bool terms_accepted

string cognito_sub_identifier UK

}

  

Journey {

int id PK

int user_id FK

string product

string status

int current_progress_stage

int total_progress_stages

date date_of_starting_treatment

string aftercare_stage

datetime created_at

}

  

ProgressEvent {

int id PK

int user_id FK

int journey_id FK

datetime start_date

int progress_stage

datetime end_date

int next_notification_id FK

string type

string reason

}

  

UserImage {

int id PK

int user_id FK

int journey_id FK

string s3_key

datetime date_uploaded

int progress_stage

}

  

Notification {

int id PK

datetime created_at

int user_id FK

int journey_id FK

text content

enum status

enum source

datetime scheduled_for

string onesignal_id

}

Table Details

users

The central entity representing an app user (patient).

Field	Type	Notes
id	int	Primary key
email	string	Unique, used as Cognito username
first_name	string	Patient’s first name
last_name	string	Patient’s last name
postal_code	str	Patient’s postal code
cognito_sub_identifier	string	Unique, links to Cognito identity
jarvis_id	int	External Jarvis system ID
hubspot_id	string	HubSpot CRM ID
assigned_dentist_id	int	FK to dentist (external)
has_pending_dentist_change	bool	Whether a dentist change is pending
terms_accepted	bool	Whether T&Cs accepted

journeys

Represents a user’s treatment cycle for a specific product. A user can have multiple journeys over time but only one active journey at any time.

Field	Type	Notes
id	int	Primary key
user_id	int	FK to users
product	string	Product type: aligners, clear_braces
status	string	Journey status: active, completed
current_progress_stage	int	Current progress stage number
total_progress_stages	int	Total stages in treatment plan
date_of_starting_treatment	date	Set on first progress event
aftercare_stage	string	HubSpot aftercare stage: scan_appointment_booked, aligner_journey_underway, journey_complete
created_at	datetime	When the journey was created

progress_events

Records progress events of different types (progress, pause, delay, continue).

Field	Type	Notes
id	int	Primary key
user_id	int	FK to users
journey_id	int	FK to journeys
start_date	datetime	When the event was created
progress_stage	int	Progress stage number at time of event
end_date	datetime	Scheduled next action (nullable)
next_notification_id	int	FK to scheduled notification
type	string	Event type: progress, pause, delay, continue
reason	string	Reason for pause/delay events (nullable)

user_images

Progress photos uploaded by users.

Field	Type	Notes
id	int	Primary key
user_id	int	FK to users
journey_id	int	FK to journeys
s3_key	string	S3 object key
date_uploaded	datetime	Upload timestamp
progress_stage	int	Progress stage number at upload

notifications

Push notification records.

Field	Type	Notes
id	int	Primary key
user_id	int	FK to users
journey_id	int	FK to journeys (nullable)
content	text	Notification message
status	enum	pending, sent, cancelled
source	enum	progress, other
scheduled_for	datetime	When to send (null = immediate)
onesignal_id	string	OneSignal notification ID for cancellation

Indexes

Key indexes for query performance:

• users.email (unique)

• users.cognito_sub_identifier (unique)

• users.jarvis_id

• users.hubspot_id (unique)

• journeys.user_id

• progress_events.user_id

• progress_events.journey_id

• user_images.user_id

• user_images.journey_id

• notifications.user_id

• notifications.journey_id

• notifications.scheduled_for (for notification worker queries)