> 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/people-and-roles.md). # People and roles Users & Roles, Trainees / Students, Faculty / Educators: 40 Medtrics Classic tables and their confirmed Oracle fields. Users & Roles, Trainees / Students, Faculty / Educators — 40 Medtrics Classic tables with 273 confirmed fields (20% of the mapping): Unchanged 63 · Renamed 109 · ID changed 101. 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). ![People and roles: 40 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%2FXDzk1l5QtEskuceBWzah%2Fmap-people.png?alt=media) ### Users & Roles 27 tables, 189 confirmed fields: Unchanged 48 · Renamed 72 · ID changed 69. #### authentication\_user **authentication\_user** → `auth.identities`, `auth.users`, `profiles` · *split* · 31 confirmed fields (Unchanged 19 · Renamed 8 · ID changed 4) Core user profile, linked to auth.users via id. Split table: each field names its new table. > Medtrics Classic kept the login and the person in one row plus a one-to-one shadow profile. Oracle splits the credential (auth.users) from the person (profiles) and folds the shadow profile's general fields back into profiles. ![Where the 31 confirmed fields of authentication\_user went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2Ft21YuXq5hJoMkjBwpVcR%2Fflow-authentication-user.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | --------------------------- | ------------------------------------- | ---------- | -------------------------------------------------- | | `id` integer | auth.identities.`user_id` UUID | ID changed | Integer link → UUID (Split) | | `id` integer | auth.users.`id` UUID | ID changed | Integer link → UUID (Split) | | `id` integer | profiles.`id` UUID | ID changed | Integer link → UUID pointing to auth.users (Split) | | `application_id` varchar | profiles.`application_id` TEXT | ID changed | Integer link → UUID | | `id` integer | auth.identities.`identity_data` JSONB | Renamed | int → json (Split, Merged, Type changed) | | `id` integer | auth.identities.`provider_id` TEXT | Renamed | int → text (Split, Type changed) | | `email` varchar | auth.identities.`identity_data` JSONB | Renamed | text → json (Split, Merged, Type changed) | | `photo` varchar | profiles.`photo_url` TEXT | Renamed | | | `date_joined` timestamptz | auth.users.`created_at` TIMESTAMPTZ | Renamed | (Split) | | `date_joined` timestamptz | profiles.`created_at` TIMESTAMPTZ | Renamed | (Split) | | `ssn` varchar | profiles.`ssn_encrypted` TEXT | Renamed | | | `sin` varchar | profiles.`sin_encrypted` TEXT | Renamed | | | `email` varchar | auth.users.`email` TEXT | Unchanged | (Split) | | `email` varchar | profiles.`email` TEXT | Unchanged | (Split) | | `secondary_email` varchar | profiles.`secondary_email` TEXT | Unchanged | | | `first_name` varchar | profiles.`first_name` TEXT | Unchanged | | | `last_name` varchar | profiles.`last_name` TEXT | Unchanged | | | `previous_name` varchar | profiles.`previous_name` TEXT | Unchanged | | | `middle_name` varchar | profiles.`middle_name` TEXT | Unchanged | | | `suffix` varchar | profiles.`suffix` TEXT | Unchanged | | | `phone` varchar | profiles.`phone` TEXT | Unchanged | | | `cell_phone` varchar | profiles.`cell_phone` TEXT | Unchanged | | | `home_phone` varchar | profiles.`home_phone` TEXT | Unchanged | | | `pager` varchar | profiles.`pager` TEXT | Unchanged | | | `class_of` varchar | profiles.`class_of` TEXT | Unchanged | | | `avatar_color` varchar | profiles.`avatar_color` TEXT | Unchanged | | | `is_active` boolean | profiles.`is_active` BOOLEAN | Unchanged | | | `about_me` text | profiles.`about_me` TEXT | Unchanged | | | `employee_number` varchar | profiles.`employee_number` TEXT | Unchanged | | | `work_mobile_phone` varchar | profiles.`work_mobile_phone` TEXT | Unchanged | | | `cc_email` varchar | profiles.`cc_email` TEXT | Unchanged | | * Swap the 8 renamed fields and point `FROM` at `auth.identities` (and the other new tables). * Re-key 4 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `profiles` keeps deleted rows. #### companies\_facultyrole **companies\_facultyrole** → `form_assignment_rule_recipient_roles`, `form_assignment_rule_responder_roles`, `roles` · *renamed* · 10 confirmed fields (Unchanged 4 · Renamed 2 · ID changed 4) Roles that receive forms under a rule. Split table: each field names its new table. > Same two-level idea (user type groups roles), but Oracle roles can be scoped to an organization or program, whereas companies.CustomRole.name was globally unique. ![Where the 10 confirmed fields of companies\_facultyrole went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FtJAHcHPcm6dR8ZF20uA6%2Fflow-companies-facultyrole.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | ------------------------------ | ------------------------------------------------------- | ---------- | ----------------------------------------------------------- | | `id` integer | form\_assignment\_rule\_recipient\_roles.`role_id` UUID | ID changed | Integer link → UUID pointing to roles (Split) | | `id` integer | form\_assignment\_rule\_responder\_roles.`role_id` UUID | ID changed | Integer link → UUID pointing to roles (Split) | | `id` integer | roles.`id` UUID | ID changed | Integer link → UUID (Split) | | `user_type_id` integer | roles.`user_type_id` UUID | ID changed | Integer link → UUID pointing to user\_types | | `id` integer | roles.`display_order` INTEGER | Renamed | (Split) | | `allow_log_work_hours` boolean | roles.`allow_log_hours` BOOLEAN | Renamed | | | `name` varchar | roles.`name` TEXT | Unchanged | | | `designate` varchar | roles.`designate` enum:role\_designate\_type | Unchanged | Now a fixed value list (role\_designate\_type) (Value list) | | `requires_sso` boolean | roles.`requires_sso` BOOLEAN | Unchanged | | | `notifications_email` boolean | roles.`notifications_email` BOOLEAN | Unchanged | | * Swap the 2 renamed fields and point `FROM` at `form_assignment_rule_recipient_roles` (and the other new tables). * Re-key 4 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `roles` keeps deleted rows. #### institutions\_institutionadministrator **institutions\_institutionadministrator** → `delegated_admin_assignments` · *merged* · 2 confirmed fields (ID changed 2) Scoped delegated administrator grants that reference existing permission codes or bundles. > Six inheritance tables plus an institution-admin table collapse into leadership assignments with a position lookup, and scoped admin grants. Day-to-day membership lives in program\_memberships (People area). ![Where the 2 confirmed fields of institutions\_institutionadministrator went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FCbbIzZVimW5v1QMMdyww%2Fflow-institutions-institutionadministrator.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). | | `user_id` integer | `user_id` UUID | ID changed | Integer link → UUID pointing to profiles | * Re-key 2 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `delegated_admin_assignments` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was institutions_institutionadministrator. SELECT t.id, -- was id t.user_id -- was user_id FROM delegated_admin_assignments t WHERE t.deleted_at IS NULL; ``` #### programs\_affiliation **programs\_affiliation** → `off_days` · *merged* · 2 confirmed fields (ID changed 2) Tracks scheduled days off for ACGME compliance (1 day off per 7 days requirement). > The Oracle table comment says so outright. This is the single biggest improvement in the area: which program and which role are now one dated, status-bearing row. ![Where the 2 confirmed fields of programs\_affiliation went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FePB6RAB3JdwrlawDX21M%2Fflow-programs-affiliation.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). | | `program_id` integer | `program_id` UUID | ID changed | Integer link → UUID pointing to programs | * 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 programs_affiliation. SELECT t.id, -- was id t.program_id -- was program_id FROM off_days t WHERE t.deleted_at IS NULL; ``` #### programs\_programadministrator **programs\_programadministrator** → `delegated_admin_assignments` · *merged* · 8 confirmed fields (Renamed 4 · ID changed 4) Scoped delegated administrator grants that reference existing permission codes or bundles. > Six inheritance tables plus an institution-admin table collapse into leadership assignments with a position lookup, and scoped admin grants. Day-to-day membership lives in program\_memberships (People area). ![Where the 8 confirmed fields of programs\_programadministrator went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FR9zMEp1QXcB2GrhXqyzW%2Fflow-programs-programadministrator.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 (Split) | | `program_id` integer | `scope_id` UUID | ID changed | Integer link → UUID (Split) | | `user_id` integer | `user_id` UUID | ID changed | Integer link → UUID pointing to profiles | | `is_default` boolean | `metadata` JSONB | Renamed | bool → json (Merged, Type changed) | | `view_confidental_data` boolean | `metadata` JSONB | Renamed | bool → json (Merged, Type changed) | | `created_date` timestamptz | `created_at` TIMESTAMPTZ | Renamed | | | `updated_date` timestamptz | `updated_at` TIMESTAMPTZ | Renamed | | * Swap the 4 renamed fields and point `FROM` at `delegated_admin_assignments`. * Re-key 4 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `delegated_admin_assignments` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was programs_programadministrator. SELECT t.id, -- was id t.metadata, -- was is_default t.created_at, -- was created_date t.program_id, -- was program_id t.scope_id, -- was program_id t.user_id, -- was user_id t.updated_at -- was updated_date FROM delegated_admin_assignments t WHERE t.deleted_at IS NULL; ``` #### users\_customfield **users\_customfield** → `custom_field_categories`, `custom_field_category_user_types` · *merged* · 8 confirmed fields (Unchanged 2 · Renamed 3 · ID changed 3) Institution-scoped custom field definitions for users, sites, and programs. Split table: each field names its new table. > Medtrics Classic had two parallel custom-field stacks (one for people, one for sites and programs). Oracle unified them into one stack -- but housed it in the Organizations domain, not People. ![Where the 8 confirmed fields of users\_customfield went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FR4sqRTbuIPbrnT1OObZc%2Fflow-users-customfield.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | ---------------------- | --------------------------------------------------------------- | ---------- | ----------------------------------------------------------------- | | `id` integer | custom\_field\_categories.`id` UUID | ID changed | Integer link → UUID (Split) | | `id` integer | custom\_field\_category\_user\_types.`category_id` UUID | ID changed | Integer link → UUID pointing to custom\_field\_categories (Split) | | `category_id` integer | custom\_field\_categories.`group_id` UUID | ID changed | Integer link → UUID pointing to custom\_field\_groups | | `custom_type` varchar | custom\_field\_categories.`field_type` enum:custom\_field\_type | Renamed | Now a fixed value list (custom\_field\_type) (Value list) | | `active` boolean | custom\_field\_categories.`is_active` BOOLEAN | Renamed | | | `order` integer | custom\_field\_categories.`display_order` INTEGER | Renamed | | | `name` varchar | custom\_field\_categories.`name` TEXT | Unchanged | | | `description` text | custom\_field\_categories.`description` TEXT | Unchanged | | * Swap the 3 renamed fields and point `FROM` at `custom_field_categories` (and the other new tables). * Re-key 3 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `custom_field_categories` keeps deleted rows. #### users\_customfieldanswer **users\_customfieldanswer** → `custom_field_entity_values` · *merged* · 3 confirmed fields (ID changed 3) Stores per-entity values for institution-configured custom fields. Supports text, number, date, and JSON (multiselect) value types. > Medtrics Classic had two parallel custom-field stacks (one for people, one for sites and programs). Oracle unified them into one stack -- but housed it in the Organizations domain, not People. ![Where the 3 confirmed fields of users\_customfieldanswer went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2F1ZKVqyZUdWUY6uVFVSMu%2Fflow-users-customfieldanswer.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | ------------------------- | ------------------ | ---------- | --------------------------------------------------------- | | `id` integer | `id` UUID | ID changed | Integer link → UUID | | `custom_field_id` integer | `category_id` UUID | ID changed | Integer link → UUID pointing to custom\_field\_categories | | `user_id` integer | `entity_id` UUID | ID changed | Integer link → UUID | * Re-key 3 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `custom_field_entity_values` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was users_customfieldanswer. SELECT t.id, -- was id t.category_id, -- was custom_field_id t.entity_id -- was user_id FROM custom_field_entity_values t WHERE t.deleted_at IS NULL; ``` #### users\_customfieldcategory **users\_customfieldcategory** → `custom_field_groups` · *merged* · 5 confirmed fields (Unchanged 2 · Renamed 2 · ID changed 1) Institution-scoped parent groups for custom field definitions; UI labels these as categories. > Medtrics Classic had two parallel custom-field stacks (one for people, one for sites and programs). Oracle unified them into one stack -- but housed it in the Organizations domain, not People. ![Where the 5 confirmed fields of users\_customfieldcategory went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FlFmvutjpiw8HHKaxWT6u%2Fflow-users-customfieldcategory.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | ---------------------- | ---------------------------------------------- | ---------- | ----------------------------------------------------------------- | | `id` integer | `id` UUID | ID changed | Integer link → UUID | | `order` integer | `display_order` INTEGER | Renamed | | | `type` varchar | `entity_type` enum:custom\_field\_entity\_type | Renamed | Now a fixed value list (custom\_field\_entity\_type) (Value list) | | `name` varchar | `name` TEXT | Unchanged | | | `description` text | `description` TEXT | Unchanged | | * Swap the 2 renamed fields and point `FROM` at `custom_field_groups`. * Re-key 1 ID field. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `custom_field_groups` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was users_customfieldcategory. SELECT t.id, -- was id t.name, -- was name t.description, -- was description t.display_order, -- was order t.entity_type -- was type FROM custom_field_groups t WHERE t.deleted_at IS NULL; ``` #### users\_customfieldoption **users\_customfieldoption** → `custom_field_entity_values`, `custom_field_options` · *merged* · 8 confirmed fields (Renamed 6 · ID changed 2) Stores per-entity values for institution-configured custom fields. Supports text, number, date, and JSON (multiselect) value types. Split table: each field names its new table. > Medtrics Classic had two parallel custom-field stacks (one for people, one for sites and programs). Oracle unified them into one stack -- but housed it in the Organizations domain, not People. ![Where the 8 confirmed fields of users\_customfieldoption went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2F4sX7t0bRKxozaqbrlePH%2Fflow-users-customfieldoption.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | -------------------------- | ----------------------------------------------- | ---------- | --------------------------------------------------------- | | `id` integer | custom\_field\_options.`id` UUID | ID changed | Integer link → UUID | | `custom_fields_id` integer | custom\_field\_options.`category_id` UUID | ID changed | Integer link → UUID pointing to custom\_field\_categories | | `text` varchar | custom\_field\_entity\_values.`value_text` TEXT | Renamed | (Split) | | `text` varchar | custom\_field\_options.`label` TEXT | Renamed | (Split) | | `text` varchar | custom\_field\_options.`value` TEXT | Renamed | (Split) | | `created_date` timestamptz | custom\_field\_options.`created_at` TIMESTAMPTZ | Renamed | | | `order` integer | custom\_field\_options.`display_order` INTEGER | Renamed | | | `updated_date` timestamptz | custom\_field\_options.`updated_at` TIMESTAMPTZ | Renamed | | * Swap the 6 renamed fields and point `FROM` at `custom_field_entity_values` (and the other new tables). * Re-key 2 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `custom_field_entity_values` keeps deleted rows. #### users\_customsitefield **users\_customsitefield** → `custom_field_categories` · *merged* · 10 confirmed fields (Unchanged 2 · Renamed 6 · ID changed 2) Institution-scoped custom field definitions for users, sites, and programs. > Medtrics Classic had two parallel custom-field stacks (one for people, one for sites and programs). Oracle unified them into one stack -- but housed it in the Organizations domain, not People. ![Where the 10 confirmed fields of users\_customsitefield went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2Fher8lS9lmVTaL1lBtmPg%2Fflow-users-customsitefield.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | -------------------------- | ---------------------------------------------- | ---------- | ----------------------------------------------------------------- | | `id` integer | `id` UUID | ID changed | Integer link → UUID | | `category_id` integer | `group_id` UUID | ID changed | Integer link → UUID pointing to custom\_field\_groups | | `custom_type` varchar | `field_type` enum:custom\_field\_type | Renamed | Now a fixed value list (custom\_field\_type) (Value list) | | `order` integer | `display_order` INTEGER | Renamed | | | `active` boolean | `is_active` BOOLEAN | Renamed | | | `created_date` timestamptz | `created_at` TIMESTAMPTZ | Renamed | | | `updated_date` timestamptz | `updated_at` TIMESTAMPTZ | Renamed | | | `org_type` varchar | `entity_type` enum:custom\_field\_entity\_type | Renamed | Now a fixed value list (custom\_field\_entity\_type) (Value list) | | `name` varchar | `name` TEXT | Unchanged | | | `description` text | `description` TEXT | Unchanged | | * Swap the 6 renamed fields and point `FROM` at `custom_field_categories`. * Re-key 2 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `custom_field_categories` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was users_customsitefield. SELECT t.id, -- was id t.name, -- was name t.description, -- was description t.field_type, -- was custom_type t.display_order, -- was order t.is_active, -- was active t.created_at, -- was created_date t.updated_at, -- was updated_date t.group_id, -- was category_id t.entity_type -- was org_type FROM custom_field_categories t WHERE t.deleted_at IS NULL; ``` #### users\_customsitefieldanswer **users\_customsitefieldanswer** → `custom_field_entity_values` · *merged/split* · 6 confirmed fields (Renamed 2 · ID changed 4) Stores per-entity values for institution-configured custom fields. Supports text, number, date, and JSON (multiselect) value types. > A six-table custom-field engine covering users, sites and programs, with per-user-type and per-site-type applicability. Much richer than Medtrics Classic, and the values table is polymorphic (see orgs-F12). ![Where the 6 confirmed fields of users\_customsitefieldanswer went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FtEqRWv09yvPJdcEU1zNt%2Fflow-users-customsitefieldanswer.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | -------------------------- | ---------------------------------------------- | ---------- | --------------------------------------------------------- | | `id` integer | `id` UUID | ID changed | Integer link → UUID | | `field_id` integer | `category_id` UUID | ID changed | Integer link → UUID pointing to custom\_field\_categories | | `program_id` integer | `entity_id` UUID | ID changed | Integer link → UUID (Split) | | `program_id` integer | `entity_type` enum:custom\_field\_entity\_type | ID changed | Integer link → UUID (Split) | | `created_date` timestamptz | `created_at` TIMESTAMPTZ | Renamed | | | `updated_date` timestamptz | `updated_at` TIMESTAMPTZ | Renamed | | * Swap the 2 renamed fields and point `FROM` at `custom_field_entity_values`. * Re-key 4 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `custom_field_entity_values` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was users_customsitefieldanswer. SELECT t.id, -- was id t.created_at, -- was created_date t.updated_at, -- was updated_date t.category_id, -- was field_id t.entity_id, -- was program_id t.entity_type -- was program_id FROM custom_field_entity_values t WHERE t.deleted_at IS NULL; ``` #### users\_customsitefieldoption **users\_customsitefieldoption** → `custom_field_entity_values`, `custom_field_options` · *merged* · 8 confirmed fields (Renamed 6 · ID changed 2) Stores per-entity values for institution-configured custom fields. Supports text, number, date, and JSON (multiselect) value types. Split table: each field names its new table. > Medtrics Classic had two parallel custom-field stacks (one for people, one for sites and programs). Oracle unified them into one stack -- but housed it in the Organizations domain, not People. ![Where the 8 confirmed fields of users\_customsitefieldoption went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FyU7OBJ6HXgqt2VqGTQce%2Fflow-users-customsitefieldoption.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | -------------------------- | ----------------------------------------------- | ---------- | --------------------------------------------------------- | | `id` integer | custom\_field\_options.`id` UUID | ID changed | Integer link → UUID | | `field_id` integer | custom\_field\_options.`category_id` UUID | ID changed | Integer link → UUID pointing to custom\_field\_categories | | `text` text | custom\_field\_entity\_values.`value_text` TEXT | Renamed | (Split) | | `text` text | custom\_field\_options.`label` TEXT | Renamed | (Split) | | `text` text | custom\_field\_options.`value` TEXT | Renamed | (Split) | | `created_date` timestamptz | custom\_field\_options.`created_at` TIMESTAMPTZ | Renamed | | | `updated_date` timestamptz | custom\_field\_options.`updated_at` TIMESTAMPTZ | Renamed | | | `order` integer | custom\_field\_options.`display_order` INTEGER | Renamed | | * Swap the 6 renamed fields and point `FROM` at `custom_field_entity_values` (and the other new tables). * Re-key 2 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `custom_field_entity_values` keeps deleted rows. #### users\_group\_programs **users\_group\_programs** → `groups` · 1 confirmed field (ID changed 1) User groups supporting both institution-level and program-level scoping. | 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. * Add `WHERE deleted_at IS NULL`: `groups` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was users_group_programs. SELECT t.id -- was id FROM groups t WHERE t.deleted_at IS NULL; ``` #### users\_group\_sites **users\_group\_sites** → `group_sites` · 2 confirmed fields (ID changed 2) Site-scoping junction for groups (a group can be restricted to specific clinical sites). Backs Medtrics Classic users\_group\_sites migration. ![Where the 2 confirmed fields of users\_group\_sites went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2F110843hnc7tHc6Tq1zje%2Fflow-users-group-sites.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). | | `participatingsite_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. ```sql -- Starter query: confirmed fields only. Was users_group_sites. SELECT t.id, -- was id t.site_id -- was participatingsite_id FROM group_sites t; ``` #### users\_grouprole **users\_grouprole** → `group_leader_roles` · *split* · 4 confirmed fields (Unchanged 1 · Renamed 2 · ID changed 1) Customizable lookup table for group leader role types per program. > Medtrics Classic had one group table with many-to-many links to programs and sites. Oracle makes institution ownership mandatory and single, moves sites to a junction table, and separates 'member' from 'leader'. Medtrics Classic users.WorkType and GroupRole.multiplier (effort weighting) have no Oracle counterpart. ![Where the 4 confirmed fields of users\_grouprole went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FFlGCQqPsSK2KUmhTS42Y%2Fflow-users-grouprole.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). | | `name` text | `code` TEXT | Renamed | (Split) | | `active` boolean | `is_active` BOOLEAN | Renamed | | | `name` text | `name` TEXT | Unchanged | (Split) | * Swap the 2 renamed fields and point `FROM` at `group_leader_roles`. * Re-key 1 ID field. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `group_leader_roles` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was users_grouprole. SELECT t.id, -- was id t.code, -- was name t.name, -- was name t.is_active -- was active FROM group_leader_roles t WHERE t.deleted_at IS NULL; ``` #### users\_grouptag **users\_grouptag** → `group_tags` · *split* · 2 confirmed fields (Renamed 1 · ID changed 1) Tag/category system for organizing groups at institution level. > Medtrics Classic had one group table with many-to-many links to programs and sites. Oracle makes institution ownership mandatory and single, moves sites to a junction table, and separates 'member' from 'leader'. Medtrics Classic users.WorkType and GroupRole.multiplier (effort weighting) have no Oracle counterpart. ![Where the 2 confirmed fields of users\_grouptag went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FM3eiT85kED22gA8shSEa%2Fflow-users-grouptag.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). | | `active` boolean | `is_active` BOOLEAN | Renamed | | * Swap the 1 renamed field and point `FROM` at `group_tags`. * Re-key 1 ID field. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `group_tags` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was users_grouptag. SELECT t.id, -- was id t.is_active -- was active FROM group_tags t WHERE t.deleted_at IS NULL; ``` #### users\_personaldata **users\_personaldata** → `profiles`, `user_credentials`, `user_immigration_status` · *split* · 14 confirmed fields (Unchanged 8 · Renamed 6) Core user profile, linked to auth.users via id. Split table: each field names its new table. > The 60-column PersonalData blob is broken up: addresses and demographics go onto profiles, credentials to user\_credentials, visas to user\_immigration\_status, emergency contacts to their own dated table. ![Where the 14 confirmed fields of users\_personaldata went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FnrATGVv51DMe49WLPCf9%2Fflow-users-personaldata.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | ------------------------ | ---------------------------------------------------- | --------- | ----------------------------------------------------------- | | `address_1` varchar | profiles.`address_line_1` TEXT | Renamed | | | `address_2` varchar | profiles.`address_line_2` TEXT | Renamed | | | `zip_code` varchar | profiles.`postal_code` TEXT | Renamed | | | `birth_date` date | profiles.`date_of_birth` DATE | Renamed | | | `npi_number` varchar | profiles.`npi` TEXT | Renamed | | | `ecfmg_number` varchar | user\_credentials.`credential_number` TEXT | Renamed | (Split) | | `city` varchar | profiles.`city` TEXT | Unchanged | | | `state` varchar | profiles.`state` TEXT | Unchanged | | | `country` varchar | profiles.`country` TEXT | Unchanged | | | `birth_place` varchar | profiles.`birth_place` TEXT | Unchanged | | | `ethnicity` varchar | profiles.`ethnicity` TEXT | Unchanged | | | `gender` varchar | profiles.`gender` TEXT | Unchanged | | | `marital_status` varchar | profiles.`marital_status` enum:marital\_status\_type | Unchanged | Now a fixed value list (marital\_status\_type) (Value list) | | `ecfmg_number` varchar | user\_immigration\_status.`ecfmg_number` TEXT | Unchanged | (Split) | * Swap the 6 renamed fields and point `FROM` at `profiles` (and the other new tables). * Add `WHERE deleted_at IS NULL`: `profiles`, `user_credentials`, `user_immigration_status` keep deleted rows. #### users\_usercustomdocument **users\_usercustomdocument** → `user_custom_documents` · *replaced* · 8 confirmed fields (Unchanged 2 · Renamed 2 · ID changed 4) Generated document instances for learners (e.g., MSPE letters). > Per-user documents and PDF annotations move out of People into the Documents domain; the signature becomes a single URL column on profiles, losing Medtrics Classic's active flag and history of past signatures. ![Where the 8 confirmed fields of users\_usercustomdocument went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FtTQbiY0CAXlxSNUYWrLG%2Fflow-users-usercustomdocument.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | ------------------------------ | --------------------------- | ---------- | --------------------------------------------------- | | `academic_year_id` integer | `academic_year_id` UUID | ID changed | Integer link → UUID | | `created_by_id` integer | `created_by` UUID | ID changed | Integer link → UUID pointing to profiles | | `document_template_id` integer | `document_template_id` UUID | ID changed | Integer link → UUID pointing to document\_templates | | `user_id` integer | `learner_id` UUID | ID changed | Integer link → UUID pointing to profiles | | `created_date` timestamptz | `created_at` TIMESTAMPTZ | Renamed | | | `updated_date` timestamptz | `updated_at` TIMESTAMPTZ | Renamed | | | `id` uuid | `id` UUID | Unchanged | | | `content` text | `content` TEXT | Unchanged | | * Swap the 2 renamed fields and point `FROM` at `user_custom_documents`. * Re-key 4 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `user_custom_documents` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was users_usercustomdocument. SELECT t.id, -- was id t.created_at, -- was created_date t.content, -- was content t.academic_year_id, -- was academic_year_id t.created_by, -- was created_by_id t.document_template_id, -- was document_template_id t.learner_id, -- was user_id t.updated_at -- was updated_date FROM user_custom_documents t WHERE t.deleted_at IS NULL; ``` #### users\_usercustomdocumenthistory **users\_usercustomdocumenthistory** → `user_custom_document_history` · *replaced* · 6 confirmed fields (Unchanged 1 · Renamed 2 · ID changed 3) Audit trail for user document changes and actions. > Per-user documents and PDF annotations move out of People into the Documents domain; the signature becomes a single URL column on profiles, losing Medtrics Classic's active flag and history of past signatures. ![Where the 6 confirmed fields of users\_usercustomdocumenthistory went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2F7HwZwGGAIlIIzwmJl8Wg%2Fflow-users-usercustomdocumenthistory.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | -------------------------- | ------------------------ | ---------- | ------------------------------------------------------- | | `id` integer | `id` UUID | ID changed | Integer link → UUID | | `document_id` uuid | `document_id` UUID | ID changed | Integer link → UUID pointing to user\_custom\_documents | | `updated_by_id` integer | `created_by` UUID | ID changed | Integer link → UUID pointing to profiles | | `action` varchar | `action_details` JSONB | Renamed | text → json (Split, Type changed) | | `created_date` timestamptz | `created_at` TIMESTAMPTZ | Renamed | | | `action` varchar | `action` TEXT | Unchanged | (Split) | * Swap the 2 renamed fields and point `FROM` at `user_custom_document_history`. * Re-key 3 ID fields. Join people on `email`, not on old IDs. ```sql -- Starter query: confirmed fields only. Was users_usercustomdocumenthistory. SELECT t.id, -- was id t.action, -- was action t.action_details, -- was action t.document_id, -- was document_id t.created_by, -- was updated_by_id t.created_at -- was created_date FROM user_custom_document_history t; ``` #### users\_usercustomdocumentmergefield **users\_usercustomdocumentmergefield** → `user_custom_document_merge_field_values` · *replaced* · 7 confirmed fields (Unchanged 1 · Renamed 2 · ID changed 4) Resolved merge field values for user document instances. Stores both system field results and custom field answers. > Per-user documents and PDF annotations move out of People into the Documents domain; the signature becomes a single URL column on profiles, losing Medtrics Classic's active flag and history of past signatures. ![Where the 7 confirmed fields of users\_usercustomdocumentmergefield went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FabCX6TsETpOVeQCIuICb%2Fflow-users-usercustomdocumentmergefield.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | -------------------------- | ------------------------ | ---------- | ------------------------------------------------------- | | `id` integer | `id` UUID | ID changed | Integer link → UUID | | `created_by_id` integer | `created_by` UUID | ID changed | Integer link → UUID pointing to profiles | | `custom_document_id` uuid | `document_id` UUID | ID changed | Integer link → UUID pointing to user\_custom\_documents | | `merge_field_id` integer | `custom_field_id` UUID | ID changed | Integer link → UUID pointing to document\_merge\_fields | | `created_date` timestamptz | `created_at` TIMESTAMPTZ | Renamed | | | `updated_date` timestamptz | `updated_at` TIMESTAMPTZ | Renamed | | | `answer` text | `answer` TEXT | Unchanged | | * Swap the 2 renamed fields and point `FROM` at `user_custom_document_merge_field_values`. * Re-key 4 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `user_custom_document_merge_field_values` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was users_usercustomdocumentmergefield. SELECT t.id, -- was id t.answer, -- was answer t.created_at, -- was created_date t.updated_at, -- was updated_date t.created_by, -- was created_by_id t.document_id, -- was custom_document_id t.custom_field_id -- was merge_field_id FROM user_custom_document_merge_field_values t WHERE t.deleted_at IS NULL; ``` #### users\_usercustomdocumentstatus **users\_usercustomdocumentstatus** → `user_custom_document_statuses` · 2 confirmed fields (Unchanged 1 · ID changed 1) Status lookup for user document instances (e.g., draft, pending\_review, approved, sent). ![Where the 2 confirmed fields of users\_usercustomdocumentstatus went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FRYGwo6QKZWi0dcWuW79N%2Fflow-users-usercustomdocumentstatus.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | ---------------------- | ----------- | ---------- | ------------------- | | `id` integer | `id` UUID | ID changed | Integer link → UUID | | `name` varchar | `name` TEXT | Unchanged | | * Re-key 1 ID field. Join people on `email`, not on old IDs. ```sql -- Starter query: confirmed fields only. Was users_usercustomdocumentstatus. SELECT t.id, -- was id t.name -- was name FROM user_custom_document_statuses t; ``` #### users\_userethnicityoption **users\_userethnicityoption** → `custom_field_options` · 3 confirmed fields (Renamed 2 · ID changed 1) Selectable options for dropdown/multiselect custom fields. ![Where the 3 confirmed fields of users\_userethnicityoption went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FnUczLXFAeaOZvLBW0KKR%2Fflow-users-userethnicityoption.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | ---------------------- | ------------ | ---------- | ------------------- | | `id` integer | `id` UUID | ID changed | Integer link → UUID | | `name` varchar | `label` TEXT | Renamed | (Split) | | `name` varchar | `value` TEXT | Renamed | (Split) | * Swap the 2 renamed fields and point `FROM` at `custom_field_options`. * Re-key 1 ID field. Join people on `email`, not on old IDs. ```sql -- Starter query: confirmed fields only. Was users_userethnicityoption. SELECT t.id, -- was id t.label, -- was name t.value -- was name FROM custom_field_options t; ``` #### users\_userrole **users\_userrole** → `role_assignment_history` · *merged/replaced* · 10 confirmed fields (Unchanged 2 · Renamed 4 · ID changed 4) Historical ledger of a user's role assignments over time (tenure tracking). Read-mostly; populated by Medtrics Classic migration and role-change triggers going forward. > The Oracle table comment says so outright. This is the single biggest improvement in the area: which program and which role are now one dated, status-bearing row. ![Where the 10 confirmed fields of users\_userrole went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FGxUcVpM18rDKSe33jlja%2Fflow-users-userrole.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | ------------------------- | ------------------------ | ---------- | ---------------------------------------- | | `id` integer | `id` UUID | ID changed | Integer link → UUID | | `created_by_id` integer | `created_by` UUID | ID changed | Integer link → UUID pointing to profiles | | `role_id` integer | `role_id` UUID | ID changed | Integer link → UUID pointing to roles | | `user_profile_id` integer | `user_id` UUID | ID changed | Integer link → UUID pointing to profiles | | `active` boolean | `is_active` BOOLEAN | Renamed | | | `note` varchar | `source_note` TEXT | Renamed | | | `created` timestamptz | `created_at` TIMESTAMPTZ | Renamed | | | `modified` timestamptz | `updated_at` TIMESTAMPTZ | Renamed | | | `start_date` date | `start_date` DATE | Unchanged | | | `end_date` date | `end_date` DATE | Unchanged | | * Swap the 4 renamed fields and point `FROM` at `role_assignment_history`. * Re-key 4 ID fields. Join people on `email`, not on old IDs. ```sql -- Starter query: confirmed fields only. Was users_userrole. SELECT t.id, -- was id t.start_date, -- was start_date t.end_date, -- was end_date t.is_active, -- was active t.source_note, -- was note t.created_at, -- was created t.updated_at, -- was modified t.created_by, -- was created_by_id t.role_id, -- was role_id t.user_id -- was user_profile_id FROM role_assignment_history t; ``` #### users\_usersitepermission **users\_usersitepermission** → `delegated_admin_assignments`, `site_permissions` · *replaced* · 13 confirmed fields (Renamed 8 · ID changed 5) Scoped delegated administrator grants that reference existing permission codes or bundles. Split table: each field names its new table. > A complete rewrite. Medtrics Classic granted a named permission over a hand-picked set of sites, roles and groups. Oracle grants a permission code at a hierarchy level, with allow / deny / inherit, bundles, overrides with reasons, and a full audit log. ![Where the 13 confirmed fields of users\_usersitepermission went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2Fxls5APxFiYLo2H1LlB1m%2Fflow-users-usersitepermission.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | ----------------------------- | ------------------------------------------------------ | ---------- | ------------------------------------------------ | | `id` integer | delegated\_admin\_assignments.`id` UUID | ID changed | Integer link → UUID (Split) | | `program_id` integer | delegated\_admin\_assignments.`program_id` UUID | ID changed | Integer link → UUID pointing to programs (Split) | | `program_id` integer | delegated\_admin\_assignments.`scope_id` UUID | ID changed | Integer link → UUID (Split) | | `user_id` integer | delegated\_admin\_assignments.`user_id` UUID | ID changed | Integer link → UUID pointing to profiles (Split) | | `user_id` integer | site\_permissions.`user_id` UUID | ID changed | Integer link → UUID pointing to profiles (Split) | | `id` integer | delegated\_admin\_assignments.`metadata` JSONB | Renamed | int → json (Split, Merged, Type changed) | | `created_date` timestamptz | delegated\_admin\_assignments.`created_at` TIMESTAMPTZ | Renamed | (Split) | | `created_date` timestamptz | site\_permissions.`created_at` TIMESTAMPTZ | Renamed | (Split) | | `updated_date` timestamptz | delegated\_admin\_assignments.`updated_at` TIMESTAMPTZ | Renamed | (Split) | | `updated_date` timestamptz | site\_permissions.`updated_at` TIMESTAMPTZ | Renamed | (Split) | | `all_sites` boolean | delegated\_admin\_assignments.`metadata` JSONB | Renamed | bool → json (Merged, Type changed) | | `user_profile_access` boolean | delegated\_admin\_assignments.`metadata` JSONB | Renamed | bool → json (Merged, Type changed) | | `all_roles` boolean | delegated\_admin\_assignments.`metadata` JSONB | Renamed | bool → json (Merged, Type changed) | * Swap the 8 renamed fields and point `FROM` at `delegated_admin_assignments` (and the other new tables). * Re-key 5 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `delegated_admin_assignments`, `site_permissions` keep deleted rows. #### users\_usersitepermission\_forms **users\_usersitepermission\_forms** → `delegated_admin_assignments` · 4 confirmed fields (Renamed 1 · ID changed 3) Scoped delegated administrator grants that reference existing permission codes or bundles. ![Where the 4 confirmed fields of users\_usersitepermission\_forms went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FqjXIWnqYLspnKceEXEOy%2Fflow-users-usersitepermission-forms.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | ---------------------- | ---------------- | ---------- | ---------------------------------------- | | `id` integer | `id` UUID | ID changed | Integer link → UUID (Split) | | `form_id` integer | `metadata` JSONB | ID changed | Integer link → UUID (Split, Merged) | | `form_id` integer | `scope_id` UUID | ID changed | Integer link → UUID (Split) | | `id` integer | `metadata` JSONB | Renamed | int → json (Split, Merged, Type changed) | * Swap the 1 renamed field and point `FROM` at `delegated_admin_assignments`. * Re-key 3 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `delegated_admin_assignments` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was users_usersitepermission_forms. SELECT t.id, -- was id t.metadata, -- was id t.scope_id -- was form_id FROM delegated_admin_assignments t WHERE t.deleted_at IS NULL; ``` #### users\_usersitepermission\_sites **users\_usersitepermission\_sites** → `delegated_admin_assignments`, `site_permissions` · 5 confirmed fields (Renamed 1 · ID changed 4) Scoped delegated administrator grants that reference existing permission codes or bundles. Split table: each field names its new table. ![Where the 5 confirmed fields of users\_usersitepermission\_sites went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FCkPxGBa0KZ46rS439tUe%2Fflow-users-usersitepermission-sites.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | ------------------------------ | ---------------------------------------------- | ---------- | --------------------------------------------- | | `id` integer | delegated\_admin\_assignments.`id` UUID | ID changed | Integer link → UUID (Split) | | `id` integer | site\_permissions.`id` UUID | ID changed | Integer link → UUID (Split) | | `participatingsite_id` integer | delegated\_admin\_assignments.`metadata` JSONB | ID changed | Integer link → UUID (Split, Merged) | | `participatingsite_id` integer | site\_permissions.`site_id` UUID | ID changed | Integer link → UUID pointing to sites (Split) | | `id` integer | delegated\_admin\_assignments.`metadata` JSONB | Renamed | int → json (Split, Merged, Type changed) | * Swap the 1 renamed field and point `FROM` at `delegated_admin_assignments` (and the other new tables). * Re-key 4 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `delegated_admin_assignments`, `site_permissions` keep deleted rows. #### users\_usertypestatus **users\_usertypestatus** → `user_status_history` · *replaced* · 7 confirmed fields (Unchanged 3 · Renamed 2 · ID changed 2) Historical ledger of a user's account/eligibility status transitions over time. Populated by Medtrics Classic migration; not yet wired to a live status-change trigger. > Oracle has three history tables. Only membership\_history is written by a trigger; the two gap-closure ledgers were filled by the migration and nothing keeps them current. ![Where the 7 confirmed fields of users\_usertypestatus went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FvFHoNqFf0vQSLBXWYoRV%2Fflow-users-usertypestatus.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | ------------------------- | ------------------------ | ---------- | ---------------------------------------- | | `id` integer | `id` UUID | ID changed | Integer link → UUID | | `user_profile_id` integer | `user_id` UUID | ID changed | Integer link → UUID pointing to profiles | | `created` timestamptz | `created_at` TIMESTAMPTZ | Renamed | | | `notes` text | `source_note` TEXT | Renamed | | | `status` varchar | `status` TEXT | Unchanged | | | `start_date` date | `start_date` DATE | Unchanged | | | `end_date` date | `end_date` DATE | Unchanged | | * Swap the 2 renamed fields and point `FROM` at `user_status_history`. * Re-key 2 ID fields. Join people on `email`, not on old IDs. ```sql -- Starter query: confirmed fields only. Was users_usertypestatus. SELECT t.id, -- was id t.status, -- was status t.start_date, -- was start_date t.end_date, -- was end_date t.created_at, -- was created t.source_note, -- was notes t.user_id -- was user_profile_id FROM user_status_history t; ``` ### Trainees / Students 8 tables, 66 confirmed fields: Unchanged 15 · Renamed 34 · ID changed 17. #### users\_citizenship **users\_citizenship** → `user_credentials`, `user_immigration_status` · *renamed* · 13 confirmed fields (Unchanged 2 · Renamed 8 · ID changed 3) User certifications, licenses, and professional credentials with verification workflow. Split table: each field names its new table. > One row per person now (user\_id is the primary key), where Medtrics Classic allowed many Citizenship rows per profile. Variable visa data moved into a JSONB field. ![Where the 13 confirmed fields of users\_citizenship went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FrMmnZe8LVhoO3aslKZwV%2Fflow-users-citizenship.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | ----------------------------------- | -------------------------------------------------------------------------------------- | ---------- | --------------------------------------------------------------- | | `id` integer | user\_credentials.`id` UUID | ID changed | Integer link → UUID | | `user_profile_id` integer | user\_credentials.`user_id` UUID | ID changed | Integer link → UUID pointing to profiles (Split) | | `user_profile_id` integer | user\_immigration\_status.`user_id` UUID | ID changed | Integer link → UUID pointing to profiles (Split) | | `status` varchar | user\_immigration\_status.`status_type` enum:immigration\_status\_type | Renamed | Now a fixed value list (immigration\_status\_type) (Value list) | | `visa_types` ARRAY | user\_immigration\_status.`visa_metadata` JSONB | Renamed | array → json (Merged, Type changed) | | `visa_status` varchar | user\_immigration\_status.`visa_metadata` JSONB | Renamed | text → json (Merged, Type changed) | | `issued` date | user\_immigration\_status.`visa_issue_date` DATE | Renamed | | | `expires` date | user\_immigration\_status.`visa_expiration_date` DATE | Renamed | | | `ecfmg_certification_date` date | user\_credentials.`issue_date` DATE | Renamed | (Split) | | `comments` text | user\_immigration\_status.`notes` TEXT | Renamed | | | `work_authorization_status` varchar | user\_immigration\_status.`work_authorization_category` enum:work\_authorization\_type | Renamed | Now a fixed value list (work\_authorization\_type) (Value list) | | `visa_type` varchar | user\_immigration\_status.`visa_type` TEXT | Unchanged | | | `ecfmg_certification_date` date | user\_immigration\_status.`ecfmg_certification_date` DATE | Unchanged | (Split) | * Swap the 8 renamed fields and point `FROM` at `user_credentials` (and the other new tables). * Re-key 3 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `user_credentials`, `user_immigration_status` keep deleted rows. #### users\_examscore **users\_examscore** → `exam_scores` · *same* · 8 confirmed fields (Unchanged 2 · Renamed 2 · ID changed 4) Standardized exam scores (USMLE, COMLEX, MCAT) - separate from course/rotation grades. > Near one-to-one. Oracle scopes exam types to an institution and adds source tracking for imported scores. ![Where the 8 confirmed fields of users\_examscore went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FFOmUHrKaofPCXOrtRHWP%2Fflow-users-examscore.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | ------------------------- | ------------------------- | ---------- | ---------------------------------------------- | | `id` integer | `id` UUID | ID changed | Integer link → UUID (Split) | | `exam_status_id` integer | `exam_status_id` UUID | ID changed | Integer link → UUID pointing to exam\_statuses | | `exam_type_id` integer | `exam_type_id` UUID | ID changed | Integer link → UUID pointing to exam\_types | | `user_profile_id` integer | `learner_id` UUID | ID changed | Integer link → UUID pointing to profiles | | `id` integer | `source_reference` TEXT | Renamed | int → text (Split, Type changed) | | `datetime` date | `exam_date` DATE | Renamed | | | `score` numeric | `score` NUMERIC(10,2) | Unchanged | | | `percentile` numeric | `percentile` NUMERIC(5,2) | Unchanged | | * Swap the 2 renamed fields and point `FROM` at `exam_scores`. * Re-key 4 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `exam_scores` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was users_examscore. SELECT t.id, -- was id t.source_reference, -- was id t.exam_date, -- was datetime t.score, -- was score t.percentile, -- was percentile t.exam_status_id, -- was exam_status_id t.exam_type_id, -- was exam_type_id t.learner_id -- was user_profile_id FROM exam_scores t WHERE t.deleted_at IS NULL; ``` #### users\_examstatus **users\_examstatus** → `exam_statuses` · *same* · 2 confirmed fields (Unchanged 1 · ID changed 1) Status values for exam scores (Scheduled, Taken, Passed, Failed, Retake Scheduled). > Near one-to-one. Oracle scopes exam types to an institution and adds source tracking for imported scores. ![Where the 2 confirmed fields of users\_examstatus went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FpkZPB2SX2vN4PTH7i7om%2Fflow-users-examstatus.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | ---------------------- | ----------- | ---------- | ------------------- | | `id` integer | `id` UUID | ID changed | Integer link → UUID | | `name` varchar | `name` TEXT | Unchanged | | * Re-key 1 ID field. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `exam_statuses` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was users_examstatus. SELECT t.id, -- was id t.name -- was name FROM exam_statuses t WHERE t.deleted_at IS NULL; ``` #### users\_gmeprogramhistory **users\_gmeprogramhistory** → `gme_training_history`, `training_periods` · *merged* · 17 confirmed fields (Unchanged 1 · Renamed 12 · ID changed 4) GME program history with year-by-year training progression and ACGME compliance tracking. Split table: each field names its new table. > Five Medtrics Classic tables collapse into one row per prior program, with the year-by-year detail (PGY level, funding, gaps, external rotations) pushed into a training\_years JSONB column. ![Where the 17 confirmed fields of users\_gmeprogramhistory went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FXQZS2j2F9NrF3qK90TXw%2Fflow-users-gmeprogramhistory.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | ------------------------- | -------------------------------------------------------- | ---------- | ------------------------------------------------ | | `id` integer | gme\_training\_history.`id` UUID | ID changed | Integer link → UUID | | `site_id` integer | gme\_training\_history.`site_id` UUID | ID changed | Integer link → UUID pointing to sites | | `user_profile_id` integer | gme\_training\_history.`user_id` UUID | ID changed | Integer link → UUID pointing to profiles (Split) | | `user_profile_id` integer | training\_periods.`user_id` UUID | ID changed | Integer link → UUID pointing to profiles (Split) | | `gme_program` varchar | gme\_training\_history.`program_name` TEXT | Renamed | (Split) | | `gme_program` varchar | training\_periods.`period_name` TEXT | Renamed | (Split) | | `program_code` varchar | gme\_training\_history.`acgme_program_code` TEXT | Renamed | | | `is_primary` boolean | gme\_training\_history.`is_primary_program` BOOLEAN | Renamed | | | `institute` varchar | gme\_training\_history.`institution_name` TEXT | Renamed | | | `start_date` date | gme\_training\_history.`program_start_date` DATE | Renamed | | | `end_date` date | gme\_training\_history.`is_current` BOOLEAN | Renamed | date → bool (Split, Type changed) | | `end_date` date | gme\_training\_history.`program_end_date` DATE | Renamed | (Split) | | `city` varchar | gme\_training\_history.`institution_city` TEXT | Renamed | | | `state` varchar | gme\_training\_history.`institution_state` TEXT | Renamed | | | `irp` boolean | gme\_training\_history.`is_in_residency_program` BOOLEAN | Renamed | | | `year_of_funding` integer | gme\_training\_history.`total_funded_years` INTEGER | Renamed | | | `time_percentage` integer | gme\_training\_history.`time_percentage` INTEGER | Unchanged | | * Swap the 12 renamed fields and point `FROM` at `gme_training_history` (and the other new tables). * Re-key 4 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `gme_training_history`, `training_periods` keep deleted rows. #### users\_medicaleducation **users\_medicaleducation** → `medical_education_history` · *merged* · 10 confirmed fields (Unchanged 4 · Renamed 4 · ID changed 2) Medical school education history and credentials - supports MD, DO, MBBS, and international degrees. > Two education tables become one with a degree\_type enum. The US school lookup moved to the shared reference-data domain. ![Where the 10 confirmed fields of users\_medicaleducation went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FzvfeQTJnT2eW7dtGoHau%2Fflow-users-medicaleducation.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | ---------------------------------- | ------------------------------- | ---------- | --------------------------------------------------------- | | `id` integer | `id` UUID | ID changed | Integer link → UUID | | `user_profile_id` integer | `user_id` UUID | ID changed | Integer link → UUID pointing to profiles | | `school` varchar | `school_name` TEXT | Renamed | | | `country` varchar | `school_country` TEXT | Renamed | | | `degree` varchar | `degree_other` TEXT | Renamed | (Split) | | `degree` varchar | `degree_type` enum:degree\_type | Renamed | Now a fixed value list (degree\_type) (Split, Value list) | | `graduation_date` date | `graduation_date` DATE | Unchanged | | | `ecfmg_certificate_number` varchar | `ecfmg_certificate_number` TEXT | Unchanged | | | `ecfmg_certificate_date` date | `ecfmg_certificate_date` DATE | Unchanged | | | `notes` text | `notes` TEXT | Unchanged | | * Swap the 4 renamed fields and point `FROM` at `medical_education_history`. * Re-key 2 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `medical_education_history` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was users_medicaleducation. SELECT t.id, -- was id t.school_name, -- was school t.school_country, -- was country t.graduation_date, -- was graduation_date t.degree_other, -- was degree t.degree_type, -- was degree t.ecfmg_certificate_number, -- was ecfmg_certificate_number t.ecfmg_certificate_date, -- was ecfmg_certificate_date t.notes, -- was notes t.user_id -- was user_profile_id FROM medical_education_history t WHERE t.deleted_at IS NULL; ``` #### users\_programtraining **users\_programtraining** → `training_periods` · *merged* · 4 confirmed fields (Renamed 3 · ID changed 1) Training period records per user (PGY levels, fellowship years, rotations periods). > Five Medtrics Classic tables collapse into one row per prior program, with the year-by-year detail (PGY level, funding, gaps, external rotations) pushed into a training\_years JSONB column. ![Where the 4 confirmed fields of users\_programtraining went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2Fh1JGkhw6AEwoxcjJ0RxF%2Fflow-users-programtraining.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | -------------------------------------- | --------------------- | ---------- | ---------------------------------- | | `programtrainingdetail_ptr_id` integer | `id` UUID | ID changed | Integer link → UUID | | `years_completed` varchar | `training_level` TEXT | Renamed | | | `funding_year` varchar | `notes` TEXT | Renamed | (Merged) | | `is_displaced_resident` boolean | `notes` TEXT | Renamed | bool → text (Merged, Type changed) | * Swap the 3 renamed fields and point `FROM` at `training_periods`. * Re-key 1 ID field. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `training_periods` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was users_programtraining. SELECT t.id, -- was programtrainingdetail_ptr_id t.training_level, -- was years_completed t.notes -- was funding_year FROM training_periods t WHERE t.deleted_at IS NULL; ``` #### users\_programtrainingdetail **users\_programtrainingdetail** → `training_periods` · *merged* · 7 confirmed fields (Unchanged 2 · Renamed 4 · ID changed 1) Training period records per user (PGY levels, fellowship years, rotations periods). > Five Medtrics Classic tables collapse into one row per prior program, with the year-by-year detail (PGY level, funding, gaps, external rotations) pushed into a training\_years JSONB column. ![Where the 7 confirmed fields of users\_programtrainingdetail went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2F52Si4pqoBilzutzAKuV1%2Fflow-users-programtrainingdetail.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). | | `end_date` date | `is_current` BOOLEAN | Renamed | date → bool (Split, Type changed) | | `external` boolean | `notes` TEXT | Renamed | bool → text (Merged, Type changed) | | `external_location` text | `notes` TEXT | Renamed | (Merged) | | `comments` text | `notes` TEXT | Renamed | (Merged) | | `start_date` date | `start_date` DATE | Unchanged | | | `end_date` date | `end_date` DATE | Unchanged | (Split) | * Swap the 4 renamed fields and point `FROM` at `training_periods`. * Re-key 1 ID field. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `training_periods` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was users_programtrainingdetail. SELECT t.id, -- was id t.start_date, -- was start_date t.end_date, -- was end_date t.is_current, -- was end_date t.notes -- was external FROM training_periods t WHERE t.deleted_at IS NULL; ``` #### users\_usschool **users\_usschool** → `us_schools` · *merged/split* · 5 confirmed fields (Unchanged 3 · Renamed 1 · ID changed 1) Reference data for US medical schools. > Same split: a general school list plus the versioned CMS school-code list used for export. ![Where the 5 confirmed fields of users\_usschool went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FYGaw916pHoMkaor9e6nt%2Fflow-users-usschool.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | ---------------------- | ------------------- | ---------- | ------------------------- | | `id` integer | `id` UUID | ID changed | Integer link → UUID | | `code` integer | `abbreviation` TEXT | Renamed | int → text (Type changed) | | `name` varchar | `name` TEXT | Unchanged | | | `city` varchar | `city` TEXT | Unchanged | | | `state` varchar | `state` TEXT | Unchanged | | * Swap the 1 renamed field and point `FROM` at `us_schools`. * Re-key 1 ID field. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `us_schools` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was users_usschool. SELECT t.id, -- was id t.abbreviation, -- was code t.name, -- was name t.city, -- was city t.state -- was state FROM us_schools t WHERE t.deleted_at IS NULL; ``` ### Faculty / Educators 5 tables, 18 confirmed fields: Renamed 3 · ID changed 15. #### curriculum\_curriculumcourcerotation\_directors **curriculum\_curriculumcourcerotation\_directors** → `curriculum_course_directors` · 3 confirmed fields (ID changed 3) Course director assignments. ![Where the 3 confirmed fields of curriculum\_curriculumcourcerotation\_directors went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FcGca33j6FYSLvE6Euc1N%2Fflow-curriculum-curriculumcourcerotation-directors.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). | | `curriculumcourcerotation_id` integer | `course_id` UUID | ID changed | Integer link → UUID pointing to curriculum\_courses | | `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. * Add `WHERE deleted_at IS NULL`: `curriculum_course_directors` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was curriculum_curriculumcourcerotation_directors. SELECT t.id, -- was id t.course_id, -- was curriculumcourcerotation_id t.user_id -- was user_id FROM curriculum_course_directors t WHERE t.deleted_at IS NULL; ``` #### rotations\_rotationblockdirector **rotations\_rotationblockdirector** → `rotation_block_directors` · *split* · 5 confirmed fields (Renamed 2 · ID changed 3) Block-rotation director assignment container. > The Medtrics Classic many-to-many 'directors' field became a proper child table with role and is\_primary. An improvement. ![Where the 5 confirmed fields of rotations\_rotationblockdirector went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FkkgdOj6tetG26T0MDIwA%2Fflow-rotations-rotationblockdirector.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | -------------------------- | ------------------------------------ | ---------- | ------------------------------------------------------------- | | `id` integer | `id` UUID | ID changed | Integer link → UUID | | `block_id` integer | `block_id` UUID | ID changed | Integer link → UUID pointing to blocks | | `rotation_id` integer | `clinical_rotation_instance_id` UUID | ID changed | Integer link → UUID pointing to clinical\_rotation\_instances | | `created_date` timestamptz | `created_at` TIMESTAMPTZ | Renamed | | | `updated_date` timestamptz | `updated_at` TIMESTAMPTZ | Renamed | | * Swap the 2 renamed fields and point `FROM` at `rotation_block_directors`. * Re-key 3 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `rotation_block_directors` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was rotations_rotationblockdirector. SELECT t.id, -- was id t.created_at, -- was created_date t.updated_at, -- was updated_date t.block_id, -- was block_id t.clinical_rotation_instance_id -- was rotation_id FROM rotation_block_directors t WHERE t.deleted_at IS NULL; ``` #### rotations\_rotationblockdirector\_directors **rotations\_rotationblockdirector\_directors** → `rotation_block_director_users` · 3 confirmed fields (ID changed 3) Multiple directors per block-rotation (many-to-many). ![Where the 3 confirmed fields of rotations\_rotationblockdirector\_directors went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FnCkWFtxFSVbAGqxyTMoF%2Fflow-rotations-rotationblockdirector-directors.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). | | `rotationblockdirector_id` integer | `block_director_id` UUID | ID changed | Integer link → UUID pointing to rotation\_block\_directors | | `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_rotationblockdirector_directors. SELECT t.id, -- was id t.block_director_id, -- was rotationblockdirector_id t.user_id -- was user_id FROM rotation_block_director_users t; ``` #### rotations\_rotationsupervisor **rotations\_rotationsupervisor** → `rotation_supervisors` · *replaced* · 3 confirmed fields (ID changed 3) Supervisor/preceptor assignments at rotation, site, or block schedule level (many-to-many). > Medtrics Classic had one clean key (user, rotation). Oracle's version can attach to four different things and merged the old preceptor concept in. ![Where the 3 confirmed fields of rotations\_rotationsupervisor went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FJ44b0XrujgHZxuy7mLLI%2Fflow-rotations-rotationsupervisor.png?alt=media) | Medtrics Classic field | Oracle | Outcome | Note | | ---------------------- | ------------------------------------ | ---------- | ------------------------------------------------------------- | | `id` integer | `id` UUID | ID changed | Integer link → UUID | | `rotation_id` integer | `clinical_rotation_instance_id` UUID | ID changed | Integer link → UUID pointing to clinical\_rotation\_instances | | `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. * Add `WHERE deleted_at IS NULL`: `rotation_supervisors` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was rotations_rotationsupervisor. SELECT t.id, -- was id t.clinical_rotation_instance_id, -- was rotation_id t.user_id -- was user_id FROM rotation_supervisors t WHERE t.deleted_at IS NULL; ``` #### users\_procedurecredentialed **users\_procedurecredentialed** → `procedure_credentials` · *renamed* · 4 confirmed fields (Renamed 1 · ID changed 3) User credentials/privileges for case templates (procedure-specific). > Medtrics Classic stored a simple yes/no credential against a procedure setup; Oracle stores a named level, a grantor, an optional expiry and a program, and grants it automatically from a database trigger. ![Where the 4 confirmed fields of users\_procedurecredentialed went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2Fx2yBkdWO1Bfy2YsQPMQG%2Fflow-users-procedurecredentialed.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). | | `credentialed_procedure_id` integer | `template_id` UUID | ID changed | Integer link → UUID pointing to case\_templates | | `user_profile_id` integer | `user_id` UUID | ID changed | Integer link → UUID pointing to profiles | | `credentia_date` date | `granted_at` TIMESTAMPTZ | Renamed | date → timestamp (Type changed) | * Swap the 1 renamed field and point `FROM` at `procedure_credentials`. * Re-key 3 ID fields. Join people on `email`, not on old IDs. * Add `WHERE deleted_at IS NULL`: `procedure_credentials` keeps deleted rows. ```sql -- Starter query: confirmed fields only. Was users_procedurecredentialed. SELECT t.id, -- was id t.granted_at, -- was credentia_date t.template_id, -- was credentialed_procedure_id t.user_id -- was user_profile_id FROM procedure_credentials 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/people-and-roles.md?ask=&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.