wotra/PLAN.md

Wotra — Working Time Tracker: Implementation Plan

1. High-Level Architecture

┌─────────────────────────┐         ┌──────────────────────────┐
│   Svelte PWA (client)   │ ◄─────► │    Go service (API)      │
│  - IndexedDB cache      │  HTTPS  │  - REST/JSON endpoints   │
│  - Service Worker       │  JSON   │  - Business logic        │
│  - Sync queue           │         │  - SQLite (modernc)      │
└─────────────────────────┘         └──────────────────────────┘

Single-user, self-hosted. One Go binary serves the Svelte SPA and the API.

2. Backend (Go)

Stack

• net/http + chi for routing
• modernc.org/sqlite (pure Go, no CGO) with database/sql
• SQLite WAL mode, foreign keys on, busy timeout 5s
• golang-migrate or hand-rolled migrations
• Structured logging via slog

Project Layout

cmd/wotra/main.go
internal/
  config/        # timezone, token, port
  domain/        # Entry, ClosedDay, ClosedWeek, Settings types
  store/         # sqlite repos + migrations
  handler/       # HTTP handlers, middleware
  service/       # business rules (close day/week, merge, overtime)
migrations/      # SQL migration files
web/             # SvelteKit app (embedded in binary at build time)
mise.toml        # task/tool runner (replaces Makefile)
PLAN.md

Database Schema

-- Raw tracking events (source of truth for open days)
CREATE TABLE entries (
  id            TEXT PRIMARY KEY,           -- UUIDv7 (client-generated, sync-friendly)
  start_time    INTEGER NOT NULL,           -- unix epoch ms, UTC
  end_time      INTEGER,                    -- NULL while running
  auto_stopped  INTEGER NOT NULL DEFAULT 0, -- 1 = stopped at midnight automatically
  note          TEXT,
  day_key       TEXT NOT NULL,              -- 'YYYY-MM-DD' in configured local tz
  updated_at    INTEGER NOT NULL,
  deleted_at    INTEGER                     -- soft delete for sync
);
CREATE INDEX idx_entries_day ON entries(day_key);

-- Closed days: merged result, effectively immutable
CREATE TABLE closed_days (
  day_key       TEXT PRIMARY KEY,           -- 'YYYY-MM-DD'
  start_time    INTEGER,                    -- min(entries.start_time), NULL for non-work kinds
  end_time      INTEGER,                    -- max(entries.end_time), NULL for non-work kinds
  worked_ms     INTEGER NOT NULL,           -- sum of entry durations (breaks excluded)
  kind          TEXT NOT NULL,              -- 'work' | 'holiday' | 'vacation' | 'sick'
  closed_at     INTEGER NOT NULL,
  updated_at    INTEGER NOT NULL
);

-- Closed weeks: snapshot of expectations at close time
CREATE TABLE closed_weeks (
  week_key      TEXT PRIMARY KEY,           -- 'YYYY-Www' ISO week
  expected_ms   INTEGER NOT NULL,           -- frozen at close
  worked_ms     INTEGER NOT NULL,           -- sum of closed_days.worked_ms for the week
  delta_ms      INTEGER NOT NULL,           -- worked_ms - expected_ms (signed)
  closed_at     INTEGER NOT NULL,
  updated_at    INTEGER NOT NULL
);

-- Settings with effective-from semantics so past weeks aren't retroactively changed
-- Migration 003: switched to TEXT UUID primary key; added updated_at
CREATE TABLE settings_history (
  id              TEXT PRIMARY KEY,             -- UUID (client-generated, sync-friendly)
  effective_from  TEXT NOT NULL,                -- 'YYYY-MM-DD'
  hours_per_week  REAL NOT NULL,
  workdays_mask   INTEGER NOT NULL DEFAULT 31,  -- bits Mon=1..Sun=64; Mon-Fri = 31
  timezone        TEXT NOT NULL DEFAULT 'UTC',
  created_at      INTEGER NOT NULL,
  updated_at      INTEGER NOT NULL
);

-- Sync event log (append-only; TTL-pruned; prune marker for stale-client detection)
CREATE TABLE sync_log (
  entity        TEXT NOT NULL,              -- 'entries' | 'closed_days' | ... | '_pruned'
  entity_id     TEXT NOT NULL,
  op            TEXT NOT NULL,              -- 'upsert' | 'delete' | 'marker'
  version       INTEGER NOT NULL,           -- monotonic server-assigned
  payload       TEXT NOT NULL,              -- JSON snapshot
  logged_at     INTEGER NOT NULL,           -- unix ms; used for TTL pruning
  PRIMARY KEY (entity, entity_id, version)
);

-- Balance adjustments: manual corrective entries that modify the overall overtime balance
-- without touching week math. Signed delta_ms (positive = credit, negative = debit).
CREATE TABLE balance_adjustments (
  id           TEXT PRIMARY KEY,            -- client-generated UUIDv7 (sync-friendly)
  delta_ms     INTEGER NOT NULL,            -- signed; non-zero enforced at service layer
  note         TEXT NOT NULL DEFAULT '',    -- optional free-text reason
  effective_at INTEGER NOT NULL,            -- unix ms; backdatable
  created_at   INTEGER NOT NULL,
  updated_at   INTEGER NOT NULL
);
CREATE INDEX idx_balance_adjustments_effective_at ON balance_adjustments(effective_at);

API Surface (REST/JSON)

# Entries
POST   /api/entries/start              { note? }            -> Entry
POST   /api/entries/{id}/stop                               -> Entry
GET    /api/entries?from=YYYY-MM-DD&to=YYYY-MM-DD
POST   /api/entries                    { start_ms, end_ms, note? }  -> Entry (completed interval)
PUT    /api/entries/{id}               { start_time?, end_time?, note? }
DELETE /api/entries/{id}               (soft delete)

# Days
GET    /api/days?from=YYYY-MM-DD&to=YYYY-MM-DD
POST   /api/days/{day_key}/close                            -> ClosedDay
POST   /api/days/{day_key}/mark        { kind }             # holiday|vacation|sick
DELETE /api/days/{day_key}/close       (reopen day)

# Weeks
GET    /api/weeks?from=YYYY-Www&to=YYYY-Www
POST   /api/weeks/{week_key}/close                          -> ClosedWeek
DELETE /api/weeks/{week_key}/close     (reopen week)
GET    /api/weeks/balance                                   -> BalanceSummary

# Balance adjustments
GET    /api/balance/adjustments                             -> []BalanceAdjustment
POST   /api/balance/adjustments        { delta_ms, note?, effective_at }  -> BalanceAdjustment
PUT    /api/balance/adjustments/{id}   { delta_ms, note?, effective_at }  -> BalanceAdjustment
DELETE /api/balance/adjustments/{id}

# Settings
GET    /api/settings                                        # current effective settings
PUT    /api/settings                   { effective_from, hours_per_week, workdays_mask, timezone }
GET    /api/settings/history

# Sync
GET    /api/sync/pull?since=N                               -> { changes[], server_version } | 410 Gone
POST   /api/sync/push                  { changes[] }        -> { applied[], skipped[] }

# Health
GET    /healthz                                             (unauthenticated)

Key Business Rules

• day_key assignment: based on start_time in the configured timezone.
• Midnight enforcement: end_time must be on the same calendar day as start_time. Server auto-stops any running entry at 23:59:59 local time via a background goroutine. Client warns the user.
• Close day: requires all entries for that day to have end_time != NULL (409 otherwise). Computes worked_ms = sum(end_time - start_time) across non-deleted entries. Entries are preserved but the day is now locked.
• Mark day: writes a closed_days row with kind != 'work'; worked_ms = expected daily ms (hours_per_week * 3600000 / popcount(workdays_mask)) for workdays, 0 for non-workdays.
• Close week: all workdays in the week must have a closed_days row. Sums worked_ms, snapshots expected_ms from settings effective at close time, stores signed delta_ms.
• Settings change: new row in settings_history with effective_from. Past closed weeks are unaffected because they store a frozen snapshot.
• Future intervals: server rejects CreateInterval and Update if the resulting day key is in the future (400 Bad Request).

Auth

• AUTH_TOKEN environment variable on the server.
• All /api/* routes require Authorization: Bearer <token>.
• Client stores token in localStorage after a one-time setup screen.
• /healthz is unauthenticated.

3. Frontend (Svelte PWA)

Stack

• SvelteKit (static adapter for embedding in Go binary)
• TypeScript
• vite-plugin-pwa + Workbox for service worker and manifest
• dexie for typed IndexedDB access
• vitest for unit tests

Screens

Screen	Purpose
Week	Week picker, 7-day chip strip, day detail panel for selected day. Default: today selected (if in week) else Monday. Today tab in bottom nav is a shortcut that selects today.
History	Month/week list of closed days and weeks with overtime indicators
Settings	hours/week, workdays mask, timezone, effective-from, token setup

The old dedicated Today route is merged into the Week view (see § Merged Week+Today View below).

Merged Week+Today View (/week)

Three regions:

1. Week header — week picker, week totals, Close Week button, closed-week banner.
2. Day strip — 7 chips (Mon–Sun), each showing: weekday label + date, mini progress bar (worked/expected), kind icon, closed badge, today highlight. Horizontal scroll-snap on mobile. Selected chip scrolls into view. role="tablist" with keyboard arrow navigation.
3. Day detail panel — for the selected day, capabilities gated per table:

Condition	canStartStop	canAddInterval	canEditEntries	canMarkKind	canCloseDay
Future	false	false	false	true	false
Today, open	true	true	true	true	true*
Today, closed	false	false	false	true	reopen
Past, open	false	true	true	true	true
Past, closed	false	false	false	true	reopen

*Close Day button disabled with tooltip while a running entry exists.

URL state: ?week=2026-W18&day=2026-04-30. Bare /week canonicalizes via replaceState. Chip clicks use replaceState (no history push). Week navigation (prev/next) uses goto (history push). Default day selection on week change: today if in week, else Monday.

Bottom nav: "Today" tab is a shortcut link to /week?week=<currentWeek>&day=<todayKey>. Active when route is /week and ?day === todayKey().

Offline Strategy

Fully offline-first: the UI always reads from and writes to Dexie (IndexedDB) without touching the network. A background sync loop (every 30 s) reconciles with the server.

1. App shell precached by Workbox.
2. All reads come directly from Dexie — no network latency in the hot path.
3. All mutations write to Dexie + outbox immediately and return. The background sync loop pushes the outbox via POST /api/sync/push and pulls server changes via GET /api/sync/pull.
4. Exceptions: day/week close and reopen require server-side computation and remain server-only. triggerSync() is called after them to update Dexie promptly without waiting 30 s.
5. On 410 Gone (server pruned data the client hasn't seen), coldStart() wipes Dexie and re-pulls everything from version 0.

4. Sync Protocol

• Every mutable row carries updated_at (ms) on the client and version (server monotonic int) once accepted.
• Pull: POST /api/sync/pull { since_version: N } → rows with version > N + server_version.
• Push: client sends batched upsert/delete events with updated_at. Server applies last-write-wins per row. Conflicts returned to client, which re-pulls those rows.

5. Build & Deployment

# Single-binary production build (via mise)
mise run build
# 1. npm run build  (inside web/)
# 2. go build -tags production -o wotra ./cmd/wotra
#    -> embeds web/build via go:embed

Dev mode: Go on :8080, Vite dev server on :5173 with /api proxied to Go.

Environment variables:

Variable	Default	Description
AUTH_TOKEN	required	Bearer token for API access
PORT	8080	Server port
DB_PATH	wotra.db	SQLite file path
TZ	UTC	Server timezone for day-key calculation

6. Milestones

M1–M6 — Done

Full backend + frontend implemented, PWA offline sync, single-binary build, mise.toml task runner.

M7 — Merge Today+Week views ✅ in progress

Staged implementation:

Stage	Goal	Status
0	Add Vitest setup	pending
1	Day strip on week view (read-only)	pending
2	Extract DayDetail component; render today in week view	pending
3	Day selection + URL-driven state	pending
4	Backend guard: reject future-day intervals	pending
5	Bottom nav Today shortcut; delete /today route	pending

M8 — Balance adjustments ✅

Manual corrective entries on the History page that adjust the overall overtime balance without touching week math. Separate balance_adjustments table with signed delta_ms, optional note, and effective_at (backdatable). Balance summary combines Σ closed_weeks.delta_ms + Σ balance_adjustments.delta_ms.

M9 — Sync redesign ✅

Full offline support with online-first, offline-fallback mutation strategy.

Backend:

• Migration 003: logged_at on sync_log; settings_history migrated to UUID TEXT PK with updated_at.
• SyncStore.Prune(ctx, ttl): deletes rows older than TTL and writes a _pruned marker at the boundary version. Clients pulling with since < marker_version receive ErrSyncStale → 410 Gone.
• GET /api/sync/pull?since=N: calls Prune lazily (30-day TTL), returns changes or 410.
• POST /api/sync/push: accepts batched outbox items; applies last-updated_at-wins for entries, balance_adjustments, settings_history. closed_days/closed_weeks are server-only and skipped. Returns {applied, skipped}.

Frontend:

• client.ts: all mutation methods (entries, balance, settings) catch TypeError (network error) and fall back to writing directly to Dexie + enqueuing in the outbox. IDs for offline creates are generated client-side via crypto.randomUUID().
• sync.ts: pushOutbox sends outbox to POST /api/sync/push; on success removes applied items. pullChanges uses GET /api/sync/pull?since=N; on 410 calls coldStart() and retries. coldStart() clears all Dexie tables and resets last_version=0.
• db.ts: Dexie v3 — settings_history key path changed to id (string); upgrade handler clears the table for repopulation via pull.
• Settings page: editingId and ID params updated from number to string.

M10 — Offline-first client ✅

Switched from online-first/offline-fallback to fully offline-first.

• client.ts: all reads now come directly from Dexie (no server fetch). All mutations write to Dexie + outbox immediately and return without waiting for the server.
• sync.ts: added triggerSync() for imperative sync after server-only mutations. Updated comment header.
• DayDetail.svelte, week/+page.svelte: call triggerSync() after day/week close and reopen so Dexie reflects server-computed state promptly.
• +layout.svelte: removed offline banner (no longer meaningful; behaviour is identical online or offline).
• online.ts: deleted (unused).

M11 — Future

CSV/JSON export, monthly summary view.

7. Decisions & Rationale

Decision	Choice	Reason
Midnight crossing	Disallow; auto-stop at 23:59:59	Keeps day-boundary logic simple and lossless
Running entry at close	Refuse with 409	Explicit is better than implicit
Expected hours split	Even across workdays	Simplest correct model; per-day config out of scope
Auth v1	Single shared bearer token	Single-user self-hosted; minimal friction
Deployment	Single Go binary embeds SPA	Simplest self-hosting story
SQLite driver	modernc.org/sqlite (pure Go)	No CGO; easy cross-compilation
Breaks in closed day	Not included in worked_ms	Sum of entry durations only; gaps between entries are breaks
Week close settings	Use close-time date, not week Monday	Settings changed mid-week apply to that week's close
weekDayKeys formula	(weekday+6)%7 for days-since-Monday	Avoids sign bug when Jan 4 is Sunday (affects year 2026)
Merged Today+Week	Single /week route with day selection	Reduces duplication; week context always visible
Balance adjustments	Separate balance_adjustments table	Avoids conflating measured deltas with manual corrections; preserves week math invariant (delta_ms = worked - expected)
Balance adjustment scope	Closed weeks only for auto balance	In-progress week delta shown separately in week view; mixing would make balance jitter
Balance adjustment IDs	TEXT (UUIDv7, client-generated)	Consistent with entries; allows offline creation and sync
Settings history PK	TEXT UUID (migration 003)	Consistent with other entities; enables offline create; updated_at enables last-write-wins sync
Sync prune strategy	Prune marker row at boundary version	No extra table; client detects stale state from the log itself; 410 triggers full re-sync
Sync conflict resolution	Last updated_at wins	Server is authoritative; simple to implement and reason about for single-user
Offline mutation flow	Offline-first	All reads/writes go through Dexie; background sync loop reconciles with server. Day/week close remains server-only (requires server computation); triggerSync() called after.