BI database
Postgres database
By default the TMF platform provides a PostgreSQL database that contains a subset of the database used by the platform. The platform database is a NoSQL database. The BI database is less privacy sensitive by aggregating certain data, although depending to which tables is granted access it's still possible to obtain a direct link to end-user data. The purpose of this database is to be able to dig into your data without interfering with the database used by the app and the rest of the platform. This postgres database is kept in sync automatically with the platform database. You can request credentials by contacting TMF support.
In what follows the different tables currently available in the BI database are explained. If for your analysis
Dashboards
TMF can setup dashboards based on the data in this database on request. You can contact TMF for a quote.
Superset
Through our partner Coop Devs a dashboard using Apache Superset can be made.
Grafana
TMF can setup a Grafana dashboard for you. Grafana dashboards are specifically made for time based data.
BI Database tables
Unique TMF IDs can be used to access the identity via the Control Center
groups
Table contains all groups that exist the platform database. Groups are used in several use cases to bundle a set of users
| Column name | Description | Data type |
|---|---|---|
| id | the unique TMF ID of this group in the platform | text |
| name | name of this group | text |
| ownergroup | ID of the administration this group belongs to | text |
| grouptype | type of group. Possible values: community, service, serviceLevel, organization | text |
| owner | ID of the user who owns this group. Only applicable for groups of type community | text |
communitymembers
Table contains data about all users that belong to a community, including some privacy sensitive information. Only end-user data that is available to the rest of the community members is also available in this table.
| Column name | Description | Data type |
|---|---|---|
| id | the unique TMF ID of this user in the platform | text |
| community | the unique TMF ID of the group representing this community in the platform | text |
| firstname | first name of the user | text |
| lastname | surname of the user | text |
| email of the user | text | |
| subscription | ID of the active subscription of the user for the service the community belongs to | text |
cars
Table contains all physical vehicles on the platform
| Column name | Description | Data type |
|---|---|---|
| id | the unique TMF ID of this vehicle in the platform | text |
| inuse | if this vehicle is currently in use or not | boolean |
| latitude | latitude of the last known position of the vehicle | double |
| longitude | longitude of the last known position of the vehicle | double |
| fuel_level | Last known % fuel level or State of Charge (if electric) of the vehicle. | double |
| charge_connected | if this electric vehicle is connected to a charging station or not (last knonwn value) | boolean |
| charge_inprogress | if this electric vehicle is charging or not (last knonwn value) | boolean |
| status | full JSON object of the last known status | json |
| licenseplate | license plate of this vehicle | text |
| model | name of the vehicle model | text |
| active_reservation | the unique TMF ID of the active reservation on this vehicle | text |
| ownergroup | ID of the administration this vehicle belongs to | text |
| energy_capacity | energy capacity of the vehicle model | double |
carconfigs
When a physical car is shared via the app one or more entries for that physical vehicle will exist in this table. Every entry represents an app listing in the carsharing app.
| Column name | Description | Data type |
|---|---|---|
| id | the unique TMF ID of this app listing in the platform | text |
| group | group in which this app listing is part of. This can be an organization, community or a fleet group | text |
| car | the unique TMF ID of the physical vehicle that is shared. Can be used to obtain the physical vehicle from the cars table | text |
| home | the unique TMF ID of the home zone. Can be used to obtain the home zone information from the places table | text |
| ownergroup | ID of the administration this app listing belongs to | text |
places
Table that contains all home zones added in the platform
| Column name | Description | Data type |
|---|---|---|
| id | the unique TMF ID of this place in the platform | text |
| name | name of this place | text |
| address | address of this place | text |
| geometry | WKT string that shapes this place | text |
| ownergroup | ID of the administration this place belongs to | text |
residencies
Table contains city, postalcode and country of all addresses of the users on the platform.
| Column name | Description | Data type |
|---|---|---|
| id | the unique TMF ID of the user | text |
| postalcode | postalcode | text |
| city | city | text |
| country | country code | text |
geocodedAddresses
Table contains latitude and longitude of the users' addresses geocoded
| Column name | Description | Data type |
|---|---|---|
| id | the unique TMF ID of the user | text |
| latitude | latitude | float |
| longitude | longitude | float |
cardiaries
Table contains the vehicle logbook entries for every physical vehicle on the platform.
reservations
Table contains all the reservations on the platform
persons
Table contains all groupmemberships of users
transactions
Table contains all transactions made on the platform. A transaction is the result of a billable action for which driving budget was added or subtracted.
| Column name | Description | Data type |
|---|---|---|
| billingAccount | the unique TMF ID of the billing account this transaction happened on | text |
| group | the unique TMF ID of the group this transaction has happened in | text |
| time | time of this transaction | timestamp with timezone |
| credits | amount of credits added during the transaction (if credits subtraced this will be negative) | double |
| creditsLeft | the balance of the billing account after the transaction | double |
| price | the monetary value of this transaction | double |
| product | reference to the subject of this transaction. Will start with 'reservationDetails' if it's about a reservation. Will start with 'orders' if it's about a purchase in the in-app shop | text |
tripinfo
Table contains all trips on the platform. This table is based on the transactions table.
usages
Alternative to tripinfo table. This table is based on the reservations table. Contains information about finished reservations.
usages_without_user_data
Privacy safe version of the usages table.
devices
Deprecated table.