> For the complete documentation index, see [llms.txt](https://docs.mymedtrics.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.mymedtrics.com/get-started/classic-data-mapping/cases-and-hours.md).

# Cases and hours

Case / Procedure Logging, Work Hours: 18 Medtrics Classic tables and their confirmed Oracle fields.

Case / Procedure Logging, Work Hours — 18 Medtrics Classic tables with 110 confirmed fields (8% of the mapping): Unchanged 12 · Renamed 63 · ID changed 35. Each table below lists where its confirmed fields went, what to change in a query, and a starter query. Back to the [map of the data](/get-started/classic-data-mapping.md).

![Cases and hours: 18 Medtrics Classic tables; the number is confirmed fields, the bar is the outcome mix](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FXMbe6HjhbiGUxxourGmD%2Fmap-clinical.png?alt=media)

### Case / Procedure Logging

13 tables, 54 confirmed fields: Unchanged 6 · Renamed 25 · ID changed 23.

#### companies\_category

**companies\_category** → `clinical_item_categories` · *replaced* · 5 confirmed fields (Unchanged 1 · Renamed 3 · ID changed 1)

Hierarchical taxonomy for all clinical items (diagnoses, procedures, medications, labs, imaging).

> These never belonged in a 'companies' app - they are the case-logging catalog. Oracle rehomes them in the cases domain (outside this area), which is the right move.

![Where the 5 confirmed fields of companies\_category went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FEjwG7ziP22U6Y6qm82b5%2Fflow-companies-category.png?alt=media)

| Medtrics Classic field | Oracle                           | Outcome    | Note                                                       |
| ---------------------- | -------------------------------- | ---------- | ---------------------------------------------------------- |
| `id` integer           | `id` UUID                        | ID changed | Integer link → UUID (Split)                                |
| `id` integer           | `display_order` INTEGER          | Renamed    | (Split)                                                    |
| `id` integer           | `path` TEXT                      | Renamed    | int → text (Split, Type changed)                           |
| `category` varchar     | `name` TEXT                      | Renamed    |                                                            |
| `type` varchar         | `type` enum:clinical\_item\_type | Unchanged  | Now a fixed value list (clinical\_item\_type) (Value list) |

* Swap the 3 renamed fields and point `FROM` at `clinical_item_categories`.
* Re-key 1 ID field. Join people on `email`, not on old IDs.
* Add `WHERE deleted_at IS NULL`: `clinical_item_categories` keeps deleted rows.

```sql
-- Starter query: confirmed fields only. Was companies_category.
SELECT
  t.display_order, -- was id
  t.id,            -- was id
  t.path,          -- was id
  t.name,          -- was category
  t.type           -- was type
FROM clinical_item_categories t
WHERE t.deleted_at IS NULL;
```

#### companies\_diagnosis

**companies\_diagnosis** → `clinical_items` · *merged/replaced* · 1 confirmed field (ID changed 1)

Unified clinical items - diagnoses, procedures, medications, labs, imaging.

> Three Medtrics Classic catalogues (the dual-purpose Diagnosis table, procedure codes and their code types) collapse into one typed taxonomy with a proper code\_system enum. This is the single biggest clean-up in the area.

| Medtrics Classic field | Oracle    | Outcome    | Note                |
| ---------------------- | --------- | ---------- | ------------------- |
| `id` integer           | `id` UUID | ID changed | Integer link → UUID |

* Re-key 1 ID field. Join people on `email`, not on old IDs.
* Add `WHERE deleted_at IS NULL`: `clinical_items` keeps deleted rows.

```sql
-- Starter query: confirmed fields only. Was companies_diagnosis.
SELECT
  t.id  -- was id
FROM clinical_items t
WHERE t.deleted_at IS NULL;
```

#### procedures\_casesection

**procedures\_casesection** → `question_sections` · *replaced* · 4 confirmed fields (Renamed 3 · ID changed 1)

> Custom case questions and their answers move to the shared Questions domain instead of having a cases-only copy. The cost is that question\_answers.case\_log\_id is a plain UUID with no foreign key (06\_questions/02\_tables.sql:393).

![Where the 4 confirmed fields of procedures\_casesection went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FLYNKi2O8YsJGiSOCKvFM%2Fflow-procedures-casesection.png?alt=media)

| Medtrics Classic field     | Oracle                   | Outcome    | Note                                                                                                                |
| -------------------------- | ------------------------ | ---------- | ------------------------------------------------------------------------------------------------------------------- |
| `id` integer               | `id` UUID                | ID changed | Integer ID replaced by a new UUID. Medtrics Classic → new ID crosswalk is kept by the migration (see Keys & joins). |
| `created_date` timestamptz | `created_at` TIMESTAMPTZ | Renamed    |                                                                                                                     |
| `updated_date` timestamptz | `updated_at` TIMESTAMPTZ | Renamed    |                                                                                                                     |
| `order` integer            | `display_order` INTEGER  | Renamed    |                                                                                                                     |

* Swap the 3 renamed fields and point `FROM` at `question_sections`.
* Re-key 1 ID field. Join people on `email`, not on old IDs.
* Add `WHERE deleted_at IS NULL`: `question_sections` keeps deleted rows.

```sql
-- Starter query: confirmed fields only. Was procedures_casesection.
SELECT
  t.id,            -- was id
  t.created_at,    -- was created_date
  t.updated_at,    -- was updated_date
  t.display_order  -- was order
FROM question_sections t
WHERE t.deleted_at IS NULL;
```

#### procedures\_demographic

**procedures\_demographic** → `questions` · *replaced* · 10 confirmed fields (Unchanged 1 · Renamed 8 · ID changed 1)

> Custom case questions and their answers move to the shared Questions domain instead of having a cases-only copy. The cost is that question\_answers.case\_log\_id is a plain UUID with no foreign key (06\_questions/02\_tables.sql:393).

![Where the 10 confirmed fields of procedures\_demographic went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2F2iWE4S3Kr1F6C05KlU1T%2Fflow-procedures-demographic.png?alt=media)

| Medtrics Classic field     | Oracle                              | Outcome    | Note                                                 |
| -------------------------- | ----------------------------------- | ---------- | ---------------------------------------------------- |
| `id` integer               | `id` UUID                           | ID changed | Integer link → UUID                                  |
| `demographic` varchar      | `title` TEXT                        | Renamed    |                                                      |
| `type` varchar             | `question_type` enum:question\_type | Renamed    | Now a fixed value list (question\_type) (Value list) |
| `status` boolean           | `is_active` BOOLEAN                 | Renamed    |                                                      |
| `hide_responses` boolean   | `confidential` BOOLEAN              | Renamed    |                                                      |
| `required` boolean         | `is_required` BOOLEAN               | Renamed    |                                                      |
| `created_date` timestamptz | `created_at` TIMESTAMPTZ            | Renamed    | (Split)                                              |
| `created_date` timestamptz | `published_at` TIMESTAMPTZ          | Renamed    | (Split)                                              |
| `updated_date` timestamptz | `updated_at` TIMESTAMPTZ            | Renamed    |                                                      |
| `description` text         | `description` TEXT                  | Unchanged  |                                                      |

* Swap the 8 renamed fields and point `FROM` at `questions`.
* Re-key 1 ID field. Join people on `email`, not on old IDs.
* Add `WHERE deleted_at IS NULL`: `questions` keeps deleted rows.

```sql
-- Starter query: confirmed fields only. Was procedures_demographic.
SELECT
  t.id,            -- was id
  t.title,         -- was demographic
  t.question_type, -- was type
  t.is_active,     -- was status
  t.confidential,  -- was hide_responses
  t.is_required,   -- was required
  t.created_at,    -- was created_date
  t.published_at,  -- was created_date
  t.updated_at,    -- was updated_date
  t.description    -- was description
FROM questions t
WHERE t.deleted_at IS NULL;
```

#### procedures\_demographic\_subcompetencies

**procedures\_demographic\_subcompetencies** → `question_competency_mappings` · 3 confirmed fields (ID changed 3)

![Where the 3 confirmed fields of procedures\_demographic\_subcompetencies went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2F1XsDMTNfrvPr7bWVa6I9%2Fflow-procedures-demographic-subcompetencies.png?alt=media)

| Medtrics Classic field   | Oracle                   | Outcome    | Note                                                                                                                |
| ------------------------ | ------------------------ | ---------- | ------------------------------------------------------------------------------------------------------------------- |
| `id` integer             | `id` UUID                | ID changed | Integer ID replaced by a new UUID. Medtrics Classic → new ID crosswalk is kept by the migration (see Keys & joins). |
| `demographic_id` integer | `question_id` UUID       | ID changed | Integer link → UUID pointing to questions                                                                           |
| `competency_id` integer  | `framework_node_id` UUID | ID changed | Integer link → UUID pointing to framework\_nodes                                                                    |

* Re-key 3 ID fields. Join people on `email`, not on old IDs.
* Add `WHERE deleted_at IS NULL`: `question_competency_mappings` keeps deleted rows.

```sql
-- Starter query: confirmed fields only. Was procedures_demographic_subcompetencies.
SELECT
  t.id,                -- was id
  t.question_id,       -- was demographic_id
  t.framework_node_id  -- was competency_id
FROM question_competency_mappings t
WHERE t.deleted_at IS NULL;
```

#### procedures\_proceduresetup

**procedures\_proceduresetup** → `case_count_thresholds`, `case_template_configs`, `case_templates` · *renamed* · 5 confirmed fields (Renamed 4 · ID changed 1)

Minimum case counts required for competency levels. Split table: each field names its new table.

> Both are self-nesting trees of case log types. Oracle adds icon, color and a JSONB sections definition; it drops the per-program ownership the Medtrics Classic setup implied through its child table.

![Where the 5 confirmed fields of procedures\_proceduresetup went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FEu16uGJB1BguPfSWurDb%2Fflow-procedures-proceduresetup.png?alt=media)

| Medtrics Classic field     | Oracle                                   | Outcome    | Note                        |
| -------------------------- | ---------------------------------------- | ---------- | --------------------------- |
| `id` integer               | case\_templates.`id` UUID                | ID changed | Integer link → UUID (Split) |
| `id` integer               | case\_templates.`display_order` INTEGER  | Renamed    | (Split)                     |
| `procedure_name` varchar   | case\_templates.`name` TEXT              | Renamed    |                             |
| `created_date` timestamptz | case\_templates.`created_at` TIMESTAMPTZ | Renamed    |                             |
| `updated_date` timestamptz | case\_templates.`updated_at` TIMESTAMPTZ | Renamed    |                             |

* Swap the 4 renamed fields and point `FROM` at `case_count_thresholds` (and the other new tables).
* Re-key 1 ID field. Join people on `email`, not on old IDs.
* Add `WHERE deleted_at IS NULL`: `case_count_thresholds`, `case_templates` keep deleted rows.

#### procedures\_proceduresetupchild

**procedures\_proceduresetupchild** → `case_templates` · *replaced* · 2 confirmed fields (Renamed 1 · ID changed 1)

Case template definitions (hierarchical).

> Roughly thirty boolean field-visibility switches and a minimum-average-score table become a short config table plus a thresholds table. Most of the Medtrics Classic per-field toggles have no direct Oracle column and are assumed to live inside case\_templates.sections JSONB.

![Where the 2 confirmed fields of procedures\_proceduresetupchild went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FL1BT5XvJDAYEpP5KVnfa%2Fflow-procedures-proceduresetupchild.png?alt=media)

| Medtrics Classic field | Oracle              | Outcome    | Note                                                                                                                |
| ---------------------- | ------------------- | ---------- | ------------------------------------------------------------------------------------------------------------------- |
| `id` integer           | `id` UUID           | ID changed | Integer ID replaced by a new UUID. Medtrics Classic → new ID crosswalk is kept by the migration (see Keys & joins). |
| `status` boolean       | `is_active` BOOLEAN | Renamed    |                                                                                                                     |

* Swap the 1 renamed field and point `FROM` at `case_templates`.
* Re-key 1 ID field. Join people on `email`, not on old IDs.
* Add `WHERE deleted_at IS NULL`: `case_templates` keeps deleted rows.

```sql
-- Starter query: confirmed fields only. Was procedures_proceduresetupchild.
SELECT
  t.id,        -- was id
  t.is_active  -- was status
FROM case_templates t
WHERE t.deleted_at IS NULL;
```

#### rotations\_caselogdemographicsanswer

**rotations\_caselogdemographicsanswer** → `question_answers` · *replaced* · 6 confirmed fields (Renamed 4 · ID changed 2)

> Custom case questions and their answers move to the shared Questions domain instead of having a cases-only copy. The cost is that question\_answers.case\_log\_id is a plain UUID with no foreign key (06\_questions/02\_tables.sql:393).

![Where the 6 confirmed fields of rotations\_caselogdemographicsanswer went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FtCASTIGv6r1eYRhRA31W%2Fflow-rotations-caselogdemographicsanswer.png?alt=media)

| Medtrics Classic field     | Oracle                     | Outcome    | Note                |
| -------------------------- | -------------------------- | ---------- | ------------------- |
| `id` integer               | `id` UUID                  | ID changed | Integer link → UUID |
| `case_log_id` integer      | `case_log_id` UUID         | ID changed | Integer link → UUID |
| `answer_text` text         | `text_value` TEXT          | Renamed    |                     |
| `created_date` timestamptz | `created_at` TIMESTAMPTZ   | Renamed    | (Split)             |
| `created_date` timestamptz | `submitted_at` TIMESTAMPTZ | Renamed    | (Split)             |
| `updated_date` timestamptz | `updated_at` TIMESTAMPTZ   | Renamed    |                     |

* Swap the 4 renamed fields and point `FROM` at `question_answers`.
* Re-key 2 ID fields. Join people on `email`, not on old IDs.
* Add `WHERE deleted_at IS NULL`: `question_answers` keeps deleted rows.

```sql
-- Starter query: confirmed fields only. Was rotations_caselogdemographicsanswer.
SELECT
  t.id,           -- was id
  t.text_value,   -- was answer_text
  t.created_at,   -- was created_date
  t.submitted_at, -- was created_date
  t.updated_at,   -- was updated_date
  t.case_log_id   -- was case_log_id
FROM question_answers t
WHERE t.deleted_at IS NULL;
```

#### rotations\_procedurelog

**rotations\_procedurelog** → `case_logs` · *replaced/split* · 9 confirmed fields (Unchanged 4 · Renamed 2 · ID changed 3)

Case logs for patient encounters and clinical experiences.

> The 37-column Medtrics Classic row becomes a narrower record plus three child tables. Multiple diagnoses and procedures per case are now properly modelled with their own attributes instead of being crammed into paired CharField + many-to-many columns.

![Where the 9 confirmed fields of rotations\_procedurelog went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2Fye7SwsDh4VpeP2qkPdIB%2Fflow-rotations-procedurelog.png?alt=media)

| Medtrics Classic field       | Oracle                                      | Outcome    | Note                                                     |
| ---------------------------- | ------------------------------------------- | ---------- | -------------------------------------------------------- |
| `id` integer                 | `id` UUID                                   | ID changed | Integer link → UUID                                      |
| `supervisor_id` integer      | `supervisor_id` UUID                        | ID changed | Integer link → UUID pointing to profiles                 |
| `user_id` integer            | `user_id` UUID                              | ID changed | Integer link → UUID pointing to profiles                 |
| `age` integer                | `patient_age` INTEGER                       | Renamed    |                                                          |
| `gender` varchar             | `patient_gender` TEXT                       | Renamed    |                                                          |
| `status` varchar             | `status` enum:case\_status                  | Unchanged  | Now a fixed value list (case\_status) (Value list)       |
| `clinical_setting` varchar   | `clinical_setting` enum:clinical\_setting   | Unchanged  | Now a fixed value list (clinical\_setting) (Value list)  |
| `procedure_duration` integer | `procedure_duration` INTEGER                | Unchanged  |                                                          |
| `performance_level` varchar  | `performance_level` enum:performance\_level | Unchanged  | Now a fixed value list (performance\_level) (Value list) |

* Swap the 2 renamed fields and point `FROM` at `case_logs`.
* Re-key 3 ID fields. Join people on `email`, not on old IDs.
* Add `WHERE deleted_at IS NULL`: `case_logs` keeps deleted rows.

```sql
-- Starter query: confirmed fields only. Was rotations_procedurelog.
SELECT
  t.id,                 -- was id
  t.status,             -- was status
  t.patient_age,        -- was age
  t.patient_gender,     -- was gender
  t.clinical_setting,   -- was clinical_setting
  t.procedure_duration, -- was procedure_duration
  t.performance_level,  -- was performance_level
  t.supervisor_id,      -- was supervisor_id
  t.user_id             -- was user_id
FROM case_logs t
WHERE t.deleted_at IS NULL;
```

#### rotations\_procedurelog\_preceptors

**rotations\_procedurelog\_preceptors** → `case_log_preceptors` · 3 confirmed fields (ID changed 3)

Junction table for multiple preceptors/supervisors per case log (Medtrics Classic: ProcedureLog.preceptors M2M).

![Where the 3 confirmed fields of rotations\_procedurelog\_preceptors went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FO28ZSUaNFL6CmcRhMLCG%2Fflow-rotations-procedurelog-preceptors.png?alt=media)

| Medtrics Classic field    | Oracle             | Outcome    | Note                                       |
| ------------------------- | ------------------ | ---------- | ------------------------------------------ |
| `id` integer              | `id` UUID          | ID changed | Integer link → UUID                        |
| `procedurelog_id` integer | `case_log_id` UUID | ID changed | Integer link → UUID pointing to case\_logs |
| `user_id` integer         | `user_id` UUID     | ID changed | Integer link → UUID pointing to profiles   |

* Re-key 3 ID fields. Join people on `email`, not on old IDs.

```sql
-- Starter query: confirmed fields only. Was rotations_procedurelog_preceptors.
SELECT
  t.id,          -- was id
  t.case_log_id, -- was procedurelog_id
  t.user_id      -- was user_id
FROM case_log_preceptors t;
```

#### rotations\_procedurelog\_primary\_diagnoses

**rotations\_procedurelog\_primary\_diagnoses** → `case_log_diagnoses` · 3 confirmed fields (ID changed 3)

Junction table linking case logs to diagnoses.

![Where the 3 confirmed fields of rotations\_procedurelog\_primary\_diagnoses went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FvuhkUtexxTa7bfKbd0WY%2Fflow-rotations-procedurelog-primary-diagnoses.png?alt=media)

| Medtrics Classic field    | Oracle              | Outcome    | Note                                                                                                                |
| ------------------------- | ------------------- | ---------- | ------------------------------------------------------------------------------------------------------------------- |
| `id` integer              | `id` UUID           | ID changed | Integer ID replaced by a new UUID. Medtrics Classic → new ID crosswalk is kept by the migration (see Keys & joins). |
| `procedurelog_id` integer | `case_log_id` UUID  | ID changed | Integer link → UUID pointing to case\_logs                                                                          |
| `diagnosis_id` integer    | `diagnosis_id` UUID | ID changed | Integer link → UUID pointing to clinical\_items                                                                     |

* Re-key 3 ID fields. Join people on `email`, not on old IDs.

```sql
-- Starter query: confirmed fields only. Was rotations_procedurelog_primary_diagnoses.
SELECT
  t.id,           -- was id
  t.case_log_id,  -- was procedurelog_id
  t.diagnosis_id  -- was diagnosis_id
FROM case_log_diagnoses t;
```

#### rotations\_procedurelog\_procedures

**rotations\_procedurelog\_procedures** → `case_log_procedures` · 1 confirmed field (ID changed 1)

Junction table linking case logs to procedures.

| Medtrics Classic field | Oracle    | Outcome    | Note                                                                                                                |
| ---------------------- | --------- | ---------- | ------------------------------------------------------------------------------------------------------------------- |
| `id` integer           | `id` UUID | ID changed | Integer ID replaced by a new UUID. Medtrics Classic → new ID crosswalk is kept by the migration (see Keys & joins). |

* Re-key 1 ID field. Join people on `email`, not on old IDs.

```sql
-- Starter query: confirmed fields only. Was rotations_procedurelog_procedures.
SELECT
  t.id  -- was id
FROM case_log_procedures t;
```

#### rotations\_procedurelog\_secondary\_diagnoses

**rotations\_procedurelog\_secondary\_diagnoses** → `case_log_diagnoses` · 2 confirmed fields (ID changed 2)

Junction table linking case logs to diagnoses.

![Where the 2 confirmed fields of rotations\_procedurelog\_secondary\_diagnoses went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FenyQcsGJR9ukypIXurhk%2Fflow-rotations-procedurelog-secondary-diagnoses.png?alt=media)

| Medtrics Classic field | Oracle              | Outcome    | Note                                                                                                                |
| ---------------------- | ------------------- | ---------- | ------------------------------------------------------------------------------------------------------------------- |
| `id` integer           | `id` UUID           | ID changed | Integer ID replaced by a new UUID. Medtrics Classic → new ID crosswalk is kept by the migration (see Keys & joins). |
| `diagnosis_id` integer | `diagnosis_id` UUID | ID changed | Integer link → UUID pointing to clinical\_items                                                                     |

* Re-key 2 ID fields. Join people on `email`, not on old IDs.

```sql
-- Starter query: confirmed fields only. Was rotations_procedurelog_secondary_diagnoses.
SELECT
  t.id,           -- was id
  t.diagnosis_id  -- was diagnosis_id
FROM case_log_diagnoses t;
```

### Work Hours

5 tables, 56 confirmed fields: Unchanged 6 · Renamed 38 · ID changed 12.

#### duty\_hours\_dutyhour

**duty\_hours\_dutyhour** → `work_hours` · *renamed* · 27 confirmed fields (Unchanged 4 · Renamed 18 · ID changed 5)

Individual work hour entries for ACGME compliance tracking. Uses simplified boolean flags to control behavior (counts\_toward\_violations, is\_in\_hospital, is\_work\_time) instead of complex type hierarchies.

> Same idea, wider row. The old free-text 'shift' label became an enum plus three behaviour switches, and the optional rotation link changed from rotations.UserSchedule to block\_schedules.

![Where the 27 confirmed fields of duty\_hours\_dutyhour went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FscHqwW484voNCjZIQzjH%2Fflow-duty-hours-dutyhour.png?alt=media)

| Medtrics Classic field         | Oracle                                                                           | Outcome    | Note                                                                                             |
| ------------------------------ | -------------------------------------------------------------------------------- | ---------- | ------------------------------------------------------------------------------------------------ |
| `id` integer                   | `id` UUID                                                                        | ID changed | Integer link → UUID                                                                              |
| `third_party_id` varchar       | `external_id` TEXT                                                               | ID changed | Integer link → UUID                                                                              |
| `supervisor_id` integer        | `supervisor_user_id` UUID                                                        | ID changed | Integer link → UUID pointing to profiles (Split)                                                 |
| `supervisor_id` integer        | `supervisor_verification_state` enum:work\_hour\_supervisor\_verification\_state | ID changed | Integer link → UUID (Split, Merged)                                                              |
| `user_id` integer              | `user_id` UUID                                                                   | ID changed | Integer link → UUID pointing to profiles                                                         |
| `title` varchar                | `display_label` TEXT                                                             | Renamed    |                                                                                                  |
| `description` text             | `notes` TEXT                                                                     | Renamed    | (Merged)                                                                                         |
| `shift` varchar                | `counts_toward_violations` BOOLEAN                                               | Renamed    | text → bool (Split, Type changed)                                                                |
| `shift` varchar                | `is_home_call` BOOLEAN                                                           | Renamed    | text → bool (Split, Type changed)                                                                |
| `shift` varchar                | `is_in_hospital` BOOLEAN                                                         | Renamed    | text → bool (Split, Type changed)                                                                |
| `shift` varchar                | `is_moonlighting` BOOLEAN                                                        | Renamed    | text → bool (Split, Type changed)                                                                |
| `shift` varchar                | `is_work_time` BOOLEAN                                                           | Renamed    | text → bool (Split, Type changed)                                                                |
| `shift` varchar                | `work_category` enum:work\_category\_type                                        | Renamed    | Now a fixed value list (work\_category\_type) (Split, Value list)                                |
| `datetime` timestamptz         | `created_at` TIMESTAMPTZ                                                         | Renamed    | (Split)                                                                                          |
| `datetime` timestamptz         | `updated_at` TIMESTAMPTZ                                                         | Renamed    | (Split)                                                                                          |
| `start_datetime` timestamptz   | `start_at` TIMESTAMPTZ                                                           | Renamed    |                                                                                                  |
| `end_datetime` timestamptz     | `end_at` TIMESTAMPTZ                                                             | Renamed    |                                                                                                  |
| `comments` text                | `notes` TEXT                                                                     | Renamed    | (Merged)                                                                                         |
| `approved` boolean             | `is_approved` BOOLEAN                                                            | Renamed    | (Split)                                                                                          |
| `approved` boolean             | `supervisor_verification_state` enum:work\_hour\_supervisor\_verification\_state | Renamed    | Now a fixed value list (work\_hour\_supervisor\_verification\_state) (Split, Merged, Value list) |
| `third_party_data` jsonb       | `notes` TEXT                                                                     | Renamed    | json → text (Merged, Type changed)                                                               |
| `supervisor_disagreed` boolean | `supervisor_verification_state` enum:work\_hour\_supervisor\_verification\_state | Renamed    | Now a fixed value list (work\_hour\_supervisor\_verification\_state) (Merged, Value list)        |
| `verified_at` timestamptz      | `supervisor_verification_state` enum:work\_hour\_supervisor\_verification\_state | Renamed    | Now a fixed value list (work\_hour\_supervisor\_verification\_state) (Split, Merged, Value list) |
| `locked` boolean               | `locked` BOOLEAN                                                                 | Unchanged  |                                                                                                  |
| `violation_checked` boolean    | `violation_checked` BOOLEAN                                                      | Unchanged  |                                                                                                  |
| `supervisor_comment` text      | `supervisor_comment` TEXT                                                        | Unchanged  |                                                                                                  |
| `verified_at` timestamptz      | `verified_at` TIMESTAMPTZ                                                        | Unchanged  | (Split)                                                                                          |

* Swap the 18 renamed fields and point `FROM` at `work_hours`.
* Re-key 5 ID fields. Join people on `email`, not on old IDs.
* Add `WHERE deleted_at IS NULL`: `work_hours` keeps deleted rows.

```sql
-- Starter query: confirmed fields only. Was duty_hours_dutyhour.
SELECT
  t.id,                       -- was id
  t.display_label,            -- was title
  t.notes,                    -- was description
  t.counts_toward_violations, -- was shift
  t.is_home_call,             -- was shift
  t.is_in_hospital,           -- was shift
  t.is_moonlighting,          -- was shift
  t.is_work_time,             -- was shift
  t.work_category,            -- was shift
  t.created_at,               -- was datetime
  t.updated_at,               -- was datetime
  t.start_at,                 -- was start_datetime
  t.end_at,                   -- was end_datetime
  t.locked                    -- was locked
FROM work_hours t
WHERE t.deleted_at IS NULL;
```

#### duty\_hours\_dutyhourstypeprogram

**duty\_hours\_dutyhourstypeprogram** → `work_type_presets` · *replaced* · 11 confirmed fields (Renamed 9 · ID changed 2)

Optional UI templates for reusable work type configurations. Provides shortcuts but does NOT enforce types - actual behavior is controlled by boolean flags on work\_hours table.

> Presets are now optional UI shortcuts that do not enforce anything; behaviour comes from the switches on the entry.

![Where the 11 confirmed fields of duty\_hours\_dutyhourstypeprogram went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2Fbb7MxocUHp73HP1o8i7S%2Fflow-duty-hours-dutyhourstypeprogram.png?alt=media)

| Medtrics Classic field     | Oracle                                            | Outcome    | Note                                                              |
| -------------------------- | ------------------------------------------------- | ---------- | ----------------------------------------------------------------- |
| `id` integer               | `id` UUID                                         | ID changed | Integer link → UUID                                               |
| `program_id` integer       | `program_id` UUID                                 | ID changed | Integer link → UUID pointing to programs                          |
| `dh_type` text             | `code` TEXT                                       | Renamed    | (Split)                                                           |
| `dh_type` text             | `default_counts_toward_violations` BOOLEAN        | Renamed    | text → bool (Split, Type changed)                                 |
| `dh_type` text             | `default_is_in_hospital` BOOLEAN                  | Renamed    | text → bool (Split, Type changed)                                 |
| `dh_type` text             | `default_is_work_time` BOOLEAN                    | Renamed    | text → bool (Split, Type changed)                                 |
| `dh_type` text             | `default_work_category` enum:work\_category\_type | Renamed    | Now a fixed value list (work\_category\_type) (Split, Value list) |
| `dh_type` text             | `name` TEXT                                       | Renamed    | (Split)                                                           |
| `active` boolean           | `is_active` BOOLEAN                               | Renamed    |                                                                   |
| `created_date` timestamptz | `created_at` TIMESTAMPTZ                          | Renamed    |                                                                   |
| `updated_date` timestamptz | `updated_at` TIMESTAMPTZ                          | Renamed    |                                                                   |

* Swap the 9 renamed fields and point `FROM` at `work_type_presets`.
* Re-key 2 ID fields. Join people on `email`, not on old IDs.
* Add `WHERE deleted_at IS NULL`: `work_type_presets` keeps deleted rows.

```sql
-- Starter query: confirmed fields only. Was duty_hours_dutyhourstypeprogram.
SELECT
  t.id,                               -- was id
  t.code,                             -- was dh_type
  t.default_counts_toward_violations, -- was dh_type
  t.default_is_in_hospital,           -- was dh_type
  t.default_is_work_time,             -- was dh_type
  t.default_work_category,            -- was dh_type
  t.name,                             -- was dh_type
  t.is_active,                        -- was active
  t.program_id,                       -- was program_id
  t.created_at,                       -- was created_date
  t.updated_at                        -- was updated_date
FROM work_type_presets t
WHERE t.deleted_at IS NULL;
```

#### duty\_hours\_dutyhoursviolationtypeprogram

**duty\_hours\_dutyhoursviolationtypeprogram** → `work_hour_violation_types` · *replaced* · 7 confirmed fields (Renamed 5 · ID changed 2)

Institution and program-scoped ACGME violation rule definitions and override configurations.

> Medtrics Classic stored per-program on/off plus two numeric thresholds; Oracle stores the whole rule definition with institution defaults and program overrides and a JSONB config for limits.

![Where the 7 confirmed fields of duty\_hours\_dutyhoursviolationtypeprogram went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FHFeGVq5mrQqZ1DFlswsi%2Fflow-duty-hours-dutyhoursviolationtypeprogram.png?alt=media)

| Medtrics Classic field     | Oracle                   | Outcome    | Note                                     |
| -------------------------- | ------------------------ | ---------- | ---------------------------------------- |
| `id` integer               | `id` UUID                | ID changed | Integer link → UUID                      |
| `program_id` integer       | `program_id` UUID        | ID changed | Integer link → UUID pointing to programs |
| `violation_type` varchar   | `code` TEXT              | Renamed    | (Split)                                  |
| `violation_type` varchar   | `name` TEXT              | Renamed    | (Split)                                  |
| `active` boolean           | `is_active` BOOLEAN      | Renamed    |                                          |
| `created_date` timestamptz | `created_at` TIMESTAMPTZ | Renamed    |                                          |
| `updated_date` timestamptz | `updated_at` TIMESTAMPTZ | Renamed    |                                          |

* Swap the 5 renamed fields and point `FROM` at `work_hour_violation_types`.
* Re-key 2 ID fields. Join people on `email`, not on old IDs.
* Add `WHERE deleted_at IS NULL`: `work_hour_violation_types` keeps deleted rows.

```sql
-- Starter query: confirmed fields only. Was duty_hours_dutyhoursviolationtypeprogram.
SELECT
  t.id,         -- was id
  t.code,       -- was violation_type
  t.name,       -- was violation_type
  t.is_active,  -- was active
  t.created_at, -- was created_date
  t.updated_at, -- was updated_date
  t.program_id  -- was program_id
FROM work_hour_violation_types t
WHERE t.deleted_at IS NULL;
```

#### duty\_hours\_dutyhourviolationtype

**duty\_hours\_dutyhourviolationtype** → `work_hour_violation_reasons` · *renamed* · 5 confirmed fields (Unchanged 1 · Renamed 3 · ID changed 1)

Reason codes used by residents to self-justify work-hour violations. Scoped institution-wide or per program.

> Medtrics Classic's global 'reason' list becomes a list scoped to an institution or program, used when a resident self-justifies.

![Where the 5 confirmed fields of duty\_hours\_dutyhourviolationtype went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FRJmywRTNgvkSoH0AsSBT%2Fflow-duty-hours-dutyhourviolationtype.png?alt=media)

| Medtrics Classic field   | Oracle                   | Outcome    | Note                |
| ------------------------ | ------------------------ | ---------- | ------------------- |
| `id` integer             | `id` UUID                | ID changed | Integer link → UUID |
| `name` varchar           | `code` TEXT              | Renamed    | (Split)             |
| `name` varchar           | `label` TEXT             | Renamed    | (Split)             |
| `active` boolean         | `is_active` BOOLEAN      | Renamed    |                     |
| `created_at` timestamptz | `created_at` TIMESTAMPTZ | Unchanged  |                     |

* Swap the 3 renamed fields and point `FROM` at `work_hour_violation_reasons`.
* Re-key 1 ID field. Join people on `email`, not on old IDs.

```sql
-- Starter query: confirmed fields only. Was duty_hours_dutyhourviolationtype.
SELECT
  t.id,         -- was id
  t.code,       -- was name
  t.label,      -- was name
  t.is_active,  -- was active
  t.created_at  -- was created_at
FROM work_hour_violation_reasons t;
```

#### duty\_hours\_offday

**duty\_hours\_offday** → `off_days` · *renamed* · 6 confirmed fields (Unchanged 1 · Renamed 3 · ID changed 2)

Tracks scheduled days off for ACGME compliance (1 day off per 7 days requirement).

> Medtrics Classic stored one row per day tied to a rotation; Oracle stores a date range tied to the program, with an approval flag.

![Where the 6 confirmed fields of duty\_hours\_offday went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2F7IeKt2mvJ0q9ytbd4if5%2Fflow-duty-hours-offday.png?alt=media)

| Medtrics Classic field     | Oracle                   | Outcome    | Note                                     |
| -------------------------- | ------------------------ | ---------- | ---------------------------------------- |
| `id` integer               | `id` UUID                | ID changed | Integer link → UUID                      |
| `user_id` integer          | `user_id` UUID           | ID changed | Integer link → UUID pointing to profiles |
| `off_date` date            | `end_date` DATE          | Renamed    | (Split)                                  |
| `off_date` date            | `start_date` DATE        | Renamed    | (Split)                                  |
| `updated_date` timestamptz | `updated_at` TIMESTAMPTZ | Renamed    |                                          |
| `created_at` timestamptz   | `created_at` TIMESTAMPTZ | Unchanged  |                                          |

* Swap the 3 renamed fields and point `FROM` at `off_days`.
* Re-key 2 ID fields. Join people on `email`, not on old IDs.
* Add `WHERE deleted_at IS NULL`: `off_days` keeps deleted rows.

```sql
-- Starter query: confirmed fields only. Was duty_hours_offday.
SELECT
  t.id,         -- was id
  t.created_at, -- was created_at
  t.end_date,   -- was off_date
  t.start_date, -- was off_date
  t.user_id,    -- was user_id
  t.updated_at  -- was updated_date
FROM off_days t
WHERE t.deleted_at IS NULL;
```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.mymedtrics.com/get-started/classic-data-mapping/cases-and-hours.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.