Timer Database Schema

Tables (2 only)

-- timers: one row per timer session
CREATE TABLE timers (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id          UUID NOT NULL,
  type             TEXT NOT NULL,               -- e.g., 'manual' | 'pomodoro' | 'countdown' | 'interval'
  title            TEXT,
  status           TEXT NOT NULL DEFAULT 'running', -- 'running' | 'paused' | 'stopped' | 'completed' | 'canceled'
  started_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  ended_at         TIMESTAMPTZ,
  -- Group multiple attempts/resets under one logical session
  base_id          UUID,                          -- root session id (self-reference to timers.id)
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (status IN ('running','paused','stopped','completed','canceled'))
);
CREATE INDEX timers_user_idx ON timers (user_id);
CREATE INDEX timers_base_idx ON timers (base_id);
CREATE INDEX timers_created_at_idx ON timers (created_at);
CREATE INDEX timers_status_idx ON timers (status);
CREATE INDEX timers_type_idx ON timers (type);

-- tags: labels attached to a timer (simple 1:N relation)
CREATE TABLE tags (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  timer_id         UUID NOT NULL REFERENCES timers(id) ON DELETE CASCADE,
  user_id          UUID NOT NULL,
  name             TEXT NOT NULL,
  status           TEXT NOT NULL DEFAULT 'active', -- 'active' | 'archived'
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX tags_timer_idx ON tags (timer_id);
CREATE INDEX tags_user_idx ON tags (user_id);
CREATE UNIQUE INDEX tags_unique_per_timer ON tags (timer_id, name);

Notes

• Exactly two tables: timers and tags, as requested.
• Tags are per timer via timer_id (1:N). If you later need reusable global tags across many timers, introduce a junction table (timer_tags) and a catalog table (tag_catalog).
• Keep inserts/updates simple: update status on timers as the lifecycle changes, and attach multiple tags rows to a timer.
• base_id groups repeated attempts: set base_id to the first timer’s id. New attempts link to the same base_id, enabling aggregated reporting.

Example Operations

-- Start a timer
INSERT INTO timers (user_id, type, title) VALUES ($1, 'manual', 'Focus') RETURNING id;

-- Add tags
INSERT INTO tags (timer_id, user_id, name) VALUES ($timer_id, $1, 'work');
INSERT INTO tags (timer_id, user_id, name) VALUES ($timer_id, $1, 'deep-focus');

-- Pause
UPDATE timers SET status='paused', updated_at=now() WHERE id=$timer_id;

-- Resume
UPDATE timers SET status='running', updated_at=now() WHERE id=$timer_id;

-- Stop
UPDATE timers SET status='completed', ended_at=now(), updated_at=now() WHERE id=$timer_id;

-- Read total seconds for a timer (simple start/end difference)
SELECT CASE WHEN ended_at IS NOT NULL
            THEN EXTRACT(EPOCH FROM (ended_at - started_at))::BIGINT
            ELSE EXTRACT(EPOCH FROM (now() - started_at))::BIGINT
       END AS total_seconds
FROM timers WHERE id=$timer_id;

-- Start a new attempt linked to the original base session
-- First attempt: base_id = id (auto-set by trigger below if not provided)
-- Subsequent attempt:
INSERT INTO timers (user_id, type, title, base_id)
VALUES ($1, 'manual', 'Focus retry', $base_id) RETURNING id;

-- Aggregate time across all attempts under the same base_id
SELECT SUM(
  CASE WHEN t.ended_at IS NOT NULL
       THEN EXTRACT(EPOCH FROM (t.ended_at - t.started_at))::BIGINT
       ELSE EXTRACT(EPOCH FROM (now() - t.started_at))::BIGINT
  END
) AS total_group_seconds
FROM timers t WHERE t.base_id = $base_id;

Triggers (recommended)

-- Touch updated_at and validate ended_at >= started_at
CREATE OR REPLACE FUNCTION timers_touch_validate()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.ended_at IS NOT NULL AND NEW.ended_at < NEW.started_at THEN
    RAISE EXCEPTION 'ended_at % cannot be before started_at %', NEW.ended_at, NEW.started_at;
  END IF;
  NEW.updated_at := now();
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_timers_touch
BEFORE INSERT OR UPDATE ON timers
FOR EACH ROW EXECUTE FUNCTION timers_touch_validate();

-- Status transitions: default base_id on insert; stamp ended_at on terminal states
CREATE OR REPLACE FUNCTION timers_pause_resume_accumulator()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    -- Default base_id to self on first creation if not provided
    IF NEW.base_id IS NULL THEN
      NEW.base_id := NEW.id;
    END IF;
    RETURN NEW;
  END IF;

  -- running -> stopped/completed
  IF OLD.status = 'running' AND NEW.status IN ('stopped','completed','canceled') THEN
    IF NEW.ended_at IS NULL THEN NEW.ended_at := now(); END IF;
  END IF;

  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_timers_accumulator
BEFORE INSERT OR UPDATE ON timers
FOR EACH ROW EXECUTE FUNCTION timers_pause_resume_accumulator();

This minimal schema keeps your model simple while covering the core fields from your diagram: start/end time, ids, type, status, and per-timer tags.