Gemini 4 Argon is now available for Google AI Pro and Ultra subscribers and via Gemini API paid tier. Learn more.

Smart Fitness App: Data Structure, Database Schema & Server Design Spec

Artifact

This is the artifact used in Episode 2: Plan Your App's System Spec (No Code). You can use this specification as a reference for structuring Cloud Firestore collections, server endpoints, and 3-tier agent memory.

Featured in: Episode 2: Plan Your App's System Spec (No Code) Episode 4: The No-Code App Implementation Plan

Status: In Progress (Drafting with Planning Companion)
Target Database: Google Cloud Firestore
Architecture Goal: Clean, maintainable, production-ready rebuild of the AI Studio smart fitness prototype.

0. Core Technical Stack & System Architecture Decisions

To ensure production stability, performance, and clear boundaries, the following architectural baseline is locked for all implementations:

Frontend Framework: Next.js (App Router, React 19) with Progressive Web App (PWA) offline asset caching (using Serwist / @ducanh2912/next-pwa).
Styling & UI Components: Tailwind CSS v4 + Tailwind Variants (for variant management) + Lucide React (icons).
Client State Architecture:
- Zustand (with persist middleware): Handles local, tactile, and ephemeral client state (stopwatch timers, active set buffers, step graph, and unsaved gym-floor inputs).
- TanStack Query (React Query): Handles all asynchronous server state, Firestore document caching, background revalidation, request deduplication, and optimistic mutations.
Backend Runtime & API Hosting: Next.js Route Handlers (app/api/...) hosted on Google Cloud Run in the same region as Firestore and Gemini Context Caching for sub-15ms internal latency.
Authentication: Firebase Authentication (Google Sign-In, Email/Password, and Anonymous onboarding linking). Client attaches Authorization: Bearer <Firebase_ID_Token> on all API requests; server verifies identity using firebase-admin.auth().verifyIdToken(token).
Primary Database: Google Cloud Firestore.

1. System Overview & Core User Journey

1.1 Scope & Architecture Principles

This specification defines the data models, Cloud Firestore schemas, server endpoint contracts, and AI agent memory structures for the Smart Fitness application. It establishes a single source of truth for all workout planning, historical session logging, and autonomous tiered coaching memory, completely decoupled from frontend presentation frameworks (which are addressed in a dedicated UI/UX design specification).

1.2 Core Data Tenets

Separation of Concerns: Strict boundary between data contracts (backend/database) and client UI design.
Hybrid Library Architecture: Static, immutable exercise encyclopedia (exercises) coupled with dynamic AI composition of weekly schedules (weekly_plans).
Human-in-the-Loop Staged Mutations: AI-generated workout alterations are returned as staged proposals; database writes occur exclusively upon explicit user acceptance.
Continuous 3-Tier Living Memory: AI autonomously captures, updates, and purges user facts across permanent staples, expiring physiological states (with explicit TTL), and transient situational context.
Granular Single-Document Reads: Data is structured into optimized documents (such as weekly plan spiral notebooks and weekly chat buckets) to minimize read latency and eliminate redundant queries.

2. Server Endpoints & API Contracts

Every endpoint must specify Response Source (AGENT, DATABASE, or HYBRID) so that agent-backed endpoints can be appropriately configured during implementation.

`POST /api/coach/chat`

Purpose: Conversational interaction with the AI Personal Trainer, handling coaching questions, motivational support, and generating proposed workout modifications (both daily adjustments and weekly overhauls).
Trigger: User submits a message in the Coach tab or taps an inline contextual suggested edit chip (e.g., "Replace Arnold Press with DB Shoulder Press").
Response Source: AGENT — Leverages Gemini with structured tool/function calling and streaming capabilities. Does not directly mutate the database; returns a staged proposal for human-in-the-loop review.
Headers: Authorization: Bearer <Firebase_ID_Token>, Content-Type: application/json

Request Payload:

{
  "message": "string (user input or selected prompt chip text)",
  "context": {
    "active_tab": "string (enum: today | week | coach)",
    "week_start_date": "string (ISO date YYYY-MM-DD)",
    "selected_day": "string (e.g. monday, optional)"
  }
}

Response Payload:

{
  "message": "string (conversational coaching response displayed in chat bubble)",
  "proposal": {
    "id": "string (unique proposal ID, nullable)",
    "type": "string (enum: NONE | UPDATE_DAY | UPDATE_WEEK)",
    "target_day": "string (e.g. monday, nullable)",
    "summary_diff": "string (concise summary of changes, e.g. 'Swapped Arnold Press ➔ DB Shoulder Press', nullable)",
    "data": "map (full proposed workout day object or weekly plan object, nullable)"
  },
  "memory_mutations": [
    {
      "operation": "string (enum: CREATE | UPDATE | DELETE)",
      "target_tier": "string (enum: tier_1_staples | tier_2_physiological | tier_3_situational)",
      "memory_id": "string (required for UPDATE and DELETE)",
      "entry": "map (memory payload with fact, category, expires_at, etc., required for CREATE and UPDATE)",
      "reason": "string (concise rationale for why memory was mutated)"
    }
  ]
}

Dual-Write to weekly_chats: On each turn, the server persists the incoming user message (text) and the coach's response (text) into /users/{userId}/weekly_chats/{week_start_date} using FieldValue.arrayUnion().
Staged Mutation Pattern: Returns the proposal payload without modifying /weekly_plans until the user taps [Accept Plan].
Continuous Autonomous Memory Management: Emits memory_mutations to create, update, or purge items from the user's 3-tier profile.

`POST /api/workouts/log`

Purpose: Persists a completed workout session to the user's permanent training archive, computes final volume/reps aggregates, checks for Personal Records (PRs), and flags the scheduled day as completed.
Trigger: User taps "Finish Workout" on the workout tracking screen.
Response Source: DATABASE — Direct database transaction/write to Cloud Firestore.

Request Payload:

{
  "plan_reference": {
    "week_start_date": "string (ISO date YYYY-MM-DD)",
    "day_of_week": "string (e.g. monday)"
  },
  "title": "string (e.g. Upper Body Hypertrophy)",
  "started_at": "string (ISO timestamp)",
  "completed_at": "string (ISO timestamp)",
  "duration_seconds": "number (e.g. 3120)",
  "exercises_logged": [
    {
      "exercise_id": "string (canonical slug, e.g. incline-db-bench-press)",
      "exercise_name": "string (e.g. Incline DB Bench Press)",
      "sets": [
        {
          "set_number": "number (e.g. 1)",
          "weight_lbs": "number (e.g. 60)",
          "reps_completed": "number (e.g. 12)",
          "completed": "boolean"
        }
      ]
    }
  ],
  "user_notes": "string (optional subjective feedback)",
  "perceived_exertion": "number (optional 1-10 RPE rating)"
}

Response Payload:

{
  "success": "boolean",
  "log_id": "string (newly created workout log document ID)",
  "summary": {
    "total_volume_lbs": "number",
    "total_sets_completed": "number",
    "total_reps_completed": "number"
  },
  "new_prs": [
    {
      "exercise_id": "string (canonical slug, e.g. incline-db-bench-press)",
      "metric": "string (e.g. '10 Rep Max' or 'Estimated 1RM')",
      "value": "string (e.g. '65 lbs' or '105 lbs')"
    }
  ]
}

3. Cloud Firestore Database Architecture

Collection: `exercises`

Hierarchy: Root Collection (/exercises/{exercise_id}) • Purpose: Static exercise encyclopedia containing multimedia, form cues, equipment requirements, and instructions.

{
  "id": "string (canonical slug matching document ID, e.g. incline-db-bench-press)",
  "name": "string (e.g. Incline DB Bench Press)",
  "category": "string (e.g. Strength, Hypertrophy, Cardio, Mobility)",
  "target_muscles": {
    "primary": ["string (e.g. Upper Chest)"],
    "secondary": ["string (e.g. Front Deltoids, Triceps)"]
  },
  "media": {
    "video_url": "string (URL to demonstration video)",
    "poster_url": "string (URL to video poster / thumbnail image)"
  },
  "instructions": ["string (step-by-step execution guidance)"],
  "form_tips": ["string (critical cues and common pitfalls)"],
  "equipment_required": ["string (e.g. Dumbbells, Incline Bench)"]
}

Collection: `weekly_plans`

Hierarchy: /users/{userId}/weekly_plans/{week_start_date} • Purpose: The "Spiral Notebook" containing the complete weekly training schedule, summary metrics, and individual day workouts.

{
  "week_start_date": "string (ISO date YYYY-MM-DD, e.g. 2026-09-07)",
  "theme": "string (e.g. Hypertrophy Focus & Upper Body Overload)",
  "summary": {
    "total_time_minutes": "number (raw minutes, e.g. 180)",
    "total_volume_lbs": "number (raw tonnage in lbs, e.g. 38400)",
    "frequency": "number (active workout days, e.g. 4)"
  },
  "suggested_edits": ["string (pre-generated AI prompt suggestion)"],
  "days": {
    "monday": {
      "date": "string (ISO date YYYY-MM-DD)",
      "day_of_week": "string (e.g. Monday)",
      "is_rest_day": "boolean",
      "status": "string (enum: scheduled | completed | skipped)",
      "completed_log_id": "string (nullable)",
      "completed_at": "string (ISO timestamp, nullable)",
      "title": "string (e.g. Upper Body Hypertrophy)",
      "focus_tag": "string (e.g. Push & Chest Dominant)",
      "duration_minutes": "number",
      "estimated_volume_lbs": "number",
      "coach_note": "string",
      "muscle_focus": [{ "muscle": "string", "intensity": "number (0-100)" }],
      "suggested_edits": ["string"],
      "blocks": [
        {
          "label": "string (e.g. Main Compound Block)",
          "exercises": [
            {
              "exercise_id": "string",
              "name": "string",
              "target_sets": "number",
              "target_reps": "string or number",
              "rest_seconds": "number"
            }
          ]
        }
      ]
    }
  }
}

Collection: `weekly_chats`

Hierarchy: /users/{userId}/weekly_chats/{week_start_date} • Purpose: The "Weekly Chat Bucket" containing the complete conversation history for a given training week in a single document read.

{
  "week_start_date": "string (ISO date YYYY-MM-DD, e.g. 2026-09-07)",
  "messages": [
    {
      "id": "string (unique message ID)",
      "sender": "string (enum: user | coach)",
      "text": "string (conversational message body)",
      "created_at": "timestamp (server timestamp)",
      "proposal": {
        "id": "string (proposal UID, nullable)",
        "type": "string (enum: UPDATE_DAY | UPDATE_WEEK, nullable)",
        "target_day": "string (e.g. monday, nullable)",
        "summary_diff": "string (nullable)",
        "status": "string (enum: pending | accepted | dismissed)",
        "data": "map (workout or week snapshot payload, nullable)"
      }
    }
  ]
}

Collection: `workout_logs`

Hierarchy: /users/{userId}/workout_logs/{log_id} • Purpose: Permanent, immutable historical archive of completed workout sessions.

{
  "id": "string (document ID)",
  "plan_reference": {
    "week_start_date": "string (ISO date YYYY-MM-DD)",
    "day_of_week": "string (e.g. monday)"
  },
  "title": "string (e.g. Upper Body Hypertrophy)",
  "started_at": "timestamp",
  "completed_at": "timestamp",
  "duration_seconds": "number",
  "summary": {
    "total_volume_lbs": "number",
    "total_sets_completed": "number",
    "total_reps_completed": "number"
  },
  "exercises_logged": [
    {
      "exercise_id": "string",
      "exercise_name": "string",
      "sets": [
        {
          "set_number": "number",
          "weight_lbs": "number",
          "reps_completed": "number",
          "completed": "boolean"
        }
      ]
    }
  ],
  "user_notes": "string (nullable)",
  "perceived_exertion": "number (1-10 RPE, nullable)"
}

Collection: `user_profiles`

Hierarchy: /users/{userId}/profile/main • Purpose: The "Living Client Chart" storing the user's 3-tier memory profile.

{
  "user_id": "string (document owner UID)",
  "display_name": "string (e.g. Alex)",
  "email": "string (e.g. alex@example.com)",
  "preferences": {
    "units": "string (enum: lbs | kg)",
    "haptics": "boolean",
    "sound": "boolean"
  },
  "equipment_ids": ["string (e.g. 'dumbbells', 'bench_adjustable', 'pull_up_bar')"],
  "onboarding_status": "string (enum: not_started | in_progress | completed)",
  "created_at": "timestamp",
  "tier_1_staples": [
    {
      "id": "string (e.g. mem_01)",
      "category": "string (enum: equipment | preference | goal | chronic_condition)",
      "fact": "string (e.g. Home gym with dumbbells up to 50 lbs and adjustable bench)",
      "source": "string (e.g. conversational_onboarding | manual_entry)"
    }
  ],
  "tier_2_physiological": [
    {
      "id": "string (e.g. mem_02)",
      "body_part": "string (e.g. hamstrings, shoulder)",
      "condition": "string (e.g. soreness, acute_fatigue, minor_strain)",
      "fact": "string (e.g. Hamstrings are moderately sore from hill sprints)",
      "effective_date": "string (ISO date YYYY-MM-DD)",
      "expires_at": "timestamp"
    }
  ],
  "tier_3_situational": [
    {
      "id": "string (e.g. mem_03)",
      "event": "string (e.g. golf_competition, travel)",
      "fact": "string (e.g. Major golf tournament on Saturday; avoid excessive arm/lat fatigue on Friday)",
      "week_start_date": "string (ISO date YYYY-MM-DD)",
      "expires_at": "timestamp"
    }
  ]
}

Collection: `prs`

Hierarchy: /users/{userId}/prs/{exercise_slug} • Purpose: Stores all-time Personal Records, estimated 1-Rep Max benchmarks, and rep-specific records.

{
  "exercise_id": "string (canonical slug, e.g. incline-db-bench-press)",
  "exercise_name": "string (e.g. Incline DB Bench Press)",
  "estimated_1rm": "number (calculated using Brzycki formula, e.g. 105)",
  "max_weight_set": {
    "weight_lbs": "number (e.g. 80)",
    "reps": "number (e.g. 10)",
    "achieved_at": "timestamp"
  },
  "rep_records": {
    "10": 80,
    "8": 85,
    "5": 95
  },
  "last_updated": "timestamp"
}

4. Data Lifecycle & Agent Pipeline

4.1 Pipeline 1: Coaching, Workout Generation & Staged Modification

User Action: Submits freeform input or taps a pre-generated contextual edit chip.
Context Assembly: Server retrieves active 3-tier memory profile and current week's plan, pruning expired memory items.
Agent Inference: Gemini processes the prompt and context using structured tool/function calling.
Autonomous Memory Mutation: Extracted user facts are applied to /user_profiles/{userId} and synchronized with equipment_ids.
Staged Proposal Staging: Proposed workout modifications are returned with proposal.status = "pending" inside /weekly_chats/{week_start_date}.
User Resolution & Persistence: Tapping [Accept Plan] commits the proposed data to /weekly_plans and marks the proposal "accepted".

4.2 Pipeline 2: Workout Execution & Archiving (Ground Truth)

Live Session Recording: Client tracks actual load lifted, repetitions completed, and completion flags in local UI state.
Session Submission: Client calls POST /api/workouts/log on "Finish Workout".
Aggregate Calculation & Validation: Server calculates cumulative session volume and checks for PRs using the Brzycki formula.
Immutable Persistence: Writes an immutable record in /workout_logs, marks the day "completed" in /weekly_plans, and upserts new PRs into /prs.

4.3 Pipeline 3: Autonomous Memory Ingestion & Invalidation

Ingestion: Facts are extracted continuously across all conversational interactions.
Classification: Tier 1 (Staples, no expiration), Tier 2 (Physiological State, 3–4 day TTL), Tier 3 (Situational Context, event-based TTL).
Pruning & Invalidation: Expired items (current_timestamp > expires_at) are filtered out of prompt context and lazily purged.

5. Firestore Security Rules & Composite Index Specifications

5.1 Security Rules (`firestore.rules`)

rules_version = '2';
service cloud.firestore {
  match /databases/{database}/documents {
    match /users/{userId} {
      allow read, write: if request.auth != null && request.auth.uid == userId;
      match /profile/{document=**} { allow read, write: if request.auth != null && request.auth.uid == userId; }
      match /weekly_plans/{planId} { allow read, write: if request.auth != null && request.auth.uid == userId; }
      match /weekly_chats/{chatId} { allow read, write: if request.auth != null && request.auth.uid == userId; }
      match /prs/{exerciseSlug} { allow read, write: if request.auth != null && request.auth.uid == userId; }
      match /workout_logs/{logId} {
        allow read, create: if request.auth != null && request.auth.uid == userId;
        allow update, delete: if false;
      }
    }
    match /exercises/{exerciseId} {
      allow read: if request.auth != null;
      allow write: if false;
    }
  }
}

5.2 Composite Query Indexes (`firestore.indexes.json`)

{
  "indexes": [
    {
      "collectionGroup": "workout_logs",
      "queryScope": "COLLECTION",
      "fields": [{ "fieldPath": "completed_at", "order": "DESCENDING" }]
    },
    {
      "collectionGroup": "weekly_plans",
      "queryScope": "COLLECTION",
      "fields": [{ "fieldPath": "week_start_date", "order": "DESCENDING" }]
    },
    {
      "collectionGroup": "prs",
      "queryScope": "COLLECTION",
      "fields": [{ "fieldPath": "last_updated", "order": "DESCENDING" }]
    }
  ]
}

6. Changelog & Locked Decisions

Date	Component / Decision	Type	Locked By
Initial Setup	Initialized empty specification scaffold	System Setup	Team
2026-09-04	Added Firestore collections: exercises and weekly_plans	Schema	Team
2026-09-04	Added POST /api/coach/chat endpoint and chat_messages collection	Endpoint & Schema	Team
2026-09-04	Refactored chat storage to weekly_chats bucket schema	Schema	Team
2026-09-04	Added POST /api/workouts/log endpoint and workout_logs collection	Endpoint & Schema	Team
2026-09-04	Added user_profiles 3-tier memory schema & updated POST /api/coach/chat with memory_mutations	Endpoint & Schema	Team
2026-09-04	Completed Section 1 (System Scope & Tenets) and Section 4 (Data Lifecycle & Pipelines)	Full Spec Completion	Team
2026-09-22	Harmonized specs: Next.js + Tailwind + Zustand stack, Bearer auth, PR subcollection, Firestore rules, and canonical exercise slugs	Full Spec Harmonization	Architecture Review

Smart Fitness App: Data Structure, Database Schema & Server Design Spec

Developer Profile Required

Create a Developer Profile

Join Builder Central to Unlock

0. Core Technical Stack & System Architecture Decisions

1. System Overview & Core User Journey

1.1 Scope & Architecture Principles

1.2 Core Data Tenets

2. Server Endpoints & API Contracts

`POST /api/coach/chat`

`POST /api/workouts/log`

3. Cloud Firestore Database Architecture

Collection: `exercises`

Collection: `weekly_plans`

Collection: `weekly_chats`

Collection: `workout_logs`

Collection: `user_profiles`

Collection: `prs`

4. Data Lifecycle & Agent Pipeline

4.1 Pipeline 1: Coaching, Workout Generation & Staged Modification

4.2 Pipeline 2: Workout Execution & Archiving (Ground Truth)

4.3 Pipeline 3: Autonomous Memory Ingestion & Invalidation

5. Firestore Security Rules & Composite Index Specifications

5.1 Security Rules (`firestore.rules`)

5.2 Composite Query Indexes (`firestore.indexes.json`)

6. Changelog & Locked Decisions

Smart Fitness App: Data Structure, Database Schema & Server Design Spec

Sign in to access this artifact

Developer Profile Required

Create a Developer Profile

Join Builder Central to Unlock

0. Core Technical Stack & System Architecture Decisions

1. System Overview & Core User Journey

1.1 Scope & Architecture Principles

1.2 Core Data Tenets

2. Server Endpoints & API Contracts

POST /api/coach/chat

POST /api/workouts/log

3. Cloud Firestore Database Architecture

Collection: exercises

Collection: weekly_plans

Collection: weekly_chats

Collection: workout_logs

Collection: user_profiles

Collection: prs

4. Data Lifecycle & Agent Pipeline

4.1 Pipeline 1: Coaching, Workout Generation & Staged Modification

4.2 Pipeline 2: Workout Execution & Archiving (Ground Truth)

4.3 Pipeline 3: Autonomous Memory Ingestion & Invalidation

5. Firestore Security Rules & Composite Index Specifications

5.1 Security Rules (firestore.rules)

5.2 Composite Query Indexes (firestore.indexes.json)

6. Changelog & Locked Decisions

`POST /api/coach/chat`

`POST /api/workouts/log`

Collection: `exercises`

Collection: `weekly_plans`

Collection: `weekly_chats`

Collection: `workout_logs`

Collection: `user_profiles`

Collection: `prs`

5.1 Security Rules (`firestore.rules`)

5.2 Composite Query Indexes (`firestore.indexes.json`)