Job Tracker

HVAC/plumbing service job tracker. Google Sheet is the database.

Setup

Google Sheet

1. Create a Google Sheet with a single tab named Jobs
2. Add header row (columns A–L): id, customer_name, customer_phone, customer_email, service_type, address, status, estimate, date_received, scheduled_date, notes, assigned_tech
3. Copy SHEET_ID from the URL: https://docs.google.com/spreadsheets/d/{SHEET_ID}/edit
4. Paste seed data from DATA-BACKUP.csv

Google API

1. Create a GCP project at console.cloud.google.com
2. Enable the Google Sheets API
3. Create an API key (Credentials → Create credentials → API key)
4. Add to .env:

SHEET_ID=your_sheet_id
GOOGLE_API_KEY=your_api_key

For write access in production, use a service account instead of an API key. Share the sheet with the service account email as Editor.

Run

bun install
bun run build    # build frontend to dist/
bun run dev      # API server on :3000, serves dist/

For frontend dev with hot reload, run bunx vite in a second terminal (proxies /api to :3000).

Architecture

Browser (React)
  → REST API (Hono, src/server.ts)
    → jobs.ts (CRUD)
      → sheets.ts (Google Sheets API)
        → Google Sheet "Jobs" tab

Column order in the sheet is positional. See src/types.ts for the mapping.

Automations

These run in Google Apps Script on the sheet, not in this repo:

Trigger	Action
Status → Completed	Email customer a "job complete" notice
Status → Invoiced	Create draft invoice in QuickBooks (webhook)
Daily 8am	Email each tech their schedule (status=Scheduled, scheduled_date=today)
New row added	Slack notification to owner
date_received + 7 days, status still Lead	Highlight row yellow (stale lead)

API

Method	Route	Description
GET	/api/jobs	List all jobs (?status= to filter)
GET	/api/jobs/:id	Get one job
POST	/api/jobs	Create job (appends row)
PATCH	/api/jobs/:id	Update job fields

No DELETE. Cancel a job by setting status to Cancelled.

Status values

Lead, Quoted, Scheduled, In Progress, Completed, Invoiced, Paid, Cancelled

Data backup

DATA-BACKUP.csv is a manual export of the Jobs tab. Re-import if the sheet needs to be restored.