# 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`