How it works
End-to-End Pipeline
Step 01
Log
Win32 API polls the active window and process name every second.
Idle detection via
GetLastInputInfo splits sessions cleanly when you step away.
Step 02
Categorize
The full CSV is uploaded to Gemini 2.5 Flash in one call. It returns a structured
category column — Programming, Studying, Social Media, and more — validated for schema correctness.
Step 03
Store
SQLite with foreign-keyed category lookups, indexed timestamps, and
duplicate-safe imports keeps data clean across multiple runs.
Step 04
Visualize
Flask dashboard displays time per category with Today / Week / Month / All Time
filters, a sortable entry table, and a Chart.js bar chart.
Technical Details
Features
Non-blocking Background Logger
The logger runs in a daemon thread so Flask can serve requests concurrently.
A clean
stop() method joins the thread and flushes the final row to
disk, preventing data loss on shutdown.
True Idle Detection
Calls
GetLastInputInfo via ctypes every second. When idle time crosses
the threshold, the current session is closed and a new IDLE row begins —
so reported time reflects actual use, not just uptime.
AI Batch Categorization
Uploads the entire CSV to Gemini in a single Files API call with a structured prompt,
then validates the response for row count, column presence, and empty categories
before writing to disk.
Parameterized Dynamic SQL
Date filter clauses are constructed at runtime and injected via
cursor.execute() parameters — flexible Today / Week / Month queries
on the same base query, with no injection surface.
Code Highlight
Dynamic SQL Date Filtering
# Date clause built at runtime, injected safely via parameterized query if date_filter == 'today': today_start = datetime.combine(datetime.now().date(), datetime.min.time()).timestamp() today_end = datetime.combine(datetime.now().date(), datetime.max.time()).timestamp() date_clause = "AND pe.start_time >= ? AND pe.start_time <= ?" date_params = [today_start, today_end] elif date_filter == 'week': date_clause = "AND pe.start_time >= ?" date_params = [(datetime.now() - timedelta(days=7)).timestamp()] query = f""" SELECT COALESCE(c.category_name, 'Uncategorized') AS category, SUM(pe.end_time - pe.start_time) / 3600.0 AS hours_spent FROM process_entries pe LEFT JOIN categories c ON pe.category_id = c.category_id WHERE pe.end_time IS NOT NULL AND (pe.end_time - pe.start_time) >= ? {date_clause} GROUP BY category ORDER BY hours_spent DESC """ cursor.execute(query, [min_duration_seconds] + date_params)
Stack
Technologies Used
Core
Python
Win32 API
psutil
ctypes
AI & Data
Gemini 2.5 Flash
Google GenAI SDK
CSV / Pandas
Backend & Frontend
Flask
SQLite
Jinja2
Chart.js