Function getSeatUsage

server/src/db/organization-db.ts:398–415

(orgId: string)

 *   - they are an active member of a working group
 */
export async function getSeatUsage(orgId: string): Promise<{ contributor: number; community_only: number }> {
  const pool = getPool();
  const result = await pool.query<{ total: string; contributor: string }>(
    `SELECT
       COUNT(*) as total,
       COUNT(*) FILTER (WHERE
         om.seat_type = 'contributor'
         OR EXISTS (SELECT 1 FROM slack_user_mappings sm WHERE sm.workos_user_id = om.workos_user_id AND sm.mapping_status = 'mapped')
         OR EXISTS (SELECT 1 FROM working_group_memberships wgm WHERE wgm.workos_user_id = om.workos_user_id AND wgm.status = 'active')
       ) as contributor
     FROM organization_memberships om
     WHERE om.workos_organization_id = $1`,
    [orgId]
  );
  const total = parseInt(result.rows[0]?.total ?? '0', 10);
  const contributor = parseInt(result.rows[0]?.contributor ?? '0', 10);
  return { contributor, community_only: total - contributor };
}

/**
 * Check whether a new seat of the given type can be added to an organization.