Habit Tracker

A daily habit tracking application that stressed time-series data patterns, aggregations, and soft delete functionality.

Domain Overview

• Habits - Behaviors to track (exercise, reading, etc.)
• Categories - Organize habits by type
• Completions - Daily check-ins with notes
• Achievements - Unlockable badges for streaks
• Streaks - Consecutive day calculations

Schema

categories (id, name, deleted_at)
habits (id, name, category_id, frequency, deleted_at, created_at)
completions (id, habit_id, completed_date, notes)
achievements (id, name, description, requirement, badge_icon)

Friction Points & Solutions

Streak Calculations

Calculating consecutive completion days required complex date logic:

-- Current streak for a habit
current_streak (a_habit_id: INTEGER_64): INTEGER
    local
        result: SIMPLE_SQL_RESULT
    do
        result := db.query_with_args ("[
            WITH RECURSIVE dates AS (
                SELECT date('now') as d
                UNION ALL
                SELECT date(d, '-1 day') FROM dates
                WHERE EXISTS (
                    SELECT 1 FROM completions
                    WHERE habit_id = ?
                    AND completed_date = date(d, '-1 day')
                )
            )
            SELECT COUNT(*) as streak FROM dates
            WHERE EXISTS (
                SELECT 1 FROM completions
                WHERE habit_id = ? AND completed_date = dates.d
            )
        ]", <<a_habit_id, a_habit_id>>)
        Result := result.first.integer_value ("streak")
    end

Time-Series Aggregations

-- Completions per week
weekly_stats (a_habit_id: INTEGER_64): SIMPLE_SQL_RESULT
    do
        Result := db.query_with_args ("[
            SELECT strftime('%Y-W%W', completed_date) as week,
                   COUNT(*) as completions
            FROM completions
            WHERE habit_id = ?
            GROUP BY week
            ORDER BY week DESC
            LIMIT 12
        ]", <<a_habit_id>>)
    end

Soft Delete Pattern (Pre-Scopes)

This mock revealed the repetitive nature of soft delete filtering, which later drove the active_only scope feature:

-- Before scopes: manual filtering everywhere
db.select_from ("habits")
    .where ("category_id = ? AND deleted_at IS NULL", cat_id)
    .execute

-- After scopes (added in DMS phase)
db.select_from ("habits")
    .active_only
    .where ("category_id = ?", cat_id)
    .execute

Key Patterns

Achievement System

check_achievements (a_user_id: INTEGER_64)
        -- Check and unlock earned achievements
    local
        streak: INTEGER
    do
        across active_habits (a_user_id) as habit loop
            streak := current_streak (habit.integer_64_value ("id"))

            if streak >= 7 then
                unlock_achievement (a_user_id, "week_warrior")
            end

            if streak >= 30 then
                unlock_achievement (a_user_id, "monthly_master")
            end
        end
    end

Date-Based Queries

completions_today: SIMPLE_SQL_RESULT
    do
        Result := db.select_from ("completions")
            .columns ("c.*, h.name as habit_name")
            .join ("habits h", "h.id = c.habit_id")
            .where ("c.completed_date = date('now')")
            .execute
    end

habits_not_completed_today: SIMPLE_SQL_RESULT
    do
        Result := db.query ("[
            SELECT h.* FROM habits h
            WHERE h.deleted_at IS NULL
            AND NOT EXISTS (
                SELECT 1 FROM completions c
                WHERE c.habit_id = h.id
                AND c.completed_date = date('now')
            )
        ]")
    end

Lessons Learned

Source Code

• HABIT_TRACKER_APP - Main application class
• HABIT - Habit entity
• HABIT_COMPLETION - Daily check-in entity
• HABIT_STREAK - Streak tracking
• HABIT_ACHIEVEMENT - Achievement definitions