Database Design | OpenPolice.org

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.

Incoming: Allegations^{(0,N)-(11,11)}, Civilians^{(11,N)-(0,11)}, Incidents^{(11,11)-(11,N)}, Privileges Profiles^(0,N)-(0,11), Surveys^(0,N)-(0,11), Officers^{(11,N)-(0,11)}, Complaint Notes^{(0,N)-(11,11)}, Event Sequence^{(0,N)-(11,11)}, Complaint-Department Links^(0,N)-(1,1), Complaint-Oversight Links^{(11,N)-(11,11)}, Silver Allegations^{(11,11)-(11,11)}, Civilian Body Weapons^(0,N)-(1,1), Complaint Review^(0,N)-(1,1), Vehicles^(0,N)-(1,1), Force^(0,N)-(1,1), Arrests^(0,1)-(1,1), Searches^(0,1)-(1,1), Stops^(0,1)-(1,1)

18 Tables with Foreign Keys

Group: Complaint Data Tables

Type: Data

45 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
Experience Node Progress ^{^} Indicates the unique Node ID number of the last Experience Node loaded during this User's Experience. com_submission_progress
User ID ^{^} Indicates the unique User ID number of the User owning the data stored in this record for this Experience. com_user_id
Tree Version Number ^{^} Stores the current version number of this User Experience, important for tracking bugs. com_tree_version
User ID ^{^} Indicates the unique User ID number of the User owning the data stored in this record for this Experience. com_user_id
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
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 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 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 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 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 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
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
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
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
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
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
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
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
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
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
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
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
List of Allegations Caches a simple written list of this complaint's allegations, separated by commas. com_alleg_list
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
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
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
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
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
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
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
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
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
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
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
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 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 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
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
Submitted to OpenPolice.org Date and time when this complaint was completed and submitted to the OP database. com_record_submitted 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
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
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
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
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
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
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
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
Wants an Attorney, But Filing Anyway Indicates whether or not this complainant wants to go ahead with filing and publishing, despite wanting help finding an attorney. Values: 0 ; 1 com_want_attorney_but_file
Reason for switching from attorney track to file & publish Values: Complaint Switching from Attorney to File Track (Definitions) com_switch_to_file

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.

Incoming: Complaints^(1,N)-(1,1), Compliments^(1,1)-(1,1)

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 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
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
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
City ^{^} The metropolitan area where an incident occurred. This is an integral component of a complete Incident location address. inc_address_city
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
Zip Code ^{^} The postal code where an incident occurred. This is an integral component of a complete incident location address. inc_address_zip
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
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
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
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
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
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
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

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.

Incoming: Compliments^(1,1)-(1,1), Complaints^(1,1)-(1,1)

2 Tables with Foreign Keys

Group: Complaint Data Tables

Type: Data

17 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
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
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
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
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
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
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
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
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
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
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
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
Attorney First Name Indicates the first name of the attorney of the complainant or victim, if applicable. scn_attorney_first_name
Attorney Last Name Indicates the last name of the attorney of the complainant or victim, if applicable. scn_attorney_last_name
Attorney Email Indicates the email address of the attorney of the complainant or victim, if applicable. scn_attorney_email
Why were no officers described? Indicates why the complainant was not able to describe any officers involved in their complaint. Values: Reasons For No Officers (Definitions) scn_why_no_officers
Describe why you were unable to add an officer Indicates any other reasons this complainant was not able to describe any officers involved in their complaint. scn_why_no_officers_other

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 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Force used against animal? Indicates whether or not force was used against an animal. Values: Y ; N ; ? alle_sil_animal_force

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.

Incoming: Civilian-Allegation Links^(0,N)-(0,N), Officer-Allegation Links^(0,N)-(0,N)

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 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
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 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
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

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.

Incoming: Allegations^(0,N)-(0,11), Force^{(0,11)-(11,11)}, Arrests^{(0,11)-(11,11)}, Searches^{(0,11)-(11,11)}, Stops^{(0,11)-(11,11)}, Officer-Event Links^(0,N)-(0,N), Civilian-Event Links^(0,N)-(0,N)

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 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

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.

Incoming: Stop Reasons^{(0,N)-(11,11)}

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 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 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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 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
Officer gave alcohol breath test? Indicates whether or not an officer administered an alcohol breath test. Values: Y ; N ; ? stop_breath_alcohol
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
Officer gave marijuana breath test? Indicates whether or not an officer administered a marijuana breath test. Values: Y ; N ; ? stop_breath_cannabis
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
Officer collected saliva? Indicates whether or not an officer collected someone's saliva. Values: Y ; N ; ? stop_saliva_test
Officer gave other sobriety tests? Indicates whether or not an officer administered other sobriety tests. Values: Y ; N ; ? stop_sobriety_other
Description of other sobriety tests Description of other sobriety tests administered by officer. stop_sobriety_other_describe

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 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

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.

Incoming: Search Contraband^{(0,N)-(11,11)}, Search Seizures^{(0,N)-(11,11)}

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 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 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
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
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
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
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
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
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
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
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
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
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
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
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
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
Property seized? Indicates that officers took property from a subject. Important for evaluating seizure allegations and forfeiture trends. Values: Y ; N ; ? srch_seized
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
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
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

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 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

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 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

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 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 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
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
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
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
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
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
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
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
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

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.

Incoming: Force Sub-Type^{(0,N)-(11,11)}, Force Body Parts^(0,N)-(0,11), Civilian-Force Links^(0,N)-(1,1), Officer-Force Links^(0,N)-(1,1)

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 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 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
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
Type 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
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
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
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
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
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
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
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
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

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 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

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 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

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 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

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 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

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.

Incoming: Stops^{(0,11)-(0,11)}, Injury Body Parts^(0,N)-(0,11)

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 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
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
Injury Description Additional narrative information about a specific injury. Important for evaluating allegations of excessive force. inj_description
Injury form complete? Indicates whether or not the complainant has gone through this injury record yet. Values: 0 ; 1 inj_done

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 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

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 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
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
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
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
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
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
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
Doctor Phone The contact number of a doctor who treated an injury. Important for communicating with doctors who treated injuries. inj_care_doctor_phone
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
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
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
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
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
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
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
Injury Care Form Done Indicates whether or not the complainant has gone through this injury care record yet. Values: 0 ; 1 inj_care_done

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 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 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 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 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

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.

Incoming: Injuries^(0,N)-(0,11), Injury Care^{(0,N)-(11,11)}, Civilian-Allegation Links^(0,N)-(0,N), Civilian-Event Links^(0,N)-(0,N), Civilian-Vehicle Links^{(0,11)-(0,11)}, Charges^(0,N)-(1,1), Civilian-Force Links^(0,N)-(1,1)

7 Tables with Foreign Keys

Group: People Involved In Complaint

Type: Data

24 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 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 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
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
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_person_id Person Contact Info^{(11,11)-(11,11)}
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 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
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
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
Occupation The profession of a civilian. This information can help investigators identify civilians. civ_occupation
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
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
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
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
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
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
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
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
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
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
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
Citation ID# Citation ID number issued to a subject. Important for tracking down more information during investigations. civ_citation_number
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
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. Values: Y ; N ; ? civ_no_charges_filed

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.

Incoming: Officer-Vehicle Links^{(0,11)-(0,11)}, Officer-Allegation Links^(0,N)-(0,N), Officer-Event Links^(0,N)-(0,N), , Officer-Force Links^(0,N)-(1,1)

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 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 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
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 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. off_person_id Person Contact Info^{(11,11)-(11,11)}
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 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
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
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
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
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
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
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
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
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 ; Not sure off_duty_status
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
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
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
Received compliments? Indicates whether or not the complainant completed a compliment page for this officer. Values: Y ; N off_gave_compliment

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.

Incoming: Officers^(0,N)-(1,1), Verified Officer-Department Links^(1,N)-(1,1)

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
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 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 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 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 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
Unique String For Record ^{^} This unique string is for cases when including the record ID number is not appropriate. off_ver_unique_str
Experience Node Progress ^{^} Indicates the unique node ID number of the last experience node loaded during this user's experience. off_ver_submission_progress
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
Tree Version Number ^{^} Stores the current version number of this user experience, important for tracking bugs. off_ver_tree_version
IP Address ^{^} Encrypted IP address of the current user. off_ver_ip_addy
Using Mobile Device ^{^} Indicates whether or not the current user is interacting via a mobile deviced. off_ver_is_mobile
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 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.

Incoming: Civilians^{(11,11)-(11,11)}, Officers^{(11,11)-(11,11)}, Administrators^{(11,11)-(11,11)}, Partners^{(11,11)-(11,11)}, Volunteers: User Info^(1,1)-(1,1), Civilians Compliments^(1,1)-(1,1), Verified Officer Records^(1,1)-(1,1)

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
First Name ^{^} The legal given name of persons that we use in all formal communications with persons or regarding complaints. prsn_name_first
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
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
Last Name ^{^} The legal surname of persons that we use in all formal communications with or regarding complaints. prsn_name_last
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
Email ^{^} The valid email address provided by or for a person. This is the primary way we communicate with them. prsn_email
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
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
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
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
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
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
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
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
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
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
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 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.

Incoming: Civilians^{(0,11)-(0,11)}, Officers^{(0,11)-(0,11)}, Physical Descriptions Race^{(0,N)-(11,11)}, Civilians Compliments^(1,1)-(1,1)

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
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
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
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
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
Physical Description ^{^} The attributes of any prominent physical characteristics on a person. This information might help verify a person's identity. phys_general_desc

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 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

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.

Incoming: Officer-Vehicle Links^{(0,N)-(11,11)}, Civilian-Vehicle Links^{(0,N)-(11,11)}

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 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
Type of Vehicle ^{^} Indicates what type of vehicle was involved during this incident. Values: Transportation (Definitions) vehic_transportation
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
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
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
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
License Plate Number ^{^} The tag numbers on the civilian's vehicle. This information might help verify a civilian's identity. vehic_vehicle_licence
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

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
First Name Indicates the first name of the complainant signing up to beta test. beta_name
Last Name Indicates the last name of the complainant signing up to beta test. beta_last_name
Year Indicates what year the beta test user's incident happened. beta_year
Incident Narrative Indicates the complainant's brief preview of their incident description. beta_narrative
Referred From Indicates where this beta tester got the link to the beta test signup, maybe via Newsletter, Facebook, YouTube, etc. beta_how_hear
Date Invited Indicates the date and time this beta test user was invited to use OP. beta_invited
User ID ^{^} Indicates the unique user ID number of the user owning the data stored in this record for this experience. beta_user_id 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
Experience Node Progress ^{^} Indicates the unique node ID number of the last experience node loaded during this user's experience. beta_submission_progress
IP Address ^{^} Encrypted IP address of the current user. beta_ip_addy
Tree Version Number ^{^} Stores the current version number of this user experience, important for tracking bugs. beta_tree_version
Unique String For Record ^{^} This unique string is for cases when including the record ID number is not appropriate. beta_unique_str
Using Mobile Device ^{^} Indicates whether or not the current user is interacting via a mobile deviced. beta_is_mobile

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.

Incoming: Privileges Profiles^(0,N)-(0,11), Complaint-Department Links^{(0,N)-(11,11)}, Officers^{(0,N)-(11,11)}, Investigative Agencies^{(0,N)-(11,11)}, Compliment-Department Links^(0,N)-(1,1), Complaint-Oversight Links^(0,N)-(1,1), Compliment-Oversight Links^(0,N)-(1,1), Verified Officer-Department Links^(0,N)-(1,1)

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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
User ID ^{^} Indicates the unique user ID number of the user owning the data stored in this record for this experience. dept_user_id 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
Tree Version Number ^{^} Stores the current version number of this user experience, important for tracking bugs. dept_tree_version
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
Unique String For Record ^{^} This unique string is for cases when including the record ID number is not appropriate. dept_unique_str
IP Address ^{^} Encrypted IP address of the current user. dept_ip_addy
Using Mobile Device ^{^} Indicates whether or not the current user is interacting via a mobile deviced. dept_is_mobile
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
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
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

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.

Incoming: Complaint-Oversight Links^{(0,N)-(11,11)}, Compliment-Oversight Links^(0,N)-(1,1)

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
Basic Oversight Level Indicates the basic level of oversight tracked by this record. Values: Investigative Level (Definitions) over_civ_model
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 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 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Email ^{^} The valid email address for an investigative agency contact. This is the primary way we communicate with them. over_email
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
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
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
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
County Indicates where an county-based investigative agency has jurisdiction. This is an integral component of next-level oversight. over_address_county
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
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
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
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
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
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
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
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
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
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
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
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

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
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

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.

Incoming: Complaints^(0,N)-(0,11), Partner Complaint Case Types^(0,N)-(1,1), Partner States^(1,N)-(1,1), Partner Capacities^(0,N)-(1,1)

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
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
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 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. part_person_id Person Contact Info^{(11,11)-(11,11)}
Bio Paragraph A brief description of the attorney and their practice. This is an integral component of a customer's profile. part_bio
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
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
Title ^{^} The job position of a customer within an organization. We might use this in formal communications with customers or regarding customers. part_title
Website ^{^} The home page of a customer's business or organization. Might include important updated information about customers. part_company_website
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
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
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
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
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
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
Experience Node Progress ^{^} Indicates the unique node ID number of the last experience node loaded during this user's experience. part_submission_progress
IP Address ^{^} Encrypted IP address of the current user. part_ip_addy
Tree Version Number ^{^} Stores the current version number of this user experience, important for tracking bugs. part_tree_version
Unique String For Record ^{^} This unique string is for cases when including the record ID number is not appropriate. part_unique_str
Using Mobile Device ^{^} Indicates whether or not the current user is interacting via a mobile deviced. part_is_mobile

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 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

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 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

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.

Incoming: Partner Complaint Case Filters^(0,N)-(1,1)

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 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. prt_flt_case_id Partner Complaint Case Types^(1,1)-(0,N)
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

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.

Incoming: Complaints^(0,N)-(0,11)

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 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. adm_person_id Person Contact Info^{(11,11)-(11,11)}

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 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 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

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, 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, 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, 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 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, 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
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
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
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
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
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
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
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
Investigation Declined Letter The evidence record storing the letter sent to the complainant by the investigative agency. lnk_com_over_declined_evidence_id

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, 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, 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, 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, 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 Civilians^(1,1)-(0,N)
Force ID The primary ID of the force record related to this civilian. lnk_civ_frc_force_id 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 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 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

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, 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, 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, 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, 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 Officers^(1,1)-(0,N)
Force ID The primary ID of the force record related to this officer. lnk_off_frc_force_id 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 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 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

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.

Incoming: Compliment-Department Links^(0,N)-(1,1), Civilians Compliments^(1,N)-(1,1), , Compliment-Oversight Links^(0,N)-(1,1)

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 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
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
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 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 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
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
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
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
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
Compliment Notes Additional annotations related to this compliment. Might add additional information or context regarding the compliment. compli_notes
Record Submitted to OpenPolice.org Date and time when this compliment was completed and submitted to the OP database. compli_record_submitted
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
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
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
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
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
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
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
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
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
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

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 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 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
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
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 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 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 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 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 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 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 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
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

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 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
Tree Version Number ^{^} Stores the current version number of this user experience, important for tracking bugs. vis_tree_version
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
Unique String For Record ^{^} This unique string is for cases when including the record ID number is not appropriate. vis_unique_str
IP Address ^{^} Encrypted IP address of the current user. vis_ip_addy
Using Mobile Device ^{^} Indicates whether or not the current user is interacting via a mobile deviced. vis_is_mobile

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, 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, 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, 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

users
()

Users

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

Incoming: , Investigative Agencies^{(0,11)-(0,11)}, Administrators^{(0,11)-(11,11)}, Partners^{(0,11)-(0,11)}, Privileges Profiles^{(0,N)-(11,11)}, Administrator Actions^{(0,N)-(11,11)}, , Surveys^{(0,11)-(0,11)}, Complaint Notes^{(0,N)-(11,11)}, Civilians^{(0,11)-(0,11)}, Complaints^(0,N)-(0,1), Site Visitors^{(11,11)-(11,11)}, Compliments^(0,N)-(0,1), Person Contact Info^(0,1)-(0,1), Police Departments^(0,N)-(0,1), Volunteers: User Info^(1,1)-(1,1), Complaint Review^(0,N)-(1,1), Beta Test Signups^{(11,11)-(11,11)}, Civilians Compliments^(0,N)-(0,1), Verified Officer Records^{(11,11)-(11,11)}

18 Tables with Foreign Keys

Field Name (in English), Description, Notes, Value Options Field Name (in Database), Data Type, Key Info
Users Unique ID id Number, Indexed, Primary Key
Username name Text
Email Address email Text

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 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
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
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
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

z_edit_departments
(zed_dept_)

Edits: Police Departments

Each record stores a copy of individual edits made to unique records in the Departments table.

Incoming: Edits: Investigative Agencies^(1,2)-(1,1)

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
Duration of Edit How many seconds the user spent editing this record. zed_dept_duration

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 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
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
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
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

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
Total Signups Indicates the total number of new volunteer signups on this date. ( default value: 0 ) volun_stat_signups
Total Logins Indicates the total number of new volunteer logins on this date. ( default value: 0 ) volun_stat_logins
Total Unique Research Users Indicates the total number of unique volunteers making research edits on this date. ( default value: 0 ) volun_stat_users_unique
Total Unique Department Researched Indicates the total number of unique departments receiving research edits on this date. ( default value: 0 ) volun_stat_depts_unique
Total Online Research Edits Indicates the total number of online research edits on this date. ( default value: 0 ) volun_stat_online_research
Total Department Call Edits Indicates the total number of department call edits on this date. ( default value: 0 ) volun_stat_calls_dept
Total Internal Affairs Call Edits Indicates the total number of internal affairs call edits on this date. ( default value: 0 ) volun_stat_calls_ia
Total Call Edits Indicates the total number of department and internal affairs call edits on this date. ( default value: 0 ) volun_stat_calls_tot
Total Edits Saved Indicates the total number of edits saved on this date. ( default value: 0 ) volun_stat_total_edits
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
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
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
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
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

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 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 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
Stars from Online Research Indicates the total number of stars this volunteer has earned from completing online research. ( default value: 0 ) user_info_stars1
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
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
Unique Departments Indicates the number of unique police departments for which this volunteer contributed research. ( default value: 0 ) user_info_depts
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

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 Complaints^(1,1)-(0,N)
User ID Indicates the unique user ID number for the person performing this complaint review. com_rev_user Users^(1,1)-(0,N)
Review Date Indicates the date when this complaint review was submitted. com_rev_date
Review Type Indicates which type of complaint review is being submitted. com_rev_type
Complaint Type Indicates the type of complaint confirmed in this review. Values: OPC Staff/Internal Complaint Type (Definitions) com_rev_complaint_type
Review Status Indicates which new status has been chosen for this complaint, if the current user has permissions. com_rev_status
Next Action After Review Indicates the reviewers recommended next action for this complaint. com_rev_next_action
User Note Indicates any other notes or information the current user wants to share with their review. com_rev_note
Focused On One Incident Indicates whether or not this complaint remained focused on one Incident. Values: 0 ; 1 com_rev_one_incident
Has Civilian Contact Indicates whether or not this complaint includes at least one civilian contact for investigation. Values: 0 ; 1 com_rev_civilian_contact
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
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
Has Evidence Uploaded Indicates whether or not this complaint includes some uploaded evidence supporting their allegations. Values: 0 ; 1 com_rev_evidence_uploaded
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
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
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
Seems Realistic Indicates the reviewer's opinion about how realistic the complainant's story is. Values: 0 ; 1 ; 2 ; 3 ; 4 com_rev_realistic
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
Explicit Language Used Indicates whether or not the reviewer thinks this complaint contains explicit language. Values: 0 ; 1 com_rev_explicit_lang
Contains Graphic Content Indicates whether or not the reviewer thinks this complaint contains particularly graphic content. Values: 0 ; 1 com_rev_graphic_content

OpenPolice.org: Database Design Specs

OpenPolice.org: Database Design Specs

OpenPolice.org: Database Design Specs

OpenPolice.org: Database Design Specs

OpenPolice.org: Database Design Specs

OpenPolice.org: Database Design Specs

OpenPolice.org: Database Design Specs

OpenPolice.org: Database Design Specs

OpenPolice.org: Database Design Specs

OpenPolice.org: Database Design Specs

OpenPolice.org: Database Design Specs

OpenPolice.org: Database Design Specs

complaints (com_)

Complaints

complaints Unique ID

User ID

Experience Node Progress

User ID

Tree Version Number

User ID

OpenPolice.org Complaint ID#

OP.org Complaint Status

OP.org Complaint Type

User ID

Incident ID

Scene ID

Publish officers' names?

Publish complainant's name?

Submitted anonymously?

Privacy Setting

Award Medallion

Have these charges been resolved?

Anyone involved charged with a crime?

Wants an attorney?

Have an attorney?

Attorney OK'd publishing on OpenPolice.org?

Unresolved charges, but use OP.org anyway?

Someong filing lawsuit?

List of Allegations

Summary (Narrative)

Any officers injured?

Officer Injury Descriptions

Previously attempted to file?

What else have you tried?

How did you hear about us?

Got suggestions or feedback for us?

Share with trusted other databases?

Officer disciplined?

Officer Discipline Type

Media Links

Primary Admin ID

Requested Partner ID

Complaint Notes

URL Slug

Submitted to OpenPolice.org

Submission Progress

A/B Testing Version

Tree Version

Honey Pot (to catch spam bots)

Using Mobile Device

Unique String

IP Address (encrypted)

Is Demo/Test Run

Wants an Attorney, But Filing Anyway

Reason for switching from attorney track to file & publish

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.

incidents Unique ID

Complaint ID

Street Address

Street Address 2

City Borough

City

State

Zip Code

Geolocation Latitude

Geolocation Longitude

Nearby Landmarks

Start Time

complaints
(com_)

incidents
(inc_)

scenes
(scn_)

alleg_silver
(alle_sil_)

allegations
(alle_)

event_sequence
(eve_)