> 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/requirements-and-leave.md).

# Requirements and leave

Requirements & Documents, Time Off & Benefits, Scholarly Activity: 26 Medtrics Classic tables and their confirmed Oracle fields.

Requirements & Documents, Time Off & Benefits, Scholarly Activity — 26 Medtrics Classic tables with 210 confirmed fields (15% of the mapping): Unchanged 22 · Renamed 120 · ID changed 68. 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).

![Requirements and leave: 26 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%2F2DrwYwd0AvVWHagUBTL6%2Fmap-reqs.png?alt=media)

### Requirements & Documents

15 tables, 148 confirmed fields: Unchanged 17 · Renamed 86 · ID changed 45.

#### requirements\_requirement

**requirements\_requirement** → `requirement_definition_programs`, `requirement_definitions` · *split* · 20 confirmed fields (Unchanged 6 · Renamed 12 · ID changed 2)

Many-to-many relationship between requirements and programs. Allows same requirement to apply to multiple programs. Split table: each field names its new table.

> One Medtrics Classic row that held both the rule and the audience became a rule table plus several link tables. This is the central improvement of the rewrite: you can now change who owes a requirement without touching the requirement itself.

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

| Medtrics Classic field                       | Oracle                                                           | Outcome    | Note                                                             |
| -------------------------------------------- | ---------------------------------------------------------------- | ---------- | ---------------------------------------------------------------- |
| `id` integer                                 | requirement\_definition\_programs.`requirement_id` UUID          | ID changed | Integer link → UUID pointing to requirement\_definitions (Split) |
| `id` integer                                 | requirement\_definitions.`id` UUID                               | ID changed | Integer link → UUID (Split)                                      |
| `file` varchar                               | requirement\_definitions.`requires_compliance_document` BOOLEAN  | Renamed    | text → bool (Type changed)                                       |
| `active` boolean                             | requirement\_definitions.`allow_program_admins` BOOLEAN          | Renamed    |                                                                  |
| `auto_renew_days_before_expire_date` integer | requirement\_definitions.`auto_renew_days_before_expiry` INTEGER | Renamed    |                                                                  |
| `created_at` timestamptz                     | requirement\_definitions.`deadline_days` INTEGER                 | Renamed    | timestamp → int (Split, Merged, Type changed)                    |
| `created_at` timestamptz                     | requirement\_definitions.`expiry_days` INTEGER                   | Renamed    | timestamp → int (Split, Merged, Type changed)                    |
| `deadline` date                              | requirement\_definitions.`deadline_days` INTEGER                 | Renamed    | date → int (Split, Merged, Type changed)                         |
| `deadline` date                              | requirement\_definitions.`expiry_days` INTEGER                   | Renamed    | date → int (Split, Merged, Type changed)                         |
| `allow_pa` boolean                           | requirement\_definitions.`submission_expiry_required` BOOLEAN    | Renamed    |                                                                  |
| `future_assignment_date` date                | requirement\_definitions.`created_at` TIMESTAMPTZ                | Renamed    | date → timestamp (Type changed)                                  |
| `submission_expiry_compulsory` boolean       | requirement\_definitions.`expiry_enabled` BOOLEAN                | Renamed    | (Split)                                                          |
| `submission_expiry_compulsory` boolean       | requirement\_definitions.`updated_at` TIMESTAMPTZ                | Renamed    | bool → timestamp (Split, Type changed)                           |
| `only_admin_use` boolean                     | requirement\_definitions.`future_assignment_date` DATE           | Renamed    | bool → date (Type changed)                                       |
| `name` varchar                               | requirement\_definitions.`name` TEXT                             | Unchanged  |                                                                  |
| `description` text                           | requirement\_definitions.`description` TEXT                      | Unchanged  |                                                                  |
| `type` varchar                               | requirement\_definitions.`type` TEXT                             | Unchanged  |                                                                  |
| `roles` ARRAY                                | requirement\_definitions.`roles` TEXT\[]                         | Unchanged  |                                                                  |
| `auto_renew` boolean                         | requirement\_definitions.`auto_renew` BOOLEAN                    | Unchanged  |                                                                  |
| `email_notification` boolean                 | requirement\_definitions.`email_notification` BOOLEAN            | Unchanged  |                                                                  |

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

#### requirements\_requirement\_excluded\_users

**requirements\_requirement\_excluded\_users** → `requirement_assignment_exclusions` · 2 confirmed fields (ID changed 2)

Explicit exclusions from requirements. Prevents auto-assignment to specific users, sites, or groups.

![Where the 2 confirmed fields of requirements\_requirement\_excluded\_users went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FpMVbKse6Mtw1ZzZaJ66x%2Fflow-requirements-requirement-excluded-users.png?alt=media)

| Medtrics Classic field   | Oracle                | Outcome    | Note                                                     |
| ------------------------ | --------------------- | ---------- | -------------------------------------------------------- |
| `id` integer             | `id` UUID             | ID changed | Integer link → UUID                                      |
| `requirement_id` integer | `requirement_id` UUID | ID changed | Integer link → UUID pointing to requirement\_definitions |

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

```sql
-- Starter query: confirmed fields only. Was requirements_requirement_excluded_users.
SELECT
  t.id,             -- was id
  t.requirement_id  -- was requirement_id
FROM requirement_assignment_exclusions t
WHERE t.deleted_at IS NULL;
```

#### requirements\_requirement\_programs

**requirements\_requirement\_programs** → `requirement_definition_programs` · 2 confirmed fields (ID changed 2)

Many-to-many relationship between requirements and programs. Allows same requirement to apply to multiple programs.

![Where the 2 confirmed fields of requirements\_requirement\_programs went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FtNjEnC1r5NHMq1mt2O1w%2Fflow-requirements-requirement-programs.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). |
| `residencyprogram_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`: `requirement_definition_programs` keeps deleted rows.

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

#### requirements\_requirement\_tags

**requirements\_requirement\_tags** → `requirement_definition_tags` · 3 confirmed fields (ID changed 3)

Many-to-many relationship between requirements and tags for categorization.

![Where the 3 confirmed fields of requirements\_requirement\_tags went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FqBuKWiy7LApR8hG3qnkr%2Fflow-requirements-requirement-tags.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). |
| `requirement_id` integer    | `requirement_id` UUID | ID changed | Integer link → UUID pointing to requirement\_definitions                                                            |
| `requirementtag_id` integer | `tag_id` UUID         | ID changed | Integer link → UUID pointing to requirement\_tags                                                                   |

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

```sql
-- Starter query: confirmed fields only. Was requirements_requirement_tags.
SELECT
  t.id,             -- was id
  t.requirement_id, -- was requirement_id
  t.tag_id          -- was requirementtag_id
FROM requirement_definition_tags t
WHERE t.deleted_at IS NULL;
```

#### requirements\_requirementrequest

**requirements\_requirementrequest** → `requirement_assignments` · *renamed* · 12 confirmed fields (Renamed 9 · ID changed 3)

Assignment of requirements to users, sites, programs, or groups using polymorphic pattern.

> Same idea - one row per person per requirement - but Oracle widened it so the party can also be a site, program or group, and added an owning program, delegation, expiry and renewal links.

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

| Medtrics Classic field     | Oracle                           | Outcome    | Note                                                     |
| -------------------------- | -------------------------------- | ---------- | -------------------------------------------------------- |
| `id` integer               | `id` UUID                        | ID changed | Integer link → UUID                                      |
| `requirement_id` integer   | `requirement_id` UUID            | ID changed | Integer link → UUID pointing to requirement\_definitions |
| `user_id` integer          | `assignee_id` UUID               | ID changed | Integer link → UUID                                      |
| `state` varchar            | `assignment_metadata` JSONB      | Renamed    | text → json (Split, Merged, Type changed)                |
| `state` varchar            | `status` TEXT                    | Renamed    | (Split)                                                  |
| `expire_date` date         | `due_date` DATE                  | Renamed    | (Split)                                                  |
| `expire_date` date         | `expiry_date` DATE               | Renamed    | (Split)                                                  |
| `renewed_date` date        | `assignment_metadata` JSONB      | Renamed    | date → json (Merged, Type changed)                       |
| `created_date` timestamptz | `assigned_at` TIMESTAMPTZ        | Renamed    |                                                          |
| `updated_date` timestamptz | `completed_at` TIMESTAMPTZ       | Renamed    | (Split)                                                  |
| `updated_date` timestamptz | `last_status_change` TIMESTAMPTZ | Renamed    | (Split)                                                  |
| `updated_date` timestamptz | `updated_at` TIMESTAMPTZ         | Renamed    | (Split)                                                  |

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

```sql
-- Starter query: confirmed fields only. Was requirements_requirementrequest.
SELECT
  t.id,                  -- was id
  t.assignment_metadata, -- was state
  t.status,              -- was state
  t.due_date,            -- was expire_date
  t.expiry_date,         -- was expire_date
  t.assigned_at,         -- was created_date
  t.requirement_id,      -- was requirement_id
  t.assignee_id,         -- was user_id
  t.completed_at,        -- was updated_date
  t.last_status_change,  -- was updated_date
  t.updated_at           -- was updated_date
FROM requirement_assignments t
WHERE t.deleted_at IS NULL;
```

#### requirements\_requirementsubmission

**requirements\_requirementsubmission** → `requirement_submissions` · *same* · 14 confirmed fields (Renamed 7 · ID changed 7)

Document submissions for requirement assignments. Multiple submissions possible per assignment (e.g., resubmissions after rejection).

> Direct counterpart. Oracle renames the columns (state to status, comment to submitter\_comment, admin\_comment to reviewer\_comment, approver\_user to reviewer\_id) and makes assignment\_id NOT NULL, closing Medtrics Classic's orphan-submission hole.

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

| Medtrics Classic field           | Oracle                     | Outcome    | Note                                                     |
| -------------------------------- | -------------------------- | ---------- | -------------------------------------------------------- |
| `id` integer                     | `id` UUID                  | ID changed | Integer link → UUID                                      |
| `certification_id` varchar       | `certification_id` TEXT    | ID changed | Integer link → UUID                                      |
| `approver_user_id` integer       | `reviewer_id` UUID         | ID changed | Integer link → UUID pointing to profiles (Split)         |
| `approver_user_id` integer       | `updated_by` UUID          | ID changed | Integer link → UUID pointing to profiles (Split)         |
| `requirement_request_id` integer | `assignment_id` UUID       | ID changed | Integer link → UUID pointing to requirement\_assignments |
| `submitted_by_id` integer        | `created_by` UUID          | ID changed | Integer link → UUID pointing to profiles (Split)         |
| `submitted_by_id` integer        | `submitted_by` UUID        | ID changed | Integer link → UUID pointing to profiles (Split)         |
| `state` varchar                  | `status` TEXT              | Renamed    |                                                          |
| `expire_date` date               | `expiry_date` DATE         | Renamed    |                                                          |
| `comment` text                   | `submitter_comment` TEXT   | Renamed    |                                                          |
| `admin_comment` text             | `reviewer_comment` TEXT    | Renamed    |                                                          |
| `created_at` timestamptz         | `submitted_at` TIMESTAMPTZ | Renamed    |                                                          |
| `review_date` timestamptz        | `reviewed_at` TIMESTAMPTZ  | Renamed    |                                                          |
| `updated_date` timestamptz       | `updated_at` TIMESTAMPTZ   | Renamed    |                                                          |

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

```sql
-- Starter query: confirmed fields only. Was requirements_requirementsubmission.
SELECT
  t.id,                -- was id
  t.status,            -- was state
  t.expiry_date,       -- was expire_date
  t.certification_id,  -- was certification_id
  t.submitter_comment, -- was comment
  t.reviewer_comment,  -- was admin_comment
  t.submitted_at,      -- was created_at
  t.reviewed_at,       -- was review_date
  t.reviewer_id,       -- was approver_user_id
  t.updated_by,        -- was approver_user_id
  t.assignment_id,     -- was requirement_request_id
  t.created_by,        -- was submitted_by_id
  t.submitted_by,      -- was submitted_by_id
  t.updated_at         -- was updated_date
FROM requirement_submissions t
WHERE t.deleted_at IS NULL;
```

#### requirements\_requirementsubmissionfile

**requirements\_requirementsubmissionfile** → `requirement_submission_documents` · *replaced* · 3 confirmed fields (ID changed 3)

Links submissions to documents. Supports multiple documents per submission.

> Medtrics Classic could store a raw file on the row or point at a Document. Oracle always points at the shared documents table, so there is exactly one place a file can live.

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

| Medtrics Classic field  | Oracle               | Outcome    | Note                                                     |
| ----------------------- | -------------------- | ---------- | -------------------------------------------------------- |
| `id` integer            | `id` UUID            | ID changed | Integer link → UUID                                      |
| `document_id` integer   | `document_id` UUID   | ID changed | Integer link → UUID pointing to documents                |
| `submission_id` integer | `submission_id` UUID | ID changed | Integer link → UUID pointing to requirement\_submissions |

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

```sql
-- Starter query: confirmed fields only. Was requirements_requirementsubmissionfile.
SELECT
  t.id,            -- was id
  t.document_id,   -- was document_id
  t.submission_id  -- was submission_id
FROM requirement_submission_documents t
WHERE t.deleted_at IS NULL;
```

#### requirements\_requirementtag

**requirements\_requirementtag** → `requirement_tags` · *same* · 4 confirmed fields (Unchanged 3 · ID changed 1)

Tags for categorizing and organizing requirements within an institution.

> Tags gained an institution and a colour, and the tag name is now unique per institution rather than globally - a genuine multi-tenant fix.

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

| Medtrics Classic field   | Oracle                   | Outcome    | Note                |
| ------------------------ | ------------------------ | ---------- | ------------------- |
| `id` integer             | `id` UUID                | ID changed | Integer link → UUID |
| `name` varchar           | `name` TEXT              | Unchanged  |                     |
| `created_at` timestamptz | `created_at` TIMESTAMPTZ | Unchanged  |                     |
| `updated_at` timestamptz | `updated_at` TIMESTAMPTZ | Unchanged  |                     |

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

```sql
-- Starter query: confirmed fields only. Was requirements_requirementtag.
SELECT
  t.id,         -- was id
  t.name,       -- was name
  t.created_at, -- was created_at
  t.updated_at  -- was updated_at
FROM requirement_tags t
WHERE t.deleted_at IS NULL;
```

#### resources\_charttemplate

**resources\_charttemplate** → `chart_templates` · *replaced* · 36 confirmed fields (Unchanged 3 · Renamed 31 · ID changed 2)

Chart template definitions for MSPE, competency tracking, and exam comparisons.

> Charts left the files app for their own domain (27\_charts) and are now attached to letter templates by a bridge table with a named placeholder. Whether every Medtrics Classic chart type and layout option survived is not visible in this slice.

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

| Medtrics Classic field         | Oracle                          | Outcome    | Note                                              |
| ------------------------------ | ------------------------------- | ---------- | ------------------------------------------------- |
| `id` integer                   | `id` UUID                       | ID changed | Integer link → UUID (Split)                       |
| `program_id` integer           | `program_id` UUID               | ID changed | Integer link → UUID pointing to programs          |
| `id` integer                   | `data_source_config` JSONB      | Renamed    | int → json (Split, Merged, Type changed)          |
| `title` varchar                | `axis_config` JSONB             | Renamed    | text → json (Merged, Type changed)                |
| `subtitle` varchar             | `axis_config` JSONB             | Renamed    | text → json (Merged, Type changed)                |
| `y_type` integer               | `axis_config` JSONB             | Renamed    | int → json (Merged, Type changed)                 |
| `y_label` varchar              | `axis_config` JSONB             | Renamed    | text → json (Merged, Type changed)                |
| `width` varchar                | `display_config` JSONB          | Renamed    | text → json (Merged, Type changed)                |
| `height` varchar               | `display_config` JSONB          | Renamed    | text → json (Merged, Type changed)                |
| `trainee_color` varchar        | `display_config` JSONB          | Renamed    | text → json (Merged, Type changed)                |
| `group_color` varchar          | `display_config` JSONB          | Renamed    | text → json (Merged, Type changed)                |
| `last_generated` timestamptz   | `last_generated_at` TIMESTAMPTZ | Renamed    |                                                   |
| `logs` text                    | `generation_logs` TEXT          | Renamed    |                                                   |
| `active` boolean               | `is_active` BOOLEAN             | Renamed    |                                                   |
| `peer_color` varchar           | `display_config` JSONB          | Renamed    | text → json (Merged, Type changed)                |
| `x_custom_range` ARRAY         | `axis_config` JSONB             | Renamed    | array → json (Merged, Type changed)               |
| `x_range_increment` numeric    | `axis_config` JSONB             | Renamed    | number → json (Merged, Type changed)              |
| `x_range_max` numeric          | `axis_config` JSONB             | Renamed    | number → json (Merged, Type changed)              |
| `x_range_min` numeric          | `axis_config` JSONB             | Renamed    | number → json (Merged, Type changed)              |
| `created_date` timestamptz     | `created_at` TIMESTAMPTZ        | Renamed    |                                                   |
| `updated_date` timestamptz     | `updated_at` TIMESTAMPTZ        | Renamed    |                                                   |
| `has_final_grade` boolean      | `data_source_config` JSONB      | Renamed    | bool → json (Merged, Type changed)                |
| `y_max` double                 | `axis_config` JSONB             | Renamed    | number → json (Merged, Type changed)              |
| `y_min` double                 | `axis_config` JSONB             | Renamed    | number → json (Merged, Type changed)              |
| `show_trainee_arrow` boolean   | `display_config` JSONB          | Renamed    | bool → json (Merged, Type changed)                |
| `column_width` varchar         | `display_config` JSONB          | Renamed    | text → json (Merged, Type changed)                |
| `column_padding` varchar       | `display_config` JSONB          | Renamed    | text → json (Merged, Type changed)                |
| `data_label_font_size` varchar | `display_config` JSONB          | Renamed    | text → json (Merged, Type changed)                |
| `x_label_font_size` varchar    | `display_config` JSONB          | Renamed    | text → json (Merged, Type changed)                |
| `y_label_font_size` varchar    | `display_config` JSONB          | Renamed    | text → json (Merged, Type changed)                |
| `y_title_font_size` varchar    | `display_config` JSONB          | Renamed    | text → json (Merged, Type changed)                |
| `hide_data_label` boolean      | `display_config` JSONB          | Renamed    | bool → json (Merged, Type changed)                |
| `show_quartile_labels` boolean | `display_config` JSONB          | Renamed    | bool → json (Merged, Type changed)                |
| `chart_type` integer           | `chart_type` enum:chart\_type   | Unchanged  | Now a fixed value list (chart\_type) (Value list) |
| `name` varchar                 | `name` TEXT                     | Unchanged  |                                                   |
| `is_running` boolean           | `is_running` BOOLEAN            | Unchanged  |                                                   |

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

```sql
-- Starter query: confirmed fields only. Was resources_charttemplate.
SELECT
  t.data_source_config, -- was id
  t.id,                 -- was id
  t.chart_type,         -- was chart_type
  t.name,               -- was name
  t.axis_config,        -- was title
  t.display_config,     -- was width
  t.last_generated_at,  -- was last_generated
  t.generation_logs,    -- was logs
  t.is_active,          -- was active
  t.is_running,         -- was is_running
  t.program_id,         -- was program_id
  t.created_at,         -- was created_date
  t.updated_at          -- was updated_date
FROM chart_templates t
WHERE t.deleted_at IS NULL;
```

#### resources\_documentcategoryexpiration

**resources\_documentcategoryexpiration** → `document_category_expiration_alerts` · *split* · 3 confirmed fields (Renamed 1 · ID changed 2)

Expiration alert configuration per document category.

> The reminder-days array plus its user list became a config row plus a recipients table, with new notify\_owner / notify\_admin switches.

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

| Medtrics Classic field         | Oracle                         | Outcome    | Note                                                 |
| ------------------------------ | ------------------------------ | ---------- | ---------------------------------------------------- |
| `id` integer                   | `id` UUID                      | ID changed | Integer link → UUID                                  |
| `document_category_id` integer | `document_category_id` UUID    | ID changed | Integer link → UUID pointing to document\_categories |
| `expiration_days` ARRAY        | `alert_days_before` INTEGER\[] | Renamed    |                                                      |

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

```sql
-- Starter query: confirmed fields only. Was resources_documentcategoryexpiration.
SELECT
  t.id,                   -- was id
  t.alert_days_before,    -- was expiration_days
  t.document_category_id  -- was document_category_id
FROM document_category_expiration_alerts t;
```

#### resources\_documentcategoryexpiration\_users

**resources\_documentcategoryexpiration\_users** → `document_category_alert_recipients` · 3 confirmed fields (ID changed 3)

Custom recipients for document category expiration alerts.

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

| Medtrics Classic field                  | Oracle                     | Outcome    | Note                                                                   |
| --------------------------------------- | -------------------------- | ---------- | ---------------------------------------------------------------------- |
| `id` integer                            | `id` UUID                  | ID changed | Integer link → UUID                                                    |
| `documentcategoryexpiration_id` integer | `expiration_alert_id` UUID | ID changed | Integer link → UUID pointing to document\_category\_expiration\_alerts |
| `user_id` integer                       | `profile_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`: `document_category_alert_recipients` keeps deleted rows.

```sql
-- Starter query: confirmed fields only. Was resources_documentcategoryexpiration_users.
SELECT
  t.id,                  -- was id
  t.expiration_alert_id, -- was documentcategoryexpiration_id
  t.profile_id           -- was user_id
FROM document_category_alert_recipients t
WHERE t.deleted_at IS NULL;
```

#### resources\_documentnotification

**resources\_documentnotification** → `notifications` · *split* · 16 confirmed fields (Renamed 12 · ID changed 4)

Streamlined document notification model with improved design.

> The Medtrics Classic 'I emailed you this document' record kept its shape but the one-time login token was replaced by a hashed, single-use, 72-hour download token.

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

| Medtrics Classic field      | Oracle                                 | Outcome    | Note                                                                |
| --------------------------- | -------------------------------------- | ---------- | ------------------------------------------------------------------- |
| `id` integer                | `id` UUID                              | ID changed | Integer link → UUID (Split)                                         |
| `document_id` integer       | `data` JSONB                           | ID changed | Integer link → UUID (Merged)                                        |
| `recipient_id` integer      | `user_id` UUID                         | ID changed | Integer link → UUID pointing to profiles                            |
| `sent_by_id` integer        | `created_by` UUID                      | ID changed | Integer link → UUID pointing to profiles                            |
| `id` integer                | `metadata` JSONB                       | Renamed    | int → json (Split, Merged, Type changed)                            |
| `notification_type` varchar | `category` enum:notification\_category | Renamed    | Now a fixed value list (notification\_category) (Split, Value list) |
| `notification_type` varchar | `metadata` JSONB                       | Renamed    | text → json (Split, Merged, Type changed)                           |
| `status` varchar            | `metadata` JSONB                       | Renamed    | text → json (Merged, Type changed)                                  |
| `subject` varchar           | `title` TEXT                           | Renamed    |                                                                     |
| `message` text              | `body` TEXT                            | Renamed    |                                                                     |
| `sent_at` timestamptz       | `created_at` TIMESTAMPTZ               | Renamed    | (Split)                                                             |
| `sent_at` timestamptz       | `metadata` JSONB                       | Renamed    | timestamp → json (Split, Merged, Type changed)                      |
| `viewed_at` timestamptz     | `metadata` JSONB                       | Renamed    | timestamp → json (Merged, Type changed)                             |
| `downloaded_at` timestamptz | `metadata` JSONB                       | Renamed    | timestamp → json (Merged, Type changed)                             |
| `additional_data` jsonb     | `data` JSONB                           | Renamed    | (Merged)                                                            |
| `updated_date` timestamptz  | `updated_at` TIMESTAMPTZ               | Renamed    |                                                                     |

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

```sql
-- Starter query: confirmed fields only. Was resources_documentnotification.
SELECT
  t.id,         -- was id
  t.metadata,   -- was id
  t.category,   -- was notification_type
  t.title,      -- was subject
  t.body,       -- was message
  t.created_at, -- was sent_at
  t.data,       -- was additional_data
  t.updated_at, -- was updated_date
  t.user_id,    -- was recipient_id
  t.created_by  -- was sent_by_id
FROM notifications t
WHERE t.deleted_at IS NULL;
```

#### resources\_resource

**resources\_resource** → `resource_folders`, `resource_shares`, `resources` · *split* · 25 confirmed fields (Unchanged 5 · Renamed 14 · ID changed 6)

Hierarchical folder structure for organizing resources. Supports multi-level scoping (org/institution/program/rotation). Split table: each field names its new table.

> The one Medtrics Classic table that was folder, file and link at once became a folder tree plus a contents table. This is the main structural win of the rewrite.

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

| Medtrics Classic field              | Oracle                                        | Outcome    | Note                                                      |
| ----------------------------------- | --------------------------------------------- | ---------- | --------------------------------------------------------- |
| `id` integer                        | resource\_folders.`id` UUID                   | ID changed | Integer link → UUID (Split, Merged)                       |
| `id` integer                        | resources.`id` UUID                           | ID changed | Integer link → UUID (Split)                               |
| `parent_id` integer                 | resource\_folders.`parent_id` UUID            | ID changed | Integer link → UUID pointing to resource\_folders (Split) |
| `parent_id` integer                 | resources.`folder_id` UUID                    | ID changed | Integer link → UUID pointing to resource\_folders (Split) |
| `rotation_id` integer               | resource\_folders.`folder_type` TEXT          | ID changed | Integer link → UUID (Split)                               |
| `rotation_id` integer               | resource\_folders.`rotation_template_id` UUID | ID changed | Integer link → UUID pointing to rotations (Split)         |
| `id` integer                        | resources.`metadata` JSONB                    | Renamed    | int → json (Split, Merged, Type changed)                  |
| `file` varchar                      | resources.`file_path` TEXT                    | Renamed    | (Split)                                                   |
| `file` varchar                      | resources.`file_type` TEXT                    | Renamed    | (Split)                                                   |
| `file` varchar                      | resources.`mime_type` TEXT                    | Renamed    | (Split)                                                   |
| `hyperlink` varchar                 | resources.`resource_type` TEXT                | Renamed    | (Split)                                                   |
| `hyperlink` varchar                 | resources.`url` TEXT                          | Renamed    | (Split)                                                   |
| `datetime` timestamptz              | resource\_folders.`created_at` TIMESTAMPTZ    | Renamed    | (Split)                                                   |
| `datetime` timestamptz              | resource\_shares.`shared_at` TIMESTAMPTZ      | Renamed    | (Split)                                                   |
| `datetime` timestamptz              | resources.`created_at` TIMESTAMPTZ            | Renamed    | (Split)                                                   |
| `last_update` timestamptz           | resource\_folders.`updated_at` TIMESTAMPTZ    | Renamed    | (Split)                                                   |
| `last_update` timestamptz           | resources.`updated_at` TIMESTAMPTZ            | Renamed    | (Split)                                                   |
| `path` varchar                      | resources.`metadata` JSONB                    | Renamed    | text → json (Split, Merged, Type changed)                 |
| `r_type` varchar                    | resources.`metadata` JSONB                    | Renamed    | text → json (Merged, Type changed)                        |
| `individuals_email_enabled` boolean | resource\_folders.`notify_on_upload` BOOLEAN  | Renamed    |                                                           |
| `name` varchar                      | resource\_folders.`name` TEXT                 | Unchanged  | (Split)                                                   |
| `name` varchar                      | resources.`name` TEXT                         | Unchanged  | (Split)                                                   |
| `description` text                  | resource\_folders.`description` TEXT          | Unchanged  | (Split)                                                   |
| `description` text                  | resources.`description` TEXT                  | Unchanged  | (Split)                                                   |
| `path` varchar                      | resource\_folders.`path` TEXT                 | Unchanged  | (Split)                                                   |

* Swap the 14 renamed fields and point `FROM` at `resource_folders` (and the other new tables).
* Re-key 6 ID fields. Join people on `email`, not on old IDs.
* Add `WHERE deleted_at IS NULL`: `resource_folders`, `resource_shares`, `resources` keep deleted rows.

#### resources\_resource\_admin\_users

**resources\_resource\_admin\_users** → `resource_shares` · 2 confirmed fields (ID changed 2)

Advanced sharing system for resources. Supports sharing with individual users, roles, or groups with granular permissions.

![Where the 2 confirmed fields of resources\_resource\_admin\_users went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FveiwX2GYRjdWHQpxuFha%2Fflow-resources-resource-admin-users.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). |
| `resource_id` integer  | `resource_id` UUID | ID changed | Integer link → UUID pointing to resources                                                                           |

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

```sql
-- Starter query: confirmed fields only. Was resources_resource_admin_users.
SELECT
  t.id,          -- was id
  t.resource_id  -- was resource_id
FROM resource_shares t
WHERE t.deleted_at IS NULL;
```

#### resources\_resource\_programs

**resources\_resource\_programs** → `document_programs` · 3 confirmed fields (ID changed 3)

Program-sharing junction for documents (Medtrics Classic resources\_resource.is\_share/is\_institutional program scoping). Backs Medtrics Classic resources\_resource\_programs migration.

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

| Medtrics Classic field        | Oracle             | Outcome    | Note                                      |
| ----------------------------- | ------------------ | ---------- | ----------------------------------------- |
| `id` integer                  | `id` UUID          | ID changed | Integer link → UUID                       |
| `resource_id` integer         | `document_id` UUID | ID changed | Integer link → UUID pointing to documents |
| `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.

```sql
-- Starter query: confirmed fields only. Was resources_resource_programs.
SELECT
  t.id,          -- was id
  t.document_id, -- was resource_id
  t.program_id   -- was residencyprogram_id
FROM document_programs t;
```

### Time Off & Benefits

8 tables, 39 confirmed fields: Unchanged 4 · Renamed 20 · ID changed 15.

#### budgets\_account

**budgets\_account** → `benefit_accounts` · *replaced* · 2 confirmed fields (Renamed 1 · ID changed 1)

User balance tracking per benefit category per program.

> Medtrics Classic accounts were shareable pots that could nest under a parent and be linked to many users, programs and roles. Oracle accounts are strictly one person, one category, one program, optionally one academic period. Simpler and much easier to query, but the nesting and group-pot capability is gone.

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

| Medtrics Classic field     | Oracle                   | Outcome    | Note                |
| -------------------------- | ------------------------ | ---------- | ------------------- |
| `id` integer               | `id` UUID                | ID changed | Integer link → UUID |
| `created_date` timestamptz | `created_at` TIMESTAMPTZ | Renamed    |                     |

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

```sql
-- Starter query: confirmed fields only. Was budgets_account.
SELECT
  t.id,         -- was id
  t.created_at  -- was created_date
FROM benefit_accounts t
WHERE t.deleted_at IS NULL;
```

#### budgets\_accountrequest

**budgets\_accountrequest** → `benefit_requests` · *renamed* · 18 confirmed fields (Unchanged 2 · Renamed 10 · ID changed 6)

User-initiated benefit requests with approval workflow.

> Both are the 'I am asking for something' row with draft, submit, approve, reject, cancel timestamps. Oracle adds time-off specifics (start\_time, end\_time, is\_partial\_day, block\_schedule\_id) and expense specifics (expense\_date, vendor).

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

| Medtrics Classic field       | Oracle                     | Outcome    | Note                                                |
| ---------------------------- | -------------------------- | ---------- | --------------------------------------------------- |
| `id` integer                 | `id` UUID                  | ID changed | Integer link → UUID                                 |
| `account_id` integer         | `account_id` UUID          | ID changed | Integer link → UUID pointing to benefit\_accounts   |
| `category_id` integer        | `category_id` UUID         | ID changed | Integer link → UUID pointing to benefit\_categories |
| `created_by_id` integer      | `created_by` UUID          | ID changed | Integer link → UUID pointing to profiles            |
| `submitted_by_id` integer    | `submitted_by` UUID        | ID changed | Integer link → UUID pointing to profiles            |
| `updated_by_id` integer      | `updated_by` UUID          | ID changed | Integer link → UUID pointing to profiles            |
| `description` text           | `rejection_reason` TEXT    | Renamed    | (Split)                                             |
| `debit` boolean              | `request_type` TEXT        | Renamed    | bool → text (Type changed)                          |
| `specific_date` timestamptz  | `expense_date` DATE        | Renamed    | timestamp → date (Split, Type changed)              |
| `specific_date` timestamptz  | `start_date` DATE          | Renamed    | timestamp → date (Split, Type changed)              |
| `value` integer              | `amount` INTEGER           | Renamed    |                                                     |
| `created_date` timestamptz   | `created_at` TIMESTAMPTZ   | Renamed    |                                                     |
| `updated_date` timestamptz   | `updated_at` TIMESTAMPTZ   | Renamed    |                                                     |
| `submitted_date` timestamptz | `submitted_at` TIMESTAMPTZ | Renamed    |                                                     |
| `approved_date` timestamptz  | `approved_at` TIMESTAMPTZ  | Renamed    |                                                     |
| `rejected_date` timestamptz  | `rejected_at` TIMESTAMPTZ  | Renamed    |                                                     |
| `description` text           | `description` TEXT         | Unchanged  | (Split)                                             |
| `end_date` timestamptz       | `end_date` DATE            | Unchanged  | timestamp → date (Type changed)                     |

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

```sql
-- Starter query: confirmed fields only. Was budgets_accountrequest.
SELECT
  t.id,               -- was id
  t.description,      -- was description
  t.rejection_reason, -- was description
  t.request_type,     -- was debit
  t.expense_date,     -- was specific_date
  t.start_date,       -- was specific_date
  t.amount,           -- was value
  t.end_date,         -- was end_date
  t.created_at,       -- was created_date
  t.updated_at,       -- was updated_date
  t.submitted_at,     -- was submitted_date
  t.approved_at,      -- was approved_date
  t.rejected_at,      -- was rejected_date
  t.account_id        -- was account_id
FROM benefit_requests t
WHERE t.deleted_at IS NULL;
```

#### budgets\_accountrequestresponse

**budgets\_accountrequestresponse** → `benefit_request_responses` · *renamed* · 4 confirmed fields (Renamed 1 · ID changed 3)

Approval/rejection decisions on benefit requests.

> One row per approver decision. Oracle adds step\_order so a decision can be tied to a stage of the approval chain.

![Where the 4 confirmed fields of budgets\_accountrequestresponse went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2Fgtu2dalLZ8KQC8pKzRCv%2Fflow-budgets-accountrequestresponse.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          |
| `request_id` integer       | `request_id` UUID        | ID changed | Integer link → UUID pointing to benefit\_requests |
| `created_date` timestamptz | `created_at` TIMESTAMPTZ | Renamed    |                                                   |

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

```sql
-- Starter query: confirmed fields only. Was budgets_accountrequestresponse.
SELECT
  t.id,         -- was id
  t.created_at, -- was created_date
  t.created_by, -- was created_by_id
  t.request_id  -- was request_id
FROM benefit_request_responses t;
```

#### budgets\_accountrule

**budgets\_accountrule** → `benefit_allocation_rules` · *split* · 4 confirmed fields (Renamed 3 · ID changed 1)

Automated benefit allocation configuration.

> The Medtrics Classic recurring-transaction engine became a configuration table plus an execution-log table. The execution log has a unique key on (rule\_id, account\_id, execution\_date) (14\_benefits/02\_tables.sql:379), which finally prevents the same top-up running twice.

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

| Medtrics Classic field     | Oracle                   | Outcome    | Note                             |
| -------------------------- | ------------------------ | ---------- | -------------------------------- |
| `id` integer               | `id` UUID                | ID changed | Integer link → UUID (Split)      |
| `id` integer               | `name` TEXT              | Renamed    | int → text (Split, Type changed) |
| `active` boolean           | `is_active` BOOLEAN      | Renamed    |                                  |
| `created_date` timestamptz | `created_at` TIMESTAMPTZ | Renamed    |                                  |

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

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

#### budgets\_accountrule\_programs

**budgets\_accountrule\_programs** → `benefit_allocation_rules` · 2 confirmed fields (ID changed 2)

Automated benefit allocation configuration.

![Where the 2 confirmed fields of budgets\_accountrule\_programs went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FdYczthrJIxfASDlxYUjN%2Fflow-budgets-accountrule-programs.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). |
| `residencyprogram_id` integer | `id` UUID | ID changed | Integer link → UUID                                                                                                 |

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

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

#### budgets\_accounttransaction

**budgets\_accounttransaction** → `benefit_transactions` · *renamed* · 1 confirmed field (ID changed 1)

Immutable ledger of all balance changes - NO UPDATE/DELETE allowed.

> Same idea, stronger guarantees: Oracle forbids edits and deletes with a trigger and records the resulting running\_balance on each row. Medtrics Classic allowed edits and used a debit boolean instead of signed amounts.

| Medtrics Classic field | Oracle                         | Outcome    | Note                                                  |
| ---------------------- | ------------------------------ | ---------- | ----------------------------------------------------- |
| `id` integer           | `reversed_transaction_id` UUID | ID changed | Integer link → UUID pointing to benefit\_transactions |

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

```sql
-- Starter query: confirmed fields only. Was budgets_accounttransaction.
SELECT
  t.reversed_transaction_id  -- was id
FROM benefit_transactions t;
```

#### budgets\_budgetcategory

**budgets\_budgetcategory** → `benefit_categories` · *merged* · 6 confirmed fields (Unchanged 2 · Renamed 3 · ID changed 1)

Institution-level benefit type definitions (PTO, sick leave, stipends, etc.).

> Two Medtrics Classic tables (the category and its unit of measure) became one table with unit\_type, unit\_label and unit\_conversion columns. Oracle also folded a lot of behaviour that used to live in code into columns on this table.

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

| Medtrics Classic field | Oracle                   | Outcome    | Note                        |
| ---------------------- | ------------------------ | ---------- | --------------------------- |
| `id` integer           | `id` UUID                | ID changed | Integer link → UUID (Split) |
| `id` integer           | `display_order` INTEGER  | Renamed    | (Split)                     |
| `active` boolean       | `is_active` BOOLEAN      | Renamed    |                             |
| `skip_iris` boolean    | `skip_reporting` BOOLEAN | Renamed    |                             |
| `name` text            | `name` TEXT              | Unchanged  |                             |
| `description` text     | `description` TEXT       | Unchanged  |                             |

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

```sql
-- Starter query: confirmed fields only. Was budgets_budgetcategory.
SELECT
  t.display_order,  -- was id
  t.id,             -- was id
  t.name,           -- was name
  t.description,    -- was description
  t.is_active,      -- was active
  t.skip_reporting  -- was skip_iris
FROM benefit_categories t
WHERE t.deleted_at IS NULL;
```

#### budgets\_status

**budgets\_status** → `benefit_request_responses`, `benefit_requests` · *dropped* · 2 confirmed fields (Renamed 2)

Approval/rejection decisions on benefit requests. Split table: each field names its new table.

> Free-text status rows were replaced by fixed database enums (14\_benefits/01\_enums.sql:22-29). This is an improvement: statuses can no longer be typoed or invented at runtime.

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

| Medtrics Classic field | Oracle                                                   | Outcome | Note                                                                  |
| ---------------------- | -------------------------------------------------------- | ------- | --------------------------------------------------------------------- |
| `name` varchar         | benefit\_request\_responses.`response_type` TEXT         | Renamed | (Split)                                                               |
| `name` varchar         | benefit\_requests.`status` enum:benefit\_request\_status | Renamed | Now a fixed value list (benefit\_request\_status) (Split, Value list) |

* Swap the 2 renamed fields and point `FROM` at `benefit_request_responses` (and the other new tables).
* Add `WHERE deleted_at IS NULL`: `benefit_requests` keeps deleted rows.

### Scholarly Activity

3 tables, 23 confirmed fields: Unchanged 1 · Renamed 14 · ID changed 8.

#### scholarly\_activity\_author

**scholarly\_activity\_author** → `scholarly_activity_authors` · *replaced* · 4 confirmed fields (Renamed 2 · ID changed 2)

Authors of scholarly activities with approval workflow - supports internal users and external collaborators.

> A two-column name list becomes a real membership table with links to platform users, ordering, roles and an approval step. This is the single biggest functional gain in the area.

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

| Medtrics Classic field          | Oracle                     | Outcome    | Note                                                  |
| ------------------------------- | -------------------------- | ---------- | ----------------------------------------------------- |
| `id` integer                    | `id` UUID                  | ID changed | Integer link → UUID                                   |
| `scholarly_activity_id` integer | `activity_id` UUID         | ID changed | Integer link → UUID pointing to scholarly\_activities |
| `first_name` varchar            | `external_first_name` TEXT | Renamed    |                                                       |
| `last_name` varchar             | `external_last_name` TEXT  | Renamed    |                                                       |

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

```sql
-- Starter query: confirmed fields only. Was scholarly_activity_author.
SELECT
  t.id,                  -- was id
  t.external_first_name, -- was first_name
  t.external_last_name,  -- was last_name
  t.activity_id          -- was scholarly_activity_id
FROM scholarly_activity_authors t
WHERE t.deleted_at IS NULL;
```

#### scholarly\_activity\_scholarlyactivity

**scholarly\_activity\_scholarlyactivity** → `scholarly_activities`, `scholarly_activity_attachments` · *merged* · 15 confirmed fields (Unchanged 1 · Renamed 9 · ID changed 5)

Tracks scholarly activities including publications, presentations, grants, and research for trainees and faculty with external integration support. Split table: each field names its new table.

> Ten Medtrics Classic tables become one table whose activity\_type column says which kind it is. Reporting gets much simpler, but the fields that were unique to each Medtrics Classic subtype no longer all have a home.

![Where the 15 confirmed fields of scholarly\_activity\_scholarlyactivity went](https://2912658775-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxpbJErq8GWWHPPkzmpv3%2Fuploads%2FQqDH5V6khvtUNiUKJ6Sf%2Fflow-scholarly-activity-scholarlyactivity.png?alt=media)

| Medtrics Classic field    | Oracle                                                    | Outcome    | Note                                                          |
| ------------------------- | --------------------------------------------------------- | ---------- | ------------------------------------------------------------- |
| `id` integer              | scholarly\_activities.`id` UUID                           | ID changed | Integer link → UUID (Split)                                   |
| `id` integer              | scholarly\_activity\_attachments.`activity_id` UUID       | ID changed | Integer link → UUID pointing to scholarly\_activities (Split) |
| `id` integer              | scholarly\_activity\_attachments.`id` UUID                | ID changed | Integer link → UUID (Split)                                   |
| `user_id` integer         | scholarly\_activities.`created_by` UUID                   | ID changed | Integer link → UUID pointing to profiles (Split)              |
| `user_id` integer         | scholarly\_activity\_attachments.`uploaded_by` UUID       | ID changed | Integer link → UUID pointing to profiles (Split)              |
| `last_update` timestamptz | scholarly\_activities.`created_at` TIMESTAMPTZ            | Renamed    | (Split)                                                       |
| `last_update` timestamptz | scholarly\_activities.`updated_at` TIMESTAMPTZ            | Renamed    | (Split)                                                       |
| `last_update` timestamptz | scholarly\_activity\_attachments.`created_at` TIMESTAMPTZ | Renamed    | (Split)                                                       |
| `last_update` timestamptz | scholarly\_activity\_attachments.`updated_at` TIMESTAMPTZ | Renamed    | (Split)                                                       |
| `activity_file` varchar   | scholarly\_activity\_attachments.`file_name` TEXT         | Renamed    | (Split)                                                       |
| `activity_file` varchar   | scholarly\_activity\_attachments.`file_type` TEXT         | Renamed    | (Split)                                                       |
| `activity_file` varchar   | scholarly\_activity\_attachments.`file_url` TEXT          | Renamed    | (Split)                                                       |
| `additional_info` text    | scholarly\_activities.`notes` TEXT                        | Renamed    |                                                               |
| `role` varchar            | scholarly\_activities.`impact_description` TEXT           | Renamed    |                                                               |
| `title` varchar           | scholarly\_activities.`title` TEXT                        | Unchanged  |                                                               |

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

#### scholarly\_activity\_teachingformalcourses

**scholarly\_activity\_teachingformalcourses** → `scholarly_activities` · *merged* · 4 confirmed fields (Renamed 3 · ID changed 1)

Tracks scholarly activities including publications, presentations, grants, and research for trainees and faculty with external integration support.

> Ten Medtrics Classic tables become one table whose activity\_type column says which kind it is. Reporting gets much simpler, but the fields that were unique to each Medtrics Classic subtype no longer all have a home.

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

| Medtrics Classic field             | Oracle                  | Outcome    | Note                                      |
| ---------------------------------- | ----------------------- | ---------- | ----------------------------------------- |
| `scholarlyactivity_ptr_id` integer | `id` UUID               | ID changed | Integer link → UUID                       |
| `start_date` date                  | `custom_metadata` JSONB | Renamed    | date → json (Split, Merged, Type changed) |
| `start_date` date                  | `publication_date` DATE | Renamed    | (Split)                                   |
| `end_date` date                    | `custom_metadata` JSONB | Renamed    | date → json (Merged, Type changed)        |

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

```sql
-- Starter query: confirmed fields only. Was scholarly_activity_teachingformalcourses.
SELECT
  t.id,               -- was scholarlyactivity_ptr_id
  t.custom_metadata,  -- was start_date
  t.publication_date  -- was start_date
FROM scholarly_activities 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/requirements-and-leave.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.