Authenticated users (auto-created on first login). Auth-only: id, email, name, picture, access_level, template IDs. Employee/HR fields have moved to people
Org-level OAuth/API tokens for external services (Xero, Slack, Productive). Tokens are AES-GCM encrypted
user_external_ids
Deprecated — external IDs are now stored directly on the people table (xero_employee_id, productive_id). This table is retained for rollback safety and will be removed in a future phase
Source of truth for all employee/HR data. Synced from Productive (name, email, title, avatar, manager hierarchy) and enriched via Xero sync (employment, contact, demographics, financial fields). People do not require an app user account
The platform is migrating from user-centric to people-centric data ownership:
Phase 1: Employee/HR fields (employment, contact, demographics, financial) moved from users to people. The users table is now auth-only. Profile and insights queries read from people.
Phase 2: Added person_id foreign keys to 8 employee data tables (pay_summaries, payslips, leave_balances, leave_requests, scorecards, onboarding_progress, reviews, feedback_requests). Existing rows backfilled via users.person_id join. Auth middleware resolves personId on every request. API routes use a dual-write pattern (both user_id and person_id) during the transition period. Indexed for query performance.
Phase 3: Eliminated user_external_ids dependency. All external ID lookups (Xero, Productive) now read directly from people table columns (xero_employee_id, productive_id). Removed dual-writes to user_external_ids from Xero linking, Productive sync, and payroll sync. Admin mappings UI now shows all people and writes directly to people. The user_external_ids table is retained for rollback safety.
Phase 4: Added getScopedPersonIds() using people.manager_id hierarchy for data scoping. All route scoping across scorecards, onboarding, leave, and performance now uses WHERE person_id IN (...) instead of WHERE user_id IN (...). Managers see direct reports via the people hierarchy. List queries JOIN people for name/email instead of users. The old getScopedUserIds() (squad-based) is retained for backward compatibility.
Phase 5: Cleanup and removal of dual-writes. Dropped user_external_ids table. Deleted getScopedUserIds(). Migrated all WHERE user_id = ? data queries to WHERE person_id = ? in performance, onboarding, and leave routes. Removed user_id from INSERT statements in reviews, scorecards, onboarding_progress, and leave_requests. Leave approval ownership checks use person_id instead of user_id, and manager verification uses people.manager_id hierarchy instead of squad_members. Leave integrations resolve external IDs via people.id instead of people.user_id. Removed backward-compat users table write from Xero payroll sync. Added partial unique indexes on person_id for ON CONFLICT migration.
Phase 6: Migrated pay_summaries, payslips, and leave_balances reads from WHERE user_id to WHERE person_id. Backfilled null person_id values. Added UNIQUE(person_id, leave_type_id) index for leave_balances ON CONFLICT migration. refreshPayrollData now takes personId directly. Replaced squad_members usage in insights (span of control, manager count, team sizes) and performance (feedback peer selection) with people.manager_id hierarchy. Scorecards squad filter uses people.squad column. People profile pay/leave lookups use person_id directly.
Phase 7: Final cleanup. Recreated all 8 data tables via SQLite table recreation to drop user_id columns. person_id is now NOT NULL REFERENCES people(id) ON DELETE CASCADE on all data tables. Changed pay_summaries PK from user_id to person_id. Removed subject_id from feedback_requests (uses subject_person_id only). Unique constraints moved from user_id combos to person_id combos (inline, non-partial). Dropped squad_members table — squad membership now derived from people.squad column with idx_people_squad index. Admin squad management and squad filter routes rewritten. Dropped all stale user_id indexes.
