Technical Reference
Database
The database is designed to be a MySQL database hosted on AWS. A breakdown of th tables follows:
Base Tables
These tables are used throughout the app. They are cross functional with the main four areas (training, incident, equipment, personnel) of the platform.
Firefighter
Purpose: Stores the core personnel records for all firefighters within the department. Contains status (active/inactive), role, and other essential information.
Column Definitions:
id (INT, PK) – Unique identifier for each firefighter.
full_name (VARCHAR(511)) – The firefighter’s full name.
start_date (DATE) – The official start (hire) date.
trainer (BOOLEAN) – Indicates whether this firefighter can lead trainings.
officer (BOOLEAN) – Indicates whether this firefighter holds an officer rank.
active_status (BOOLEAN) – True if actively employed/engaged, false if deactivated or inactive.
company_id (INT, FK → company(id)) – Links the firefighter to a specific crew/company.
firefighter_role (VARCHAR(255)) – Descriptive role or title (e.g., Firefighter, Captain, etc.).
Deletion:
In practice, firefighters are not deleted but toggled inactive (active_status = false).
The FK to company uses ON DELETE SET NULL, so if a company is removed, the company_id here becomes NULL. They will be orphaned from a company.
Similarly, in chain of command, if a firefighter is removed, the FK to firefighter_id uses ON DELETE SET NULL. They will be orphaned in the chain of command.
Joins:
firefighter ↔︎ company on company_id (one company can have many firefighters).
Referenced by many tables via firefighter_id (e.g., chain_of_command, firefighter_contact, attendance, etc.).
Apparatus
Purpose: Represents a piece of firefighting apparatus (e.g., Engine 1, Ladder Truck). Tracks which vehicles/resources are active and available.
Column Definitions:
id (INT, PK) – Unique identifier for each apparatus.
apparatus_name (VARCHAR(255)) – Official or commonly used name/label.
apparatus_expire (DATE) – Date after which the apparatus is considered “retired” or otherwise not available for new assignments.
Deletion:
Typically not deleted if used historically. Instead, the apparatus_expire field indicates it is no longer active. List is managed in the settings tab.
Joins:
Referenced in tables such as equipment (equipment can be assigned to an apparatus), apparatus_response, and firefighter_apparatus.
Setting
Purpose: Stores configurable keys and values for various features or behaviors within the platform (system‐wide or departmental settings). See Setting Reference for more details.
Column Definitions:
id (INT, PK) – Unique identifier.
major_setting_key (VARCHAR(255)) – Broad category of the setting.
minor_setting_key (VARCHAR(255)) – Sub‐key or a more specific setting name.
setting_value (VARCHAR(255)) – The stored value.
Deletion:
Typically, you might delete old or unused settings. No special constraints beyond the primary key.
Joins:
Not usually joined to other tables. Accessed by the application to retrieve configuration.
Training Tables
These tables are used to track training sessions, attendance, and related details.
Training
Purpose: Defines a training session/event with a topic, time range, and related details.
Column Definitions:
id (INT, PK) – Unique identifier for the training event.
training_type (VARCHAR(255)) – Category or type of training (e.g., “Fire Behavior,” “Rescue Ops”).
topic (VARCHAR(255)) – Short description of the subject.
training_description (VARCHAR(1000)) – More detailed information about the training.
start_time (DATETIME) – Date/time the training is scheduled to begin.
end_time (DATETIME) – Date/time the training is scheduled to end.
credit_hours (FLOAT/DECIMAL) – Number of credited hours awarded. Defaults to the difference between start_time and end_time.
trainer (INT, FK → firefighter(id)) – The firefighter responsible for leading or coordinating the training.
training_expire (DATE) – Date this training was deleted and no longer included in any reporting.
Deletion:
Typically not deleted if historical records (attendance) reference it. Instead, might mark expired or keep for audit.
Joins:
training ↔︎ attendance on id → training_id (one training has many attendance records).
training ↔︎ firefighter on trainer → firefighter(id).
Attendance
Purpose: Tracks which firefighters attended each training session, plus check‐in/check‐out times.
Column Definitions:
id (INT, PK) – Unique attendance record identifier.
firefighter_id (INT, FK → firefighter(id)) – Id of Who attended.
training_id (INT, FK → training(id)) – Which training session.
check_in (DATETIME) – Actual check‐in time.(UTC)
check_out (DATETIME) – Actual check‐out time.(UTC)
auto_checkout (BOOLEAN) – Indicates if the system automatically handled check‐out.
credit (BOOLEAN) – Whether the firefighter receives credit for attending. This is assigned via stored proc in the database.
excused (BOOLEAN) – If the firefighter was excused from attending.
Deletion:
Typically only removed if the attendance was invalid or created by mistake. Usually retained for record keeping.
Joins:
attendance ↔︎ firefighter (many attendance records per firefighter).
attendance ↔︎ training (many attendance records per training).
Incident Tables
These tables are used to track incidents, responses, and related details.
Incident
Purpose: Top‐level record representing a call or event to which the department responds (fire, EMS, etc.).
Column Definitions:
id (INT, PK) – Unique identifier.
cad_identifier (VARCHAR(255)) – The identifier from the dispatch system.
incident_start (DATETIME) – When the incident began.
incident_end (DATETIME) – When the incident concluded or was cleared.
address (VARCHAR(255)) – Location of the incident.
dispatch_code (VARCHAR(255)) – Official code from the dispatch system.
area (VARCHAR(255)) – Specific category (mutual aid, primary area, municipality, etc.).
canceled (BOOLEAN) – True if the incident was canceled.
dropped (BOOLEAN) – True if the incident was dropped.
finalized (BOOLEAN) – True if the incident is closed and all records are finalized.
incident_expire (DATE) – Mark to exclude from reporting.
Deletion:
Usually never deleted; kept indefinitely for auditing. Instead, you will see incident_expire.
Joins:
incident ↔︎ response (one incident can have many responses).
incident ↔︎ incident_unit (tracks which type of response was needed).
Response
Purpose: Represents an actual response instance tied to an incident (e.g., engine 1 responded). Typically, most incidents will only have one response (a response can have multiple firefighters, apparatus, etc.) An example of a mulit-response incident would be a wildland fire where one crew responded for the first 12 hours, and a second crew took over for the next 12 hours.
Column Definitions:
id (INT, PK) – Unique identifier for each response.
incident_id (INT, FK → incident(id), ON DELETE RESTRICT, ON UPDATE CASCADE) – Which incident this response belongs to.
response_start (DATETIME) – When that response effort began.
response_end (DATETIME) – When that response ended.
notes (TEXT) – Field notes, relevant info about the response.
response_expire (DATE) – Exclude from reporting.
Deletion:
Generally retained for incident history. Expire to exclude. ON DELETE RESTRICT ensures the incident cannot be deleted if responses exist.
Joins:
response ↔︎ incident on incident_id.
response ↔︎ firefighter_response, apparatus_response, firefighter_apparatus.
Unit
Purpose: Stores types of units or categories (e.g., Fire, EMS, Wildland, etc.). Departments can customize.
Column Definitions:
id (INT, PK) – Unique identifier.
unit_type (VARCHAR(255)) – Name/category (e.g., “Fire”, “EMS”, etc.).
unit_expire (DATE) – When this unit type is no longer considered active for new usage.
Deletion:
Typically not removed; once expired, it should not be used for new incidents.
Joins:
unit ↔︎ incident_unit (many incidents can reference each unit type).
Incident Unit
Purpose: Links an incident to the type of unit (e.g., Fire, EMS, Rescue) that responded.
Column Definitions:
id (INT, PK) – Unique identifier.
incident_id (INT, FK → incident(id)) – The incident.
unit_type_id (INT, FK → unit(id)) – The type of unit that responded.
Deletion:
Typically retained for historical reference. ON DELETE RESTRICT means you cannot remove an incident or unit if references exist here.
Joins:
incident_unit ↔︎ incident.
incident_unit ↔︎ unit (one incident can have several types of units needed).
This application tracks unit type response separately from apparatus because, in many volunteer departments, apparatus are often used simply to transport personnel. In these cases, it’s important to record the type of response unit (e.g., EMS, fire, rescue) independently from the specific vehicle used.
Firefighter Response
Purpose: Associates individual firefighters with a particular response to track who participated and any time adjustments.
Column Definitions:
id (INT, PK) – Unique record for each firefighter/response combination.
response_id (INT, FK → response(id)) – Which response they participated in.
firefighter_id (INT, FK → firefighter(id)) – The firefighter involved.
time_adjustment (FLOAT/DECIMAL) – Additional time or offset for reporting hours.
Deletion:
No option for deletion. Edits will overwrite old records.
Joins:
firefighter_response ↔︎ firefighter.
firefighter_response ↔︎ response.
Apparatus Response
Purpose: Tracks which apparatus were assigned to a specific response.
Column Definitions:
id (INT, PK) – Unique identifier.
response_id (INT, FK → response(id)) – The response record.
apparatus_id (INT, FK → apparatus(id)) – The apparatus involved.
Deletion:
No option for deletion. Edits will overwrite old records.
Joins:
apparatus_response ↔︎ response.
apparatus_response ↔︎ apparatus.
Firefighter Apparatus
Purpose: Specifically ties a firefighter to an apparatus for the duration of a response (e.g., who was riding on or responsible for which vehicle).
Column Definitions:
id (INT, PK) – Unique identifier.
response_id (INT, FK → response(id)) – Which response.
firefighter_id (INT, FK → firefighter(id)) – The firefighter.
apparatus_id (INT, FK → apparatus(id)) – The apparatus.
Deletion:
No option for deletion. Edits will overwrite old records.
Joins:
firefighter_apparatus ↔︎ response.
firefighter_apparatus ↔︎ firefighter.
firefighter_apparatus ↔︎ apparatus.
Equipment Tables
These tables are used to track equipment, checks, and compliance.
Equipment Type
Purpose: Defines the category of equipment, plus checking and expiration intervals (e.g., SCBA, Monitor, Defibrillator).
Column Definitions:
id (INT, PK) – Unique identifier for the type.
equipment_type (VARCHAR(255)) – Descriptive name (e.g., “SCBA Battery”).
check_lead_time, check_lead_time_unit (INT, VARCHAR) – How far in advance of a check you want a warning and the time unit (days, weeks, etc.).
check_time, check_time_unit (INT, VARCHAR) – Frequency of required checks.
expire_lead_time, expire_lead_time_unit (INT, VARCHAR) – How far in advance of expiration you want a warning.
expire_time, expire_time_unit (INT, VARCHAR) – How long until the equipment of this type typically expires.
equipment_type_expire (DATE) – Date this type is no longer used.
Deletion:
Generally not physically removed once used in equipment; expired types remain in the system for historical data.
Joins:
equipment_type ↔︎ equipment.
Equipment
Purpose: An instance of a specific piece of equipment belonging to a type and assigned either to a firefighter or apparatus.
Column Definitions:
id (INT, PK) – Unique identifier for each piece of equipment.
equipment_name (VARCHAR(255)) – Human‐readable name/label.
equipment_type_id (INT, FK → equipment_type(id)) – What type it is.
firefighter_id (INT, FK → firefighter(id)) – If the equipment is assigned to a firefighter.
apparatus_id (INT, FK → apparatus(id)) – If the equipment is assigned to an apparatus.
next_check_date (DATE) – When the next required check is due.
expiration_date (DATE) – When this item expires.
snooze_expires (DATE) – If the check was “snoozed,” when that snooze period ends.
expire_equipment (DATE) – Date this equipment no longer shows up in reporting.
Deletion:
Typically retained for safety/audit; once “expired,” either have to replace and renew equipment, or, delete the equipment (through setting a date in the expire_equipment column).
Joins:
equipment ↔︎ equipment_type.
Potentially joined to firefighter or apparatus to show ownership/assignment.
equipment_log references specific equipment checks.
Equipment Log
Purpose: Logs each check performed on a piece of equipment, capturing who did it and when.
Column Definitions:
id (INT, PK) – Unique identifier for each log entry.
checked_by_id (INT, FK → firefighter(id)) – The firefighter who performed the check.
equipment_id (INT, FK → equipment(id)) – The item being checked.
check_date_time (DATETIME) – When the check occurred.
Deletion:
Typically never deleted unless a check entry was made in error, as it’s important for audit/compliance.
Joins:
equipment_log ↔︎ equipment.
equipment_log ↔︎ firefighter (who did the check).
Equipment Check Compliance
Purpose: Snapshots overall equipment‐check compliance, typically a percentage representing how many items are up‐to‐date on required checks.
Column Definitions:
id (INT, PK) – Unique identifier for each snapshot record.
compliance_date_time (DATETIME) – The timestamp when compliance was calculated.
check_compliance (FLOAT/DECIMAL) – The percentage of equipment that is not overdue for checks (0–100, or a fraction).
expire_compliance (FLOAT/DECIMAL) – The percentage of equipment that is not expired (0–100, or a fraction).
overall_compliance (FLOAT/DECIMAL) – The overall compliance percentage (0–100, or a fraction).
Deletion:
Generally retained for historical trend analysis.
Joins:
Usually stands alone, referenced by reporting features rather than a direct join to other tables.
Personnel Tables
These tables are used to track personnel, certifications, and related details. ___
Company
Purpose: Identifies which crew/company (e.g., “A‐Shift,” “Engine 3 Crew”) a firefighter belongs to.
Column Definitions:
id (INT, PK) – Unique identifier for the company.
company_name (VARCHAR(255)) – Descriptive name of the crew/company.
Deletion:
Can be removed, but if referenced by a firefighter (company_id), that field is set to NULL.
Joins:
company ↔︎ firefighter (one‐to‐many).
Certification Type
Purpose: Defines different possible certifications, along with renewal and lead‐time requirements.
Column Definitions:
id (INT, PK) – Unique ID for the certification type.
certification_name (VARCHAR(255)) – Name (e.g., “EMT‐Basic,” “Firefighter 1”).
lead_time, lead_time_unit (INT, VARCHAR) – Advance notice needed before initial certification expiration.
renew_time, renew_time_unit (INT, VARCHAR) – How often recertification is required.
certification_expire (DATE) – Date this certification type is no longer used.
Deletion:
Typically not deleted but expired, to maintain references in certification.
Joins:
certification_type ↔︎ certification.
Certification
Purpose: An actual certification instance held by a particular firefighter.
Column Definitions:
id (INT, PK) – Unique identifier for the certification record.
type_id (INT, FK → certification_type(id)) – Which certification type.
firefighter_id (INT, FK → firefighter(id)) – Who holds the certification.
expiration_date (DATE) – When the certification for this individual expires.
Deletion:
Can be deleted or renewed. No historical records are kept.
Joins:
certification ↔︎ certification_type.
certification ↔︎ firefighter.
Firefighter Status History
Purpose: Captures active/inactive date ranges for each firefighter to calculate total tenure or track leaves/retirements.
Column Definitions:
id (INT, PK) – Unique identifier for each status record.
firefighter_id (INT, FK → firefighter(id)) – Linked firefighter.
inactive_start_date (DATE) – Start of an inactive period.
inactive_end_date (DATE) – End of that inactive period (NULL if still inactive).
Deletion:
Typically not deleted; it’s a timeline record of a firefighter’s employment status.
Joins:
firefighter_status_history ↔︎ firefighter.
Chain of Command
Purpose: Defines the supervisory relationships: who reports to whom.
Column Definitions:
id (INT, PK) – Unique record.
firefighter_id (INT, FK → firefighter(id)) – The subordinate.
supervisor_id (INT, FK → firefighter(id)) – The supervisor.
Deletion:
If a supervisor is deleted (rare), references here become NULL if ON DELETE SET NULL.
Typically, changes are made by altering references or adding new rows.
Joins:
chain_of_command ↔︎ firefighter (both subordinate and supervisor are in the same table).
Firefighter Contact
Purpose: Stores contact details (address, phone, email) for each firefighter.
Column Definitions:
id (INT, PK) – Unique identifier.
firefighter_id (INT, FK → firefighter(id)) – Linked firefighter.
street_address_1, street_address_2 (VARCHAR(255)) – Mailing address lines.
city (VARCHAR(255)) – City.
state (CHAR(2)) – State/province (assuming US 2‐letter codes).
zip_code (VARCHAR(10)) – ZIP/Postal code.
phone_number (VARCHAR(15)) – Primary contact number.
email_address (VARCHAR(255)) – Primary email.
Deletion:
Possibly removed if the contact is outdated or if the firefighter is removed. Usually just updated.
Joins:
firefighter_contact ↔︎ firefighter (one‐to‐one or one‐to‐many if you allow multiple contacts per firefighter).