DOC

Department of Corrections (DOC) Open Data

The DOC data comes from the public website data dump. The data is released quarterly by the DOC (Jan, April, July, Oct). The data is released as a fixed width text file.

• Aliases
• Offense Codes
• Profile
• Sentences
• Statuses
• 2017 Export

DOC Data Mapping

Notes

• Rails generates the following columns by default
  • id - auto-incremented
  • created_at, updated_at - timestamp of when the record was created / updated
• The DOC data is linked through the Rails primary key (doc_profiles.id) when the data is imported rather than relying on the DOC_NUM that comes through the DOC files

Aliases

Database	DOC File	Additional Notes
id	N/A
doc_profile_id	N/A	Foreign Key doc_profiles.id
doc_number	DOC_NUM	Unique inmate identifier from DOC files
last_name	LAST_NAME
first_name	FIRST_NAME
middle_name	MIDDLE_NAME
suffix	SUFFIX
created_at	N/A
updated_at	N/A

Offense Codes

Database Column	DOC File	Additional Notes
id	N/A
statute_code	STATUTE_CODE
description	DESCRIPTION
is_violent	VIOLENT
created_at	N/A
updated_at	N/A

Profile

Database Column	DOC File	Additional Notes
id	N/A
doc_number	DOC_NUM	Unique inmate identifier from DOC files
last_name	LAST_NAME
first_name	FIRST_NAME
middle_name	MIDDLE_NAME
suffix	SUFFIX
last_move_date	LAST_MOVE_DATE
facility	FACILITY	Current location of inmate. Used to create the doc_statuses table.
birth_date	BIRTH_DATE
sex	SEX
race	RACE
hair	HAIR
height_ft	HEIGHT_FT
height_in	HEIGHT_IN
weight	WEIGHT
eye	EYE
status	STATUS
created_at	N/A
updated_at	N/A

Sentences

Database Column	DOC File	Additional Notes
id	N/A
doc_profile_id	N/A	Foreign Key doc_profiles.id
doc_offense_code_id	N/A	Foreign Key doc_offense_codes.id
statute_code	STATUTE_CODE
sentencing_county	SENTENCING_COUNTY
js_date	JS_DATE	Date of sentencing
crf_number	CRF_NUMBER	Case number in OSCN. See note below about formatting.
incarcerated_term_in_years	INCARCERATED_TERM_IN_YEARS
probation_term_in_years	PROBATION_TERM_IN_YEARS
is_death_sentence	N/A	Boolean field: True if incarcerated_term_in_years is 9999
is_life_sentence	N/A	Boolean field: True if incarcerated_term_in_years is 7777
is_life_no_parole_sentence	N/A	Boolean field: True set if incarcerated_term_in_years is 8888
created_at	N/A
updated_at	N/A
court_case_id	N/A	Foreign Key court_cases.id Links DOC Sentence back to OSCN court case
sentence_id	SENTENCE_ID	New in DOC file as of Q1 2022
consecutive_to_sentence_id	CONSEC_TO_SENTENCE_ID	New in DOC file as of Q1 2022

CRF_NUMBER Formatting

Most of the DOC data follows one of the following formats:

• CF-2020-123
• 2020-123
• 20-123

Using regex, the format is determined and the link is created between the OSCN case and the DOC sentence.

Statuses

The doc_statuses table represents the historical status of an inmate. It is created by taking a snapshot of the DOC Profile information (mainly facility) from the historical Quarterly data dumps. This allows us to see where an inmate was at the time that the file was released. It can also be used to know when an inmate was released.

Database Column	DOC File	Additional Notes
id	N/A
doc_profile_id	Foreign Key doc_profiles.id
facility	Foreign Key doc_profiles.id
created_at	N/A
updated_at	N/A

2017 Data Export

The 2017 export found here (insert link to article) has additional tables and fields that are pulled in for historical purposes.

Offender Receptions

The offender receptions is mapped to the doc_statuses table.

Database Column	DOC File	Additional Notes
id	N/A
doc_profile_id	Foreign Key doc_profiles.id (Linked through the DOCNum field)
facility	Facility	Facilities are abbreviated instead of the full name.
reason	Reason
created_at	N/A
updated_at	N/A

Offender Exits

The offender exits is mapped to the doc_statuses table. This particular field does not have

Database Column	DOC File	Additional Notes
id	N/A
doc_profile_id	Foreign Key doc_profiles.id (Linked through the DOCNum field)
facility	ExitReason	This is mapped here because it contains things like DISCHARGE and TRANSFER. There is an additional field that has context on the reason for that has no header and is not in the Readme
reason	null	The 4th column in the database does not have a header but contains information on the reason for the release
created_at	N/A
updated_at	N/A

Offender Sentences

Add table for the sentences mapping