-- Add employee_id column to user_org_memberships
-- Date: 2025-11-18
-- Description: Add employee_id field to map biometric device user IDs to TAF users

-- Add employee_id column
ALTER TABLE user_org_memberships
ADD COLUMN IF NOT EXISTS employee_id VARCHAR(50);

-- Add index for faster lookups
CREATE INDEX IF NOT EXISTS idx_user_org_memberships_employee_id 
ON user_org_memberships(employee_id, org_id);

-- Add comment
COMMENT ON COLUMN user_org_memberships.employee_id IS 'Employee ID used in biometric devices and other external systems';

-- Example: Update existing users with employee IDs
-- UPDATE user_org_memberships SET employee_id = '1' WHERE user_id = 'some-user-uuid' AND org_id = 'some-org-uuid';
