Clean up the existing table data

User Story

As a user who wants keep my data in the system accurate and up to date I want to be able to see fields I understand in my data table So that I am clear about my data in the system

Business Context:

In #528 we exposed previously hidden user data through a table visualization. We now want to restructure and clean up this data so that users are not confused by what they see taken from the raw data tables. This will make it easier for any maintenance moving forward.

In Scope

CARD 1: changes on officers table

[ ] Rename the windows_username column in the officers table to employee_id and make this column into a required part of the primary key.
[ ] Replace all references to windows_username in the codebase with employee_id (Note: also search and replace windowsUsername with employeeId)
[ ] Drop the officer_number column in the officers table & remove any code references to it. Officer number is now a useless & misleading field.
[ ] Rename the supervisor_officer_number column in officers table to supervisor_employee_id and reroute code references to the field to the new name. (note: also search for supervisorOfficerNumber and replace with supervisorEmployeeId) (restrict changes to Officers Table, not Cases_Officers)
[ ] Update the columns for the officers seeders to reflect newly missing column and name changes after migration so that you can compose up again. Ensure all rows of dummy data have employeeIds.

CARD 2: changes on cases_officers table

[ ] Rename the supervisor_officer_number column in cases_officers to supervisor_employee_id and reroute code references to the field to the new name. (note: also search for supervisorOfficerNumber and replace with supervisorEmployeeId)
[ ] Drop the supervisor_windows_username column in the cases_officers table & replace any code references to it with supervisor_employee_id. Supervisor Windows Username currently just holds duplicate information. (note: also search for supervisorWindowsUsername and replace with supervisorEmployeeId)
[ ] Ensure that when officers are attached to cases the cases_officer created has a supervisor_employee_id (won't necessarily appear on the app yet), will likely need code changes.

CARD 3: fully update seeders

[ ] Update the dummy data for the officers seeders so that the supervisor_employee_id column actually points to the supervisor_employee_id, a script could be useful for this (see reroute-supervisor-officer-number.js)

Acceptance Criteria

There's no mention of windows_username in the codebase or in any database, including local, CI, staging, and prod. Instead, we use employee_id.

When I look at any officer in the officer's table, their supervisor_employee_id accurately points to the employee id of their supervisor. This is true in all environments.

When I look at any row in cases_officer, the supervisor_employee_id accurately points to the employee id of the corresponding supervisor. This is true in all environments.

There's no mention of officer_number in the codebase or in any database.

supervisor_officer_number has been removed and supervisor_employee_id is the new foreign key

Notes

Placeholder card; work is being done in new cards

Checklist

Kickoff

[ ] We have we indicated which organizations and user permissions require this
[ ] We have indicated whether we have any dependencies to other cards/work
[ ] We have indicated which environment this can be tested in
[ ] We have included a mockup if this is design related
[ ] We have included clear ACs to test it
[ ] We have estimated the story

Deskcheck

[ ] This work has been demoed in CI (or higher) and led by the dev pair who worked on this card
[ ] We have run through and passed all the ACs
[ ] Our documentation has been updated
[ ] Our onboarding checklist has been updated

PublicDataWorks / complaint-manager