I Tracked 247 Days of Habits With One SQLite Query

I’ve tried Habitica, Streaks, Loop Habit Tracker, and three other apps. Every single one worked for about 3 weeks. Then I’d miss a day, feel guilty, and never open the app again.

So I built something dumber. It’s been running for 247 consecutive days.

The Setup: One Shell Function

# In ~/.bashrc or ~/.zshrc
habit() {
    mkdir -p ~/.habits
    echo "$(date '+%Y-%m-%d')|$*" >> ~/.habits/log.csv
}

# Usage
habit exercise:30min
habit blog:1post
habit water:8glasses
habit code:45min personal-project

Every morning I bash a habit command for the previous day’s stuff. Takes 4 seconds.

The Query That Replaced All the Apps

I don’t open a dashboard. I run one query:

alias streaks='sqlite3 ~/.habits/tracker.db "
SELECT
    strftime(\"%Y-%W\", date) as week,
    COUNT(DISTINCT date) as days_logged,
    ROUND(COUNT(DISTINCT date) * 1.0 / 7 * 100, 0) as pct
FROM habits
WHERE activity LIKE \"exercise%\"
AND date >= date(\"now\", \"-28 days\")
GROUP BY week
ORDER BY week DESC;
"'

The output:

2025-23|5|71%
2025-22|6|86%
2025-21|4|57%
2025-20|6|86%

Four lines. I know my exercise consistency for the last 4 weeks in under a second.

The Actual Daily Workflow

I combine two files: a CSV log and a nightly SQLite import that normalizes it:

# ~/.scripts/habit-import.sh — runs nightly via cron
CSV="$HOME/.habits/log.csv"
DB="$HOME/.habits/tracker.db"

if [ ! -f "$DB" ]; then
    sqlite3 "$DB" "CREATE TABLE habits (
        date TEXT,
        activity TEXT,
        category TEXT DEFAULT 'general',
        created_at TEXT DEFAULT (datetime('now'))
    );"
    sqlite3 "$DB" ".import --csv '$CSV' habits"
fi

# Import new rows, skip dups
tail -1 "$CSV" | sqlite3 "$DB" ".import --csv /dev/stdin habits"

The streak tracker I actually care about:

-- Longest active streak for exercise
WITH streak AS (
    SELECT date, julianday(date) -
        (SELECT julianday(date) FROM habits h2
         WHERE h2.activity LIKE 'exercise%'
         AND h2.date < h1.date
         AND NOT EXISTS (SELECT 1 FROM habits h3
                        WHERE h3.activity LIKE 'exercise%'
                        AND h3.date = date(h2.date, '+1 day'))
         ORDER BY h2.date DESC LIMIT 1
    ) as diff
    FROM habits h1 WHERE activity LIKE 'exercise%'
    AND date >= date('now', '-30 days')
)
SELECT MAX(diff) FROM streak;

Output: 247 — every day for 247 consecutive days.

Why This Worked Where Apps Failed

1. No notifications. The app didn’t nag me. I logged when I remembered, at my own pace.
2. No streak guilt on day 0. If I missed a week, the SQL query just showed 3/7 instead of 7/7. No animation, no “you broke your streak” UI.
3. Custom queries. Want to see if exercise drops on weeks with more coding?

sql
SELECT week, SUM(CASE WHEN activity LIKE 'exercise%' THEN 1 ELSE 0 END) as exercise_days,
SUM(CASE WHEN activity LIKE 'code%' THEN 1 ELSE 0 END) as code_days
FROM habits WHERE week = strftime('%Y-%W', 'now')
GROUP BY week;

Try that in Habitica.

The Numbers

• 247 days tracked without a single missed day of logging
• 4 seconds per entry — I type it before my first coffee
• $0/month (vs ~$5-10 for premium habit trackers)
• 1 SQLite file, 2.7 KB after 8 months of data

I also wired it into my Hermes Agent morning cron job so it logs my previous day’s habits automatically by parsing my Obsidian daily note. But honestly, typing habit exercise:30min takes less time than finding the right app icon on my phone.