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

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

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

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):

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):

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):

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

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

// 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

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."