Sleek Coach Docs

Database Reference

Schema reference for the Sleek Coach database. Column-level detail lives in the SQLModel definitions (apps/api/app/*/models.py); this document covers the shape of the schema, enums, indexes, and the migration workflow.

Overview

• Database: PostgreSQL 15+
• ORM: SQLModel (SQLAlchemy + Pydantic)
• Migrations: Alembic (apps/api/migrations/versions/)
• Primary keys: UUID v4
• Timestamps: naive UTC
• Foreign keys: user-owned rows use ON DELETE CASCADE from user.id (except ai_policy_violation_log.session_id, which is SET NULL)

Entity Relationship Diagram

erDiagram
    USER ||--o| USER_PROFILE : has
    USER ||--o| USER_GOAL : has
    USER ||--o| DIET_PREFERENCES : has
    USER ||--o{ USER_CONSENT : grants
    USER ||--o{ CHECK_IN : logs
    USER ||--o{ NUTRITION_DAY : logs
    USER ||--o{ PROGRESS_PHOTO : uploads
    USER ||--o{ REFRESH_TOKEN : authenticates
    USER ||--o{ AI_SESSION : participates
    AI_SESSION ||--o{ AI_TOOL_CALL_LOG : executes
    AI_SESSION ||--o{ AI_POLICY_VIOLATION_LOG : triggers
    USER ||--o{ AI_TOOL_CALL_LOG : owns
    USER ||--o{ AI_POLICY_VIOLATION_LOG : owns

    USER {
        uuid id PK
        string email UK
        string hashed_password
        boolean is_active
        boolean is_verified
    }

    USER_PROFILE {
        uuid id PK
        uuid user_id FK UK
        string display_name
        float height_cm
        enum sex
        int birth_year
        enum activity_level
        string timezone
    }

    USER_GOAL {
        uuid id PK
        uuid user_id FK UK
        enum goal_type
        float target_weight_kg
        enum pace_preference
        date target_date
    }

    DIET_PREFERENCES {
        uuid id PK
        uuid user_id FK UK
        enum diet_type
        json allergies
        json disliked_foods
        int meals_per_day
        json macro_targets
    }

    USER_CONSENT {
        uuid id PK
        uuid user_id FK
        string consent_type
        boolean granted
        string version
        timestamp granted_at
        timestamp revoked_at
    }

    CHECK_IN {
        uuid id PK
        uuid user_id FK
        date date
        decimal weight_kg
        text notes
        int energy_level
        int sleep_quality
        int mood
        decimal adherence_score
        timestamp client_updated_at
    }

    NUTRITION_DAY {
        uuid id PK
        uuid user_id FK
        date date
        int calories
        decimal protein_g
        decimal carbs_g
        decimal fat_g
        decimal fiber_g
        enum source
        text notes
    }

    PROGRESS_PHOTO {
        uuid id PK
        uuid user_id FK
        date date
        string s3_key UK
        string content_hash
        enum visibility
        json photo_metadata
    }

    REFRESH_TOKEN {
        uuid id PK
        uuid user_id FK
        string token_hash
        timestamp expires_at
        timestamp revoked_at
        string user_agent
        string ip_address
    }

    AI_SESSION {
        uuid id PK
        uuid user_id FK
        enum status
        timestamp started_at
        timestamp last_message_at
        int message_count
        int tokens_used
        string model_tier
        jsonb conversation_history
    }

    AI_TOOL_CALL_LOG {
        uuid id PK
        uuid session_id FK
        uuid user_id FK
        string tool_name
        string tool_category
        string input_hash
        string input_summary
        text output_summary
        enum status
        string error_message
        int latency_ms
        boolean cached
    }

    AI_POLICY_VIOLATION_LOG {
        uuid id PK
        uuid session_id FK
        uuid user_id FK
        enum violation_type
        string severity
        text trigger_content
        string action_taken
        jsonb details
    }

All tables also carry created_at (and, where rows are mutable, updated_at) timestamps; they are omitted above for brevity.

Tables

Table	Model file	Notes
user	app/users/models.py	Auth core; unique indexed email, Argon2id password hash
user_profile	app/users/models.py	1:1 with user (unique user_id)
user_goal	app/users/models.py	1:1 with user
diet_preferences	app/users/models.py	1:1 with user
user_consent	app/users/models.py	Privacy consent audit trail (type, version, grant/revoke times, IP, user agent)
refresh_token	app/auth/models.py	SHA-256 token hashes for rotation and revocation
check_in	app/checkins/models.py	One per user per day (unique user_id, date); client_updated_at supports offline sync
nutrition_day	app/nutrition/models.py	One per user per day (unique user_id, date)
progress_photo	app/photos/models.py	S3 object metadata; unique s3_key, content hash for dedup
ai_session	app/coach_ai/models.py	Coach conversation sessions; conversation_history is capped JSONB (see AI_COACH.md). The unused context_summary and metadata columns were dropped in migration 0008
ai_tool_call_log	app/coach_ai/models.py	Audit row per coach tool execution
ai_policy_violation_log	app/coach_ai/models.py	Audit row per safety policy violation

Enum Types

Defined next to their models as Python str enums:

Enum	Values
Sex	male, female, other, prefer_not_to_say
ActivityLevel	sedentary, light, moderate, active, very_active
GoalType	fat_loss, muscle_gain, recomp, maintenance, performance
PacePreference	slow, moderate, aggressive
DietType	none, vegetarian, vegan, pescatarian, keto, paleo, halal, kosher
ConsentType	terms_of_service, privacy_policy, web_search, analytics, photo_ai_access
NutritionSource	manual, mfp_import
PhotoVisibility	private, coach_only
SessionStatus	active, completed, expired
ToolCallStatus	success, failed, blocked
PolicyViolationType	calorie_minimum, calorie_maximum, protein_minimum, weight_loss_rate, eating_disorder_signal, medical_claim, unsafe_content

Indexes

Declared in the models’ __table_args__:

• user: unique index on email
• check_in: ix_check_in_user_id; unique ix_check_in_user_date (user_id, date)
• nutrition_day: ix_nutrition_day_user_id; unique ix_nutrition_day_user_date (user_id, date)
• progress_photo: ix_progress_photo_user_id; ix_progress_photo_user_date (user_id, date); unique ix_progress_photo_s3_key
• refresh_token: ix_refresh_token_user_id; ix_refresh_token_token_hash
• ai_session: ix_ai_session_user_id
• ai_tool_call_log: ix_ai_tool_call_log_session_id; ix_ai_tool_call_log_user_id
• ai_policy_violation_log: ix_ai_policy_violation_log_user_id

Migration 0006_performance_indexes adds: ix_user_profile_user_id, ix_user_goal_user_id, ix_diet_preferences_user_id, ix_ai_session_status, ix_ai_session_user_status (user_id, status), ix_check_in_date, ix_nutrition_day_date, ix_ai_tool_call_log_created_at.

Migrations

Migrations live in apps/api/migrations/versions/ (0001 initial schema through 0008, which dropped the dead ai_session columns). From the repo root:

# Apply all pending migrations
make -C apps/api migrate

# Create a new migration (prompts for a message, uses --autogenerate)
make -C apps/api migrate-create

# Roll back one migration
make -C apps/api migrate-down

For finer control, run Alembic directly from apps/api: uv run alembic current, uv run alembic history, uv run alembic downgrade <revision>.

Practices:

1. Always review auto-generated migrations; Alembic does not detect every change correctly.
2. Test migrations on staging before production.
3. Keep migrations small: one logical change each, with a working downgrade().
4. Never modify a committed migration; create a new one instead.

Connection Pooling

Defaults from apps/api/app/config.py: pool size 5, max overflow 10. Backup, recovery, and retention procedures: RUNBOOK.md.

This site is open source. Improve this page.