Back to log(ger)

Under the hood

A visual tour of log(ger)’s SQLite database. Each animation below shows real table rows appearing, values flashing, and aggregates rebalancing as the corresponding operation runs in the app. Everything is local on disk — no servers, no sync.

The schema, at a glance

One SQLite file: ~/Library/Application Support/Logger/logger.db (packaged) or ./logger.db (dev). Four tiers in the core hierarchy and a few helpers around them.

Core hierarchy

category_groups

Research · Training · Personal · Courses

id, name, display_name
color, position, is_system

1:N

category_families

Salk · MPI · COGS · CSE…

id, name, display_name, color
group_id → category_groups

1:N

Full schema

Every table, every column, with types and key relationships annotated. PK primary key · FK foreign key · UQ unique · IDX indexed

category_groups

PKidINTEGER

UQnameTEXTNOT NULL

display_nameTEXT

descriptionTEXT

colorTEXT

positionINTEGERDEFAULT 0

is_systemBOOLEAN

created_atTEXT

category_families

PKidINTEGER

UQnameTEXTNOT NULL

display_nameTEXT

descriptionTEXT

colorTEXT

family_typeTEXTlegacy

FKgroup_idINTEGER→ category_groups.id

created_atTEXT

family_match_rules

PKidINTEGER

FKfamily_idINTEGER→ category_families.idCASCADE

UQmatch_typeTEXT"exact" | "prefix"

IDXpatternTEXTlowercased

positionINTEGER

created_atTEXT

sessions

PKidINTEGER

UQyearINTEGERNOT NULL

seasonTEXTNOT NULL

labelTEXT

start_dateTEXT

end_dateTEXT

is_activeBOOLEAN

source_fileTEXT

created_atTEXT

Watch the database change

Each panel shows the tables that the operation touches, with rows appearing, values flashing, and aggregates re-balancing as the app commits its writes. Everything loops — let it run for a few seconds to see the full sequence.

▶ START

Start a timer

You hit play on a category. A row appears in timer_entries — nothing else changes yet.

timer_entries

idcategorystart_timeactive

13Salk09:14

12COGS 17208:02

14Salk14:32

▸ INSERT INTO timer_entries with is_active = true

▸ No other tables touched — duration is unknown until stop

⏹ STOP

Stop a timer

Duration computes, the timer row updates, then observations and text_entries roll the change up.

timer_entries

idcategorydurationactive

14Salk —35m

observations

datecategoryminutes

2026-05-26Salk 70m105m

text_entries

datestudy_materials

2026-05-26 "meeting (60m), wrote analysis (35m)"

▸ duration_minutes = round((now − start − paused) / 60) = 35m

▸ observation for (2026-05-26, Salk) gets +35m

▸ today’s narrative appends the description

▸ if you picked Yesterday in the late-night prompt, date shifts before the upsert

＋ ADD

Add a manual entry

You forgot to time something. Insert + upsert + append, same downstream story as stop.

manual_entries

datecategoryminnote

2026-05-25Salk90debugging

observations

datecategoryminutes

2026-05-25Salk 120m210m

text_entries

datestudy_materials

2026-05-25 "…, debugging"

✎ EDIT

Edit an entry

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

timer_entries (row #87)

datecategoryduration

2026-05-252026-05-26 COGSSalk 60m75m

observations · SUBTRACT

2026-05-25COGS~~60m~~ → 0m

→

observations · UPSERT

2026-05-26Salk+75m

▸ Net effect: charts always reflect the current truth, no matter how an entry has been reshaped.

⚑ FAMILY

Add a new family

One insert into category_families, optionally with auto-link rules.

category_families

idnamegroupcolor

11salkResearch

13cseCourses

26roboticsResearch

family_match_rules

typepatternfamily

exactsalksalk

prefixcsecse

exactroboticsrobotics

▸ Existing categories aren’t touched — you assign them via the Family Manager.

📥 IMPORT

Import a CSV pair

One file pair fans out across six tables in a single transaction.

CSV

2026_spring_study.csv
2026_spring_text.csv

sessions · +1

categories · +10

categories.family_id linked

daily_records · +56

observations · +254

text_entries · +56

⇄ SWAP

Replace the database

The file on disk is swapped. The previous DB rolls into a single backup. Schema migrations re-run on the new file.

logger.db
current

uploaded.db
candidate

BAK

logger.db.bak
rolling backup

▸ VALIDATE magic bytes + open + SELECT 1

▸ COPY logger.db → logger.db.bak

▸ DISPOSE engine pool — open connections close

▸ MOVE candidate.db → logger.db

▸ init_db() idempotently catches up the schema

🗑 DELETE

Delete an entry

Subtract from the aggregate first, then drop the row. The narrative log stays.

observations

datecategoryminutes

2026-05-26Salk 105m70m

timer_entries

idcategoryduration

14Salk35m

▸ text_entries stays — the day’s narrative isn’t rewound.

Properties worth knowing

Aggregations are stored, not derived on read. observations is the truth for analytics — updated transactionally with every write so charts never have to scan timer/manual tables.
Schema migrations are idempotent. Every backend start runs init_db, which probes before it changes anything. Safe to restart at any time; safe to load an older DB.
The DB is the source of truth. Use Download current from Settings to archive a snapshot; Choose .db file to load one back.
Late-night attribution lives at the entry, not the aggregate. A timer started at 1am that you attributed to “yesterday” writes date = yesterday on the entry; the observation rolls up under yesterday too.
One rolling backup. Each DB swap overwrites logger.db.bak — if you want a longer trail, Download current first.

← Back to log(ger)