What lands in a PostgreSQL copy of your Wialon fleet

Anyone evaluating a pipeline out of Wialon eventually asks the same question, and it is the right one: what exactly ends up in the database? Not the marketing version — the table names, the grain of each row, and whether the question you need answered is answerable at all.

This is that answer. It is worth reading before you commit to anything, and worth ignoring entirely if you only want to know what the product does — that part is on how it works.

One table per data source, named after where it came from

There is no fixed schema handed to every customer. You pick data sources in the console, and each one gets its own table, created from the column list that source publishes. The prefix says what kind of data it is: w_prop_* for properties, w_evnt_* for events, w_rprt_* for Wialon report templates.

Table	Grain	What it holds
w_prop_unit	One row per unit	The vehicle reference — identity, hardware, fuel and trip settings
w_prop_unit_group	Group ↔ unit	Wialon group membership, for fleet-wide grouping
w_prop_sensors	Unit + sensor	Sensor definitions, including calibration tables
w_prop_custom_fields	Unit + field	Your own Wialon custom fields, queryable
w_prop_last_value	Unit + item	Last known mileage, engine hours, position and sensor values
w_evnt_trips	Unit + start epoch	Trips: start and end time and position, distance, speeds
w_evnt_speedings	Unit + start epoch	Speeding events with the limit that was exceeded
w_evnt_fuel	Unit + sensor + start	Fillings and drains, with the level around the event
w_evnt_ev_charge	Unit + sensor + start	EV charging events, in kWh
w_rprt_*	Whatever the report has	One table per applied table of a Wialon report template

Every database also carries _meta_data_sources: one row per table with its provider, category, grain, JSON column schema and last_synced_at. It is the first query worth running, because it answers “what is in here and how fresh is it” without anyone having to ask us.

SELECT target_table, provider, category, grain, last_synced_at
FROM   _meta_data_sources
ORDER  BY last_synced_at DESC NULLS LAST;

w_prop_unit — the table everything joins to

Unit properties as Wialon holds them, so a report can say “DAF XF, 2019, plate ABC-1234” instead of an opaque unit id. unit_id is the primary key here and a column on every event table, which makes it the join key you will write most often.

• unit_id, name — the join key and its label
• vin, registration_plate, brand, model, year, color
• vehicle_type, vehicle_class, primary_fuel_type — the grouping most comparisons need
• imei, hw_name, phone — the hardware behind the data
• mileage_km, engine_hours, last_message_time — for spotting units that went quiet
• trip_* and fuel_* — the Wialon settings that produced every derived number

That last group matters more than it looks. Wialon’s own calculation settings — trip detection thresholds, fuel calculation flags, consumption rates — travel with the vehicle row. When two numbers disagree, the configuration that produced them is sitting in the database next to them, which turns an argument into a lookup.

Events are stored as they happened

Movement lands event by event rather than pre-aggregated into a daily row, because the aggregate you need is rarely the one a vendor picked. A trip carries its start and end as both a timestamp and a unix epoch, the coordinates of both ends, distance in metres, average and maximum speed. A speeding event adds the limit that was exceeded, so “over the limit” is a fact in the row rather than an assumption in your query.

There is no local date column anywhere in the schema, and that is deliberate. Instants are stored in UTC; you derive the calendar day in whatever zone the question needs. A fleet crossing a timezone, or a report asked for in the customer’s zone rather than the depot’s, is then a change to the query instead of a re-extraction.

-- Distance and trips per vehicle per local day, last 30 days
SELECT u.name                                            AS unit,
       (t.start_time AT TIME ZONE 'Europe/Warsaw')::date AS day,
       COUNT(*)                                          AS trips,
       ROUND(SUM(t.distance_m) / 1000.0, 1)              AS km,
       MAX(t.max_speed)                                  AS max_kmh
FROM   w_evnt_trips t
JOIN   w_prop_unit  u USING (unit_id)
WHERE  t.start_time >= NOW() - INTERVAL '30 days'
GROUP  BY 1, 2
ORDER  BY day DESC, km DESC;

Fuel and charging keep the sensor, not just the vehicle

One row per filling or drain, keyed by unit, sensor and start time: filled and theft in litres, value for the fuel level around the event, raw_value for the uncalibrated reading, plus the coordinates. Keying on the sensor rather than the unit matters on trucks with two tanks, where a single per-vehicle number hides which side the loss came from.

Electric vehicles use the same shape in w_evnt_ev_charge: a charge column in kWh, the same sensor key, the same start and end envelope. Mixed fleets query both tables rather than a schema that pretends diesel and electric are the same event.

-- Drains over 20 litres in the last 90 days, with location
SELECT u.name        AS unit,
       f.start_time,
       f.theft       AS drained_liters,
       f.value       AS level_at_event,
       f.start_lat, f.start_lon
FROM   w_evnt_fuel f
JOIN   w_prop_unit u USING (unit_id)
WHERE  f.start_time >= NOW() - INTERVAL '90 days'
  AND  f.theft > 20
ORDER  BY f.theft DESC;

Your own report templates, as tables

The templates your team already maintains in Wialon can be extracted as they are: each applied table of a template becomes its own w_rprt_* table, with the columns that template produces. It is the shortest path from a report someone exports every Monday to the same numbers in SQL, without redefining the metric and then arguing about which version is right.

Reports are executed in batches of units per day rather than one call for the whole fleet and the whole period, because Wialon cancels long executions server-side — the same constraint covered in working within Wialon’s limits.

The part that is actually worth the migration

The database is yours, so nothing stops you creating tables next to these and joining them. Fuel card transactions beside w_evnt_fuel turn “the card was charged for 300 litres” into “and 240 reached the tank”. Customer contracts beside w_evnt_trips turn distance into cost per delivery. Maintenance records beside w_prop_unit.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 — FleetSQL versus Wialon reports sets out where that line falls.

Questions that come up before signing

Which tables will our database have? The ones for the data sources you enable, and no others. Enabling a source creates its table from a published column list; disabling it leaves the table and its rows alone.

Can we add our own tables and views? Yes. It is your database, not a read-only export. Sync only touches the tables it owns, so materialised views, reference tables and your own metric definitions sit beside them untouched.

How do we reconcile a number against Wialon? Run the equivalent Wialon report for the same 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.