Reference

What the database actually contains

No proprietary format and nothing to reverse-engineer. FleetSQL writes a small set of plain PostgreSQL tables, documented here in full, so you can judge before signing whether the questions you need answered are answerable.

Grain: One row per vehicle per day
Events: Fillings, drains, eco-driving
Keys: unit_id joins everything
Types: Plain SQL, no extensions

wialon_standard — the daily telemetry table

The table most queries start from. One row per vehicle per day, keyed on (date, unit_id), which is what makes a re-run of a date replace rather than duplicate. Fuel appears in several columns on purpose: Wialon calculates consumption more than one way, and collapsing them into a single number would hide the disagreement that is often the finding itself.

Column	Type	What it holds
date, unit_id	date, varchar	Primary key — the grain of the table
unit_name	varchar	Denormalised so most queries need no join
trips_km, gps_km	numeric	Distance from trip detection and from raw GPS
trips_hours, engine_hours	numeric	Time in trips, and total engine time
motion_hours, idling_hours	numeric	Moving versus engine-on-and-stationary
consumed_fls_liters	numeric	Consumption from fuel level sensors
consumed_abs_liters	numeric	Consumption from an absolute-consumption sensor
consumed_math_liters	numeric	Consumption calculated mathematically
consumed_rates_liters	numeric	Consumption from configured rates
filled_liters, drained_liters	numeric	Daily totals for fillings and drains
consumed_kwh, charged_kwh	numeric	The same picture for electric vehicles
first_engine_on, last_engine_off	timestamp	Shift boundaries, without parsing messages

units — the vehicle reference

Unit properties as Wialon holds them, so a report can say "DAF XF, 2019, plate ABC-1234" rather than an opaque unit id. custom_fields is JSONB and carries whatever your Wialon account keeps there — cost centre, contract, customer — which is usually what makes a fleet-wide aggregate mean something to finance.

unit_id, unit_name — the join key and its label
vin, plate_number, brand, model, year, color
vehicle_type, fuel_type — the grouping most comparisons need
custom_fields (JSONB) — your own Wialon custom fields, queryable
is_active, last_message_time — for spotting units that went quiet

event_filling and event_drain — fuel, event by event

Where the daily table gives totals, these give incidents: one row per filling or drain, with the level before and after, the volume, the timestamp, and coordinates plus a resolved address. That is enough to answer the question a fleet manager actually asks — not "how much fuel disappeared last month" but "where and when, and who was driving".

Electric fleets are covered by the same tables: battery_level_before, battery_level_after, kwh_charged and kwh_registered sit alongside the litre columns rather than in a separate schema.

Drains over 20 litres in the last 90 days, with location

SELECT d.event_time,
       d.unit_name,
       d.liters,
       d.fuel_level_before,
       d.fuel_level_after,
       d.driver,
       d.location_address
FROM   event_drain d
WHERE  d.date >= CURRENT_DATE - INTERVAL '90 days'
  AND  d.liters > 20
ORDER  BY d.liters DESC;

event_eco_driving — driver behaviour

One row per violation: violation_type, the measured value, duration in both a readable string and duration_seconds, maximum speed during the event, coordinates, address, and the driver assigned at the time. Because the driver name is stored on the event rather than resolved at query time, a later reassignment in Wialon does not silently rewrite last quarter's ranking.

Violations per 100 km by driver, last 30 days

WITH km AS (
  SELECT unit_id, SUM(trips_km) AS km
  FROM   wialon_standard
  WHERE  date >= CURRENT_DATE - INTERVAL '30 days'
  GROUP  BY unit_id
)
SELECT e.driver,
       COUNT(*)                                   AS violations,
       ROUND(SUM(k.km))                           AS km,
       ROUND(100.0 * COUNT(*) / NULLIF(SUM(k.km), 0), 2) AS per_100km
FROM   event_eco_driving e
JOIN   km k USING (unit_id)
WHERE  e.date >= CURRENT_DATE - INTERVAL '30 days'
  AND  e.driver IS NOT NULL
GROUP  BY e.driver
ORDER  BY per_100km DESC;

Joining your own systems

The database is yours, so nothing stops you creating tables next to these and joining them. Fuel card transactions beside event_filling turn "the card was charged for 300 litres" into "and 240 reached the tank". Customer contracts beside wialon_standard turn distance into cost per delivery. Maintenance records beside engine_hours turn a calendar into condition-based servicing.

This is the part a reporting screen cannot do at any price, and it is the reason the data lands in Postgres rather than in another dashboard — see FleetSQL versus Wialon reports for where the line actually falls.

Frequently asked questions

Why are there four consumption columns? +

Because Wialon calculates fuel consumption several ways — level sensors, absolute-consumption sensors, mathematical estimation, configured rates — and they disagree. Collapsing them into one number would hide that disagreement, which is often the finding: a vehicle whose sensor and rate figures diverge is usually a calibration problem worth fixing.

Can we add our own tables and views? +

Yes. It is your database, not a read-only export. Teams routinely add reference tables, materialised views for dashboards, and their own metric definitions. Sync only touches the tables it owns, so what you create beside them is untouched.

How do we reconcile a number against Wialon? +

Run the equivalent Wialon report for the same date range and compare. Differences almost always trace to timezone configuration or to comparing a level-sensor figure against a rates-based one — both visible in the columns rather than hidden behind a single total.

Is the schema stable across releases? +

Columns are added, not repurposed, so a query written today keeps returning what it returned. Anything that would change the meaning of an existing column ships as a new column instead, which is what makes it safe to build dashboards directly on these tables.

Worth reading

Why and how to extract your Wialon fleet data

Four reasons to get telematics data out of Wialon — and three ways to do it: by hand, a custom ETL pipeline, or FleetSQL straight to PostgreSQL.

Who drove it before the tracker went offline?

A Wialon integrator needed the last driver of every unit that stopped reporting. Three ways to get it: reports and Excel, FleetTAB, or SQL.

Contact

Interested in FleetSQL?

Why FleetSQL

Your Telematics data in a PostgreSQL database you control — queryable, joinable, and yours.