Database Schema Overview

This document serves as a brief reference overview of the database.

Note that some of this documentation might be incorrect or out of date. If you see any problems, please feel free to open an issue or issue a pull request to correct the issue.

Please see the create_tables.sql file for the underlying SQL used to create the database tables.

Here is the full list of tables:

active_rider_table
admin_groups
admins
admins_session_info
archive_user_card
audit_admins
audit_user_card
audit_user_pass
audit_users
authorization_log
avls_data
billing_log
billing_log_annotation
billing_log_org
bus_checkin_log
diagnostic_log
drivers
groups
live_paddles
live_stops
old_paddles
old_stops
paddles
pass_option
price_point
ridership
rlapi_acos
rlapi_aros
rlapi_aros_acos
rule_class
rule_mappings
stops
org_api_log
org_api_password_reset
org_api_register_email
org_api_session
org_card_order_queue
org_default_card_value
org_default_pass_value
update_level
user_card
user_card_org
user_pass
user_pass_org
users

Here is a brief overview of what each table does and where it is used.

`active_rider_table`

Field	Type	Null	Key	Default	Extra
`logical_card_id`	`big int(20)`	`NO`	`MUL`	`NULL`
`seq_num`	`big int(20)`	`NO`	`PRI`	`NULL`	`auto_increment`
`rfid_token`	`varchar(32)`	`YES`		`NULL`
`mag_token`	`varchar(32)`	`YES`		`NULL`
`rule_name`	`varchar(24)`	`YES`		`NULL`
`rule_param`	`varchar(24)`	`YES`		`NULL`
`deleted`	`tiny int(1)`	`YES`		`0`
`parent_entity`	`varchar(32)`	`YES`		`NULL`
`notes`	`varchar(64)`	`YES`		`NULL`

Used as an append only table that implies a snapshot of which passes are associated to each card. Each bus reads the most recent entries in the active_rider_table to update its local database.

After a logical_card_id has an entry with the deleted field set, it should never appear in the table again.

Here is an example of a 'school' card that was added, updated then deleted:

`logical_card_id`	`seq_num`	`rfid_token`	`mag_token`	`rule_name`	`rule_param`	`deleted`	`parent_entity`	`notes`
`123`	`5001`	`NULL`	`2:54321`	`ORG-REJECT`	`NULL`	`0`	`NULL`	`NULL`
`123`	`6023`	`NULL`	`2:54321`	`SCHOOL-EVE`	`STUDENT`	`0`	`NULL`	`NULL`
`213`	`7011`	`NULL`	`NULL`	`NULL`	`NULL`	`1`	`NULL`	`NULL`

The mag_token is used here for testing purposes and is not real.

parent_entity is deprecated.

`user_card`

Field	Type	Null	Key	Default	Extra
`logical_card_id`	`big int(20)`	`NO`	`PRI`	`NULL`	`auto_increment`
`card_number`	`varchar(128)`	`YES`	UNI	`NULL`
`rfsite`	`int(127)`	`YES`		`NULL`
`rfid`	`int(127)`	`YES`		`NULL`
`mag_token`	`varchar(255)`	`YES`	`MUL`	`NULL`
`rfid_token`	`varchar(255)`	`YES`	`MUL`	`NULL`
`comment`	`varchar(255)`	`YES`		`NULL`
`userid`	`int(11)`	`YES`	`MUL`	`NULL`
`issued`	`datetime`	`YES`		`NULL`
`active`	`tiny int(1)`	`YES`	`MUL`	`1`
`deactivated`	`datetime`	`YES`		`NULL`
`lastused`	`datetime`	`YES`		`NULL`
`firstused`	`datetime`	`YES`		`NULL`
`group_id`	`int(11)`	`YES`		`NULL`
`issuetype`	`varchar(255)`	`YES`		`NULL`

The active field must be set to 1 for the card to be valid. We have a general policy entries should be deactivated rather than deleted.

user_card entries can have a users entry associated with them via a non null value of user_id. Multiple cards can be associated to a single user. Having differing group_id for users and user_card entries associated with each other is allowed.

All alterations to a user_card, including insertion, should insert a corresponding entry in audit_user_card.

`user_pass`

Field	Type	Null	Key	Default	Extra
`user_pass_id`	`big int(20)`	`NO`	`PRI`	`NULL`	`auto_increment`
`logical_card_id`	`big int(20)`	`YES`	`MUL`	`NULL`
`issued`	`datetime`	`YES`		`NULL`
`activated`	`datetime`	`YES`		`NULL`
`deactivated`	`datetime`	`YES`		`NULL`
`firstused`	`datetime`	`YES`		`NULL`
`lastused`	`datetime`	`YES`		`NULL`
`nrides_orig`	`int(11)`	`YES`		`NULL`
`nrides_remain`	`int(11)`	`YES`		`NULL`
`nday_orig`	`int(11)`	`YES`		`NULL`
`nday_expiration`	`datetime`	`YES`		`NULL`
`active`	`tiny int(1)`	`YES`		`0`
`expired`	`tiny int(1)`	`YES`		`0`
`rule`	`varchar(255)`	`YES`		`NULL`
`queue_order`	`int(11)`	`YES`		`NULL`
`comment`	`varchar(255)`	`YES`		`NULL`
`paytype`	`varchar(255)`	`YES`		`NULL`

logical_card_id is the id of the user_card entry. Multiple passes can be associated to a single user_card entry.

If the expired flag is set to 1, the entry should be ignored as it is no longer in use. For any group of user_passes whose expired flag is 0 (that is, unexpired) and that have the same logical_card_id as a parent, there should be only one active pass. It is an error if there are multiple user_passes that are not expired, are associated with the same logical_card_id and have the active flag set.

queue_order is the order in which a pass should be active should the previous entry in the queue_order be expired. Expired user_pass entries should ignore the queue_order field.

Having different group_ids for user_cards and user_passes associated with each other is allowed.

user_pass entries should never be deleted under normal circumstances. Instead, set the expired flag to 1.

`avls_data`

Field	Type	Null	Key	Default
`equip_num`	`int(11)`	`YES`		`NULL`
`driver`	`int(11)`	`YES`		`NULL`
`paddle`	`int(11)`	`YES`		`NULL`
`route`	`int(11)`	`YES`		`NULL`
`trip`	`int(11)`	`YES`		`NULL`
`stop`	`int(11)`	`YES`		`NULL`
`chirp_time`	`timestamp`	`NO`	`MUL`	`CURRENT_TIMESTAMP`
`latitude`	`double`	`YES`		`NULL`
`longitude`	`double`	`YES`		`NULL`
`heading`	`double`	`YES`		`NULL`
`velocity`	`double`	`YES`		`NULL`

Records the GPS position of buses. Should the buses GPRS connectivity be disconnected, the GPS position locations are stored locally and put into the diagnostic_log table.

`users`

Field	Type	Null	Key	Default	Extra
`username`	char(255)	`YES`	`MUL`	`NULL`
`userid`	`int(127)`	`NO`	`PRI`	`NULL`	`auto_increment`
`comment`	char(255)	`YES`		`NULL`
`first_name`	char(128)	`YES`		`NULL`
`last_name`	char(128)	`YES`		`NULL`
`phone`	char(31)	`YES`		`NULL`
`email`	char(128)	`YES`		`NULL`
`address`	char(255)	`YES`		`NULL`
`city`	char(127)	`YES`		`NULL`
`state`	char(127)	`YES`		`NULL`
`zip`	char(31)	`YES`		`NULL`
`created`	`datetime`	`YES`		`NULL`
`active`	`tiny int(1)`	`YES`		`NULL`
`passwordhash`	`varchar(255)`	`YES`		`NULL`
`shipping_address`	`varchar(255)`	`YES`		`NULL`
`shipping_city`	`varchar(255)`	`YES`		`NULL`
`shipping_state`	`varchar(255)`	`YES`		`NULL`
`shipping_zip`	`varchar(255)`	`YES`		`NULL`
`shipping_name`	`varchar(255)`	`YES`		`NULL`
`shipping_country_code`	`varchar(32)`	`YES`		`NULL`
`shipping_country_name`	`varchar(255)`	`YES`		`NULL`
`reset_attempts`	`int(11)`	`YES`		`NULL`
`group_id`	`int(11)`	`YES`		`NULL`

Holds user information. The ORG store front implicitly uses this data (through the Popufare API) to hold user information.

passwordhash is a SHA1 digest of the users username and password concatenated (in that order).

All updates to users, including new entries, should have a corresponding entry in audit_users.

`admins`

Field	Type	Null	Key	Default	Extra
`username`	char(255)	`YES`	`MUL`	`NULL`
`password`	char(255)	`YES`		`NULL`
`userid`	`int(127)`	`NO`	`PRI`	`NULL`	`auto_increment`
`active`	`tiny int(4)`	`YES`		`1`
`comment`	`varchar(255)`	`YES`		`NULL`
`group_id`	`int(11)`	`YES`		`NULL`

Administrator information for the admin web UI.

`groups`

Field	Type	Null	Key	Default	Extra
`group_id`	`int(11)`	`YES`		`NULL`
`group_name`	`varchar(255)`	`YES`		`NULL`

Maps group_ids to group_names.

`billing_log`

Field	Type	Null	Key	Default	Extra
`conf_checksum`	`varchar(32)`	`YES`		`NULL`
`equip_num`	`int(11)`	`YES`		`NULL`
`driver`	`int(11)`	`YES`		`NULL`
`paddle`	`int(11)`	`YES`		`NULL`
`route`	`int(11)`	`YES`		`NULL`
`trip`	`int(11)`	`YES`		`NULL`
`stop`	`int(11)`	`YES`		`NULL`
`ride_time`	`datetime`	`YES`	`MUL`	`NULL`
`latitude`	`double`	`YES`		`NULL`
`longitude`	`double`	`YES`		`NULL`
`action`	`varchar(16)`	`YES`		`NULL`
`rule`	`varchar(24)`	`YES`		`NULL`
`ruleparam`	`varchar(24)`	`YES`		`NULL`
`reason`	`varchar(64)`	`YES`		`NULL`
`credential`	`varchar(32)`	`YES`		`NULL`
`logical_card_id`	`big int(20)`	`NO`		`NULL`
`cash_value`	`int(11)`	`YES`		`NULL`
`stop_name`	`varchar(64)`	`YES`		`NULL`
`seq_num`	`big int(20)`	`NO`	`PRI`	`NULL`	`auto_increment`

`billing_log_annotation`

Field	Type	Null	Key	Default	Extra
`id`	`int(11)`	`NO`	`PRI`	`NULL`	`auto_increment`
`seq_num`	`int(11)`	`NO`	`MUL`	`NULL`
`equip_num`	`int(11)`	`YES`		`NULL`
`driver`	`int(11)`	`YES`		`NULL`
`paddle`	`int(11)`	`YES`		`NULL`
`route`	`int(11)`	`YES`		`NULL`
`trip`	`int(11)`	`YES`		`NULL`
`stop`	`int(11)`	`YES`		`NULL`
`ride_time`	`datetime`	`YES`		`NULL`
`latitude`	`double`	`YES`		`NULL`
`longitude`	`double`	`YES`		`NULL`
`action`	`varchar(16)`	`YES`		`NULL`
`rule`	`varchar(24)`	`YES`		`NULL`
`ruleparam`	`varchar(24)`	`YES`		`NULL`
`reason`	`varchar(64)`	`YES`		`NULL`
`credential`	`varchar(32)`	`YES`		`NULL`
`logical_card_id`	`big int(20)`	`YES`		`NULL`
`cash_value`	`int(11)`	`YES`		`NULL`
`stop_name`	`varchar(64)`	`YES`		`NULL`
`note`	`varchar(512)`	`YES`		`NULL`
`timestamp`	`timestamp`	`NO`		`CURRENT_TIMESTAMP`

All of the annotated fields in the billing_log_annotation view match the field names from the billing_log table (e.g. "route"), and orig is prepended to all of the original fields (e.g. orig_route), with one important exception. The annotated ride_time field is called annotated_ride_time, and the original ride_time field is simply ride_time.

`rule_class`

Field	Type	Null	Key	Default	Extra
`rulename`	`varchar(24)`	`YES`		`NULL`
`ruleclass`	`varchar(24)`	`YES`		`NULL`

Indicates what type a rulename is. ruleclass may only be NRIDE, NDAY or OTHER

`rule_mapping`

Field	Type	Null	Key	Default
`rule`	char(255)	`NO`	`PRI`
`rule_text`	char(255)	`YES`		`NULL`
`group_id`	`int(11)`	`YES`		`NULL`
`active`	`int(11)`	`YES`		`1`

Used in the Admin web UI and elsewhere to map rules to groups and give human readable descriptions.

Entries with the active set to 0 should be ignored.

`pass_option`

Field	Type	Null	Key	Default	Extra
`id`	`int(11)`	`NO`	`PRI`	`NULL`	`auto_increment`
`group_id`	`int(11)`	`YES`		`NULL`
`param`	`int(11)`	`YES`		`NULL`
`name`	`varchar(255)`	`YES`		`NULL`
`rule`	`varchar(255)`	`YES`		`NULL`
`db_rule`	`varchar(255)`	`YES`		`NULL`
`type`	`varchar(255)`	`YES`		`NULL`
`description`	`varchar(255)`	`YES`		`NULL`
`num_opt`	`int(11)`	`YES`		`0`
`option0`	`varchar(255)`	`YES`		`NULL`
`option1`	`varchar(255)`	`YES`		`NULL`
`option2`	`varchar(255)`	`YES`		`NULL`
`option3`	`varchar(255)`	`YES`		`NULL`
`active`	`tiny int(4)`	`YES`		`0`

Used in the Admin web UI to provide pass options for a given rule.

`price_point`

Field	Type	Null	Key	Default	Extra
`id`	`int(11)`	`NO`	`PRI`	`NULL`	`auto_increment`
`price`	`double`	`YES`		`NULL`
`param`	`int(11)`	`YES`		`NULL`
`name`	`varchar(255)`	`YES`		`NULL`
`rule`	`varchar(32)`	`YES`		`NULL`
`db_rule`	`varchar(32)`	`YES`		`NULL`
`group_id`	`int(11)`	`YES`		`NULL`
`type`	`varchar(255)`	`YES`		`NULL`
`description`	`varchar(1024)`	`YES`		`NULL`
`num_opt`	`int(11)`	`YES`		`0`
`price_option0`	`varchar(255)`	`YES`		`NULL`
`price_option1`	`varchar(255)`	`YES`		`NULL`
`price_option2`	`varchar(255)`	`YES`		`NULL`
`price_option3`	`varchar(255)`	`YES`		`NULL`
`active`	`tiny int(1)`	`YES`		`0`

Used in the store front web interface to display the appropriate options and prices for passes.

`org_default_card_value`

Field	Type	Null	Key	Default	Extra
`id`	`int(11)`	`NO`	`PRI`	`NULL`	`auto_increment`
`group_id`	`int(11)`	`YES`		`NULL`
`mag_track`	`int(11)`	`YES`		`NULL`
`rf_length`	`int(11)`	`YES`		`NULL`
`rf_site`	`int(11)`	`YES`		`NULL`

Default values for cards within a group.

`org_default_pass_value`

Field	Type	Null	Key	Default	Extra
`id`	`int(11)`	`NO`	`PRI`	`NULL`	`auto_increment`
`name`	`varchar(255)`	`YES`		`NULL`
`nday`	`int(11)`	`YES`		`NULL`
`nride`	`int(11)`	`YES`		`NULL`
`description`	`varchar(255)`	`YES`		`NULL`
`start`	`datetime`	`YES`		`NULL`
`end`	`datetime`	`YES`		`NULL`
`group_id`	`int(11)`	`YES`		`NULL`

Default options for NDay or NRide passes.

Used for access control rights for the Popufare API.

`update_level`

Field	Type	Null	Key	Default	Extra
`equip_num`	`int(11)`	`NO`	`MUL`	`0`
`client_file`	`varchar(32)`	`NO`	`MUL`	`NULL`
`checksum`	`varchar(32)`	`NO`		`NULL`
`file_size`	`int(11)`	`NO`		`NULL`
`file_path`	`varchar(512)`	`NO`		`NULL`
`fileversion`	`varchar(32)`	`YES`		`NULL`
`serial`	`big int(20)`	`NO`	`MUL`	`NULL`	`auto_increment`

Used by the version_server version daemon to determine which version of the config or firmware each bus in the fleet should receive. Using an equip_num of 0 is a wildcard for everything. Should the equip_num field be non-zero, it will be used instead.

For config or firmware deployment to a subset of buses in the fleet, a non-zero equipment number can be used to deploy to those specific buses. After full deployment, those entries should be removed and a default entry with an equip_num of 0 should be put in its place.

`diagnostic_log`

Field	Type	Null	Key	Default	Extra
`servertime`	`timestamp`	`NO`	`MUL`	`CURRENT_TIMESTAMP`	`on update CURRENT_TIMESTAMP`
`loglvl`	`varchar(8)`	`YES`		`NULL`
`message`	`varchar(256)`	`YES`		`NULL`

Buses can add to this table by sending diagnostic messages to the billing_server. The billing_server also updates this table should it find discrepancies when updating user_pass information.

These tables are used for the webui interface for paddle management.

Refer to the webui directory in subversion to see what each table does.

Database-Schema.md 23 KB Permalink History Raw

Database Schema Overview

active_rider_table

user_card

user_pass

avls_data

users

admins

groups

billing_log

billing_log_annotation

rule_class

rule_mapping

pass_option

price_point

org_default_card_value

org_default_pass_value

update_level

diagnostic_log

Database-Schema.md 23 KB

Permalink History Raw