Overview of the Developer Foundation Schema
Your Foundation server ships with our basic fleet tracking application built on Rails and is compatible with several different types of tracking devices (Enfora, Xirgo, Orbit One, CalAmp). This application sits on top of a MySQL database with a relatively simple schema. Click here or on the image below to enlarge it.
You can also view the schema.rb file, which is a textual representation of the database schema and a part of every Rails application. We’re including a sample schema.rb for the purpose of this article and you can find a more recent version located at /opt/ublip/rails/current/db/schema.rb on your Foundation server.
Now we’ll take a brief look at some of the schema tables and their purpose:
Accounts
Accounts represent the highest level entity within the system. Each account has a unique URL and is tied to the subdomain field. For example, your server is likely provisioned at a URL such as http://customer.domain.com. The “customer” part of the URL is the subdomain and is used to load the proper account. During development your server will only support one account (you can request more by contacting our support team), but once your application goes to production you can have an unlimited number of accounts.
Accounts do have a few required fields, which include subdomain, zip, and company. The enforcement of required fields is done through the Rails application (see /opt/ublip/rails/current/models/account.rb) and not the database. The is_verified field must be flagged as true for an account to be active in the system. Accounts can be easily created, modified, and deleted by using the admin web interface of the Rails application.
Users
Users are associated with accounts through the foreign key account_id in the users table. The required user fields are first_name, last_name, email, password (see /opt/ublip/rails/current/models/user.rb). Each account can have an unlimited number of users with different privileges. The is_super_admin privilege grants users access to the admin interface which allows a user to create/modify/delete an account/device/user as well as send remote configuration parameters to devices. In most cases this is an privilege that should be granted only to high-level internal users within your company.
User passwords are managed by the Rails application and are encrypted using a dynamically generated salt. You can always feel free to bypass this mechanism or use your own, if you’re working directly with the Foundation schema outside of the Rails application. Users can be easily created, modified, and deleted using the admin interface of the Rails application. In addition to this, a user can be easily moved from one account to another within the system.
Devices
Similar to users, devices are associated with accounts through the foreign key account_id in the devices table. There’s also another important association called group_id, which we’ll address in a few moments. The required device fields are name and imei (see {rails_app}/models/device.rb). There are a few fields in the devices table that are automatically updated from our device communications software. It is strongly recommended that you don’t modify any device records manually without a good understanding of how the devices table works. In most cases you should be able to easily create, modify, and delete devices using the admin interface of the Rails application.
Readings
Readings belong to devices. This association is done through the foreign key device_id in the readings table. In the case of our core GPS tracking application all readings have a location component to them. In future articles we’ll discuss applications that may consist of sensor readings with no location component. You’ll notice several fields in the readings table that correspond with GPS (latitude, longitude, altitude, speed, and direction).
An important field to consider is event_type. This field is used when generating different types of reports within the Rails application. Some different event types may include geofence, idle, engine on, engine off, and a few others. The address field is updated by a background process on your server called the Reverse Geocoder, which converts a raw latitude/longitude to a physical street address.
Device Profiles
Device profiles allow you to determine what information you want to display within the application. For example, a device attached to a vehicle may report locations, speeds, idles, and stops while a device attached to a generator may only report a location and runtime. These profiles could be created and named Fleet Basic and Mobile Generator. Device profiles are associated with devices via the profile_id field in the devices table.