1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- Initial schema for timetrack database
-- Creates schema_version tracking, clients, and time_entries tables

CREATE TABLE IF NOT EXISTS schema_version (
  version INTEGER PRIMARY KEY,
  applied_at INTEGER NOT NULL  -- Unix timestamp
);

CREATE TABLE IF NOT EXISTS clients (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  target_hours REAL NOT NULL DEFAULT 40.0
);

CREATE TABLE IF NOT EXISTS time_entries (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  client_id INTEGER NOT NULL,
  start_time INTEGER NOT NULL,   -- Unix timestamp
  end_time INTEGER,               -- Unix timestamp, NULL = in progress
  FOREIGN KEY (client_id) REFERENCES clients(id),
  CHECK (end_time IS NULL OR end_time > start_time)
);

CREATE INDEX IF NOT EXISTS idx_time_entries_client_start
  ON time_entries(client_id, start_time);