coastal_timesheet/FEATURES-SCRIPTFORGE.md

# Coastal Timesheet v2 — New Feature Recommendations

**Author:** ScriptForge ⚡ (Expert Panel — Scripting & Testing)  
**Date:** 2025-07-18  
**Scope:** Backend API features to take this from "solid timesheet app" to "best-in-class workforce management tool"

---

## Current State Summary

The v2 backend is well-built: Express + Prisma + PostgreSQL with JWT auth, Zod validation, weekly timesheets (Mon–Sun), multi-entry-per-day, auto-save, submit/approve/reject/reopen workflow, PDF generation, email delivery, admin panel with employee/homeowner CRUD, and filtered reporting. **37/41 audit tests pass.**

What's missing is everything that happens *around* the timesheet — the operational glue that a 40+ person island contracting company actually needs to run payroll, stay accountable, and scale without drowning in admin work.

---

## Feature 1: Audit Trail / Activity Log

**Priority:** 🔴 HIGH  
**Complexity:** Medium (2–3 days)  
**Why:** Right now, there is zero record of *who changed what and when*. An admin approves a timesheet — no log. An admin reopens an approved timesheet and changes entries — no log. An employee edits hours — no log. For a company processing payroll for 40+ workers, this is a liability gap.

### Schema Addition

```prisma
model AuditLog {
  id          String   @id @default(uuid())
  userId      String   @map("user_id")
  action      String   // CREATE_ENTRY, UPDATE_ENTRY, DELETE_ENTRY, SUBMIT_TIMESHEET,
                       // APPROVE_TIMESHEET, REJECT_TIMESHEET, REOPEN_TIMESHEET,
                       // UPDATE_USER, DEACTIVATE_USER, etc.
  entityType  String   @map("entity_type")  // TimeEntry, Timesheet, User, Homeowner
  entityId    String   @map("entity_id")
  oldValues   Json?    @map("old_values")   // snapshot before change
  newValues   Json?    @map("new_values")   // snapshot after change
  ipAddress   String?  @map("ip_address")
  userAgent   String?  @map("user_agent")
  createdAt   DateTime @default(now()) @map("created_at")

  user        User     @relation(fields: [userId], references: [id])

  @@index([entityType, entityId])
  @@index([userId])
  @@index([createdAt])
  @@map("audit_logs")
}
```

### API Endpoints

```
GET /api/admin/audit-logs?entityType=&entityId=&userId=&from=&to=&action=&page=&limit=
GET /api/admin/audit-logs/:entityId/history   (full change history for one record)
```

### Implementation Notes

- Create a `logAudit(req, action, entityType, entityId, oldValues, newValues)` utility
- Call it as middleware or explicitly after every mutation in entries, timesheets, admin routes
- Store `req.ip` and `req.headers['user-agent']` for forensics
- Old/new values as JSON snapshots enable "diff view" on the frontend later
- Add retention policy: auto-delete logs older than 2 years via a cron job or Prisma middleware

---

## Feature 2: Overtime Calculation Engine

**Priority:** 🔴 HIGH  
**Complexity:** Medium (2–3 days)  
**Why:** The app tracks `hoursWorked` per entry but has zero concept of overtime. A contracting company with 40+ employees absolutely needs to know who's hitting 40h/week, what the OT breakdown is, and what the payroll cost looks like before cutting checks. This is also a legal compliance concern.

### Schema Addition

```prisma
model OvertimeRule {
  id               String   @id @default(uuid())
  name             String   @unique  // "Standard", "Holiday", etc.
  weeklyThreshold  Decimal  @default(40) @map("weekly_threshold") @db.Decimal(5,2)
  dailyThreshold   Decimal? @map("daily_threshold") @db.Decimal(5,2)  // optional: some states require daily OT
  overtimeRate     Decimal  @default(1.5) @map("overtime_rate") @db.Decimal(3,2)
  doubleTimeRate   Decimal? @map("double_time_rate") @db.Decimal(3,2) // e.g., over 12h/day
  isActive         Boolean  @default(true) @map("is_active")
  createdAt        DateTime @default(now()) @map("created_at")
  updatedAt        DateTime @updatedAt @map("updated_at")

  @@map("overtime_rules")
}
```

### API Endpoints

```
GET    /api/admin/overtime-rules
POST   /api/admin/overtime-rules
PUT    /api/admin/overtime-rules/:id
GET    /api/admin/reports/overtime?from=&to=&userId=    → computed OT breakdown
GET    /api/timesheets/overtime?week=YYYY-MM-DD         → employee sees their own OT for the week
```

### Implementation Notes

- The OT calculation is a **read-time computation**, not stored — keeps source of truth clean
- `GET /reports/overtime` returns per-employee: `{ regularHours, overtimeHours, doubleTimeHours, totalHours }`
- Compute from `TimeEntry` rows grouped by userId + week, applying the active `OvertimeRule`
- Support both weekly threshold (federal FLSA: >40h) and daily threshold (California: >8h/day)
- The report endpoint should be able to span multiple weeks for payroll periods
- Add `hourlyRate` field to `User` model (optional) so the report can also compute `estimatedPay`

---

## Feature 3: Bulk Timesheet Operations

**Priority:** 🔴 HIGH  
**Complexity:** Low–Medium (1–2 days)  
**Why:** An admin managing 40+ employees who submit timesheets weekly is looking at potentially 40 approval clicks every Monday morning. The current API handles one timesheet at a time — approve, reject, reopen are all single-ID operations. This doesn't scale.

### API Endpoints

```
POST /api/admin/timesheets/bulk-approve
  Body: { timesheetIds: string[], notes?: string }
  Response: { approved: number, failed: { id, reason }[] }

POST /api/admin/timesheets/bulk-reject
  Body: { timesheetIds: string[], notes: string }
  Response: { rejected: number, failed: { id, reason }[] }

POST /api/admin/timesheets/bulk-reopen
  Body: { timesheetIds: string[] }
  Response: { reopened: number, failed: { id, reason }[] }
```

### Implementation Notes

- Use `prisma.$transaction()` for atomicity — either all succeed or return partial results with failure reasons
- Validate each timesheet's current status before acting (can't approve a draft, can't reject an approved, etc.)
- Return a summary with individual failure reasons so the admin knows exactly what happened
- Cap batch size at 100 to prevent abuse
- Wire into audit log (Feature 1): one audit entry per timesheet, not one for the batch

---

## Feature 4: Notification System (Webhook + Email Triggers)

**Priority:** 🟡 MEDIUM  
**Complexity:** Medium (3–4 days)  
**Why:** The app already has email capability (used for emailing PDFs), but there are no *automatic* notifications. When an employee submits a timesheet, the admin doesn't know until they manually check. When a timesheet is rejected, the employee doesn't know until they manually check. For a crew of 40+ people on an island, this creates bottlenecks.

### Schema Addition

```prisma
model NotificationPreference {
  id          String   @id @default(uuid())
  userId      String   @map("user_id")
  event       String   // TIMESHEET_SUBMITTED, TIMESHEET_APPROVED, TIMESHEET_REJECTED,
                       // REMINDER_SUBMIT, REMINDER_APPROVE
  channel     String   @default("email")  // email, webhook (future: push, sms)
  enabled     Boolean  @default(true)
  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")

  user        User     @relation(fields: [userId], references: [id])

  @@unique([userId, event, channel])
  @@map("notification_preferences")
}
```

### Triggered Events

| Event | Who Gets Notified | Trigger Point |
|-------|-------------------|---------------|
| `TIMESHEET_SUBMITTED` | All admins | `POST /api/timesheets/submit` |
| `TIMESHEET_APPROVED` | The employee | `PUT /api/admin/timesheets/:id/approve` |
| `TIMESHEET_REJECTED` | The employee (with rejection notes) | `PUT /api/admin/timesheets/:id/reject` |
| `REMINDER_SUBMIT` | Employees with no submission for current week | Cron: Sunday 6 PM |
| `REMINDER_APPROVE` | Admins with pending submissions | Cron: Monday 9 AM |

### API Endpoints

```
GET  /api/notifications/preferences           → user's notification settings
PUT  /api/notifications/preferences           → update preferences
POST /api/admin/notifications/send-reminders  → manually trigger submission reminders
```

### Implementation Notes

- Build a `NotificationService` class that the route handlers call: `notifyService.emit('TIMESHEET_SUBMITTED', { timesheet, user })`
- The service checks preferences, then dispatches via email (already have SMTP util) or webhook
- Cron-based reminders use a lightweight scheduler (e.g., `node-cron`) or a separate script run by system cron
- Keep notifications async (fire-and-forget with error logging) so they never slow down the API response
- Future: add webhook URL support per-user so third-party systems (Slack, accounting) can subscribe

---

## Feature 5: CSV/Excel Export for Payroll Integration

**Priority:** 🟡 MEDIUM  
**Complexity:** Low (1 day)  
**Why:** The app has PDF export, which is great for records and signatures — but nobody imports a PDF into QuickBooks, Gusto, or ADP. Payroll processing for 40+ employees requires **machine-readable export** in CSV or XLSX format, ideally matching common payroll import templates.

### API Endpoints

```
GET /api/admin/reports/export?format=csv&from=&to=&userId=&homeownerId=
GET /api/admin/reports/export?format=xlsx&from=&to=&groupBy=user|homeowner|week
GET /api/timesheets/:id/export?format=csv       → single timesheet export
```

### Export Formats

**Payroll CSV** (one row per employee per week):
```csv
Employee Name,Email,Week Starting,Regular Hours,Overtime Hours,Total Hours,Status,Approved By,Approved Date
John Smith,john@coastal.com,2026-02-09,38.50,0.00,38.50,approved,Admin,2026-02-16
```

**Detailed CSV** (one row per time entry):
```csv
Date,Employee,Homeowner,Hours,Description,Timesheet Status
2026-02-09,John Smith,Johnson Residence,4.50,Deck repair,approved
```

**Homeowner Billing CSV** (for invoicing homeowners):
```csv
Homeowner,Address,Total Hours,Employee,Date,Description,Hours
Johnson Residence,123 Beach Rd,12.50,John Smith,2026-02-09,Deck repair,4.50
```

### Implementation Notes

- Use `csv-stringify` (lightweight, no binary deps) for CSV
- Use `exceljs` for XLSX if needed (supports formatting, multiple sheets)
- The `groupBy` parameter controls whether rows are aggregated by user, homeowner, or week
- Stream large exports using `res.write()` + `csv-stringify`'s streaming API to avoid loading everything into memory
- Add `Content-Disposition: attachment; filename="coastal-payroll-2026-02-09.csv"` headers
- This pairs with the overtime engine (Feature 2): the payroll CSV should include regular/OT hour splits

---

## Feature 6: Geolocation Check-In/Check-Out

**Priority:** 🟡 MEDIUM  
**Complexity:** Medium–High (3–5 days)  
**Why:** Workers travel between homeowner properties on an island. Manual hour entry is trust-based. Geolocation check-in/out provides accountability — not as a punitive surveillance tool, but as a convenience (auto-populate which homeowner they're at) and a verification layer (admin can see that claimed hours at a property match GPS presence).

### Schema Additions

```prisma
model HomeownerLocation {
  id            String   @id @default(uuid())
  homeownerId   String   @unique @map("homeowner_id")
  latitude      Decimal  @db.Decimal(10, 7)
  longitude     Decimal  @db.Decimal(10, 7)
  radiusMeters  Int      @default(200) @map("radius_meters")  // geofence radius
  createdAt     DateTime @default(now()) @map("created_at")
  updatedAt     DateTime @updatedAt @map("updated_at")

  homeowner     Homeowner @relation(fields: [homeownerId], references: [id])

  @@map("homeowner_locations")
}

model CheckIn {
  id            String    @id @default(uuid())
  userId        String    @map("user_id")
  homeownerId   String?   @map("homeowner_id")  // auto-matched or manual
  latitude      Decimal   @db.Decimal(10, 7)
  longitude     Decimal   @db.Decimal(10, 7)
  accuracy      Decimal?  @db.Decimal(8, 2)     // GPS accuracy in meters
  type          String    // CHECK_IN or CHECK_OUT
  autoMatched   Boolean   @default(false) @map("auto_matched")  // was homeowner auto-detected?
  timeEntryId   String?   @map("time_entry_id")  // linked after checkout
  createdAt     DateTime  @default(now()) @map("created_at")

  user          User      @relation(fields: [userId], references: [id])

  @@index([userId, createdAt])
  @@map("check_ins")
}
```

### API Endpoints

```
POST /api/checkin
  Body: { latitude, longitude, accuracy?, homeownerId? }
  Response: { checkInId, autoMatchedHomeowner?, timestamp }

POST /api/checkout
  Body: { latitude, longitude, accuracy? }
  Response: { checkOutId, duration, suggestedEntry: { homeownerId, hoursWorked } }

GET  /api/checkins?date=YYYY-MM-DD               → employee's check-in history
GET  /api/admin/checkins?userId=&date=&homeownerId=  → admin view of all check-ins

PUT  /api/admin/homeowners/:id/location
  Body: { latitude, longitude, radiusMeters? }    → set homeowner geofence
```

### Implementation Notes

- On check-in, use Haversine formula to find the nearest `HomeownerLocation` within `radiusMeters` — auto-populate `homeownerId`
- On check-out, calculate duration since last check-in and suggest a `TimeEntry` with pre-filled hours
- The frontend (mobile-first) calls `navigator.geolocation.getCurrentPosition()` and sends coords to the API
- **Privacy consideration:** Store location only at check-in/out moments, not continuous tracking
- Geolocation is *supplementary*, not mandatory — employees can still manually create entries without checking in
- Admin reports can flag discrepancies: "claimed 8h at Johnson property but only checked in for 3h"
- Island properties are spread out enough that a 200m default radius should avoid overlap

---

## Feature 7: Rate Limiting & API Throttling Improvements

**Priority:** 🟢 LOW  
**Complexity:** Low (0.5–1 day)  
**Why:** The current rate limiter only covers auth endpoints (50 req / 15 min). All other API routes are unlimited. With 40+ employees hitting auto-save on the frontend, plus admin bulk operations, plus PDF generation (CPU-intensive), the API needs tiered rate limiting to stay healthy under load.

### Implementation

```js
// Tiered rate limiters
const authLimiter = rateLimit({ windowMs: 15 * 60 * 1000, max: 10 });  // tighten from 50 → 10
const apiLimiter  = rateLimit({ windowMs: 1 * 60 * 1000, max: 120 });  // general: 120/min per IP
const pdfLimiter  = rateLimit({ windowMs: 1 * 60 * 1000, max: 10 });   // PDF: 10/min (CPU-heavy)
const bulkLimiter = rateLimit({ windowMs: 1 * 60 * 1000, max: 5 });    // bulk ops: 5/min

// Apply
app.use('/api/auth', authLimiter);
app.use('/api/', apiLimiter);
app.use('/api/timesheets/:id/pdf', pdfLimiter);
app.use('/api/admin/reports/pdf', pdfLimiter);
app.use('/api/admin/timesheets/bulk-*', bulkLimiter);
```

### Additional Improvements

- Add `Retry-After` header on 429 responses (already supported by `express-rate-limit`)
- Use `req.user.id` as the key generator for authenticated routes (instead of IP) — prevents one user from burning the limit for everyone behind a shared NAT
- Add a `/api/health` rate limit (currently unlimited — potential for monitoring abuse)
- Consider Redis-backed rate limiting (`rate-limit-redis`) if the app scales to multiple backend instances

---

## Feature 8: Timesheet Locking with Payroll Period Cutoffs

**Priority:** 🟢 LOW  
**Complexity:** Low–Medium (1–2 days)  
**Why:** The current locking mechanism is per-timesheet (submitted/approved = locked). But there's no *global* time-based cutoff. An employee could go back and edit timesheets from 3 months ago as long as they were never submitted. A payroll admin needs hard cutoff dates: "Everything before Feb 1st is frozen — payroll has been run."

### Schema Addition

```prisma
model PayrollPeriod {
  id          String    @id @default(uuid())
  name        String    // "Jan 2026 Payroll", "Pay Period 2026-W06"
  startDate   DateTime  @map("start_date") @db.Date
  endDate     DateTime  @map("end_date") @db.Date
  lockedAt    DateTime? @map("locked_at")     // null = still open
  lockedBy    String?   @map("locked_by")
  processedAt DateTime? @map("processed_at")  // when payroll was actually run
  notes       String?
  createdAt   DateTime  @default(now()) @map("created_at")
  updatedAt   DateTime  @updatedAt @map("updated_at")

  @@map("payroll_periods")
}
```

### API Endpoints

```
GET    /api/admin/payroll-periods                → list all periods
POST   /api/admin/payroll-periods                → create a new period
PUT    /api/admin/payroll-periods/:id/lock       → lock period (freezes all entries in range)
PUT    /api/admin/payroll-periods/:id/unlock     → unlock for corrections
DELETE /api/admin/payroll-periods/:id            → only if not yet locked
```

### Implementation Notes

- Modify the entry mutation guards in `entries.js`: before checking timesheet status, also check if the entry's date falls within a locked payroll period
- Guard logic: `if (payrollPeriod && payrollPeriod.lockedAt) → 403 "Payroll period is locked"`
- Locking a period should auto-approve all submitted timesheets within that date range (or warn about un-submitted ones)
- The lock endpoint should return a summary: `{ locked: true, timesheetsApproved: 38, timesheetsStillDraft: 2, warning: "2 employees haven't submitted" }`
- Pairs with CSV export (Feature 5): `GET /api/admin/payroll-periods/:id/export?format=csv`

---

## Priority Summary

| # | Feature | Priority | Complexity | Effort |
|---|---------|----------|------------|--------|
| 1 | Audit Trail / Activity Log | 🔴 HIGH | Medium | 2–3 days |
| 2 | Overtime Calculation Engine | 🔴 HIGH | Medium | 2–3 days |
| 3 | Bulk Timesheet Operations | 🔴 HIGH | Low–Med | 1–2 days |
| 4 | Notification System | 🟡 MEDIUM | Medium | 3–4 days |
| 5 | CSV/Excel Payroll Export | 🟡 MEDIUM | Low | 1 day |
| 6 | Geolocation Check-In/Out | 🟡 MEDIUM | Med–High | 3–5 days |
| 7 | Rate Limiting Improvements | 🟢 LOW | Low | 0.5–1 day |
| 8 | Payroll Period Cutoffs | 🟢 LOW | Low–Med | 1–2 days |

**Total estimated effort: 14–21 days** for all 8 features.

**Recommended build order:** 3 → 1 → 5 → 2 → 4 → 8 → 7 → 6

Rationale: Start with bulk ops (instant admin productivity win, lowest effort of the HIGHs), then audit trail (compliance foundation everything else builds on), then CSV export (unblocks payroll integration immediately), then overtime (builds on the export), then notifications (quality-of-life), then payroll periods (builds on audit + overtime), then rate limiting (quick hardening), and finally geolocation (biggest lift, most optional).

---

*ScriptForge ⚡ — "If it doesn't have an audit log, it didn't happen."*