Spike: Review Police data available for use on VAMC police page

[xiongjaneg]

Description

The data available from Axon needs to be made available so that we can display it on a VAMC page. For the near term, this is likely a manual CSV. The vendor API is likely not available until next spring, March or April. Lighthouse likely would not be involved until that time.

Details received from vendor

• Directed to Axon Records DataStore Field Mappings for data details
  • Michelle's note: based on my quick review, there is clearly more data available in the full SQL select statement than will be needed/appropriate for our needs. We need to ensure we do not pull in PII.
• Confirmed that data will be mapped to VISN > health care system > facility > location within facility (i.e. department)
  • Michelle's note: Facility level should be sufficient level of granularity - location within facility is not necessary
• the vendor recommended we reference the Tucson Police data and Analysis site, which is where data shared in this comment on #15376 was found

Additional context

• the data uses codes from two crime reporting systems: Uniform Crime Reporting (UCR) and National Incident-Based Reporting System (NIBRS)
  • NIBRS User manual

Income Limits example

Income Limits also uses a CSV and a vets-api API implementation to pull / display data.

• Josh Mills built the API in Ruby
• Daniel Sasser managed the backend data store stories

Pending

how to identify data for the following (Michelle's note: I've already asked if this data is expected to be in the same database or separate data source)

• use of force and weapons discharge by Department police officers
• complaints, investigations, and disciplinary actions regarding Department police officers.
• contact information

Data elements

Required (based on understanding of legislature)

• Date
• Facility
• number of arrests made
• number of of tickets issued
• number of of prosecutions, ticketing, and other actions relating to such arrests
• number of incidents with Officer use of force
• number of incidents with Officer weapons discharge
• number of Complaints again officers
• number of Investigations against officers
• number of Disciplinary actions against officers
• Department/Officer name/title
• Department contact info

Nice to have elements (for context)

• total number of incidents by source (calls for service vs officer initiated)
• broad category of incident (event type)

Tasks

• review mapping resources
• document mapping for most relevant required fields

Acceptance Criteria

• [x] Design developed by Jordan in #15376 is reviewed
• [x] Recommendation is made on process for translating CSV to usable format for static pages
• [x] A skeleton is started for the general file structure pending any missing information. (e.g., if the output has to be JSON, the Ruby requirements are documented)
• [x] Subsequent tickets are stubbed out as needed

Team

• Facilities

[xiongjaneg]

Data availability concerns captured in Slack thread

[mmiddaugh]

We received some additional information:

• Q: Is the source for the police contact information the same as the incident data?
  • A: This information is available in an Excel/CSV file

---

• Q: What types of contact information is expected? (One or more phone number(s), email, physical location, named individual, and/or title/role?)
  • A: Physical Location, Telephone Number (24 hour dispatch number, external), Chief of Police name, and email.

---

• Q: Should we expect police data and contact information for every facility? Or at the system level only?
  • A: Believed to be at facility level but will confirm

---

• Q: Is the source for use of force data the same as the incident data?
  • A: This information will exist on the Axon platform under the Standards module

---

• Q: Is the source for data for complaints, investigations, and disciplinary actions the same as the incident data?
  • A: The investigative arm is separate from HR which tracks disciplinary actions (the question has been forwarded to another SME for feedback)

---

Pending answers from the software vendor

• Q: Is the API schema known? Can we expect the data in the static dump to be like the intended API output?
• Q: Can we get sample API response data?

[jilladams]

Tagged as Sprint 96 since pushed out of Sprint 95 plan, and got new info that we may be able to pull this in.

[mmiddaugh]

Additional info from the data software vendor:

regarding an API: Our plan for facilitating access to this data leverages the Axon DataStore, which is not an API endpoint but rather a Microsoft Azure SQL Database to which your team can connect using ODBC-compliant tools. This Axon DataStore Introduction article provides some additional details on this architecture. That article and the DataStore Field Mapping article are the best resources for seeing examples of how the data is returned when queries are run against the database. Once your team has finalized what will be displayed on the dashboards and the specific data points needed to support that display, a custom database view could be created to make it more efficient to query those specific points using a tool such as PowerBI.

As a reminder, direct access to the Axon DataStore will not be available until the completion of Phase 2 of this project.

Regarding different sources for the data, there is a separate DataStore for the Axon Records and Axon Standards products. As the RMS system, Records houses the day-to-day forms, incident reports, and investigations completed by VA police. Axon Standards is usually the source for use of force and sometimes disciplinary information/internal investigations. I would have to defer to the VA team for specific details on the data points that they are capturing in each system.

[jilladams]

If we go the vets-api route, we know we will need to:

• Write the API
• Do backend work to sort out the datastorage in S3
• Hook up the front-end to the API

[jilladams]

From planning: so far, we believe the contact info will be a (maybe one of many) CSV, and CMS does not need to be involved.

[eselkin]

• I reviewed the sketch and comments on the design issue
• From the conversation in our last Scrum meeting, it seemed like content-build should be where we focus the effort for processing the CSV as other file formations happen here (i.e. the KISS JSON documents). We can then handle the processed data from a JSON similar to other KISS documents within react elements or wherever.
• The static portion of the file should definitely be in content-build. But have to figure out how to add a page that would be linked off the side-nav. Will ask @maxx1128 and others if necessary. The page needs to be linked off the side-nav of a facility but will not be created in pages.json because the page content does not originate from drupal. We might need to intercede between the steps of getContentViaGraphQL and loadDrupal

We would need to inject into the drupalPages.data keys with FacilitySidebarQuery a new element in data.xFacilitySideBarQuery.links[-1].links[-1].links[-1] like

{
   "expanded": false,
   "description": "Police data",
   "label": "VA Police", 
   "url": {
        "path": `${data.xFacilitySideBarQuery.links[0].url.path}/va-police`
   },
   "entity": {
      "linkedEntity": {
         "entityPublished": true,
         "moderationState": "published"
      }
    }
}

We should probably use a tested component like https://www.npmjs.com/package/csvtojson rather than manually transforming the CSV. I'm assuming the CSV will be exported as 1 row = 1 facility for 1 year, but it may be entirely different (e.g. 1 event / row). I looked at the Tucson data that Axon provides, but you can't actually see the data being used so it's not helpful.

[jilladams]

@eselkin in scrum today, we discussed that due to the tight timing on this project, we should meet to review SPIKE findings and figure out what we still need from the data vendor, so we can get them moving. @xiongjaneg volunteered to schedule that sync, so if we are ready for it sooner than end of sprint, please let her know? The main points to understand for that purpose are:

• Do we have a clear read on whether we will be receiving all the data we need from the vendor, to satisfy the design?
  • If not, what's missing.
• What's our plan for how we'll access that data? In order to understand how we're going to build / dependencies, etc.

(I know you're still working through things, just FYI-ing the scrum convo.)

[eselkin]

A little clarification on the data would be nice, like:

1. Is the CSV row cumulative data for a period or is it 1 row per event that needs to be summarized/bucketed for display?
2. Do all facilities have some data?
3. What key/identifier are they using for a VAMC location?

[eselkin]

We need a ticket for

1. Determine how to create a content-build page when no graphql is being used to generate data. We need a VA Police Page built for each VAMC facility.
2. Create a Vets-Website component for loading in JSON data for a facility Id and displaying sections and boxes within each section for the important data.
3. Complete the code to add VA Police to the Facility Sidebar (see comment above)
4. IS there possibly special code for Lovell? Does the sidebar get generated differently? Does the main page need two separate pages?

[jilladams]

Eli has reviewed:

• data we want
• Data the vendor has said they’ll provide
• the Tucson data sample - is behind PowerBI, so all we see is how the raw data has been piped into a front end, not the raw data itself.

So we will need: an example of raw data. We have some questions:

• Is it one row per event or
• one row per facility
• or one row per month, etc.
• have they already summarized the data into buckets. etc

Next step: @eselkin to let @xiongjaneg know when he's ready to review (tomorrow?) so we can move the meeting currently scheduled for 10/23.

[eselkin]

One more question, does Lovell's TRICARE side also get the VA Police link?

reminder to add to VA Police link in sidenav for Lovell page

{
...
in entity: {
   ...
   "fieldMenuSection": "both"|"va"
   }
}

[eselkin]

Will stop working on determining how to place the sidenav links and build pages. Will work on:

1. Creating skeleton structure for loading in a CSV and processing the unknown content into JSON
2. Adding step 1 into metalsmith
3. Skeleton structure for loading content from processed data in 1 & 2 and loading into redux (similar to KISS -- adding selectors for Facility ID to retrieve data).

Need tickets for

1. Finalizing structure of CSV -> JSON
2. Finalizing component reading data from redux

[xiongjaneg]

Ticket 1: #15722 CSV -> JSON Ticket 2: #15723 redux

[xiongjaneg]

@mmiddaugh Here's Eli and Max's ideal data/format for Police data from the vendor

Required (based on understanding of legislature)

Date Facility - should match the facility ID provided by facility api, otherwise would possibly need Drupal data matching

For each column below, there should be a row for all the data needed per the desired date range

• number of arrests made
• number of of tickets issued
• number of of prosecutions, ticketing, and other actions relating to such arrests
• number of incidents with Officer use of force
• number of incidents with Officer weapons discharge
• number of Complaints again officers
• number of Investigations against officers
• number of Disciplinary actions against officers

Doesn't need to be in the table above, just need info per facility

• Department/Officer name/title
• Department contact info

Nice to have elements (for context), possibly additional columns formatted in the same way as required elements above

• total number of incidents by source (calls for service vs officer initiated)
  • ideally: one row per year with calls for service and officer initiated
  • needs more data than summary of the required elements
  • might need a whole table of each event and statistics per event
• broad category of incident (event type)
  • ideally: a column of each event type with the number of each type

[xiongjaneg]

Per Eli: the example of Tuscon police data was not helpful because it was just a summary of the stats not access to the raw data

[xiongjaneg]

For everything, need facility ID and it should match what's in VAST, otherwise there would have to be some effort to map them

[eselkin]

If the rows are per event, in a worst-case-scenario, making sure we don't have PII in the data is best. It's likely that we just want true or false for columns. The Columns would still be the similar datetime_utc	system_id	facility_id	arrest_made	ticket_issued	prosecution_ticket	officer_used_force	weapon_discharged	complaint	investigation_of_officer	disciplinary_action_against_officer	call_for_service	officer_initiated	category_of_incident
2023-01-01T10:00:00Z	va_10101	vha_605	true	true	false	false	false	false	true	NULL or empty

Similar to their query in https://my.axon.com/s/article/Axon-Records-DataStore-Field-Mappings?language=en_US on We don't want any of the PersonToOffence or Person information and don't care about the report numbers, unless they tell us we have to get report numbers if we're joining on them. But we don't know all the fields available, I think the mappings doesn't list everything and they know the fields better than we do. Not sure where to find whether it was initiated as a call or by the officer. Not sure where disciplinary action is held.

select c.Description, o.NibrsUcrCode, c.Code, o.Severity
    , o.NibrsUcrCode, c.Ucr
    , o.Completion, l.facilityId, 
from axon.Offenses o
    inner join dw.OffenseToCharge otc on o.ExternalId = otc.FromExternalId and otc.ReportNumber = o.ReportNumber
    inner join axon.Charges c on otc.ToExternalId = c.ExternalId and otc.ReportNumber = c.ReportNumber
    inner join dw.Offenses dwo on o.ExternalId = dwo.ExternalId and o.ReportNumber = dwo.ReportNumber
    inner join axon.RecordsLocations l on o.ExternalId = l.ExternalId and o.ReportNumber = l.ReportNumber
    inner join axon.ArrestsCharges ac on o.ExternalId = ac.ParentExternalId and  o.ReportNumber = ac.ReportNumber

[mmiddaugh]

Not sure where disciplinary action is held.

Hi @eselkin, it seems there are two separate DataStores:

• Axon Records houses the day-to-day forms, incident reports, and investigations completed by VA police
• Axon Standards is usually the source for use of force and sometimes disciplinary information/internal investigations

Is there an assumption that the data is connected between the Record and the Standards database as in the table commented above or should we confirm?

[eselkin]

If they are different stores, that's fine. I was just being optimistic above. We can combine things from the outputted CSVs as long as there's some ID that connects them.

[eselkin]

Added some ACs on finalize CSV->JSON

[davidmpickett]

• Q: Should we expect police data and contact information for every facility? Or at the system level only?
  • A: Believed to be at facility level but will confirm

Have we received any further detail on this? Do we have an example of the raw contact data is stored?

[eselkin]

• Q: Should we expect police data and contact information for every facility? Or at the system level only?

  • A: Believed to be at facility level but will confirm

Have we received any further detail on this? Do we have an example of the raw contact data is stored?

Not as far as I know.

[xiongjaneg]

From Michelle and Jane conversation: We will want the police data at the facility level. The vendor said they can provided it down to even the department level, but for our purposes, we will want it displayed at the facility level.

[davidmpickett]

Should have clarified, I'm specifically asking about the Contact Information, which seems like it's stored in an entirely different system than the other data. Do we know if the Contact Information has a facility level of granularity? Looking at the Tuscon example, there are geographical subdivisions (Wards, Divisions, etc) for incident data, but there is no Contact Information per these subdivisions. It is only displayed at the home page level in the footer

[eselkin]

Will stop working on determining how to place the sidenav links and build pages. Will work on:

1. Creating skeleton structure for loading in a CSV and processing the unknown content into JSON
2. Adding step 1 into metalsmith
3. Skeleton structure for loading content from processed data in 1 & 2 and loading into redux (similar to KISS -- adding selectors for Facility ID to retrieve data).

Need tickets for

1. Finalizing structure of CSV -> JSON
2. Finalizing component reading data from redux

1,2 are in PR and QA 3 is not really technically possible until 1,2 passes so that the files are accessible to vets-website. So it's probably best to put this with #15723

Also 3, generally requires that drupalStaticData and connect-drupal-static-data and selectors be a bit more generalized, like jsonStaticData and connect-json-static-data. I've done most of this, just making sure tests pass.

[mmiddaugh]

Thank you for this @eselkin I'll email our list of requested data and ask for a meeting to discuss and view raw data

[jilladams]

@eselkin @xiongjaneg should this ticket stay open? Not sure if we need it to track the next steps re: the data call with Axon or not.

[xiongjaneg]

@jilladams @eselkin Our ACs are covered. Whatever comes out of the Axon call can be added to a subsequent ticket or stub out a new one. I'll close this.