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

Open Police Complaints: Database Design Specs

Complaints
(Com)

Complaints

Reports of dissatisfaction that contain one or more Allegations of Officer misconduct. Complaints are vital, because they provide the essential store of information for communicating what happened during a police encounter. Complaints data also helps the organization keep track of how various police accountability professionals are responding to Complaint Allegations.
14 Tables with Foreign Keys
Group: Complaint Data Tables
Type: Data
37 Fields Total
5 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
Complaints Unique ID
ComID
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.
ComUserID
Number
OPC Complaint Status
The current progress of a "complete" or "incomplete" Complaint within the OPC 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)
ComStatus
Number Indexed
OPC 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)
ComType
Number Indexed
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.
ComIncidentID
Number
Scene ID
The unique number of the Scene record related to this Complaint. This number helps us identify the Scene that this Complaint documents.
ComSceneID
Number
Privacy Setting
User-selected category for Complaint records. This defines what personally identifiable information (PII) is publicly shared.
Notes: Should we move privacy settings at the end of the Complaint process for now (in the Simulation/Implementation)? (We can later test whether users prefer this selection to appear earlier or later in the process.) Wikipedia Article on the topic: https://en.wikipedia.org/wiki/Personally_identifiable_information
Values: Privacy Types (Definitions)
ComPrivacy
Number
Award Medallion
The current level of detail asked of Complainant based on their selection. We use this information internally to determine what fields to show the Complainant, and what Medallion up-sells to encourage when appropriate.
Notes: Defaults to Gold.
Values: Silver ; Gold
ComAwardMedallion
Text
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.
ComSummary
Text-Long
List of Allegations
Caches a simple written list of this Complaint's allegations, separated by commas.
ComAllegList
Text-Long
Previously attempted to submit?
Indicates whether or not the Complainant previously attempted to formally submit their police complaint before using OPC. This is important for research about the use of OPC.
Values: Y ; N ; ?
ComTriedOtherWays
Text
What else have you tried?
The Complainant can describe how else they previously attempted to formally submit their police complaint before using OPC. This is important for research about the use of OPC.
ComTriedOtherWaysDesc
Text
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 ; ?
ComOfficerInjured
Text
Officer Injury Descriptions
Further describes Officers' injuries incurred during a specific Use of Force. This is important for tracking an acknowledging the fact that there is more than one side to every story.
ComOfficerInjuredDesc
Text
Do you 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 ; ?
ComAttorneyHas
Text
Attorney Condoned Publishing via OPC
Indicates whether or not the Complainant's attorney explicitly approved their use of Open Police Complaints. This is an important safety check to minimize potential risks of using OPC.
Values: Y ; N ; ?
ComAttorneyOKedOPC
Text
Wants An Attorney
Indicates whether or not the Complainant would like an attorney. This is important for understanding the incident's legal situation and as a safety check to minimize potential risks of using OPC.
Values: Y ; N ; ?
ComAttorneyWant
Text
Anyone involved under arrest or charged with crime?
Indicates whether or not any Civilians were arrested or charged with a crime. This is important for understanding the Incident's legal situation and as a safety check to minimize potential risks of using OPC.
Values: Y ; N ; ?
ComAnyoneCharged
Text
Have these charges been resolved?
Indicates whether or not all criminal charges have been resolved. This is important for understanding the Incident's legal situation and as a safety check to minimize potential risks of using OPC.
Values: Y ; N ; ?
ComAllChargesResolved
Text
Unresolved Charges, But Use OPC Anyway
Indicates whether or not the Complainant has explicitly chosen to use OPC 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 OPC.
Values: Unresolved Charges Actions (Definitions) ( default value: 0 )
ComUnresolvedChargesActions
Number
How Did You Hear About Us
Indicates how the Complainant heard about Open Police Complaints. This is important for internal understanding of OPC marketing strategies.
ComHowHear
Text
Got suggestions or feedback for us?
Indicates any suggestions or feedback the user has for us at the end of the process. This is important to let us know if we missed anything, etc.
ComFeedback
Text-Long
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 ; ?
ComOfficerDisciplined
Text
Officer Discipline Type
Indicates the category of formal punishment any Subject Officers associated with a Complaint have received. Important for statistical purposes and for bringing sense of justice and closure to Civilians.
Values: Officer Discipline Types (Definitions)
ComOfficerDisciplineType
Number
Media Links
The URL address of a news report containing information related to this Complaint. Important for tracking and verifying new information related to a Complaint's Allegations.
Notes: Proper news story selection criteria here: http://www.policemisconduct.net/about/news-feed-faq/ Must be careful not to let people post links to their personal blogs, because that won't include objective information.
ComMediaLinks
Text-Long
Primary Admin ID ^
The unique number of the Administrator record related to this Complaint. This number helps us identify which Administrator is the point of contact for this Complaint.
Notes: OPC staff in charge of Complaint
ComAdminID
Number Indexed
Attorney ID ^
The unique number of the Customer record related to this Complaint. This is important for identifying the Attorney who has taken this case.
ComAttID
Number Indexed
Complaint Notes
Additional annotations related to this Complaint. Might add additional information or context regarding the Complaint.
Notes: Changed to "Complaint Notes"
ComNotes
Text-Long
URL Slug
This defines the version of the Complaint's Social Media Headline which is compatible and ideal to be used as part of a website URL. This is vital for creating public pages of the website which are intuitive and professional.
ComSlug
Text
Submitted to OpenPolice.org
Date and time when this Complaint was completed and submitted to the OPC database.
ComRecordSubmitted
Date&Time Indexed
Submission Progress
Indicates current progress of an "incomplete" Complaint. Important for identifying problem areas which might cause Complaints to be left unfinished.
Notes: TBD: [Shorthand] notation of where in the complaint submission process. Probably both an overall section identifier (like Turbo Tax), and sub-section identifier.
ComSubmissionProgress
Text
A/B Testing Version ^
Stores a complex string reflecting all A/B Testing variations in effect at the time of this User's Experience of this Node.
ComVersionAB
Text
Tree Version
Indicates which precise version number of this software was running at the time of this Complaint's submission. This is important for internal use, quality control, and potential debugging.
ComTreeVersion
Text
Honey Pot (to catch spam bots)
Indicates whether or not a spam bot filled in the form field which human users cannot see. This is important for quickly categorizing Complaints as spam.
ComHoneyPot
Text
Using Mobile Device
Indicates whether or not this Complaint was started on a mobile device. This is important for tracking trends of usage, and potential debugging.
Values: 0 ; 1
ComIsMobile
Number
Unique String
This unique, randomly generated string can be important for creating custom URLs which are more secure than using the Complaint ID#.
ComUniqueStr
Text
IP Address (encrypted)
This logs an encrypted copy of the Complainants IP Address. This is important for checking if multiple Complaints were submitted from the same location, especially when filtering Complaints categorized as spam or abuse.
ComIPaddy
Text
Public ID Number
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.
ComPublicID
Number
Compliments
(Compli)

Compliments

Reports of satisfaction that contain one or more Compliment/Commendation of Officer conduct. Compliments are vital, because they provide the essential store of information for communicating what happened during positive police encounters.
1 Table with Foreign Key
Group: Complaint Data Tables
Type: Data
20 Fields Total
3 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
Compliments Unique ID
CompliID
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.
CompliUserID
Number
OPC Complaint Status
The current progress of a "complete" or "incomplete" Compliment within the OPC 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)
CompliStatus
Number
OPC Complaint Type
The Administrator-selected category for newly-submitted Compliment records. Essential for determining where and how new Compliment records are stored and shared.
CompliType
Number
Incident ID
The unique number of the Incident record related to this Compliment. This number helps us identify the Incident that this Compliment documents.
CompliIncidentID
Number
Scene ID
The unique number of the Scene record related to this Compliment. This number helps us identify the Scene that this compliment documents.
CompliSceneID
Number
Privacy Setting
User-selected category for Compliment records. This defines what personally identifiable information (PII) is publicly shared.
Values: Privacy Types (Definitions)
CompliPrivacy
Number
Summary (Narrative)
The Complainant narrative describes the chronological sequence of key Incident events and Commendations. This story brings to life to an otherwise clinical and legalistic Compliment document.
CompliSummary
Text-Long
How did you hear about OPC?
Indicates how the Complainant heard about Open Police Complaints. This is important for internal understanding of OPC marketing strategies.
CompliHowHear
Text
User Suggestions and Feedback
Indicates any suggestions or feedback the user has for us at the end of the process. This is important to let us know if we missed anything, etc.
CompliFeedback
Text-Long
URL Slug
This defines the version of the Compliment's Social Media Headline which is compatible and ideal to be used as part of a website URL. This is vital for creating public pages of the website which are intuitive and professional.
CompliSlug
Text
Compliment Notes
Additional annotations related to this Compliment. Might add additional information or context regarding the Compliment.
CompliNotes
Text-Long
Record Submitted to OpenPolice.org
Date and time when this Compliment was completed and submitted to the OPC database.
CompliRecordSubmitted
Date&Time
Submission Progress
Indicates current progress of an "incomplete" Compliment. Important for identifying problem areas which might cause Compliments to be left unfinished.
CompliSubmissionProgress
Text
A/B Testing Version ^
Stores a complex string reflecting all A/B Testing variations in effect at the time of this User's Experience of this Node.
CompliVersionAB
Text
Tree Version
Indicates which precise version number of this software was running at the time of this Compliment's submission. This is important for internal use, quality control, and potential debugging.
CompliTreeVersion
Text
Honey Pot (to catch spam bots)
Indicates whether or not a spam bot filled in the form field which human users cannot see. This is important for quickly categorizing Compliments as spam.
CompliHoneyPot
Text
Using Mobile Device
Indicates whether or not this Compliment was started on a mobile device. This is important for tracking trends of usage, and potential debugging.
Values: 0 ; 1
CompliIsMobile
Number
Unique String
This unique, randomly generated string can be important for creating custom URLs which are more secure than using the Compliment ID#.
CompliUniqueStr
Text
IP Address (encrypted)
This logs an encrypted copy of the Complainants IP Address. This is important for checking if multiple Compliments were submitted from the same location, especially when filtering Compliments categorized as spam or abuse.
CompliIPaddy
Text
Public ID Number
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.
CompliPublicID
Number
Incidents
(Inc)

Incidents

Represent individual, uninterrupted police events occurring at a specific time and place. Incident information is vital for identifying and associating data from multiple Complaints with each other.
2 Tables with Foreign Keys
Group: Complaint Data Tables
Type: Data
13 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
Incidents Unique ID
IncID
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.
IncComplaintID
Number Indexed
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.
IncAddress
Text
Street Address 2 ^
The second line, if needed of the postal location at or near where an Incident occurred. This is an integral component of a complete Incident location address.
IncAddress2
Text
City ^
The metropolitan area where an Incident occurred. This is an integral component of a complete Incident location address.
IncAddressCity
Text
State ^
The state or district where an Incident occurred. This is an integral component of a complete Incident location address.
Notes: Incident locations should only include locations within the 50 U.S. states and the District of Columbia.
IncAddressState
Text
Zip Code ^
The postal code where an Incident occurred. This is an integral component of a complete Incident location address.
IncAddressZip
Text
Geolocation Latitude
The geographic coordinate that specifies the north-south position of the point on the Earth's surface where an Incident occurred. This information might help identify Police Departments whose jurisdictions cover the coordinates where an Incident occurred.
IncAddressLat
Number-Decimals
Geolocation Longitude
The geographic coordinate that specifies the east-west position of the point on the Earth's surface where an Incident occurred. This information might help identify Police Departments whose jurisdictions cover the coordinates where an Incident occurred.
IncAddressLng
Number-Decimals
Nearby Landmarks
An easily-recognized place, object, or feature that can help establish a precise Incident location. Useful for identifying locations where an address alone won't suffice.
Notes: e.g. near a memorial inside a large park.
IncLandmarks
Text
Start Time ^
The date, hour, and minute when a single Incident begins. This information is vital for verifying an Incident or merging Incident data from other sources.
Notes: Morgan: We might need a Business rule indicating that the Incident "Start Time" and "End Time" can't be more than 24 hours apart. Specifically, we don't want Complainants reporting allegations from multiple Incidents lumping them into a months or years-long Incident timeframe.
IncTimeStart
Date&Time
End Time ^
The date, hour, and minute when a single Incident ends. This information is vital for verifying an Incident occurrence or merging Incident data from other sources.
IncTimeEnd
Date&Time
Duration ^
The total calculated minutes of of a single Incident. This information is vital for verifying an Incident occurrence or merging Incident data from other sources.
Notes: calculated based on Incident Start Time and Incident End Time.
IncDuration
Number
Publicly Mapped
Indicates whether or not the Complainant wants the address of this Incident made public for mapping and research purposes.
Values: 0 ; 1
IncPublic
Number
Scenes
(Scn)

Scenes

Scene information provides descriptive details about the setting where an Incident occurred. Beyond mere time and location, this adds an important contextual backdrop behind specific Incident events. Scene information also helps provide a more objective evaluation of the Incident and Allegations.
2 Tables with Foreign Keys
Group: Complaint Data Tables
Type: Data
7 Fields Total
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
Scenes Unique ID
ScnID
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 ; ?
ScnIsVehicle
Text
Scene Type
Category of possible settings where an Incident occurred. This selection provides important contextual information about the location of the Incident, which helps us better evaluate the Incident and Allegations.
Notes: Which of the following best describes the scene of the incident? If the incident includes multiple locations, pick the one where you first witnessed police contact.
Values: Scene Type (Definitions)
ScnType
Number
Description
Narrative details about the setting where an Incident occurred. Beyond mere time and location, scene information adds an important contextual backdrop behind specific Incident events.
ScnDescription
Text-Long
Incident began with forcible entry?
Indicates that the Incident involved the use of a forcible entry, SWAT raids, or drug task force. Important for evaluating SWAT incidents and trends.
Notes: This should only appear as an option if users selects from Home or Private Residence; Workplace; College Dorm from Scene Type options.
Values: Y ; N ; ?
ScnForcibleEntry
Text
Closed circuit cameras on scene?
Indicates that there was possible video surveillance of the Scene. Important for obtaining new Evidence of the Incident.
Notes: Go back to the scene to check for CCTV cameras to chase down potential video Evidence. If selected, we might want to email user article on How to Track Down CCTV Footage, or something like that.
Values: Y ; N ; ?
ScnCCTV
Text
Closed Circuit Camera Descriptions
Narrative details about the type and location of any visible video surveillance cameras. Might help Oversight Agencies or Attorneys track down new Evidence of the Incident.
ScnCCTVDesc
Text-Long
Incident began with traffic accident?
Whether or not an Incident began with a vehicle accident. This provides important contextual information about the the nature of the Incident to better evaluate the Incident and Allegations.
Values: Y ; N ; ?
ScnIsVehicleAccident
Text
AllegSilver
(AlleSil)

Silver Allegations

Tracks the yes/no responses to Silver-Level Allegations against Officers, vital to identifying the most serious Complaints. This information also helps OPC provide useful next-steps advice for Complainants who generate Complaints.
Group: Complaint Data Tables
Type: Data
28 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
AllegSilver Unique ID
AlleSilID
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.
AlleSilComplaintID
Number
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 ; ?
AlleSilStopYN
Text
Think stop or detention wrongful?
Indicates whether or not the Complainant believes there is an Allegation regarding the wrongful stop of at least one Civilian. Such Allegation claims are central to Complaints.
Values: Y ; N ; ?
AlleSilStopWrongful
Text
Any one 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 ; ?
AlleSilOfficerID
Text
Officer refused to provide ID?
Indicates whether or not the Complainant believes there is a potential Allegation regarding an Officer refusing to provide identification. Such Allegation claims are central to Complaints.
Values: Y ; N ; ?
AlleSilOfficerRefuseID
Text
Officer searched someone or something?
Indicates whether or not an Officer searched anyone during the Incident. This is important for later reloading Complaint forms for silver Allegations with the user's previous response.
Values: Y ; N ; ?
AlleSilSearchYN
Text
Think search was wrongful?
Indicates whether or not the Complainant believes there is an Allegation regarding the wrongful search of at least one Civilian. Such Allegation claims are central to Complaints.
Values: Y ; N ; ?
AlleSilSearchWrongful
Text
Officer used physical force?
Indicates whether or not an Officer used physical force against anyone during the Incident. This is important for later reloading Complaint forms for silver Allegations with the user's previous response.
Values: Y ; N ; ?
AlleSilForceYN
Text
Think physical force unreasonable?
Indicates whether or not the Complainant believes there is an Allegation regarding the unreasonable use of force against at least one Civilian. Such Allegation claims are central to Complaints.
Values: Y ; N ; ?
AlleSilForceUnreason
Text
Property seized or damaged?
Indicates whether or not an Officer seized or damaged any property during the Incident. This is important for later reloading Complaint forms for silver Allegations with the user's previous response.
Values: Y ; N ; ?
AlleSilPropertyYN
Text
Think property wrongfully seized?
Indicates whether or not the Complainant believes there is an Allegation regarding the wrongful seizure or destruction of at least one Civilian's property. Such Allegation claims are central to Complaints.
Values: Y ; N ; ?
AlleSilPropertyWrongful
Text
Officer arrested someone?
Indicates whether or not an Officer arrested anyone during the Incident. This is important for later reloading Complaint forms for silver Allegations with the user's previous response.
Values: Y ; N ; ?
AlleSilArrestYN
Text
Think property damage wrongful?
Indicates whether or not the Complainant believes there is an Allegation regarding the wrongful destruction of at least one Civilian's property. Such Allegation claims are central to Complaints.
Values: Y ; N ; ?
AlleSilPropertyDamage
Text
Think arrest was wrongful?
Indicates whether or not the Complainant believes there is an Allegation regarding the wrongful arrest of at least one Civilian. Such Allegation claims are central to Complaints.
Values: Y ; N ; ?
AlleSilArrestWrongful
Text
Think arrest charges were retaliation?
Indicates whether or not the Complainant believes there is an Allegation regarding retaliatory charges against at least one Civilian. Such Allegation claims are central to Complaints.
Values: Y ; N ; ?
AlleSilArrestRetaliatory
Text
Officer fail to read Miranda Rights?
Indicates whether or not the Complainant believes there is an Allegation regarding a failure to read Miranda rights during at least one Arrest. Such Allegation claims are central to Complaints.
Values: Y ; N ; ?
AlleSilArrestMiranda
Text
Officer give ticket or citation?
Indicates whether or not an Officer cited anyone during the Incident. This is important for later reloading Complaint forms for silver Allegations with the user's previous response.
Values: Y ; N ; ?
AlleSilCitationYN
Text
Think citation charges were excessive?
Indicates whether or not the Complainant believes there is an Allegation regarding retaliatory citation charges against at least one Civilian. Such Allegation claims are central to Complaints.
Values: Y ; N ; ?
AlleSilCitationExcessive
Text
Think officer violated policy or procedure?
Indicates whether or not the Complainant believes there is an Allegation regarding Officer actions which did not follow appropriate policy or procedure. Such Allegation claims are central to Complaints.
Values: Y ; N ; ?
AlleSilProcedure
Text
Think officer neglected duty?
Indicates whether or not the Complainant believes there is an Allegation regarding Officer inactions which did not follow appropriate policy or procedure. Such Allegation claims are central to Complaints.
Values: Y ; N ; ?
AlleSilNeglectDuty
Text
Think officer actions were biased?
Indicates whether or not the Complainant believes there is an Allegation regarding policing based on bias. Such Allegation claims are central to Complaints.
Values: Y ; N ; ?
AlleSilBias
Text
Think officer sexually harassed someone?
Indicates whether or not an Officer sexually harassed anyone during the Incident. This is important for later reloading Complaint forms for silver Allegations with the user's previous response.
Values: Y ; N ; ?
AlleSilSexualHarass
Text
Think officer sexually assaulted someone?
Indicates whether or not an Officer sexually assaulted anyone during the Incident. This is important for later reloading Complaint forms for silver Allegations with the user's previous response.
Values: Y ; N ; ?
AlleSilSexualAssault
Text
Think officer wrongfully intimidated w/ weapon display?
The Officer brandished or discharged a weapon in 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)
AlleSilIntimidatingWeapon
Number
What weapon was wrongfully displayed?
The category of weapon the Officer used in a threatening manner. Important for identifying whether an Officer might have violated Department policies or procedures.
Notes: Range of Values is a subset of Force Type, which can be used for intimidation. Hide Force Type Definitions: Control Hold, Body Weapons, Takedown, Vehicle
Values: Force Type (Definitions)
AlleSilIntimidatingWeaponType
Number
Officer enter private space without warrant?
Indicates whether or not an Officer entered a space without a warrant. This is important for determining the details of justified entry on private property.
Values: Y ; N ; ?
AlleSilWrongfulEntry
Text
Think officer conduct improper or unprofessional?
Indicates whether or not the Complainant believes there is an Allegation regarding conduct unbecoming of an Officer. Such Allegation claims are central to Complaints.
Values: Y ; N ; ?
AlleSilUnbecoming
Text
Think officer was discourteous?
Indicates whether or not the Complainant believes there is an Allegation regarding at least one Officer's discourtesy. Such Allegation claims are central to Complaints.
Values: Y ; N ; ?
AlleSilDiscourteous
Text
Allegations
(Alle)

Allegations

Represent specific misconduct accusations against Officers, vital to identifying the most serious Complaints. This information also helps OPC provide useful next-steps advice for Complainants who generate Complaints. This table provides storage for the descriptions of why each Allegation has been made, and a record to link with Officers and/or Victims.
2 Tables with Foreign Keys
Group: Complaint Data Tables
Type: Data
5 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
Allegations Unique ID
AlleID
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.
AlleComplaintID
Number Indexed
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)
AlleType
Number
Event Sequence ID ^
The unique number of the Incident Event record related to this Allegation. This is important for linking the Allegation to a specific Stop, Search, Use of Force, or Arrest.
AlleEventSequenceID
Number
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.
AlleDescription
Text-Long
Allegation Findings
The official final disposition of an Allegation. Important for tracking responsiveness of Police Departments, Oversight Agencies, and Complainants.
Notes: Final Allegation status is categorized by Admins after reviewing official department documentation.
Values: Allegation Findings (Definitions)
AlleFindings
Number
OffCompliments
(OffComp)

Officer Compliments

Tracks the yes/no responses to Compliments given to Officers.
Group: Complaint Data Tables
Type: Data
9 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
OffCompliments Unique ID
OffCompID
Number, Indexed, Primary Key
Officer ID
The unique number of the Officer record being linked in this record. Vital for associating an Officer with this row's Compliments.
OffCompOffID
Number
Officer display valor?
Indicates whether or not the officer showed extraordinary courage in the face of danger.
Values: Y ; N ; ?
OffCompValor
Text
Officer apply medial aid?
Indicates whether or not the officer applied medical aid exceeding the normal call of duty.
Values: Y ; N ; ?
OffCompLifesaving
Text
Officer de-escalate tense situation?
Indicates whether or not the officer skillfully calmed down a tense situation, using minimal or no force.
Values: Y ; N ; ?
OffCompDeescalation
Text
Officer was courteous?
Indicates whether or not the officer behaved in a courteous, respectful, and straightforward manner.
Values: Y ; N ; ?
OffCompProfessionalism
Text
Officer was fair?
Indicates whether or not the officer’s use of power was reasonable, appropriate, and free from bias.
Values: Y ; N ; ?
OffCompFairness
Text
Officer respected constitutional rights?
Indicates whether or not the officer’s words and actions showed respect for the Bill of Rights.
Values: Y ; N ; ?
OffCompConstitutional
Text
Officer was compassionate?
Indicates whether or not the officer displayed empathy and generosity beyond the call of duty.
Values: Y ; N ; ?
OffCompCompassion
Text
Officer positively engaged community?
Indicates whether or not the officer engaged the community to build bonds of trust.
Values: Y ; N ; ?
OffCompCommunity
Text
EventSequence
(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.
8 Tables with Foreign Keys Every Event Sequence Record must be associated with exactly one Stop, Search, Force, or Arrest which occur in the chronology of the overall Incident. There can potentially be many Orders from many Officers to many Civilians related to each Stop, Search, use of Force, Arrest, etc.
Group: Complaint Data Tables
Type: Linking
4 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
EventSequence Unique ID
EveID
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.
EveComplaintID
Number Indexed
Event Order
The is the chronological rank of events within an incident, identifying the order in which they occurred. This is vital to presenting the Stops, Searches, Uses of Force, and Arrests in the order they occurred during the Incident.
EveOrder
Number
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
EveType
Text
User Finished Event
Internally indicates whether or not the Complainant completed the final questions related to a Stop, Search, Use of Force, or Arrest. This is important for determining when the Complainant has completed all dynamically generated sections describing 'What Happened'.
Values: Y ; N
EveUserFinished
Text
Stops
(Stop)

Stops

Includes all instances where Officers pull over a vehicle, detain a pedestrian, or otherwise question a Civilian at a residence. This table provides important information about Officers' behavior and Stop practices within particular Departments.
1 Table with Foreign Key Every Event Sequence Record must be associated with exactly one Stop, Search, Force, or Arrest which occur in the chronology of the overall Incident. For a handcuff Injury to be associated with a Stop, tangible Evidence is required. <b>Administrator Tasks During Review:</b> Was this a Search Incident To Arrest? --- Validate Described Reasons for Stop --- ...
Group: Complaint Data Tables
Type: Subset
25 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
Stops Unique ID
StopID
Number, Indexed, Primary Key
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.
StopEventSequenceID
Number
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?
StopStatedReasonDesc
Text-Long
Subject ask to leave?
Indicates whether or not this Subject asked the Officer if he/she was free to go during the Stop. 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 ; ?
StopSubjectAskedToLeave
Text
What did subject say to officer?
Narrative account of what Subject said to the Officer during the Stop. This is important for evaluating any Allegations related to this Stop.
Notes: As best you can remember, what were the exact words this Subject said to this Officer?
StopSubjectStatementsDesc
Text-Long
Officer entered private property?
Indicates whether or not the an Officer entered a private residence or workplace. Important for identifying possible violation of Department procedure.
Values: Y ; N ; ?
StopEnterPrivateProperty
Text
What happened?
Describes how an Officer entered a private residence or workplace. Important for identifying possible violation of Department procedure.
StopEnterPrivatePropertyDesc
Text
Officer request permission to enter?
Indicates whether or not the Officer requested permission to enter a private residence or workplace. Important for identifying possible violation of Department procedure.
Values: Y ; N ; ?
StopPermissionEnter
Text
Subject gave officer permission to enter?
Indicates whether or not the Civilian granted an Officer permission to enter a private residence or workplace. Important for identifying possible violation of Department procedure.
Notes: If Complainant respond "No", then prompt for Wrongful Entry Allegation (with description).
Values: Y ; N ; ?
StopPermissionEnterGranted
Text
Officer requested ID?
Indicates whether or not the Officer requested the Subject's identification. Important for identifying possible violation of Department procedure.
Values: Y ; N ; ?
StopRequestID
Text
Subject refused to give ID?
Indicates whether or not a Civilian refused to provide ID to an Officer. Important for identifying patterns of excessive ID requests and retaliatory policing.
Notes: http://www.flexyourrights.org/faqs/when-can-police-ask-for-id/
Values: Y ; N ; ?
StopRefuseID
Text
Subject asked for officer's ID?
Indicates whether or not a Subject requested the Officer's identification. Important for identifying possible violation of Department procedure.
Values: Y ; N ; ?
StopRequestOfficerID
Text
Citation ID#
Citation ID Number issued to a Subject. Important for tracking down more information during investigations.
StopCitationNumber
Text
Officer refused to give ID?
Indicates whether or not the Officer shared their name and/or badge number after Subject requested it. Important for identifying possible violation of Department procedure.
Notes: We might use this opportunity to let Complainants know that insisting Officers share their badge number isn't always a smart move. Tip: It's okay if you didn't get the badge or vehicle number. An officer--s identity can usually be established with a time, location, and physical description.
Values: Y ; N ; ?
StopOfficerRefuseID
Text
Subject frisked?
Indicates whether or not this Subject was patted down by an Officer during this Stop. This is important information for evaluating certain Allegations related to this stop. Also important for identifying patterns of excessive pat downs.
Notes: Related Article http://www.flexyourrights.org/faqs/what-is-reasonable-suspicion/ There was at one point a copy of this field in Searches. We deleted it because frisks are not technically searches and are generally legally permissible as part of a Stop. Q. Do we want mere frisks to trigger wrongful search Allegations? Because frisks alone are almost always legally permissible, this might be Allegation overkill.
Values: Y ; N ; ?
StopSubjectFrisk
Text
Subject handcuffed?
Indicates whether or not this Subject was handcuffed by an Officer during this Stop. Important information for identifying Department patterns of excessive handcuffing during Stops.
Notes: San Jose IPA is trying to limit this common practice.
Values: Y ; N ; ?
StopSubjectHandcuffed
Text
Subject injured from handcuffs?
Indicates whether or not the Subject reported any Injuries related to an Officer's misuse of handcuffs. Important for connecting with possible Use of Force.
Values: Y ; N ; ?
StopStopSubjectHandcuffInjYN
Text
Handcuff Injury ID
Indicates whether or not the Subject reported any Injuries related to an Officer's misuse of handcuffs. Important for connecting with possible Use of Force.
Notes: Injuries from handcuffs should only link to an Allegation of Excessive Force if and only if the Complainant provides Evidence of Injury. NULL if No
StopSubjectHandcuffInjury
Number
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.
StopDuration
Number
Officer gave alcohol breath test?
Indicates whether or not an Officer administered an alcohol breath test.
Values: Y ; N ; ?
StopBreathAlcohol
Text
Officer say subject failed alcohol breath test?
Indicates whether or not an Officer administered an alcohol breath test, and claimed someone failed.
Values: Y ; N ; ?
StopBreathAlcoholFailed
Text
Officer gave marijuana breath test?
Indicates whether or not an Officer administered a marijuana breath test.
Values: Y ; N ; ?
StopBreathCannabis
Text
Officer say subject failed marijuana breath test?
Indicates whether or not an Officer administered a marijuana breath test, and claimed someone failed.
Values: Y ; N ; ?
StopBreathCannabisFailed
Text
Officer collected saliva?
Indicates whether or not an Officer collected someone's saliva.
Values: Y ; N ; ?
StopSalivaTest
Text
Officer gave other sobriety tests?
Indicates whether or not an Officer administered other sobriety tests.
Values: Y ; N ; ?
StopSobrietyOther
Text
Description of other sobriety tests
Description of other sobriety tests administered by Officer.
StopSobrietyOtherDescribe
Text
StopReasons
(StopReas)

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
StopReasons Unique ID
StopReasID
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.
StopReasStopID
Number
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)
StopReasReason
Number
Searches
(Srch)

Searches

Includes all instances of vehicle searches, home, or other property searches. Detailed Search information is vital to evaluating the legitimacy of the search itself as well as identifying general Search practices within particular Departments.
2 Tables with Foreign Keys Every Event Sequence Record must be associated with exactly one Stop, Search, Force, or Arrest which occur in the chronology of the overall Incident.
Group: Complaint Data Tables
Type: Subset
19 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
Searches Unique ID
SrchID
Number, Indexed, Primary Key
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.
SrchEventSequenceID
Number
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 ; ?
SrchStatedReason
Text
Stated reason description
The specific reason the Officer gave for conducting this Search. Important for evaluating possible wrongful search Allegation and identifying Search trends.
Notes: As best you can remember, what were the exact words this Officer said before conducting this search?
SrchStatedReasonDesc
Text-Long
Officer request permission to search?
Indicates whether or not the Officer asked the Subject for permission to Search their person or property. Important for evaluating possible wrongful search Allegation and identifying Search trends.
Values: Y ; N ; ?
SrchOfficerRequest
Text
Officer request description
The specific words and tone the Officer used to solicit consent to search. Important for evaluating possible wrongful search Allegation and identifying Search trends.
Notes: What were the exact words and tone this Officer used to request consent to search? (Search requests often sound a lot like commands.)
SrchOfficerRequestDesc
Text-Long
Did subject consent to search?
Indicates whether or not the Subject gave the Officer permission to Search their person or property. Important for evaluating possible wrongful search Allegation and identifying Search trends.
Values: Y ; N ; ?
SrchSubjectConsent
Text
What did subject say?
Narrative account of specific words a Subject used to consent to the search. Important for evaluating possible wrongful search Allegation and identifying Search trends.
Notes: What were the exact words and tone [the Subject] used to give consent to the Officer?
SrchSubjectSay
Text-Long
Officer make threats or lie to get consent to search?
Indicates whether or not an Officer made any stated or implied threats to the Subject to obtain consent to search Subject's person or property. Important for evaluating possible wrongful search Allegation and identifying Search trends.
Values: Y ; N ; ?
SrchOfficerThreats
Text
Officer threat description
The specific words the Officer used to trick Subject into consenting to Search. Important for evaluating possible wrongful search Allegation and identifying Search trends.
Notes: What were the exact words this Officer used to threaten or trick the Subject into giving consent to search?
SrchOfficerThreatsDesc
Text-Long
Officer strip searched someone?
Indicates whether or not an Officer performed a strip search on a Subject (not including at the police station). Important for evaluating possible wrongful search Allegation and identifying search trends.
Values: Y ; N ; ?
SrchStrip
Text
Strip search description
Narrative details about the nature of the strip search (not including at the police station). Important for identifying particularly egregious wrongful search Allegations — including cavity searches.
Notes: We understand that this isn't easy, but please provide as much detail as you can about the strip search. The more information you provide, the better we can help address your complaint.
SrchStripSearchDesc
Text
Officer use K9 (dog) sniff?
Indicates whether or not an Officer had a K9 (Dog) sniff a Subject or their property. Important for identifying possible wrongful search Allegation and identifying K9 (Dog) search trends.
Notes: Q. Do we want mere K9 sniffs to trigger wrongful search Allegations? Because K9 sniffs alone are almost always legally permissible, this might be Allegation overkill.
Values: Y ; N ; ?
SrchK9sniff
Text
Officer discover contraband?
Indicates whether or not illegal items were discovered during a Search. Important for identifying search trends.
Values: Y ; N ; ?
SrchContrabandDiscovered
Text
Officer have warrant?
Indicates whether or not an Officer had a warrant for a Search. Important for evaluating possible wrongful search Allegations and for identifying search trends.
Values: Y ; N ; ?
SrchOfficerWarrant
Text
What did warrant say?
The words on the official warrant. Important for evaluating possible wrongful search Allegations.
Notes: User may type actual words on the warrant. Or they may upload a photograph or scan of the warrant as Evidence.
SrchOfficerWarrantSay
Text-Long
Property seized?
Indicates that Officers took property from a Subject. Important for evaluating seizure Allegations and forfeiture trends.
Values: Y ; N ; ?
SrchSeized
Text
Property seizure description
Additional narrative information about the specific Property items that were seized. Important for evaluating Property seizure Allegations.
Notes: Describe the specific items seized. For example, if cash seized, please specify the dollar amount.
SrchSeizedDesc
Text-Long
Officer damaged property?
Indicates that Officers damaged or destroyed Subject's Property. Important for evaluating Property damage claims.
Notes: IF Yes, then Evidence can be optionally uploaded.
Values: Y ; N ; ?
SrchDamage
Text
Property damage description
Additional information about the nature of the damaged or destroyed Property. Important for evaluating Property damage claims.
Notes: Describe the specific items damaged and the nature of the damage. Please include dollar amounts for repairing or replacing damaged items.
SrchDamageDesc
Text-Long
SearchContra
(SrchCon)

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
SearchContra Unique ID
SrchConID
Number, Indexed, Primary Key
Search ID
The unique number of the Search record related to this Search Contraband found. Vital for associating multiple Search Contraband items with a single Search during an Incident.
SrchConSearchID
Number
Contraband Types
Categories of illegal items discovered during a Search. Important for identifying search trends.
Values: Contraband Types (Definitions)
SrchConType
Number
SearchSeize
(SrchSeiz)

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
SearchSeize Unique ID
SrchSeizID
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.
SrchSeizSearchID
Number
Seized property type
Categories of items Seized discovered during a Search. Important for identifying search trends.
Values: Property Seized Types (Definitions)
SrchSeizType
Number
Arrests
(Arst)

Arrests

Includes all instances where individuals are placed under arrest. This table information helps provide richer detail about the nature of the Arrest and the individual burden of certain low-level discretionary or retaliatory Arrests.
Every Event Sequence Record must be associated with exactly one Stop, Search, Force, or Arrest which occur in the chronology of the overall Incident. <b>Administrator Tasks During Review:</b> Was this a Search Incident To Arrest? --- Validate Described Reasons for Stop --- ...
Group: Complaint Data Tables
Type: Subset
9 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
Arrests Unique ID
ArstID
Number, Indexed, Primary Key
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.
ArstEventSequenceID
Number
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 ; ?
ArstChargesFiled
Text
Did officer state a reason for arrest?
Indicates whether or not an Officer stated a reason for Arresting a Civilian. Important for evaluating wrongful arrest Allegations.
Notes: Did [the officer] verbally state a reason why he arrested [this Subject]?
Values: Y ; N ; ?
ArstStatedReason
Text
What was stated reason for arrest?
The specific reason an Officer gave for making an arrest. Important for evaluating wrongful arrest Allegations.
Notes: Before the arrest happened, what specific reasons did [the officer] give for arresting [this Subject]?
ArstStatedReasonDesc
Text-Long
Did the officer read Miranda rights?
Indicates whether or not an Officer read a Subject their Miranda rights during Arrest. Important for evaluating possible procedure Allegation.
Notes: Failure to read subject Miranda rights turns on a "procedure" violation, and a value of Yes here is equivalent to an Allegation.
Values: Y ; N ; ?
ArstMiranda
Text
Was this a Search Incident To Arrest? (Admin)
Indicates whether or not Administrators determine that an Officer 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 ; ?
ArstSITA
Text
Were all charges dropped upon release?
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 ; ?
ArstNoChargesFiled
Text
Strip search done at station?
Indicates whether or not an Officer performed a strip search on a Subject at the police station. Important for evaluating possible wrongful search Allegation and identifying search trends.
Values: Y ; N ; ?
ArstStrip
Text
Strip search description
Narrative details about the nature of the strip search. Important for identifying particularly egregious wrongful search Allegations -- including cavity searches.
ArstStripSearchDesc
Text
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.
2 Tables with Foreign Keys Every Event Sequence Record must be associated with exactly one Stop, Search, Force, or Arrest which occur in the chronology of the overall Incident.
Group: Complaint Data Tables
Type: Subset
12 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
Force Unique ID
ForID
Number, Indexed, Primary Key
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.
ForEventSequenceID
Number
Against 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 ; ?
ForAgainstAnimal
Text
Describe Animal
Narrative description of animal that Officer Force was directed against. Important for verifying animal cruelty Allegations.
Notes: Tells us about the animal. For example, what kind of animal is it? Is it a wild animal or stray? Or is it a family pet? Please describe.
ForAnimalDesc
Text
Types of Force
The categories of Force an Officer used on a Subject. Essential for evaluating Allegations of excessive force and for tracking Force trends.
Values: Force Type (Definitions)
ForType
Number
Other Type of Force
Category of Force an Officer used on Subject. Important for identifying less-common Force types.
Notes: Only visible if "Other" selected under "Which Types of Force did this officer used?"
ForTypeOther
Text
Type of gun ammunition
Category of ammunition an Officer used on a Subject. Important for investigating Allegations of Force and for identifying Force trends.
Notes: Only if Force Type is 'Gun'.
Values: Gun Ammo Types (Definitions)
ForGunAmmoType
Number
Gun Description
Narrative details of gun Officer used on Suspect. Important for investigating Allegations of Force and for identifying Force trends.
Notes: Only if Force Type is 'Gun'.
ForGunDesc
Text
How many times was subject hit?
Indicates the number of times an Officer hit a Subject with this type of Force. Important for investigating Allegations of Force and for identifying Force trends.
Notes: We might not want to reveal this for certain types of force -- such takedowns and control holds. Because those are likely to only be deployed once.
ForHowManyTimes
Number
Officers Orders Used Before Force
Indicates whether or not an Officer issued orders before using Force against a Subject. Important for investigating Excessive Force Allegations of Excessive Force.
Values: Y ; N ; ?
ForOrdersBeforeForce
Text
Victim's Response Before Force
Narrative account of what the Subject of an Excessive Force Allegation said or did before Officer used Force. Important for investigating Excessive Force Allegations and for identifying Force trends.
ForOrdersSubjectResponse
Text-Long
Subject handcuffed when hit?
Indicates whether or not Subject was handcuffed when struck by an Officer. Important for evaluating Allegations of Excessive Force and tracking Force trends.
Values: Y ; N ; ?
ForWhileHandcuffed
Text
Subject held down when hit?
Indicates whether or not Subject was restrained on the ground when struck by an Officer. Important for evaluating Allegations of Excessive Force and tracking Force trends.
Values: Y ; N ; ?
ForWhileHeldDown
Text
ForceSubType
(ForceSub)

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
ForceSubType Unique ID
ForceSubID
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.
ForceSubForceID
Number
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)
ForceSubType
Number
ForceBodyParts
(FrcBdy)

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
ForceBodyParts Unique ID
FrcBdyID
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.
FrcBdyForceID
Number
Which parts of body 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)
FrcBdyPart
Number
CivWeapons
(CivWeap)

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
CivWeapons Unique ID
CivWeapID
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.
CivWeapComID
Number
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.
CivWeapBodyWeapon
Number
Charges
(Chrg)

Charges

Includes all instances where individuals are charged without being arrested. This table information helps provide richer detail, especially about the use of excessive/unnecessary citations.
Group: Complaint Data Tables
Type: Data
3 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
Charges Unique ID
ChrgID
Number, Indexed, Primary Key
Civilian ID
The unique number of the Victim's Civilian record who has received this Arrest or Stop Charge.
ChrgCivID
Number
Event Sequence ID
The unique number of the Event Sequence record related to this Arrest or Stop Charge.
ChrgEventID
Number
What charges were 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)
ChrgCharges
Number
Injuries
(Inj)

Injuries

Includes all instances of bodily harm sustained as a result of alleged Force initiated by Officers. Detailed Injury information is vital to substantiating such Allegations if they are truthful.
2 Tables with Foreign Keys For a handcuff Injury to be associated with a Stop, tangible Evidence is required.
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
InjID
Number, Indexed, Primary Key
Subject of Injury
Indicates which Subject's Injuries are described by this record.
InjSubjectID
Number
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)
InjType
Number
How Many Injuries
A number to indicate how many Injuries a particular Civilian received during an use of Force Incident. Important for quantifying the number of discrete Injuries received from Force.
Notes: We have decided this might not be necessary 10/17/15======This field hidden for Injury Types (these types count by Injury Locations): In-patient hospital stay required, Blood loss requiring transfusion, Major concussion, Longer than brief loss of consciousness, Minor concussion, Brief loss of consciousness,
InjHowManyTimes
Number
Injury Description
Additional narrative information about a specific Injury. Important for evaluating Allegations of excessive Force.
InjDescription
Text-Long
Injury Form Done
Indicates whether or not the Complainant has gone through this Injury record yet.
Values: 0 ; 1
InjDone
Number
InjuryBodyParts
(InjBdy)

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
InjuryBodyParts Unique ID
InjBdyID
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.
InjBdyInjuryID
Number
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)
InjBdyPart
Number
InjuryCare
(InjCare)

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
InjuryCare Unique ID
InjCareID
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.
InjCareSubjectID
Number
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 ; ?
InjCareResultInDeath
Text
Date & Time of death
Indicates the official time of death of a Civilian resulting from a Force Injury. Important for statistical purposes.
InjCareTimeOfDeath
Date&Time
Received Medical Care
Indicates whether or not an Injured Civilian received medical care from a licensed medical practitioner. Essential for urging Injured Civilians to seek medical care who haven't received it yet.
Notes: Implementation Note: We must urge users with fresh Injuries to seek medical care if they haven't yet. Because official medical records are essential for substantiating Force Allegations.
Values: Y ; N ; ?
InjCareGotMedical
Text
Hospital Where Treated
The name of the hospital where the Civilian who received Injuries was initially treated. Important for verifying Injury information.
InjCareHospitalTreated
Text
Doctor First Name
The legal given name of a doctor who treated an Injury. Important for identifying doctors who treated Injuries.
InjCareDoctorNameFirst
Text
Doctor Last Name
The legal surname of a doctor who treated an Injury. Important for identifying doctors who treated Injuries.
InjCareDoctorNameLast
Text
Doctor Email
The valid email address of a doctor who treated an Injury. Important for communicating with doctors who treated Injuries.
InjCareDoctorEmail
Text
Doctor Phone
The contact number of a doctor who treated an Injury. Important for communicating with doctors who treated Injuries.
InjCareDoctorPhone
Text
Emergency staff on scene?
Indicates whether or not emergency medical staff were present at the Scene where any Injuries were received. Important for identifying emergency medical personnel who treated Injuries.
Values: Y ; N ; ?
InjCareEmergencyOnScene
Text
Emergency Staff First Name
The legal given name of an emergency medical services staffer present at the Scene where any Injuries were received. Important for identifying emergency medical personnel who treated Injuries.
InjCareEmergencyNameFirst
Text
Emergency Staff Last Name
The legal surname of an emergency medical services staffer present at the Scene where any Injuries were received. Important for identifying emergency medical personnel who treated Injuries.
InjCareEmergencyNameLast
Text
Emergency Staff ID Number
A unique number assigned to the emergency medical services staffer by their department. Important for identifying emergency medical personnel who treated Injuries.
InjCareEmergencyIDnumber
Text
Emergency Staff Vehicle Number
A unique number assigned to an emergency medical services vehicle. Important for identifying emergency medical personnel who treated Injuries.
InjCareEmergencyVehicleNumber
Text
Emergency Staff Licence Number
The license plate number of an emergency medical services vehicle. Important for identifying emergency medical personnel who treated Injuries.
InjCareEmergencyLicenceNumber
Text
Emergency Staff Department Name
The official name of the emergency medical services department. Important for identifying emergency medical personnel who treated Injuries.
InjCareEmergencyDeptName
Text
Injury Care Form Done
Indicates whether or not the Complainant has gone through this Injury Care record yet.
Values: 0 ; 1
InjCareDone
Number
Surveys
(Surv)

Surveys

Includes feedback after the entire process has been completed. This is primarily provided by Complainants, but may also be expanded to other types of system Users. This is vital for continuing to improve the quality of the system, both in general and in specific detail.
Group: Complaint Data Tables
Type: Data
2 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
Surveys Unique ID
SurvID
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.
SurvComplaintID
Number Indexed
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 OPC system.
SurvAuthUserID
Number
ComplaintNotes
(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
4 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
ComplaintNotes Unique ID
NoteID
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.
NoteComplaintID
Number Indexed
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.
NoteUserID
Number
Timestamp ^
The date and time when a Note was added to a Complaint. This is vital to tracking the chronology of new updates, information, and/or Evidence appended to a completed Complaint.
( default value: NOW() )
NoteTimestamp
Date&Time
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.
NoteContent
Text-Long
Civilians
(Civ)

Civilians

Individuals who are directly impacted by, observed, or are reporting on an police incident. This information allows us to identify and contact individuals who've been impacted by alleged incident.
6 Tables with Foreign Keys
Group: People Involved In Complaint
Type: Data
23 Fields Total
4 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
Civilians Unique ID
CivID
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.
CivComplaintID
Number Indexed
User ID ^
The primary User Authentication number connected to a Civilian's record. Important for allowing Complainants to log into OPC to complete Complaints or update Complaints.
CivUserID
Number
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 )
CivIsCreator
Text
Civilian's role in incident?
Indicates 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
CivRole
Text
Person Contact Info ID
The primary Person Contact record number connected to a Civilian's record. Important for linking name and basic contact information to a Civilian.
Physical Description ID
The primary Physical Description record number connected to a Civilian's record. Important for linking name and basic contact information to a Civilian.
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 ; ?
CivGiveName
Text
Contact information known?
Indicates whether or not the Complainant intends to provide any contact information for this Civilian. This is important for later reloading Complaint forms with the user's previous response.
Values: Y ; N ; ?
CivGiveContactInfo
Text
Occupation
The profession of a Civilian. This information can help investigators identify Civilians.
CivOccupation
Text
Using Vehicle at Start
Indicates whether or not the Complainant intends to provide information about the vehicle this Civilian was in. This is important for later reloading Complaint forms with the user's previous response.
Values: Y ; N ; ?
CivHadVehicle
Text
Was subject 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 ; ?
CivChase
Text
Civilian 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 ; ?
CivResident
Text
Type of chase
Indicates whether the Subject's chase took place on foot, in vehicles, or both. Important for evaluating Allegations of Excessive Force and tracking Force trends.
CivChaseType
Number
Did subject have weapon?
Indicates whether or not the Victim of this Use of Force seemed to have a weapon at the time of the Incident. This is important for determining if the Officer used Unreasonable Force.
Values: Y ; N ; ?
CivVictimHadWeapon
Text
Weapon Type
Indicates what type of weapon the Victim of this Use of Force had at the time of the Incident. This is important for determining if the Officer used Unreasonable Force.
Values: Civilian Weapons (Definitions)
CivVictimWhatWeapon
Number
Did subject use the weapon?
Indicates how the Victim used a weapon during this Use of Force. This is important for determining if the Officer used Unreasonable Force.
Values: Intimidating Displays Of Weapon (Definitions)
CivVictimUseWeapon
Number
Civilian 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 ; ?
CivCameraRecord
Text
Civilian 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 ; ?
CivUsedProfanity
Text
Did civilian receive 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 ; ?
CivHasInjury
Text
Received medical care?
Indicates whether or not Subject received Medical Care for any Injuries from the Use of Force. Important for asking followup questions.
Values: Y ; N ; ?
CivHasInjuryCare
Text
Given Citation (Not Arrested)
Indicates whether or not this Subject was given a Citation with Charges as a result of this Stop.
Values: Y ; N ; ?
CivGivenCitation
Text
Given Written Warning
Indicates whether or not this Subject was only given a Warning as a result of this Stop.
Values: Y ; N ; ?
CivGivenWarning
Text
Other charges filed?
Citation or arrest charge(s) an Officer issued a Subject as a result of this Stop. Important for evaluating wrongful citation Allegations and tracking citation trends.
CivChargesOther
Text-Long
Officers
(Off)

Officers

Includes law enforcement personnel who engaged in alleged misconduct or who witnessed alleged misconduct mentioned in a Complaint. This information is vital for verifying the identity of named and unnamed officers who were involved in alleged police misconduct.
4 Tables with Foreign Keys A new Verified Officer Record should be created for every Officer if a matching Verified Officer does not exist upon creation of a new Complaint record (as decided by Complainant).
Group: People Involved In Complaint
Type: Data
19 Fields Total
4 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
Officers Unique ID
OffID
Number, Indexed, Primary Key
Is Verified Officer Record?
This marks the verified record of an Verified Officer Record with the most accurate and current information. This is vital for tracking Officers involved in multiple Incidents over time.
Values: 0 ; 1
OffIsVerified
Number Indexed
Complaint ID ^
The unique number of the Complaint record related to this Officer's record. Vital for associating Officer with all other Complaint data.
OffComplaintID
Number Indexed
Officer's role in this incident
Indicates category of an Officer. 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
OffRole
Text
Department ID ^
The unique number of the Department record related to this Officer. Vital for associating this Officer with other Department data.
OffDeptID
Number Indexed
Person Contact Info ID
The primary Person Contact record number connected to a Officer's record. Important for linking name and basic contact information to an Officer.
Physical Description ID
The primary Physical Description record number connected to a Officer's record. Important for linking name and basic contact information to a Officer.
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 ; ?
OffGiveName
Text
Using Vehicle at Start
Indicates whether or not the Complainant intends to provide information about the vehicle this Officer was in. This is important for later reloading Complaint forms with the user's previous response.
Values: Y ; N ; ?
OffHadVehicle
Text
Department Precinct/District ^
The district within a city or municipality where an Officer is assigned. This information might help us verify an Officer's identity.
Notes: Most Departments will NOT have precinct. However, precincts or districts usually exist in big cities.
OffPrecinct
Text
Badge Number ^
An identification number 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
OffBadgeNumber
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.
OffIDnumber
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 ; ?
OffOfficerRank
Text
Officer 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 ; ?
OffDashCam
Text
Officer 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 ; ?
OffBodyCam
Text
Duty status during incident
Category indicates whether Officer was on-duty or off-duty during Incident. Particularly important for investigating Allegations against off-duty officers.
Values: On-Duty ; Off-Duty ; ?
OffDutyStatus
Text
Officer 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 ; ?
OffUniform
Text
Officer 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 ; ?
OffUsedProfanity
Text
Additional Details
Supplemental narrative details about Officers involved in Incident. Important for verifying an Officer's identity or Department.
Notes: This field only used for light/bronze version of user experience.
OffAdditionalDetails
Text-Long
Officer received compliments?
Indicates whether or not the Complainant completed a Compliment page for this Officer.
Values: Y ; N
OffGaveCompliment
Text
PersonContact
(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.
5 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
PersonContact Unique ID
PrsnID
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
PrsnNamePrefix
Text
First Name ^
The legal given name of persons that we use in all formal communications with persons or regarding Complaints.
PrsnNameFirst
Text
Nickname ^
Preferred first name of persons that we may use in less-formal communications with them. Important for maintaining cordial relationships with these key contacts.
Notes: Use only if selected. Field may otherwise be left blank
PrsnNickname
Text
Middle Name ^
The name of a person, placed between their first name and before their surname, that we use in all formal communications with persons or regarding Complaints.
Notes: Use only if selected. Field may otherwise be left blank
PrsnNameMiddle
Text
Last Name ^
The legal surname of persons that we use in formal communications with or regarding Complaints.
PrsnNameLast
Text
Name Suffix ^
Letters added after a person's last name related to this record -- which provide additional information about their title or inherited name -- that we use in all formal communications with persons or regarding Complaints.
PrsnNameSuffix
Text
Email ^
The valid email address provided by or for a person. This is the primary way we communicate with them.
PrsnEmail
Text
Home Phone ^
The contact number where a person can be reached at their home. This is usually a landline phone, which cannot receive text messages.
Notes: At least one phone number -- be it Home, Work, or Mobile -- should be required for public complaints.
PrsnPhoneHome
Text
Work Phone ^
The contact number where persons can be reached at their place of business. This is usually a landline phone, which cannot receive text messages.
Notes: We might need to add an "extension" field for some work phone numbers.
PrsnPhoneWork
Text
Mobile Phone ^
The contact number where persons can be reached on their cellular device. We may use this number for both text and voice communications.
PrsnPhoneMobile
Text
Street Address ^
The first line of the postal location where a person resides or is able to receive mail. This is an integral component of a person's complete address.
Notes: If they can't reliable receive mail at residence, use reliable mailing address instead.
PrsnAddress
Text
Street Address 2 ^
The second line, if needed, of the postal location where a person resides or is able to receive mail. This is an integral component of a person's complete address.
PrsnAddress2
Text
City ^
The metropolitan area where a person resides or is able to receive mail. This is an integral component of a person's complete address.
PrsnAddressCity
Text
State ^
The state or district where a person resides or is able to receive mail. This is an integral component of a person's complete address.
PrsnAddressState
Text
Zip Code ^
The postal code where a person resides or is able to receive mail. This is an integral component of a person's complete address.
PrsnAddressZip
Text
Date of Birth ^
The month, day, and year a person was born. This information is used to help verify a person's identity.
Notes: If Date of Birth is unknown, the "Age" field will become visible, which offers age ranges to be selected.
PrsnBirthday
Date
Facebook ^
This is the URL address of a person's Facebook page. This is used to help verify their identities and for communicating with them if they select this as a preferred method of contact.
PrsnFacebook
Text
User ID
Indicates the unique User ID number of the User owning the data stored in this record for this Contact Info.
PrsnUserID
Number
PhysicalDesc
(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.
3 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
PhysicalDesc Unique ID
PhysID
Number, Indexed, Primary Key
Gender ^
The sex classification of a Civilian or Officer. This category is used to help verify a Civilian or Officer'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 ; ?
PhysGender
Text
Gender Other
Other gender classifications of a Civilian or Officer, with suggestions provided by the Definitions for Gender Identity. This category is used to help verify a Civilian or Officer's identity.
PhysGenderOther
Text
Age ^
Estimated age in years of a Civilian or Officer. This information is used to verify a Civilian or Officer'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)
PhysAge
Number
Height ^
Estimated height of a Civilian or Officer in feet and inches. This information might help verify a Civilian or Officer'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.
PhysHeight
Number
Body Type ^
General category of a Civilian or Officer's body shape. This information might help verify a Civilian or Officer's identity.
Values: Body Types (Definitions)
PhysBodyType
Number
Physical Description ^
The attributes of any prominent physical characteristics on a Civilian or Officer. This information might help verify a Civilian or Officer's identity.
PhysGeneralDesc
Text
PhysicalDescRace
(PhysRace)

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
PhysicalDescRace Unique ID
PhysRaceID
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 Civilian or Officer.
PhysRacePhysDescID
Number
Race
The Complainant-selected or self-ascribed racial classification of a Civilian or Officer. This classification helps verify the identity of Subject of Complaints 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)
PhysRaceRace
Number
Vehicles
(Vehic)

Vehicles

This table includes identifying information about Civilian and Officer vehicles. This data helps investigators locate or verify the identities of Officers and Civilians involved in an Incident.
2 Tables with Foreign Keys
Group: People Involved In Complaint
Type: Subset
9 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
Vehicles Unique ID
VehicID
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.
VehicComplaintID
Number
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
VehicIsCivilian
Number
Type of Vehicle ^
Indicates what type of vehicle was involved during this Incident.
Values: Transportation Officer (Definitions)
VehicTransportation
Number
Vehicle Unmarked or Undercover?
Indicates whether or not an Officer's vehicle was an unmarked car, meaning it looks like a normal vehicle with no badges, graphics, or visible police lights. This is important for tracking trends of Officer behavior.
Values: Y ; N ; ?
VehicUnmarked
Text
Vehicle Make ^
The company that manufactured a Civilian's vehicle. This information might help verify a Civilian's identity.
Notes: Vehicle makes include Toyota, Ford, Honda, Chevrolet, etc.
VehicVehicleMake
Text
Vehicle Model ^
The name or brand of the Civilian's vehicle. This information might help verify a Civilian's identity.
Notes: Vehicle models include Toyota Camry, Ford F-Series, Chevrolet Silverado, etc.
VehicVehicleModel
Text
Vehicle Description ^
Additional attributes of the Civilian's vehicle. This information might help verify a Civilian's identity.
Notes: What color was the vehicle? Did it have any distinguishing characteristics, such as dents, customizations, etc?
VehicVehicleDesc
Text
License Plate Number ^
The tag numbers on the Civilian's vehicle. This information might help verify a Civilian's identity.
VehicVehicleLicence
Text
Vehicle Number
A unique vehicle ID number. Important for verifying an Officer's identity or Department.
Notes: This is not the same as License Number.
VehicVehicleNumber
Text
Departments
(Dept)

Police Departments

Includes Local, State, and Federal law enforcement agencies and their jurisdictional boundaries. This table information allows the organization to identify and track Departments associated with Complaints.
6 Tables with Foreign Keys
Group: People & Groups Involved In Complaint Process
Type: Data
26 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
Departments Unique ID
DeptID
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.
DeptName
Text
URL Slug
This defines the version of the department name which is compatible and ideal to be used as part of a website URL. This is vital for creating public pages of the website which are intuitive and professional.
DeptSlug
Text
Type of Department
The general category of a particular law enforcement agency. This information helps us identify the responsibilities and jurisdiction of a given Police Department.
Notes: Detailed Google Doc: https://docs.google.com/document/d/1OZBZYd8V7gC3es46Z5sWrfu2IRvhrK1CkfV8vKp8xgY/edit?usp=sharing
Values: Types of Departments (Definitions)
DeptType
Number
Activity Status
The current activity status of the Department, 1 for active, 0 for inactive. This vital for identifying Departments which have been closed since the creation of the original data set.
Values: Department Status (Definitions)
DeptStatus
Number
Contact Info Last Verified ^
Indicates the date and time when Police Department contact information was last verified. This is crucial for keeping track of the accuracy of Police Department contact information.
Notes: NULL value means unverified.
DeptVerified
Date&Time
Email ^
The valid public-facing email address for Police Departments. If we don't have a direct email contact with an Internal Affairs Staffer, this will be the primary way we contact Police Departments.
Notes: This is the email that would be featured on their OPC web page for this Department. We might include this in addition to a general Internal Affairs email contact, if one exists.
DeptEmail
Text
Work Phone ^
The public contact number where Police Departments can be reached.
Notes: We might need to add an "extension" field for some work phone numbers.
DeptPhoneWork
Text
Street Address ^
The first line of the postal location where a Police Department receives mail. This is an integral component of a Police Department's complete address.
DeptAddress
Text
Street Address 2 ^
The second line, if needed, of the postal location where a Police Department receives mail. This is an integral component of a Police Department's complete address.
DeptAddress2
Text
City ^
The metropolitan area where a Police Department receives mail. This is an integral component of a Police Department's complete address.
DeptAddressCity
Text
State ^
The state or district where a Police Department receives mail. This is an integral component of a Police Department's complete address.
DeptAddressState
Text
Zip Code ^
The postal code where a Police Department receives mail. This is an integral component of a Police Department's complete address.
DeptAddressZip
Text
County
The state region where a local Police Department is headquartered or has jurisdiction. This information helps us track which Police Departments have jurisdiction over a specific Incident location.
Notes: Not applicable to Departments with Federal and Statewide jurisdictions.
DeptAddressCounty
Text
Openness Score
This indicates an overall rating for a Police Department's online presence and the quality of their Complaint submission process. This is important for encouraging Departments to improve these metrics.
DeptScoreOpenness
Text
Total Number of Employees
The total number of sworn Officers employed by a Police Department. This information will be used for comparative statistical purposes and to determine the most likely Department on-scene during a particular Incident.
Notes: For example, if a User doesn't know which Police Department employed a given Subject Officer, the field list for Police Departments would begin by listing the local department with the highest number of total sworn officers.
DeptTotOfficers
Number
Jurisdiction Population
The total number of people who live within the geographical boundaries of a Police Department. This information will help identify policing trends and for comparative statistical purposes.
DeptJurisdictionPopulation
Number
Jurisdiction GPS
A series of GPS coordinates, representing the geographical polygon of a department's jurisdiction. Important for identifying the Police Department attached to a Complaint -- especially when Complainants are unsure.
DeptJurisdictionGPS
Text-Long
User ID ^
Indicates the unique User ID number of the User owning the data stored in this record for this Experience.
DeptUserID
Number
Experience Node Progress ^
Indicates the unique Node ID number of the last Experience Node loaded during this User's Experience.
DeptSubmissionProgress
Number
Tree Version Number ^
Stores the current version number of this User Experience, important for tracking bugs.
DeptTreeVersion
Text
A/B Testing Version ^
Stores a complex string reflecting all A/B Testing variations in effect at the time of this User's Experience of this Node.
DeptVersionAB
Text
Unique String For Record ^
This unique string is for cases when including the record ID number is not appropriate.
DeptUniqueStr
Text
IP Address ^
Encrypted IP address of the current user.
DeptIPaddy
Text
Using Mobile Device ^
Indicates whether or not the current user is interacting via a mobile deviced.
DeptIsMobile
Text
Geolocation Latitude
The geographic coordinate that specifies the north-south position of the point on the Earth's surface where a Police Department is located.
DeptAddressLat
Number-Decimals
Geolocation Longitude
The geographic coordinate that specifies the east-west position of the point on the Earth's surface where a Police Department is located.
DeptAddressLng
Number-Decimals
Oversight
(Over)

Oversight 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 OPC.
1 Table with Foreign Key
Group: People & Groups Involved In Complaint Process
Type: Data
38 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
Oversight Unique ID
OverID
Number, Indexed, Primary Key
Oversight Type
Indicates category of Oversight Agency. This information is essential for determining if a given Oversight Agency is either an Internal Affairs Department or a Citizen Oversight Agency. (They cannot be both.)
Values: Oversight Agency Types (Definitions)
OverType
Number
Civilian Oversight Model
Indicates subcategory of Civilian Oversight Agency, either Investigative, Review, or Audit. This information is essential for determining scope of a given Oversight Agency.
Values: Civilian Oversight Models (Definitions)
OverCivModel
Number
User ID ^
The primary User Authentication number connected to this Oversight Agency. This number allows Oversight Agency contacts to log into the OPC system.
OverUserID
Number
Department ID ^
The unique number of the Department record related to this Oversight Agency. This information helps us identify the proper Oversight Agency with jurisdiction over an OPC Complaint directed at a specific Police Department.
OverDeptID
Number Indexed
Agency Name
The official name of an Oversight Agency. This is an essential component of a Oversight Agency's complete address.
OverAgncName
Text
Contact Info Last Verified ^
Indicates the date and time when an Oversight Agency's Police contact information was last verified. This is crucial for keeping track of the accuracy of Oversight Agency's contact information.
Notes: NULL value means unverified.
OverVerified
Date&Time
Name Prefix ^
Letters placed before an Oversight Agency contact's first name that we use in all formal communications with Oversight Agency contacts or regarding Oversight Agency contacts.
Notes: Use only if selected. Field may otherwise be left blank
OverNamePrefix
Text
First Name ^
The legal given name of an Oversight Agency contact that we use to identify them. We also use this in all formal communications with Oversight Agency contacts and regarding Oversight Agency contacts.
OverNameFirst
Text
Nickname ^
Preferred first name of Oversight 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
OverNickname
Text
Middle Name ^
The name of an Oversight Agency contact, placed between their first name and before their surname, that we use in all formal communications with Oversight Agency contacts or regarding Oversight Agency contacts.
Notes: Use only if selected. Field may otherwise be left blank
OverNameMiddle
Text
Last Name ^
The legal surname of an Oversight Agency contact that we use in formal communications with or regarding an Oversight Agency contact.
OverNameLast
Text
Name Suffix ^
Letters added after an Oversight Agency contact's last name -- which provide additional information about their title or inherited name -- that we use in all formal communications with Oversight Agency contacts or regarding Oversight Agency contacts.
OverNameSuffix
Text
Title ^
The job position of an Oversight Agency contact. We might use this in formal communications with Oversight Agency contacts or regarding Oversight Agency contacts.
OverTitle
Text
ID Number
A unique number used to identify each Oversight Agency. We assign a number to all active Oversight Agencies and remains with them for the duration of their existence.
OverIDnumber
Text
Website ^
The page URL of the Civilian Oversight Agency or Internal Affairs Department. Ideally provides clear information about the Police Department's complaint process.
Notes: IMPORTANT: Oversight 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.
OverWebsite
Text
Facebook
This is the URL address of a Agency's Facebook page. If available, this will be included in each Agency's OPC page so that members of public may have another way to voice their complements or concerns with the department.
OverFacebook
Text
Twitter
This is the URL address of a Agency's Twitter page. If available, this will be included in each Agency's OPC page so that members of public may have another way to voice their complements or concerns with the department.
OverTwitter
Text
YouTube URL
This is the URL address of a Agency's YouTube page. If available, this will be included in each Agency's OPC page so that members of public see this aspect of the department's web presence.
OverYouTube
Text
Homepage Complaint Link?
Indicates whether or not a Police Department--s main homepage has a visible link to complaint information. (It should, but most will not.)
Values: Y ; N
OverHomepageComplaintLink
Text
Website Complaint Information URL ^
Indicates whether or not the designated Oversight Agency URL (if there is one) has clear-cut info on how about how the complaints process works. (Most will not.)
OverWebComplaintInfo
Text
Complaint PDF
Indicates whether or not the designated Police Department provide a PDF of their paper Complaint form online. This URL is very important for providing Complainants with easy access to the form they might be required to fill out for an investigation to begin.
OverComplaintPDF
Text
Website Complaint Form URL
Indicates whether or not the designated Police Department website has a form to allow complaints to be submitted online. (Most will not.)
OverComplaintWebForm
Text
Email ^
The valid email address for an Oversight Agency contact. This is the primary way we communicate with them.
OverEmail
Text
Work Phone ^
The contact number where Oversight Agency contact can be reached.
Notes: We might need to add an "extension" field for some work phone numbers.
OverPhoneWork
Text
Street Address ^
The first line of the postal location where an Oversight Agency contact receive mail. This is an integral component of an Oversight Agency contact's complete address.
OverAddress
Text
Street Address 2 ^
The second line, if needed, of the postal location where an Oversight Agency contact receives mail. This is an integral component of an Oversight Agency contact's complete address.
OverAddress2
Text
City ^
The metropolitan area where an Oversight Agency contact receives mail. This is an integral component of an Oversight Agency contact's complete address.
OverAddressCity
Text
State ^
The state or district where an Oversight Agency contact receives mail. This is an integral component of an Oversight Agency contact's complete address.
OverAddressState
Text
Zip Code ^
The postal code where an Oversight Agency contact receives mail. This is an integral component of an Oversight Agency contact's complete address.
OverAddressZip
Text
Days Until Submission Deadline
Indicates that there is a policy stipulating a maximum number of days between when the Incident occurred and when a Complaint must be submitted [or received?] by an Oversight Agency in order for it to be properly investigated. This information might help OPC 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.
OverSubmitDeadline
Number
Official Form Not Required
This flags that the designated Police Department requires their own paper form to investigate, important for rating Departments.
Values: 0 ; 1
OverOfficialFormNotReq
Number
Anonymous Complaints Investigated
This flags that the designated Police Department does accept -- and will investigate -- complaints which are submitted anonymously. This is important for department ratings and OPC compatibility.
Values: 0 ; 1
OverOfficialAnon
Number
Way to Submit: Online Form
This flags that the designated Police Department currently accepts complaints when submitted via an online form, important for rating Departments.
Values: 0 ; 1
OverWaySubOnline
Number
Way to Submit: Email
This flags that the designated Police Department currently accepts complaints when submitted via email, important for rating Departments.
Values: 0 ; 1
OverWaySubEmail
Number
Way to Submit: Verbal Phone
This flags that the designated Police Department currently accepts complaints when submitted verbally over the phone, important for rating Departments.
Values: 0 ; 1
OverWaySubVerbalPhone
Number
Way to Submit: Paper Form Mail
This flags that the designated Police Department currently accepts complaints when submitted on their paper form by mail, important for rating Departments.
Values: 0 ; 1
OverWaySubPaperMail
Number
Way to Submit: Requires In-Person Paper Form
This flags that the designated Police Department currently accepts complaints when submitted on their paper form in-person, important for rating Departments.
Values: 0 ; 1
OverWaySubPaperInPerson
Number
Way to Submit: Requires Notary
This flags that the designated Police Department currently requires complaints to be submitted with a notary.
Values: 0 ; 1
OverWaySubNotary
Number
Partners
(Part)

Partners

People and organizations who access our detailed complaints data to assist their police accountability efforts and research. Information in this table is vital to the organization, because it enables us to communicate with Partners about our collaboration and service offerings.
1 Table with Foreign Key
Group: People & Groups Involved In Complaint Process
Type: Data
6 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
Partners Unique ID
PartID
Number, Indexed, Primary Key
Customer Occupation
The profession of a Customer. This information helps us keep track of who is using our product and identify potential marketing opportunities for new Customers.
Values: Customer Types (Definitions)
PartType
Number
User ID ^
The primary User Authentication number connected to a Customer's record. This number allows Customers to log into the OPC system.
PartUserID
Number
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.
Title ^
The job position of a Customer within an organization. We might use this in formal communications with Customers or regarding Customers.
PartTitle
Text
Company Name ^
The full name of the business or or organization a Customer represents. This is an integral component of a Customer's complete address.
PartCompanyName
Text
Website ^
The home page of a Customer's business or organization. Might include important updated information about Customers.
PartCompanyWebsite
Text
Administrators
(Adm)

Administrators

OPC staff and volunteers with special access permission to review and manage Complaints. This information allows OPC management to measure the performance of individual Administrators.
1 Table with Foreign Key
Group: People & Groups Involved In Complaint Process
Type: Data
2 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
Administrators Unique ID
AdmID
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 OPC system.
AdmUserID
Number
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.
LinksComplaintDept
(LnkComDept)

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.
LnkComDeptComplaintID
Primary Key, Number
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 Oversight Agency should be contacted.
LnkComDeptDeptID
Composite, Primary Key, Number Indexed
LinksComplaintOversight
(LnkComOver)

Complaint-Oversight Links

Includes linkages between Oversight Agencies involved in a Complaint. This allows us to track the progress of Complaints being investigated by more than one Oversight Agency.
Group: Complaint Linkage Tables
Type: Linking
10 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.
LnkComOverComplaintID
Composite, Primary Key, Number Indexed
Department ID
The unique number of the Police Department record involved with this Complaint. This number helps track the investigative progress of the Oversight Agencies where this Complaint has been filed.
LnkComOverDeptID
Number
Oversight ID ^
The unique number of the Oversight Agency record involved with this Complaint. This number helps track the investigative progress of any Oversight Agencies who have jurisdiction over this Complaint.
LnkComOverOverID
Composite, Primary Key, Number Indexed
Submitted to Oversight
Indicates date and time when an electronic Complaint was sent to an appropriate Oversight Agency. This is an essential for tracking an Oversight Agency's response -- or non-response -- rates to OPC-submitted Complaints.
( default value: NULL )
LnkComOverSubmitted
Date&Time
No Response from Oversight
Indicates date and time when a Complainant last confirmed that they have heard no response from the appropriate Oversight Agency. This is essential for tracking an Oversight Agency's response time when acknowledging receipt of OPC-submitted Complaints.
LnkComOverStillNoResponse
Date&Time
Received by Oversight
Indicates date and time when an electronic Complaint was received by an appropriate Oversight Agency. This is essential for tracking an Oversight Agency's response time when acknowledging receipt of OPC-submitted Complaints.
Notes: We don't yet know how many departments will opt to electronically indicate that they've received OPC complaints. In fact, many will likely skip this process and directly contact Complainants.
( default value: NULL )
LnkComOverReceived
Date&Time
Investigated by Oversight
Indicates the date and time when the Complainant was contacted by an Oversight 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 Oversight Agency response rates and response times.
( default value: NULL )
LnkComOverInvestigated
Date&Time
Oversight Report Uploaded
Indicates the date and time when an Oversight Agency contact regarding the status of their Complaint or to investigate any Allegations contained in the Complaint.
LnkComOverReportDate
Date&Time
Oversight Investigation Report
The Evidence record storing the final determination letter submitted by the Oversight Agency regarding an OPC-submitted Complaint. This report provides essential information for tracking how Oversight Agencies respond to citizen complaints.
Notes: Complainants must scan these reports and upload them to their Complaint as Evidence.
LnkComOverOversightReportEvidenceID
Number
Agency Complaint Number
A unique number used to identify Complaints. It is assigned by the Oversight Agency investigating an OPC-generated Complaint.
LnkComOverAgencyComplaintNumber
Number
LinksComplimentDept
(LnkCompliDept)

Compliment-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
LinksComplimentDept Unique ID
LnkCompliDeptID
Number, Indexed, Primary Key
Compliment ID
The unique number of the Compliment record, vital to associating with all other Compliment data.
LnkCompliDeptComplimentID
Number
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 Oversight Agency should be contacted.
LnkCompliDeptDeptID
Number
LinksOfficerAllegations
(LnkOffAlle)

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.
LnkOffAlleOffID
Composite, Primary Key, Number
Allegation ID
This helps to link one Officer to one Allegation, via the Allegation's primary ID.
LnkOffAlleAlleID
Composite, Primary Key, Number
LinksOfficerEvents
(LnkOffEve)

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.
LnkOffEveOffID
Composite, Primary Key, Number
Event ID
This helps to link one Officer to one Event (which represents one Stop, Search, Use of Force, or Arrest), via the Event's primary ID.
LnkOffEveEveID
Composite, Primary Key, Number
LinksOfficerVehicles
(LnkOffVehic)

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
2 Fields Total
2 Outgoing Keys
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
LinksOfficerVehicles Unique ID
LnkOffVehicID
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.
LnkOffVehicOffID
Number
Vehicle ID
The unique number of the Vehicle record being linked in this record. Vital for associating Civilians or Officers with the Vehicle.
LnkOffVehicVehicID
Number
LinksCivilianAllegations
(LnkCivAlle)

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.
LnkCivAlleCivID
Composite, Primary Key, Number
Allegation ID
This helps to link one Civilian to one Allegation, via the Allegation's primary ID.
LnkCivAlleAlleID
Composite, Primary Key, Number
LinksCivilianEvents
(LnkCivEve)

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 Civilian to one Event (which represents one Stop, Search, Use of Force, or Arrest), via the Civilian's primary ID.
LnkCivEveCivID
Composite, Primary Key, Number
Event ID
This helps to link one Civilian to one Event (which represents one Stop, Search, Use of Force, or Arrest), via the Event's primary ID.
LnkCivEveEveID
Composite, Primary Key, Number
LinksCivilianVehicles
(LnkCivVehic)

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
LinksCivilianVehicles Unique ID
LnkCivVehicID
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.
LnkCivVehicCivID
Number
Vehicle ID
The unique number of the Vehicle record being linked in this record. Vital for associating Civilians with the Vehicle.
LnkCivVehicVehicID
Number
Vehicle Role
When a Vehicle is associated with multiple Civilians, this information is important for identifier for which person was driving a Vehicle.
Values: Driver ; Passenger
LnkCivVehicRole
Text
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
VisID
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.
VisUserID
Number
Experience Node Progress ^
Indicates the unique Node ID number of the last Experience Node loaded during this User's Experience.
VisSubmissionProgress
Number
Tree Version Number ^
Stores the current version number of this User Experience, important for tracking bugs.
VisTreeVersion
Text
A/B Testing Version ^
Stores a complex string reflecting all A/B Testing variations in effect at the time of this User's Experience of this Node.
VisVersionAB
Text
Unique String For Record ^
This unique string is for cases when including the record ID number is not appropriate.
VisUniqueStr
Text
IP Address ^
Encrypted IP address of the current user.
VisIPaddy
Text
Using Mobile Device ^
Indicates whether or not the current user is interacting via a mobile deviced.
VisIsMobile
Text
PrivilegeProfiles
(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.
The Department ID field can be used instead of the Complaint ID to allow privileges to a User for all Complaints associated with a certain Police Department.
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.
PrivUserID
Composite, Primary Key, Number
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).
PrivComplaintID
Composite, Primary Key, Number Indexed
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).
PrivDeptID
Composite, Primary Key, Number Indexed
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
PrivAccessLevel
Text
AdminActions
(AdmAct)

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
6 Fields Total
1 Outgoing Key
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
AdminActions Unique ID
AdmActID
Number, Indexed, Primary Key
User ID ^
The primary User Authentication number connected to this Action, vital for tracking System User behavior and system security.
AdmActUserID
Number
Timestamp ^
The date and time of an Administrator's specific Action affecting system data, vital for tracking System User behavior and potentially providing security validation.
( default value: NOW() )
AdmActTimestamp
Date&Time
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.
AdmActTable
Text
Table Record ID
The unique primary ID number of the record in the table whose contents are being altered by a System User (usually an Administrator), at any point after a Complaint has been submitted. This is important tracking the specific record being altered.
AdmActRecordID
Number
Old Data Values
A data dump of the initial value of all the table record's fields' contents, or at least those with values being changed by a System User. This is vital to provide breadcrumbs to correct potential problems.
Notes: Data formatting TBD, but could be like JSON or XML.
AdmActOldData
Text-Long
New Data Values
A data dump of the newly updated values of all the table record's fields' contents, or at least those with values being changed by a System User. This is vital to provide breadcrumbs to correct potential problems.
Notes: Data formatting TBD, but could be like JSON or XML.
AdmActNewData
Text-Long
users
()

Users

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

Edits: Police Departments

Each record stores a copy of individual edits made to unique records in the Departments table.
1 Table with Foreign Key
Group: Record Edit Histories
Type: Validation
2 Fields Total
Field Name (in English), Description, Notes, Value Options
Field Name (in Database), Data Type, Key Info
Zedit_Departments Unique ID
ZedDeptID
Number, Indexed, Primary Key
User ID
Indicates the unique User ID number of the User editing the original Departments record.
ZedDeptUserID
Number
Duration of Edit
How many seconds the user spent editing this record.
ZedDeptDuration
Number
Zedit_Oversight
(ZedOver)

Edits: Oversight 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
Zedit_Oversight Unique ID
ZedOverID
Number, Indexed, Primary Key
Department Edit Record ID
The unique ID number of the edit record tied to this extended edit record.
ZedOverZedDeptID
Number
Completed Online Research
Indicates whether or not the volunteer completed the online portion of the police department research process.
Values: 0 ; 1
ZedOverOnlineResearch
Number
Made Department Call
Indicates whether or not the volunteer completed the research with a phone call to the police department.
Values: 0 ; 1
ZedOverMadeDeptCall
Number
Made Internal Affairs Call
Indicates whether or not the volunteer completed the research with a phone call to the internal affairs office.
Values: 0 ; 1
ZedOverMadeIACall
Number
Volunteer Notes
Provides a space for a volunteer to leave any significant notes about this department (or research attempt) for future volunteers or staff.
ZedOverNotes
Text-Long
zVolunStatDays
(VolunStat)

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
zVolunStatDays Unique ID
VolunStatID
Number, Indexed, Primary Key
Date
Indicates the date for which this record's totals are cached.
VolunStatDate
Date
Total Signups
Indicates the total number of new volunteer signups on this date.
( default value: 0 )
VolunStatSignups
Number
Total Logins
Indicates the total number of new volunteer logins on this date.
( default value: 0 )
VolunStatLogins
Number
Total Unique Research Users
Indicates the total number of unique volunteers making research edits on this date.
( default value: 0 )
VolunStatUsersUnique
Number
Total Unique Department Researched
Indicates the total number of unique departments receiving research edits on this date.
( default value: 0 )
VolunStatDeptsUnique
Number
Total Online Research Edits
Indicates the total number of online research edits on this date.
( default value: 0 )
VolunStatOnlineResearch
Number
Total Department Call Edits
Indicates the total number of department call edits on this date.
( default value: 0 )
VolunStatCallsDept
Number
Total Internal Affairs Call Edits
Indicates the total number of internal affairs call edits on this date.
( default value: 0 )
VolunStatCallsIA
Number
Total Call Edits
Indicates the total number of department and internal affairs call edits on this date.
( default value: 0 )
VolunStatTot
Number
Total Edits Saved
Indicates the total number of edits saved on this date.
( default value: 0 )
VolunStatTotalEdits
Number
Total Online Research Volunteer Edits
Indicates the total number of online research edits by volunteers on this date.
( default value: 0 )
VolunStatOnlineResearchV
Number
Total Department Call Volunteer Edits
Indicates the total number of department call edits by volunteers on this date.
( default value: 0 )
VolunStatCallsDeptV
Number
Total Internal Affairs Call Volunteer Edits
Indicates the total number of internal affairs call edits by volunteers on this date.
( default value: 0 )
VolunStatCallsIAV
Number
Total Call Volunteer Edits
Indicates the total number of department and internal affairs call edits by volunteers on this date.
( default value: 0 )
VolunStatTotV
Number
Total Edits Saved by Volunteers
Indicates the total number of edits by volunteers saved on this date.
( default value: 0 )
VolunStatTotalEditsV
Number
zVolunUserInfo
(UserInfo)

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
zVolunUserInfo Unique ID
UserInfoID
Number, Indexed, Primary Key
User ID
Indicates the unique User ID number of the User record this table extends.
UserInfoUserID
Number
Person Contact Record ID
Indicates the unique Person Contact ID number for the record used to store this system User's personal information within OPC's data table.
UserInfoPersonContactID
Number
Stars Total
Indicates the total number of stars this volunteer has earned.
( default value: 0 )
UserInfoStars
Number
Stars from Online Research
Indicates the total number of stars this volunteer has earned from completing online research.
( default value: 0 )
UserInfoStars1
Number
Stars from Department Calls
Indicates the total number of stars this volunteer has earned from calling and speaking with police departments.
( default value: 0 )
UserInfoStars2
Number
Stars from Interal Affairs Calls
Indicates the total number of stars this volunteer has earned from calling and speaking with internal affairs offices.
( default value: 0 )
UserInfoStars3
Number
Unique Departments
Indicates the number of unique police departments for which this volunteer contributed research.
( default value: 0 )
UserInfoDepts
Number
Average Time Researching Department
Indicates average time (in seconds) this volunteer spend editing each department they worked on.
( default value: 0 )
UserInfoAvgTimeDept
Number
zComplaintReviews
(ComRev)

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
zComplaintReviews Unique ID
ComRevID
Number, Indexed, Primary Key
Complaint ID
Indicates the unique Complaint ID number of the Complaint being reviewed.
ComRevComplaint
Number
User ID
Indicates the unique User ID number for the person performing this Complaint review.
ComRevUser
Number
Review Date
Indicates the date when this Complaint review was submitted.
ComRevDate
Date
Review Type
Indicates which type of Complaint review is being submitted.
ComRevType
Text
Complaint Type
Indicates the type of Complaint confirmed in this review.
Values: OPC Staff/Internal Complaint Type (Definitions)
ComRevComplaintType
Number
Review Status
Indicates which new status has been chosen for this Complaint, if the current User has permissions.
ComRevStatus
Text
Next Action After Review
Indicates the reviewers recommended next action for this Complaint.
ComRevNext Action
Text
User Note
Indicates any other notes or information the current User wants to share with their review.
ComRevNote
Text-Long
Focused On One Incident
Indicates whether or not this Complaint remained focused on one Incident.
Values: 0 ; 1
ComRevOneIncident
Number
Has Civilian Contact
Indicates whether or not this Complaint includes at least one civilian contact for investigation.
Values: 0 ; 1
ComRevCivilianContact
Number
At Least One Officer
Indicates whether or not this Complaint includes information about at least one officer to be investigated.
Values: 0 ; 1
ComRevOneOfficer
Number
Has At Least One Allegation
Indicates whether or not this Complaint includes at least one Allegation for investigation, and is a story about police misconduct.
Values: 0 ; 1
ComRevOneAllegation
Number
Has Evidence Uploaded
Indicates whether or not this Complaint includes some uploaded evidence supporting their Allegations.
Values: 0 ; 1
ComRevEvidenceUploaded
Number
English Skill Level
Indicates the reviewer's opinion about the Complainant's skill level with English writing.
Values: 0 ; 1 ; 2 ; 3 ; 4
ComRevEnglishSkill
Number
Readability Level
Indicates the reviewer's opinion about how easy it was to read the Complaint.
Values: 0 ; 1 ; 2 ; 3 ; 4
ComRevReadability
Number
Consistency Level
Indicates the reviewer's opinion about the Complainant's level of consistency, meaning freedom from variation or contradiction.
Values: 0 ; 1 ; 2 ; 3 ; 4
ComRevConsistency
Number
Seems Realistic
Indicates the reviewer's opinion about how realistic the Complainant's story is.
Values: 0 ; 1 ; 2 ; 3 ; 4
ComRevRealistic
Number
Outrage Level
Indicates the reviewer's personal level of outrage about their understanding of the Incident.
Values: 0 ; 1 ; 2 ; 3 ; 4
ComRevOutrage
Number
Explicit Language Used
Indicates whether or not the reviewer thinks this Complaint contains explicit language.
Values: 0 ; 1
ComRevExplicitLang
Number
Contains Graphic Content
Indicates whether or not the reviewer thinks this Complaint contains particularly graphic content.
Values: 0 ; 1
ComRevGraphicContent
Number