229 lines
6.5 KiB
Markdown
229 lines
6.5 KiB
Markdown
# Identity / Employee Separation Review
|
|
|
|
## 1. Summary
|
|
|
|
This change starts the separation between login identity (`users`) and employee master data (`employees`) without breaking the existing Auth.js credential login flow.
|
|
|
|
The implementation is intentionally staged:
|
|
|
|
- keep `users` as the current authentication source
|
|
- introduce additive fields needed for identity separation
|
|
- start linking `users.employeeId -> employees.id`
|
|
- backfill `training_records.employee_id`
|
|
- backfill `employee_training_targets.employee_id`
|
|
- change employee import to update `employees` first, then link users
|
|
|
|
## 2. Architecture Changes
|
|
|
|
### Users
|
|
|
|
`users` remains the authentication identity table for now, but now also stores identity-specific metadata:
|
|
|
|
- `provider`
|
|
- `providerUserId`
|
|
- `employeeId`
|
|
- `lastLoginAt`
|
|
|
|
### Employees
|
|
|
|
`employees` becomes the HR master source and now supports richer profile fields:
|
|
|
|
- `prefix`
|
|
- `firstNameTh`
|
|
- `lastNameTh`
|
|
- `firstNameEn`
|
|
- `lastNameEn`
|
|
- `displayName`
|
|
- `phone`
|
|
- `companyName`
|
|
- `status`
|
|
|
|
### Linking
|
|
|
|
User-to-employee linking now uses `employeeCode` within the active organization.
|
|
|
|
New helper:
|
|
|
|
- `src/features/employees/server/employee-identity-linking.ts`
|
|
|
|
This helper:
|
|
|
|
- upserts employee master rows from import
|
|
- links `users.employeeId`
|
|
- backfills `training_records.employee_id`
|
|
- backfills `employee_training_targets.employee_id`
|
|
|
|
## 3. Database Changes
|
|
|
|
Migration file:
|
|
|
|
- `drizzle/0009_lean_hobgoblin.sql`
|
|
|
|
Schema changes:
|
|
|
|
- `users.provider text not null default 'credentials'`
|
|
- `users.provider_user_id text null`
|
|
- `users.employee_id integer null`
|
|
- `users.last_login_at timestamptz null`
|
|
- `employees.prefix text null`
|
|
- `employees.first_name_th text null`
|
|
- `employees.last_name_th text null`
|
|
- `employees.first_name_en text null`
|
|
- `employees.last_name_en text null`
|
|
- `employees.display_name text null`
|
|
- `employees.phone text null`
|
|
- `employees.company_name text null`
|
|
- `employees.status text not null default 'active'`
|
|
- `employee_training_targets.employee_id integer null`
|
|
|
|
Backfill in migration:
|
|
|
|
- `employees.display_name` from `full_name`
|
|
- `employees.company_name` from `company`
|
|
- `employees.status` from `is_active`
|
|
- `users.employee_id` from membership + `employee_code`
|
|
- `training_records.employee_id` from linked users
|
|
- `employee_training_targets.employee_id` from linked users
|
|
|
|
## 4. Migration Strategy
|
|
|
|
This is a safe additive migration.
|
|
|
|
1. Add new nullable columns and defaults.
|
|
2. Backfill employee links from existing `employee_code`.
|
|
3. Keep all old columns and old flows working.
|
|
4. Start writing `employeeId` in import/auth/training-record paths.
|
|
5. Delay destructive cleanup until reports/dashboard/training matrix fully move to employee-master ownership.
|
|
|
|
## 5. User Login Behavior
|
|
|
|
Credential login is unchanged for end users.
|
|
|
|
New behavior on successful login:
|
|
|
|
- set `users.provider = 'credentials'`
|
|
- set `users.last_login_at`
|
|
- try to link `users.employeeId` from the active organization and `employeeCode`
|
|
|
|
If no matching employee master exists:
|
|
|
|
- login still succeeds
|
|
- `employeeId` remains `null`
|
|
|
|
## 6. Keycloak Mapping Behavior
|
|
|
|
Keycloak provider is not implemented in this stage.
|
|
|
|
However, the schema and identity model now support it:
|
|
|
|
- `provider`
|
|
- `providerUserId`
|
|
- `employeeId`
|
|
|
|
Recommended future behavior:
|
|
|
|
- read `empcode`
|
|
- map to `employees.employeeCode`
|
|
- set `users.employeeId`
|
|
|
|
## 7. Employee Import Behavior
|
|
|
|
Employee import now does more than create/update user rows.
|
|
|
|
It now:
|
|
|
|
1. upserts into `employees`
|
|
2. links any existing users in the same organization by `employeeCode`
|
|
3. keeps updating/creating user rows for backward compatibility
|
|
4. stores `employee_training_targets.employee_id`
|
|
|
|
Important note:
|
|
|
|
- this stage keeps the current auto-create user behavior because the existing application still depends on `users` in many places
|
|
|
|
## 8. Training Record Impact
|
|
|
|
New and edited training records now save:
|
|
|
|
- `userId` as the authenticated/persona owner used by current flows
|
|
- `employeeId` from the linked employee master when available
|
|
|
|
This means we can start moving reports and dashboards toward employee ownership without losing compatibility.
|
|
|
|
## 9. Dashboard Impact
|
|
|
|
Dashboard queries are not fully migrated yet.
|
|
|
|
What changed now:
|
|
|
|
- target resolution can read employee-linked targets indirectly through the user-to-employee link path
|
|
|
|
What remains:
|
|
|
|
- overview aggregates still primarily scope through `users` and `training_records.userId`
|
|
|
|
## 10. Reports Impact
|
|
|
|
Reports are not fully converted in this stage.
|
|
|
|
Current state:
|
|
|
|
- report ownership and filtering still use `users`
|
|
- training records now carry `employeeId`, which prepares the next migration stage
|
|
|
|
## 11. Permission Rules
|
|
|
|
No role behavior changed in this stage.
|
|
|
|
- `super_admin`: unchanged
|
|
- `admin`: unchanged
|
|
- `user`: unchanged
|
|
|
|
This stage is structural, not RBAC-changing.
|
|
|
|
## 12. Manual Test Checklist
|
|
|
|
1. Run migration `0009_lean_hobgoblin.sql`.
|
|
2. Sign in with an existing credential user.
|
|
3. Verify `users.last_login_at` updates.
|
|
4. Verify `users.employee_id` links when `employee_code` matches an employee master row in the active organization.
|
|
5. Import employee Excel data with existing employee codes.
|
|
6. Verify `employees` rows are created/updated.
|
|
7. Verify matching `users.employee_id` is linked.
|
|
8. Create a training record.
|
|
9. Verify `training_records.employee_id` is saved when the user is linked.
|
|
10. Update a training record.
|
|
11. Verify `employee_training_targets.employee_id` is populated on imported targets.
|
|
|
|
## 13. Rollback Plan
|
|
|
|
If rollback is required:
|
|
|
|
1. stop app deployment
|
|
2. revert application code
|
|
3. keep added columns unused, or manually drop them in a dedicated rollback migration
|
|
|
|
Because this stage is additive, the safest rollback is application-level rollback first, not destructive DB rollback.
|
|
|
|
## 14. Known Limitations
|
|
|
|
- `users` still contains employee-facing profile fields for compatibility
|
|
- reports still aggregate mainly via `userId`
|
|
- dashboard still aggregates mainly via `userId`
|
|
- training matrix still aggregates mainly via `userId`
|
|
- import still auto-creates login users for compatibility
|
|
- Keycloak runtime mapping is not implemented yet
|
|
|
|
## Changed Files
|
|
|
|
- `src/db/schema.ts`
|
|
- `src/auth.ts`
|
|
- `src/types/next-auth.d.ts`
|
|
- `src/features/employees/server/employee-identity-linking.ts`
|
|
- `src/app/api/import-employees/route.ts`
|
|
- `src/features/users/server/user-data.ts`
|
|
- `src/app/api/training-records/route.ts`
|
|
- `src/app/api/training-records/[id]/route.ts`
|
|
- `src/features/training-policy/server/employee-training-target-data.ts`
|
|
- `drizzle/0009_lean_hobgoblin.sql`
|