log(ger)

Under the hood

No server, no sync — log(ger) is one SQLite file on your disk. Here is how your time is shaped, how every write rolls up, and how the app reads it all back fast. Everything below is the real schema.

~/Library/Application Support/Logger/logger.db · ./logger.db in dev

One file, four levels

The core is a single chain: a group holds families, a family holds per-session categories, a category holds the entries you log. Everything else hangs off this spine.

category_groupsResearch · Courses · Personal 1→N category_familiesSalk · MPI · COGS… 1→N categoriesone per session 1→N timer_entries
manual_entriesthe minutes

Every table, the columns that matter

Eleven core tables. id is the primary key everywhere; → marks a foreign key.

sessions
  • year, season, label
  • start_date, end_date
  • is_active
category_groups
  • name, display_name
  • color, position
  • is_system
category_families
  • name, display_name, color
  • group_id → groups
categories
  • name, display_name
  • session_id → sessions
  • family_id → families
timer_entries
  • session_id, category_id
  • date, start_time, end_time
  • duration_minutes, is_paused
manual_entries
  • session_id, category_id
  • date, duration_minutes
  • description, start_time
daily_records
  • session_id → sessions
  • date, week_number
  • total_minutes
observations
  • daily_record_id, category_id
  • minutes
  • source (import/timer/manual)
break_days
  • date (unique)
  • label
family_match_rules
  • family_id → families
  • match_type (exact / prefix)
  • pattern
text_entries
  • session_id → sessions
  • date, location
  • notes, study_materials
settings · chat_messages · ai_descriptions
  • key/value config
  • chat history
  • generated summaries

Watch a write land

Stop a 45-minute timer and three tables change in one transaction — the entry is recorded, the per-day-per-category aggregate is upserted, and the day’s total recomputes. Charts read the aggregate, never the raw rows.

timer_entries INSERT
May 25Salkearlier
May 25Salk45m
→
observations UPSERT
May 25 · Salk120m
→
daily_records RECOMPUTE
May 25 total200m

Every other change, same discipline

Whatever you do, the stored aggregate stays correct — writes always go through, never around it.

Add a manual entry

manual_entries +1observations ↑daily_records ↻

Insert, then the same upsert-and-recompute as a timer stop. Identical downstream.

Edit an entry

observations −oldobservations +new

Change the date, category, or duration and the aggregate rebalances: subtract from the old bucket, add to the new.

Delete an entry

observations ↓row removed

Subtract from the aggregate first; the observation is dropped if it hits zero. Then the row goes.

Mark a break

break_days +1no minutes

A date (or range) is inserted with a label. It adds no time; the streak query simply steps over it.

Import a CSV pair

sessionscategoriesdaily_recordsobservationstext_entries

One file pair fans across the tables in a single transaction; new categories auto-link to families by rule.

Replace the database

validatelogger.db.bakswapinit_db

Magic-bytes checked, current file rolled into one backup, the file swapped, then idempotent migrations re-run.

Reading it back, fast

Because the aggregate is always current, every view is a cheap read — no scanning thousands of raw entries.

∑

Pre-aggregated truth

observations holds minutes per day per category. Dashboards and charts query it directly.

⊞

Two SQL views

v_daily_totals (date × category) and v_family_totals (family per session) pre-join the hierarchy for analytics.

🔥

Streaks, break-aware

The streak walks daily_records back from today; a day in break_days bridges the gap instead of resetting it.

Why it holds up

  • Aggregations are stored, not derived. observations is written transactionally with every entry — charts never scan raw timer or manual rows.
  • Every mutation is transactional. Insert/upsert/recompute commit together; a failure rolls the whole thing back, so totals can’t drift from entries.
  • Migrations are idempotent. Every start runs init_db, which probes the schema before it changes anything. Safe to restart; safe to load an older database.
  • The file is the source of truth. Download current archives a snapshot; Choose .db file loads one back. Each swap keeps one rolling logger.db.bak.
  • Dates live on the entry. A timer started at 1am attributed to “yesterday” writes date = yesterday; the aggregate rolls up under yesterday too.

Every table and column lives in models.py.