1. What the Excel file and the interview tell us
The file and the interview change the picture from the public website. Flex-Tech's core business is field maintenance on Telesur's fiber network: crews of 2–4 technicians per car close Telesur trouble tickets (TT numbers like 10562233), install connections (WO numbers), and log poles, "verdelging" and aftercare. Technicians are paid per day worked, with 150% on Saturdays and 200% on Sundays and public holidays. The pay period runs from the 21st to the 20th.
The interview names five needs: who worked and who didn't, how many hours (half days, people leaving at 12:00, 13:00 or 15:00), production per day, production per team/car, and a name list that changes as people come and go. The Excel file covers most of this but has no hours at all, and renaming someone mid-month breaks their history.
| Excel tab | What it does today | Limitation found | FlexOps module |
|---|---|---|---|
| Start | Period (21st→20th), day multipliers, holiday list, instructions | New month = copy file and clear rows by hand | Settings, Pay periods (auto-roll on the 21st) |
| Ticket_Registratie | One row per technician per ticket; 14 helper formula columns | Same ticket typed 3–4×; typos ("Isaii"); only 3,000 rows | Tickets (one record, many technicians) |
| Correcties | Manual +/− with mandatory reason | No timestamp, anyone can edit | Corrections with audit log |
| Aanwezigheid | ✓ per person per day derived from tickets; paid days | No hours, no half days, no "reported off" | Day start (crew check-in), Attendance & hours |
| Dag_Overzicht | Pick a day: tickets, notes, who worked | One day at a time, desktop only | Dashboard, Day start |
| Technicus_ / Team_Dashboard | Production per person and per car | No efficiency per man-day, no causes | Analysis, Crews & vehicles |
| Referentie_arbeiders | Max 40 names, 7 teams | Renaming breaks old tickets; deleting loses history | Technicians (ID-based, active/inactive, aliases) |
| Beheer | Formula health checks, admin rules, unlock password in plain text | Fragile; the sheet password is readable by anyone who opens the tab | Built-in validation, per-user logins, roles |
Facts from the October file (21 Sep – 3 Oct 2026)
- 154 tickets, 153 unique TT numbers (1 repeat visit), 0 WO numbers, 0 poles/verdelging/aftercare filled in.
- 15 technicians active out of 22 on the list; 4 cars used (Team 1–4); crews of 3 (100 tickets) or 4 (45).
- 136 tickets closed by Miquel, 17 by "Isaii" (should be Isaiah), 1 empty.
- Biggest causes: damaged or cut dropwire, ONT replace/reset, patch cables. 20 tickets (13%) name a third party that damaged the cable: trucks, Digicel, EBS, a garbage truck, an excavator, Public Works.
2. System overview
3. NestJS module layout
apps/
api/ # NestJS
src/
core/ # auth, users, roles, audit-log, settings, files
people/ # technicians, employment periods, aliases, day rates
fleet/ # vehicles (= teams), plates, km, fuel
crews/ # daily crew per vehicle: check-in/out, hours, half days
tickets/ # TT/WO records, technicians on ticket, cause, photos, materials
attendance/ # derived presence + hours per person per day
payroll/ # pay periods (21→20), multipliers, holidays, close/reopen, export
corrections/ # manual adjustments with reason, author, timestamp
inventory/ # stock per vehicle/warehouse, serial numbers (ONT, FMC)
analytics/ # dashboards, causes, third-party damage, efficiency
requests/ # website + WhatsApp inbox → schedule as ticket
hr/ # files, contracts, leave & sickness, certifications, on/offboarding
office/ # tasks, purchase requests, expenses, vehicle admin, documents
approvals/ # generic approval engine (leave, purchase, expense, period close)
access/ # roles, permission matrix, policies, guards, audit interceptor
dashboards/ # one endpoint per role, returns only what that role may see
cms/
pages/ services/ projects/ media/ # public website
kb/ announcements/ # internal content
notifications/ # WhatsApp, push, email (BullMQ workers)
admin/ # Next.js office app
crew/ # Next.js PWA for crew leaders (offline queue)
site/ # Next.js public site, built from CMS content
packages/
db/ # Prisma schema + migrations + Excel importer
shared/ # DTOs, enums, pay-rule functions (used by api + apps)4. Database (Prisma schema, core tables)
enum Role { OWNER ADMIN COORDINATOR CREW_LEADER TECHNICIAN EDITOR }
enum DayType { WEEKDAY SATURDAY SUNDAY HOLIDAY }
enum CauseCode { DROP_THIRD_PARTY DROP_VANDAL_ANIMAL DROP_UNKNOWN ONT PATCH NETWORK CUSTOMER OTHER }
enum CorrectionKind { PRESENT ABSENT TICKETS CONNECTIONS POLES VERDELGING AFTERCARE PAID_DAYS }
model User { id String @id @default(cuid()) email String? @unique phone String? @unique
passwordHash String role Role personId String? @unique person Person? @relation(fields:[personId], references:[id]) }
model Person { id String @id @default(cuid()) displayName String aliases String[]
role String // technicus | coördinator
phone String? active Boolean @default(true)
employments Employment[] rates DayRate[] crewDays CrewMember[] tickets TicketTech[] }
model Employment { id String @id @default(cuid()) personId String start DateTime @db.Date end DateTime? @db.Date
person Person @relation(fields:[personId], references:[id]) }
model DayRate { id String @id @default(cuid()) personId String amountSrd Decimal @db.Decimal(12,2)
validFrom DateTime @db.Date person Person @relation(fields:[personId], references:[id]) }
model Vehicle { id String @id @default(cuid()) name String @unique // "Team 1"
plate String? active Boolean @default(true) crews CrewDay[] tickets Ticket[] }
model CrewDay { id String @id @default(cuid()) date DateTime @db.Date vehicleId String
leaderId String? members CrewMember[]
vehicle Vehicle @relation(fields:[vehicleId], references:[id])
@@unique([date, vehicleId]) }
model CrewMember { id String @id @default(cuid()) crewDayId String personId String
start String end String? // "07:30"
dayFactor Decimal @db.Decimal(3,2) @default(1) // 1, 0.5, 0 (reported off)
crewDay CrewDay @relation(fields:[crewDayId], references:[id])
person Person @relation(fields:[personId], references:[id])
@@unique([crewDayId, personId]) }
model Ticket { id String @id @default(cuid()) date DateTime @db.Date
operator String @default("Telesur")
ttNumber String? woNumber String? vehicleId String
poles Int @default(0) verdelging Int @default(0) aftercare Boolean @default(false)
cause CauseCode thirdParty String? note String?
closedById String? isRevisit Boolean @default(false) source String // app | office | import
lat Float? lng Float? createdAt DateTime @default(now())
techs TicketTech[] photos Photo[] materials MaterialUse[]
vehicle Vehicle @relation(fields:[vehicleId], references:[id])
@@index([date]) @@index([ttNumber]) }
model TicketTech { ticketId String personId String
ticket Ticket @relation(fields:[ticketId], references:[id]) person Person @relation(fields:[personId], references:[id])
@@id([ticketId, personId]) }
model Photo { id String @id @default(cuid()) ticketId String kind String /* before|after */ url String
ticket Ticket @relation(fields:[ticketId], references:[id]) }
model Item { id String @id @default(cuid()) name String serialised Boolean @default(false) owner String /* Telesur|Flex-Tech */ }
model StockLevel { itemId String locationId String qty Int min Int @default(0) @@id([itemId, locationId]) }
model MaterialUse { id String @id @default(cuid()) ticketId String itemId String qty Int serial String?
ticket Ticket @relation(fields:[ticketId], references:[id]) }
model PayPeriod { id String @id @default(cuid()) start DateTime @db.Date end DateTime @db.Date
closedAt DateTime? closedById String? @@unique([start]) }
model Holiday { date DateTime @id @db.Date name String }
model Multiplier { dayType DayType @id value Decimal @db.Decimal(4,2) } // 1, 1.5, 2, 2
model Correction { id String @id @default(cuid()) date DateTime @db.Date personId String
kind CorrectionKind qty Decimal @db.Decimal(6,2) reason String
createdById String createdAt DateTime @default(now()) }
model AuditLog { id BigInt @id @default(autoincrement()) at DateTime @default(now())
userId String entity String entityId String action String before Json? after Json? }
// ---- HR & office ----
model HrFile { personId String @id contractType String? startDate DateTime? @db.Date driverLicense String?
leaveDaysPerYear Int? emergencyContact String? /* PERSONAL */ notes String? /* PERSONAL */ }
model Leave { id String @id @default(cuid()) personId String from DateTime @db.Date to DateTime @db.Date
type String /* Verlof|Ziek|Bijzonder|Onbetaald */ reason String? /* PERSONAL when Ziek */
status String /* Aangevraagd|Goedgekeurd|Afgewezen|Gemeld */ decidedById String? decidedAt DateTime? }
model Certification { id String @id @default(cuid()) personId String kind String expiresAt DateTime @db.Date fileId String? }
model Checklist { id String @id @default(cuid()) personId String kind String /* onboarding|offboarding */ items Json }
model Task { id String @id @default(cuid()) title String assigneeId String due DateTime? @db.Date status String }
model PurchaseRequest { id String @id @default(cuid()) item String amount Decimal currency String reason String?
requestedById String status String approvedById String? approvedAt DateTime? }
model Expense { id String @id @default(cuid()) personId String date DateTime @db.Date kind String amount Decimal
currency String receiptFileId String? status String /* Ingediend|Goedgekeurd|Afgewezen|Uitbetaald */ }
model VehicleAdmin{ vehicleId String @id plate String? km Int? inspectionDue DateTime? insuranceUntil DateTime? serviceDue DateTime? }
model CompanyDoc { id String @id @default(cuid()) title String expiresAt DateTime? ownerId String fileId String? minRole Role }
model Permission { role Role resource String level String /* none|read|write */ @@id([role, resource]) }
model Setting { key String @id value Json } // e.g. purchase.approval.limitSrd = 5000
model CmsPage { id String @id @default(cuid()) slug String locale String title String
blocks Json status String /* draft|published */ publishedAt DateTime? seo Json?
@@unique([slug, locale]) }
model CmsService { id String @id @default(cuid()) title String body String visible Boolean sort Int }
model KbArticle { id String @id @default(cuid()) title String category String body String status String version Int }
model Announcement{ id String @id @default(cuid()) title String body String audience String sentAt DateTime? }
model Request { id String @id @default(cuid()) channel String name String phone String? area String?
message String status String ticketId String? createdAt DateTime @default(now()) }5. Business rules (put these in packages/shared and unit-test them)
- Pay period: starts on the 21st, ends on the 20th of the next month. A job on the 21st at 00:05 opens the next period automatically.
- Day type: holiday list first, then Saturday, then Sunday, else weekday. Multipliers 1 / 1.5 / 2 / 2 (holiday), editable by Owner only, changes logged.
- Present on a day if: on at least one ticket, OR checked in on a crew with dayFactor > 0, OR a PRESENT correction. An ABSENT correction or dayFactor 0 always wins.
- Paid days = Σ(multiplier × dayFactor) over present days + PAID_DAYS corrections. Half day = 0.5 (setting). Matches the Excel result exactly for full days (e.g. Danning: 9 + 2×1.5 + 1×2 = 14).
- Hours = end − start, minus 30 min break when longer than 5 h. Shown for efficiency and overtime; pay stays day-based unless Flex-Tech decides otherwise.
- Production: a ticket counts once per technician on it (same as Excel) and once per vehicle. Efficiency = tickets per man-day.
- Duplicate TT: allowed only as an explicit revisit (isRevisit = true), shown in analysis.
- Names: tickets reference person IDs. Renaming keeps an alias; people are never deleted, only set inactive with an end date.
- Closed period: no create/update/delete on tickets, crews or corrections dated inside it. Reopen requires Owner and is logged.
- Corrections: reason mandatory, author and timestamp automatic, never edited, only reversed by a new correction.
6. Main API endpoints
| Method | Path | Who | Purpose |
|---|---|---|---|
| POST | /crews/:date/:vehicle/check-in | Crew leader | Start of day: members + start time |
| PATCH | /crews/:id/members/:personId | Coordinator, crew leader | End time, half day, reported off |
| POST | /tickets | Crew leader, coordinator | Close a TT/WO with technicians, cause, materials |
| POST | /tickets/:id/photos | Crew leader | Upload before/after photo (compressed on device) |
| GET | /attendance?period=2026-09 | Coordinator, owner | Grid of presence, half days, hours |
| GET | /payroll/:period | Owner, admin | Paid days and amounts per person |
| POST | /payroll/:period/close | Owner | Lock the period, generate export |
| POST | /corrections | Coordinator | Add correction with reason |
| GET | /analytics/causes, /analytics/third-party, /analytics/efficiency | Owner | Dashboards |
| POST | /import/excel | Admin | Import a monthly production workbook |
| GET/PUT | /cms/pages/:slug, /cms/services, /cms/kb | Editor | Website and internal content |
| POST | /public/requests | Public (rate-limited, captcha) | Website form → Requests inbox |
| POST | /webhooks/whatsapp | Meta | Incoming WhatsApp → Requests inbox |
7. Roles and clearance
Access is checked on three levels, always on the server (the front end only hides what the server would refuse anyway):
- Resource level: a permission matrix role × module with none / read / write. Stored in the
Permissiontable so the owner can change it without a deploy (the demo's "Rollen & rechten" screen). - Field level: money (day rates, amounts, labor cost) only for Owner and Office manager; personal data (emergency contact, HR notes, reason for sickness) only for Owner and HR. Implemented as a serializer that strips fields per role.
- Row level: technicians and crew leaders only see their own days, pay, leave and expenses; a crew leader can only close tickets for his own vehicle, on the current day. Implemented as Prisma
wherefilters injected by a policy, plus Postgres row-level security as a second lock.
| Role | Level | Typical person | Dashboard shows | Approves |
|---|---|---|---|---|
| Owner | 5 | Director | Approvals waiting, expiring items, labor cost, production, causes | Purchases above limit, period close/reopen, permissions |
| Office manager | 4 | Office/admin | Approvals, tasks, deadlines, vehicles, requests, paid days | Purchases up to limit (SRD 5,000 default), expenses |
| HR | 3 | HR/admin | Headcount, absent today, leave requests, certificates, file completeness | Leave |
| Coordinator | 3 | Miquel, Isaiah | Today's crews, tickets today, warnings, absences next 7 days | Corrections (with reason) |
| Crew leader | 2 | One per car | Own days and pay + crew app | — |
| Technician | 1 | All technicians | Own days, hours, paid days, tickets, leave, expenses, certificates | — |
| Website editor | 1 | Marketing / you | Website status, drafts, requests (read) | Publishes website content |
Implementation in NestJS
@Controller('payroll')
export class PayrollController {
@Get(':period')
@Can('uitbetaling', 'read') // resource level, checked against Permission table (cached)
async get(@Param('period') p: string, @User() u: AuthUser) {
const rows = await this.payroll.forPeriod(p, scopeFor(u)); // row level: own data for TECHNICIAN
return stripFields(rows, u.role, { money: ['dayRate','amount'] }); // field level
}
}
// Every mutating request passes AuditInterceptor → AuditLog(userId, role, entity, before, after)
Approval engine (one pattern for everything)
Leave, purchase requests, expenses and period close share one state machine: draft → submitted → approved | rejected → (paid). Rules decide who may approve (role, amount threshold in SRD, never your own request). Every decision is logged and triggers a WhatsApp/app notification to the requester.
Logins
- Office roles: email + password + 2FA (authenticator app).
- Technicians and crew leaders: phone number + one-time PIN by WhatsApp/SMS, device remembered for 30 days.
- Leaving employee: offboarding checklist blocks the account the same day.
8. Automations
- 07:00 daily: WhatsApp to crew leaders "check in your crew"; 09:00 reminder to coordinator for vehicles without check-in.
- 17:30 daily: crews still open → reminder to close the day with end times.
- On ticket save: third-party cause → flagged for damage claim with photos; duplicate TT → revisit flag.
- Stock below minimum on a vehicle → task for the warehouse.
- Holiday within 7 days → automatic announcement (200%) and availability poll.
- 20th, 18:00: period summary to owner; 21st, 00:05: open next period.
- New website/WhatsApp request → auto-reply with reference number, appears in Requests.
9. Moving off Excel
- Write an importer for the workbook format (Ticket_Registratie, Correcties, Referentie_arbeiders, Start holidays). Group rows by "Hoofdrij" into one ticket with many technicians; map names and aliases ("Isaii" → Isaiah).
- Import the October file and compare per-person paid days against the Aanwezigheid tab. They must match before go-live.
- Run Excel and FlexOps side by side for one full period (21st → 20th).
- From the next 21st, only FlexOps. The Excel files stay as read-only archive.
10. Roadmap (one full-stack developer, estimates)
For the client the phases are grouped into the 4 steps shown in the demo's "Start hier" page: Step 1 Replace Excel = phases 0–2; Step 2 Insight & materials = analysis plus the inventory part of phase 4; Step 3 HR & office = phases 5–6; Step 4 Website & customers = phase 3 plus WhatsApp. Present the steps to the client and keep the phases for planning.
| Phase | Weeks | Delivers |
|---|---|---|
| 0 · Foundation | 2 | Repo, auth/roles, audit log, Prisma schema, settings, holidays, Excel importer |
| 1 · Replace the Excel file | 4 | Technicians, vehicles, tickets, corrections, attendance, payroll, dashboard, close period |
| 2 · Crew app | 3–4 | PWA: check-in, hours/half day, close ticket with photos, offline queue |
| 3 · CMS | 3 | Public site from CMS (NL/EN), services, projects, request forms, knowledge base, announcements |
| 4 · Inventory & WhatsApp | 3 | Stock per vehicle, serial numbers, WhatsApp Cloud API notifications and inbox |
| 5 · HR & office | 4 | HR files, leave/sickness, certificates, on/offboarding, tasks, purchasing, expenses, vehicle admin, documents, approval engine |
| 6 · Roles & dashboards | 2 | Permission matrix UI, field/row-level policies, one dashboard per role, audit log screen (the core RBAC goes in phase 0) |
| 7 · Analysis & claims | 2 | Cause and third-party reports, efficiency per man-day, export for Telesur |
11. Questions to settle with Flex-Tech before building
- What is "Verdelging" exactly, and how are poles, verdelging and aftercare paid by Telesur?
- Does Telesur pay Flex-Tech per ticket, per WO, or per day? (Determines revenue reporting and invoicing to Telesur.)
- Is a half day always 0.5, or paid by hours? What about overtime after 16:00?
- Who is crew leader per vehicle, and do the cars have fixed plates/drivers?
- Which materials belong to Telesur and which to Flex-Tech?
- Who fills which role: is there an office manager and a separate HR person, or is that one person? Who are the crew leaders?
- Leave policy: days per year, paid sick days, and who approves (HR or owner)?
- Purchase approval limit for the office manager (demo uses SRD 5,000).
- Does Telesur accept photos/claims for third-party damage, and in what format?
12. Feedback Flex-Tech (oktober 2026) and where it lives
| Request | Module / screen | Build notes |
|---|---|---|
| Arrival and departure times | crews · Tijden & overwerk · crew app (check-in / close day) | CrewMember.start/end; app sends device time, office can correct (audited) |
| Production chart = table, ranked by most TT/WO | analytics · Productie | One query feeds both chart and table; sort on the selected type |
| WO production | tickets · Productie | Ticket.kind enum TT | WO | NZTT | NZWO | HW |
| Users with Full / Half / None | access · Gebruikers, Rollen & rechten | Role default + UserOverride(userId, group, level); level none|read|write shown as Geen|Half|Full |
| Daily overtime | attendance · Tijden & overwerk, Uitbetaling | OT = worked − 8 h (minus 30 min break), rounded to 15 min; overtime rate to be agreed |
| Late, leave, sick | attendance + hr/leave | Late after start + grace (10 min, setting) |
| Employee database | people + hr-files · Medewerkers | Searchable list, dossier with personal fields gated by role |
| Alerts: inspection, insurance, aerial-lift certification | fleet · Wagenpark & hoogwerker, dashboards | Vehicle and Lift(certUntil, inspectionDue, insuranceUntil); daily job raises alerts 30 days ahead |
| Aerial-lift quote column | office · Hoogwerker-offertes, Tickets | Quote(nr, client, hours, rate, status); Ticket.quoteId |
| Colour code TT / WO / Nazorg / Hoogwerker | shared UI | TT blue, WO green, nazorg = open ring of the parent colour, hoogwerker amber; always with a text label (colour-blind safe, validated) |
| iOS and Android | crew app | PWA (Safari "Add to Home Screen", Chrome "Install app"), offline queue; optional Capacitor wrapper if App Store presence is wanted |
| Hours: why a half day | crews · Dagstart, Tijden | CrewMember.halfDayReason required when dayFactor < 1 |