()
| 23 | * Returns a router to be mounted at /api/admin/users |
| 24 | */ |
| 25 | export function createAdminUsersRouter(): Router { |
| 26 | const router = Router(); |
| 27 | |
| 28 | // GET /api/admin/users - Unified view of AAO members and Slack users with engagement |
| 29 | // Uses local organization_memberships table (synced from WorkOS via webhooks) for fast queries |
| 30 | // Also joins users table for engagement scores and goal selection |
| 31 | router.get('/', requireAuth, requireAdmin, async (req, res) => { |
| 32 | const startTime = Date.now(); |
| 33 | try { |
| 34 | const pool = getPool(); |
| 35 | const slackDb = new SlackDatabase(); |
| 36 | const wgDb = new WorkingGroupDatabase(); |
| 37 | const { search, status, group, goal, lifecycle, stage } = req.query; |
| 38 | const searchTerm = typeof search === 'string' ? search.toLowerCase().trim() : ''; |
| 39 | const statusFilter = typeof status === 'string' ? status : ''; |
| 40 | const filterByGroup = typeof group === 'string' ? group : undefined; |
| 41 | const filterByGoal = typeof goal === 'string' ? goal : undefined; |
| 42 | const filterByLifecycle = typeof lifecycle === 'string' ? lifecycle : undefined; |
| 43 | const filterByStage = typeof stage === 'string' ? stage : undefined; |
| 44 | |
| 45 | // Get all AAO users from local organization_memberships table with community points |
| 46 | const aaoUsersResult = await pool.query<{ |
| 47 | workos_user_id: string; |
| 48 | email: string; |
| 49 | first_name: string | null; |
| 50 | last_name: string | null; |
| 51 | org_id: string; |
| 52 | org_name: string; |
| 53 | is_personal: boolean; |
| 54 | community_points: number; |
| 55 | lifecycle_stage: string | null; |
| 56 | relationship_stage: string | null; |
| 57 | goal_key: string | null; |
| 58 | goal_name: string | null; |
| 59 | last_activity_at: Date | null; |
| 60 | marketing_opt_in: boolean | null; |
| 61 | }>(` |
| 62 | SELECT DISTINCT ON (om.workos_user_id) |
| 63 | om.workos_user_id, |
| 64 | om.email, |
| 65 | om.first_name, |
| 66 | om.last_name, |
| 67 | om.workos_organization_id AS org_id, |
| 68 | o.name AS org_name, |
| 69 | COALESCE(o.is_personal, false) AS is_personal, |
| 70 | COALESCE(cp.total_points, 0)::int AS community_points, |
| 71 | u.lifecycle_stage, |
| 72 | pr.stage AS relationship_stage, |
| 73 | uc.goal_key, |
| 74 | uc.goal_name, |
| 75 | GREATEST(sm.last_slack_activity_at, u.updated_at) as last_activity_at, |
| 76 | ep.marketing_opt_in |
| 77 | FROM organization_memberships om |
| 78 | INNER JOIN organizations o ON om.workos_organization_id = o.workos_organization_id |
| 79 | LEFT JOIN users u ON u.workos_user_id = om.workos_user_id |
| 80 | LEFT JOIN person_relationships pr ON pr.workos_user_id = om.workos_user_id |
| 81 | LEFT JOIN unified_contacts_with_goals uc ON uc.workos_user_id = om.workos_user_id |
| 82 | LEFT JOIN slack_user_mappings sm ON sm.workos_user_id = om.workos_user_id |
no test coverage detected