"""Database schema information and descriptions."""
SCHEMA_INFO = {
"tables": {
"workout_sets": {
"description": "Each row represents one set from a workout. Multiple sets form an exercise, and multiple exercises form a workout.",
"columns": {
"id": {
"type": "INTEGER",
"description": "Auto-incrementing primary key for each set",
},
"title": {
"type": "TEXT",
"description": "Workout session title/name (e.g., 'Push Day', 'Leg Day')",
},
"start_time": {
"type": "TEXT",
"description": "ISO timestamp when the workout started (e.g., '2024-01-15 10:30:00')",
},
"end_time": {
"type": "TEXT",
"description": "ISO timestamp when the workout ended",
},
"description": {
"type": "TEXT",
"description": "Optional workout description or notes",
},
"exercise_title": {
"type": "TEXT",
"description": "Exercise name (e.g., 'Bench Press (Barbell)', 'Squat (Barbell)'). Use this to group sets by exercise.",
},
"superset_id": {
"type": "TEXT",
"description": "Groups exercises performed as a superset (back-to-back). NULL for straight sets, same value for exercises in the same superset.",
},
"exercise_notes": {
"type": "TEXT",
"description": "Per-exercise notes. May contain patterns like 'rest-pause', 'tempo', 'form check'.",
},
"set_index": {
"type": "INTEGER",
"description": "Set number within the exercise (0-indexed or 1-indexed depending on export)",
},
"set_type": {
"type": "TEXT",
"description": "Type of set: 'normal' (working set), 'warmup', 'failure', 'drop', etc.",
},
"weight_lbs": {
"type": "REAL",
"description": "Weight used in pounds. NULL for bodyweight exercises or if not tracked.",
},
"reps": {
"type": "INTEGER",
"description": "Number of repetitions completed. NULL if not applicable (e.g., timed exercises).",
},
"distance_miles": {
"type": "REAL",
"description": "Distance in miles (for cardio). NULL for strength training.",
},
"duration_seconds": {
"type": "INTEGER",
"description": "Duration in seconds (for timed exercises like planks). NULL for rep-based exercises.",
},
"rpe": {
"type": "REAL",
"description": "Rate of Perceived Exertion (1-10 scale). 10 = max effort/failure, 8 = 2 reps in reserve. NULL if not tracked.",
},
"imported_at": {
"type": "TEXT",
"description": "Timestamp when this data was imported into the database",
},
},
}
},
"indexes": [
{
"name": "idx_exercise_title",
"columns": ["exercise_title"],
"description": "Fast lookups by exercise name",
},
{
"name": "idx_start_time",
"columns": ["start_time"],
"description": "Fast filtering by date/time",
},
{
"name": "idx_exercise_start",
"columns": ["exercise_title", "start_time"],
"description": "Fast queries for specific exercise over time",
},
],
}
def get_schema_description() -> str:
"""Generate a human-readable schema description."""
lines = ["# Hevy Workout Database Schema\n"]
for table_name, table_info in SCHEMA_INFO["tables"].items():
lines.append(f"## Table: {table_name}")
lines.append(f"\n{table_info['description']}\n")
lines.append("### Columns:\n")
for col_name, col_info in table_info["columns"].items():
lines.append(f"- **{col_name}** ({col_info['type']})")
lines.append(f" - {col_info['description']}\n")
lines.append("## Indexes:\n")
for idx in SCHEMA_INFO["indexes"]:
cols = ", ".join(idx["columns"])
lines.append(f"- **{idx['name']}** on ({cols})")
lines.append(f" - {idx['description']}\n")
lines.append("\n## Query Tips:\n")
lines.append("- **Group sets by workout**: Use `title` and `start_time`")
lines.append("- **Group sets by exercise**: Use `exercise_title`")
lines.append(
"- **Filter working sets**: `WHERE set_type = 'normal'` or `WHERE set_type != 'warmup'`"
)
lines.append("- **Calculate volume**: `SUM(weight_lbs * reps)` for each exercise")
lines.append("- **Find PRs**: `MAX(weight_lbs)` or `MAX(weight_lbs * reps)` (total load)")
lines.append(
"- **Time-based analysis**: Use `DATE(start_time)` to group by day, `strftime('%Y-%m', start_time)` for months"
)
lines.append("- **Supersets**: `WHERE superset_id IS NOT NULL GROUP BY superset_id`")
return "\n".join(lines)
