Data models

Scoped to Stage 1 (foundation for personal coach – running data).
I'll extend this when I add Apple HealthKit, predictive analytics, and holistic health data later.

---

Conventions

• IDs: id is an auto-increment int for internal joins; external IDs stored with unique constraints (e.g., stravaId).
• Timestamps: createdAt, updatedAt (UTC).
• Soft delete: use an audit entry; don’t hard-delete activities.
• Units: SI (metres, seconds). Format in UI only.
• Privacy: store references to images, not blobs, in primary tables.

---

Activity

Core activity data with performance optimisations.

The canonical record for a run imported from Strava (running only in stage 1).

field	type	notes
id	BigInt (pk)	Strava activity id
name	String	Activity title
sportType	String	"Run","Ride","Walk","Workout" (indexed)
date	DateTime	Activity date (indexed)
distanceKm	Float	Distance in kilometers (indexed)
movingTimeSec	Int	Moving time in seconds
elapsedTimeSec	Int	Total elapsed time
elevationGainM	Float	Elevation gain in meters
avgHr	Int?	Average heart rate (nullable)
maxHr	Int?	Maximum heart rate (nullable)
avgCadence	Float?	Average cadence (nullable)
maxCadence	Float?	Maximum cadence (nullable)
avgPaceSecPerKm	Int?	Average pace per km (indexed)
fastestPaceSecPerKm	Int?	Best 1km pace (indexed)
calories	Int?	Calories burned (nullable)
avgPower	Float?	Average watts (nullable)
maxPower	Float?	Maximum watts (nullable)
temperature	Float?	Temperature in celsius (nullable)
description	String?	Activity description (nullable)
gearName	String?	Gear used (nullable)
achievementCount	Int?	Kudos, achievements (nullable)
photoCount	Int?	Number of photos (nullable)
elevationLowM	Float?	Lowest elevation (nullable)
elevationHighM	Float?	Highest elevation (nullable)
thumbPng	Bytes?	Thumbnail (excluded from API responses)
isPrivate	Boolean	Privacy flag
raw	Json?	Full Strava JSON (excluded from API responses)
createdAt	DateTime	Record creation timestamp

Indexes

• unique(stravaId).
• idx(startTime).
• idx(athleteId, startTime).

---

Split

Enhanced per-kilometer splits with normalized data.

Normalised lap/split storage. No fabrication.

field	type	notes
activityId	BigInt	Foreign key to Activity
km	Int	Split number (1,2,3,4...)
distanceM	Float?	Actual distance in meters (1000, 983, 83)
movingTimeSec	Int?	Actual duration for this split
elapsedTimeSec	Int?	Total elapsed time including stops
avgSpeedMs	Float?	Average speed in m/s from Strava
paceSec	Int?	Pace per km (legacy field)
elevGainM	Float?	Elevation gain for this split
hrAvg	Int?	Average heart rate for this split

Indexes

• @@id([activityId, km]).
• @@index([activityId]).

Rules

• Last lap may be partial; store exactly as reported by Strava.
• Normalized storage from Strava's raw data for accuracy.

---

Best efforts

Precomputed tables for faster UI (400m → 50k).

field	type	notes
id	int (pk)
activityId	int (fk)	→ activity.id
distanceM	int	400,800,1000,1609,5000,10000,21100,42200,50000
durationS	int	best time observed
window	enum	all, 2y, 1y, 3m
rank	int	1..N

Indexes

• idx(distanceM, window, durationS).
• idx(activityId).

---

Strava token

Database-first authentication with audit trail.

Strava OAuth tokens with enterprise-grade audit trail.

field	type	notes
id	Int (pk)	Auto-increment primary key
athleteId	BigInt	Strava athlete ID (allows multiple records per athlete)
accessToken	String	Encrypted at rest
refreshToken	String	Encrypted at rest
expiresAt	DateTime	Token expiry time
scope	String?	OAuth scopes granted (nullable)
isActive	Boolean	Audit trail flag (default: true)
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime	Last update timestamp
lastUsedAt	DateTime	Tracks token usage
revokedAt	DateTime?	When token was deactivated/disconnected
athleteName	String?	Athlete profile info (nullable)
profileUrl	String?	Profile URL (nullable)

Indexes

• @@index([athleteId, isActive]) for efficient queries.

Rules

• Refresh proactively (e.g., 5 min before expiry).
• On failure → status='degraded' and surface reconnect UI.

---

Audit

One row per significant event.

field	type	notes
id	int (pk)
entity	enum	activity, split, token, job, admin.
entityId	int	target id
action	enum	create, update, skip, delete, error.
message	string	human-readable
meta	json	error codes, request ids
createdAt	datetime

---

Job

Background work queue (webhook fetch, thumbnails, analytics).

field	type	notes
id	int (pk)
type	enum	fetch, thumbnail, analytics.
payload	json	minimal
status	enum	queued, running, done, error, retry.
attempts	int	0..N
lastError	string?	nullable
createdAt	datetime
updatedAt	datetime

Rules

• Exponential backoff; cap attempts; move to error with audit.

---

Thumbnail

Reference to generated images.

field	type	notes
id	int (pk)
activityId	int (fk)	→ activity.id
url	string	relative/absolute
width	int	px
height	int	px
createdAt	datetime

Principle: API returns metadata/URLs; UI fetches images directly.

---

User

Placeholder for stage 2+.

For stage 1 this can be a single admin user or omitted entirely.

field	type	notes
id	int (pk)
email	string	unique
role	enum	admin, user.
createdAt	datetime

---

Enhanced models

Stage 1 foundation complete.

Caching strategy

• Thumbnails: public, max-age=86400, stale-while-revalidate=604800 (24h cache + 7-day stale).
• Activity details: public, max-age=3600, stale-while-revalidate=86400 (1h cache + 24h stale).
• Activity lists: public, max-age=300, stale-while-revalidate=1800 (5min cache + 30min stale).
• Maps & splits: public, max-age=3600, stale-while-revalidate=86400 (1h cache + 24h stale).

Performance protection

• Large field exclusion: raw (MB of JSON) and thumbPng (base64 images) automatically excluded from API responses.
• Field selection: Lists APIs use explicit field selection to avoid performance issues.
• Database optimisation: Proper indexing on sportType, date, fastestPaceSecPerKm, distanceKm.

ER sketch

Stage 1 complete.

Activity (1) — (N) Split
Activity (1) — (N) ThumbnailJob
Activity (1) — (N) ActivityStream
Activity (1) — (N) BestEffort

StravaToken — standalone with isActive audit trail
ActivityFetchJob — webhook-triggered job queue
WebhookSubscription — app-wide subscription cache

Real-time webhook architecture with enterprise-grade audit trails and performance optimisation.