Contract snapshot

meta:
  version: 1.0
  info: |
    Records for individuals released from the Florida Department of Corrections since
    October 1, 1997. The cohort includes basic information, aliases, current and prior
    prison offenses, detainers and incarceration history.

  version: 1
  category: crime-justice
  tags:
    - Corrections
    - Incarceration
    - Florida
    - Release


###########################################################################
# TABLE DEFINITIONS (Read by schema.py)
###########################################################################

tables:

  INMATE_RELEASE_ROOT:
    display_name: Released Inmates - Basic Information
    info: |
      Basic information on imates released since October 1, 1997.
    columns:
      DCNumber:
        rename_to: person_id
        cast_to: string
        meta:
          description: |
            Florida DOC person identifier (DCNumber).
      LastName: 
        rename_to: last_name
        cast_to: string
        case: title
        meta:
          description: | 
            Person last name as reported.
      FirstName:
        rename_to: first_name
        cast_to: string
        case: title
        meta:
          description: |
            Person first name.
      MiddleName: 
        rename_to: middle_name
        cast_to: string
        case: title
        meta:
          description: |
            Person middle name  or middle initial (may be null).
      NameSuffix:
        rename_to: name_suffix
        cast_to: string
        case: title
        meta:
          description: |
            Name suffix such as  JR, SR, III (may be null).
      Race:
        rename_to: race_code
        cast_to: string
        case: upper
        meta:
          description: |
            Race code as provided by the source. Use with race_name for the human-readable label.
      race_descr:
        rename_to: race_name
        cast_to: string
        case: title
        meta:
          description: |
            Human-readable race label corresponding to race code.
      Sex:
        rename_to: sex_code
        cast_to: string
        case: upper
        meta:
          description: |
            Sex code as provided by the source (e.g., M, F).
      BirthDate:
        rename_to: birth_date
        cast_to: date
        format_in: "%m/%d/%Y"
        meta:
          description: |
            Date of birth
      PrisonReleaseDate:
        rename_to: prison_release_date
        cast_to: date
        format_in: "%m/%d/%Y"
        meta:
          description: |
            Projected or recorded prison release date from the source (may change over time).
      releasedateflag_descr:
        rename_to: release_date_status
        cast_to: string
        case: title
        meta:
          description: |
            Source-provided status/flag describing the validity or meaning of the prison release date.
      custody_description:
        rename_to: custody_level
        cast_to: string
        case: title
        meta:
          description: |
            Custody level classification as provided by the source (e,g., Minimum, Medium, Close).
    
    canonical_order:
      - person_id
      - birth_date
      - prison_release_date
      - race_code
      - race_name
      - sex_code
      - custody_level
      - release_date_status
      - last_name
      - first_name
      - middle_name
      - name_suffix

  INMATE_RELEASE_OFFENSES_PRPR: &release_offenses
    display_name: Released Inmates - Prior Prison Offenses
    info: |
      Prior prison offenses for released inmates.

    columns:
      DCNumber:
        rename_to: person_id
        cast_to: string
        meta:
          description: |
            Florida DOC person identifier (DCNumber).
      Sequence:
        rename_to: sequence
        cast_to: int
        meta:
          description: |
            Sequence number used by the source system to order multiple offenses asscociated with 
            the same person and case. Primarily used to preserve original ordering rather than 
            to convey legal hierarchy.
      OffenseDate:
        rename_to: offense_date
        cast_to: date
        format_in: "%m/%d/%Y"
        meta:
          description: |
            Date on which the offense was reported to have occured. This reflects the offense
            event date, not the adjudication or sentencing.
      DateAdjudicated:
        rename_to: adjudication_date
        cast_to: date
        format_in: "%m/%d/%Y"
        meta:
          description: |
            Date on which the offense was adjudicated by the court (i.e., legal resolution date).
            May occur significantly after the offense date.
      County_of_Conviction:
        rename_to: county_of_conviction
        cast_to: string
        case: title
        meta:
          description: |
            County in which the offense was convicted (i.e., the court with jurisdiction over the conviction),
            such as Duval or Broward County. This reflects the jurisdiction of conviction, not the location
            of incarceration.
      CaseNumber:
        rename_to: case_number
        cast_to: string
        meta:
          description: |
            Court case number associated with the offense. Multiple offenses may share the same case number 
            when charged or adjudicated together.
      adjudicationcharge_descr:
        rename_to: charge_description
        cast_to: string
        case: title
        meta:
          description: |
            Text description of the adjudicated charge associated with the offense. This is a human-readable
            label and may contain abbreviations or truncated legal terminology.
      prisonterm:
        rename_to: prison_term
        cast_to: string
        format_in: YYYMMDD
        meta:
          description: |
            Encoded representation of the prison term associated with the offense as provided by the source
            system. The format is non-standard and requires parsing to derive duration or sentencing details.
    
    canonical_order:
      - person_id
      - sequence
      - offense_date
      - adjudication_date
      - prison_term
      - county_of_conviction
      - case_number
      - charge_description

  INMATE_RELEASE_OFFENSES_CPS:
    display_name: Released Inmates - Currrent Prison Offenses
    info: |
      Current prison offenses for released inmates.
    <<: *release_offenses

  INMATE_RELEASE_DETAINERS: 
    display_name: Released Inmates - Detainers
    info: |
      Detainers for released innmates.
    columns:
      DCNumber:
        rename_to: person_id
        cast_to: string
        meta:
          description: | 
            Florida DOC person identifier (DCNumber).
      Authority: 
        rename_to: requesting_authority
        cast_to: string
        case: title
        meta:
          description: |
            Requesting authority that lodged the detainer or notification (court, sheriff's office, probation/parole, etc.).
      DetainerDate: 
        rename_to: detainer_start_date
        cast_to: date
        format_in: "%m/%d/%Y"
        meta:
          description: |
            Date the detainer/notification was recorded (i.e., when the hold/notice) was lodged.
      RemovalDate: 
        rename_to: detainer_end_date
        cast_to: date
        format_in: "%m/%d/%Y"
        meta:
          description: |
            Date the detainer/notification was removed/cleared. Null typically indicates an active (not yet removed) detainer.
      SequenceNo: 
        rename_to: detainer_sequence
        cast_to: int
        meta:
          description: |
            Sequence number used by the source to order multiple detainer records for the same inmate.
      detainertype_descr: 
        rename_to: detainer_type_name
        cast_to: string
        case: title
        meta:
          description: |
            Human-readable detainer type label from the source (e.g., 'DETAIN', 'NTFY/P&P').
    
    canonical_order:
      - person_id
      - detainer_sequence
      - detainer_start_date
      - detainer_end_date
      - detainer_type_name
      - requesting_authority

  INMATE_RELEASE_INCARHIST:
    display_name: Released Inmates - Incarceration History
    info: |
      Incarceration history  for released inmates.
    columns:
      DCNumber:
        rename_to: person_id
        cast_to: string
        meta:
          description: |
            Florida DOC person identifier (DCNumber) associated with the incarceration history record.
      ReceiptDate:
        rename_to: incarceration_start_date
        cast_to: date
        format_in: "%m/%d/%Y"
        meta:
          description: |
            Date the inmate was received into custody for this incarceration episode.
      ReleaseDate:
        rename_to: incarceration_end_date
        cast_to: date
        format_in: "%m/%d/%Y"
        meta:
          description: |
            Date the inmate was released from custody for this incarceration episode. Null indicates the episode is ongoing.

    canonical_order:
      - person_id
      - incarceration_start_date
      - incarceration_end_date

  INMATE_ACTIVE_ALIASES:
    display_name: Florida Released Inmates Aliases
    info: |
      Aliases for released inmates.
    columns:
      DCNumber:
        rename_to: person_id
        cast_to: string
        meta:
          description: |
            Florida DOC identifier (DCNumber) for joining to other tables across inmates, offenders, released inmates.
      LastName: 
        rename_to: last_name
        cast_to: string
        case: title
        meta:
          description: | 
            Person last name as reported.
      FirstName:
        rename_to: first_name
        cast_to: string
        case: title
        meta:
          description: |
            Person first name.
      MiddleName: 
        rename_to: middle_name
        cast_to: string
        case: title
        meta:
          description: |
            Person middle name  or middle initial (maybe null).
      NameSuffix:
        rename_to: name_suffix
        cast_to: string
        case: title
        meta:
          description: |
            Name suffix such as  JR, SR, III (maybe null).

    canonical_order:
      - person_id
      - last_name
      - first_name
      - middle_name
      - name_suffix