Database Structure
The production database is a managed Postgresql cluster with separate instances for different conceptual schemas--the county and state schemas. It is possible that these schemas will be merged in the future, but they were originally split to allow development on each side of the proverbial fence to move quickly without posing regression risk to one another.
Each schema instance is a vanilla SQL database with application-time temporal tables (discussed here). Schemas are optimized for flexibility of representation and expansion, to allow our system to expand gracefully with new jurisdictions, new portions of the criminal justice system, and new legal structures. Queries to the database from the platform's app layer pass through the SQLAlchemy ORM.
The schemas are discussed in greater detail here. This discussion is focused on the database layout. In each schema, each primary table has a temporal version that provides historical snapshots to track changes to individual entities. Foreign key constraints are used between the primary tables to enforce assumptions that enable standardized operations downstream. Historical tables have foreign key references to their primary counterparts, but not to one another.
The state schema is focused on corrections and supervision at present, but has been built with expansion into law enforcement and courts in mind. The person is at the center of the entity graph with a few child entities pointing up to it, chief among them the sentence group which represents a group of related sentences stemming from a brand new offense triggering interaction with the justice system. Most of the graph hangs off of the sentence group.
The county schema is focused on jail bookings at present, including charges, sentences, bonds, holds, and so forth, to provide an accurate portrayal of the use of jails for both pre-trial detention and sentences to incarceration. The person is at the top of the entity tree and entities descend from there.
