285 lines
12 KiB
Markdown
285 lines
12 KiB
Markdown
# Harding's Clocks — Repair Tracker Design Doc
|
||
|
||
## 1. Purpose
|
||
|
||
A dead-simple web app for a one-person clock repair shop to:
|
||
|
||
- Track customers and the clocks they bring in
|
||
- Open service orders, log work performed, and list parts
|
||
- Generate a printable invoice matching the existing paper form
|
||
|
||
Design principle: **KISS**. One user (Dad), one machine, one job at a time. No accounts, no multi-tenancy, no plugin system, no build ceremony beyond what Vite needs.
|
||
|
||
## 2. Non-Goals
|
||
|
||
- No authentication in the app (handled by Caddy — see §9)
|
||
- No customer-facing portal
|
||
- No payment processing (just record what was paid)
|
||
- No job templates — a fixed `job_type` field instead
|
||
- No inventory management — parts are free-text lines on an order
|
||
- No email/SMS, no reporting dashboards (can add later)
|
||
|
||
## 3. Tech Stack
|
||
|
||
| Layer | Choice | Why |
|
||
|---------------|---------------------------|--------------------------------------------------|
|
||
| Database | SQLite (`better-sqlite3`) | Single file, zero-config, synchronous API |
|
||
| Runtime | Node.js (LTS) | Familiar, boring, reliable |
|
||
| Language | TypeScript | Type safety on the data model without heaviness |
|
||
| HTTP server | Express (or Fastify) | Minimal routing; serve HTML + handle form posts |
|
||
| Frontend | HTMX + server-rendered HTML | No SPA. Server returns HTML fragments. |
|
||
| Templating | Nunjucks (or EJS) | Plain HTML templates, easy to eyeball |
|
||
| Build/dev | Vite | Fast dev server + asset bundling for the bit of JS/CSS |
|
||
| Reverse proxy | Caddy | TLS + basic-auth in front |
|
||
|
||
**Why HTMX, not React:** the whole app is forms and lists. HTMX lets the server
|
||
return HTML fragments on `hx-post`/`hx-get`, so there's no client state to manage
|
||
and no API/JSON layer to maintain. TypeScript stays on the server where the logic is.
|
||
|
||
## 4. Data Model
|
||
|
||
Five tables plus one view. Money stored as **INTEGER cents** to avoid float rounding.
|
||
Dates stored as ISO-8601 TEXT (`YYYY-MM-DD` or full datetime).
|
||
|
||
**Discounts:** entered as a line item with a negative `unit_price` (e.g. a `labor` line "10% discount" at `-$5.00`). No special column needed.
|
||
|
||
**Soft deletes:** orders are never hard-deleted. Setting `deleted_at` hides them from all normal queries; they can be recovered if needed.
|
||
|
||
```sql
|
||
CREATE TABLE customers (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
name TEXT NOT NULL,
|
||
address_line1 TEXT,
|
||
address_line2 TEXT,
|
||
city TEXT,
|
||
state TEXT,
|
||
zip TEXT,
|
||
phone TEXT,
|
||
alt_phone TEXT,
|
||
email TEXT,
|
||
created_at TEXT DEFAULT (datetime('now'))
|
||
);
|
||
|
||
CREATE TABLE clocks (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
customer_id INTEGER NOT NULL REFERENCES customers(id),
|
||
manufacturer TEXT,
|
||
model TEXT,
|
||
serial_number TEXT,
|
||
alternate_id TEXT,
|
||
reference TEXT,
|
||
description TEXT,
|
||
created_at TEXT DEFAULT (datetime('now'))
|
||
);
|
||
|
||
CREATE TABLE service_orders (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
order_number INTEGER UNIQUE,
|
||
customer_id INTEGER NOT NULL REFERENCES customers(id),
|
||
clock_id INTEGER REFERENCES clocks(id),
|
||
job_type TEXT CHECK (job_type IN (
|
||
'31 DAY','CLEANING','ESTIMATE','FIT UP/REPLACE',
|
||
'HOUSE CALL','MOVE CLOCK','OVERHAUL','SERVICE',
|
||
'WALK IN SERVICE')),
|
||
service_rep TEXT,
|
||
technician TEXT,
|
||
status TEXT DEFAULT 'OPEN' CHECK (status IN ('OPEN','IN PROGRESS','DONE','PAID')),
|
||
date_in TEXT DEFAULT (date('now')),
|
||
date_out TEXT,
|
||
due_date TEXT,
|
||
cust_po TEXT,
|
||
terms TEXT,
|
||
-- money summary (cents)
|
||
tax INTEGER DEFAULT 0,
|
||
shipping INTEGER DEFAULT 0,
|
||
pickup_delivery INTEGER DEFAULT 0,
|
||
service_call INTEGER DEFAULT 0,
|
||
disposal INTEGER DEFAULT 0,
|
||
amount_paid INTEGER DEFAULT 0,
|
||
created_at TEXT DEFAULT (datetime('now')),
|
||
deleted_at TEXT -- soft delete; NULL = active
|
||
);
|
||
|
||
CREATE TABLE line_items (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
service_order_id INTEGER NOT NULL REFERENCES service_orders(id) ON DELETE CASCADE,
|
||
kind TEXT NOT NULL DEFAULT 'labor', -- 'labor' | 'part' | 'sublet'
|
||
stock_code TEXT,
|
||
description TEXT,
|
||
quantity REAL DEFAULT 1,
|
||
unit_price INTEGER DEFAULT 0, -- cents
|
||
sort_order INTEGER DEFAULT 0
|
||
);
|
||
|
||
CREATE TABLE work_log (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
service_order_id INTEGER NOT NULL REFERENCES service_orders(id) ON DELETE CASCADE,
|
||
note TEXT NOT NULL,
|
||
logged_at TEXT DEFAULT (datetime('now'))
|
||
);
|
||
|
||
-- ROUND() keeps cents integer despite REAL quantity (e.g. 1.5 hrs × 175_00 ¢).
|
||
CREATE VIEW order_totals AS
|
||
SELECT
|
||
so.id AS service_order_id,
|
||
COALESCE(SUM(CASE WHEN li.kind='part' THEN ROUND(li.quantity*li.unit_price) END),0) AS total_parts,
|
||
COALESCE(SUM(CASE WHEN li.kind='labor' THEN ROUND(li.quantity*li.unit_price) END),0) AS total_labor,
|
||
COALESCE(SUM(CASE WHEN li.kind='sublet' THEN ROUND(li.quantity*li.unit_price) END),0) AS total_sublet,
|
||
COALESCE(SUM(ROUND(li.quantity*li.unit_price)),0)
|
||
+ so.tax + so.shipping + so.pickup_delivery + so.service_call + so.disposal AS grand_total,
|
||
COALESCE(SUM(ROUND(li.quantity*li.unit_price)),0)
|
||
+ so.tax + so.shipping + so.pickup_delivery + so.service_call + so.disposal
|
||
- so.amount_paid AS balance_due
|
||
FROM service_orders so
|
||
LEFT JOIN line_items li ON li.service_order_id = so.id
|
||
WHERE so.deleted_at IS NULL
|
||
GROUP BY so.id;
|
||
|
||
CREATE INDEX idx_line_items_order ON line_items(service_order_id);
|
||
CREATE INDEX idx_work_log_order ON work_log(service_order_id);
|
||
CREATE INDEX idx_clocks_customer ON clocks(customer_id);
|
||
CREATE INDEX idx_orders_customer ON service_orders(customer_id);
|
||
```
|
||
|
||
**Order numbers:** `order_number` starts at 8247 (next after the sample) and
|
||
increments. Simplest approach: `COALESCE(MAX(order_number), 8246) + 1` at insert
|
||
time inside a transaction — the `COALESCE` handles an empty table correctly.
|
||
`better-sqlite3` is synchronous so there's no race on a single-user app. Gaps from
|
||
soft-deleted orders are fine; numbers are never recycled.
|
||
|
||
## 5. Application Structure
|
||
|
||
```
|
||
clock-tracker/
|
||
├─ src/
|
||
│ ├─ server.ts # Express app, route wiring
|
||
│ ├─ db.ts # better-sqlite3 connection + prepared statements; runs PRAGMA foreign_keys=ON
|
||
│ ├─ schema.sql # the DDL above (run on first boot if db missing)
|
||
│ ├─ money.ts # cents <-> "$1.75" helpers
|
||
│ ├─ routes/
|
||
│ │ ├─ customers.ts
|
||
│ │ ├─ clocks.ts
|
||
│ │ ├─ orders.ts # create/edit order, add line items, work log
|
||
│ │ └─ invoice.ts # printable invoice view
|
||
│ └─ views/ # Nunjucks templates + HTMX fragments
|
||
│ ├─ layout.njk
|
||
│ ├─ orders/
|
||
│ │ ├─ list.njk
|
||
│ │ ├─ detail.njk
|
||
│ │ └─ _line_item_row.njk # fragment returned to HTMX
|
||
│ └─ invoice.njk
|
||
├─ public/ # CSS, tiny JS, htmx.min.js
|
||
├─ data/app.db # the SQLite file (gitignored)
|
||
├─ vite.config.ts
|
||
├─ tsconfig.json
|
||
└─ package.json
|
||
```
|
||
|
||
## 6. Routes
|
||
|
||
| Method | Path | Returns | Purpose |
|
||
|--------|-----------------------------------|------------------|----------------------------------|
|
||
| GET | `/` | full page | Order list (open orders first) |
|
||
| GET | `/orders/new` | full page | New-order form |
|
||
| POST | `/orders` | redirect | Create order + customer/clock |
|
||
| GET | `/orders/:id` | full page | Order detail (work + parts) |
|
||
| POST | `/orders/:id/line-items` | HTML fragment | Add a labor/part line (HTMX) |
|
||
| DELETE | `/orders/:id/line-items/:lineId` | HTML fragment | Remove a line + OOB totals update (HTMX) |
|
||
| POST | `/orders/:id/work-log` | HTML fragment | Add a work-log note (HTMX) |
|
||
| POST | `/orders/:id` | HTML fragment | Update status/dates/totals |
|
||
| DELETE | `/orders/:id` | redirect | Soft-delete order (sets deleted_at) |
|
||
| GET | `/orders/:id/invoice` | full page | Printable invoice |
|
||
| GET | `/customers` | full page | Customer list/search |
|
||
|
||
HTMX pattern: adding a part line does `hx-post="/orders/42/line-items"` with
|
||
`hx-target="#lines" hx-swap="beforeend"`; the server returns just the `<tr>` for
|
||
the new line plus an out-of-band swap updating the totals box.
|
||
|
||
## 7. The Invoice View
|
||
|
||
`GET /orders/:id/invoice` renders `invoice.njk` — a print-oriented HTML page
|
||
mirroring the paper layout: shop header block, Ship-To, manufacturer/clock block,
|
||
the line-item table (work performed + parts), and the totals box from
|
||
`order_totals`. A `@media print` stylesheet hides nav and fits one page.
|
||
"Print" is just the browser's print dialog → save as PDF or paper. No PDF library.
|
||
|
||
## 8. Local Dev & Build
|
||
|
||
- `npm run dev` — Vite dev server for assets + `tsx watch src/server.ts` for the API. (Vite proxies to the Node server, or run them side by side; keep it simple with `concurrently`.)
|
||
- `npm run build` — `vite build` for `public/` assets + `tsc` for server.
|
||
- `npm start` — `node dist/server.js`, serving built assets and listening on `127.0.0.1:3000`.
|
||
- DB bootstraps itself: on start, if `data/app.db` doesn't exist, run `schema.sql`.
|
||
|
||
## 9. Deployment & "Auth"
|
||
|
||
The app binds to localhost only. Caddy sits in front, terminates TLS, and does
|
||
HTTP Basic Auth so only Dad gets in. Example `Caddyfile`:
|
||
|
||
```
|
||
clocks.example.com {
|
||
basic_auth {
|
||
dad $2a$14$...bcrypt-hash...
|
||
}
|
||
reverse_proxy 127.0.0.1:3000
|
||
}
|
||
```
|
||
|
||
Generate the hash with `caddy hash-password`. That single credential is the whole
|
||
security model — acceptable because it's one private user on a small shop tool.
|
||
The app itself never sees or stores credentials.
|
||
|
||
## 10. Backups
|
||
|
||
The database is one file (`data/app.db`). A nightly `cron` job copies it somewhere
|
||
safe using `better-sqlite3`'s built-in backup API (avoids needing the `sqlite3` CLI):
|
||
|
||
```ts
|
||
// scripts/backup.ts
|
||
import Database from 'better-sqlite3';
|
||
import { execSync } from 'child_process';
|
||
const date = execSync('date +%F').toString().trim();
|
||
const db = new Database('data/app.db');
|
||
await db.backup(`/backups/app-${date}.db`);
|
||
db.close();
|
||
```
|
||
|
||
Run with `tsx scripts/backup.ts` from cron. Keep ~30 days. That's the entire
|
||
disaster-recovery plan, and it's enough.
|
||
|
||
## 12. CSS Design Tokens
|
||
|
||
All colours live in `:root` as custom properties. No other values should be hardcoded.
|
||
|
||
```css
|
||
:root {
|
||
/* Surfaces */
|
||
--color-bg: #F2E8D5; /* aged parchment — page background */
|
||
--color-surface: #EAD9BE; /* light oak — cards, panels */
|
||
--color-border: #C4A882; /* tan grain — dividers, input borders */
|
||
|
||
/* Wood tones */
|
||
--color-wood-dark: #4A2810; /* dark mahogany — nav, headings */
|
||
--color-wood-mid: #8B5E3C; /* medium walnut — buttons, interactive */
|
||
--color-wood-light: #B5842A; /* brass — hover states, focus rings */
|
||
|
||
/* Text */
|
||
--color-text: #2C1A0E; /* near-black brown — body copy */
|
||
--color-text-muted: #7A5C42; /* faded oak — labels, secondary info */
|
||
--color-text-on-dark: #F2E8D5; /* parchment — text on dark backgrounds */
|
||
|
||
/* Status */
|
||
--color-status-open: #8B5E3C; /* walnut */
|
||
--color-status-inprogress:#B5842A; /* brass */
|
||
--color-status-done: #5C7A42; /* muted forest green */
|
||
--color-status-paid: #3D5C2A; /* deeper green */
|
||
}
|
||
```
|
||
|
||
Typography: system serif stack for headings (`Georgia, 'Times New Roman', serif`), system sans for body (`system-ui, sans-serif`). No web font downloads — keeps it fast and offline-friendly.
|
||
|
||
## 11. Future (explicitly deferred)
|
||
|
||
Only if actually needed: photo attachments per clock (the software's "Images" tab),
|
||
customer search-as-you-type, a simple monthly revenue total, quoted-values/estimate
|
||
tracking. None of this is built now.
|