Field Service Database Schema for AI Agents: What to Include

The core shape: clients, properties, and jobs

The first design decision is to separate clients from properties. A client is the person or company responsible for the work. A property is the physical location where the work happens. A job is the work order at that location.

This sounds basic, but many small systems flatten those records into one spreadsheet row. That breaks down quickly. One client can own several properties. One property can have years of service history. One job can involve multiple visits, technicians, parts, photos, and invoices.

For AI reliability, the schema should make those relationships explicit. The agent should not have to infer whether "Johnson" means the homeowner, the rental property, or the job from last month. IDs and foreign keys remove that ambiguity.

Dispatch and scheduling tables

A field service database schema for AI agents needs more than a due date. It should include appointments, technician assignments, availability windows, service zones, route or travel assumptions, priority, and job readiness.

Dispatch is where small errors become expensive. If the AI agent cannot tell whether a technician is assigned, whether the appointment has been confirmed, whether the required parts are available, or whether the property has access restrictions, it will give dispatch advice that sounds reasonable but fails in the field.

At a conceptual level, dispatch tables should connect jobs to scheduled visits, visits to technicians, technicians to skills and availability, and appointments to status events. That gives the agent a real operating board instead of a list of calendar entries.

Status history and event logs

Current status is not enough. A good schema stores both the current state and the history of changes. For example, a job may move from requested to scheduled, then dispatched, then in progress, then completed, then reopened because a part failed.

If the system only stores the latest status, the agent loses the timeline. It cannot explain what happened, who changed it, or why the customer was told one thing and the invoice shows another. Status history also protects against accidental overwrites.

For AI agents, event logs are a practical guardrail. They let the agent answer questions from recorded facts: when the technician arrived, when the job was marked complete, when photos were uploaded, when the invoice was approved, and when payment was received.

Technicians, skills, and time tracking

Technician records should include more than contact information. The schema should capture skill tags, license or certification notes where needed, service areas, active or inactive status, normal work hours, and assigned vehicle or van. If helpers and lead technicians are used, the relationship between crew members should be represented too.

Time tracking should connect to both jobs and visits. Clock-in and clock-out records are useful for payroll, but they are also useful for operations. They reveal jobs that run long, job types that need better estimates, and callbacks that quietly drain margin.

An AI agent can summarize those patterns only if the records are consistent. "Tech was there most of the afternoon" is not a reliable data point. A timestamped visit record is.

Parts, inventory, and purchasing

Parts are where many service databases are too thin. A useful schema should distinguish between parts needed for a job, parts ordered, parts received, parts loaded onto a van, parts installed, parts returned, and parts marked defective.

Those are different operational states. If the agent sees only a line item called "controller," it cannot know whether the controller was quoted, ordered, on the truck, installed, or replaced under warranty. That difference changes scheduling, billing, and customer communication.

A complete schema will usually include suppliers, purchase orders, purchase order items, inventory locations, van stock, job part requirements, installed parts, returns, and defects. The exact table count can vary, but the relationships should make part movement traceable from request to installation.

Photos, documents, QA, and invoices

Photos should not live as random links in a note. They should attach to the job, the property, and the stage of work when possible: before, during, completion, warranty, or defect. The same applies to signed forms, estimates, permits, and other documents.

QA records should connect to jobs and technicians. A small checklist can capture whether required photos exist, whether the installed parts match the invoice, whether the site was cleaned up, and whether the customer was briefed. The goal is not paperwork for its own sake. The goal is to make completion reliable.

Invoices and payments should connect back to the job records. If the agent can compare completed work, installed parts, invoice lines, and payment status, it can flag the jobs that are finished in the field but incomplete in the office.

Why 38 tables is reasonable

When people hear "38 tables," it can sound like overkill. In practice, it is normal once you stop cramming separate concepts into one row. Clients, properties, jobs, appointments, assignments, technician profiles, skills, time entries, status history, parts, suppliers, purchase orders, van inventory, photos, QA, invoices, payments, and audit records all deserve clear homes.

The point is not complexity. The point is reducing ambiguity. A smaller schema that mixes unrelated facts may look simpler, but it forces the AI agent to interpret text instead of query records. That is where wrong answers come from.

A well-designed schema can still be simple to use. The complexity stays in the structure so the daily workflow feels cleaner.

Design decisions that matter for AI reliability

Stable IDs matter. Human-readable names change, but IDs should not. Status history matters because it preserves the timeline. Foreign keys matter because they tell the agent how facts connect. Separate photo and document tables matter because attachments are part of the work record, not decorations.

Invoice linkage matters because service businesses lose money when completed work does not turn into accurate billing. Parts linkage matters because dispatch decisions depend on material readiness. Audit fields matter because the business needs to know whether a human, an import, or an AI agent changed a record.

If you build your own schema, build for these reliability rules first. Fancy dashboards can wait. Trustworthy records cannot.

Build it yourself or start from a prebuilt schema

If you have a technical team, you can build this yourself in PostgreSQL. Start with the core entities, add status history early, and resist the temptation to store operational facts as freeform notes. Keep notes, but do not make notes carry the whole business.

If you want a ready-made option, SQL Agent is a prebuilt 38-table PostgreSQL operations database for service businesses. It is designed around the same shape described here: dispatch, clients, properties, jobs, technicians, parts, photos, QA, invoices, and payments.

The practical benefit is speed. Instead of spending weeks deciding how to model callbacks, van stock, job photos, and invoice linkage, you can give the AI agent a structured starting point and improve the workflow from there.

Bottom line

A field service database schema for AI should reflect the real world of service operations. Clients are not properties. Jobs are not invoices. Parts are not just notes. Photos are not optional context. Status changes are part of the record.

Whether you build your own or use SQL Agent, the goal is the same: give the AI agent structured operational truth so it can help with dispatch, documentation, billing, and service history without guessing.