# 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]`).