The tables behind Field Service Management

The field-tech master. Every dispatched-and-on-site human is one row. Joined to the HRIS Employee record by EmployeeRef for payroll and benefits, but lifecycle here is operational: Active means schedulable, on leave drops out of the schedule-optimization pool, Inactive is gone.

Per-Tech skills and certifications with expiry. The schedule-optimization workflow filters the eligible-Tech pool by matching ServiceCategories.RequiredSkills against active (not expired) entries here.

Multi-tech crew for jobs that need more than one body — install days, rooftop unit swaps, big plumbing repipes. A Crew has a Lead (senior Tech) who owns the on-site call.

The roster of techs in a crew. A Tech can be in more than one crew (they can run their own calls solo most days and roll with the install crew on Fridays).

One on-site visit with a committed assignment, customer window and completion receipt. Created → Scheduled → Dispatched → en route → on site → Completed is the successful path. Cancelled and Rescheduled are controlled exception branches before completion, not transitions after a completed signed visit.

Daily / weekly schedule view header. One per Day per Technician (or per Crew). The Dispatcher’s working canvas.

Per-Technician time-slot configuration — when each tech is schedulable, in what windows, and how many calls per window. Drives schedule-optimization eligibility and route capacity.

Accepted technician unavailability for leave, training and nonworking time. Operations Manager records or cancels these operational intervals; an HR connector may import them only with a scoped service identity and source receipt. This is a scheduling mirror, not HR leave approval.

Immutable available-time basis for each technician and local calendar day, including zero-call and zero-capacity days. The server freezes one row after day close from the accepted scheduling basis; later slot, leave or employment changes cannot rewrite the historical denominator.

Per-Tech daily route header. Generated by route-planning nightly; revised throughout the day on cancellations and reschedules.

The ordered stops on a day’s route. One per service call in the day’s plan, plus optional warehouse-stop entries for parts pickup.

One on-site visit with a committed assignment, customer window and completion receipt. Created → Scheduled → Dispatched → en route → on site → Completed is the successful path. Cancelled and Rescheduled are controlled exception branches before completion, not transitions after a completed signed visit.

Labor entries — one per Tech per session on the call. The check-in / check-out workflow opens and closes a row automatically; manual entries are allowed for crew helpers.

Free-form notes — Tech observations, dispatcher dispatch instructions, manager call-out. Visibility flag controls customer-receipt visibility.

Before / during / after photos — the dispute-killer. Required by service category configuration on most repair categories.

Pending mobile commands captured while disconnected. Notes, labor, photos and signature captures wait for authenticated server validation and revision checks. Payments remain unconfirmed requests, and server completion is not claimed until all required checks succeed.

The parts-and-materials catalog carries current approved warehouse cost and customer price. Changes retain an audit record; historical service call parts and billing revisions preserve the exact accepted price snapshot.

Per-Technician parts inventory on the truck. The most-frequently written table in the app — every service call parts row triggers a decrement here.

Central warehouse parts inventory. The source for replenishment runs out to truck. Read-only mirror if a separate scm-inventory app owns the master; local table here for FSM-only deployments.

Posted parts consumed on an assigned call, with immutable cost/price snapshots and an atomic stock movements posting. A draft mobile event is not a posted consumption record. Linked reversals compensate stock and trigger billing reconciliation.

Immutable inventory postings for receipts, truck consumption, replenishment and compensating reversals. Balance projections and a posted movement commit atomically in local mode. Reversed originals remain in the signed ledger sum; the linked compensating row supplies the opposite effect.

Inventory requests prepared by the Inventory Clerk and independently approved by Operations Manager. Warehouse-to-truck moves progress through Approved, Picked and Loaded; procurement requests remain Sent until acknowledged. Posted stock is corrected by linked compensating movements.

Pre-work quote. Required when a Tech finds work beyond the original call’s scope. Customer signs Estimate before any extra labor / parts hits the call.

Immutable customer signature receipts bound to an exact displayed document, scope revision and authorized signer. Estimate approval and completion acknowledgment have separate purposes and cannot substitute for each other.

One on-site visit with a committed assignment, customer window and completion receipt. Created → Scheduled → Dispatched → en route → on site → Completed is the successful path. Cancelled and Rescheduled are controlled exception branches before completion, not transitions after a completed signed visit.

Posted parts consumed on an assigned call, with immutable cost/price snapshots and an atomic stock movements posting. A draft mobile event is not a posted consumption record. Linked reversals compensate stock and trigger billing reconciliation.

Labor entries — one per Tech per session on the call. The check-in / check-out workflow opens and closes a row automatically; manual entries are allowed for crew helpers.

The service taxonomy the dispatcher and the customer pick from. Each category snaps a default Duration (used for slot sizing) and required skills (used for tech-eligibility filtering).

The invoice generated from a completed service call. Header here; detail in invoice lines. Posted outbound to fin-invoicing-ar, which is the system of record for AR.

One row per service call parts line and one per service call labor session, plus optional adjustment lines (discounts, trip fees).

On-site payment record. Calls into fin-payments for processor auth/capture, but the operational record (which Tech took it, which call, when) lives here.

One on-site visit with a committed assignment, customer window and completion receipt. Created → Scheduled → Dispatched → en route → on site → Completed is the successful path. Cancelled and Rescheduled are controlled exception branches before completion, not transitions after a completed signed visit.

Posted parts consumed on an assigned call, with immutable cost/price snapshots and an atomic stock movements posting. A draft mobile event is not a posted consumption record. Linked reversals compensate stock and trigger billing reconciliation.

Labor entries — one per Tech per session on the call. The check-in / check-out workflow opens and closes a row automatically; manual entries are allowed for crew helpers.

Immutable customer signature receipts bound to an exact displayed document, scope revision and authorized signer. Estimate approval and completion acknowledgment have separate purposes and cannot substitute for each other.

Versioned permission to finish a service visit with an unpaid balance. Admin publishes an exact category/customer policy revision; an independent Operations Manager still approves each call-specific exception. Published terms are immutable, and retiring a policy stops new decisions and prevents consumption of unused approvals; it preserves receipts already used for completed work. This records payment terms rather than a payment or cleared receivable.

Immutable audit of every status transition and assignment change on a service call. The disputed-call ledger.

Recurring service contracts — an HVAC tune-up twice a year, a restaurant-chain quarterly hood-system service. Drives the generate-the-next-call workflow on the cadence.

The included services per contract. The cadence engine reads these to generate the right service call with the right service category on the right date.

One on-site visit with a committed assignment, customer window and completion receipt. Created → Scheduled → Dispatched → en route → on site → Completed is the successful path. Cancelled and Rescheduled are controlled exception branches before completion, not transitions after a completed signed visit.

Post-call CSAT trigger. Lightweight mirror of the survey trip; the survey infrastructure itself lives in support-csat-nps.

The physical site the Tech is going to. A Customer can have many sites — a property manager has dozens. The dispatch-and-route engine uses the lat/long here to sequence the day.

The account a service is rendered to. May be a residential homeowner or a B2B account (a property manager, a restaurant chain). One Customer has one or many service locations.

Local mapping of authenticated principals; identity login is owned by ERP.AI. Each mapped user has one local role; immutable actions retain their real actor.