Monday, September 9, 2019

Criminal Justice System Information – A NoSQL Solution


Cards used in Bletchley Park during WWII

By James E. McMillan, Principal Court Management Consultant, National Center for State Courts

Summary: I think that I have found a solution for one of the most difficult problems in justice systems: the criminal case information data model and coinciding information accuracy.  I understand that this is a bold statement and therefore the following article explains it in some detail.

The problem with tracking criminal case information from inception (incident or indictment), through the process and subsequent consequences and compliance have always been complexity.  Criminal and juvenile case data includes charges, modifications, findings, orders, fines and restitution payment, and behavioral/remediation compliance that change and reconfigure in non-specific ways.  The graphic from a SEARCH Group Report below shows some of the data and workflows involved.



---



The bottom line is that it has been hard to build a data model to reflect this highly dynamic reality. And as a result, it is no wonder there have been complaints for decades about the inaccuracy of the criminal justice system statistics and supporting data and data relationships?

Therefore, traditional structured hierarchical and relational database solutions have not really worked.  In addition, while the data about criminal defendants and cases are messy, so are the relationships.  Criminal defendants may be involved in multiple incidents, charges, and cases but also in civil matters such as domestic or family cases involving personal relationships.  On top of that, we can layer other civil case matters, workman’s compensation, bankruptcies, and social services.

Last on this theme, I would point to Google as a great example as to why a relational database cannot be always used to model the world.  RDBMS were obviously well established when Google was created, but they simply could not do the job.

A Solution: NoSQL databases (MongoDB)

Many people have asked me over the years what is the best solution to whatever data/system problem they are having.  I have never been able to perfectly answer that question because the IT world is constantly changing.  Now that doesn’t mean that previous technology decisions have been wrong.  They were the best that could be made at the time. But it also does mean that these are final answers, but rather an evolving direction because technology continually changes.

That brings us to a solution that I believe can solve our criminal case information problem, the new NoSQL (not only SQL/non-relational) database and specifically MongoDB.

“Cards”

In NoSQL/MongoDB the basic units are officially called documents. But for our purposes in the courts and justice system, I think this name/terminology doesn’t work as well. Documents have a very specific meaning and use in justice systems versus the “card” analogy.  Also, most courts and justice agencies have used index cards for decades to capture data.  Therefore, I beg forgiveness from the NoSQL experts.  To continue…

A simple MongoDB address card looks like this “under the covers”.

https://www.infoq.com/articles/data-model-mongodb/

Many of you are familiar with XML.  MongoDB uses the JSON (https://www.json.org/) notation to delineate data fields.  And as you can see, they use brackets in this example to group data together such as “addresses” where two street addresses are captured.  And the field names could certainly use NIEM (https://niem.gov/) fields if they are adapted to JSON.

For reference, I have shared one of our initial test templates as a PDF document here.

Concept #1 - One can create a card for everything 

A card for each police incident, arrest, and charge.  Cards can be made for the prosecutor charges, for grand jury indictments, or even one when they drop a charge.  There is no need to an initial arrest as the “starting point” because that might be linked to multiple incidents.

Cards are created from saved “templates”.  In our test system (more below) we created templates for criminal charges, criminal charge modification, bail/bond, criminal sentence assessed, sentence performed, and payment for a start.

Cards can be made in the same case for data that originates in other agencies such as jail/prison status reports, drug testing reports, probation compliance, and financial payments.  For example, we are creating and linking the test system financial payment cards with a bank’s transaction records so that we have this information connected to the case.  Then we can also in turn link a non-payment card to subsequent consequences or a sentence modification that a judge may order.

Concept #2 - Cards can be linked

Linking cards to one another can be made specifically on each card by “_id”: ObjectID (automatically assigned by MongoDB or by any other ID say drivers license number as long as one repeats it either on the cards to be related on in an index card.  Simply card 1 with ID #1 can have a field called card_link that could have ID #2 as data and card #2 would have a card link to ID #1.  For a much better explanation of this see this tutorial

For our purposes think again that a card from a criminal case could be linked to a card in a juvenile case so that the person's entire picture can be mapped and in turn better understood. Both of these examples are essentially a one-to-one map.

An alternative approach would be to create a card with all of the references say for an individual or a case.  In other words, a one-to-many design/map.

Concept #3 – Cards can be edited.

Next, just to “blow your mind”, cards can be dynamically edited and expanded.  Yes, fields and data can be added to cards after they are created.  Say you want a single card to show all the charges.  But later a charge is added.  You have the option of creating a new card or editing the original card.  But… but you say…what about data integrity?  The MongoDB automatically creates an audit trail and their new 4.2 version also has a snapshot and roll-back functionality.

The graphic below shows the test version of the MongoDB system the NCSC is developing with the Judiciary of Trinidad & Tobago.

Click on image to view

The cards are grouped under the “consequences” column and the data from the card/document template is shown in a user-friendly format on the right.  But note two buttons.  One is to add consequences (cards) and the other grey one is to add a field to the card as is shown in the "grey area" of the form.  Flexibility is key and what has been missing in previous approaches.

Concept #4 – NoSQL systems can exist beside and complement traditional relational systems

The system shown above exists in its own Docker container while the rest of the CCMS lives in a container with the MySQL relational database system.  We use the front end and back end code to link the two database systems together.

Concept #5 – Indexing

MongoDB provides a rich set of field/data indexing tools.  As the documentation states: “Indexes support the efficient execution of queries in MongoDB. Without indexes, MongoDB must perform a collection scan… If an appropriate index exists for a query, MongoDB can use the index to limit the number of documents it must inspect.”

Concept #6 – Full-text search

“MongoDB supports query operations that perform a text search of string content. To perform a text search, MongoDB uses a text index and the $text operator.” For more see: https://docs.mongodb.com/manual/text-search/

Concept #7 – card authentication and field-level encryption

An article on how MongoDB assigns a timestamp to each card (document) is available here:
https://docs.mongodb.com/manual/reference/bson-types/

And an article describing this new encryption functionality is available at: https://thehackernews.com/2019/06/mongodb-fle-data-encryption.html

Conclusion

I believe that NoSQL/MongoDB provides a lot of new tools to apply and design fresh thinking and approaches that has the great potential to create more accurate data and referential associations to solve our difficult criminal justice data problems.

Last, while we are in the test phase of implementation with the Judiciary of Trinidad and Tobago, we are convinced that “it is all in the cards”.

© Copywrite National Center for State Courts, USA 2019


No comments:

Post a Comment