🗄️ Database Schema & PostGIS Reference

This document maps out the PostGIS-enabled PostgreSQL database schema managed via Drizzle ORM for the BikeTrack application. All schema code resides in [apps/api/src/db/schema.ts](file:///Users/ulad/Documents/Pets/BikeTrackV2/bike-maps/apps/api/src/db/schema.ts).

🗺️ Database ER Diagram (High Level)

🛠️ Custom Spatial Geometry Types

PostGIS geometry columns are integrated into Drizzle using custom types defined at the top of the schema file:

1. `lineStringGeometry`

Postgres Column Type: geometry(LineString, 4326)
Purpose: Holds ordered arrays of coordinate points [longitude, latitude] defining a route path.
Projection: SRID 4326 (WGS 84 - Standard World Geodetic System).

2. `polygonGeometry`

Postgres Column Type: geometry(Polygon, 4326)
Purpose: Defines bounded area segments for map overlays and route metadata.

📋 Table Definitions

1. `users`

Represents application user accounts synced with Firebase Auth.

Column	Type	Constraints	Description
`id`	`text`	`primaryKey`	Firebase Authentication Unique ID (UID)
`email`	`text`	-	Email address
`stravaAthleteId`	`text`	`unique`	Linked Strava Account Athlete ID
`stravaAccessToken`	`text`	-	Strava OAuth Access Token
`stravaRefreshToken`	`text`	-	Strava OAuth Refresh Token
`stravaTokenExpiresAt`	`timestamp`	-	Strava Token Expiration Date
`stravaClientId`	`text`	-	Strava Client ID override (if configured)
`stravaClientSecret`	`text`	-	Strava Client Secret override
`lastStravaSyncDate`	`timestamp`	-	Date of last successful activity sync
`createdAt`	`timestamp`	`defaultNow()`	Account Creation Date

2. `tracks`

Saves user-planned route coordinates, telemetry stats, cue sheets, and waypoints.

Column	Type	Constraints	Description
`id`	`uuid`	`primaryKey`, `defaultRandom()`	Unique ID
`userId`	`text`	`notNull`, `references(users.id)`	Owner ID
`title`	`text`	`notNull`	Track Title
`description`	`text`	-	Short Description
`routeGeometry`	`lineStringGeometry`	`notNull`	PostGIS LineString geometry containing points
`distanceMeters`	`doublePrecision`	`notNull`	Total route length in meters
`stats`	`jsonb`	`default('{}')`	Elevation gain/loss and surface breakdown stats
`waypoints`	`jsonb`	`default('[]')`	Core plan waypoints `[lng, lat]` used in editing
`cues`	`jsonb`	-	Array of cue directions (turn sheets)
`colorHex`	`text`	-	Route display color on map
`avoidanceZones`	`jsonb`	`default('[]')`	Spatial bounding boxes to bypass during routing
`isFavorite`	`boolean`	`notNull`, `default(false)`	Favorite toggle
`deletedAt`	`timestamp`	-	Soft delete timestamp
`createdAt`	`timestamp`	`defaultNow()`	Track creation date
`updatedAt`	`timestamp`	`defaultNow()`	Track last modification date

NOTE

Indices:

idx_tracks_user_id: Index on userId to load user library quickly.
idx_tracks_user_folder_state: Composite index on userId, deletedAt, and isFavorite to optimize dashboard layout queries.

3. `collections`

Represents folder organizations to group tracks (e.g. "Gravel Projects", "Italy Trip 2026").

Column	Type	Constraints	Description
`id`	`uuid`	`primaryKey`, `defaultRandom()`	Unique ID
`userId`	`text`	`notNull`, `references(users.id, CASCADE)`	Owner ID
`name`	`text`	`notNull`	Collection folder name
`colorHex`	`text`	-	Cover color
`imageUrl`	`text`	-	Custom cover image URL
`isProject`	`boolean`	`notNull`, `default(false)`	True if this record represents a planning project
`projectMetadata`	`jsonb`	-	Project-level metadata
`createdAt`	`timestamp`	`defaultNow()`	Creation timestamp

NOTE

Unique Constraints:

collections_user_id_name_key: Unique index on (userId, name) preventing duplicate folder names per user.

4. `track_collections`

Many-to-many junction table to map tracks to their folders (collections).

Column	Type	Constraints	Description
`trackId`	`uuid`	`notNull`, `references(tracks.id, CASCADE)`	Track Link
`collectionId`	`uuid`	`notNull`, `references(collections.id, CASCADE)`	Collection Link
`sortOrder`	`integer`	`notNull`, `default(0)`	Sorting layout precedence in the folder

5. `pois`

User-defined Points of Interest rendered as emoji markers on the map canvas.

Column	Type	Constraints	Description
`id`	`uuid`	`primaryKey`, `defaultRandom()`	Unique ID
`userId`	`text`	`notNull`, `references(users.id, CASCADE)`	Owner ID
`name`	`text`	`notNull`	POI display label
`type`	`poi_type` enum	`notNull`	Enums: `WATER`, `FOOD`, `HAZARD`, `VIEWPOINT`, `CAMPING`, `GENERAL`
`coordinates`	`jsonb`	`notNull`	Geographical array `[lng, lat]`
`description`	`text`	-	Detailed description popup content
`scope`	`text`	`notNull`	Scope classification (`"track"`, `"global"`)
`visibility`	`text`	`notNull`	Visibility permissions (`"private"`, `"public"`)
`routeId`	`uuid`	`references(tracks.id, CASCADE)`	Route link (if this POI is attached to a specific route)
`createdAt`	`timestamp`	`defaultNow()`	Creation date

6. `bikes`

Virtual representation of a user's bicycle in the virtual Garage.

Column	Type	Constraints	Description
`id`	`uuid`	`primaryKey`, `defaultRandom()`	Unique ID
`userId`	`text`	`notNull`, `references(users.id, CASCADE)`	Owner ID
`name`	`text`	`notNull`	Bike Label (e.g. "Canyon Grizl")
`brand`	`text`	`notNull`	Brand
`model`	`text`	`notNull`	Model
`type`	`text`	`notNull`, `default('Other')`	Category (Road, Gravel, MTB, E-Bike)
`stravaGearId`	`text`	-	Strava Gear Identifier for automatic component wear updates
`totalKm`	`doublePrecision`	`notNull`, `default(0)`	Total distance ridden
`isIgnored`	`boolean`	`notNull`, `default(false)`	Flag to ignore from sync calculations

7. `components`

Virtual bike components that track dual-wear limits (distance and operating hours).

Column	Type	Constraints	Description
`id`	`uuid`	`primaryKey`, `defaultRandom()`	Unique ID
`userId`	`text`	`notNull`, `references(users.id, CASCADE)`	Owner ID
`bikeId`	`uuid`	`references(bikes.id, SET NULL)`	Currently mounted bicycle
`name`	`text`	`notNull`	Component Name (e.g. "Chain CN-M8100")
`brand`	`text`	-	Brand
`model`	`text`	-	Model
`price`	`doublePrecision`	-	Purchase price
`category`	`text`	`notNull`, `default('DRIVETRAIN')`	drivetrain, brakes, wheels, suspension, cockpit, accessories
`status`	`text`	`notNull`, `default('INVENTORY')`	inventory, mounted, archived
`compatibleBikeIds`	`jsonb`	`default('[]')`	Array of compatible bike IDs
`tags`	`jsonb`	`default('[]')`	Custom text tags
`lastServiceDate`	`timestamp`	-	Timestamp of last recorded maintenance action
`maxLifespanKm`	`doublePrecision`	`notNull`, `default(0)`	Maximum wear limit (kilometers)
`currentLifespanKm`	`doublePrecision`	`notNull`, `default(0)`	Ridden kilometers since brand new
`maxLifespanHours`	`doublePrecision`	`notNull`, `default(0)`	Maximum wear limit (hours)
`currentLifespanHours`	`doublePrecision`	`notNull`, `default(0)`	Ridden hours since brand new
`serviceIntervalKm`	`doublePrecision`	`notNull`, `default(0)`	Kilometers between required maintenance
`currentServiceKm`	`doublePrecision`	`notNull`, `default(0)`	Kilometers since last service event
`serviceIntervalHours`	`doublePrecision`	`notNull`, `default(0)`	Hours between required maintenance
`currentServiceHours`	`doublePrecision`	`notNull`, `default(0)`	Hours since last service event
`type`	`text`	`notNull`	Drivetrain subclass (retained for backward compatibility)
`maxKm`	`doublePrecision`	`notNull`	Wear limit kilometer override (legacy)
`currentKm`	`doublePrecision`	`notNull`, `default(0)`	Current kilometers (legacy)
`kmSinceLastService`	`doublePrecision`	`notNull`, `default(0)`	Kilometers since service (legacy)

8. `maintenance_logs`

Historical records of services performed on virtual components.

Column	Type	Constraints	Description
`id`	`uuid`	`primaryKey`, `defaultRandom()`	Unique ID
`componentId`	`uuid`	`notNull`, `references(components.id, CASCADE)`	Serviced component ID
`bikeId`	`uuid`	`references(bikes.id, SET NULL)`	Bicycle reference during service
`actionType`	`text`	`notNull`	Type of service (Clean, Replace, Lubricate)
`notes`	`text`	-	Custom description of work
`kmAtService`	`doublePrecision`	`notNull`	Component kilometer read when service was performed
`performedAt`	`timestamp`	`notNull`, `defaultNow()`	Log timestamp

9. `ride_wear_logs`

Accumulates wear coefficients based on route types to model component degradation.

Column	Type	Constraints	Description
`id`	`uuid`	`primaryKey`, `defaultRandom()`	Unique ID
`bikeId`	`uuid`	`notNull`, `references(bikes.id, CASCADE)`	Battered bicycle
`trackId`	`text`	`notNull`	Associated ride activity or path
`distance`	`doublePrecision`	`notNull`	Ridden distance
`multiplier`	`doublePrecision`	`notNull`	Environmental multiplier (e.g. Gravel=1.2x, Dirt=1.5x)
`appliedAt`	`timestamp`	`notNull`, `defaultNow()`	Execution date

10. `activities`

Rides imported from Strava to track mileage and calculate virtual component wear.

Column	Type	Constraints	Description
`id`	`uuid`	`primaryKey`, `defaultRandom()`	Unique ID
`userId`	`text`	`notNull`, `references(users.id, CASCADE)`	Athlete ID
`externalId`	`text`	`notNull`, `unique`	Strava API Activity ID
`name`	`text`	`notNull`	Strava ride title
`distance`	`doublePrecision`	`notNull`	Rode distance in meters
`movingTime`	`integer`	`notNull`	Ride duration in seconds
`startDate`	`timestamp`	`notNull`	Date ride began
`gearId`	`text`	-	Strava Gear/Bike ID used on the ride
`summaryPolyline`	`text`	-	Strava simplified map polyline string
`isIgnored`	`boolean`	`notNull`, `default(false)`	Skip processing
`trackId`	`uuid`	`references(tracks.id, SET NULL)`	Correlated saved route
`status`	`text`	`notNull`, `default('PENDING')`	Import status (`PENDING`, `PROCESSED`)
`localBikeId`	`uuid`	`references(bikes.id, SET NULL)`	Matched garage bike
`timezone`	`text`	-	Timezone string
`commute`	`boolean`	`default(false)`	True if ride was marked as commute
`sportType`	`text`	`notNull`, `default('Ride')`	Ride sport type

11. `routes`

Geographic route caches computed from activities or custom trim events.

Column	Type	Constraints	Description
`id`	`uuid`	`primaryKey`, `defaultRandom()`	Unique ID
`userId`	`text`	`notNull`, `references(users.id, CASCADE)`	Owner ID
`sourceActivityId`	`uuid`	`references(activities.id, SET NULL)`	Original imported Strava Activity
`name`	`text`	`notNull`	Route label
`distance`	`doublePrecision`	`notNull`	Precision distance (km)
`polyline`	`text`	`notNull`	Compressed encoded path polyline
`isEbike`	`boolean`	`notNull`, `default(false)`	Flag if ridden on electric bike
`createdAt`	`timestamp`	`notNull`, `defaultNow()`	Cache creation date

🔒 Transaction Safeguards

Drizzle transaction layers are used during route updates (e.g. in /api/routes/:id/crop) to protect geometry integrity:

Coordinates are loaded and verified in memory.
A single nested SQL BEGIN...COMMIT block performs:
- Database line update (routes.polyline).
- Telemetry distance normalization (routes.distance).
- Triggering component wear recalibration in a downstream queue step.
If any math check or coordinate slice operation fails, the transaction is immediately rolled back using transaction.rollback(), ensuring no partial/broken coordinates are ever committed to Postgres.

🗄️ Database Schema & PostGIS Reference ​

🗺️ Database ER Diagram (High Level) ​

🛠️ Custom Spatial Geometry Types ​

1. lineStringGeometry ​

2. polygonGeometry ​

📋 Table Definitions ​

1. users ​

2. tracks ​

3. collections ​

4. track_collections ​

5. pois ​

6. bikes ​

7. components ​

8. maintenance_logs ​

9. ride_wear_logs ​

10. activities ​

11. routes ​

🔒 Transaction Safeguards ​