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](../server/sql_schema/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_pass`es 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_pass`es 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_card`s and `user_pass`es 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 | Extra | |------------|-----------|------|-----|-------------------|-------| | `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_id`s to `group_name`s. --- `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 | Extra | |-----------|-----------|------|-----|---------|-------| | `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.