Incident Command System

Required in the U.S. for disaster response. This is a bit of brainstorming on what is needed to fit into ICS for use by Citizen Corps programs (CERT, Neighborhood Watch, etc.) Initial version not quite done... still thinking about best way to organize people, roles, incidents, schedules... NDA

Because one of the principles of ICS is that it can be used for all sorts of "incidents," including training, exercises, etc., this model treats everything it possibly can as an incident, using incident types to distinguish. Part of the intent is to encourage teams to use ICS regularly.

Components

• Location: address, coordinate, postal info
• Person: individual
• Role: hierarchy of structure and command
• Organization: companies, teams, agencies, etc., with inter-relationships
• Membership: relationship of people to organizations
• Incident: deployment, training, exercise, debriefing, etc.
• Credential: training, skill, certification, vaccinations, etc.
• Prerequisite: what credentials needed before a person can participate in an incident
• Event: what happened at a particular time and place; logging
• Equipment: personal and organizational gear
• Resource: supplier of equipment, skills, etc.
• Project: workflow for planning and management
• Email: contact info
• Phone: contact info
• Web page: associated with people, organizations, resources

Functions

• Add, modify, remove each kind of component
• Generate lists of each kind of component
• Print identification cards/badges
• Keep track of who is active, by neighborhood and city; ping inactive people
• Select individuals by various criteria, then send notifications
• Interface to calendar, recurring automatic emails for meeting notifications
• Issue training certificate
• Alerting - instant messaging, SMS, printed phone tree generation
• Export KML data for mapping, with symbols
• Dashboards for incidents, teams, resources, projects
• Training reports - who needs which courses, what expires soon?
• ICS/NIMS forms
• Access control
• Automatic data dump to forms suitable for laptops, mobile - cache critical data for when network is down.
• Data import
• Per-volunteer web pages (wiki) with controls on what to make public, links to social network sites
• MORE TBD

Table structures

person

person_id	INTEGER(8)	UNSIGNED	NOT NULL
person_title	VARCHAR(40)
first_name	VARCHAR(40)	NOT NULL
last_name	VARCHAR(40)	NOT NULL
nickname	VARCHAR(40)
photo	BLOB
physical_limitations	TEXT
comments	TEXT
gender	ENUM(m,f)
birthdate	DATE
status	ENUM('interested_volunteer,pending_volunteer,active_volunteer,inactive_volunteer,liason,contact')	NOT NULL
emergency_contact	INTEGER(8)	UNSIGNED
credential_id	INTEGER(8)	UNSIGNED
phone_id	INTEGER(8)	UNSIGNED
PRIMARY KEY	(person_id)
UNIQUE KEY	IDX_person1(person_id)
KEY	IDX_person2(person_id,credential_id)
KEY	IDX_person3(person_id,phone_id)

role

role_id	INTEGER(8)	UNSIGNED	NOT NULL
ics_level	ENUM('Command,Section,Division,Individual')
ics_role	ENUM('PIO,Safety,Operations,Planning,Logistics,Intelligence,Finance/Admin')
PRIMARY KEY	(role_id)
UNIQUE KEY	IDX_role1(role_id)

equipment

equipment_id	INTEGER(8)	UNSIGNED
equipment_name	VARCHAR(40)	NOT NULL
person_id	INTEGER(8)	UNSIGNED
PRIMARY KEY	(equipment_id)
KEY	IDX_equipment1(person_id,equipment_id)

equipment_type

equipment_type_id	INTEGER(8)	UNSIGNED	NOT NULL
equipment_type	VARCHAR(40)	NOT NULL
equipment_category	VARCHAR(40)	NOT NULL
PRIMARY KEY	(equipment_type_id)

COMMENT='Types include things like handie-talkie,first aid kit,boots,gloves. Categories include things like communications,PPE,medical,SAR.';

location

location_id	INTEGER(8)	UNSIGNED	NOT NULL
location_type	ENUM('home,work,incident,organization,company,division,shelter,camp,command post,resource')	NOT NULL
street_address	VARCHAR(100)
street_address2	VARCHAR(100)
city	VARCHAR(50)
county	VARCHAR(50)
postal_code	VARCHAR(40)
longitude	VARCHAR(40)
latitude	VARCHAR(40)
last_update	DATE
organization_id	INTEGER(8)	UNSIGNED
PRIMARY KEY	(location_id,location_type)
UNIQUE KEY	IDX_location1(location_id,location_type)
KEY	IDX_location2(organization_id,location_id)

person_location

person_id	INTEGER(8)	UNSIGNED	NOT NULL
location_id	INTEGER(8)	UNSIGNED	NOT NULL
location_type	ENUM('home,work,other')	NOT NULL
comments	TEXT
PRIMARY KEY	(person_id,location_id,location_type)
KEY	IDX_person_location1(location_id,location_type)
KEY	IDX_person_location2(person_id)

incident

incident_id	INTEGER(8)	UNSIGNED	NOT NULL
planned	BOOL
descriptiion	TEXT
comments	TEXT
activation_code	VARCHAR(10)
incident_primary_contact	INTEGER(8)	UNSIGNED
PRIMARY KEY	(incident_id)
UNIQUE KEY	IDX_incident1(incident_id)

COMMENT='planned events,training,emergencies,shelters,etc.';

tag

tag_id	INTEGER(8)	UNSIGNED	NOT NULL
description	TEXT
PRIMARY KEY	(tag_id)
UNIQUE KEY	IDX_tag1(tag_id)

COMMENT='Tags used in Twitter,etc.';

incident_tag

incident_id	INTEGER(8)	UNSIGNED	NOT NULL
tag_id	INTEGER(8)	UNSIGNED	NOT NULL
PRIMARY KEY	(incident_id,tag_id)
KEY	IDX_incident_tag1(incident_id)
KEY	IDX_incident_tag2(tag_id)

event

event_id	INTEGER(8)	UNSIGNED	NOT NULL
category_id	INTEGER(8)	UNSIGNED	NOT NULL
event_type_id	INTEGER(8)	UNSIGNED	NOT NULL
description	TEXT
PRIMARY KEY	(event_id,category_id,event_type_id)
KEY	IDX_event1(category_id)
KEY	IDX_event2(event_type_id)
UNIQUE KEY	IDX_event3(event_id,category_id,event_type_id)

COMMENT='Things that happen before,during,after incidents. Command change,assessment,assignment,relief,demobilize,rehab';

event_category

category_id	INTEGER(8)	UNSIGNED	NOT NULL
category_name	VARCHAR(40)
description	TEXT
PRIMARY KEY	(category_id)
UNIQUE KEY	IDX_Entity_11(category_name)
UNIQUE KEY	IDX_event_category2(category_id)

COMMENT='Categories refer to incident resources - people,communications,equipment,supplies.';

event_type

event_type_id	INTEGER(8)	UNSIGNED	NOT NULL
event_type_name	VARCHAR(40)
description	TEXT
PRIMARY KEY	(event_type_id)
UNIQUE KEY	IDX_event_type1(event_type_id)

COMMENT='Specific things that happen - assign people, make assessment, identify hazard,etc.';

incident_event

incident_event_id	INTEGER(8)	UNSIGNED	NOT NULL
incident_id	INTEGER(8)	UNSIGNED
event_id	INTEGER(8)	UNSIGNED
person_id	INTEGER(8)	UNSIGNED
role_id	INTEGER(8)	UNSIGNED
location_id	INTEGER(8)	UNSIGNED
resource_id	INTEGER(8)	UNSIGNED
location_type	ENUM('home,work,incident,organization,company,division,shelter,camp,command	post,resource')
category_id	INTEGER(8)	UNSIGNED
event_type_id	INTEGER(8)	UNSIGNED
starting_time	DATETIME	NOT NULL
authority	INTEGER(8)	UNSIGNED
communications_channel	INTEGER(8)	UNSIGNED
comm_channel_id	INTEGER(8)	UNSIGNED
tactical_name	VARCHAR(40)
ending_time	DATE
PRIMARY KEY	(incident_event_id)
UNIQUE KEY	IDX_incident_event1(incident_id,event_id)
KEY	IDX_incident_event2(person_id)
KEY	IDX_incident_event3(location_id,location_type)
KEY	IDX_incident_event4(event_id,category_id,event_type_id)
KEY	IDX_incident_event5(incident_id)
KEY	IDX_incident_event6(authority)
KEY	IDX_incident_event7(comm_channel_id)
KEY	IDX_incident_event8(role_id)

COMMENT='Logging';

communication_channel

comm_channel_id	INTEGER(8)	UNSIGNED	NOT NULL
channel_type	ENUM('HF,VHF,UHF,FRS')	NOT NULL
tone	ENUM('#1 67.0,#2 71.9,#3 74.4,#4 77.0,#5 79.7,#6 82.5,#7 85.4,#8 88.5,#9 91.5,#10 94.8,#11 97.4,#12 100.0,#13 103.5,#14 107.2,#15 110.9,#16 114.8,#17 118.8,#18 123.0,#19 127.3,#20 131.8,#21 136.5,#22 141.3,#23 146.2,#24 151.4,#25 156.7,#26 162.2,#27 167.9,#28 173.8,#29 179.9,#30 186.2,#31 192.8,#32 203.5,#33 210.7,#34 218.1,#35 225.7,#36 233.6,#37 241.8,#38 250.3)
repeater_id	VARCHAR(10)
repeater_offset	SMALLINT
PRIMARY KEY	(comm_channel_id)
UNIQUE KEY	IDX_communication_channel1(comm_channel_id))

COMMENT='Ham and FRS frequencies,PLs,etc.';

liaison

liaison_id	INTEGER(8)	UNSIGNED	NOT NULL
person_id	INTEGER(8)	UNSIGNED	NOT NULL
organization_id1	INTEGER(8)	UNSIGNED	NOT NULL
organization_id2	INTEGER(8)	UNSIGNED	NOT NULL
when_assigned	DATETIME	NOT NULL
when_relieved	DATETIME
PRIMARY KEY	(liaison_id)
UNIQUE KEY	IDX_Entity_11(person_id,organization_id1,organization_id2,when_assigned)
KEY	IDX_liaison2(person_id)
UNIQUE KEY	IDX_liaison3(organization_id1)
UNIQUE KEY	IDX_liaison4(organization_id2)

organization

organization_id	INTEGER(8)	UNSIGNED	NOT NULL
organization_name	VARCHAR(100)	NOT NULL
organization_phone	INTEGER(8)	UNSIGNED
organization_type	ENUM('business,foundation,elected government,government agency,utility,public works,school,medical care,NGO,law enforcement,fire,EMS,religious,search and rescue,hazmat,agricultural,food,building supplies,demolition,long-term recovery,child care,senior care,human services,logistics,communications,disabled care')
vulnerable	ENUM('no,elderly,child,infant,disabled')
donor	BOOL
parent_organization	INTEGER(8)	UNSIGNED
geographic_scope	ENUM('neighborhood,city,county,region,state,national,global')
geographic_scope_name	VARCHAR(40)
liaison_id	INTEGER(8)	UNSIGNED
page_id	VARCHAR(40)	NOT NULL
location_id	INTEGER(8)	UNSIGNED
phone_id	INTEGER(8)	UNSIGNED
PRIMARY KEY	(organization_id)
UNIQUE KEY	IDX_Entity_11(organization_name)
KEY	IDX_organization2(organization_id,page_id)
KEY	IDX_organization3(organization_id)
KEY	IDX_organization4(organization_id,location_id)
KEY	IDX_organization5(organization_id,phone_id)

person_language

person_id	INTEGER(8)	UNSIGNED	NOT NULL
language_id	INTEGER(8)	UNSIGNED	NOT NULL
fluency	ENUM('mininal,conversational,advanced,native/fluent')	NOT NULL
english_translator	BOOL
PRIMARY KEY	(person_id)
UNIQUE KEY	IDX_person_language1(person_id)

language

language_id	INTEGER(8)	UNSIGNED	NOT NULL
language_name	VARCHAR(50)	NOT NULL
person_id	INTEGER(8)	UNSIGNED
PRIMARY KEY	(language_id)
KEY	IDX_language1(person_id)

organization_page

organization_id	INTEGER(8)	UNSIGNED	NOT NULL
page_id	VARCHAR(40)	NOT NULL
PRIMARY KEY	(organization_id,page_id)
UNIQUE KEY	IDX_organization_page1(organization_id,page_id)

page

page_id	INTEGER(8)	UNSIGNED	NOT NULL
url	VARCHAR(255)
description	VARCHAR(100)
page_type	ENUM('Twitter,Facebook,YouTube,Wiki,Personal,Home')	NOT NULL
organization_id	INTEGER(8)	UNSIGNED
person_id	INTEGER(8)	UNSIGNED
PRIMARY KEY	(page_id)
KEY	IDX_page1(organization_id,page_id)
KEY	IDX_page2(person_id,page_id)

person_organization

person_id	INTEGER(8)	UNSIGNED	NOT NULL
organization_id	ENUM('PIO,Safety,Operations,Planning,Logistics,Intelligence,Finance/Admin')	NOT NULL
role_id	INTEGER(8)	UNSIGNED
active	BOOL
leader	BOOL
membership_started	DATE
membership_ends	DATE
PRIMARY KEY	(person_id,organization_id)
UNIQUE KEY	IDX_person_organization1(person_id)
UNIQUE KEY	IDX_person_organization2(organization_id)

person_page

person_id	INTEGER(8)	UNSIGNED	NOT NULL
page_id	INTEGER(8)	UNSIGNED	NOT NULL
PRIMARY KEY	(person_id,page_id)
UNIQUE KEY	IDX_person_page1(person_id)
UNIQUE KEY	IDX_person_page2(person_id,page_id)

person_equipment

person_id	INTEGER(8)	UNSIGNED	NOT NULL
equipment_id	INTEGER(8)	UNSIGNED	NOT NULL
needs	INTEGER	UNSIGNED	NOT NULL	DEFAULT	1
has	INTEGER	UNSIGNED	NOT NULL	DEFAULT	0
PRIMARY KEY	(person_id,equipment_id)
UNIQUE KEY	IDX_person_equipment1(person_id)
UNIQUE KEY	IDX_person_equipment2(person_id,equipment_id)

organization_location

organization_id	INTEGER(8)	UNSIGNED	NOT NULL
location_id	INTEGER(8)	UNSIGNED	NOT NULL
PRIMARY KEY	(organization_id,location_id)
UNIQUE KEY	IDX_organization_location1(organization_id,location_id)

phone

phone_id	INTEGER(8)	UNSIGNED	NOT NULL
area_code	INTEGER(4)	UNSIGNED	NOT NULL
country_code	INTEGER	UNSIGNED	NOT NULL
phone_number	INTEGER(8)	UNSIGNED	NOT NULL
extension	INTEGER(6)	UNSIGNED
service_provider	VARCHAR(40)
confidential	BOOL	NOT NULL	DEFAULT	False
primary	BOOL
24_7	BOOL
last_updated	DATE
person_id	INTEGER(8)	UNSIGNED
organization_id	INTEGER(8)	UNSIGNED
PRIMARY KEY	(phone_id)
KEY	IDX_phone1(person_id,phone_id)
KEY	IDX_phone2(organization_id,phone_id)

credential

credential_id	INTEGER(8)	UNSIGNED	NOT NULL
credential_name	VARCHAR(50)	NOT NULL
credential_description	TEXT	NOT NULL
credential_type	ENUM('communications,ICS,EMS,SAR,CERT,vaccination')	NOT NULL
person_id	INTEGER(8)	UNSIGNED
PRIMARY KEY	(credential_id)
KEY	IDX_credential1(person_id,credential_id)

person_credential

person_id	INTEGER(8)	UNSIGNED	NOT NULL
credential_id	INTEGER(8)	UNSIGNED	NOT NULL
issue_date	DATE	NOT NULL
expires	DATE
image	BLOB
verified_by	INTEGER(8)	UNSIGNED	NOT NULL
PRIMARY KEY	(person_id,credential_id)
UNIQUE KEY	IDX_person_credential1(person_id,credential_id)

person_phone

person_id	INTEGER(8)	UNSIGNED	NOT NULL
phone_id	INTEGER(8)	UNSIGNED	NOT NULL
phone_type	ENUM('home,work,organization,other')	NOT NULL
PRIMARY KEY	(person_id,phone_id)
UNIQUE KEY	IDX_person_phone1(person_id,phone_id)

organization_phone

organization_id	INTEGER(8)	UNSIGNED	NOT NULL
phone_id	INTEGER(8)	UNSIGNED	NOT NULL
phone_purpose	VARCHAR(75)
PRIMARY KEY	(organization_id,phone_id)
UNIQUE KEY	IDX_organization_phone1(organization_id,phone_id)