Contract snapshot

meta:
  version: 1.0
  info: |
    Snapshot of individuals currently incarcerated in the Florida Department of Corrections. 
    Includes basic information, aliases, current and prior prison offenses, detainers and incarceration history.

  category: crime-justice
  tags:
    - Corrections
    - Incarceration
    - Florida


####################
# 📅 CONFIGURATION
####################


tables:

  INMATE_ACTIVE_ROOT:
    display_name: Basic Information
    info: |
      Basic inmate information on active 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).
      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: |
      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).
      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).  
      ReceiptDate:
        rename_to: custody_start_date
        cast_to: date
        format_in: "%m/%d/%Y"
        meta:
          description: |
            Date the inmate was received into current custody episode (custody start date for the active snapshot).
      FACILITY_description:
          rename_to: facility_name
          cast_to: string
          case: upper
          meta:
            description: |
              Current facility name/description where the inmate is housed (as reported in the snapshot).

    canonical_order:
      - person_id
      - birth_date
      - custody_start_date
      - prison_release_date
      - race_code
      - race_name
      - sex_code
      - custody_level
      - facility_name
      - last_name
      - first_name
      - middle_name
      - name_suffix


  INMATE_ACTIVE_OFFENSES_PRPR: &active_offenses
    display_name: Prior Prison Offenses
    info: |
      Prior prison offenses for active inmates.
    columns:
      DCNumber:
        rename_to: person_id
        cast_to: string
        meta:
          description: |
            Florida DOC inmate identifier (DCNumber) for joining to other tables.
      Sequence:
        rename_to: sequence
        cast_to: int
        meta:
          description: |
            Sequence number used by the source system to order multiple offenses associated 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 occurred. 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 occure 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
      - county_of_conviction
      - case_number
      - prison_term
      - charge_description
  
  INMATE_ACTIVE_OFFENSES_CPS:
    <<: *active_offenses
    display_name: Current Prison Offenses
    info: |
      Current prison offenses for active inmates.
   

  INMATE_ACTIVE_DETAINERS:
    display_name: Detainers
    info: |
      Detainers for active inmates.
    columns:
      DCNumber:
        rename_to: person_id
        cast_to: string
        meta:
          description: | 
            Florida DOC person identifier (DCNumber) for joining to other tables.
      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 sourece (e.g., 'DETAIN', 'NTFY/P&P').
      DetainerType: 
        rename_to: detainer_type_code
        cast_to: string
        case: upper
        meta:
          description: |
            Source code describing the detainer category (e.g., 'D' detainer/hold, 'N' notification). Use with detainer_type_name.

    canonical_order:
      - person_id
      - detainer_sequence
      - detainer_start_date
      - detainer_end_date
      - requesting_authority
      - detainer_type_name
      - detainer_type_code


  INMATE_ACTIVE_INCARHIST:
    display_name: Incarceration History
    info: |
      Incarceration history for active inmates.
    columns:
      DCNumber:
        rename_to: person_id
        cast_to: string
        meta:
          description: |
            Florida DOC person identifier (DCNumber) to join incarceration history to other tables.
      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 Inmates Aliases
    info: |
      Aliases for active 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