58 lines
2.1 KiB
Markdown
58 lines
2.1 KiB
Markdown
# Data Architecture Specification
|
|
|
|
**Status**: Draft
|
|
**Version**: 1.0.0
|
|
**Last Updated**: 2026-01-13
|
|
|
|
## 1. Overview
|
|
This document defines the data architecture for FitTrack2, specifically focusing on the storage of high-resolution activity data.
|
|
|
|
## 2. Spatial Data & PostGIS
|
|
To support advanced segment matching and efficient spatial queries, the database utilizes the **PostGIS** extension.
|
|
- **Extension**: `postgis` (v3.5.0+)
|
|
- **SRID**: 4326 (WGS 84)
|
|
|
|
## 3. Schema Definitions
|
|
|
|
### 3.1 Activities
|
|
Stores metadata and summary statistics for an activity.
|
|
|
|
| Column | Type | Description |
|
|
|--------|------|-------------|
|
|
| id | Integer | PK |
|
|
| garmin_activity_id | String | Unique External ID |
|
|
| start_time | DateTime | UTC Start |
|
|
| file_content | LargeBinary | Raw FIT/TCX file (Legacy/Backup) |
|
|
| ... | ... | (See `backend/src/models/activity.py`) |
|
|
|
|
### 3.2 Activity Streams (New)
|
|
Stores the time-series data for an activity. Uses PostgreSQL Arrays for efficient storage of high-frequency data (1Hz).
|
|
|
|
**Table Name**: `activity_streams`
|
|
|
|
| Column | Type | Constraints | Description |
|
|
|--------|------|-------------|-------------|
|
|
| id | Integer | PK, Auto-inc | Unique ID |
|
|
| activity_id | Integer | FK, Not Null | Reference to `activities.id` |
|
|
| time_offset | Integer[] | | Seconds from start_time |
|
|
| latitude | Float[] | | Degrees (WGS84) |
|
|
| longitude | Float[] | | Degrees (WGS84) |
|
|
| elevation | Float[] | | Meters |
|
|
| heart_rate | Integer[] | | BPM |
|
|
| power | Integer[] | | Watts |
|
|
| cadence | Integer[] | | RPM |
|
|
| speed | Float[] | | m/s |
|
|
| distance | Float[] | | Meters |
|
|
| temperature | Float[] | | Celsius |
|
|
| moving | Boolean[] | | Moving status (derived) |
|
|
| grade_smooth | Float[] | | Gradient % |
|
|
| geom | Geometry(LineString, 4326) | Indexed | Spatial representation of the track |
|
|
|
|
**Indexes**:
|
|
- `idx_activity_streams_activity_id` on `activity_id`
|
|
- `idx_activity_streams_geom` using GIST on `geom`
|
|
|
|
## 4. Derived Data Logic
|
|
- The `geom` column is automatically derived from `latitude` and `longitude` arrays during insertion or update.
|
|
- `time_offset` relates directly to the array index of other columns (e.g., `heart_rate[i]` occurred at `time_offset[i]`).
|