Data model

Connect service locations, accepted visits, posted stock and billing receipts.

35 tables · 88 relationships

Data model 34 of 35 tables drawn

Data model34 of 35 tables, within two hops of ServiceCalls

How the model is organised

People and capacity

Users, Technicians, TechnicianSkills, Crews, CrewMembers, Schedules, ScheduleSlots, ScheduleExceptions, TechnicianAvailabilityDays. Map real principals and participating technicians to usable working time.

Users
Technicians
TechnicianSkills
Crews
CrewMembers
Schedules
ScheduleSlots
ScheduleExceptions
TechnicianAvailabilityDays

Customers and agreements

Customers, ServiceLocations, ServiceCategories, ServiceContracts, ServiceContractItems, CreditPolicies. Keep customer locations, service obligations and versioned credit terms connected.

Customers
ServiceLocations
ServiceCategories
ServiceContracts
ServiceContractItems
CreditPolicies

Visits and mobile work

ServiceCalls, ServiceCallActivities, ServiceCallLabor, ServiceCallNotes, ServiceCallPhotos, Routes, RouteStops, MobileSyncQueue. Retain accepted appointments, field evidence and original synchronization identities.

ServiceCalls
ServiceCallActivities
ServiceCallLabor
ServiceCallNotes
ServiceCallPhotos
Routes
RouteStops
MobileSyncQueue

Parts and inventory

Parts, TruckStock, WarehouseStock, ServiceCallParts, StockMovements, PartsReplenishments. Trace each draw, replenishment and compensating return to the exact stock source.

Parts
TruckStock
WarehouseStock
ServiceCallParts
StockMovements
PartsReplenishments

Commercial evidence

Estimates, Signatures, Invoices, InvoiceLines, Payments, Surveys. Connect exact customer-authorized scope to invoices, confirmed collection and actual feedback.

Estimates
Signatures
Invoices
InvoiceLines
Payments
Surveys

Keys and references

Use unique source, occurrence, device and connector command identities. Customer/location/call ownership must agree across references. Preserve exact signed revisions and immutable posted movements; never use a display name as an authorization key.

Migration order

Map Users, Technicians, skills, crews and service categories.
Load customer locations, access controls, contracts and item recurrence.
Configure working slots, dated unavailability, parts and the single inventory ownership mode.
Load validated open calls, accepted assignments and opening stock evidence.
Configure estimate/signature and versioned credit policy, payment requests, AR mapping and survey connection.
Reconcile test visits, denied actions, mobile conflicts, stock reversals and billing acknowledgements before release.

Tables

Find a table 35 tables

TechniciansThe field-tech master. Every dispatched-and-on-site human is onemaster

Field	Type	Required	Details
`Tech`	text	yes	Tech ID, e.g. T-1042
`FullName`	text	yes
`EmployeeRef`	text		HRIS Employee number
`MobilePhone`	text	yes	US 10-digit; used for 10DLC SMS to Tech
`BadgeNumber`	text		On-site ID badge
`Email`	text	yes
`Status`	select	yes	Active, OnLeave, Inactive
`HomeBaseLocation`	text		Warehouse / depot the truck returns to
`ServiceArea`	text		ZIP-code list or metro tag
`HireDate`	date
`HourlyBillableRate`	currency		USD; used by ServiceCallLabor default
`Active`	toggle	yes
`User`	reference	yes	→ Users; unique mapping to Technician or Crew Lead principal
`TimeZone`	text	yes	IANA scheduling time zone; weekly working slots and technician day boundaries resolve to UTC instants in this zone

TechnicianSkillsPer-Tech skills and certifications with expiry. The Line item of Technicians.line item

Field	Type	Required	Details
`Technician`	reference	yes	→ Technicians
`Skill`	select	yes	HVAC, HVAC-Refrigerant-EPA, Electrical, Electrical-HighVoltage, Plumbing, Plumbing-Gas, Refrigeration, Appliance, Boiler, SolarPV, Other
`Rating`	select		Apprentice, Journeyman, Master
`CertificationNumber`	text
`IssuedAt`	date
`ExpiresAt`	date		Auto-drops from eligibility once past
`Active`	toggle	yes

CrewsMulti-tech crew for jobs that need more than one body — installmaster

Field	Type	Required	Details
`CrewName`	text	yes
`Lead`	reference	yes	→ Technicians
`MemberCount`	formula	yes	fx: COUNT_DISTINCT(CrewMembers.Technician where Crew=this AND Active=true AND EffectiveFrom <= now AND (EffectiveTo IS NULL OR EffectiveTo > now))
`ServiceArea`	text
`Active`	toggle	yes

CrewMembersThe roster of techs in a crew. A Tech can be in more than one crew Line item of Crews.line item

Field	Type	Required	Details
`Crew`	reference	yes	→ Crews
`Technician`	reference	yes	→ Technicians
`RoleOnCrew`	select		Lead, Helper, Apprentice
`Active`	toggle	yes
`EffectiveFrom`	datetime	yes	Inclusive membership start; history referenced by accepted assignments cannot be deleted
`EffectiveTo`	datetime		Exclusive membership end; empty means open-ended. A crew assignment requires membership covering its full scheduled interval

ServiceCategoriesThe service taxonomy the dispatcher and the customer pick from.master

Field	Type	Required	Details
`CategoryCode`	text	yes
`Name`	text	yes	e.g. HVAC-Repair, HVAC-Maintenance, Electrical-Service, Plumbing-Emergency
`Trade`	select	yes	HVAC, Electrical, Plumbing, Refrigeration, Appliance, Other
`DefaultDurationMinutes`	number	yes	Used by ScheduleSlots and route ETA
`RequiredSkills`	text	yes	Comma-separated skill tags; matched against TechnicianSkills.Skill
`DefaultPriority`	select	yes	Low, Medium, High, Emergency
`BillableRateOverride`	currency		If set, overrides Tech.HourlyBillableRate
`RequiresPaymentOnCompletion`	toggle	yes	Drives the on-site payment flow
`Active`	toggle	yes
`CrewLeadApprovalLimit`	currency	yes	USD; effective configuration copied to each submitted estimate; zero routes all decisions to Operations Manager
`RequiredPhotoPhases`	text		Configured Before, During or After evidence required before completion

CustomersThe account a service is rendered to. May be a residentialmaster

Field	Type	Required	Details
`CustomerNumber`	text	yes
`Name`	text	yes
`Type`	select	yes	Residential, Commercial, Property-Manager, Government
`ContactPhone`	text	yes	US 10-digit
`ContactEmail`	text	yes
`BillingAddressLine1`	text
`BillingCity`	text
`BillingState`	select		US state, two-letter
`BillingZip`	text		US 5-digit (or ZIP+4)
`TCPAOptInForSMS`	toggle	yes	Required before any 10DLC SMS to this customer
`TCPAOptInAt`	datetime		Evidence time for the recorded SMS permission; revocation clears send eligibility without deleting history
`AccessNotes`	text		Gate code, dog-on-property, side-entrance — masked except to assigned Tech
`Active`	toggle	yes

ServiceLocationsThe physical site the Tech is going to. A Customer can have many Line item of Customers.line item

Field	Type	Required	Details
`Customer`	reference	yes	→ Customers
`LocationName`	text	yes	"Main Office", "Unit B", "Roof of 4th & Main"
`AddressLine1`	text	yes
`AddressLine2`	text
`City`	text	yes
`State`	select	yes	US state, two-letter
`Zip`	text	yes	US 5-digit
`Latitude`	number		Geocoded; used by route-planning
`Longitude`	number
`AccessNotes`	text		Site-specific gate code, key location
`Active`	toggle	yes
`TimeZone`	text	yes	IANA time zone used for customer windows, recurrence and route dates

ServiceContractsRecurring service contracts — an HVAC tune-up twice a year, atransaction

Field	Type	Required	Details
`ContractNumber`	text	yes
`Customer`	reference	yes	→ Customers
`ContractTermStart`	date	yes
`ContractTermEnd`	date	yes
`Frequency`	select	yes	Monthly, Quarterly, SemiAnnual, Annual
`NextScheduledAt`	date	yes	When the next ServiceCall auto-generates
`ContractValue`	currency	yes	USD, total annualized
`BillingFrequency`	select	yes	UpFront, Monthly, Quarterly, PerVisit
`Status`	select	yes	Draft, Active, Suspended, Cancelled, Expired
`Revision`	number	yes	Schedule changes version future uncreated occurrences; created calls retain their original obligation

ServiceContractItemsThe included services per contract. The cadence engine reads these Line item of ServiceContracts.line item

Field	Type	Required	Details
`ServiceContract`	reference	yes	→ ServiceContracts
`ServiceCategory`	reference	yes	→ ServiceCategories
`ServiceLocation`	reference	yes	→ ServiceLocations
`VisitsPerYear`	number	yes
`IncludedDurationMinutes`	number		Past which billable-time kicks in
`Notes`	text
`NextDueDate`	date	yes	Next service obligation for this item; recurrence uses the customer location time zone
`ServiceDays`	text		Explicit annual calendar for irregular VisitsPerYear; required when not implied by contract Frequency

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

Field	Type	Required	Details
`CallNumber`	text	yes
`Customer`	reference	yes	→ Customers
`ServiceLocation`	reference	yes	→ ServiceLocations
`ServiceCategory`	reference	yes	→ ServiceCategories
`Source`	select	yes	HelpdeskTicket, CustomerWebform, Phone, ServiceContract, RecallVisit, Manual
`HelpdeskTicketNumber`	text		Set when Source=HelpdeskTicket; the originating Ticket
`ServiceContract`	reference		→ ServiceContracts (set when Source=ServiceContract)
`Priority`	select	yes	Low, Medium, High, Emergency
`Status`	select	yes	Created, Scheduled, Dispatched, EnRoute, OnSite, Completed, Cancelled, Rescheduled
`RequestedAt`	datetime	yes	When the customer asked
`RequestedWindowStart`	datetime		Customer's preferred earliest
`RequestedWindowEnd`	datetime		Customer's preferred latest
`ScheduledStart`	datetime		Committed by Dispatcher after eligibility and overlap checks
`EstimatedDurationMinutes`	number	yes	Defaulted from ServiceCategory.DefaultDurationMinutes
`AssignedTechnician`	reference		→ Technicians (null if Crew assigned)
`AssignedCrew`	reference		→ Crews (null if single Tech)
`ArrivedAt`	datetime		Stamped on EnRoute → OnSite
`CompletedAt`	datetime		Stamped on OnSite → Completed
`CancelledAt`	datetime
`CancelReason`	select		CustomerCancelled, NoAccess, NoPartsAvailable, WeatherDelay, TechUnavailable, Duplicate, Other
`ResolutionSummary`	text		What got fixed, in plain English
`InvoiceGeneratedAt`	datetime		Set by invoice-generation-from-completed-call
`Revision`	number	yes	Server incremented on each accepted state, assignment or scope change
`ScheduledEnd`	datetime		ScheduledStart plus accepted duration; include configured travel buffers when checking resource conflicts
`AssignedBy`	reference		→ Users; dispatcher who committed the current assignment
`SourceRequestKey`	text	yes	Unique per source connector and source event or recurring occurrence
`ContractItem`	reference		→ ServiceContractItems; required for generated contract visits
`OccurrenceDueDate`	date		Contract-item occurrence date; unique with ContractItem
`OriginalCall`	reference		→ ServiceCalls; required for RecallVisit or a replacement after Rescheduled
`CompletionSignature`	reference		→ Signatures; exact current call and completion scope
`AssignedMembersSnapshot`	longtext		Server-written unique Technician IDs selected at accepted assignment revision, including every crew member; required when scheduled. Preserve prior snapshots in ServiceCallActivities; later roster edits do not rewrite accepted capacity history
`CreditPolicy`	reference		→ CreditPolicies; exact published revision selected by server for this call
`CreditDecision`	select	yes	NotRequested, Pending, Approved, Declined, Invalidated
`CreditRequestedBy`	reference		→ Users; assigned Technician or Crew Lead; required after a request
`CreditRequestedAt`	datetime		Server credit request time
`CreditApprovedBy`	reference		→ Users; Operations Manager, different from requester and CreditPolicy.PublishedBy; only set by a successful decision
`CreditApprovedAt`	datetime		Server decision time; present only for Approved current decision
`CreditCallRevision`	number		Exact call revision approved; immutable completion-only transition may consume this revision without invalidation
`CreditUnpaidAmount`	currency		USD positive outstanding amount authorized after verified net captures; frozen with signed completion scope
`CreditDecisionReceipt`	reference		→ ServiceCallActivities; immutable CreditApproved event containing policy ID/version/hash/full terms, call revision, completion-signature hash, unpaid amount, currency, requester, independent approver and decision time

ServiceCallActivitiesImmutable audit of every status transition and assignment change Line item of ServiceCalls.line item

Field	Type	Required	Details
`ServiceCall`	reference	yes	→ ServiceCalls
`EventType`	select	yes	StatusChange, TechAssigned, TechReassigned, CrewAssigned, Rescheduled, Cancelled, Reopened, NoteAdded, CreditRequested, CreditApproved, CreditDeclined, CreditInvalidated
`OldValue`	text
`NewValue`	text
`ChangedBy`	reference	yes	→ Users; server actor, including an explicitly configured service principal
`ChangedAt`	datetime	yes

ServiceCallPartsPosted parts consumed on an assigned call, with immutable cost/price snapshots and an atomic StockMovements posting. A draft mobile event is not a posted consumption record. Linked reversals compensate stock and trigger billing reconciliation. Line item of ServiceCalls.line item

Field	Type	Required	Details
`ServiceCall`	reference	yes	→ ServiceCalls
`Part`	reference	yes	→ Parts
`Quantity`	number	yes	Positive quantity for an original draw or its full compensating reversal; reversal preserves original part, call and price/cost snapshots
`FromTruckStock`	reference		→ TruckStock (the row this drew from)
`UnitCost`	currency	yes	USD; snapshot of Parts.Cost at time of use
`UnitPrice`	currency	yes	USD; snapshot of Parts.Price at time of use
`LineTotal`	formula	yes	fx: IF(ReversalOf IS NULL, 1, -1) * Quantity * UnitPrice; include both original and reversal for net usage/billing reconciliation, even when the original Status is Reversed
`AddedAt`	datetime	yes
`CommandKey`	text	yes	Unique client event or server operation key
`Status`	select	yes	Posted, Reversed
`ReversalOf`	reference		→ ServiceCallParts; a reversal creates a linked immutable compensating row
`Estimate`	reference		→ Estimates; approved exact scope required for additional work

ServiceCallLaborLabor entries — one per Tech per session on the call. The Line item of ServiceCalls.line item

Field	Type	Required	Details
`ServiceCall`	reference	yes	→ ServiceCalls
`Technician`	reference	yes	→ Technicians
`StartAt`	datetime	yes
`EndAt`	datetime		Null while clock is open
`Hours`	formula	yes	fx: (EndAt − StartAt) in hours
`BillableRate`	currency	yes	USD; defaulted from Tech or Category override
`LineTotal`	formula	yes	fx: Hours × BillableRate
`CommandKey`	text	yes	Unique labor-session start/end command identity
`Estimate`	reference		→ Estimates; approved exact scope required for additional work

ServiceCallNotesFree-form notes — Tech observations, dispatcher dispatch instructions, Line item of ServiceCalls.line item

Field	Type	Required	Details
`ServiceCall`	reference	yes	→ ServiceCalls
`Author`	reference	yes	→ Users; authenticated author with access to this call
`Body`	text	yes
`Visibility`	select	yes	Internal, OnReceipt, OnInvoice
`CreatedAt`	datetime	yes

ServiceCallPhotosBefore / during / after photos — the dispute-killer. Required by Line item of ServiceCalls.line item

Field	Type	Required	Details
`ServiceCall`	reference	yes	→ ServiceCalls
`Phase`	select	yes	Before, During, After
`FileName`	text	yes
`StorageURL`	text	yes
`Caption`	text
`TakenBy`	reference	yes	→ Technicians
`TakenAt`	datetime	yes

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

Field	Type	Required	Details
`Part`	text	yes	SKU
`Description`	text	yes
`Trade`	select		HVAC, Electrical, Plumbing, Refrigeration, Appliance, Other
`UnitOfMeasure`	select	yes	Each, Foot, Pound, Gallon, Box
`Cost`	currency	yes	USD; landed cost into warehouse
`Price`	currency	yes	USD; billable price to customer
`MinReorder`	number	yes	Warehouse-level reorder threshold
`TruckMinPerTech`	number		If set, every Tech's TruckStock should hold at least this many
`Active`	toggle	yes

TruckStockPer-Technician parts inventory on the truck. The most-frequentlymaster

Field	Type	Required	Details
`Technician`	reference	yes	→ Technicians
`Part`	reference	yes	→ Parts
`Quantity`	number	yes	Signed StockMovements sum for this exact stock row: incoming Quantity minus outgoing Quantity across both Posted and Reversed rows, including original and compensating rows; nonnegative
`LastReplenishedAt`	date
`LastCountedAt`	date		Set on physical truck count
`BelowMinimum`	formula	yes	fx: Quantity < Parts.TruckMinPerTech

WarehouseStockCentral warehouse parts inventory. The source for replenishmentmaster

Field	Type	Required	Details
`Part`	reference	yes	→ Parts
`Warehouse`	text	yes	Warehouse / depot name
`Quantity`	number	yes	Local mode: incoming Quantity minus outgoing Quantity across both Posted and Reversed StockMovements for this row. External mode: correlated acknowledged balance only; never both ledgers
`LastCountedAt`	date
`BelowMinimum`	formula	yes	fx: Quantity < Parts.MinReorder

PartsReplenishmentsInventory 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.transaction

Field	Type	Required	Details
`ReplenishmentNumber`	text	yes
`Type`	select	yes	WarehouseToTruck, WarehouseToSupplier
`Part`	reference	yes	→ Parts
`Quantity`	number	yes
`FromWarehouse`	text		Set on WarehouseToTruck
`ToTechnician`	reference		→ Technicians (set on WarehouseToTruck)
`ToSupplier`	text		Set on WarehouseToSupplier
`Status`	select	yes	Suggested, Approved, Picked, Loaded, Sent, Completed, Cancelled
`RequestedAt`	date	yes
`CompletedAt`	date
`CreatedBy`	reference	yes	→ Users; source request actor
`ApprovedBy`	reference		→ Users; Operations Manager, different from CreatedBy
`ApprovedAt`	datetime		Required only when Status IN (Approved, Picked, Loaded, Sent, Completed)
`Revision`	number	yes	Exact quantity/source/destination version approved
`CommandKey`	text	yes	Unique replenishment intent; retries reuse it
`ExternalStatus`	select	yes	NotRequested, Pending, Succeeded, Failed, Unknown
`ExternalRef`	text		Acknowledged procurement or external inventory request identity

SchedulesDaily / weekly schedule view header. One per Day per Technician (ortransaction

Field	Type	Required	Details
`ScheduleDate`	date	yes
`Technician`	reference		→ Technicians (null if Crew schedule)
`Crew`	reference		→ Crews (null if individual schedule)
`TotalCalls`	formula	yes	fx: COUNT_DISTINCT(ScheduleCalls(this)); helper scope and day-overlap rules are defined in Scheduling calculations below
`ScheduledMinutes`	formula	yes	fx: SUM_UNION_MINUTES(PlannedIntervals(this)); person-minutes, per-technician interval union clipped to this schedule day
`AvailableMinutes`	formula		fx: IF(schedule day is closed, FrozenAvailabilityMinutes(this), SUM_UNION_MINUTES(AvailableIntervals(this))); exact people/day basis; missing required historical snapshot yields NULL and an incomplete-basis exception
`UtilizationPercent`	formula		fx: IF(AvailableMinutes IS NOT NULL AND AvailableMinutes > 0, 100 * ScheduledMinutes / AvailableMinutes, NULL); planned ratio, distinct from the actual closed-labor Tech Utilization report
`TimeZone`	text	yes	IANA zone defining this ScheduleDate. Individual schedule uses Technician.TimeZone; crew schedule uses the configured dispatch zone shown to its dispatcher

ScheduleSlotsPer-Technician time-slot configuration — when each tech ismaster

Field	Type	Required	Details
`Technician`	reference	yes	→ Technicians
`DayOfWeek`	select	yes	Mon, Tue, Wed, Thu, Fri, Sat, Sun
`StartAt`	text	yes	Local time, HH:MM
`EndAt`	text	yes	Local time, HH:MM
`MaxCalls`	number	yes
`AcceptsEmergency`	toggle	yes	True if this slot is eligible for Priority=Emergency calls
`Active`	toggle	yes

RoutesPer-Tech daily route header. Generated by `route-planning`transaction

Field	Type	Required	Details
`RouteNumber`	text	yes
`Technician`	reference	yes	→ Technicians
`RouteDate`	date	yes
`StartLocation`	text		Defaults to Tech.HomeBaseLocation
`EndLocation`	text
`TotalDriveMinutes`	formula	yes	fx: SUM(RouteStops.DriveMinutesFromPrevious)
`TotalDistanceMiles`	formula	yes	fx: SUM(RouteStops.MilesFromPrevious)
`Status`	select	yes	Planned, Active, Completed, Abandoned

RouteStopsThe ordered stops on a day's route. One per ServiceCall in the Line item of Routes.line item

Field	Type	Required	Details
`Route`	reference	yes	→ Routes
`StopOrder`	number	yes	1-based sequence
`StopType`	select	yes	ServiceCall, WarehousePickup, Lunch, EndOfDay
`ServiceCall`	reference		→ ServiceCalls (set when StopType=ServiceCall)
`ETAArrival`	datetime
`ETADeparture`	datetime
`DriveMinutesFromPrevious`	number
`MilesFromPrevious`	number

SignaturesImmutable customer signature receipts bound to an exact displayed document, scope revision and authorized signer. EstimateApproval and CompletionAcknowledgment have separate purposes and cannot substitute for each other.transaction

Field	Type	Required	Details
`ServiceCall`	reference	yes	→ ServiceCalls
`SignerName`	text	yes	The person who signed
`SignerRole`	select		Homeowner, Tenant, PropertyManager, BusinessOwner, AuthorizedAgent
`SignedAt`	datetime	yes
`SignatureImageURL`	text	yes	Document-store URL
`SignedDocumentType`	select	yes	WorkAuthorization, CompletionAcknowledgment, EstimateApproval
`GeoLatitude`	number		Captured at sign-time for proof
`GeoLongitude`	number
`SignatureNumber`	text	yes	Unique capture receipt
`Estimate`	reference		→ Estimates; required for EstimateApproval
`DocumentRevision`	number	yes	Exact estimate revision or completion scope revision signed
`DocumentHash`	text	yes	Hash of displayed scope, prices and document content
`CapturedBy`	reference	yes	→ Users; authorized capture actor
`SignerAuthorityEvidence`	text	yes	Recorded reason the customer signer can authorize this customer/location/scope

InvoicesThe invoice generated from a completed ServiceCall. Header here;transaction

Field	Type	Required	Details
`InvoiceNumber`	text	yes
`ServiceCall`	reference	yes	→ ServiceCalls
`Customer`	reference	yes	→ Customers
`InvoiceDate`	date	yes
`DueDate`	date	yes
`Currency`	select	yes	USD only
`Subtotal`	formula	yes	fx: SUM(InvoiceLines.LineTotal); tax is excluded from all line totals
`TaxAmount`	currency		USD; per US state / local rules
`TotalAmount`	formula	yes	fx: Subtotal + TaxAmount
`AmountPaid`	formula	yes	fx: SUM(Payments.NetCaptured where Invoice=this); do not count authorizations, unknown captures or refunded amounts
`BalanceDue`	formula	yes	fx: TotalAmount − AmountPaid
`Status`	select	yes	Draft, Sent, PartiallyPaid, Paid, Void, Disputed
`ARSyncedAt`	datetime		Required only when SyncStatus is Succeeded; server verified acknowledgement time
`SyncStatus`	select	yes	NotRequested, Pending, Succeeded, Failed, Unknown
`SyncRequestKey`	text	yes	Unique call billing revision sent to AR; retries use the same key
`ExternalInvoiceRef`	text		Verified AR invoice identity; populated only after acknowledged success
`BillingRevision`	number	yes	Frozen completion billing snapshot
`TaxRuleVersion`	text	yes	Effective configured tax decision reference; never infer a rate from a state abbreviation

InvoiceLinesOne row per ServiceCallParts line and one per ServiceCallLabor Line item of Invoices.line item

Field	Type	Required	Details
`Invoice`	reference	yes	→ Invoices
`LineType`	select	yes	Parts, Labor, TripFee, Discount, Other
`Description`	text	yes
`Quantity`	number	yes
`UnitPrice`	currency	yes	USD
`LineTotal`	formula	yes	fx: Quantity × UnitPrice; discounts use signed negative UnitPrice; tax is carried once in Invoices.TaxAmount
`SourceServiceCallPart`	reference		→ ServiceCallParts (when LineType=Parts)
`SourceServiceCallLabor`	reference		→ ServiceCallLabor (when LineType=Labor)

PaymentsOn-site payment record. Calls into `fin-payments` for processortransaction

Field	Type	Required	Details
`PaymentNumber`	text	yes
`ServiceCall`	reference	yes	→ ServiceCalls
`Invoice`	reference		→ Invoices
`Customer`	reference	yes	→ Customers
`Method`	select	yes	Card, Check, Cash, ACH
`Amount`	currency	yes	USD
`ProcessorRef`	text		Stripe Terminal / Square charge ID — masked except Admin / Operations Manager
`ProcessorName`	select		StripeTerminal, SquareInPerson, Manual
`Status`	select	yes	Pending, Authorized, Captured, Unknown, Failed, PartiallyRefunded, Refunded, Voided
`CollectedBy`	reference	yes	→ Technicians
`CollectedAt`	datetime		Required only when Status IN (Captured, PartiallyRefunded, Refunded); never a pending intent timestamp
`CommandKey`	text	yes	Stable payment request identity; replays never create another charge
`RequestedAt`	datetime	yes	Payment intent creation time
`ConfirmedAt`	datetime		Required only when Status IN (Captured, PartiallyRefunded, Refunded); verified processor or approved cash/check receipt
`RefundedAmount`	currency	yes	USD; sum of acknowledged refunds, bounded from zero to captured Amount
`NetCaptured`	formula	yes	fx: captured Amount minus acknowledged RefundedAmount; zero before confirmed capture

EstimatesPre-work quote. Required when a Tech finds work beyond thetransaction

Field	Type	Required	Details
`EstimateNumber`	text	yes
`ServiceCall`	reference	yes	→ ServiceCalls
`Customer`	reference	yes	→ Customers
`EstimatedAmount`	currency	yes	USD
`Status`	select	yes	Draft, Submitted, InternallyApproved, PresentedToCustomer, Approved, Declined, Expired, Cancelled
`PresentedAt`	datetime
`ApprovedAt`	datetime
`ApprovalSignature`	reference		→ Signatures (with SignedDocumentType=EstimateApproval)
`ExpiresAt`	datetime
`Revision`	number	yes	Incremented for scope, price or terms changes; prior decisions never apply to a changed revision
`Scope`	longtext	yes	Exact additional work, quantities, price basis and exclusions presented to the customer
`Terms`	longtext	yes	Expiry and payment terms shown with the estimate
`CreatedBy`	reference	yes	→ Users; originating technician or crew lead
`SubmittedAt`	datetime		Server submission time
`ApprovalLimitSnapshot`	currency		Configured Crew Lead limit frozen on submission
`ReviewedBy`	reference		→ Users; eligible Crew Lead or Operations Manager, different from CreatedBy
`ReviewedAt`	datetime		Internal independent decision time
`ApprovedRevision`	number		Revision accepted internally and signed by the customer

SurveysPost-call CSAT trigger. Lightweight mirror of the survey trip; thetransaction

Field	Type	Required	Details
`ServiceCall`	reference	yes	→ ServiceCalls
`Customer`	reference	yes	→ Customers
`Score`	number		1–5; populated when customer responds
`Comment`	text
`TriggeredAt`	datetime		Verified survey provider accepted time
`RespondedAt`	date
`SurveyNumber`	text	yes	Unique completed-call survey intent
`Status`	select	yes	Pending, Sent, Responded, Failed, Unknown, Suppressed
`RequestKey`	text	yes	Unique completed ServiceCall and survey purpose
`RequestedAt`	datetime	yes	Local follow-up intent time

MobileSyncQueuePending 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.transaction

Field	Type	Required	Details
`QueueNumber`	text	yes
`Technician`	reference	yes	→ Technicians
`ServiceCall`	reference		→ ServiceCalls
`EventType`	select	yes	StatusChange, PartsAdded, LaborStarted, LaborEnded, PhotoUploaded, SignatureCaptured, PaymentCollected, NoteAdded
`Payload`	text	yes	JSON-serialized event body
`QueuedAt`	datetime	yes
`AppliedAt`	datetime
`Status`	select	yes	Queued, Applied, Failed, Discarded
`FailureReason`	text
`DeviceEventKey`	text	yes	Unique authenticated device and event identity; reused for replay
`ExpectedCallRevision`	number	yes	Revision observed when action was captured
`CapturedAt`	datetime	yes	Device-observed time retained separately from server acceptance time
`ResultRef`	text		Original accepted command receipt, stock posting or payment intent identity

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

Field	Type	Required	Details
`UserNumber`	text	yes	Unique operational principal mapping
`PrincipalId`	text	yes	Unique authenticated identity; never supplied as actor by the client
`DisplayName`	text	yes
`Role`	select	yes	Admin, Operations Manager, Dispatcher, Technician, Crew Lead, Inventory Clerk, Viewer
`Active`	boolean	yes	Inactive users cannot execute commands

StockMovementsImmutable 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.transaction

Field	Type	Required	Details
`MovementNumber`	text	yes	Unique movement number
`CommandKey`	text	yes	Unique operation key; duplicate requests return original result
`Part`	reference	yes	→ Parts
`Quantity`	number	yes	Positive movement quantity
`FromWarehouse`	reference		→ WarehouseStock
`FromTruck`	reference		→ TruckStock
`ToWarehouse`	reference		→ WarehouseStock
`ToTruck`	reference		→ TruckStock
`ServiceCallPart`	reference		→ ServiceCallParts; consumption source
`Replenishment`	reference		→ PartsReplenishments; transfer source
`ReversalOf`	reference		→ StockMovements; at most one full compensating reversal
`Status`	select	yes	Posted, Reversed; lifecycle label only: both remain included in signed balance aggregation
`PostedAt`	datetime	yes	Server committed time
`PostedBy`	reference	yes	→ Users; authenticated authorized operation
`SourceEvidence`	attachment		Verified accessible authorization receipt required for an opening receipt, count adjustment or reversal; immutable after posting

ScheduleExceptionsAccepted 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.transaction

Field	Type	Required	Details
`ExceptionNumber`	text	yes	Unique operational exception identity
`Technician`	reference	yes	→ Technicians
`StartAt`	datetime	yes	Inclusive UTC instant
`EndAt`	datetime	yes	Exclusive UTC instant after StartAt
`Reason`	select	yes	Leave, Training, Nonworking
`Status`	select	yes	Active, Cancelled
`SourceKey`	text	yes	Unique local request or source-system exception/revision key; retries return the accepted interval
`SourceEvidence`	text	yes	Retained authorized instruction or acknowledged HR event; excludes private medical details
`RecordedBy`	reference	yes	→ Users; authenticated Operations Manager or configured service actor
`RecordedAt`	datetime	yes	Server acceptance time

CreditPoliciesVersioned 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.master

Field	Type	Required	Details
`PolicyNumber`	text	yes	Stable policy identifier; unique with Version
`Version`	number	yes	Positive integer; a change creates a new revision
`ServiceCategory`	reference	yes	→ ServiceCategories; exact category scope
`Customer`	reference		→ Customers; null covers eligible customers of this category
`EffectiveFrom`	datetime	yes	Inclusive eligibility start
`EffectiveTo`	datetime		Exclusive eligibility end
`MaximumUnpaidAmount`	currency	yes	USD; positive per-call unpaid limit
`PaymentTermDays`	number	yes	Nonnegative integer days after completed visit used for the resulting receivable
`Terms`	longtext	yes	Full customer-facing credit terms
`Status`	select	yes	Draft, Active, Retired
`ContentHash`	text		Server hash of published category/customer, effective dates, limit and terms; required once Active or Retired
`PublishedBy`	reference		→ Users; authenticated Admin; required once published
`PublishedAt`	datetime		Server publication time; required once published

TechnicianAvailabilityDaysImmutable 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.transaction

Field	Type	Required	Details
`AvailabilityNumber`	text	yes	Unique Technician and AvailabilityDate identity
`Technician`	reference	yes	→ Technicians
`AvailabilityDate`	date	yes	Technician-local calendar day; populated even when no call or labor exists
`TimeZone`	text	yes	IANA zone frozen for that day; later technician-zone changes do not shift this record
`DayStart`	datetime	yes	Inclusive UTC day boundary
`DayEnd`	datetime	yes	Exclusive next-day boundary
`WorkingIntervals`	longtext	yes	Server-validated sorted disjoint UTC intervals from accepted weekly working configuration for this day
`UnavailableIntervals`	longtext	yes	Union of accepted dated leave, training and nonworking intervals clipped to this day
`AvailableMinutes`	number	yes	Nonnegative elapsed minutes in WorkingIntervals minus UnavailableIntervals; zero is a recorded value
`SourceSnapshot`	longtext	yes	Immutable full working-slot values, technician eligibility effective for the day, exception identities and accepted/cancelled interval state; source IDs alone are insufficient
`Status`	select	yes	Frozen
`FrozenAt`	datetime	yes	Server day-close publication instant
`FrozenBy`	reference	yes	→ Users; configured service identity

ERP•AI workspace

Start now Field Service Management

Create your ERP.AI account and get started with Proto.

Start now Download agent

Previous · reference 2 of 6Reports and analytics Next · reference 4 of 6Integrations

Field Service Management

Modules

Field Service Management

Processes

Field Service Management

Industries

Field Service Management

Reference

Field Service Management

Data model

How the model is organised

People and capacity

Customers and agreements

Visits and mobile work

Parts and inventory

Commercial evidence

Keys and references

Migration order

Tables

Start now Field Service Management

Field Service Management

Modules

Field Service Management

Processes

Field Service Management

Industries

Field Service Management

Reference

Data model

How the model is organised

People and capacity

Customers and agreements

Visits and mobile work

Parts and inventory

Commercial evidence

Keys and references

Migration order

Tables

Start now Field Service Management

Your Privacy, Your Choice

Manage Your Cookie Preferences