# Leave Module Upgrade Notes

The leave tables now track the payroll user UUID and owning organisation alongside the existing descriptive fields. Deployments upgrading from the legacy `employeeName`/`employee_id` model should follow the steps below.

## 1. Apply schema changes

1. Deploy the updated schema definitions from `TAFDB.sql`/`TAFDB.pgsql`.
2. Execute the helper script `migrations/20240912_leave_uuid_backfill.sql` against each environment.
   * The script adds the new columns (`user_id`, `org_id`) if they are missing.
   * It attempts to backfill `leave_requests` by matching the stored `employeeName` against payroll `users.full_name`.
   * `leave_balances` are matched via the legacy `employees` table and then promoted to payroll users.

## 2. Review unmatched rows

The automated backfill skips rows that cannot be matched unambiguously. To complete the migration:

1. Run a query such as the following to identify remaining work:

   ```sql
   SELECT id, "employeeName", user_id, org_id
   FROM leave_requests
   WHERE user_id IS NULL;
   ```

   ```sql
   SELECT id, employee_id, user_id, org_id
   FROM leave_balances
   WHERE user_id IS NULL;
   ```

2. For each unmatched record, determine the correct payroll `user_id` (for example by email) and update both `user_id` and `org_id` manually.
3. Once all rows are populated, you may make the columns `NOT NULL` if that matches your data quality requirements.

## 3. Application behaviour changes

* New leave requests created by workflow automation write the requester’s payroll `user_id` and their latest organisation membership (`user_org_memberships.org_id`).
* Leave balance adjustments must now reference the UUID columns. Any custom scripts that previously updated `employee_id` should be revised to use `user_id` and, where applicable, `org_id`.

## 4. Rollback considerations

If you need to roll back the change:

1. Stop any services that create leave records.
2. Export the UUID columns for auditing.
3. Drop the `user_id`/`org_id` columns after confirming no dependent code remains.

> **Tip:** Keep a copy of the `migrations/20240912_leave_uuid_backfill.sql` script with your deployment runbook so environments stay aligned.