public-data-docs

Table: VEHICLES_AUD

Description:

Audit history of the vehicles table. Each row is one revision of one vehicle — created whenever any tracked field on that vehicle changes, capturing the vehicle’s full tracked-field state as of that revision (not one row per individual field). This includes manual fleet/ops service-state transitions (service_state_v2), so this is the authoritative history of when a vehicle went out of service and when it returned to operational. It does not track rental/booking state.

⚠️ created_at is not a revision timestamp. It is copied from the vehicle’s original creation time and stays frozen across every revision of that vehicle. To find out when a given audit row happened, join to revinfo — see “Getting the revision timestamp” below. This trips people up regularly; read that section before querying this table for recency or freshness.

🔑 Primary Key

(id, rev) — one row per vehicle per revision. id matches vehicles.id.

Field Name	Data Type	Description
`id`	NUMBER	Vehicle ID. Matches `vehicles.id`.
`rev`	NUMBER	Revision number. Join to `revinfo.rev` to get the actual time of this revision. Monotonically increasing — order timelines by `rev`, not `created_at`.
`revtype`	NUMBER	Kind of change this revision represents. (0 => ADD: vehicle created. 1 => MOD: a tracked field changed. 2 => DEL: vehicle deleted.)
`revtype_name`	TEXT	String label for `revtype`.
`created_at`	TIMESTAMP_TZ	Not a revision timestamp — the vehicle’s original creation time, identical across every revision of that vehicle. Do not use this to check recency; see `revinfo` below.
`code`	TEXT	Internal or external reference code, as of this revision.
`custom_properties`	VARIANT	Tenant-specific/dynamic metadata, as of this revision.
`legal_inspection_date`	DATE	Date of the last legal inspection, as of this revision.
`license_plate`	TEXT	License plate, as of this revision.
`name`	TEXT	Display name/label, as of this revision.
`type`	NUMBER	Legacy hardware/module type discriminator. Use `type_name` for the label.
`type_name`	TEXT	String label for `type`.
`vin`	TEXT	Vehicle Identification Number, as of this revision.
`created_by`	NUMBER	User/system that created the vehicle.
`updated_by`	NUMBER	User/system that made this revision.
`branch_id`	NUMBER	Associated branch/location ID, as of this revision.
`service_state_v2`	NUMBER	Service state as of this revision. Same value space as `vehicles.service_state_v2` (0 => FUNCTIONAL. 1 => INSPECT. 2 => RELOCATE. 3 => COLLECT. 4 => SERVICE_ON_SITE. 5 => SERVICE_WORKSHOP. 6 => SERVICE_EXTERNAL. 7 => REPLACE. 8 => IMPOUNDED. 9 => LOST. 10 => RETIRED. 11 => OTHER. 12 => READY_FOR_DEPLOYMENT. 13 => MAINTENANCE. 14 => WAITING_FOR_PARTS. 15 => UNREPAIRABLE. 16 => DEFLEETED. 17 => OPERATIONAL_HOLD. 18 => AWAITING_ACTION. 19 => SERVICE_MECHANICS. 20 => REGULATORY_INSPECTION_READY. 21 => REGULATORY_INSPECTION. 22 => SERVICE_WORKSHOP_INSURANCE. 23 => SERVICE_WORKSHOP_QUEUE. 24 => READY_FOR_DEPLOYMENT_NEW. 25 => COLLECT_EXTERNAL.)
`service_state_v2_name`	TEXT	String label for `service_state_v2`. Operational states: FUNCTIONAL, INSPECT, RELOCATE.
`category_id`	NUMBER	Vehicle category ID, as of this revision.
`module_id`	NUMBER	ID of the IoT module connected to the vehicle, as of this revision.
`uuid`	TEXT	Universally unique identifier for the vehicle.
`remaining_kilometers`	FLOAT	Estimated range left, as of this revision.
`ignored_rental_blockers`	VARIANT	Rental blockers explicitly ignored, as of this revision.
`_snowflake_inserted_at`	TIMESTAMP_NTZ	System timestamp when the record was inserted into Snowflake. Not the revision time — see `revinfo`.
`_snowflake_updated_at`	TIMESTAMP_NTZ	System timestamp when the record was last updated in Snowflake.
`_snowflake_deleted`	BOOLEAN	True if the record was deleted in the source.

Getting the revision timestamp

vehicles_aud rows carry no usable timestamp of their own — created_at is frozen at the vehicle’s first insert. The actual wall-clock time of a revision lives in revinfo, joined on rev:

SELECT
  a.id,
  a.rev,
  a.revtype_name,
  a.service_state_v2_name,
  TO_TIMESTAMP(r.revtstmp, 3) AS revision_at   -- revtstmp is epoch milliseconds
FROM vehicles_aud a
JOIN revinfo r ON a.rev = r.rev
ORDER BY r.revtstmp DESC;

Use revision_at (from this join) — not created_at — for anything involving recency, freshness, or “when did this change happen.”

Reconstructing service-state history / downtime

A vehicle’s downtime is the interval between a revision that moves service_state_v2 to a non-operational value and the next revision that moves it back to an operational one (FUNCTIONAL / INSPECT / RELOCATE).

Not every revision changes service_state_v2 — a revision can be created by any other tracked field changing (branch reassignment, license plate update, etc.) while the service state stays the same. Collapse consecutive same-state revisions first, or a LAG()/LEAD() pass over the raw rows can anchor an interval on a row that isn’t an actual state change:

WITH ordered AS (
  SELECT a.id, a.rev, a.service_state_v2, a.service_state_v2_name,
         TO_TIMESTAMP(r.revtstmp, 3) AS revision_at,
         LAG(a.service_state_v2) OVER (PARTITION BY a.id ORDER BY a.rev) AS prev_state
  FROM vehicles_aud a
  JOIN revinfo r ON a.rev = r.rev
),
state_changes AS (
  -- keep only rows where service_state_v2 actually changed
  SELECT * FROM ordered WHERE prev_state IS NULL OR prev_state != service_state_v2
)
SELECT id, rev, service_state_v2_name, revision_at,
       LEAD(revision_at) OVER (PARTITION BY id ORDER BY rev) AS next_change_at
FROM state_changes
ORDER BY id, rev;

History window: service_state_v2 has been audited since its source migration shipped — verified as 2026-01-26 (earliest revinfo.revtstmp with a non-null service_state_v2 in the share). Vehicles not modified since then have no recorded service-state in the audit — that reflects no changes since audit start, not missing data.