How to Build a Self-Hosted Harvest & Toggl Replacement with Your AI Coding Agent

1. Why Naive Prompts Fail at Building a Harvest Replacement

If you ask an AI agent to "build a time tracking and invoicing app like Harvest," it will generate a prototype that looks great for 5 minutes and corrupts billing data in production. This blueprint enforces four non-negotiable engineering invariants:

-- 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 Architecture (Hard 200-Line Cap)

To prevent AI context degradation and monolithic 1,500-line React files, every presentation component, hook, and server action is strictly capped under 200 lines:

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. Phase-by-Phase Prompts for Claude Code, Cursor & Windsurf

Open your agent in Planning Mode, attach harvest-time-tracker.plan.md, and feed these 5 gated prompts sequentially:

I have attached the complete PromptScale technical blueprint for the Harvest & Toggl Replacement. We are entering Strict Planning Mode. Review the PostgreSQL schema, 200-line modular file limits, and Two-Gate test requirements. Do not write any code until you ask me up to 5 clarifying questions and confirm Phase 1 scope.

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.

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.

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.

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.

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.