Tracking Service Models
Database Tables
positions
GPS position data from devices.
| Column | Type | Nullable | Description |
|---|---|---|---|
| id | BIGINT | No | Primary key |
| deviceid | BIGINT | No | Reference to device |
| protocol | VARCHAR(128) | Yes | Protocol name (s21l, gt06, etc.) |
| servertime | DATETIME | Yes | When server received |
| devicetime | DATETIME | Yes | When device reported |
| fixtime | DATETIME | Yes | GPS fix time |
| valid | INTEGER | No | GPS fix validity (0/1) |
| latitude | DOUBLE | No | Latitude degrees |
| longitude | DOUBLE | No | Longitude degrees |
| altitude | DOUBLE | No | Altitude in meters |
| speed | DOUBLE | No | Speed in knots |
| course | DOUBLE | No | Heading in degrees |
| accuracy | DOUBLE | No | GPS accuracy |
| address | VARCHAR(512) | Yes | Reverse geocoded address |
| attributes | TEXT | Yes | JSON attributes |
| network | TEXT | Yes | JSON network info |
events
System events (overspeed, geofence, alarms, etc.).
| Column | Type | Nullable | Description |
|---|---|---|---|
| id | BIGINT | No | Primary key |
| type | VARCHAR(128) | No | Event type |
| eventtime | DATETIME | Yes | When event occurred |
| deviceid | BIGINT | No | Reference to device |
| positionid | BIGINT | Yes | Reference to position |
| geofenceid | BIGINT | Yes | Reference to geofence |
| maintenanceid | BIGINT | Yes | Reference to maintenance |
| attributes | TEXT | Yes | JSON attributes |
geofences
Geographic boundaries for monitoring.
| Column | Type | Nullable | Description |
|---|---|---|---|
| id | BIGINT | No | Primary key |
| name | VARCHAR(128) | No | Geofence name |
| description | VARCHAR(512) | Yes | Description |
| area | TEXT | Yes | WKT geometry |
| calendarid | BIGINT | Yes | Calendar reference |
| attributes | TEXT | Yes | JSON attributes |
user_geofence
User-Geofence permission links.
| Column | Type | Nullable | Description |
|---|---|---|---|
| userid | BIGINT | No | User ID (PK) |
| geofenceid | BIGINT | No | Geofence ID (PK) |
device_geofence
Device-Geofence links.
| Column | Type | Nullable | Description |
|---|---|---|---|
| deviceid | BIGINT | No | Device ID (PK) |
| geofenceid | BIGINT | No | Geofence ID (PK) |
user_device
User-device permission model with sharing support.
| Column | Type | Nullable | Description |
|---|---|---|---|
| userid | BIGINT | No | User ID (PK) |
| deviceid | BIGINT | No | Device ID (PK) |
| is_owner | INTEGER | No | 1=owner, 0=shared user |
| shared_by | BIGINT | Yes | User ID who shared this device |
| permission_position | INTEGER | No | Can view positions (default 1) |
| permission_events | INTEGER | No | Can view events (default 1) |
| permission_geofences | INTEGER | No | Can view geofences (default 1) |
| permission_notifications | INTEGER | No | Receives notifications (default 1) |
| permission_commands | INTEGER | No | Can send commands (default 0) |
| created_at | DATETIME | Yes | When access was granted |
Event Types
| Type | Description |
|---|---|
deviceOnline | Device came online |
deviceOffline | Device went offline |
deviceUnknown | Device status unknown |
deviceInactive | Device inactive for extended period |
deviceMoving | Device started moving |
deviceStopped | Device stopped |
deviceOverspeed | Device exceeded speed limit |
deviceFuelDrop | Fuel level dropped suddenly |
deviceFuelIncrease | Fuel level increased (refuel) |
geofenceEnter | Device entered geofence |
geofenceExit | Device exited geofence |
alarm | Device alarm triggered (see alarmType in attributes) |
ignitionOn | Ignition turned on |
ignitionOff | Ignition turned off |
maintenance | Maintenance reminder triggered |
textMessage | Text message received |
driverChanged | Driver ID changed |
commandResult | Command result received |
Alarm Types (in event attributes)
| Alarm | Description |
|---|---|
sos | SOS/Emergency button pressed |
vibration | Vibration detected |
movement | Movement detected |
overspeed | Overspeed alarm |
lowPower | Low external power |
lowBattery | Low battery |
powerOff | Power disconnected |
powerOn | Power connected |
gpsAntennaCut | GPS antenna cut/tampered |
accident | Accident detected |
tow | Vehicle towing detected |
hardAcceleration | Hard acceleration |
hardBraking | Hard braking |
hardCornering | Hard cornering |
jamming | GPS jamming detected |
tampering | Device tampering |
Geofence Area Formats (WKT)
Circle
CIRCLE (latitude longitude, radius)
latitude: Center latitude in degreeslongitude: Center longitude in degreesradius: Radius in meters
Example: CIRCLE (45.4654 9.1859, 100) - 100m radius circle
Polygon
POLYGON ((lat1 lon1, lat2 lon2, lat3 lon3, lat1 lon1))
- List of coordinate pairs
- First and last point must be the same (closed polygon)
- Minimum 4 points (triangle + closing point)
Example: POLYGON ((45.4654 9.1859, 45.4655 9.1860, 45.4656 9.1858, 45.4654 9.1859))
Linestring (Corridor)
LINESTRING (lat1 lon1, lat2 lon2, lat3 lon3)
- Used for route corridors
- Requires additional radius in attributes
Example: LINESTRING (45.4654 9.1859, 45.4700 9.1900, 45.4750 9.1920)
Position Attributes (Common)
| Attribute | Type | Description |
|---|---|---|
sat | int | Satellites in use |
hdop | float | Horizontal dilution of precision |
ignition | bool | Ignition state |
motion | bool | Motion state |
battery | float | Battery voltage |
batteryLevel | int | Battery percentage (0-100) |
fuel | float | Fuel level (liters) |
odometer | float | Odometer (meters) |
distance | float | Distance traveled (meters) |
totalDistance | float | Total distance (meters) |
hours | int | Engine hours (milliseconds) |
alarm | string | Alarm type if active |