Skip to content

Data Model

What is this?

A technical overview of ChatKcal's Single Table Design in DynamoDB, covering primary keys, entity structures, and the atomic aggregation strategy for daily totals.

ChatKcal uses AWS DynamoDB with a Single Table Design (STD) optimized for time-series retrieval per user.

1. Core Schema

The database relies on a composite primary key structure to segregate users and sort their data by time.

  • Partition Key (PK): USER#<Cognito_Sub_ID>
    • Scope: All data for a single user lives in one partition.
  • Sort Key (SK): MEAL#<ISO_Timestamp>#<Unique_ID>
    • Behavior: Allows efficient querying of date ranges.
    • Unique ID: Appended to ensure no collisions if two meals are logged at the exact same second.
  • Global Secondary Index (GSI1): Used for looking up users by PAT hash.
    • Partition Key (GSI1PK): PATHASH#<sha256_hash>
    • Sort Key (GSI1SK): METADATA
    • Projection: INCLUDE (scopes, lastUsedAt) to minimize storage costs.

A standard meal record containing the nutritional data and metadata.

  • Attributes:
    • mealSummary: String (e.g., "Bowl of Chili")
    • emoji: String (e.g., "🥣")
    • calories, protein, carbs, fat: Float
    • extendedNutrients: Map\ (e.g., {"fiber": 5.2})
    • notes: String
    • userDate: String (e.g., "2025-12-31") - Explicit logical date for aggregation.
    • createdAt: ISO Timestamp
    • formatVersion: Number (Current: 4)

Migration Note

Records created prior to version 4 used PascalCase attributes (e.g., MealSummary) and Int values. The system handles both formats transparently.

Stores user-specific settings and goals.

  • SK: CONFIG#TARGETS
  • Attributes:
    • calories, protein, carbs, fat: Float
    • displayUnit: String ("kcal" | "kj")
    • nutrientRegistry: Map\ (Definitions for extended nutrients)
    • version: Number (Optimistic Locking)
    • updatedAt: ISO Timestamp

Stores metadata for Personal Access Tokens.

  • SK: PAT#<Unique_ID>
  • Attributes:
    • name: String (e.g., "iPhone Shortcut")
    • scopes: List\ (e.g., ["read", "write"])
    • lastUsedAt: String (YYYY-MM-DD)
    • createdAt: ISO Timestamp
    • GSI1PK: PATHASH#<sha256_hash>
    • GSI1SK: METADATA

Tracks daily API consumption for quota enforcement.

  • SK: USAGE#<YYYY-MM-DD>
  • Attributes:
    • reads: Number (Atomic counter, sampled)
    • writes: Number (Atomic counter, sampled)
    • ttl: Number (Epoch timestamp for auto-cleanup)

Target Validation

  • Partial Updates: Users can update a subset of targets (e.g., only Protein).
  • Zero Values (0): Standard way to "clear" or disable a specific target.
  • Nulls: Ignored by the backend to prevent accidental deletion.

2. Aggregation Strategy

To efficiently support "Unique Days Logged" and "Long-term Charting", we maintain a separate "Summary Item" for each day.

The Day Summary Entity

Instead of scanning thousands of meal records, we update a summary record atomically using TransactWriteItems.

Structure:

  • SK: SUMMARY#<YYYY-MM-DD>
  • Attributes:
    • type: "DaySummary"
    • totalCalories, totalProtein, totalCarbs, totalFat: Float
    • extendedNutrients: Map\
    • mealCount: Number
    • updatedAt: ISO Timestamp

Pipeline Flow

Action DynamoDB Operation
Create Meal PutItem (Meal) + UpdateItem (ADD totals to Summary)
Delete Meal DeleteItem (Meal) + UpdateItem (SUBTRACT totals from Summary)

3. Data Format Migration (Version 4)

As of January 2026, the application transitioned from PascalCase/Int to camelCase/Float.

Hybrid Casing Strategy

To ensure zero downtime:

  1. Write Path: Always uses camelCase (FormatVersion 4+).
  2. Read Path: Checks both casings (e.g., item.calories || item.Calories).
  3. Maintenance: An optional standardization script is available at scripts/migrate_data_format.py.

4. Flexible Nutrient Schema (Implemented)

Implemented Jan 2026

We now support user-defined extended nutrients (e.g., Fiber, Sodium) using a Metadata-Driven DynamoDB Map strategy.

  • Strategy:
    1. Storage: Extended nutrients are stored in a DynamoDB Map extendedNutrients on both Meal and DaySummary.
    2. Configuration: The CONFIG#TARGETS entity acts as a schema registry, defining units and goals for custom nutrients.
  • Optimization: We use Frontend-Assisted Validation. The client passes a registrySnapshot during addMeal to filter nutrients without requiring an extra database read (RCU), keeping costs low.
  • Details: See the ADR 001: Flexible Nutrient Schema for full technical rationale.

5. Time Logic (The "4 AM Rule")

Single Source of Truth

If we ever change the definition of a "Day" (e.g., changing the 4 AM start time), we rely on the userDate attribute on the Meal records to rebuild the summaries.