PostgreSQL Schema for a Field Service Business: Free Starting Structure

Start with the real operating model

Field service data has a simple backbone: clients own or manage locations, locations have assets, assets generate jobs, jobs need people, parts, photos, notes, and billing. If your database does not reflect that chain, your app will feel wrong within a week. Dispatchers will create duplicate customers. Techs will upload photos without context. Owners will ask basic questions and get fuzzy answers.

The free starting structure below is not a full enterprise system. It is a practical field service PostgreSQL schema you can build from. It is also the shape an AI assistant needs if you expect it to answer operational questions without digging through loose spreadsheets and folders.

Core customer and location tables

Do not put everything in a single customer table. Most service companies deal with repeat clients, rental managers, commercial accounts, and multi-location properties. You need separation between the billing relationship and the physical place where work happens.

clients
- id uuid primary key
- company_name text
- contact_name text
- phone text
- email text
- billing_address text
- status text
- created_at timestamptz

properties
- id uuid primary key
- client_id uuid references clients(id)
- property_name text
- service_address text
- access_notes text
- gate_code text
- timezone text
- created_at timestamptz

This lets one client have several service locations, and one location carry its own access notes, safety notes, and service history. That matters when a technician is standing in a driveway and needs the right record, not the billing address from an invoice.

Jobs, visits, and assignments

A job is the business request. A visit is a scheduled trip. An assignment connects a person to that trip. Keep those separate. A panel upgrade, repipe, camera install, or compressor replacement may need multiple visits and more than one technician.

jobs
- id uuid primary key
- client_id uuid references clients(id)
- property_id uuid references properties(id)
- job_type text
- priority text
- status text
- problem_summary text
- internal_notes text
- created_at timestamptz

job_visits
- id uuid primary key
- job_id uuid references jobs(id)
- scheduled_start timestamptz
- scheduled_end timestamptz
- arrival_window text
- visit_status text
- completion_notes text

technicians
- id uuid primary key
- full_name text
- phone text
- email text
- trade text
- active boolean

visit_assignments
- id uuid primary key
- visit_id uuid references job_visits(id)
- technician_id uuid references technicians(id)
- role text
- assigned_at timestamptz

This structure gives dispatch a clean schedule and gives an AI assistant enough detail to say who is going where, when, why, and what needs to be done before arrival.

Assets, equipment, and service history

For HVAC and smart-home work especially, you need equipment records. For plumbing and electrical, assets may be water heaters, panels, generators, pumps, lighting systems, control boards, or recurring service components.

assets
- id uuid primary key
- property_id uuid references properties(id)
- asset_type text
- manufacturer text
- model_number text
- serial_number text
- install_date date
- warranty_end date
- notes text

asset_service_events
- id uuid primary key
- asset_id uuid references assets(id)
- job_id uuid references jobs(id)
- event_type text
- event_notes text
- event_date date

Once assets are tracked, your assistant can answer practical questions: which systems are under warranty, which properties have old equipment, which jobs touched the same model, and what parts were used on the last visit.

Parts inventory and job materials

Inventory is where many field service databases get sloppy. You need item definitions, stock locations, stock movements, and job usage. Do not only store “parts used” as a note. That note cannot tell you what to reorder or which van is short.

parts
- id uuid primary key
- sku text
- part_name text
- category text
- supplier text
- unit_cost numeric(12,2)
- active boolean

stock_locations
- id uuid primary key
- location_name text
- location_type text

inventory_movements
- id uuid primary key
- part_id uuid references parts(id)
- stock_location_id uuid references stock_locations(id)
- job_id uuid references jobs(id)
- movement_type text
- quantity numeric(12,2)
- occurred_at timestamptz

job_parts
- id uuid primary key
- job_id uuid references jobs(id)
- part_id uuid references parts(id)
- quantity_used numeric(12,2)
- billable boolean

Photos, documents, and invoices

Photos should be database records, even if the actual image file lives in object storage. Store the URL, who uploaded it, what job it belongs to, and what kind of photo it is. The same goes for signed approvals, permits, estimates, and invoice PDFs.

job_photos
- id uuid primary key
- job_id uuid references jobs(id)
- visit_id uuid references job_visits(id)
- uploaded_by uuid references technicians(id)
- photo_url text
- caption text
- photo_type text
- taken_at timestamptz

invoices
- id uuid primary key
- job_id uuid references jobs(id)
- invoice_number text
- status text
- subtotal numeric(12,2)
- tax numeric(12,2)
- total numeric(12,2)
- issued_at timestamptz
- paid_at timestamptz

That structure gives you clean follow-through: open jobs missing photos, completed jobs not invoiced, invoices without supporting documentation, and callbacks tied to previous work.

When to build versus buy

If you are a developer building an internal tool, the structure above is a good first pass. Add indexes on foreign keys and status fields. Add audit timestamps. Use enums or lookup tables where your team needs tight control. Add row-level permissions if multiple users or agents will touch the system.

If you are an owner who wants the database without a custom build, SQL Agent is the direct route. It is a pre-built 38-table PostgreSQL operations database for service businesses, designed so an AI assistant can auto-install it in one command and start organizing dispatch, clients, parts, photos, and invoices. The product is a $295 one-time purchase through Stripe, not a monthly software seat.

The biggest benefit is not saving a few CREATE TABLE statements. It is skipping the weeks of decisions around relationships, install scripts, credentials, agent access, and verification. SQL Agent gives your AI a clean operating structure on day one.

Bottom line

A field service schema should be boring in the right way. Clients, properties, jobs, visits, techs, assets, parts, photos, and invoices need clear relationships. If you get that foundation right, your AI assistant can do useful operations work instead of guessing from scattered files.