> 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/programs.md).

# Programs

Programs & Institutions: 14 Medtrics Classic tables and their confirmed Oracle fields.

Programs & Institutions — 14 Medtrics Classic tables with 128 confirmed fields (9% of the mapping): Unchanged 23 · Renamed 82 · ID changed 23. 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).

![Programs: 14 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%2F95hEIDjar5zjZshlARv2%2Fmap-programs.png?alt=media)

### Programs & Institutions

14 tables, 128 confirmed fields: Unchanged 23 · Renamed 82 · ID changed 23.

#### institutions\_customdocumentmergefield

**institutions\_customdocumentmergefield** → `document_merge_fields` · *merged* · 10 confirmed fields (Unchanged 3 · Renamed 5 · ID changed 2)

Institution-configurable custom merge fields (text/select/chart/image/document). System fields stored in application code.

> Medtrics Classic kept the letter templates in the institutions app. Oracle brought them into the documents domain and normalised merge fields into a catalogue plus a per-template junction.

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

| Medtrics Classic field      | Oracle                  | Outcome    | Note                                      |
| --------------------------- | ----------------------- | ---------- | ----------------------------------------- |
| `id` integer                | `id` UUID               | ID changed | Integer link → UUID                       |
| `document_field_id` integer | `field_config` JSONB    | ID changed | Integer link → UUID (Merged)              |
| `field_type` varchar        | `field_config` JSONB    | Renamed    | text → json (Split, Merged, Type changed) |
| `field_options` jsonb       | `field_config` JSONB    | Renamed    | (Merged)                                  |
| `active` boolean            | `is_active` BOOLEAN     | Renamed    |                                           |
| `editable` boolean          | `is_editable` BOOLEAN   | Renamed    |                                           |
| `insertable` boolean        | `is_insertable` BOOLEAN | Renamed    |                                           |
| `field_name` varchar        | `field_name` TEXT       | Unchanged  |                                           |
| `field_type` varchar        | `field_type` TEXT       | Unchanged  | (Split)                                   |
| `default_answer` varchar    | `default_answer` TEXT   | Unchanged  |                                           |

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

```sql
-- Starter query: confirmed fields only. Was institutions_customdocumentmergefield.
SELECT
  t.id,             -- was id
  t.field_name,     -- was field_name
  t.field_config,   -- was field_type
  t.field_type,     -- was field_type
  t.is_active,      -- was active
  t.is_editable,    -- was editable
  t.default_answer, -- was default_answer
  t.is_insertable   -- was insertable
FROM document_merge_fields t
WHERE t.deleted_at IS NULL;
```

#### institutions\_customdocumenttemplate

**institutions\_customdocumenttemplate** → `document_templates` · *merged* · 8 confirmed fields (Unchanged 3 · Renamed 3 · ID changed 2)

Templates for generating MSPE reports, letters, and other formatted documents.

> Medtrics Classic kept the letter templates in the institutions app. Oracle brought them into the documents domain and normalised merge fields into a catalogue plus a per-template junction.

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

| Medtrics Classic field     | Oracle                                        | Outcome    | Note                                                                  |
| -------------------------- | --------------------------------------------- | ---------- | --------------------------------------------------------------------- |
| `id` integer               | `id` UUID                                     | ID changed | Integer link → UUID                                                   |
| `category_id` integer      | `document_category_id` UUID                   | ID changed | Integer link → UUID pointing to document\_categories                  |
| `name` varchar             | `template_type` enum:document\_template\_type | Renamed    | Now a fixed value list (document\_template\_type) (Split, Value list) |
| `content` text             | `template_content` TEXT                       | Renamed    |                                                                       |
| `active` boolean           | `is_active` BOOLEAN                           | Renamed    |                                                                       |
| `name` varchar             | `name` TEXT                                   | Unchanged  | (Split)                                                               |
| `pa_editable` boolean      | `pa_editable` BOOLEAN                         | Unchanged  |                                                                       |
| `is_mspe_document` boolean | `is_mspe_document` BOOLEAN                    | Unchanged  |                                                                       |

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

```sql
-- Starter query: confirmed fields only. Was institutions_customdocumenttemplate.
SELECT
  t.id,                   -- was id
  t.name,                 -- was name
  t.template_type,        -- was name
  t.template_content,     -- was content
  t.is_active,            -- was active
  t.pa_editable,          -- was pa_editable
  t.document_category_id, -- was category_id
  t.is_mspe_document      -- was is_mspe_document
FROM document_templates t
WHERE t.deleted_at IS NULL;
```

#### institutions\_customdocumenttemplate\_programs

**institutions\_customdocumenttemplate\_programs** → `document_template_programs` · 3 confirmed fields (ID changed 3)

Junction table: which programs can use which document templates.

![Where the 3 confirmed fields of institutions\_customdocumenttemplate\_programs went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2Fh8arA91RW0p4bQJNXcrp%2Fflow-institutions-customdocumenttemplate-programs.png?alt=media)

| Medtrics Classic field              | Oracle             | Outcome    | Note                                                |
| ----------------------------------- | ------------------ | ---------- | --------------------------------------------------- |
| `id` integer                        | `id` UUID          | ID changed | Integer link → UUID                                 |
| `customdocumenttemplate_id` integer | `template_id` UUID | ID changed | Integer link → UUID pointing to document\_templates |
| `residencyprogram_id` integer       | `program_id` UUID  | ID changed | Integer link → UUID pointing to programs            |

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

```sql
-- Starter query: confirmed fields only. Was institutions_customdocumenttemplate_programs.
SELECT
  t.id,          -- was id
  t.template_id, -- was customdocumenttemplate_id
  t.program_id   -- was residencyprogram_id
FROM document_template_programs t
WHERE t.deleted_at IS NULL;
```

#### institutions\_customeventtype

**institutions\_customeventtype** → `event_types` · *merged/renamed* · 3 confirmed fields (Unchanged 1 · Renamed 1 · ID changed 1)

Categories for events (lecture, lab, conference, etc.).

> Deleting an event type is now blocked while events still use it (ON DELETE RESTRICT), which is safer than Medtrics Classic's CASCADE that would have deleted the events.

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

| Medtrics Classic field | Oracle              | Outcome    | Note                |
| ---------------------- | ------------------- | ---------- | ------------------- |
| `id` integer           | `id` UUID           | ID changed | Integer link → UUID |
| `active` boolean       | `is_active` BOOLEAN | Renamed    |                     |
| `name` varchar         | `name` TEXT         | Unchanged  |                     |

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

```sql
-- Starter query: confirmed fields only. Was institutions_customeventtype.
SELECT
  t.id,        -- was id
  t.name,      -- was name
  t.is_active  -- was active
FROM event_types t
WHERE t.deleted_at IS NULL;
```

#### institutions\_customtitle

**institutions\_customtitle** → `custom_titles` · *renamed* · 4 confirmed fields (Unchanged 1 · Renamed 2 · ID changed 1)

Institution-configurable titles for user assignments.

> Institution-scoped titles, same concept.

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

| Medtrics Classic field            | Oracle                           | Outcome    | Note                |
| --------------------------------- | -------------------------------- | ---------- | ------------------- |
| `id` integer                      | `id` UUID                        | ID changed | Integer link → UUID |
| `active` boolean                  | `is_active` BOOLEAN              | Renamed    |                     |
| `administrative_use_only` boolean | `is_administrative_only` BOOLEAN | Renamed    |                     |
| `name` text                       | `name` TEXT                      | Unchanged  |                     |

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

```sql
-- Starter query: confirmed fields only. Was institutions_customtitle.
SELECT
  t.id,                     -- was id
  t.name,                   -- was name
  t.is_active,              -- was active
  t.is_administrative_only  -- was administrative_use_only
FROM custom_titles t
WHERE t.deleted_at IS NULL;
```

#### institutions\_department

**institutions\_department** → `departments` · *same* · 7 confirmed fields (Unchanged 2 · Renamed 3 · ID changed 2)

Organizational units/departments within sites.

> Near-identical, except Oracle adds a second optional parent (departments.program\_id) alongside the required site\_id.

![Where the 7 confirmed fields of institutions\_department went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2F7oOtrX2FrDpJQW2LUW8V%2Fflow-institutions-department.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). |
| `site_id` integer       | `site_id` UUID     | ID changed | Integer link → UUID pointing to sites                                                                               |
| `contact_name` varchar  | `description` TEXT | Renamed    | (Merged)                                                                                                            |
| `contact_phone` varchar | `phone` TEXT       | Renamed    |                                                                                                                     |
| `contact_email` varchar | `description` TEXT | Renamed    | (Merged)                                                                                                            |
| `name` varchar          | `name` TEXT        | Unchanged  |                                                                                                                     |
| `description` text      | `description` TEXT | Unchanged  | (Merged)                                                                                                            |

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

```sql
-- Starter query: confirmed fields only. Was institutions_department.
SELECT
  t.id,          -- was id
  t.name,        -- was name
  t.description, -- was contact_name
  t.phone,       -- was contact_phone
  t.site_id      -- was site_id
FROM departments t
WHERE t.deleted_at IS NULL;
```

#### institutions\_employer

**institutions\_employer** → `employers` · *renamed/same* · 4 confirmed fields (Unchanged 2 · Renamed 1 · ID changed 1)

Sponsoring employers for CMS IRIS reporting.

> Moved into the IRIS domain unchanged in meaning: the entity that holds a CMS provider number and bills Medicare.

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

| Medtrics Classic field    | Oracle                 | Outcome    | Note                |
| ------------------------- | ---------------------- | ---------- | ------------------- |
| `id` integer              | `id` UUID              | ID changed | Integer link → UUID |
| `address` varchar         | `address_line1` TEXT   | Renamed    |                     |
| `name` varchar            | `name` TEXT            | Unchanged  |                     |
| `provider_number` varchar | `provider_number` TEXT | Unchanged  |                     |

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

```sql
-- Starter query: confirmed fields only. Was institutions_employer.
SELECT
  t.id,              -- was id
  t.name,            -- was name
  t.address_line1,   -- was address
  t.provider_number  -- was provider_number
FROM employers t
WHERE t.deleted_at IS NULL;
```

#### institutions\_institutionnotificationsettings

**institutions\_institutionnotificationsettings** → `gme_reporting_settings` · *merged* · 6 confirmed fields (Unchanged 1 · Renamed 5)

Effective-dated institution settings that control GME reporting source selection and reproducibility.

> Roughly 70 Medtrics Classic columns became JSONB keys. The password policy in particular is now institution\_settings.login, whose comment lists the exact keys (PE-022): 'password\_min\_length, password\_requires\_uppercase/lowercase/numbers/special, password\_expiry\_days...'. Fewer migrations, but nothing stops a bad key being written.

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

| Medtrics Classic field                           | Oracle                                        | Outcome   | Note                               |
| ------------------------------------------------ | --------------------------------------------- | --------- | ---------------------------------- |
| `id` integer                                     | `settings` JSONB                              | Renamed   | int → json (Merged, Type changed)  |
| `shift_minimun_duration_iris` integer            | `minimum_iris_shift_duration_minutes` INTEGER | Renamed   |                                    |
| `diagnoses_and_procedure_filter_enabled` boolean | `settings` JSONB                              | Renamed   | bool → json (Merged, Type changed) |
| `created_date` timestamptz                       | `created_at` TIMESTAMPTZ                      | Renamed   |                                    |
| `updated_date` timestamptz                       | `updated_at` TIMESTAMPTZ                      | Renamed   |                                    |
| `include_shift_schedule_iris` boolean            | `include_shift_schedule_iris` BOOLEAN         | Unchanged |                                    |

* Swap the 5 renamed fields and point `FROM` at `gme_reporting_settings`.
* Add `WHERE deleted_at IS NULL`: `gme_reporting_settings` keeps deleted rows.

```sql
-- Starter query: confirmed fields only. Was institutions_institutionnotificationsettings.
SELECT
  t.settings,                            -- was id
  t.include_shift_schedule_iris,         -- was include_shift_schedule_iris
  t.minimum_iris_shift_duration_minutes, -- was shift_minimun_duration_iris
  t.created_at,                          -- was created_date
  t.updated_at                           -- was updated_date
FROM gme_reporting_settings t
WHERE t.deleted_at IS NULL;
```

#### institutions\_participatingsite

**institutions\_participatingsite** → `sites` · *split* · 14 confirmed fields (Unchanged 6 · Renamed 6 · ID changed 2)

Physical training sites/locations.

> The Medtrics Classic site carried its classification as free text ('designate'). Oracle splits that into three managed lists and gains an institution owner, a parent site, capacity fields and geo coordinates.

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

| Medtrics Classic field | Oracle                 | Outcome    | Note                             |
| ---------------------- | ---------------------- | ---------- | -------------------------------- |
| `id` integer           | `id` UUID              | ID changed | Integer link → UUID (Split)      |
| `site_id` varchar      | `code` TEXT            | ID changed | Integer link → UUID              |
| `id` integer           | `external_id` TEXT     | Renamed    | int → text (Split, Type changed) |
| `address` varchar      | `address_line_1` TEXT  | Renamed    |                                  |
| `zip_code` varchar     | `postal_code` TEXT     | Renamed    |                                  |
| `active` boolean       | `is_active` BOOLEAN    | Renamed    |                                  |
| `student_max` integer  | `max_trainees` INTEGER | Renamed    |                                  |
| `student_min` integer  | `min_trainees` INTEGER | Renamed    |                                  |
| `name` varchar         | `name` TEXT            | Unchanged  |                                  |
| `city` varchar         | `city` TEXT            | Unchanged  |                                  |
| `state` varchar        | `state` TEXT           | Unchanged  |                                  |
| `country` varchar      | `country` TEXT         | Unchanged  |                                  |
| `description` text     | `description` TEXT     | Unchanged  |                                  |
| `phone` varchar        | `phone` TEXT           | Unchanged  |                                  |

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

```sql
-- Starter query: confirmed fields only. Was institutions_participatingsite.
SELECT
  t.external_id,    -- was id
  t.id,             -- was id
  t.name,           -- was name
  t.address_line_1, -- was address
  t.postal_code,    -- was zip_code
  t.city,           -- was city
  t.state,          -- was state
  t.country,        -- was country
  t.description,    -- was description
  t.phone,          -- was phone
  t.is_active,      -- was active
  t.code,           -- was site_id
  t.max_trainees,   -- was student_max
  t.min_trainees    -- was student_min
FROM sites t
WHERE t.deleted_at IS NULL;
```

#### institutions\_sessioneventtype

**institutions\_sessioneventtype** → `curriculum_session_types` · *merged* · 5 confirmed fields (Unchanged 2 · Renamed 2 · ID changed 1)

Session types (e.g., Lecture, Lab, Clinical, Small Group); shared institution-wide when program\_id IS NULL, program-scoped otherwise.

> Two Medtrics Classic event-type tables collapse into one institution-scoped list.

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

| Medtrics Classic field | Oracle              | Outcome    | Note                |
| ---------------------- | ------------------- | ---------- | ------------------- |
| `id` integer           | `id` UUID           | ID changed | Integer link → UUID |
| `active` boolean       | `is_active` BOOLEAN | Renamed    |                     |
| `event_type` varchar   | `code` TEXT         | Renamed    |                     |
| `name` varchar         | `name` TEXT         | Unchanged  |                     |
| `color` varchar        | `color` TEXT        | Unchanged  |                     |

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

```sql
-- Starter query: confirmed fields only. Was institutions_sessioneventtype.
SELECT
  t.id,        -- was id
  t.name,      -- was name
  t.is_active, -- was active
  t.code,      -- was event_type
  t.color      -- was color
FROM curriculum_session_types t
WHERE t.deleted_at IS NULL;
```

#### programs\_notificationsettings

**programs\_notificationsettings** → `program_settings`, `work_hour_notification_settings` · *merged* · 53 confirmed fields (Renamed 49 · ID changed 4)

Configurable settings per program. Split table: each field names its new table.

> About 80 Medtrics Classic per-program boolean columns became JSONB buckets (cases, evaluations, notifications, scheduler, work\_hours, curriculum, integrations, capacity, regional, benefits).

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

| Medtrics Classic field                                   | Oracle                                                                        | Outcome    | Note                                             |
| -------------------------------------------------------- | ----------------------------------------------------------------------------- | ---------- | ------------------------------------------------ |
| `program_id` integer                                     | program\_settings.`program_id` UUID                                           | ID changed | Integer link → UUID pointing to programs (Split) |
| `program_id` integer                                     | work\_hour\_notification\_settings.`program_id` UUID                          | ID changed | Integer link → UUID pointing to programs (Split) |
| `created_by_id` integer                                  | program\_settings.`created_by` UUID                                           | ID changed | Integer link → UUID pointing to profiles         |
| `updated_by_id` integer                                  | program\_settings.`updated_by` UUID                                           | ID changed | Integer link → UUID pointing to profiles         |
| `duty_hour_reminder` integer                             | work\_hour\_notification\_settings.`remind_after_days` INTEGER                | Renamed    |                                                  |
| `duty_hour_reminder_enabled` boolean                     | work\_hour\_notification\_settings.`remind_resident_missing_log` BOOLEAN      | Renamed    |                                                  |
| `curriculum_distribution` integer                        | program\_settings.`curriculum` JSONB                                          | Renamed    | int → json (Merged, Type changed)                |
| `curriculum_distribution_enabled` boolean                | program\_settings.`curriculum` JSONB                                          | Renamed    | bool → json (Merged, Type changed)               |
| `evaluation_assignment_enabled` boolean                  | program\_settings.`evaluations` JSONB                                         | Renamed    | bool → json (Merged, Type changed)               |
| `shift_change_requests_enabled` boolean                  | program\_settings.`scheduler` JSONB                                           | Renamed    | bool → json (Merged, Type changed)               |
| `shift_change_swap_users_enabled` boolean                | program\_settings.`scheduler` JSONB                                           | Renamed    | bool → json (Merged, Type changed)               |
| `work_hour_log_compliance_enabled` boolean               | work\_hour\_notification\_settings.`config` JSONB                             | Renamed    | bool → json (Merged, Type changed)               |
| `work_hour_log_compliance_include_weekends` boolean      | work\_hour\_notification\_settings.`config` JSONB                             | Renamed    | bool → json (Merged, Type changed)               |
| `send_email_to_pd_after_violation_triggered` boolean     | work\_hour\_notification\_settings.`email_pd_on_violation` BOOLEAN            | Renamed    |                                                  |
| `form_distribution_enabled` boolean                      | program\_settings.`evaluations` JSONB                                         | Renamed    | bool → json (Merged, Type changed)               |
| `forms_assignment_hold_enabled` boolean                  | program\_settings.`evaluations` JSONB                                         | Renamed    | bool → json (Merged, Type changed)               |
| `forms_assignment_hold_interval` integer                 | program\_settings.`evaluations` JSONB                                         | Renamed    | int → json (Merged, Type changed)                |
| `send_administrative_emails` boolean                     | program\_settings.`notifications` JSONB                                       | Renamed    | bool → json (Merged, Type changed)               |
| `max_duty_hours_rule` varchar                            | program\_settings.`work_hours` JSONB                                          | Renamed    | text → json (Merged, Type changed)               |
| `enforce_aamc_objective_code_lengths` boolean            | program\_settings.`curriculum` JSONB                                          | Renamed    | bool → json (Merged, Type changed)               |
| `send_confidential_evaluations_published_emails` boolean | program\_settings.`evaluations` JSONB                                         | Renamed    | bool → json (Merged, Type changed)               |
| `bulk_approve_pending_cases_enabled` boolean             | program\_settings.`cases` JSONB                                               | Renamed    | bool → json (Merged, Type changed)               |
| `log_case_reminder_days` integer                         | program\_settings.`cases` JSONB                                               | Renamed    | int → json (Merged, Type changed)                |
| `log_case_reminder_enabled` boolean                      | program\_settings.`cases` JSONB                                               | Renamed    | bool → json (Merged, Type changed)               |
| `shift_change_block_schedule_review_enabled` boolean     | program\_settings.`scheduler` JSONB                                           | Renamed    | bool → json (Merged, Type changed)               |
| `created_date` timestamptz                               | program\_settings.`created_at` TIMESTAMPTZ                                    | Renamed    | (Split)                                          |
| `created_date` timestamptz                               | work\_hour\_notification\_settings.`created_at` TIMESTAMPTZ                   | Renamed    | (Split)                                          |
| `updated_date` timestamptz                               | program\_settings.`updated_at` TIMESTAMPTZ                                    | Renamed    | (Split)                                          |
| `updated_date` timestamptz                               | work\_hour\_notification\_settings.`updated_at` TIMESTAMPTZ                   | Renamed    | (Split)                                          |
| `case_duration_interval_minutes` integer                 | program\_settings.`cases` JSONB                                               | Renamed    | int → json (Merged, Type changed)                |
| `case_duration_limits` boolean                           | program\_settings.`cases` JSONB                                               | Renamed    | bool → json (Merged, Type changed)               |
| `case_duration_maximum` integer                          | program\_settings.`cases` JSONB                                               | Renamed    | int → json (Merged, Type changed)                |
| `case_duration_minimum` integer                          | program\_settings.`cases` JSONB                                               | Renamed    | int → json (Merged, Type changed)                |
| `curriculum_academic_progress_on_student_portal` boolean | program\_settings.`curriculum` JSONB                                          | Renamed    | bool → json (Merged, Type changed)               |
| `notify_program_admin_disabled` boolean                  | program\_settings.`notifications` JSONB                                       | Renamed    | bool → json (Merged, Type changed)               |
| `case_log_window_days` integer                           | program\_settings.`cases` JSONB                                               | Renamed    | int → json (Merged, Type changed)                |
| `case_log_window_enabled` boolean                        | program\_settings.`cases` JSONB                                               | Renamed    | bool → json (Merged, Type changed)               |
| `rotation_swap_request_deadline_days` integer            | program\_settings.`scheduler` JSONB                                           | Renamed    | int → json (Merged, Type changed)                |
| `rotation_swap_request_deadline_enabled` boolean         | program\_settings.`scheduler` JSONB                                           | Renamed    | bool → json (Merged, Type changed)               |
| `auto_finalize_duty_hours_enabled` boolean               | program\_settings.`work_hours` JSONB                                          | Renamed    | bool → json (Split, Merged, Type changed)        |
| `auto_finalize_duty_hours_enabled` boolean               | work\_hour\_notification\_settings.`config` JSONB                             | Renamed    | bool → json (Split, Merged, Type changed)        |
| `auto_finalize_duty_hours_interval_days` integer         | program\_settings.`work_hours` JSONB                                          | Renamed    | int → json (Split, Merged, Type changed)         |
| `auto_finalize_duty_hours_interval_days` integer         | work\_hour\_notification\_settings.`config` JSONB                             | Renamed    | int → json (Split, Merged, Type changed)         |
| `auto_finalize_duty_hours_reminder_days` integer         | work\_hour\_notification\_settings.`remind_period_closing` BOOLEAN            | Renamed    | int → bool (Split, Type changed)                 |
| `auto_finalize_duty_hours_reminder_days` integer         | work\_hour\_notification\_settings.`remind_period_days_before` INTEGER        | Renamed    | (Split)                                          |
| `supervisor_verification_day_of_week` integer            | work\_hour\_notification\_settings.`weekly_supervisor_digest_dow` INTEGER     | Renamed    |                                                  |
| `supervisor_verification_enabled` boolean                | work\_hour\_notification\_settings.`weekly_supervisor_digest_enabled` BOOLEAN | Renamed    |                                                  |
| `work_hour_disagreement_notification_enabled` boolean    | work\_hour\_notification\_settings.`email_coords_on_disagreement` BOOLEAN     | Renamed    |                                                  |
| `rotation_auto_generate_codes` boolean                   | program\_settings.`scheduler` JSONB                                           | Renamed    | bool → json (Merged, Type changed)               |
| `rotation_code_format_order` varchar                     | program\_settings.`scheduler` JSONB                                           | Renamed    | text → json (Merged, Type changed)               |
| `rotation_code_format_separator` varchar                 | program\_settings.`scheduler` JSONB                                           | Renamed    | text → json (Merged, Type changed)               |
| `rotation_require_gcr_mapping` boolean                   | program\_settings.`scheduler` JSONB                                           | Renamed    | bool → json (Merged, Type changed)               |
| `auto_finalize_duty_hours_day_of_week` integer           | program\_settings.`work_hours` JSONB                                          | Renamed    | int → json (Merged, Type changed)                |

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

#### programs\_programsite

**programs\_programsite** → `site_permissions` · *replaced* · 2 confirmed fields (ID changed 2)

User permissions for specific sites.

> The many-to-many junction was dropped in favour of a single nullable program\_id on sites. This is a capability reduction, not a simplification - see finding orgs-F5.

![Where the 2 confirmed fields of programs\_programsite went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FA1ZW6oU1jFk1oGgZH1bx%2Fflow-programs-programsite.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). |
| `site_id` integer      | `site_id` UUID | ID changed | Integer link → UUID pointing to sites                                                                               |

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

```sql
-- Starter query: confirmed fields only. Was programs_programsite.
SELECT
  t.id,      -- was id
  t.site_id  -- was site_id
FROM site_permissions t
WHERE t.deleted_at IS NULL;
```

#### programs\_residencycode

**programs\_residencycode** → `medical_specialties` · *replaced/split* · 4 confirmed fields (Unchanged 1 · Renamed 2 · ID changed 1)

Reference list of medical specialty/residency codes (e.g. ACGME-style specialty codes). Read-only reference data, seeded from Medtrics Classic client migrations.

> Institution-configurable codes stayed in residency\_codes; the authoritative CMS list became separate versioned reference data.

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

| Medtrics Classic field        | Oracle             | Outcome    | Note                |
| ----------------------------- | ------------------ | ---------- | ------------------- |
| `id` integer                  | `id` UUID          | ID changed | Integer link → UUID |
| `restyp_code` varchar         | `code` TEXT        | Renamed    |                     |
| `primary_description` varchar | `description` TEXT | Renamed    |                     |
| `name` varchar                | `name` TEXT        | Unchanged  |                     |

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

```sql
-- Starter query: confirmed fields only. Was programs_residencycode.
SELECT
  t.id,          -- was id
  t.code,        -- was restyp_code
  t.name,        -- was name
  t.description  -- was primary_description
FROM medical_specialties t;
```

#### programs\_residencyprogram

**programs\_residencyprogram** → `programs` · *same/split* · 5 confirmed fields (Unchanged 1 · Renamed 3 · ID changed 1)

Educational programs (residencies, fellowships, clerkships).

> Mapped here only because everything in this area hangs off it. The program table itself is analysed in another area.

![Where the 5 confirmed fields of programs\_residencyprogram went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FM5nhyMaEIghN02spsWFY%2Fflow-programs-residencyprogram.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). |
| `codename` varchar      | `code` TEXT                | Renamed    |                                                                                                                     |
| `active` boolean        | `is_active` BOOLEAN        | Renamed    |                                                                                                                     |
| `not_part_iris` boolean | `not_part_of_iris` BOOLEAN | Renamed    |                                                                                                                     |
| `name` varchar          | `name` TEXT                | Unchanged  |                                                                                                                     |

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

```sql
-- Starter query: confirmed fields only. Was programs_residencyprogram.
SELECT
  t.id,               -- was id
  t.name,             -- was name
  t.code,             -- was codename
  t.is_active,        -- was active
  t.not_part_of_iris  -- was not_part_iris
FROM programs 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/programs.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.