Appointment Data Model

Data Model Overview

🏗️ Primary Entities

Appointments: Scheduled shop visits with status, type, and scheduling window

Scheduling Times: Start/end window, missed timestamp in UTC and local timezone

Source Attribution: How the appointment was created (widget, SMS integration, Google, etc.)

Campaign Attribution: Optional link to the marketing campaign that drove the booking

🔗 Key Relationships

Many-to-One: Appointment → Shop (each appointment belongs to one shop)

Many-to-One: Appointment → Customer (each appointment may belong to one customer)

Many-to-One: Appointment → Vehicle (each appointment may be for one vehicle)

Many-to-One: Appointment → Campaign (optional marketing attribution)

Tip

Flat Structure: Unlike invoices, appointments are returned as flat rows with no nested arrays. Requested services are not available in the data warehouse — the appointment APPOINTMENT_DESCRIPTION field often contains the service request details as free text.

Field Specifications

Appointment Identification

Core identification fields linking appointments to tenants, shops, customers, vehicles, and campaigns.

Tip

Primary Key: STEER_APPOINTMENT_ID is the primary key for this table. Each row represents one unique appointment.

Field Name	Data Type	Description	Required
STEER_TENANT_ID	String (UUID)	Unique tenant identifier (parent organization)	Yes
STEER_SHOP_ID	String (UUID)	Shop location where the appointment is scheduled. Links to Shop Data Model STEER_SHOP_ID	Yes
STEER_CUSTOMER_ID	String (UUID)	Customer who booked the appointment. Links to Customer Data Model STEER_CUSTOMER_ID	No
STEER_VEHICLE_ID	String (UUID)	Vehicle the appointment is for. Links to Vehicle Data Model STEER_VEHICLE_ID	No
STEER_APPOINTMENT_ID	String (UUID)	Unique appointment identifier (primary key)	Yes
STEER_CAMPAIGN_ID	String (UUID)	Marketing campaign that drove the booking. Links to Campaign Data Model STEER_CAMPAIGN_ID	No
STEER_MARKETING_TYPE_ID	String (UUID)	Marketing type associated with the campaign attribution	No

Appointment Details

Core appointment information including title, status, type, and source.

Field Name	Data Type	Description	Required
APPOINTMENT_TITLE	String	Appointment subject/heading (e.g., Oil Change, or integration-generated titles)	No
APPOINTMENT_DESCRIPTION	String	Detailed notes or service request details, often free text including requested services and comments	No
APPOINTMENT_STATUS	String	Current appointment status (see enum values below)	Yes
APPOINTMENT_TYPE	String	Visit type — drop-off, wait, or pick-up (see enum values below)	No
APPOINTMENT_SOURCE_TYPE	String	How the appointment was created (see enum values below)	No
APPOINTMENT_LEAD_SOURCE	String	Free-text lead source tracking value entered by the shop (e.g., Text Marketing, Social Media)	No

Vehicle Display

Denormalized vehicle display names captured on the appointment.

Field Name	Data Type	Description	Required
APPOINTMENT_VEHICLE_NAME	String	Display name of the vehicle (e.g., 2021 Toyota Camry)	No
APPOINTMENT_VEHICLE_FULL_NAME	String	Full vehicle description including model details	No

Timestamps

Appointment lifecycle dates in both UTC and the shop’s local timezone.

Field Name	Data Type	Description	Required
APPOINTMENT_CREATED_AT	Timestamp	Appointment creation time as reported by the source system (UTC). For appointments imported from a shop management system this reflects the source/integration timestamp, which during a bulk import or migration is the import time rather than the original booking time. See the caution below.	Yes
APPOINTMENT_STARTED_AT	Timestamp	Scheduled appointment window start (UTC)	No
APPOINTMENT_ENDED_AT	Timestamp	Scheduled appointment window end (UTC)	No
APPOINTMENT_MISSED_AT	Timestamp	When the appointment was marked as missed (UTC)	No
APPOINTMENT_STARTED_AT_LOCAL	Timestamp	Scheduled window start (shop’s local timezone)	No
APPOINTMENT_ENDED_AT_LOCAL	Timestamp	Scheduled window end (shop’s local timezone)	No
APPOINTMENT_MISSED_AT_LOCAL	Timestamp	Missed timestamp (shop’s local timezone)	No

Deletion Flag

Field Name	Data Type	Description	Required
APPOINTMENT_IS_DELETED	Boolean	Soft-delete flag. true when the appointment was deleted from the platform, removed by an integration sync, or removed as a duplicate. Deleted appointments are included so consumers can propagate deletions	Yes

System Metadata

Field Name	Data Type	Description	Required
STEER_CREATED_AT	Timestamp	When the record was first written into Steer (UTC). This is a Steer-side ingest timestamp, independent of the source system — for imported appointments it reflects the moment Steer ingested the record (e.g. the onboarding/migration time), not the original booking time. Use this to distinguish genuine real-time bookings from bulk-imported history.	No
STEER_UPDATED_AT	Timestamp	Last modification timestamp for this appointment record (UTC)	No

Field Enum Values

Status

• RequestMade: Booking request submitted, awaiting shop approval
• Booked: Confirmed and scheduled
• InProgress: Vehicle currently being serviced
• Finished: Appointment completed
• Missed: Customer did not show up
• Declined: Appointment request declined
• CheckedIn / Unknown: Rare legacy values that may appear in historical data

Type

• DropOff: Customer drops off the vehicle
• Wait: Customer waits at the shop during service
• PickUp: Customer picks up the vehicle
• None: No specific visit type recorded

Source Type

• Manual: Created manually by shop staff
• Campaigns: Steer Scheduler Link
• Website: Steer Scheduler Widget
• ShopManagementSystem: Synced from the shop management system integration
• MechanicAdvisor: MechanicAdvisor integration
• Google: Reserve with Google
• PublicVehiclePage: Vehicle Profile public page
• AutoOps: AutoOps scheduling tool
• DigitalGlovebox: Digital Glovebox

Timestamp Handling

Caution

Timezone: All UTC timestamp fields (APPOINTMENT_CREATED_AT, APPOINTMENT_STARTED_AT, APPOINTMENT_ENDED_AT, APPOINTMENT_MISSED_AT, STEER_CREATED_AT, STEER_UPDATED_AT) are stored as timestamp_ntz (timestamp without timezone) in UTC. Local timezone variants (APPOINTMENT_STARTED_AT_LOCAL, APPOINTMENT_ENDED_AT_LOCAL, APPOINTMENT_MISSED_AT_LOCAL) are in the shop’s local timezone. Use UTC timestamps for data processing and local timestamps for display purposes.

Danger

APPOINTMENT_CREATED_AT vs STEER_CREATED_AT — do not confuse them. They answer different questions:

• APPOINTMENT_CREATED_AT is the source system’s creation timestamp. When a shop is onboarded or migrated between integrations, its entire appointment history is imported in one batch — and for that imported history this field reflects the import/sync time, not when the customer originally booked. This produces large spikes of appointments all sharing one APPOINTMENT_CREATED_AT date.
• STEER_CREATED_AT is when the record first entered Steer (a stable, Steer-side ingest stamp). For an onboarded shop, the bulk of records share the onboarding date here.

For analyzing real appointment-creation activity (e.g. creation-to-start lead time), do not treat APPOINTMENT_CREATED_AT as the true booking time for imported records. A reliable approach is to evaluate each record’s relationship between created and start time: when APPOINTMENT_STARTED_AT >= APPOINTMENT_CREATED_AT the booking is a genuine forward booking and the lead time is trustworthy; when APPOINTMENT_STARTED_AT < APPOINTMENT_CREATED_AT the record was almost certainly back-imported and its creation time cannot be used. STEER_CREATED_AT helps you identify and exclude the bulk-import cohort.

Data Quality Notes

Null Values

Note

Expected Null Values

• STEER_CUSTOMER_ID may be null for appointments not linked to a customer (e.g., blocked time or walk-ins)
• STEER_VEHICLE_ID, APPOINTMENT_VEHICLE_NAME, and APPOINTMENT_VEHICLE_FULL_NAME may be null when no vehicle is linked
• STEER_CAMPAIGN_ID and STEER_MARKETING_TYPE_ID are null for appointments without campaign attribution (the majority)
• APPOINTMENT_DESCRIPTION is null when no notes or service details were recorded
• APPOINTMENT_MISSED_AT and its local variant are only set for appointments with status Missed
• APPOINTMENT_LEAD_SOURCE is a free-text field and is null for most appointments
• STEER_CREATED_AT is populated for all current records; treat it as nullable on ingest for safety
• STEER_UPDATED_AT may be null for a small fraction (~1%) of historical records; fall back to STEER_CREATED_AT for incremental processing if needed

Sample Record

Complete API response example with an appointment record:

{
  "data": [
    [
      "d119bb99-0645-43e3-8274-247362e5432e",
      "385620aa-685f-43b6-bbcf-2d59ce25edc8",
      "08d6f284-ff43-4475-8a35-31ed2b379794",
      "403b8b7e-534e-4772-b450-b96673c2341f",
      "e2ca36ab-4f4f-45eb-9168-20403e8ef1da",
      null,
      null,
      "Oil Change Appointment",
      "SERVICE: EMISSIONS INSPECTION\r\nCOMMENT: SAFETY AND EMISSIONS INSPECTION",
      "Finished",
      "Wait",
      "Website",
      null,
      "2011 Honda Fit",
      "2011 Honda Fit Fit",
      "2026-03-20T17:41:41.003Z",
      "2026-03-27T15:00:00.000Z",
      "2026-03-27T15:30:00.000Z",
      null,
      "2026-03-27T10:00:00.000Z",
      "2026-03-27T10:30:00.000Z",
      null,
      false,
      "2026-03-20T17:45:02.118Z",
      "2026-03-27T16:02:11.451Z"
    ]
  ],
  "metadata": {
    "rowType": [
      {"name": "STEER_TENANT_ID", "type": "text"},
      {"name": "STEER_SHOP_ID", "type": "text"},
      {"name": "STEER_CUSTOMER_ID", "type": "text"},
      {"name": "STEER_VEHICLE_ID", "type": "text"},
      {"name": "STEER_APPOINTMENT_ID", "type": "text"},
      {"name": "STEER_CAMPAIGN_ID", "type": "text"},
      {"name": "STEER_MARKETING_TYPE_ID", "type": "text"},
      {"name": "APPOINTMENT_TITLE", "type": "text"},
      {"name": "APPOINTMENT_DESCRIPTION", "type": "text"},
      {"name": "APPOINTMENT_STATUS", "type": "text"},
      {"name": "APPOINTMENT_TYPE", "type": "text"},
      {"name": "APPOINTMENT_SOURCE_TYPE", "type": "text"},
      {"name": "APPOINTMENT_LEAD_SOURCE", "type": "text"},
      {"name": "APPOINTMENT_VEHICLE_NAME", "type": "text"},
      {"name": "APPOINTMENT_VEHICLE_FULL_NAME", "type": "text"},
      {"name": "APPOINTMENT_CREATED_AT", "type": "timestamp_ntz"},
      {"name": "APPOINTMENT_STARTED_AT", "type": "timestamp_ntz"},
      {"name": "APPOINTMENT_ENDED_AT", "type": "timestamp_ntz"},
      {"name": "APPOINTMENT_MISSED_AT", "type": "timestamp_ntz"},
      {"name": "APPOINTMENT_STARTED_AT_LOCAL", "type": "timestamp_ntz"},
      {"name": "APPOINTMENT_ENDED_AT_LOCAL", "type": "timestamp_ntz"},
      {"name": "APPOINTMENT_MISSED_AT_LOCAL", "type": "timestamp_ntz"},
      {"name": "APPOINTMENT_IS_DELETED", "type": "boolean"},
      {"name": "STEER_CREATED_AT", "type": "timestamp_ntz"},
      {"name": "STEER_UPDATED_AT", "type": "timestamp_ntz"}
    ]
  }
}