Harvest & Toggl Replacement — Entire Agent Prompt Specification

# Blueprint: B2B Time Tracker & Customizable Invoicing Engine (Harvest / Toggl Clone)
**Version:** 1.2.0 // Production Vetted  
**Target Agents:** Claude Code, Cursor, Windsurf, Devin  
**Methodology:** Strict Planning Mode, Schema-First Relational DDL, 200-Line File Cap, Two-Gate Regression Testing  

---

## 1. Executive Summary & Tech Stack Constraints
An agency-grade B2B time tracking and invoicing engine allowing distributed teams and contractors to track live hours, assign billable rates, detect idle coffee breaks, and generate professional sequential invoices (`INV-2026-001`) with customizable tax and itemized timestamps.

### Architectural Constraints:
- **Framework:** Next.js 14+ (App Router) with Server Actions & Route Handlers
- **Language:** TypeScript 5+ (Strict Mode: `noImplicitAny: true`, `strictNullChecks: true`)
- **Database:** PostgreSQL (Supabase / Neon) via PgBouncer Pooler (Port 6543)
- **Data Access:** Drizzle ORM or Kysely (zero unindexed joins, prepared statements)
- **State Management:** TanStack Query for server cache; local timer drift delta calculation
- **Testing:** Vitest + React Testing Library + Playwright E2E
- **File Limit:** Hard 200-line cap per file. Atomic presentation components, custom hooks, and server actions separated.

---

## 2. Strict Relational Schema (PostgreSQL DDL)

```sql
-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Workspaces (Tenant Isolation)
CREATE TABLE workspaces (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    default_currency VARCHAR(3) DEFAULT 'USD',
    invoice_prefix VARCHAR(10) DEFAULT 'INV',
    next_invoice_number INT DEFAULT 1,
    tax_rate_percent NUMERIC(5,2) DEFAULT 0.00,
    created_at TIMESTAMPTZ NOT NULL DEFAULT timezone('utc', now()),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT timezone('utc', now())
);

-- Users / Members
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    email VARCHAR(255) NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL CHECK (role IN ('owner', 'admin', 'contractor', 'viewer')),
    default_hourly_rate_cents INT DEFAULT 10000, -- in integer cents ($100.00/hr)
    created_at TIMESTAMPTZ NOT NULL DEFAULT timezone('utc', now())
);
CREATE INDEX idx_users_workspace ON users(workspace_id);

-- Clients & Projects
CREATE TABLE clients (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    billing_email VARCHAR(255),
    address_line1 VARCHAR(255),
    created_at TIMESTAMPTZ NOT NULL DEFAULT timezone('utc', now())
);
CREATE INDEX idx_clients_workspace ON clients(workspace_id);

CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    client_id UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    hourly_rate_cents INT NOT NULL DEFAULT 10000,
    is_archived BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT timezone('utc', now())
);
CREATE INDEX idx_projects_workspace ON projects(workspace_id);

-- Time Entries (Live Timers & Manual Logs)
CREATE TABLE time_entries (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE RESTRICT,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    started_at TIMESTAMPTZ NOT NULL,
    stopped_at TIMESTAMPTZ, -- NULL while timer is actively running
    duration_seconds INT GENERATED ALWAYS AS (
        CASE WHEN stopped_at IS NOT NULL THEN EXTRACT(EPOCH FROM (stopped_at - started_at))::INT ELSE NULL END
    ) STORED,
    hourly_rate_cents INT NOT NULL,
    notes TEXT,
    is_invoiced BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT timezone('utc', now())
);
CREATE INDEX idx_time_entries_workspace ON time_entries(workspace_id);
CREATE INDEX idx_time_entries_user_active ON time_entries(user_id) WHERE stopped_at IS NULL;
CREATE INDEX idx_time_entries_started_at ON time_entries(started_at);

-- Invoices & Sequential Locks
CREATE TABLE invoices (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    client_id UUID NOT NULL REFERENCES clients(id) ON DELETE RESTRICT,
    invoice_number VARCHAR(50) NOT NULL, -- e.g. INV-2026-001
    status VARCHAR(20) NOT NULL CHECK (status IN ('draft', 'sent', 'paid', 'void')),
    issue_date DATE NOT NULL,
    due_date DATE NOT NULL,
    subtotal_cents INT NOT NULL DEFAULT 0,
    tax_percent NUMERIC(5,2) NOT NULL DEFAULT 0.00,
    tax_cents INT NOT NULL DEFAULT 0,
    total_cents INT NOT NULL DEFAULT 0,
    notes TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT timezone('utc', now()),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT timezone('utc', now()),
    CONSTRAINT uq_workspace_invoice_number UNIQUE (workspace_id, invoice_number)
);
CREATE INDEX idx_invoices_workspace ON invoices(workspace_id);

-- Invoice Line Items (Mapping time ranges)
CREATE TABLE invoice_line_items (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    invoice_id UUID NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
    time_entry_id UUID REFERENCES time_entries(id) ON DELETE SET NULL,
    description TEXT NOT NULL,
    quantity_hours NUMERIC(7,2) NOT NULL,
    unit_rate_cents INT NOT NULL,
    amount_cents INT NOT NULL
);
CREATE INDEX idx_invoice_line_items ON invoice_line_items(invoice_id);
```

---

## 3. Modular File System Blueprint (Under 200 Lines Per File)

```
src/
├── app/
│   ├── (auth)/
│   │   ├── login/page.tsx                  # < 90 lines
│   │   └── layout.tsx                      # < 50 lines
│   ├── (dashboard)/
│   │   ├── timer/page.tsx                  # < 120 lines
│   │   ├── projects/page.tsx               # < 140 lines
│   │   ├── invoices/page.tsx               # < 160 lines
│   │   └── invoices/[id]/page.tsx          # < 180 lines
│   └── api/
│       ├── timer/route.ts                  # < 110 lines
│       └── invoices/route.ts               # < 170 lines
├── components/
│   ├── timer/
│   │   ├── timer-display.tsx               # < 95 lines
│   │   ├── timer-controls.tsx              # < 85 lines
│   │   └── idle-prompt-dialog.tsx          # < 120 lines
│   ├── invoices/
│   │   ├── invoice-generator-modal.tsx     # < 190 lines
│   │   ├── invoice-preview-sheet.tsx       # < 175 lines
│   │   └── invoice-status-badge.tsx        # < 60 lines
│   └── ui/
│       ├── button.tsx, badge.tsx, modal.tsx # Atomic UI primitives
├── hooks/
│   ├── use-active-timer.ts                 # < 140 lines (drift delta logic)
│   └── use-idle-detector.ts                # < 110 lines (activity listener)
├── server/
│   ├── actions/
│   │   ├── timer-actions.ts                # < 160 lines
│   │   └── invoice-actions.ts              # < 190 lines (atomic sequence lock)
│   └── db/
│       ├── client.ts                       # < 60 lines (Port 6543 pooler)
│       └── schema.ts                       # < 190 lines (Drizzle definitions)
└── lib/
    ├── currency.ts                         # < 50 lines (cents conversion)
    ├── invoice-sequence.ts                 # < 90 lines (atomic counter generator)
    └── time-math.ts                        # < 130 lines (UTC drift & DST math)
```

---

## 4. State Machine & Business Logic Invariants
1. **Clock Drift Recovery:** The UI never increments seconds with naive `setInterval`. Elapsed time is strictly computed as `delta = Math.floor((Date.now() - startedAtUTC) / 1000)`. On `window.addEventListener('visibilitychange')`, the delta is re-evaluated immediately from the server timestamp.
2. **Atomic Invoice Numbering:** Invoice generation locks the `workspaces` table row (`SELECT next_invoice_number FROM workspaces WHERE id = $1 FOR UPDATE`) to prevent duplicate invoice numbers under race conditions.
3. **Single Active Timer Rule:** A database constraint or transactional check verifies `WHERE user_id = $1 AND stopped_at IS NULL` has a count of 0 before starting a new timer.
4. **Currency Safety:** All monetary values (hourly rates, subtotals, tax, totals) are stored as integer cents to eliminate JavaScript floating-point errors (`0.1 + 0.2 = 0.30000000000000004`).

---

## 5. Phase-by-Phase Implementation Prompts for Agents

### Phase 1: Environment & Relational DDL
```
Execute Phase 1: Set up Next.js 14 App Router with TypeScript Strict Mode and Drizzle ORM connecting to PostgreSQL on Port 6543 pooler.
Create the database schema migrations matching the DDL in the blueprint: workspaces, users, clients, projects, time_entries, invoices, invoice_line_items.
Add seed data with 1 test workspace, 2 clients, 3 projects, and initial rate tiers.
Verify with Vitest that database connections and migrations execute cleanly.
Keep all files under 200 lines.
```

### Phase 2: Live Timer Engine with Clock Drift & Idle Detection
```
Execute Phase 2: Build the live timer engine.
Implement `useActiveTimer` hook calculating duration strictly via UTC timestamp delta against `time_entries.started_at`.
Wire the `visibilitychange` window event to recalculate elapsed time on tab return.
Implement `useIdleDetector` to surface the idle dialog after 15 minutes of inactivity.
Ensure only one active timer can exist per user.
Write Vitest unit tests simulating background tab sleep and idle resolution.
```

### Phase 3: Project Tagging & Role-Based Summary Aggregations
```
Execute Phase 3: Build the projects page and daily/weekly timesheet views.
Calculate billable totals using integer cents arithmetic.
Enforce role boundaries: 'contractor' role sees only their assigned entries; 'admin' sees team-wide billable hours.
Write test coverage for daylight saving time (DST) transitions and midnight boundary crossing.
```

### Phase 4: Customizable Invoicing Engine with Atomic Sequencing
```
Execute Phase 4: Implement the customizable invoice generation engine.
Create server action `generateInvoice(workspaceId, clientId, startDate, endDate, taxPercent)`:
1. Atomically increment workspace `next_invoice_number` inside a transaction lock (`FOR UPDATE`).
2. Generate formatted sequential invoice number (e.g. `INV-2026-001`).
3. Query billable `time_entries` in range, calculate itemized subtotals, compute integer tax, and mark entries `is_invoiced = true`.
4. Render printable HTML/PDF invoice preview sheet.
Write concurrency tests in Vitest ensuring two simultaneous requests never generate identical invoice numbers.
```

### Phase 5: Two-Gate Pre/Post Regression Harness
```
Execute Phase 5: Implement the Two-Gate regression script in package.json (`npm run test:gate1` and `npm run test:gate2`).
Add Playwright end-to-end smoke test covering: Start timer -> Stop timer -> Generate Invoice -> Verify line item total.
Ensure 100% of tests pass before declaring Phase 5 complete.
```

---

## 6. Two-Gate Test Specification (Vitest Fixtures)
- `tests/unit/time-math.test.ts`: Verifies DST shifts and midnight boundary crossing.
- `tests/unit/currency.test.ts`: Verifies floating-point cents formatting (`10050` -> `"$100.50"`).
- `tests/integration/invoice-sequence.test.ts`: Fires 10 concurrent requests to `generateInvoice` and asserts 10 distinct sequential IDs (`INV-001` through `INV-010`).
- `tests/integration/timer-drift.test.ts`: Simulates 1 hour of tab suspension and asserts accurate elapsed duration.