Creative Commons Attribution-ShareAlike License All specifications for database designs and user experience (form tree map) are made available
by Flex Your Rights under the Creative Commons Attribution-ShareAlike License, 2020.

OpenPolice.org: Database Design Specs

complaints
(com_)

Complaints

Reports of dissatisfaction that contain one or more Allegations of Officer misconduct. Complaints are vital, because they provide the essential store of information for communicating what happened during a police encounter. Complaints data also helps the organization keep track of how various police accountability professionals are responding to Complaint Allegations.
18 Tables with Foreign Keys
Group: Complaint Data Tables
Type: Data
43 Fields Total
5 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

complaints Unique ID

com_id
Number, Indexed, Primary Key

User ID

^
Indicates the unique User ID number of the User owning the data stored in this record for this Experience.
com_user_id
Number

Experience Node Progress

^
Indicates the unique Node ID number of the last Experience Node loaded during this User's Experience.
com_submission_progress
Number

User ID

^
Indicates the unique User ID number of the User owning the data stored in this record for this Experience.
com_user_id
Number

Tree Version Number

^
Stores the current version number of this User Experience, important for tracking bugs.
com_tree_version
Text

OpenPolice.org Complaint ID#

Indicates the unique identification number for referring to this complaint in public and in URLs. Important for making public IDs counting only completed complaints, not every partial submission.
com_public_id
Number

OP.org Complaint Status

The current progress of a "complete" or "incomplete" complaint within the OP system. We use this information internally to determine next administrator actions to guide a complaint to the final status of "closed."
Notes: AKA Disposition of Complaint. see Work Flows.
Values: Complaint Status (Definitions)
com_status
Number Indexed

OP.org Complaint Type

The administrator-selected category for newly-submitted complaint records. Essential for determining where and how new complaint records are stored and shared.
Values: OPC Staff/Internal Complaint Type (Definitions)
com_type
Number Indexed

User ID

^
Indicates the unique user ID number of the user owning the data stored in this record for this experience.
com_user_id
Number
Users(0,1)-(0,N)

Incident ID

^
The unique number of the incident record related to this complaint. This number helps us identify the Incident that this complaint documents. It might also help us associate additional complaints with the same Incident.
Notes: More than one Complaint record can be associated with a single Incident record.
com_incident_id
Number
Incidents(1,1)-(1,N)

Scene ID

The unique number of the Scene record related to this Complaint. This number helps us identify the Scene that this Complaint documents.
com_scene_id
Number
Scenes(1,1)-(1,1)

Publish officers' names?

Indicates whether or not this complainant wants to publish the officers' identifying information on OpenPolice.org.
Values: 0,1
com_publish_officer_name
Number

Publish complainant's name?

Indicates whether or not this complainant wants to publish their name on OpenPolice.org.
Values: 0,1
com_publish_user_name
Number

Submitted anonymously?

Indicates whether or not this complainant wants to give their name and contact information to the police, oversight agencies, and OpenPolice.org itself.
Values: 0 ; 1
com_anon
Number

Privacy Setting

User-selected category for complaint records. This defines what personally identifiable information (PII) is publicly shared.
Notes: Should we move privacy settings at the end of the Complaint process for now (in the Simulation/Implementation)? (We can later test whether users prefer this selection to appear earlier or later in the process.) Wikipedia Article on the topic: https://en.wikipedia.org/wiki/Personally_identifiable_information
Values: Privacy Types (Definitions)
com_privacy
Number

Award Medallion

The current level of detail asked of complainant based on their selection. We use this information internally to determine what fields to show the complainant.
Notes: Defaults to Gold.
Values: Silver ; Gold
com_award_medallion
Text

Have these charges been resolved?

Indicates whether or not all criminal charges have been resolved. This is important for understanding the legal situation for any civilians involved and provides a safety check to minimize the potential risks of using OP.
Values: Y ; N ; ?
com_all_charges_resolved
Text

Anyone involved charged with a crime?

Indicates whether or not any civilians were charged with crime. This is important for understanding the incident's legal situation and as a safety check to minimize the potential risks of using OP.
Values: Y ; N ; ?
com_anyone_charged
Text

Wants an attorney?

Indicates whether or not the complainant would like an attorney. This is important for understanding the incident's legal situation and as a safety check to minimize potential risks of using OP.
Values: Y ; N ; ?
com_attorney_want
Text

Have an attorney?

Indicates whether or not the complainant is already represented by an attorney. This is important for understanding the incident's legal situation and as a safety check to minimize risks.
Notes: eg. http://www.aclupa.org/our-work/legal/fileacomplaint/electronic-complaint-form/
Values: Y ; N ; ?
com_attorney_has
Text

Attorney OK'd publishing on OpenPolice.org?

Indicates whether or not the complainant's attorney explicitly approved their use of OpenPolice.org. This is an important safety check to minimize potential risks of using OP.
Values: Y ; N ; ?
com_attorney_oked
Text

Unresolved charges, but use OP.org anyway?

Indicates whether or not the complainant has explicitly chosen to use OP despite legal situations related to the Incident. This is important for understanding the incident's legal situation and as a safety check to minimize potential risks of using OP.
Values: Unresolved Charges Actions (Definitions) ( default value: 0 )
com_unresolved_charges_actions
Number

Someong filing lawsuit?

Indicates whether or not someone involved in this incident is thinking about filing a lawsuit against the police. If they respond yes, this will place complainants in the lawyer track.
Values: Y ; N ; ?
com_file_lawsuit
Text

List of Allegations

Caches a simple written list of this complaint's allegations, separated by commas.
com_alleg_list
Text-Long

Summary (Narrative)

The complainant narrative describes the chronological sequence of key Incident events and allegations. This story brings to life to an otherwise clinical and legalistic complaint document.
com_summary
Text-Long

Any officers injured?

Indicates whether or not any officer was also injured during a specific use of force. This is important for tracking an acknowledging the fact that there is more than one side to every story.
Values: Y ; N ; ?
com_officer_injured
Text

Officer Injury Descriptions

Further describes officers' injuries incurred during a specific use of force. This is important for tracking an acknowledging the fact that there is more than one side to every story.
com_officer_injured_desc
Text

Previously attempted to file?

Indicates whether or not the complainant previously attempted to formally submit their police complaint before using OP. This is important for research about the use of OP.
Values: Y ; N ; ?
com_tried_other_ways
Text

What else have you tried?

The complainant can describe how else they previously attempted to formally submit their police complaint before using OP. This is important for research about the use of OP.
com_tried_other_ways_desc
Text-Long

How did you hear about us?

Indicates how the complainant heard about OpenPolice.org. This is important for internal understanding of OP marketing strategies.
com_how_hear
Text

Got suggestions or feedback for us?

Indicates any suggestions or feedback the user has for us at the end of the process. This is important to let us know if we missed anything, etc.
com_feedback
Text-Long

Share with trusted other databases?

Indicates whether or not the complainant consents to share their complaint data with other databases trusted by OP.
Values: 0 ; 1
com_share_data
Number

Officer disciplined?

Indicates whether or not any subject officers associated with a complaint have received any formal punishment for their actions. Important for for statistical purposes and for bringing sense of justice and closure to civilians.
Notes: The likelihood of an individual complaint allegation being sustained is very low. However, the likelihood of obtaining officer discipline data relating to a specific sustained complaint is extremely remote.
Values: Y ; N ; ?
com_officer_disciplined
Text

Officer Discipline Type

Indicates the category of formal punishment any subject officers associated with a complaint have received. Important for statistical purposes and for bringing sense of justice and closure to civilians.
Values: Officer Discipline Types (Definitions)
com_officer_discipline_type
Number

Media Links

The URL address of a news report containing information related to this complaint. Important for tracking and verifying new information related to a complaint's allegations.
Notes: Proper news story selection criteria here: http://www.policemisconduct.net/about/news-feed-faq/ Must be careful not to let people post links to their personal blogs, because that won't include objective information.
com_media_links
Text-Long

Primary Admin ID

^
The unique number of the administrator record related to this complaint. This number helps us identify which Administrator is the point of contact for this complaint.
Notes: OPC staff in charge of Complaint
com_admin_id
Number Indexed
Administrators(0,11)-(0,N)

Requested Partner ID

^
Indicates the unique partner ID number of the person or organization requested by this complainant.
com_att_id
Number
Partners(0,11)-(0,N)

Complaint Notes

Additional annotations related to this complaint. Might add additional information or context regarding the complaint.
Notes: Changed to "Complaint Notes"
com_notes
Text-Long

URL Slug

This defines the version of the complaint's Social Media Headline which is compatible and ideal to be used as part of a website URL. This is vital for creating public pages of the website which are intuitive and professional.
com_slug
Text

Submitted to OpenPolice.org

Date and time when this complaint was completed and submitted to the OP database.
com_record_submitted
Date&Time Indexed

Submission Progress

Indicates current progress of an "incomplete" complaint. Important for identifying problem areas which might cause complaints to be left unfinished.
Notes: TBD: [Shorthand] notation of where in the complaint submission process. Probably both an overall section identifier (like Turbo Tax), and sub-section identifier.
com_submission_progress
Text

A/B Testing Version

^
Stores a complex string reflecting all A/B Testing variations in effect at the time of this user's experience of this node.
com_version_ab
Text

Tree Version

Indicates which precise version number of this software was running at the time of this complaint's submission. This is important for internal use, quality control, and potential debugging.
com_tree_version
Text

Honey Pot (to catch spam bots)

Indicates whether or not a spam bot filled in the form field which human users cannot see. This is important for quickly categorizing complaints as spam.
com_honey_pot
Text

Using Mobile Device

Indicates whether or not this complaint was started on a mobile device. This is important for tracking trends of usage, and potential debugging.
Values: 0 ; 1
com_is_mobile
Number

Unique String

This unique, randomly generated string can be important for creating custom URLs which are more secure than using the Complaint ID#.
com_unique_str
Text

IP Address (encrypted)

This logs an encrypted copy of the complainants IP Address. This is important for checking if multiple complaints were submitted from the same location, especially when filtering complaints categorized as spam or abuse.
com_ip_addy
Text

Is Demo/Test Run

Indicates whether or not this user started the complaint process just to demo it.
Values: 0 ; 1 ( default value: 0 )
com_is_demo
Number
incidents
(inc_)

Incidents

Represent individual, uninterrupted police events occurring at a specific time and place. Incident information is vital for identifying and associating data from multiple Complaints with each other.
2 Tables with Foreign Keys
Group: Complaint Data Tables
Type: Data
14 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

incidents Unique ID

inc_id
Number, Indexed, Primary Key

Complaint ID

^
The unique number of the complaint record related to this Incident, vital to associating with all other complaint data.
inc_complaint_id
Number Indexed
Complaints(11,N)-(11,11)

Street Address

^
The first line of the postal location at or near where an incident occurred. This is an integral component of a complete incident location address.
Notes: Could we indicate highway mile markers or exits in the address field -- or should that be part of a separate field? Incident address locations should only include locations within the 50 U.S. states and the District of Columbia.
inc_address
Text

Street Address 2

^
The second line, if needed of the postal location at or near where an incident occurred. This is an integral component of a complete incident location address.
inc_address2
Text

City Borough

Indicates the borough within the city where the incident took place. Important for managing large cities like NYC.
Values: NYC Boroughs (Definitions)
inc_borough
Number

City

^
The metropolitan area where an incident occurred. This is an integral component of a complete Incident location address.
inc_address_city
Text

State

^
The state or district where an incident occurred. This is an integral component of a complete incident location address.
Notes: Incident locations should only include locations within the 50 U.S. states and the District of Columbia.
inc_address_state
Text

Zip Code

^
The postal code where an incident occurred. This is an integral component of a complete incident location address.
inc_address_zip
Text

Geolocation Latitude

The geographic coordinate that specifies the north-south position of the point on the Earth's surface where an incident occurred. This information might help identify police departments whose jurisdictions cover the coordinates where an incident occurred.
inc_address_lat
Number-Decimals

Geolocation Longitude

The geographic coordinate that specifies the east-west position of the point on the Earth's surface where an incident occurred. This information might help identify police departments whose jurisdictions cover the coordinates where an Incident occurred.
inc_address_lng
Number-Decimals

Nearby Landmarks

An easily-recognized place, object, or feature that can help establish a precise Incident location. Useful for identifying locations where an address alone won't suffice.
Notes: e.g. near a memorial inside a large park.
inc_landmarks
Text

Start Time

^
The date, hour, and minute when a single incident begins. This information is vital for verifying an incident or merging incident data from other sources.
Notes: Morgan: We might need a Business rule indicating that the Incident "Start Time" and "End Time" can't be more than 24 hours apart. Specifically, we don't want Complainants reporting allegations from multiple Incidents lumping them into a months or years-long Incident timeframe.
inc_time_start
Date&Time

End Time

^
The date, hour, and minute when a single incident ends. This information is vital for verifying an incident occurrence or merging incident data from other sources.
inc_time_end
Date&Time

Duration

^
The total calculated minutes of of a single incident. This information is vital for verifying an incident occurrence or merging incident data from other sources.
Notes: calculated based on Incident Start Time and Incident End Time.
inc_duration
Number

Publicly mapped location?

Indicates whether or not the complainant wants the address of this incident made public for mapping and research purposes.
Values: 0 ; 1
inc_public
Number
scenes
(scn_)

Scenes

Scene information provides descriptive details about the setting where an Incident occurred. Beyond mere time and location, this adds an important contextual backdrop behind specific Incident events. Scene information also helps provide a more objective evaluation of the Incident and Allegations.
2 Tables with Foreign Keys
Group: Complaint Data Tables
Type: Data
15 Fields Total
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

scenes Unique ID

scn_id
Number, Indexed, Primary Key

Incident began with vehicle stop?

Whether or not an incident began with a vehicle, which includes cars, motorcycles, bicycles, and boats. This provides important contextual information about the the nature of the Incident to better evaluate the incident and allegations.
Values: Y ; N ; ?
scn_is_vehicle
Text

Scene Type

Category of possible settings where an incident occurred. This selection provides important contextual information about the location of the incident, which helps us better evaluate the incident and allegations.
Notes: Which of the following best describes the scene of the incident? If the incident includes multiple locations, pick the one where you first witnessed police contact.
Values: Scene Type (Definitions)
scn_type
Number

Description

Narrative details about the setting where an incident occurred. Beyond mere time and location, scene information adds an important contextual backdrop behind specific incident events.
scn_description
Text-Long

Incident began with forcible entry?

Indicates that the incident involved the use of a forcible entry, SWAT raids, or drug task force. Important for evaluating SWAT incidents and trends.
Notes: This should only appear as an option if users selects from Home or Private Residence; Workplace; College Dorm from Scene Type options.
Values: Y ; N ; ?
scn_forcible_entry
Text

Closed circuit cameras on scene?

Indicates that there was possible video surveillance of the scene. Important for obtaining new evidence of the incident.
Notes: Go back to the scene to check for CCTV cameras to chase down potential video Evidence. If selected, we might want to email user article on How to Track Down CCTV Footage, or something like that.
Values: Y ; N ; ?
scn_cctv
Text

Closed Circuit Camera Descriptions

Narrative details about the type and location of any visible video surveillance cameras. Might help investigative agencies or attorneys track down new evidence of the incident.
scn_cctv_desc
Text-Long

Incident began with traffic accident?

Whether or not an incident began with a vehicle accident. This provides important contextual information about the the nature of the incident to better evaluate the incident and allegations.
Values: Y ; N ; ?
scn_is_vehicle_accident
Text

How did this make you feel?

This indicates how the complainant feels about this police incident.
Values: How did this make you feel (Definitions)
scn_how_feel
Number

Desires for Officer(s) Involved

Indicates the complainant's desires for the officer(s) involved. Important for comparing with resulting behavior.
Values: Desires for Officers (Definitions)
scn_desires_officers
Number

Other Desires for Officer(s) Involved

Indicates the complainant's other desires for the officer(s) involved. Important for tracking desires beyond out provided list.
scn_desires_officers_other
Text

Desires for Department(s) Involved

Indicates the complainant's desires for the department(s) involved. Important for comparing with resulting policies and behavior.
Values: Desires for Departments (Definitions)
scn_desires_depts
Number

Other Desires for Department(s) Involved

Indicates the complainant's other desires for the department(s) involved. Important for tracking desires beyond out provided list.
scn_desires_depts_other
Text

Attorney First Name

Indicates the first name of the attorney of the complainant or victim, if applicable.
scn_attorney_first_name
Text

Attorney Last Name

Indicates the last name of the attorney of the complainant or victim, if applicable.
scn_attorney_last_name
Text

Attorney Email

Indicates the email address of the attorney of the complainant or victim, if applicable.
scn_attorney_email
Text
alleg_silver
(alle_sil_)

Silver Allegations

Tracks the yes/no responses to Silver-Level Allegations against Officers, vital to identifying the most serious Complaints. This information also helps OpenPolice.org provide useful next-steps advice for Complainants who generate Complaints.
Group: Complaint Data Tables
Type: Data
31 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

alleg_silver Unique ID

alle_sil_id
Number, Indexed, Primary Key

Complaint ID

The unique number of the complaint record related to this record of allegation-related responses, vital to associating with all other complaint data.
alle_sil_complaint_id
Number
Complaints(11,11)-(11,11)

Officer stop, detain, or questioned someone?

Indicates whether or not an officer detained anyone during the Incident. This is important for later reloading complaint forms for silver allegations with the user's previous response.
Values: Y ; N ; ?
alle_sil_stop_yn
Text

Think stop or detention wrongful?

Indicates whether or not the complainant thinks an officer wrongfully stopped someone. Such allegation claims are central to complaints.
Values: Y ; N ; ?
alle_sil_stop_wrongful
Text

Anyone ask for officer's ID?

Indicates whether or not anybody asked for any officer's identification. Storing this is important in determining whether or not to display the question regarding an officer's refusal.
Values: Y ; N ; ?
alle_sil_officer_id
Text

Officer refused to provide ID?

Indicates whether or not the complainant thinks an officer refused to provide identification when requested by a civilian. Such allegation claims are central to complaints.
Values: Y ; N ; ?
alle_sil_officer_refuse_id
Text

Officer searched someone or something?

Indicates whether or not an officer searched anyone during the incident. This is important for later reloading complaint forms for silver allegations with the user's previous response.
Values: Y ; N ; ?
alle_sil_search_yn
Text

Think search was wrongful?

Indicates whether or not the complainant thinks an officer wrongfully searched someone. Such allegation claims are central to complaints.
Values: Y ; N ; ?
alle_sil_search_wrongful
Text

Officer used physical force?

Indicates whether or not an officer used physical force against anyone during the incident. This is important for later reloading complaint forms for silver allegations with the user's previous response.
Values: Y ; N ; ?
alle_sil_force_yn
Text

Think physical force unreasonable?

Indicates whether or not the complainant thinks an officer used unreasonable force. Such allegation claims are central to complaints.
Values: Y ; N ; ?
alle_sil_force_unreason
Text

Property seized or damaged?

Indicates whether or not an officer seized or damaged any property during the incident. This is important for later reloading complaint forms for silver allegations with the user's previous response.
Values: Y ; N ; ?
alle_sil_property_yn
Text

Think property wrongfully seized?

Indicates whether or not the complainant thinks an officer wrongfully seized someone's property. Such allegation claims are central to complaints.
Values: Y ; N ; ?
alle_sil_property_wrongful
Text

Officer arrested someone?

Indicates whether or not an officer arrested anyone during the Incident. This is important for later reloading complaint forms for silver allegations with the user's previous response.
Values: Y ; N ; ?
alle_sil_arrest_yn
Text

Think property damage wrongful?

Indicates whether or not the complainant thinks an officer wrongfully destroyed someone's property. Such allegation claims are central to complaints.
Values: Y ; N ; ?
alle_sil_property_damage
Text

Think arrest was wrongful?

Indicates whether or not the complainant thinks an officer made a wrongful arrest. Such allegation claims are central to complaints.
Values: Y ; N ; ?
alle_sil_arrest_wrongful
Text

Think arrest charges were retaliation?

Indicates whether or not the complainant thinks believes an officer issued retaliatory charges. against at least one civilian. Such allegation claims are central to complaints.
Values: Y ; N ; ?
alle_sil_arrest_retaliatory
Text

Officer fail to read Miranda Rights?

Indicates whether or not the complainant believes there is an allegation regarding a failure to read miranda rights during at least one arrest. Such allegation claims are central to complaints.
Values: Y ; N ; ?
alle_sil_arrest_miranda
Text

Officer give ticket or citation?

Indicates whether or not an officer cited anyone during the Incident. This is important for later reloading complaint forms for silver allegations with the user's previous response.
Values: Y ; N ; ?
alle_sil_citation_yn
Text

Think citation charges were excessive?

Indicates whether or not the complainant thinks an officer issued retaliatory citation charges. Such allegation claims are central to complaints.
Values: Y ; N ; ?
alle_sil_citation_excessive
Text

Think officer violated policy or procedure?

This indicates whether or not the complainant believes that an officer's actions did not follow the appropriate policy or procedure. Such allegation claims are central to complaints.
Values: Y ; N ; ?
alle_sil_procedure
Text

Think officer neglected duty?

This indicates whether or not the complainant believes that an officer's inactions did not follow the appropriate policy or procedure. Such allegation claims are central to complaints.
Values: Y ; N ; ?
alle_sil_neglect_duty
Text

Think officer actions were biased?

This indicates whether or not the complainant thinks that is an officer behaved in a biased manner. Such allegation claims are central to complaints.
Values: Y ; N ; ?
alle_sil_bias
Text

Think officer sexually harassed someone?

Indicates whether or not the complainant thinks an officer sexually harassed someone during the incident. Such allegation claims are central to complaints.
Values: Y ; N ; ?
alle_sil_sexual_harass
Text

Think officer sexually assaulted someone?

Indicates whether or not the complainant thinks an officer sexually assaulted someone during the incident. Such allegation claims are central to complaints.
Values: Y ; N ; ?
alle_sil_sexual_assault
Text

Think officer wrongfully intimidated w/ weapon display?

Indicates whether or not the complainant thinks an officer brandished or discharged a weapon in a threatening manner. Important for identifying non-force complaints where an officer might have used a weapon for intimidation purposes.
Notes: IF this Complaint includes a Use of Force Allegation, THEN the Complainant should NOT be offered this Weapon Intimidation Allegation. Weapon-related threats do not apply to weapons safely in holsters. Actual use of weapon AGAINST a person is use of Force, not just Intimidation.
Values: Intimidating Displays Of Weapon (Definitions)
alle_sil_intimidating_weapon
Number

What weapon was wrongfully displayed?

The category of weapon the officer used in a threatening manner. Important for identifying whether an officer might have violated department policies or procedures.
Notes: Range of Values is a subset of Force Type, which can be used for intimidation. Hide Force Type Definitions: Control Hold, Body Weapons, Takedown, Vehicle
Values: Force Type (Definitions)
alle_sil_intimidating_weapon_type
Number

Officer enter private space without warrant?

Indicates whether or not an officer entered a space without a warrant. This is important for determining the details of justified entry on private property.
Values: Y ; N ; ?
alle_sil_wrongful_entry
Text

Incident part of repeated or continued contact with the same officer?

Indicates whether or not this incident part of repeated or continued contact with the same officer. This is important to determine if they should be asked about ongoing/continued harassment.
Values: Y ; N ; ?
alle_sil_repeat_contact
Text

Incident is part of repeated harassment?

This indicates whether or not the complainant thinks an officer continued to contact someone without lawful justification.
Values: Y ; N ; ?
alle_sil_repeat_harass
Text

Think officer conduct improper or unprofessional?

Indicates whether or not the complainant believes there is an allegation regarding conduct unbecoming of an officer. Such allegation claims are central to complaints.
Values: Y ; N ; ?
alle_sil_unbecoming
Text

Think officer was discourteous?

Indicates whether or not the complainant believes there is an allegation regarding at least one officer's discourtesy. Such allegation claims are central to complaints.
Values: Y ; N ; ?
alle_sil_discourteous
Text

Force used against animal?

Indicates whether or not force was used against an animal.
Values: Y ; N ; ?
alle_sil_animal_force
Text
allegations
(alle_)

Allegations

Represent specific misconduct accusations against Officers, vital to identifying the most serious Complaints. This information also helps OpenPolice.org provide useful next-steps advice for Complainants who generate Complaints. This table provides storage for the descriptions of why each Allegation has been made, and a record to link with Officers and/or Victims.
2 Tables with Foreign Keys
Group: Complaint Data Tables
Type: Data
5 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

allegations Unique ID

alle_id
Number, Indexed, Primary Key

Complaint ID

^
The unique number of the complaint record related to this allegation, vital to associating with all other complaint data.
alle_complaint_id
Number Indexed
Complaints(11,11)-(0,N)

Allegation Type

The specific misconduct accusations cited by complainants against one or more officers related to a single Incident. Essential for statistical purposes and for prioritizing new complaints.
Notes: Asked at the end during Phase 4: Policy or Procedure, Courtesy, Intimidating Display Of Weapon, Sexual Assault, Conduct Unbecoming an Officer (Neglect of Duty is part of Policy/Procedure)
Values: Allegation Type (Definitions)
alle_type
Number

Event Sequence ID

^
The unique number of the incident event record related to this allegation. This is important for linking the allegation to a specific stop, search, use of force, or arrest.
alle_event_sequence_id
Number
Event Sequence(0,11)-(0,N)

Subject comments about specific allegations

Additional narrative information about a specific allegation. Might provide additional context to help investigate and verify these allegations.
Notes: Anytime a user indicate an Allegation they will be prompted for additional information to describe the Allegation or why they think the Allegation should apply.
alle_description
Text-Long

Allegation Findings

The official final disposition of an allegation. Important for tracking responsiveness of police departments, investigative agencies, and complainants.
Notes: Final Allegation status is categorized by Admins after reviewing official department documentation.
Values: Allegation Findings (Definitions)
alle_findings
Number
event_sequence
(eve_)

Event Sequence

Represent key discrete events occurring during a police Incident which require details like Stops, Searches, uses of Force, and Arrests. Event Sequence information is vital for identifying the chronological order of these events. This table also acts as a common reference to associate Orders, Evidence, and People-Event Links with all four of these types of events.
7 Tables with Foreign Keys
Group: Complaint Data Tables
Type: Linking
2 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

event_sequence Unique ID

eve_id
Number, Indexed, Primary Key

Complaint ID

^
The unique number of the complaint record related to this incident event, vital to associating with all other complaint data.
eve_complaint_id
Number Indexed
Complaints(11,11)-(0,N)

Event Type

Indicates whether this record is the type of event representing a stop, search, use of force, or arrest. This is important for determining how to handle this event throughout the system.
Values: Stops ; Searches ; Force ; Arrests
eve_type
Text
stops
(stop_)

Stops

Includes all instances where Officers pull over a vehicle, detain a pedestrian, or otherwise question a Civilian at a residence. This table provides important information about Officers' behavior and Stop practices within particular Departments.
1 Table with Foreign Key
Group: Complaint Data Tables
Type: Subset
25 Fields Total
3 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

stops Unique ID

stop_id
Number, Indexed, Primary Key

Complaint ID

The primary ID of the complaint record related to this stop record.
stop_com_id
Number
Complaints(1,1)-(0,1)

Event Sequence ID

^
The primary ID of the incident event record related to this stops record. This is important for establishing this stop's position in the chronology of events, and associating one or more civilians and one or more officers with this stop.
stop_event_sequence_id
Number
Event Sequence(11,11)-(0,11)

Stated Stop Reason Description

Narrative description of the officer's stated reason for stopping any civilians related to this stop. Might provide additional information for evaluating any allegations related to this stop.
Notes: As best you can remember, what were the exact words this Officer used to explain why you were stopped?
stop_stated_reason_desc
Text-Long

Subject ask to leave?

Indicates whether or not this subject asked the officer if he/she was free to go during the dtop. Doing this indicates that a stop is no longer voluntary. This is important information for evaluating certain allegations related to this stop.
Notes: http://www.flexyourrights.org/faqs/how-long-can-police-detain-you/
Values: Y ; N ; ?
stop_subject_asked_to_leave
Text

What did subject say to officer?

Narrative account of what subject said to the officer during the stop. This is important for evaluating any allegations related to this stop.
Notes: As best you can remember, what were the exact words this Subject said to this Officer?
stop_subject_statements_desc
Text-Long

Officer entered private property?

Indicates whether or not the an officer entered a private residence or workplace. Important for identifying possible violation of department procedure.
Values: Y ; N ; ?
stop_enter_private_property
Text

What happened?

Describes how an officer entered a private residence or workplace. Important for identifying possible violation of department procedure.
stop_enter_private_property_desc
Text

Officer request permission to enter?

Indicates whether or not the officer requested permission to enter a private residence or workplace. Important for identifying possible violation of department procedure.
Values: Y ; N ; ?
stop_permission_enter
Text

Subject gave officer permission to enter?

Indicates whether or not the civilian granted an officer permission to enter a private residence or workplace. Important for identifying possible violation of department procedure.
Notes: If Complainant respond "No", then prompt for Wrongful Entry Allegation (with description).
Values: Y ; N ; ?
stop_permission_enter_granted
Text

Officer requested ID?

Indicates whether or not the officer requested the subject's identification. Important for identifying possible violation of department procedure.
Values: Y ; N ; ?
stop_request_id
Text

Subject refused to give ID?

Indicates whether or not a civilian refused to provide ID to an officer. Important for identifying patterns of excessive ID requests and retaliatory policing.
Notes: http://www.flexyourrights.org/faqs/when-can-police-ask-for-id/
Values: Y ; N ; ?
stop_refuse_id
Text

Subject asked for officer's ID?

Indicates whether or not a subject requested the officer's identification. Important for identifying possible violation of department procedure.
Values: Y ; N ; ?
stop_request_officer_id
Text

Officer refused to give ID?

Indicates whether or not the officer shared their name and/or badge number after subject requested it. Important for identifying possible violation of department procedure.
Notes: We might use this opportunity to let Complainants know that insisting Officers share their badge number isn't always a smart move. Tip: It's okay if you didn't get the badge or vehicle number. An officer--s identity can usually be established with a time, location, and physical description.
Values: Y ; N ; ?
stop_officer_refuse_id
Text

Subject frisked?

Indicates whether or not this subject was patted down by an officer during this stop. This is important information for evaluating certain allegations related to this stop. Also important for identifying patterns of excessive pat downs.
Notes: Related Article http://www.flexyourrights.org/faqs/what-is-reasonable-suspicion/ There was at one point a copy of this field in Searches. We deleted it because frisks are not technically searches and are generally legally permissible as part of a Stop. Q. Do we want mere frisks to trigger wrongful search Allegations? Because frisks alone are almost always legally permissible, this might be Allegation overkill.
Values: Y ; N ; ?
stop_subject_frisk
Text

Subject handcuffed?

Indicates whether or not this subject was handcuffed by an officer during this stop. Important information for identifying department patterns of excessive handcuffing during stops.
Notes: San Jose IPA is trying to limit this common practice.
Values: Y ; N ; ?
stop_subject_handcuffed
Text

Subject injured from handcuffs?

Indicates whether or not the subject reported any injuries related to an officer's misuse of handcuffs. Important for connecting with possible use of force.
Values: Y ; N ; ?
stop_subject_handcuff_inj_yn
Text

Handcuff Injury ID

Indicates whether or not the subject reported any Injuries related to an officer's misuse of handcuffs. Important for connecting with possible use of force.
Notes: Injuries from handcuffs should only link to an Allegation of Excessive Force if and only if the Complainant provides Evidence of Injury. NULL if No
stop_subject_handcuff_injury
Number
Injuries(0,11)-(0,11)

Duration

^
The total calculated minutes of of a single stop. This information is vital for verifying a stop occurrence and evaluating its legal legitimacy.
Notes: Calculated based on Stop Start Time and Stop End Time. Instead of capturing this information within the Stop, this is found in the duration of the Incident for Complaints with a Stop but no Arrest.
stop_duration
Number

Officer gave alcohol breath test?

Indicates whether or not an officer administered an alcohol breath test.
Values: Y ; N ; ?
stop_breath_alcohol
Text

Officer say subject failed alcohol breath test?

Indicates whether or not an officer administered an alcohol breath test, and claimed someone failed.
Values: Y ; N ; ?
stop_breath_alcohol_failed
Text

Officer gave marijuana breath test?

Indicates whether or not an officer administered a marijuana breath test.
Values: Y ; N ; ?
stop_breath_cannabis
Text

Officer say subject failed marijuana breath test?

Indicates whether or not an officer administered a marijuana breath test, and claimed someone failed.
Values: Y ; N ; ?
stop_breath_cannabis_failed
Text

Officer collected saliva?

Indicates whether or not an officer collected someone's saliva.
Values: Y ; N ; ?
stop_saliva_test
Text

Officer gave other sobriety tests?

Indicates whether or not an officer administered other sobriety tests.
Values: Y ; N ; ?
stop_sobriety_other
Text

Description of other sobriety tests

Description of other sobriety tests administered by officer.
stop_sobriety_other_describe
Text
stop_reasons
(stop_reas_)

Stop Reasons

The table stores the stated or non-stated Officer explanations to Civilians for a Stop or detention. This table is important for associating multiple Stop Reasons given for a single Stop.
Group: Complaint Data Tables
Type: Subset
2 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

stop_reasons Unique ID

stop_reas_id
Number, Indexed, Primary Key

Stop ID

The unique number of the stop record related to this stop reason. Vital for associating multiple stop reasons with a single stop during an incident.
stop_reas_stop_id
Number
Stops(11,11)-(0,N)

Officer gave reason for stop

The stated or non-stated officer explanations to civilians for a stop or detention. This table is important for associating multiple reasons given for a single stop.
Values: Reason for Vehicle Stop (Definitions)
stop_reas_reason
Number
searches
(srch_)

Searches

Includes all instances of vehicle searches, home, or other property searches. Detailed Search information is vital to evaluating the legitimacy of the search itself as well as identifying general Search practices within particular Departments.
2 Tables with Foreign Keys
Group: Complaint Data Tables
Type: Subset
20 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

searches Unique ID

srch_id
Number, Indexed, Primary Key

Complaint ID

The primary ID of the complaint record related to this search record.
srch_com_id
Number
Complaints(1,1)-(0,1)

Event Sequence ID

^
The primary ID of the incident event record related to this Search record. This is important for establishing this search's position in the chronology of events, and associating one or more civilians and one or more officers with this search.
srch_event_sequence_id
Number
Event Sequence(11,11)-(0,11)

Did officer state reason for search?

Indicates whether or not the officer gave a reason for this search. Important for evaluating possible wrongful search allegation and identifying search trends.
Values: Y ; N ; ?
srch_stated_reason
Text

Stated reason description

The specific reason the officer gave for conducting this search. Important for evaluating possible wrongful search allegation and identifying search trends.
Notes: As best you can remember, what were the exact words this Officer said before conducting this search?
srch_stated_reason_desc
Text-Long

Officer request permission to search?

Indicates whether or not the officer asked the subject for permission to search their person or property. Important for evaluating possible wrongful search allegation and identifying search trends.
Values: Y ; N ; ?
srch_officer_request
Text

Officer request description

The specific words and tone the officer used to solicit consent to search. Important for evaluating possible wrongful search allegation and identifying search trends.
Notes: What were the exact words and tone this Officer used to request consent to search? (Search requests often sound a lot like commands.)
srch_officer_request_desc
Text-Long

Did subject consent to search?

Indicates whether or not the subject gave the officer permission to search their person or property. Important for evaluating possible wrongful search allegation and identifying search trends.
Values: Y ; N ; ?
srch_subject_consent
Text

What did subject say?

Narrative account of specific words a subject used to consent to the search. Important for evaluating possible wrongful search allegation and identifying search trends.
Notes: What were the exact words and tone [the Subject] used to give consent to the Officer?
srch_subject_say
Text-Long

Officer make threats or lie to get consent to search?

Indicates whether or not an officer made any stated or implied threats to the subject to obtain consent to search subject's person or property. Important for evaluating possible wrongful search allegation and identifying search trends.
Values: Y ; N ; ?
srch_officer_threats
Text

Officer threat description

The specific words the officer used to trick subject into consenting to search. Important for evaluating possible wrongful search allegation and identifying search trends.
Notes: What were the exact words this Officer used to threaten or trick the Subject into giving consent to search?
srch_officer_threats_desc
Text-Long

Officer strip searched someone?

Indicates whether or not an officer performed a strip search on a subject (not including at the police station). Important for evaluating possible wrongful search allegation and identifying search trends.
Values: Y ; N ; ?
srch_strip
Text

Strip search description

Narrative details about the nature of the strip search (not including at the police station). Important for identifying particularly egregious wrongful search allegations — including cavity searches.
Notes: We understand that this isn't easy, but please provide as much detail as you can about the strip search. The more information you provide, the better we can help address your complaint.
srch_strip_search_desc
Text

Officer use K9 (dog) sniff?

Indicates whether or not an officer had a K9 (dog) sniff a subject or their property. Important for identifying possible wrongful search allegation and identifying K9 (dog) search trends.
Notes: Q. Do we want mere K9 sniffs to trigger wrongful search Allegations? Because K9 sniffs alone are almost always legally permissible, this might be Allegation overkill.
Values: Y ; N ; ?
srch_k9_sniff
Text

Officer discover contraband?

Indicates whether or not illegal items were discovered during a search. Important for identifying search trends.
Values: Y ; N ; ?
srch_contraband_discovered
Text

Officer have warrant?

Indicates whether or not an officer had a warrant for a search. Important for evaluating possible wrongful search allegations and for identifying search trends.
Values: Y ; N ; ?
srch_officer_warrant
Text

What did warrant say?

The words on the official warrant. Important for evaluating possible wrongful search allegations.
Notes: User may type actual words on the warrant. Or they may upload a photograph or scan of the warrant as Evidence.
srch_officer_warrant_say
Text-Long

Property seized?

Indicates that officers took property from a subject. Important for evaluating seizure allegations and forfeiture trends.
Values: Y ; N ; ?
srch_seized
Text

Property seizure description

Additional narrative information about the specific property items that were seized. Important for evaluating property seizure allegations.
Notes: Describe the specific items seized. For example, if cash seized, please specify the dollar amount.
srch_seized_desc
Text-Long

Officer damaged property?

Indicates that officers damaged or destroyed subject's property. Important for evaluating property damage claims.
Notes: IF Yes, then Evidence can be optionally uploaded.
Values: Y ; N ; ?
srch_damage
Text

Property damage description

Additional information about the nature of the damaged or destroyed property. Important for evaluating property damage claims.
Notes: Describe the specific items damaged and the nature of the damage. Please include dollar amounts for repairing or replacing damaged items.
srch_damage_desc
Text-Long
search_contra
(srch_con_)

Search Contraband

This table stores types of illegal items that were seized during a Search. Important for associating multiple types of contraband items with a single Search.
Group: Complaint Data Tables
Type: Subset
2 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

search_contra Unique ID

srch_con_id
Number, Indexed, Primary Key

Search ID

The unique number of the search record related to this search contraband found. Vital for associating multiple search ccontraband items with a single search during an incident.
srch_con_search_id
Number
Searches(11,11)-(0,N)

Contraband Types

Categories of illegal items discovered during a search. Important for identifying search trends.
Values: Contraband Types (Definitions)
srch_con_type
Number
search_seize
(srch_seiz_)

Search Seizures

This table stores types of legal property that were seized during a Search. Important for associating multiple types of seized items with a single Search.
Group: Complaint Data Tables
Type: Subset
2 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

search_seize Unique ID

srch_seiz_id
Number, Indexed, Primary Key

Search ID

The unique number of the search record related to this search seizure. Vital for associating multiple search seizures items with a single search during an Incident.
srch_seiz_search_id
Number
Searches(11,11)-(0,N)

Seized property type

Categories of items seized discovered during a search. Important for identifying search trends.
Values: Property Seized Types (Definitions)
srch_seiz_type
Number
arrests
(arst_)

Arrests

Includes all instances where individuals are placed under arrest. This table information helps provide richer detail about the nature of the Arrest and the individual burden of certain low-level discretionary or retaliatory Arrests.
Group: Complaint Data Tables
Type: Subset
11 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

arrests Unique ID

arst_id
Number, Indexed, Primary Key

Complaint ID

The primary ID of the complaint record related to this arrest record.
arst_com_id
Number
Complaints(1,1)-(0,1)

Event Sequence ID

^
The primary ID of the Incident Event record related to this Arrest record. This is important for establishing this arrest's position in the chronology of events, and associating one or more civilians and one or more officers with this arrest.
arst_event_sequence_id
Number
Event Sequence(11,11)-(0,11)

Were any charges filed?

Indicates whether or not any charges were filed as a part of this specific arrest. This is important for determining the risk involved with this complaint, as well as revealing fields to describe the specific charges filed.
Values: Y ; N ; ?
arst_charges_filed
Text

Did officer state a reason for arrest?

Indicates whether or not an officer stated a reason for arresting a civilian. Important for evaluating wrongful arrest allegations.
Notes: Did [the officer] verbally state a reason why he arrested [this Subject]?
Values: Y ; N ; ?
arst_stated_reason
Text

What was stated reason for arrest?

The specific reason an officer gave for making an arrest. Important for evaluating wrongful arrest allegations.
Notes: Before the arrest happened, what specific reasons did [the officer] give for arresting [this Subject]?
arst_stated_reason_desc
Text-Long

Did the officer read Miranda rights?

Indicates whether or not an officer read a subject their Miranda rights during arrest. Important for evaluating possible procedure allegation.
Notes: Failure to read subject Miranda rights turns on a "procedure" violation, and a value of Yes here is equivalent to an Allegation.
Values: Y ; N ; ?
arst_miranda
Text

Was this a search incident to arrest? (Admin)

Indicates whether or not administrators determine that an afficer searched a subject after an Arrest. Important for tracking searches incident to arrest.
Notes: Commonly known as Search Incident To Arrest (SITA) or the Chimel rule, is a legal principle that allows police to perform a warrantless search of an arrested person, and the area within the arrestee--s immediate control, in the interest of officer safety, the prevention of escape, and the destruction of evidence.
Values: Y ; N ; ?
arst_sita
Text

Were all charges dropped before released?

Indicates that subject was placed under arrest, but no official charges were filed. Important for identifying and evaluating wrongful arrest allegations.
Notes: Check with Attorney for precise terminology and common usage.
Values: Y ; N ; ?
arst_no_charges_filed
Text

Strip search done at station?

Indicates whether or not an officer performed a strip search on a subject at the police station. Important for evaluating possible wrongful search allegation and identifying search trends.
Values: Y ; N ; ?
arst_strip
Text

Strip search description

Narrative details about the nature of the strip search. Important for identifying particularly egregious wrongful search allegations -- including cavity searches.
arst_strip_search_desc
Text

Other charges filed?

Arrest charge(s) an officer issued a subject as a result of this stop. Important for evaluating wrongful arrest and retaliatory allegations and tracking citation trends.
arst_charges_other
Text-Long
force
(for_)

Force

Includes all Allegations that feature use of violent contact up to and including deadly Force by an Officer. Because excessive Force Allegations deserve the greatest scrutiny, this information is vital to substantiating such Allegations if they are truthful.
4 Tables with Foreign Keys
Group: Complaint Data Tables
Type: Subset
13 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

force Unique ID

for_id
Number, Indexed, Primary Key

Complaint ID

The primary ID of the complaint record related to this force record.
for_com_id
Number
Complaints(1,1)-(0,N)

Event Sequence ID

^
The primary ID of the incident event record related to this Force record. This is important for establishing this force's position in the chronology of events, and associating one or more civilians and one or more officers with this force.
for_event_sequence_id
Number
Event Sequence(11,11)-(0,11)

Against an animal?

Indicates that this use of force was directed against a non-human pet or animal. Important for identifying and tracking animal cruelty allegations.
Notes: IF "Yes" force was used against an animal, and "Yes" this is allegation-worthy excessive use of force, THEN this is an Animal Cruelty Allegation.
Values: Y ; N ; ?
for_against_animal
Text

Describe Animal

Narrative description of animal that officer force was directed against. Important for verifying animal cruelty allegations.
Notes: Tells us about the animal. For example, what kind of animal is it? Is it a wild animal or stray? Or is it a family pet? Please describe.
for_animal_desc
Text

Types of Force

The categories of force an officer used on a subject. Essential for evaluating allegations of excessive force and for tracking force trends.
Values: Force Type (Definitions)
for_type
Number

Other Type of Force

Category of force an officer used on subject. Important for identifying less-common force types.
Notes: Only visible if "Other" selected under "Which Types of Force did this officer used?"
for_type_other
Text

Type of Gun Ammunition

Category of ammunition an officer used on a subject. Important for investigating allegations of force and for identifying force trends.
Notes: Only if Force Type is 'Gun'.
Values: Gun Ammo Types (Definitions)
for_gun_ammo_type
Number

Gun Description

Narrative details of gun officer used on suspect. Important for investigating allegations of force and for identifying force trends.
Notes: Only if Force Type is 'Gun'.
for_gun_desc
Text

How many times was subject hit?

Indicates the number of times an officer hit a subject with this type of force. Important for investigating allegations of force and for identifying force trends.
Notes: We might not want to reveal this for certain types of force -- such takedowns and control holds. Because those are likely to only be deployed once.
for_how_many_times
Number

Officers orders used before force?

Indicates whether or not an officer issued orders before using force against a subject. Important for investigating excessive force allegations of excessive force.
Values: Y ; N ; ?
for_orders_before_force
Text

Victim's response before force

Narrative account of what the subject of an excessive force allegation said or did before officer used force. Important for investigating excessive force allegations and for identifying force trends.
for_orders_subject_response
Text-Long

Subject handcuffed when hit?

Indicates whether or not subject was handcuffed when struck by an officer. Important for evaluating allegations of excessive force and tracking force trends.
Values: Y ; N ; ?
for_while_handcuffed
Text

Subject held down when hit?

Indicates whether or not subject was restrained on the ground when struck by an officer. Important for evaluating allegations of excessive force and tracking force trends.
Values: Y ; N ; ?
for_while_held_down
Text
force_sub_type
(force_sub_)

Force Sub-Type

This subset table stores secondary types of Use of Force. This table specifically includes greater detail for Use of Force records that include any of these types of force: 1) Control Hold, 2) Body Weapon, and 3) Takedown. This data is important for linking multiple force type details with a single Use of Force.
Group: Complaint Data Tables
Type: Subset
2 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

force_sub_type Unique ID

force_sub_id
Number, Indexed, Primary Key

Force ID

The unique number of the force record related to further type of force details. Vital for associating multiple secondary types of force used with a single use of force during an incident.
force_sub_force_id
Number
Force(11,11)-(0,N)

Secondary Force Type

This provides greater detail on the type of force used in this instance. The options vary between force types control hold, body weapon, or takedown.
Values: Force Type - Control Hold (Definitions)
force_sub_type
Number
force_body_parts
(frc_bdy_)

Force Body Parts

This subset table stores which areas of a Civilian's body were impacted by Force. This is important for providing investigators documentation regarding Civilian's Injuries.
Group: Complaint Data Tables
Type: Subset
2 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

force_body_parts Unique ID

frc_bdy_id
Number, Indexed, Primary Key

Force ID

The unique number of the use of force record which this body part helps describe. Vital for associating multiple body parts with a single use of force during an incident.
frc_bdy_force_id
Number
Force(0,11)-(0,N)

Parts of body which were hit

This describes which part of the body force was used upon or where injuries happened. This is important for providing investigators documentation regarding civilian health.
Values: Body Part (Definitions)
frc_bdy_part
Number
civ_weapons
(civ_weap_)

Civilian Body Weapons

This subset table stores one or more body weapons used by a Civilian against an Officer.
Group: Complaint Data Tables
Type: Subset
2 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

civ_weapons Unique ID

civ_weap_id
Number, Indexed, Primary Key

Complaint ID

The unique number of the complaint record related to these uses of force against an officer. Vital for associating multiple secondary types of force used with a single use of force during an Incident.
civ_weap_com_id
Number
Complaints(1,1)-(0,N)

Civilian body weapon use against officer

The definition ID number of the type of body weapon used by the civilian against the officer. Vital for associating multiple body weapons with a single use of force during an incident.
Values: Force Type - Body Weapons (Definitions)
civ_weap_body_weapon
Number
charges
(chrg_)

Charges

Includes all instances where individuals are charged without being arrested. This table information helps provide richer detail, especially about the use of excessive/unnecessary citations.
Group: Complaint Data Tables
Type: Data
2 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

charges Unique ID

chrg_id
Number, Indexed, Primary Key

Civilian ID

The unique number of the victim's civilian record who has received this arrest or stop charge.
chrg_civ_id
Number
Civilians(1,1)-(0,N)

Charges Filed

Category of citation or arrest charge(s) an officer issued a subject. Important for evaluating excessive citation allegations and tracking citation trends.
Notes: Complainants should be able to select multiple Citations, because we want to be able to track Officers who are Citation-crazy.
Values: Citation Charges (Definitions)
chrg_charges
Number
injuries
(inj_)

Injuries

Includes all instances of bodily harm sustained as a result of alleged Force initiated by Officers. Detailed Injury information is vital to substantiating such Allegations if they are truthful.
2 Tables with Foreign Keys
Group: Complaint Data Tables
Type: Data
5 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

injuries Unique ID

inj_id
Number, Indexed, Primary Key

Subject of Injury

Indicates which subject's injuries are described by this record.
inj_subject_id
Number
Civilians(0,11)-(0,N)

Injury Type(s)

Category of Injury on a civilian's body resulting from use of force. Important for evaluating allegations of excessive force.
Notes: SJ IPA has twenty injury options broken into six categories -- including "none," "pre-existing," and "unknown."
Values: Injury Types (Definitions)
inj_type
Number

How Many Injuries

A number to indicate how many Injuries a particular civilian received during an use of force incident. Important for quantifying the number of discrete Injuries received from force.
Notes: We have decided this might not be necessary 10/17/15======This field hidden for Injury Types (these types count by Injury Locations): In-patient hospital stay required, Blood loss requiring transfusion, Major concussion, Longer than brief loss of consciousness, Minor concussion, Brief loss of consciousness,
inj_how_many_times
Number

Injury Description

Additional narrative information about a specific injury. Important for evaluating allegations of excessive force.
inj_description
Text-Long

Injury form complete?

Indicates whether or not the complainant has gone through this injury record yet.
Values: 0 ; 1
inj_done
Number
injury_body_parts
(inj_bdy_)

Injury Body Parts

This subset table stores which areas of a Civilian's body were impacted by this Injury. This is important for providing investigators documentation regarding Civilian's Injuries.
Group: Complaint Data Tables
Type: Subset
2 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

injury_body_parts Unique ID

inj_bdy_id
Number, Indexed, Primary Key

Injury ID

The unique number of the injury record which this body part helps describe. Vital for associating multiple body parts with a single type of injury ocurring during an incident.
inj_bdy_injury_id
Number
Injuries(0,11)-(0,N)

Part(s) of body injured

This describes the part of the body where injuries occurred. This is important for providing investigators documentation regarding civilian health.
Values: Body Part (Definitions)
inj_bdy_part
Number
injury_care
(inj_care_)

Injury Care

Information from medical institutions who treated a Civilian's Injuries. Important for investigating use of Force Allegations.
Group: Complaint Data Tables
Type: Data
17 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

injury_care Unique ID

inj_care_id
Number, Indexed, Primary Key

Subject ID

^
Indicates which subject received injury care tracked in this record. Important for identifying and matching specific uses of force with specific injuries received during an incident.
inj_care_subject_id
Number
Civilians(11,11)-(0,N)

Injuries result in death?

Indicates that an injury resulted in death to the civilian. Important for identifying police killings.
Notes: This might be redundant if we have a "Fatal injuries" selection under "Injury Type." However, I feel like this might be an important stand-alone question. Implementation Note: This should NOT be asked if the Complainant indicates that they are the Civilian linked to this particular Injury -- because that would be damn near impossible.
Values: Y ; N ; ?
inj_care_result_in_death
Text

Date & Time of death

Indicates the official time of death of a civilian resulting from a force injury. Important for statistical purposes.
inj_care_time_of_death
Date&Time

Received medical care?

Indicates whether or not an injured civilian received medical care from a licensed medical practitioner. Essential for urging injured civilians to seek medical care who haven't received it yet.
Notes: Implementation Note: We must urge users with fresh Injuries to seek medical care if they haven't yet. Because official medical records are essential for substantiating Force Allegations.
Values: Y ; N ; ?
inj_care_got_medical
Text

Hospital Where Treated

The name of the hospital where the civilian who received injuries was initially treated. Important for verifying injury information.
inj_care_hospital_treated
Text

Doctor First Name

The legal given name of a doctor who treated an injury. Important for identifying doctors who treated injuries.
inj_care_doctor_name_first
Text

Doctor Last Name

The legal surname of a doctor who treated an injury. Important for identifying doctors who treated Injuries.
inj_care_doctor_name_last
Text

Doctor Email

The valid email address of a doctor who treated an injury. Important for communicating with doctors who treated injuries.
inj_care_doctor_email
Text

Doctor Phone

The contact number of a doctor who treated an injury. Important for communicating with doctors who treated injuries.
inj_care_doctor_phone
Text

Emergency staff on scene?

Indicates whether or not emergency medical staff were present at the scene where any injuries were received. Important for identifying emergency medical personnel who treated injuries.
Values: Y ; N ; ?
inj_care_emergency_on_scene
Text

Emergency Staff First Name

The legal given name of an emergency medical services staffer present at the scene where any injuries were received. Important for identifying emergency medical personnel who treated injuries.
inj_care_emergency_name_first
Text

Emergency Staff Last Name

The legal surname of an emergency medical services staffer present at the scene where any Injuries were received. Important for identifying emergency medical personnel who treated injuries.
inj_care_emergency_name_last
Text

Emergency Staff ID Number

A unique number assigned to the emergency medical services staffer by their department. Important for identifying emergency medical personnel who treated injuries.
inj_care_emergency_id_number
Text

Emergency Staff Vehicle Number

A unique number assigned to an emergency medical services vehicle. Important for identifying emergency medical personnel who treated injuries.
inj_care_emergency_vehicle_number
Text

Emergency Staff Licence Number

The license plate number of an emergency medical services vehicle. Important for identifying emergency medical personnel who treated injuries.
inj_care_emergency_licence_number
Text

Emergency Staff Department Name

The official name of the emergency medical services department. Important for identifying emergency medical personnel who treated injuries.
inj_care_emergency_dept_name
Text

Injury Care Form Done

Indicates whether or not the complainant has gone through this injury care record yet.
Values: 0 ; 1
inj_care_done
Number
surveys
(surv_)

Surveys

Includes feedback after the entire process has been completed. This is primarily provided by Complainants, but may also be expanded to other types of system Users. This is vital for continuing to improve the quality of the system, both in general and in specific detail.
Group: Complaint Data Tables
Type: Data
2 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

surveys Unique ID

surv_id
Number, Indexed, Primary Key

Complaint ID

^
The unique number of the complaint record related to this survey record, vital to associating with all other complaint data.
surv_complaint_id
Number Indexed
Complaints(0,11)-(0,N)

User Authentication ID

^
The unique number of the system user record logged in while completing this survey record. Important for tracking which user is providing this feedback on the OP system.
surv_auth_user_id
Number
Users(0,11)-(0,11)
complaint_notes
(note_)

Complaint Notes

Information which must be appended to Complaints any time after they are submitted. This is important for recording updates from system Administrators or even allowing Complainants to potentially upload evidence obtained after initially submitting their report.
Group: Complaint Data Tables
Type: Data
3 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

complaint_notes Unique ID

note_id
Number, Indexed, Primary Key

Complaint ID

^
The unique number of the complaint record related to this complaint note. Vital for associating complaint record to complaint note data.
note_complaint_id
Number Indexed
Complaints(11,11)-(0,N)

User ID

^
The primary user authentication number connected to this complaint note. This is vital to tracking which system user provide which new information and evidence to a complaint.
note_user_id
Number
Users(11,11)-(0,N)

Note Content

The main text/body of a note being added to a complaint, providing new information or descriptive updates of the complaint's status. This is important as an open-ended tool to add extra information about a complaint.
note_content
Text-Long
civilians
(civ_)

Civilians

Individuals who are directly impacted by, observed, or are reporting on an police incident. This information allows us to identify and contact individuals who've been impacted by alleged incident.
7 Tables with Foreign Keys
Group: People Involved In Complaint
Type: Data
23 Fields Total
4 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

civilians Unique ID

civ_id
Number, Indexed, Primary Key

Complaint ID

^
The unique number of the complaint record related to this civilian's record. Vital for associating this civilian with other complaint data.
civ_complaint_id
Number Indexed
Complaints(0,11)-(11,N)

User ID

^
The primary user authentication number connected to a civilian's record. Important for allowing complainants to log into OP to complete complaints or update complaints.
civ_user_id
Number
Users(0,11)-(0,11)

Creator of this complaint?

Indicates that this civilian is the person who created the complaint or compliment. This information helps us identity complainants who are also subjects or witnesses.
Values: Y ; N ( default value: N )
civ_is_creator
Text

Civilian's role in incident?

Indicates the category of a civilian. This information is essential for understanding whether a given civilian was a subject of an allegation or a witness to the allegation.
Notes: People cannot be both a Subject and a Witness to an Incident. If considered both, then they are a Subject.
Values: Witness ; Subject ; Neither ; Upset
civ_role
Text

Person Contact Info ID

The primary person contact record number connected to a civilian's record. Important for linking name and basic contact information to a civilian.

Physical Description ID

The primary physical description record number connected to a civilian's record. Important for linking name and basic contact information to a civilian.
civ_phys_desc_id
Number
Physical Descriptions(0,11)-(0,11)

Name of civilian known?

Indicates whether or not the complainant intends to provide information about the name of this civilian. This is important for later reloading complaint forms with the user's previous response.
Values: Y ; N ; ?
civ_give_name
Text

Contact information known?

Indicates whether or not the complainant intends to provide any contact information for this civilian. This is important for later reloading complaint forms with the user's previous response.
Values: Y ; N ; ?
civ_give_contact_info
Text

Resident of jurisdiction?

Indicates whether or not a victim was a resident of the police department's jurisdiction, at the time of the incident. This information could help inform race-based allegations.
Values: Y ; N ; ?
civ_resident
Text

Occupation

The profession of a civilian. This information can help investigators identify civilians.
civ_occupation
Text

Using vehicle at start?

Indicates whether or not the complainant intends to provide information about the vehicle this civilian was in. This is important for later reloading complaint forms with the user's previous response.
Values: Y ; N ; ?
civ_had_vehicle
Text

Chased by police?

Indicates whether or not subject was chased by an officer. Important for evaluating allegations of excessive force and tracking force trends.
Values: Y ; N ; ?
civ_chase
Text

Type of Chase

Indicates whether the subject's chase took place on foot, in vehicles, or both. Important for evaluating allegations of excessive force and tracking force trends.
Values: Chase Types (Definitions)
civ_chase_type
Number

Did subject have weapon?

Indicates what type of weapon the victim of this use of force had at the time of the incident (including 'No weapon' and 'Not sure'). This is important for determining if the officer used unreasonable force.
Values: Civilian Weapons (Definitions)
civ_victim_what_weapon
Number

Did subject use the weapon?

Indicates how the victim used a weapon during this use of force. This is important for determining if the officer used unreasonable force.
Values: Intimidating Displays Of Weapon (Definitions)
civ_victim_use_weapon
Number

Recorded this Incident?

^
Indicates whether or not this civilian recorded the Incident. This information might help us track down new video evidence.
Values: Y ; N ; ?
civ_camera_record
Text

Used profanity?

Indicates whether or not this civilian used profanity during the incident. This information might help establish credibility of the complainant.
Values: Y ; N ; ?
civ_used_profanity
Text

Received injuries from use of force?

Indicates whether or not subject has at least one injury from the use of force. Important for asking followup questions.
Values: Y ; N ; ?
civ_has_injury
Text

Received medical care?

Indicates whether or not subject received medical care for any injuries from the use of force. Important for asking followup questions.
Values: Y ; N ; ?
civ_has_injury_care
Text

Given Citation (Not Arrested)

Indicates whether or not this subject was given a citation with charges as a result of this stop.
Values: Y ; N ; ?
civ_given_citation
Text

Given Written Warning

Indicates whether or not this subject was only given a warning as a result of this stop.
Values: Y ; N ; ?
civ_given_warning
Text

Citation ID#

Citation ID number issued to a subject. Important for tracking down more information during investigations.
civ_citation_number
Text

Other Charges Filed

Citation charge(s) an officer issued a subject as a result of this stop. Important for evaluating wrongful citation allegations and tracking citation trends.
civ_charges_other
Text-Long
officers
(off_)

Officers

Includes law enforcement personnel who engaged in alleged misconduct or who witnessed alleged misconduct mentioned in a Complaint. This information is vital for verifying the identity of named and unnamed officers who were involved in alleged police misconduct.
4 Tables with Foreign Keys
Group: People Involved In Complaint
Type: Data
19 Fields Total
5 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

officers Unique ID

off_id
Number, Indexed, Primary Key

Verified Officer Record ID

The unique number of the verified officer record related to this officer's record. Vital for associating officer with multiple complaints and departments.
off_verified_id
Number
Verified Officer Records(1,1)-(0,N)

Complaint ID

^
The unique number of the complaint record related to this officer's record. Vital for associating officer with all other complaint data.
off_complaint_id
Number Indexed
Complaints(0,11)-(11,N)

Officer's Role in this Incident

Indicates an officer's role in the incident. This information is essential for understanding whether a given officer was a subject of an allegation or a witness to the allegation.
Values: Subject Officer ; Witness Officer
off_role
Text

Department ID

^
The unique number of the department record related to this officer. Vital for associating this officer with other department data.
off_dept_id
Number Indexed
Police Departments(11,11)-(0,N)

Person Contact Info ID

The primary person contact record number connected to a officer's record. Important for linking name and basic contact information to an officer.

Physical Description ID

The primary physical description record number connected to a officer's record. Important for linking name and basic contact information to a officer.
off_phys_desc_id
Number
Physical Descriptions(0,11)-(0,11)

Officer identity known?

Indicates whether or not the complainant intends to provide information about the name and/or badge number(s) of this officer. This is important for reloading complaint forms with the user's previous response.
Values: Y ; N ; ?
off_give_name
Text

Using Vehicle at Start

Indicates whether or not the complainant intends to provide information about the vehicle this officer was in. This is important for later reloading complaint forms with the user's previous response.
Values: Y ; N ; ?
off_had_vehicle
Text

Department Precinct/District

^
The district within a city or municipality where an officer is assigned. This information might help us verify an officer's identity.
Notes: Most Departments will NOT have precinct. However, precincts or districts usually exist in big cities.
off_precinct
Text

Badge Number

^
An identification number that departments assign to officers. This information might help us verify an officer's identity.
Notes: We might want to tell users that they don't necessarily need a badge number to identify Officers
off_badge_number
Text

Additional ID#

^
A secondary number that departments assign to officers, which stays consistent over time. This information might help us verify an officer's identity.
Notes: Eg. in New York, officers have a consistent Tax ID. This might help us track Officers who obtain a new rank, or move to a different precinct or district.
off_id_number
Number

Officer Rank

^
This indicates an officer's position within the department. This information might help us verify an officer's identity.
Notes: Option list from Philly PD, adapt as needed.
Values: Police Officer ; Detective ; Corporal ; Sergeant ; Lieutenant ; Captain ; Staff Inspector ; Inspector ; Chief Inspector ; Deputy Commissioner ; Commissioner ; ?
off_officer_rank
Text

Vehicle had dash cam?

Indicates whether or not the officer's vehicle had a dashcam. Essential for obtaining possible video evidence of the incident.
Notes: The Complainant won't likely know if there was a dashcam. This is intended as a prompt to urge Complainants to seek this information on their own or with an Attorney's help. On 9/17, we decided not to include this in the UI specs because most complainants will 1) not know if there is a dash cam; 2) won't be able to access any available footage without lawyer; and 3) this could be an unnecessary and complex distraction for users.
Values: Y ; N ; ?
off_dash_cam
Text

Wearing body camera?

Indicates whether or not the officer was wearing a body-mounted camera. Essential for obtaining possible video evidence of the incident.
Notes: We will need to create document with tips on obtaining dash-cam and on-body camera footage.
Values: Y ; N ; ?
off_body_cam
Text

Duty Status During Incident

Indicates whether an officer was on-duty or off-duty during the incident. Particularly important for investigating allegations against off-duty officers.
Values: On-Duty ; Off-Duty ; ?
off_duty_status
Text

Wearing uniform?

Indicates whether or not officer was wearing a uniform during the incident. Particularly important for investigating allegations against officers in plain clothes.
Values: Y ; N ; ?
off_uniform
Text

Used profanity?

Indicates whether or not this officer used profanity during the incident. This information helps flag a certain type of discourtesy.
Values: Y ; N ; ?
off_used_profanity
Text

Additional Details

Supplemental narrative details about officers involved in incident. Important for verifying an officer's identity or department.
Notes: This field only used for light/bronze version of user experience.
off_additional_details
Text-Long

Received compliments?

Indicates whether or not the complainant completed a compliment page for this officer.
Values: Y ; N
off_gave_compliment
Text
officers_verified
(off_ver_)

Verified Officer Records

An internal Verified Officer Record is used to the most accurate and current information on an officer. This is vital for tracking Officers involved in multiple Incidents over time, and multiple departments.
2 Tables with Foreign Keys
Group: People Involved In Complaint
Type: Data
13 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

officers_verified Unique ID

off_ver_id
Number, Indexed, Primary Key

Verification Status

Indicates whether or not this officer has been verified as employed by a department listed in at least one complaint. This is vital for tracking which officers' employment has been confirmed by OpenPolice.org staff.
Values: Verified Officer Status (Definitions)
off_ver_status
Number

Person Contact Info ID

The primary person contact record number connected to this verified officer record. Important for linking name and basic contact information to a verified officer.
off_ver_person_id
Number
Person Contact Info(1,1)-(1,1)

Number of Published Complaints

Indicates the total number of complaints which have made it through any published status.
( default value: 0 )
off_ver_cnt_complaints
Number

Number of Published Allegations

Indicates the total number of complaint allegations which have made it through any published status.
( default value: 0 )
off_ver_cnt_allegations
Number

Number of Published Compliments

Indicates the total number of compliments which have made it through any published status.
( default value: 0 )
off_ver_cnt_compliments
Number

Number of Published Commendations

Indicates the total number of compliment commendations which have made it through any published status.
( default value: 0 )
off_ver_cnt_commends
Number

Unique String For Record

^
This unique string is for cases when including the record ID number is not appropriate.
off_ver_unique_str
Text

Experience Node Progress

^
Indicates the unique node ID number of the last experience node loaded during this user's experience.
off_ver_submission_progress
Number

A/B Testing Version

^
Stores a complex string reflecting all A/B Testing variations in effect at the time of this user's experience of this node.
off_ver_version_ab
Text

Tree Version Number

^
Stores the current version number of this user experience, important for tracking bugs.
off_ver_tree_version
Text

IP Address

^
Encrypted IP address of the current user.
off_ver_ip_addy
Text

Using Mobile Device

^
Indicates whether or not the current user is interacting via a mobile deviced.
off_ver_is_mobile
Text

User ID

^
Indicates the unique user ID number of the user owning the data stored in this record for this experience.
off_ver_user_id
Number
Users(11,11)-(11,11)
person_contact
(prsn_)

Person Contact Info

This is the name and contact info for Civilians, Officers, and other system Users. This information is vital for keeping in touch with all the key people connected to the police oversight process.
7 Tables with Foreign Keys
Group: People Involved In Complaint
Type: Subset
18 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

person_contact Unique ID

prsn_id
Number, Indexed, Primary Key

Name Prefix

^
Letters placed before a person's first name that we use in all formal communications with persons or regarding complaints.
Notes: Use only if selected. Field may otherwise be left blank
prsn_name_prefix
Text

First Name

^
The legal given name of persons that we use in all formal communications with persons or regarding complaints.
prsn_name_first
Text

Nickname

^
Preferred first name of persons that we may use in less-formal communications with them. Important for maintaining cordial relationships with these key contacts.
Notes: Use only if selected. Field may otherwise be left blank. For people directly involved in a police incident (Victims, Witnesses, Officers), the Nickname field is really for the sake of user experience while taking the survey.
prsn_nickname
Text

Middle Name

^
The name of a person, placed between their first name and before their surname, that we use in all formal communications with persons or regarding complaints.
Notes: Use only if selected. Field may otherwise be left blank
prsn_name_middle
Text

Last Name

^
The legal surname of persons that we use in all formal communications with or regarding complaints.
prsn_name_last
Text

Name Suffix

^
Letters added after a person's last name related to this record -- which provide additional information about their title or inherited name -- that we use in all formal communications with persons or regarding complaints.
prsn_name_suffix
Text

Email

^
The valid email address provided by or for a person. This is the primary way we communicate with them.
prsn_email
Text

Home Phone

^
The contact number where a person can be reached at their home. This is usually a landline phone, which cannot receive text messages.
Notes: At least one phone number -- be it Home, Work, or Mobile -- should be required for public complaints.
prsn_phone_home
Text

Work Phone

^
The contact number where persons can be reached at their place of business. This is usually a landline phone, which cannot receive text messages.
Notes: We might need to add an "extension" field for some work phone numbers.
prsn_phone_work
Text

Mobile Phone

^
The contact number where persons can be reached on their cellular device. We may use this number for both text and voice communications.
prsn_phone_mobile
Text

Street Address

^
The first line of the postal location where a person resides or is able to receive mail. This is an integral component of a person's complete address.
Notes: If they can't reliable receive mail at residence, use reliable mailing address instead.
prsn_address
Text

Street Address 2

^
The second line, if needed, of the postal location where a person resides or is able to receive mail. This is an integral component of a person's complete address.
prsn_address2
Text

City

^
The metropolitan area where a person resides or is able to receive mail. This is an integral component of a person's complete address.
prsn_address_city
Text

State

^
The state or district where a person resides or is able to receive mail. This is an integral component of a person's complete address.
prsn_address_state
Text

Zip Code

^
The postal code where a person resides or is able to receive mail. This is an integral component of a person's complete address.
prsn_address_zip
Text

Date of Birth

^
The month, day, and year a person was born. This information is used to help verify a person's identity.
Notes: If Date of Birth is unknown, the "Age" field will become visible, which offers age ranges to be selected.
prsn_birthday
Date

Facebook

^
This is the URL address of a person's Facebook page. This is used to help verify their identities and for communicating with them if they select this as a preferred method of contact.
prsn_facebook
Text

User ID

Indicates the unique User ID number of the user owning the data stored in this record for this contact info.
prsn_user_id
Number
Users(0,1)-(0,1)
physical_desc
(phys_)

Physical Descriptions

This table includes subjective characteristics of Civilians and Officers as described by Complainants. This information is vital for for verifying the identity of Civilians and Officers connected to Complaints.
4 Tables with Foreign Keys
Group: People Involved In Complaint
Type: Subset
6 Fields Total
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

physical_desc Unique ID

phys_id
Number, Indexed, Primary Key

Gender

^
The sex classification of a person. This category is used to help verify a person's identity.
Notes: Depending on who is selecting, this might be self-ascribed or Complainant-selected for another Civilian. Question should read, "What is the gender of the subject of the alleged misconduct?" Options should be Male, Female, and Other (please specify)
Values: M ; F ; ?
phys_gender
Text

Gender Other

Open field for other gender classifications of someone involved in the incident. It includes autocomplete suggestions for Transgender, Female to male transgender, Male to female transgender, Genderqueer/Androgynous, Cross-dresser, Transsexual, Intersex.
phys_gender_other
Text

Age Range

^
Estimated age in years of a person. This information is used to verify a person's identity.
Notes: Because the "Date of Birth" field will determine Complainant's age. This field should become visible only if a Civilian's age is unknown. The "Harmonised Standard 3" age ranges presented here: https://meta.wikimedia.org/wiki/Survey_best_practices#Age
Values: Age Ranges (Definitions)
phys_age
Number

Height

^
Estimated height of a person in feet and inches. This information might help verify a person's identity.
Notes: We should use a tool that indicates feet and inches (not just total inches.) Most people wouldn't describe someone as 72 inches. They would say 6 feet.
phys_height
Number

Body Type

^
General category of a person's body shape. This information might help verify a person's identity.
Values: Body Types (Definitions)
phys_body_type
Number

Physical Description

^
The attributes of any prominent physical characteristics on a person. This information might help verify a person's identity.
phys_general_desc
Text
physical_desc_race
(phys_race_)

Physical Descriptions Race

This subset table stores races associated with the Physical Descriptions of Civilians and Officers. This table specifically provides the important ability to accurately store data for people with multiple races.
Group: People Involved In Complaint
Type: Subset
2 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

physical_desc_race Unique ID

phys_race_id
Number, Indexed, Primary Key

Physical Description ID

The primary physical description record number connected to this race association. Important for multiple races to a single person.
phys_race_phys_desc_id
Number
Physical Descriptions(11,11)-(0,N)

Race

The complainant-selected or self-ascribed racial classification of a person. This classification helps verify the identity of officers who are the subject of a complaint and is useful for determining possible patterns or practices of race-based policing.
Notes: Depending on who is selecting, this might be self-ascribed or Complainant-selected for another Civilian. We will use the Census Bureau's classifications: http://www.census.gov/topics/population/race/about.html
Values: Races (Definitions)
phys_race_race
Number
vehicles
(vehic_)

Vehicles

This table includes identifying information about Civilian and Officer vehicles. This data helps investigators locate or verify the identities of Officers and Civilians involved in an Incident.
2 Tables with Foreign Keys
Group: People Involved In Complaint
Type: Subset
9 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

vehicles Unique ID

vehic_id
Number, Indexed, Primary Key

Complaint ID

The unique number of the complaint record related to this Incident, vital to associating with all other complaint data.
vehic_complaint_id
Number
Complaints(1,1)-(0,N)

Was civilian vehicle?

Indicates whether or not this record describes a civilian vehicle. Otherwise, this record belongs to an officer's vehicle.
Values: 0 ; 1
vehic_is_civilian
Number

Type of Vehicle

^
Indicates what type of vehicle was involved during this incident.
Values: Transportation Officer (Definitions)
vehic_transportation
Number

Vehicle unmarked or undercover?

Indicates whether or not an officer's vehicle was an unmarked car, meaning it looks like a normal vehicle with no badges, graphics, or visible police lights. This is important for tracking trends of officer behavior.
Values: Y ; N ; ?
vehic_unmarked
Text

Vehicle Make

^
The company that manufactured a civilian's vehicle. This information might help verify a civilian's identity.
Notes: Vehicle makes include Toyota, Ford, Honda, Chevrolet, etc.
vehic_vehicle_make
Text

Vehicle Model

^
The name or brand of the civilian's vehicle. This information might help verify a civilian's identity.
Notes: Vehicle models include Toyota Camry, Ford F-Series, Chevrolet Silverado, etc.
vehic_vehicle_model
Text

Vehicle Description

^
Additional attributes of the civilian's vehicle. This information might help verify a civilian's identity.
Notes: What color was the vehicle? Did it have any distinguishing characteristics, such as dents, customizations, etc?
vehic_vehicle_desc
Text

License Plate Number

^
The tag numbers on the civilian's vehicle. This information might help verify a civilian's identity.
vehic_vehicle_licence
Text

Vehicle Number

A unique vehicle ID number. Important for verifying an officer's identity or department.
Notes: This is not the same as License Number.
vehic_vehicle_number
Text
tester_beta
(beta_)

Beta Test Signups

Each record is one complainant who requested to participate in the pilot program.
Group: People Involved In Complaint
Type: Data
14 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

tester_beta Unique ID

beta_id
Number, Indexed, Primary Key

Email Address

Indicates the email address of the beta tester to be used for contact.
beta_email
Text

First Name

Indicates the first name of the complainant signing up to beta test.
beta_name
Text

Last Name

Indicates the last name of the complainant signing up to beta test.
beta_last_name
Text

Year

Indicates what year the beta test user's incident happened.
beta_year
Number

Incident Narrative

Indicates the complainant's brief preview of their incident description.
beta_narrative
Text-Long

Referred From

Indicates where this beta tester got the link to the beta test signup, maybe via Newsletter, Facebook, YouTube, etc.
beta_how_hear
Text

Date Invited

Indicates the date and time this beta test user was invited to use OP.
beta_invited
Date

User ID

^
Indicates the unique user ID number of the user owning the data stored in this record for this experience.
beta_user_id
Number
Users(11,11)-(11,11)

A/B Testing Version

^
Stores a complex string reflecting all A/B Testing variations in effect at the time of this user's experience of this node.
beta_version_ab
Text

Experience Node Progress

^
Indicates the unique node ID number of the last experience node loaded during this user's experience.
beta_submission_progress
Number

IP Address

^
Encrypted IP address of the current user.
beta_ip_addy
Text

Tree Version Number

^
Stores the current version number of this user experience, important for tracking bugs.
beta_tree_version
Text

Unique String For Record

^
This unique string is for cases when including the record ID number is not appropriate.
beta_unique_str
Text

Using Mobile Device

^
Indicates whether or not the current user is interacting via a mobile deviced.
beta_is_mobile
Text
departments
(dept_)

Police Departments

Includes Local, State, and Federal law enforcement agencies and their jurisdictional boundaries. This table information allows the organization to identify and track Departments associated with Complaints.
8 Tables with Foreign Keys
Group: People & Groups Involved In Complaint Process
Type: Data
27 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

departments Unique ID

dept_id
Number, Indexed, Primary Key

Department Name

The official name of the law enforcement agency. This is an essential component of a police department's complete address.
dept_name
Text

URL Slug

This defines the version of the department name which is compatible and ideal to be used as part of a website URL. This is vital for creating public pages of the website which are intuitive and professional.
dept_slug
Text

Type of Department

The general category of a particular law enforcement agency. This information helps us identify the responsibilities and jurisdiction of a given police department.
Notes: Detailed Google Doc: https://docs.google.com/document/d/1OZBZYd8V7gC3es46Z5sWrfu2IRvhrK1CkfV8vKp8xgY/edit?usp=sharing
Values: Types of Departments (Definitions)
dept_type
Number

Activity Status

The current activity status of the department, 1 for active, 0 for inactive. This vital for identifying departments which have been closed since the creation of the original data set.
Values: Department Status (Definitions)
dept_status
Number

Contact Info Last Verified

^
Indicates the date and time when police department contact information was last verified. This is crucial for keeping track of the accuracy of police department contact information.
Notes: NULL value means unverified.
dept_verified
Date&Time

Email

^
The valid public-facing email address for police departments. If we don't have a direct email contact with an Internal Affairs Staffer, this will be the primary way we contact police departments.
Notes: This is the email that would be featured on their OPC web page for this Department. We might include this in addition to a general Internal Affairs email contact, if one exists.
dept_email
Text

Work Phone

^
The public contact number where police departments can be reached.
Notes: We might need to add an "extension" field for some work phone numbers.
dept_phone_work
Text

Street Address

^
The first line of the postal location where a police department receives mail. This is an integral component of a police department's complete address.
dept_address
Text

Street Address 2

^
The second line, if needed, of the postal location where a police department receives mail. This is an integral component of a police department's complete address.
dept_address2
Text

City

^
The metropolitan area where a police department receives mail. This is an integral component of a police department's complete address.
dept_address_city
Text

State

^
The state or district where a police department receives mail. This is an integral component of a police department's complete address.
dept_address_state
Text

Zip Code

^
The postal code where a police department receives mail. This is an integral component of a police department's complete address.
dept_address_zip
Text

County

The state region where a local police department is headquartered or has jurisdiction. This information helps us track which police departments have jurisdiction over a specific incident location.
Notes: Not applicable to Departments with Federal and Statewide jurisdictions.
dept_address_county
Text

Openness Score

This indicates an overall rating for a police department's online presence and the quality of their complaint submission process. This is important for encouraging departments to improve these metrics.
dept_score_openness
Text

Total Number of Employees

The total number of sworn officers employed by a police department. This information will be used for comparative statistical purposes and to determine the most likely department on-scene during a particular Incident.
Notes: For example, if a User doesn't know which Police Department employed a given Subject Officer, the field list for Police Departments would begin by listing the local department with the highest number of total sworn officers.
dept_tot_officers
Number

Jurisdiction Population

The total number of people who live within the geographical boundaries of a police department. This information will help identify policing trends and for comparative statistical purposes.
dept_jurisdiction_population
Number

Jurisdiction GPS

A series of GPS coordinates, representing the geographical polygon of a department's jurisdiction. Important for identifying the police department attached to a complaint -- especially when complainants are unsure.
dept_jurisdiction_gps
Text-Long

User ID

^
Indicates the unique user ID number of the user owning the data stored in this record for this experience.
dept_user_id
Number
Users(0,1)-(0,N)

Experience Node Progress

^
Indicates the unique node ID number of the last experience Node loaded during this user's experience.
dept_submission_progress
Number

Tree Version Number

^
Stores the current version number of this user experience, important for tracking bugs.
dept_tree_version
Text

A/B Testing Version

^
Stores a complex string reflecting all A/B Testing variations in effect at the time of this user's experience of this node.
dept_version_ab
Text

Unique String For Record

^
This unique string is for cases when including the record ID number is not appropriate.
dept_unique_str
Text

IP Address

^
Encrypted IP address of the current user.
dept_ip_addy
Text

Using Mobile Device

^
Indicates whether or not the current user is interacting via a mobile deviced.
dept_is_mobile
Text

Geolocation Latitude

The geographic coordinate that specifies the north-south position of the point on the Earth's surface where a police department is located.
dept_address_lat
Number-Decimals

Geolocation Longitude

The geographic coordinate that specifies the east-west position of the point on the Earth's surface where a police department is located.
dept_address_lng
Number-Decimals

Can OP E-File Compliant?

Indicates whether or not this department is "OpenPolice Compliant". This means they will investigate complaints sent to a known email address, and do not require their own official form.
Values: 0 ; 1
dept_op_compliant
Number
oversight
(over_)

Investigative Agencies

These are the organizations tasked with receiving and investigating Civilian Complaints against Officers. This information helps us keep track of who is investigating Complaints submitted through OpenPolice.org.
2 Tables with Foreign Keys
Group: People & Groups Involved In Complaint Process
Type: Data
40 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

oversight Unique ID

over_id
Number, Indexed, Primary Key

Oversight Type

Indicates category of investigative agency. This information is essential for determining if a given investigative agency is either an Internal Affairs Department or a Citizen Investigative Agency. (They cannot be both.)
Values: Investigative Agency Types (Definitions)
over_type
Number

Basic Oversight Level

Indicates the basic level of oversight tracked by this record.
Values: Investigative Level (Definitions)
over_civ_model
Number

User ID

^
The primary user authentication number connected to this investigative agency. This number allows investigative agency contacts to log into the OP system.
over_user_id
Number
Users(0,11)-(0,11)

Department ID

^
The unique number of the department record related to this investigative agency. This information helps us identify the proper investigative agency with jurisdiction over an OP complaint directed at a specific police department.
over_dept_id
Number Indexed
Police Departments(11,11)-(0,N)

Agency Name

The official name of an investigative agency. This is an essential component of a investigative agency's complete address.
over_agnc_name
Text

Contact Info Last Verified

^
Indicates the date and time when an investigative agency's police contact information was last verified. This is crucial for keeping track of the accuracy of investigative agency's contact information.
Notes: NULL value means unverified.
over_verified
Date&Time

Name Prefix

^
Letters placed before an investigative agency contact's first name that we use in all formal communications with investigative agency contacts or regarding investigative agency contacts.
Notes: Use only if selected. Field may otherwise be left blank
over_name_prefix
Text

First Name

^
The legal given name of an investigative agency contact that we use to identify them. We also use this in all formal communications with investigative agency contacts and regarding investigative agency contacts.
over_name_first
Text

Nickname

^
Preferred first name of investigative agency contacts that we use in less-formal communications with them. Important for maintaining cordial relationships with these key contacts.
Notes: Use only if selected. Field may otherwise be left blank
over_nickname
Text

Middle Name

^
The name of an investigative agency contact, placed between their first name and before their surname, that we use in all formal communications with investigative agency contacts or regarding investigative agency contacts.
Notes: Use only if selected. Field may otherwise be left blank
over_name_middle
Text

Last Name

^
The legal surname of an investigative agency contact that we use in formal communications with or regarding an investigative agency contact.
over_name_last
Text

Name Suffix

^
Letters added after an investigative agency contact's last name -- which provide additional information about their title or inherited name -- that we use in all formal communications with investigative agency contacts or regarding investigative agency contacts.
over_name_suffix
Text

Title

^
The job position of an investigative agency contact. We might use this in formal communications with investigative agency contacts or regarding investigative agency contacts.
over_title
Text

ID Number

A unique number used to identify each investigative agency. We assign a number to all active investigative agencies and remains with them for the duration of their existence.
over_id_number
Text

Website

^
The page URL of the Civilian Investigative Agency or Internal Affairs Department. Ideally provides clear information about the police department's complaint process.
Notes: IMPORTANT: Investigative Agencies include both Civilian Oversight Agencies (COAs) and Internal Affairs (IA) Departments. If a Police Department has a NACOLE-approved COA, use their URL instead of the Department's IA URL. https://nacole.org/resources/u-s-oversight-agency-websites/ Keep in mind that a .gov URL with clear-cut police complaint information will frequently be absent or difficult to find. If that's the case, OPC's web form must clearly indicate this absence.
over_website
Text

Facebook

This is the URL address of a agency's Facebook page. If available, this will be included in each agency's OP page so that members of public may have another way to voice their complements or concerns with the department.
over_facebook
Text

Twitter

This is the URL address of a agency's Twitter page. If available, this will be included in each agency's OP page so that members of public may have another way to voice their complements or concerns with the department.
over_twitter
Text

YouTube URL

This is the URL address of a agency's YouTube page. If available, this will be included in each agency's OPC page so that members of public see this aspect of the department's web presence.
over_youtube
Text

Homepage Complaint Link?

Indicates whether or not a police department--s main homepage has a visible link to complaint information. (It should, but most will not.)
Values: Y ; N
over_homepage_complaint_link
Text

Website Complaint Information URL

^
Indicates whether or not the designated investigative agency URL (if there is one) has clear-cut info on how about how the complaints process works. (Most will not.)
over_web_complaint_info
Text

Complaint PDF

Indicates whether or not the designated police department provide a PDF of their paper complaint form online. This URL is very important for providing complainants with easy access to the form they might be required to fill out for an investigation to begin.
over_complaint_pdf
Text

Website Complaint Form URL

Indicates whether or not the designated police department website has a form to allow complaints to be submitted online. (Most will not.)
over_complaint_web_form
Text

Email

^
The valid email address for an investigative agency contact. This is the primary way we communicate with them.
over_email
Text

Work Phone

^
The contact number where investigative agency contact can be reached.
Notes: We might need to add an "extension" field for some work phone numbers.
over_phone_work
Text

Street Address

^
The first line of the postal location where an investigative agency contact receive mail. This is an integral component of an investigative agency contact's complete address.
over_address
Text

Street Address 2

^
The second line, if needed, of the postal location where an investigative agency contact receives mail. This is an integral component of an investigative agency contact's complete address.
over_address2
Text

City

^
The metropolitan area where an investigative agency contact receives mail. This is an integral component of an investigative agency contact's complete address.
over_address_city
Text

County

Indicates where an county-based investigative agency has jurisdiction. This is an integral component of next-level oversight.
over_address_county
Text

State

^
The state or district where an investigative agency contact receives mail. This is an integral component of an investigative agency contact's complete address.
over_address_state
Text

Zip Code

^
The postal code where an investigative agency contact receives mail. This is an integral component of an investigative agency contact's complete address.
over_address_zip
Text

Days Until Submission Deadline

Indicates that there is a policy stipulating a maximum number of days between when the Incident occurred and when a complaint must be submitted [or received?] by an investigative agency in order for it to be properly investigated. This information might help OP administrators prioritize the review of new complaints. For departments without a time limit for submitting complaints, this field is stored as -1.
Notes: This is used to calculate deadlines for each Complaint in the Administrator tools.
over_submit_deadline
Number

Official Form Not Required

This flags that the designated police department requires their own paper form to investigate, important for rating departments.
Values: 0 ; 1
over_official_form_not_req
Number

Anonymous Complaints Investigated?

This flags that the designated police department does accept -- and will investigate -- complaints which are submitted anonymously. This is important for department ratings and OP compatibility.
Values: 0 ; 1
over_official_anon
Number

Way to Submit: Online Form?

This flags that the designated police department currently accepts complaints when submitted via an online form, important for rating departments.
Values: 0 ; 1
over_way_sub_online
Number

Way to Submit: Email?

This flags that the designated police department currently accepts complaints when submitted via email, important for rating departments.
Values: 0 ; 1
over_way_sub_email
Number

Way to Submit: Verbal Phone?

This flags that the designated police department currently accepts complaints when submitted verbally over the phone, important for rating departments.
Values: 0 ; 1
over_way_sub_verbal_phone
Number

Way to Submit: Paper Form Mail

This flags that the designated police department currently accepts complaints when submitted on their paper form by mail, important for rating departments.
Values: 0 ; 1
over_way_sub_paper_mail
Number

Way to Submit: Requires In-Person Paper Form?

This flags that the designated police department currently accepts complaints when submitted on their paper form in-person, important for rating departments.
Values: 0 ; 1
over_way_sub_paper_in_person
Number

Way to Submit: Requires Notary?

This flags that the designated police department currently requires complaints to be submitted with a notary.
Values: 0 ; 1
over_way_sub_notary
Number

Keep Email Private?

Indicates whether or not this oversight agency is using an email address for OP which is not otherwise public. This hides it from the public department page.
Values: 0 ; 1 ( default value: 0 )
over_keep_email_private
Number
oversight_models
(over_mod_)

Oversight Models

This helper table tracks the Oversight Models associated with each Investigative Agency.
Group: People & Groups Involved In Complaint Process
Type: Subset
2 Fields Total
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

oversight_models Unique ID

over_mod_id
Number, Indexed, Primary Key

Oversight ID

The unique number of the investigative agency with this oversight model.
over_mod_oversight_id
Number

Civilian Oversight Model

Indicates subcategory of civilian oversight agency, either investigative, review, or audit. This information is essential for determining scope of a given investigative agency.
Values: Civilian Oversight Models (Definitions)
over_mod_civ_model
Number
partners
(part_)

Partners

People and organizations who access our detailed complaints data to assist their police accountability efforts and research. Information in this table is vital to the organization, because it enables us to communicate with Partners about our collaboration and service offerings.
4 Tables with Foreign Keys
Group: People & Groups Involved In Complaint Process
Type: Data
20 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

partners Unique ID

part_id
Number, Indexed, Primary Key

Partner Role

The role of this partner. This information helps us keep track of who is using our product and identify potential marketing opportunities for new partners.
Values: Customer Types (Definitions)
part_type
Number

Is Active?

Indicates whether or not this partner is currently an active participant in OpenPolice.org. A value of zero will remove the partner from public listings.
Values: 0 ; 1 ( default value: 1 )
part_status
Number

User ID

^
The primary user authentication number connected to a customer's record. This number allows customers to log into the OP system.
part_user_id
Number
Users(0,11)-(0,11)

Person Contact Info ID

The primary person contact record number connected to a customer's record. Important for linking name and basic contact information to a customer.

Bio Paragraph

A brief description of the attorney and their practice. This is an integral component of a customer's profile.
part_bio
Text

URL Slug

This defines the version of the attorney's name which is compatible and ideal to be used as part of a website URL. This is vital for creating public pages of the website which are intuitive and professional.
part_slug
Text

Company Name

^
The full name of the business or or organization a Customer represents. This is an integral component of a Customer's complete address.
part_company_name
Text

Title

^
The job position of a customer within an organization. We might use this in formal communications with customers or regarding customers.
part_title
Text

Website

^
The home page of a customer's business or organization. Might include important updated information about customers.
part_company_website
Text

Attorney Online Bio

The primary online bio for this attorney, often from their company/firm's website. Might include important information for site users.
part_bio_url
Text-Long

Assistance Requirements

A brief description of the organization's requirements for requesting assistance. This is an integral component of a customer's profile and intake process.
part_help_reqs
Text-Long

Description of Geographic Service Area

Indicates whatever description the Partner finds appropriate to more specifically describe the geographic area where they offer services. Important for enabling all site Users to find better matches for the help they need.
part_geo_desc
Text

Photo Url

URL for the primary bio photo for this attorney, often from their company/firm's website. This makes their profile page much more interesting.
part_photo_url
Text

Referral Alert Perference

Indicates how often, if at all, the partner wishes to be alerted about complaints which meet their selected conditions.
Values: Attorney Referral Alerts (Definitions)
part_alerts
Number

A/B Testing Version

^
Stores a complex string reflecting all A/B Testing variations in effect at the time of this user's experience of this node.
part_version_ab
Text

Experience Node Progress

^
Indicates the unique node ID number of the last experience node loaded during this user's experience.
part_submission_progress
Number

IP Address

^
Encrypted IP address of the current user.
part_ip_addy
Text

Tree Version Number

^
Stores the current version number of this user experience, important for tracking bugs.
part_tree_version
Text

Unique String For Record

^
This unique string is for cases when including the record ID number is not appropriate.
part_unique_str
Text

Using Mobile Device

^
Indicates whether or not the current user is interacting via a mobile deviced.
part_is_mobile
Text
partner_states
(prt_sta_)

Partner States

Partners can specify one or more states where there currently work. Information in this table enables us to alert Partners to the best matches they can actually work with.
Group: People & Groups Involved In Complaint Process
Type: Linking
2 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

partner_states Unique ID

prt_sta_id
Number, Indexed, Primary Key

Partner ID

The primary partner record number connected to a customer's record. Important for linking their active states.
prt_sta_part_id
Number
Partners(1,1)-(1,N)

State

Indicates the abbreviation for one state where this partner is active. Important for associating multiple states with one partner.
prt_sta_state
Text
partner_capac
(prt_cap_)

Partner Capacities

Partners can specify one or more sets of Capacities to list for their organization. These are important for site visitors and power users to filter for the most helpful organizations for a given situation.
Group: People & Groups Involved In Complaint Process
Type: Subset
2 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

partner_capac Unique ID

prt_cap_id
Number, Indexed, Primary Key

Part ID

The primary partner record number connected to a capacity. Important for filtering organizations for a given situation.
prt_cap_part_id
Number
Partners(1,1)-(0,N)

Organizational Capacity

Indicates the capacity with which this partner organization can potentially help OP users. Important for associating multiple capacities with one partner.
Values: Organization Capacities (Definitions)
prt_cap_capacity
Number
partner_case_types
(prt_cas_)

Partner Complaint Case Types

Partners can specify one or more sets of filters for types of complaints that match their needs. Each record links one complaint case profile to as many complaint filters as needed. Information in this table enables us to alert Partners to the best matches.
1 Table with Foreign Key
Group: People & Groups Involved In Complaint Process
Type: Subset
1 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

partner_case_types Unique ID

prt_cas_id
Number, Indexed, Primary Key

Partner ID

The primary partner ID number connected to an attorney's record. This number allows partners to load their complaint filters.
prt_cas_partner_id
Number
Partners(1,1)-(0,N)
partner_filters
(prt_flt_)

Partner Complaint Case Filters

Partners can specify one or more sets of filters to define one type of case.
Group: People & Groups Involved In Complaint Process
Type: Linking
2 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

partner_filters Unique ID

prt_flt_id
Number, Indexed, Primary Key

Case Type ID

The primary partner case type ID number connected to an attorney's case type record. This number allows partners to load their complaint filters.

Filter Condition

Indicates the type of filter as one of the requirements for a case to be qualified as a match.
Values: Attorney Complaint Filters (Definitions)
prt_flt_filter
Number
administrators
(adm_)

Administrators

OpenPolice.org staff and volunteers with special access permission to review and manage Complaints. This information allows OpenPolice.org management to measure the performance of individual Administrators.
1 Table with Foreign Key
Group: People & Groups Involved In Complaint Process
Type: Data
2 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

administrators Unique ID

adm_id
Number, Indexed, Primary Key

User ID

^
The primary user authentication number connected to an administrator's record. This number allows administrators to log into the OP system.
adm_user_id
Number
Users(11,11)-(0,11)

Person Contact Info ID

The primary person contact record number connected to a administrator's record. Important for linking name and basic contact information to a administrator.
links_officer_dept
(lnk_off_dept_)

Verified Officer-Department Links

Includes linkages between Verified Officer Records and Police Departments. This allows a single Officer to be associated with more than one Police Department over time.
Group: Complaint Linkage Tables
Type: Linking
3 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

links_officer_dept Unique ID

lnk_off_dept_id
Number, Indexed, Primary Key

Verified Officer Record ID

The unique number of the verified officer record, vital to associating with all other complaint data.
lnk_off_dept_officer_id
Number
Verified Officer Records(1,1)-(1,N)

Department ID

The unique number of the department record related to this officer, critical for identifying officers that moved to multiple departments.
lnk_off_dept_dept_id
Number
Police Departments(1,1)-(0,N)

Date Employment Verified

Indicates the date when an officer was verified as being employed at this department.
lnk_off_dept_date_verified
Date
links_complaint_dept
(lnk_com_dept_)

Complaint-Department Links

Includes linkages between Police Departments and Complaints. This allows a single Complaint to be associated with more than one Police Department.
Group: Complaint Linkage Tables
Type: Linking
2 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

Complaint ID

^
The unique number of the complaint record, vital to associating with all other complaint data.
lnk_com_dept_complaint_id
Primary Key, Number
Complaints(1,1)-(0,N)

Department ID

^
The unique number of the department record related to this complaint. This number helps us identify which department this complaint is directed and therefore which investigative agency should be contacted.
lnk_com_dept_dept_id
Composite, Primary Key, Number Indexed
Police Departments(11,11)-(0,N)
links_complaint_oversight
(lnk_com_over_)

Complaint-Oversight Links

Includes linkages between Investigative Agencies involved in a Complaint. This allows us to track the progress of Complaints being investigated by more than one Investigative Agency.
Group: Complaint Linkage Tables
Type: Linking
12 Fields Total
3 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

Complaint ID

^
The unique number of the complaint record, vital to associating with all other complaint data, vital to associating with all other complaint data.
lnk_com_over_complaint_id
Composite, Primary Key, Number Indexed
Complaints(11,11)-(11,N)

Department ID

The unique number of the police department record involved with this complaint. This number helps track the investigative progress of the investigative agencies where this complaint has been filed.
lnk_com_over_dept_id
Number
Police Departments(1,1)-(0,N)

Oversight ID

^
The unique number of the investigative agency record involved with this complaint. This number helps track the investigative progress of any investigative agencies who have jurisdiction over this complaint.
lnk_com_over_over_id
Composite, Primary Key, Number Indexed
Investigative Agencies(11,11)-(0,N)

Filed with Investigative Agency

Indicates date and time when an electronic complaint was sent to an appropriate investigative agency. This is an essential for tracking an investigative agency's response -- or non-response -- rates to OP-submitted complaints.
( default value: NULL )
lnk_com_over_submitted
Date&Time

No Response from Investigative Agency

Indicates date and time when a complainant last confirmed that they have heard no response from the appropriate investigative agency. This is essential for tracking an investigative agency's response time when acknowledging receipt of OP-submitted complaints.
lnk_com_over_still_no_response
Date&Time

Received by Investigative Agency

Indicates date and time when an electronic complaint was received by an appropriate investigative agency. This is essential for tracking an investigative agency's response time when acknowledging receipt of OP-submitted complaints.
Notes: We don't yet know how many departments will opt to electronically indicate that they've received OpenPolice.org complaints. In fact, many will likely skip this process and directly contact Complainants.
( default value: NULL )
lnk_com_over_received
Date&Time

Investigated by Investigative Agency

Indicates the date and time when the complainant was contacted by an investigative agency contact regarding the status of their complaint or to investigate any allegations contained in the complaint.
Notes: Complainants must provide OPC this information. So we must send Complainants regular follow up emails to track Investigative Agency response rates and response times.
( default value: NULL )
lnk_com_over_investigated
Date&Time

Investigative Agency Report Uploaded

Indicates the date and time when an investigative agency contact regarding the status of their complaint or to investigate any allegations contained in the complaint.
lnk_com_over_report_date
Date&Time

Investigative Agency Findings Report

The evidence record storing the final determination letter submitted by the investigative agency regarding an OP-submitted complaint. This report provides essential information for tracking how investigative agencies respond to citizen complaints.
Notes: Complainants must scan these reports and upload them to their Complaint as Evidence.
lnk_com_over_oversight_report_evidence_id
Number

Investigative Agency Complaint Number

A unique number used to identify Complaints. It is assigned by the Investigative Agency investigating an OPC-generated Complaint.
lnk_com_over_agency_complaint_number
Number

Declined by Investigative Agency

Indicates the date and time when the complainant was contacted by an investigative agency contact declining to investigate their complaint.
lnk_com_over_declined
Date&Time

Investigation Declined Letter

The evidence record storing the letter sent to the complainant by the investigative agency.
lnk_com_over_declined_evidence_id
Number
links_officer_allegations
(lnk_off_alle_)

Officer-Allegation Links

Group: Complaint Linkage Tables
Type: Linking
2 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

Officer ID

This helps to link one officer to one allegation, via the officer's primary ID.
lnk_off_alle_off_id
Composite, Primary Key, Number
Officers(0,N)-(0,N)

Allegation ID

This helps to link one Officer to one allegation, via the allegation's primary ID.
lnk_off_alle_alle_id
Composite, Primary Key, Number
Allegations(0,N)-(0,N)
links_officer_events
(lnk_off_eve_)

Officer-Event Links

Includes linkages between Officers involved in an Incident with key events that happened during an Incident. Each record links one Officer with one Event (a Stop, Search, Property, or Use of Force).
Group: Complaint Linkage Tables
Type: Linking
2 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

Officer ID

This helps to link one officer to one event (which represents one stop, search, use of force, or arrest), via the officer's primary ID.
lnk_off_eve_off_id
Composite, Primary Key, Number
Officers(0,N)-(0,N)

Event ID

This helps to link one officer to one event (which represents one stop, search, use of force, or arrest), via the officer's primary ID.
lnk_off_eve_eve_id
Composite, Primary Key, Number
Event Sequence(0,N)-(0,N)
links_civilian_force
(lnk_civ_frc_)

Civilian-Force Links

Includes linkages between Civilians and one type of force used against them.
Group: Complaint Linkage Tables
Type: Linking
2 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

links_civilian_force Unique ID

lnk_civ_frc_id
Number, Indexed, Primary Key

Civilian ID

The primary ID of the civilian record related to this force record.
lnk_civ_frc_civ_id
Number
Civilians(1,1)-(0,N)

Force ID

The primary ID of the force record related to this civilian.
lnk_civ_frc_force_id
Number
Force(1,1)-(0,N)
links_officer_vehicles
(lnk_off_vehic_)

Officer-Vehicle Links

This table includes linkages between vehicles and people. This allows a single vehicle to be associated with one or more Officer.
Group: Complaint Linkage Tables
Type: Linking
3 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

links_officer_vehicles Unique ID

lnk_off_vehic_id
Number, Indexed, Primary Key

Officer ID

The unique number of the officer record being linked in this record. Vital for associating officers with the vehicle.
lnk_off_vehic_off_id
Number
Officers(0,11)-(0,11)

Vehicle ID

The unique number of the vehicle record being linked in this record. Vital for associating civilians or officers with the vehicle.
lnk_off_vehic_vehic_id
Number
Vehicles(11,11)-(0,N)

Vehicle Role

When a vehicle is associated with multiple officers, this information can be important for identifier for which person was driving a vehicle.
Values: Vehicle Roles (Definitions)
lnk_off_vehic_role
Number
links_civilian_allegations
(lnk_civ_alle_)

Civilian-Allegation Links

Group: Complaint Linkage Tables
Type: Linking
2 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

Civilian ID

This helps to link one civilian to one allegation, via the civilian's primary ID.
lnk_civ_alle_civ_id
Composite, Primary Key, Number
Civilians(0,N)-(0,N)

Allegation ID

This helps to link one civilian to one allegation, via the allegation's primary ID.
lnk_civ_alle_alle_id
Composite, Primary Key, Number
Allegations(0,N)-(0,N)
links_civilian_events
(lnk_civ_eve_)

Civilian-Event Links

Group: Complaint Linkage Tables
Type: Linking
2 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

Civilian ID

This helps to link one officer to one event (which represents one stop, search, use of force, or arrest), via the officer's primary ID.
lnk_civ_eve_civ_id
Composite, Primary Key, Number
Civilians(0,N)-(0,N)

Event ID

This helps to link one officer to one event (which represents one stop, search, use of force, or arrest), via the officer's primary ID.
lnk_civ_eve_eve_id
Composite, Primary Key, Number
Event Sequence(0,N)-(0,N)
links_officer_force
(lnk_off_frc_)

Officer-Force Links

Includes linkages between Officers and one type of force used against them.
Group: Complaint Linkage Tables
Type: Linking
2 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

links_officer_force Unique ID

lnk_off_frc_id
Number, Indexed, Primary Key

Officer ID

The primary ID of the officer record related to this force record.
lnk_off_frc_off_id
Number
Officers(1,1)-(0,N)

Force ID

The primary ID of the force record related to this officer.
lnk_off_frc_force_id
Number
Force(1,1)-(0,N)
links_civilian_vehicles
(lnk_civ_vehic_)

Civilian-Vehicle Links

This table includes linkages between vehicles and people. This allows a single vehicle to be associated with one or more Civilian.
Group: Complaint Linkage Tables
Type: Linking
3 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

links_civilian_vehicles Unique ID

lnk_civ_vehic_id
Number, Indexed, Primary Key

Civilian ID

The unique number of the civilian record being linked in this record. Vital for associating civilians with the vehicle.
lnk_civ_vehic_civ_id
Number
Civilians(0,11)-(0,11)

Vehicle ID

The unique number of the vehicle record being linked in this record. Vital for associating civilians with the vehicle.
lnk_civ_vehic_vehic_id
Number
Vehicles(11,11)-(0,N)

Vehicle Role

When a vehicle is associated with multiple civilians, this information is important for identifier for which person was driving a vehicle.
Values: Vehicle Roles (Definitions)
lnk_civ_vehic_role
Number
compliments
(compli_)

Compliments

Reports of satisfaction that contain one or more Compliment/Commendation of Officer conduct. Compliments are vital, because they provide the essential store of information for communicating what happened during positive police encounters.
3 Tables with Foreign Keys
Group: Compliment Data Tables
Type: Data
22 Fields Total
3 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

compliments Unique ID

compli_id
Number, Indexed, Primary Key

User ID

Indicates the unique user ID number of the user owning the data stored in this record for this experience.
compli_user_id
Number
Users(0,1)-(0,N)

OPC Complaint Status

The current progress of a "complete" or "incomplete" compliment within the OP system. We use this information internally to determine next administrator actions to guide a compliment to the final status of "closed."
Values: Compliment Status (Definitions)
compli_status
Number

OPC Complaint Type

The administrator-selected category for newly-submitted compliment records. Essential for determining where and how new compliment records are stored and shared.
compli_type
Number

Incident ID

The unique number of the Incident record related to this compliment. This number helps us identify the incident that this compliment documents.
compli_incident_id
Number
Incidents(1,1)-(1,1)

Scene ID

The unique number of the scene record related to this compliment. This number helps us identify the scene that this compliment documents.
compli_scene_id
Number
Scenes(1,1)-(1,1)

Privacy Setting

User-selected category for compliment records. This defines what personally identifiable information (PII) is publicly shared.
Values: Privacy Types (Definitions)
compli_privacy
Number

Summary (Narrative)

The complainant narrative describes the chronological sequence of key incident events and commendations. This story brings to life to an otherwise clinical and legalistic compliment document.
compli_summary
Text-Long

How did you hear about OP.org?

Indicates how the complainant heard about OpenPolice.org. This is important for internal understanding of OP marketing strategies.
compli_how_hear
Text

User Suggestions and Feedback

Indicates any suggestions or feedback the user has for us at the end of the process. This is important to let us know if we missed anything, etc.
compli_feedback
Text-Long

URL Slug

This defines the version of the compliment's social media headline which is compatible and ideal to be used as part of a website URL. This is vital for creating public pages of the website which are intuitive and professional.
compli_slug
Text

Compliment Notes

Additional annotations related to this compliment. Might add additional information or context regarding the compliment.
compli_notes
Text-Long

Record Submitted to OpenPolice.org

Date and time when this compliment was completed and submitted to the OP database.
compli_record_submitted
Date&Time

Submission Progress

Indicates current progress of an "incomplete" compliment. Important for identifying problem areas which might cause compliments to be left unfinished.
compli_submission_progress
Text

A/B Testing Version

^
Stores a complex string reflecting all A/B Testing variations in effect at the time of this user's experience of this node.
compli_version_ab
Text

Tree Version

Indicates which precise version number of this software was running at the time of this compliment's submission. This is important for internal use, quality control, and potential debugging.
compli_tree_version
Text

Honey Pot (to catch spam bots)

Indicates whether or not a spam bot filled in the form field which human users cannot see. This is important for quickly categorizing compliments as spam.
compli_honey_pot
Text

Using Mobile Device

Indicates whether or not this compliment was started on a mobile device. This is important for tracking trends of usage, and potential debugging.
Values: 0 ; 1
compli_is_mobile
Number

Unique String

This unique, randomly generated string can be important for creating custom URLs which are more secure than using the compliment ID#.
compli_unique_str
Text

IP Address (encrypted)

This logs an encrypted copy of the complainants IP Address. This is important for checking if multiple compliments were submitted from the same location, especially when filtering compliments categorized as spam or abuse.
compli_ip_addy
Text

OpenPolice.org Compliment ID#

Indicates the unique identification number for referring to this compliment in public and in URLs. Important for making public IDs counting only completed compliments, not every partial submission.
compli_public_id
Number

Is Demo/Test Run

Indicates whether or not this user started the compliment process just to demo it.
Values: 0 ; 1 ( default value: 0 )
compli_is_demo
Number

Share with Trusted Other Databases

Indicates whether or not the complimentor consents to share their compliment data with other databases trusted by OP.
Values: 0 ; 1
compli_share_data
Number
civ_compliment
(civ_comp_)

Civilians Compliments

Individuals who are directly impacted by, observed, or are reporting on an police incident. This information allows us to identify and contact individuals who've been impacted by alleged incident.
Group: Compliment Data Tables
Type: Data
6 Fields Total
4 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

civ_compliment Unique ID

civ_comp_id
Number, Indexed, Primary Key

Compliment ID

The unique number of the compliment record related to this civilian's record. Vital for associating this civilian with other compliment data.
civ_comp_compliment_id
Number
Compliments(1,1)-(1,N)

User ID

The primary user authentication number connected to a civilian's record. Important for allowing users to log into OP to complete compliments or update compliments.
civ_comp_user_id
Number
Users(0,1)-(0,N)

Creator of this complaint?

Indicates that this civilian is the person who created the compliment. This information helps us identity complainants who are also subjects or witnesses.
Values: Y ; N ( default value: N )
civ_comp_is_creator
Text

Civilian's role in incident?

Indicates the category of a civilian. This information is essential for understanding whether a given civilian was a subject of an commendation or a witness to the commendation.
Values: Witness ; Subject ; Neither ; Impressed
civ_comp_role
Text

Person Contact Info ID

The primary person contact record number connected to a civilian's record. Important for linking name and basic contact information to a civilian.
civ_comp_person_id
Number
Person Contact Info(1,1)-(1,1)

Physical Description ID

The primary physical description record number connected to a civilian's record. Important for linking name and basic contact information to a civilian.
civ_comp_phys_desc_id
Number
Physical Descriptions(1,1)-(1,1)
links_compliment_dept
(lnk_compli_dept_)

Compliment-Department Links

Includes linkages between Police Departments and Compliments. This allows a single Compliment to be associated with more than one Police Department.
Group: Compliment Data Tables
Type: Linking
2 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

links_compliment_dept Unique ID

lnk_compli_dept_id
Number, Indexed, Primary Key

Compliment ID

The unique number of the compliment record, vital to associating with all other compliment data.
lnk_compli_dept_compliment_id
Number
Compliments(1,1)-(0,N)

Department ID

The unique number of the department record related to this complaint. This number helps us identify which department this complaint is directed and therefore which investigative agency should be contacted.
lnk_compli_dept_dept_id
Number
Police Departments(1,1)-(0,N)
links_compliment_oversight
(lnk_compli_over_)

Compliment-Oversight Links

Includes linkages between Investigative Agencies involved in a Compliment. This allows us to track the progress of Compliments being investigated by more than one Investigative Agency.
Group: Compliment Data Tables
Type: Linking
5 Fields Total
3 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

links_compliment_oversight Unique ID

lnk_compli_over_id
Number, Indexed, Primary Key

Compliment ID

The unique number of the compliment record, vital to associating with all other compliment data, vital to associating with all other compliment data.
lnk_compli_over_compliment_id
Number
Compliments(1,1)-(0,N)

Department ID

The unique number of the police department record involved with this compliment. This number helps track the investigative progress of the investigative agencies where this compliment has been filed.
lnk_compli_over_dept_id
Number
Police Departments(1,1)-(0,N)

Oversight ID

The unique number of the investigative agency record involved with this compliment. This number helps track the investigative progress of any investigative agencies who have jurisdiction over this compliment.
lnk_compli_over_over_id
Number
Investigative Agencies(1,1)-(0,N)

Submitted to Investigative Agency

Indicates date and time when an electronic compliment was sent to an appropriate investigative agency. This is an essential for tracking an investigative agency's response -- or non-response -- rates to OP-submitted compliments.
lnk_compli_over_submitted
Date&Time

Received by Investigative Agency

Indicates date and time when an electronic compliment was received by an appropriate investigative agency. This is essential for tracking an investigative agency's response time when acknowledging receipt of OP-submitted compliments.
lnk_compli_over_received
Date&Time
visitors
(vis_)

Site Visitors

Represent user sessions while visiting the web site. This is useful for tracking visitor responses, searches, etc.
Group: Internal Infrastructure
Type: Data
7 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

visitors Unique ID

vis_id
Number, Indexed, Primary Key

User ID

^
Indicates the unique user ID number of the user owning the data stored in this record for this experience.
vis_user_id
Number
Users(11,11)-(11,11)

Experience Node Progress

^
Indicates the unique node ID number of the last experience node loaded during this user's experience.
vis_submission_progress
Number

Tree Version Number

^
Stores the current version number of this user experience, important for tracking bugs.
vis_tree_version
Text

A/B Testing Version

^
Stores a complex string reflecting all A/B Testing variations in effect at the time of this user's experience of this node.
vis_version_ab
Text

Unique String For Record

^
This unique string is for cases when including the record ID number is not appropriate.
vis_unique_str
Text

IP Address

^
Encrypted IP address of the current user.
vis_ip_addy
Text

Using Mobile Device

^
Indicates whether or not the current user is interacting via a mobile deviced.
vis_is_mobile
Text
privilege_profiles
(priv_)

Privileges Profiles

Beyond default privileges for various types of system users, this table includes explicit privileges to enable access to specific system data. This is vital in allowing User IDs (which are associated with different user types) to be granted access to specific complaints or sets of complaints.
Group: Internal Infrastructure
Type: Linking
4 Fields Total
3 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

User ID

^
The primary user authentication number connected to this privilege profile, vital to associating permissions with a specific system user.
priv_user_id
Composite, Primary Key, Number
Users(11,11)-(0,N)

Complaint ID

^
The unique number of the complaint record related to this privilege profile, vital to associating permissions with a specific complaint (instead of an entire department).
priv_complaint_id
Composite, Primary Key, Number Indexed
Complaints(0,11)-(0,N)

Department ID

^
The unique number of the department record related to this privilege profile, vital to associating permissions with all complaints tied to a police department (instead of a single complaint).
priv_dept_id
Composite, Primary Key, Number Indexed
Police Departments(0,11)-(0,N)

Data Access Level

The permission specifications for this record's user's access to either one complaint or one department's complaints. This is vital for determining exactly what privacy rules apply for a given user attempting to access a given complaint.
Notes: Details TBD, with approximate categories suggested for now.
Values: View w/out Names ; View ; Edit ; Manage
priv_access_level
Text
users
()

Users

This represents the Laravel Users table, but will not actually be implemented by SurvLoop as part of the database installation.
admin_actions
(adm_act_)

Administrator Actions

Includes administrator edits to all user data and incident reports. This information provides a complete history of data changes so any human, system, or security errors can be investigated and reversed if necessary.
Group: Logging Data Tables
Type: Validation
5 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

admin_actions Unique ID

adm_act_id
Number, Indexed, Primary Key

User ID

^
The primary user authentication number connected to this action, vital for tracking system user behavior and system security.
adm_act_user_id
Number
Users(11,11)-(0,N)

Table Edited

Name of the table whose contents are being altered by a system user (usually an administrator), at any point after a complaint has been submitted. This is important tracking the table being altered.
adm_act_table
Text

Table Record ID

The unique primary ID number of the record in the table whose contents are being altered by a system user (usually an administrator), at any point after a complaint has been submitted. This is important tracking the specific record being altered.
adm_act_record_id
Number

Old Data Values

A data dump of the initial value of all the table record's fields' contents, or at least those with values being changed by a system user. This is vital to provide breadcrumbs to correct potential problems.
Notes: Data formatting TBD, but could be like JSON or XML.
adm_act_old_data
Text-Long

New Data Values

A data dump of the newly updated values of all the table record's fields' contents, or at least those with values being changed by a system user. This is vital to provide breadcrumbs to correct potential problems.
Notes: Data formatting TBD, but could be like JSON or XML.
adm_act_new_data
Text-Long
z_edit_departments
(zed_dept_)

Edits: Police Departments

Each record stores a copy of individual edits made to unique records in the Departments table.
1 Table with Foreign Key
Group: Record Edit Histories
Type: Validation
2 Fields Total
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

z_edit_departments Unique ID

zed_dept_id
Number, Indexed, Primary Key

User ID

Indicates the unique user ID number of the user editing the original departments record.
zed_dept_user_id
Number

Duration of Edit

How many seconds the user spent editing this record.
zed_dept_duration
Number
z_edit_oversight
(zed_over_)

Edits: Investigative Agencies

Each record stores a copy of individual edits made to unique records in the Oversight table.
Group: Record Edit Histories
Type: Validation
5 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

z_edit_oversight Unique ID

zed_over_id
Number, Indexed, Primary Key

Department Edit Record ID

The unique ID number of the edit record tied to this extended edit record.
zed_over_zed_dept_id
Number
Edits: Police Departments(1,1)-(1,2)

Completed Online Research

Indicates whether or not the volunteer completed the online portion of the police department research process.
Values: 0 ; 1
zed_over_online_research
Number

Made Department Call

Indicates whether or not the volunteer completed the research with a phone call to the police department.
Values: 0 ; 1
zed_over_made_dept_call
Number

Made Internal Affairs Call

Indicates whether or not the volunteer completed the research with a phone call to the internal affairs office.
Values: 0 ; 1
zed_over_made_ia_call
Number

Volunteer Notes

Provides a space for a volunteer to leave any significant notes about this department (or research attempt) for future volunteers or staff.
zed_over_notes
Text-Long
z_volun_stat_days
(volun_stat_)

Volunteers: Daily Stats

Each record stores daily statistics for volunteer activity.
Group: Internal Volunteer Data
Type: Validation
15 Fields Total
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

z_volun_stat_days Unique ID

volun_stat_id
Number, Indexed, Primary Key

Date

Indicates the date for which this record's totals are cached.
volun_stat_date
Date

Total Signups

Indicates the total number of new volunteer signups on this date.
( default value: 0 )
volun_stat_signups
Number

Total Logins

Indicates the total number of new volunteer logins on this date.
( default value: 0 )
volun_stat_logins
Number

Total Unique Research Users

Indicates the total number of unique volunteers making research edits on this date.
( default value: 0 )
volun_stat_users_unique
Number

Total Unique Department Researched

Indicates the total number of unique departments receiving research edits on this date.
( default value: 0 )
volun_stat_depts_unique
Number

Total Online Research Edits

Indicates the total number of online research edits on this date.
( default value: 0 )
volun_stat_online_research
Number

Total Department Call Edits

Indicates the total number of department call edits on this date.
( default value: 0 )
volun_stat_calls_dept
Number

Total Internal Affairs Call Edits

Indicates the total number of internal affairs call edits on this date.
( default value: 0 )
volun_stat_calls_ia
Number

Total Call Edits

Indicates the total number of department and internal affairs call edits on this date.
( default value: 0 )
volun_stat_calls_tot
Number

Total Edits Saved

Indicates the total number of edits saved on this date.
( default value: 0 )
volun_stat_total_edits
Number

Total Online Research Volunteer Edits

Indicates the total number of online research edits by volunteers on this date.
( default value: 0 )
volun_stat_online_research_v
Number

Total Department Call Volunteer Edits

Indicates the total number of department call edits by volunteers on this date.
( default value: 0 )
volun_stat_calls_dept_v
Number

Total Internal Affairs Call Volunteer Edits

Indicates the total number of internal affairs call edits by volunteers on this date.
( default value: 0 )
volun_stat_calls_ia_v
Number

Total Call Volunteer Edits

Indicates the total number of department and internal affairs call edits by volunteers on this date.
( default value: 0 )
volun_stat_calls_tot_v
Number

Total Edits Saved by Volunteers

Indicates the total number of edits by volunteers saved on this date.
( default value: 0 )
volun_stat_total_edits_v
Number
z_volun_user_info
(user_info_)

Volunteers: User Info

Each record stores extra information related to one volunteer User.
Group: Internal Volunteer Data
Type: Data
8 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

z_volun_user_info Unique ID

user_info_id
Number, Indexed, Primary Key

User ID

Indicates the unique user ID number of the user record this table extends.
user_info_user_id
Number
Users(1,1)-(1,1)

Person Contact Record ID

Indicates the unique person contact ID number for the record used to store this system user's personal information within OP's data table.
user_info_person_contact_id
Number
Person Contact Info(1,1)-(1,1)

Stars Total

Indicates the total number of stars this volunteer has earned.
( default value: 0 )
user_info_stars
Number

Stars from Online Research

Indicates the total number of stars this volunteer has earned from completing online research.
( default value: 0 )
user_info_stars1
Number

Stars from Department Calls

Indicates the total number of stars this volunteer has earned from calling and speaking with police departments.
( default value: 0 )
user_info_stars2
Number

Stars from Interal Affairs Calls

Indicates the total number of stars this volunteer has earned from calling and speaking with internal affairs offices.
( default value: 0 )
user_info_stars3
Number

Unique Departments

Indicates the number of unique police departments for which this volunteer contributed research.
( default value: 0 )
user_info_depts
Number

Average Time Researching Department

Indicates average time (in seconds) this volunteer spend editing each department they worked on.
( default value: 0 )
user_info_avg_time_dept
Number
z_complaint_reviews
(com_rev_)

Complaint Review

Each record stores feedback from a system admin, the complaint owner, or the department.
Group: Internal Volunteer Data
Type: Data
20 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info

z_complaint_reviews Unique ID

com_rev_id
Number, Indexed, Primary Key

Complaint ID

Indicates the unique complaint ID number of the complaint being reviewed.
com_rev_complaint
Number
Complaints(1,1)-(0,N)

User ID

Indicates the unique user ID number for the person performing this complaint review.
com_rev_user
Number
Users(1,1)-(0,N)

Review Date

Indicates the date when this complaint review was submitted.
com_rev_date
Date

Review Type

Indicates which type of complaint review is being submitted.
com_rev_type
Text

Complaint Type

Indicates the type of complaint confirmed in this review.
Values: OPC Staff/Internal Complaint Type (Definitions)
com_rev_complaint_type
Number

Review Status

Indicates which new status has been chosen for this complaint, if the current user has permissions.
com_rev_status
Text

Next Action After Review

Indicates the reviewers recommended next action for this complaint.
com_rev_next_action
Text

User Note

Indicates any other notes or information the current user wants to share with their review.
com_rev_note
Text-Long

Focused On One Incident

Indicates whether or not this complaint remained focused on one Incident.
Values: 0 ; 1
com_rev_one_incident
Number

Has Civilian Contact

Indicates whether or not this complaint includes at least one civilian contact for investigation.
Values: 0 ; 1
com_rev_civilian_contact
Number

At Least One Officer

Indicates whether or not this complaint includes information about at least one officer to be investigated.
Values: 0 ; 1
com_rev_one_officer
Number

Has At Least One Allegation

Indicates whether or not this complaint includes at least one allegation for investigation, and is a story about police misconduct.
Values: 0 ; 1
com_rev_one_allegation
Number

Has Evidence Uploaded

Indicates whether or not this complaint includes some uploaded evidence supporting their allegations.
Values: 0 ; 1
com_rev_evidence_uploaded
Number

English Skill Level

Indicates the reviewer's opinion about the complainant's skill level with English writing.
Values: 0 ; 1 ; 2 ; 3 ; 4
com_rev_english_skill
Number

Readability Level

Indicates the reviewer's opinion about how easy it was to read the complaint.
Values: 0 ; 1 ; 2 ; 3 ; 4
com_rev_readability
Number

Consistency Level

Indicates the reviewer's opinion about the complainant's level of consistency, meaning freedom from variation or contradiction.
Values: 0 ; 1 ; 2 ; 3 ; 4
com_rev_consistency
Number

Seems Realistic

Indicates the reviewer's opinion about how realistic the complainant's story is.
Values: 0 ; 1 ; 2 ; 3 ; 4
com_rev_realistic
Number

Outrage Level

Indicates the reviewer's personal level of outrage about their understanding of the incident.
Values: 0 ; 1 ; 2 ; 3 ; 4
com_rev_outrage
Number

Explicit Language Used

Indicates whether or not the reviewer thinks this complaint contains explicit language.
Values: 0 ; 1
com_rev_explicit_lang
Number

Contains Graphic Content

Indicates whether or not the reviewer thinks this complaint contains particularly graphic content.
Values: 0 ; 1
com_rev_graphic_content
Number